AlgoHeap | Software Engineering, System Design, DSA & AI/ML Learning Platform

Database Engineering

Overview

SQL is the primary language used by backend systems to read and modify relational data. Even when an application uses an ORM, production engineers still need to understand the SQL generated underneath.

SQL knowledge matters for correctness, latency, debugging, migrations, reporting, API pagination, and incident response. A small query change can reduce response time from seconds to milliseconds, or accidentally lock millions of rows.

Backend SQL responsibilities

Fetch exactly the data an API needs
Avoid unnecessary round trips
Keep mutations transactional
Understand indexes and execution plans
Debug slow queries safely
Prevent accidental full-table operations

Backend Request to SQL

Architecture

HTTP Request
    |
    v
Controller
    |
    v
Service
    |
    v
Repository / DAO
    |
    v
SQL Query
    |
    v
Database Engine

Database Engineering

SELECT

SELECT retrieves rows and columns from one or more tables. Production queries should select only the columns needed by the use case.

sql

SELECT id, email, status, created_at
FROM customers
WHERE status = 'ACTIVE';

Avoid SELECT * in API code because schema changes can increase payload size, break mappers, expose sensitive columns, and reduce index-only scan opportunities.

Database Engineering

INSERT, UPDATE, DELETE

INSERT, UPDATE, and DELETE mutate data. In backend systems, mutations should usually run inside explicit transaction boundaries and include safe predicates.

sql

INSERT INTO orders (customer_id, status, total_amount)
VALUES (42, 'CREATED', 1999);

UPDATE orders
SET status = 'PAID'
WHERE id = 1001 AND status = 'CREATED';

DELETE FROM shopping_cart_items
WHERE cart_id = 77 AND product_id = 501;

Database Engineering

WHERE, GROUP BY, HAVING, ORDER BY

WHERE filters rows before aggregation. GROUP BY groups rows. HAVING filters groups after aggregation. ORDER BY defines result ordering.

sql

SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spend
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY total_spend DESC;

SQL Logical Processing Order

Architecture

FROM / JOIN
    |
    v
WHERE
    |
    v
GROUP BY
    |
    v
HAVING
    |
    v
SELECT
    |
    v
ORDER BY
    |
    v
LIMIT / OFFSET

Database Engineering

JOINS

Joins combine rows from multiple tables. Backend APIs often join users, orders, payments, products, inventory, and audit records.

sql

SELECT o.id, o.status, c.email, p.status AS payment_status
FROM orders o
JOIN customers c ON c.id = o.customer_id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '7 days';

Common join types

INNER JOIN returns matching rows from both tables
LEFT JOIN keeps all rows from the left table
RIGHT JOIN keeps all rows from the right table
FULL OUTER JOIN keeps rows from both sides

Join Mental Model

Architecture

orders.customer_id
        |
        v
customers.id

orders.id
        |
        v
payments.order_id