7 SQL interview exercises that reveal how you check results

SQL interview practice is most useful when you check what each returned row represents. These seven exercises use a tiny fictional dataset so you can calculate the answer by hand before trusting a query. Explain your assumptions, especially around joins and counting.

Create Customers with customer_id values 1, 2 and 3, named A, B and C. Create Orders with columns order_id, customer_id and amount: (101, 1, 40), (102, 1, 60), (103, 2, 25). Customer C has no order. Amounts are fictional units, not real revenue. The query examples use basic SQL; confirm the database and assessment rules before your interview.

1. Select only the columns needed

Query: SELECT order_id, amount FROM Orders;

Expected records are 101 with 40, 102 with 60 and 103 with 25. Explain why you selected these columns rather than every field. The purpose is to make the answer’s scope visible, not to imply that selecting all columns is always wrong.

Do not assume the database will return a particular row order without an explicit ordering instruction. Check column names and data types before moving to a more complex exercise.

2. Filter using the exact boundary

Query: SELECT order_id FROM Orders WHERE amount >= 40 ORDER BY order_id;

Expected answer: 101 and 102. Now change the condition to amount > 40; only 102 remains. Explain the difference between inclusive and exclusive boundaries.

This is a small but useful interview check. Repeat the requested condition in plain language before writing it. If a real task says “recent orders” or “large orders”, clarify the date or amount threshold rather than choosing one silently.

3. Aggregate at the requested level

Query: SELECT customer_id, SUM(amount) AS total_amount FROM Orders GROUP BY customer_id ORDER BY customer_id;

Expected answer: customer 1 totals 100 and customer 2 totals 25. Each output row now represents a customer with orders, not an individual order. The totals reconcile to 125.

Describe the grouping level before discussing the result. PostgreSQL’s aggregate tutorial explains grouping and aggregate functions. Do not mistake an aggregate result for evidence about customers absent from the source rows.

4. Check what an inner join leaves out

Query: SELECT c.name, o.order_id FROM Customers c INNER JOIN Orders o ON c.customer_id = o.customer_id ORDER BY o.order_id;

Expected rows: A–101, A–102 and B–103. Customer C does not appear because there is no matching order. A appears twice because two orders match.

Explain both effects. Repeated customer information is not automatically a duplicate error; it follows from the relationship. The PostgreSQL join tutorial is a primary reference for these join principles.

5. Keep customers with no matching order

Use the same join but replace INNER JOIN with LEFT JOIN and order by c.customer_id, o.order_id. Expected output adds C with a NULL order_id. Distinguish “no matching order” from a real order whose amount happens to be zero.

Explain why starting from Customers matters. If the business question asks about all customers, beginning only with Orders cannot reveal customers who have no order record. State which side of the join defines the population you intend to retain.

6. Count orders without counting the unmatched row

Query: SELECT c.customer_id, COUNT(o.order_id) AS order_count FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id ORDER BY c.customer_id;

Expected counts: 1 has 2, 2 has 1 and 3 has 0. COUNT of the non-null order identifier avoids treating C’s unmatched joined row as an order.

Compare this with COUNT(*) and explain the difference. Practise with a unique, non-null order identifier; do not assume an arbitrary nullable field is an interchangeable counting key.

7. Challenge your result with a controlled change

Add a new fictional order (104, 3, 10). The total amount should become 135 and customer 3’s order count should become 1. Re-run the relevant queries and compare these expectations.

Then remove the practice addition so your baseline remains reproducible. This demonstrates a useful habit: create a small case whose expected answer you know. It is stronger than saying a query looks correct because it ran without an error.

Explain your solution in an interview

Use four sentences: what the question asks; what one output row represents; how the query achieves that; and how you checked it. If you do not finish, explain the remaining uncertainty. Do not claim that these exercises cover every SQL assessment or advanced database topic.

Should I memorise these queries?

Understand them and practise variations. A changed counting unit or join relationship can change the correct answer.

Can I use real employer data to practise?

Use fictional or appropriately permitted data. Keep confidential information out of interview files and public repositories.

For broader preparation, read the data analyst interview questions and distinguish practice work from professional experience in your application.