SELECT, WHERE and GROUP BY
The fictional orders below belong to two customers. WHERE filters individual orders; GROUP BY forms one row per customer; HAVING filters those groups.
WITH orders(order_id, customer_id, total) AS (
VALUES (101, 1, 40), (102, 1, 60), (103, 2, 30)
)
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
WHERE total >= 30
GROUP BY customer_id
HAVING SUM(total) >= 50
ORDER BY revenue DESC;
The result is customer 1, two orders and revenue of 100. Customer 2 passes the row filter but fails the group filter. ORDER BY controls the output order. The figures are teaching data.
JOIN and missing matches
Use a LEFT JOIN when customers with no orders must remain in the report. Count a matched order identifier rather than COUNT(*), which also counts the customer’s unmatched output row.
WITH customers(customer_id, name) AS (
VALUES (1, 'Mina'), (2, 'Nuri'), (3, 'Teo')
), orders(order_id, customer_id, total) AS (
VALUES (101, 1, 40), (102, 1, 60), (103, 2, 30)
)
SELECT c.name, COUNT(o.order_id) AS order_count, SUM(o.total) AS revenue
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;
Mina has two orders and revenue of 100. Nuri has one and 30. Teo has zero orders and NULL revenue. Use COALESCE(SUM(o.total), 0) if the report defines that missing total as zero. Practice SQL joins before joining several detail tables.
Window functions
A window function adds a calculation while keeping each row. This query numbers orders within each customer; order_id breaks ties in total.
WITH orders(order_id, customer_id, total) AS (
VALUES (101, 1, 40), (102, 1, 60), (103, 2, 30)
)
SELECT order_id, customer_id, total,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY total DESC, order_id
) AS rn
FROM orders
ORDER BY customer_id, rn;
Customer 1’s order 102 gets rn = 1; order 101 gets 2. Customer 2’s order 103 gets 1. To keep only rn = 1, put this query in another CTE and filter the numbered rows outside it. The window function guide covers ranks and running totals.
Plans and safe changes
Put EXPLAIN before a query to inspect its plan. Use the SQL query optimization guide for estimates, measured evidence and correctness checks. An index’s behavior depends on the database engine.
This sheet focuses on reading data. Practice updates and transactions in the SQL course; use the PostgreSQL administration documentation for database administration. For timed selection tests, use SQL interview practice. The SQL certification guide explains the difference between vendor credentials and our practice records.