JOIN connects tables through a shared column, usually a foreign key. INNER JOIN returns only rows for which matching data exists in both tables.

SELECT   a.col1, b.col2
FROM     table_a a                  -- a is an alias for table_a
INNER JOIN table_b b               -- INNER is optional; JOIN alone means INNER JOIN
  ON   a.id = b.a_id;             -- Join condition (primary key to foreign key)

JOIN is essential when an API must return orders with user names. Combining the data in one query avoids an N+1 query loop in the application.

Join the orders and users tables. For orders whose status is 'completed', return order_id, user_name, amount, ordered_at, ordered by ordered_at from newest to oldest.

Expected output (completed orders only, ordered_at descending):

Orders 102 (pending) and 105 (cancelled) are excluded.

SELECT
  o.order_id,
  u.name   AS user_name,
  o.amount,
  o.ordered_at
FROM       orders o
INNER JOIN users  u            -- Join rows that exist in both tables
  ON  o.user_id = u.user_id
WHERE  o.status = 'completed'  -- Completed orders only
ORDER BY o.ordered_at DESC;

/*
  Logical execution order:
  1. FROM orders o              → Read rows
  2. INNER JOIN users u         → Join matching rows
  3. WHERE o.status             → Filter rows
  4. SELECT                     → Evaluate columns
  5. ORDER BY o.ordered_at DESC → Sort and return
*/

GROUP BY groups rows by selected column values. Aggregate functions such as COUNT, SUM, AVG, MAX, and MIN reduce each group to one row. HAVING then filters the aggregate results, much like WHERE filters rows before aggregation.

SELECT
  group_col,
  COUNT(*)      AS row_count,
  SUM(amount)   AS total_amount
FROM   table_name
WHERE  status = 'completed'  -- Filter individual rows before grouping
GROUP BY group_col                  -- Create one group per value
HAVING COUNT(*) >= 2;            -- Filter groups after aggregation

For completed orders in orders, calculate order_count and total_amount for each user_id. Return only users with at least two orders, ordered by total_amount descending.

Expected output (completed orders, order_count ≥ 2, total_amount descending):

U02 has only one completed order (order_id 3), so HAVING excludes it.

SELECT
  user_id,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_amount
FROM   orders
WHERE  status = 'completed'  -- Filter rows before aggregation
GROUP BY user_id
HAVING COUNT(*) >= 2          -- Keep groups with at least two orders
ORDER BY total_amount DESC;

/*
  Logical execution order:
  1. FROM orders                → Read rows
  2. WHERE status               → Filter rows
  3. GROUP BY user_id           → Form groups
  4. COUNT(*) / SUM(amount)     → Aggregate each group
  5. HAVING COUNT(*) >= 2       → Filter groups
  6. SELECT                     → Evaluate columns
  7. ORDER BY total_amount DESC → Sort and return
*/

LIMIT sets the maximum number of rows to return, while OFFSET skips rows from the beginning. Together they provide the basic page-number pagination used by endpoints such as /items?page=2&per_page=10.

SELECT col1, col2
FROM   table_name
ORDER BY col1 ASC  -- Always define a stable order
LIMIT  10             -- Number of rows per page
OFFSET 10;            -- Rows to skip: (page - 1) × page size

Large offsets force the database to scan and discard many rows. For large production APIs, cursor pagination based on the last retrieved ID is usually more efficient.

From products, return page 2 with three rows per page. Select product_id, name, price and order the rows by price ascending.

Sort by price ascending and return page 2 (items 4–6):

SELECT
  product_id,
  name,
  price
FROM   products
ORDER BY price ASC  -- Lowest price first
LIMIT  3               -- Return three rows
OFFSET 3;              -- Skip page 1

/*
  Logical execution order:
  1. FROM products     → Read rows
  2. ORDER BY price    → Sort rows
  3. OFFSET 3          → Skip three rows
  4. LIMIT 3           → Return three rows
*/

INSERT adds a new row to a table. PostgreSQL's RETURNING clause returns columns from the inserted row like SELECT. It is the standard way for a create API to return a database-generated ID without a second query.

INSERT INTO table_name (col1, col2)
VALUES ('value1', 'value2')
RETURNING id, col1;  -- Return values from the inserted row

SERIAL, BIGSERIAL, and GENERATED ALWAYS AS IDENTITY columns are generated during INSERT; the application does not need to choose an ID in advance.

Create a row in tasks with title='Batch processing implementation', status='pending', and created_at set to the current time. Return the database-generated id and created_at.

Values returned by RETURNING (the generated id and current timestamp):

The database generates id=3 automatically; the application does not supply it.

INSERT INTO tasks (
  title,
  status,
  created_at
)
VALUES (
  'Batch processing implementation',
  'pending',
  NOW()                  -- Current timestamp
)
RETURNING id, created_at;  -- Return values from the inserted row

/*
  Execution flow:
  1. INSERT INTO tasks       → Prepare a new row
  2. VALUES                  → Assign values to columns
  3. BIGSERIAL               → Generate id automatically
  4. RETURNING id, created_at → Return the inserted row's values
*/