Fifteen SQL queries you'll be asked at an interview

How to prepare

Don't memorise queries. Create a database with users, orders and products, fill it by hand, and solve against it. Understanding stays; memorisation doesn't.

Everything below sits within the basic level.

The fifteen

1. Rows with a condition. Orders from the last month. Tests: working with dates.

2. Sorting and limiting. The ten most expensive orders. Tests: descending sort and limiting.

3. Distinct values. The list of cities orders came from, without repeats. Tests: knowing how to select distinct values.

4. Counting. How many orders a user has in total. Tests: counting with a condition.

5. Grouping. How many orders each user has. Tests: understanding grouping. The classic error is selecting a column that's neither grouped nor aggregated.

6. Filtering groups. Users with more than five orders. Tests: the difference between filtering rows and filtering groups. One of the most common mistakes.

7. Joining two tables. Orders together with user names. Tests: inner joins.

8. Left join. All users and their order counts, including users with none. Tests: understanding why left joins exist. A key problem.

9. Finding what's missing. Users who haven't placed a single order. Tests: a left join with a null check, or a negated membership test.

10. Sums per group. Total order value per city. Tests: combining a join, grouping and aggregation.

11. Averages. Average order value per month. Tests: grouping by a computed value.

12. Maximum within a group. Each user's most recent order. Tests: thinking. Several approaches work; any correct one is fine.

13. Conditional update. Mark orders older than a year as archived. Tests: caution. Say out loud that without a condition the whole table would be updated.

14. Conditional delete. Delete incomplete orders older than a month. Tests: the same, plus awareness of consequences.

15. Three tables. User, order and order items — show who bought what. Tests: joining more than two tables.

Where beginners go wrong

Confusing row filters with group filters. Selecting rows and selecting groups are different things.

Forgetting that joins can duplicate rows. A user with five orders appears five times in the result.

Aggregating after a join without grouping. You get a number over the whole table rather than per user.

Ignoring nulls. Comparing against null doesn't behave as expected: it needs its own check.

What to say when you don't know

The same as with any problem: reason aloud. "Here I need to join the two tables, group by user and count. I don't recall the exact syntax for filtering groups, but the logic is this." That scores higher than silence.