SQL test practice

What the real test usually looks like

There is no single SQL test. Each employer chooses a testing platform, or writes its own test, and decides how many questions, how long and which database. The job posting or the test invitation is the best guide to yours. Public examples show the range:

• HackerRank's SQL skills tests. HackerRank lists its SQL (Basic) skills test as 2 questions in 30 minutes, covering topics like simple queries (HackerRank), and its SQL (Intermediate) test as 2 questions in 35 minutes, covering topics like complex joins (HackerRank). Employers who build their own HackerRank tests can add database questions and choose which databases candidates may use (HackerRank support).
• A query-writing task. TestGorilla describes its SQLite coding test as 30 minutes spent writing a query for a realistic scenario against a database of medium complexity, covering joining and filtering, simple calculations, sorting, rounding and excluding irrelevant entries (TestGorilla).
• A short knowledge test. TestGorilla lists its MySQL test as 15 questions in 10 minutes, drawn from a bank of 101 (TestGorilla).

Content areas commonly reported across SQL tests:

• Filtering and sorting: SELECT, WHERE, ORDER BY and conditions with AND, OR, IN and BETWEEN.
• Joins: inner and left joins, and what happens to rows with no match.
• Grouping: GROUP BY with COUNT, SUM, AVG, MIN and MAX, and HAVING to filter groups.
• NULLs: how missing values behave in comparisons, counts and averages.
• Subqueries: a query inside another, with IN, EXISTS or a single value.
• Writing a query from a plain request, often scored by whether it returns exactly the expected rows.

What our mocks cover. Each full mock is 18 questions in 45 minutes in four sections: SQL concepts (5), what the query returns (6), fixing a query (4) and choosing the right query (3). You can go back and flag questions. We use portable standard SQL, so the queries mean the same thing in PostgreSQL, MySQL, SQL Server, SQLite and most other databases. Our mocks practice reading, tracing and choosing queries; they do not run queries you write. Many real SQL tests ask you to write a query that is checked against expected results, so use our mocks to build the reading and tracing skill behind that, and also write queries yourself in a free database such as SQLite.

Question types with worked examples

One worked example for each section, with the trap most people fall into.

1. SQL concepts. What is the difference between COUNT() and COUNT(email)? COUNT() counts every row; COUNT(email) counts only the rows where email is not NULL. Trap: assuming every count includes every row. Most aggregate functions skip NULLs.

2. What the query returns. Table scores has values 8, 5, NULL and 8. What does SELECT COUNT(DISTINCT score) return? The distinct non-NULL values are 8 and 5, so the answer is 2. Trap: counting NULL as a value, or counting the duplicate 8 twice. Write the table out and cross off rows as the query would.

3. Fixing a query. A query reads WHERE city = 'Austin' OR city = 'Dallas' AND active = 1, and it returns inactive Austin customers. AND is evaluated before OR, so the condition really means Austin, or active Dallas. The fix is brackets: WHERE (city = 'Austin' OR city = 'Dallas') AND active = 1. Trap: reading the condition left to right.

4. Choosing the right query. You need the five most recent orders. Which is right: ORDER BY order_date DESC with a limit of 5, or ORDER BY order_date with a limit of 5? The first. Without DESC the sort is oldest first, so you would get the five oldest. Trap: picking the query that looks right without checking the sort direction. (Databases spell the limit differently, such as LIMIT 5 or FETCH FIRST 5 ROWS ONLY, so check which your test uses.)

5. Joins with conditions. A left join followed by a WHERE test on the right table's column quietly turns into an inner join, because unmatched rows have NULL there and the test removes them. Trap: not noticing which rows vanish. Ask, "What happens to a row with no match?"

How to prepare

1. Read your invitation first. Find out which platform you will use, how long you have, whether you will write queries or answer multiple choice questions, and which database it runs. Formats vary too much to assume.
2. Learn the order a query runs in. FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT and ORDER BY. Most "why is this wrong?" questions make sense once you know which step sees which data.
3. Trace small tables by hand. Write out three or four rows, then apply each clause in order. It is the fastest way to check your answer, and it catches NULL and duplicate traps.
4. Treat NULL as its own topic. Practice IS NULL, COUNT(column) versus COUNT(*), AVG skipping NULLs, NOT IN with a NULL in the list, and what a left join puts in unmatched columns.
5. Write real queries too. Our mocks build reading and tracing skill; also write queries in a free database like SQLite so typing them under time feels normal. Some tests check your query against many cases, such as the more than 10 test cases TestGorilla describes for its SQLite test (TestGorilla).
6. Check the result, not just the syntax. A query that runs can still return the wrong rows. Before you submit, ask what happens with no matches, duplicates and NULLs.
7. Practice at the real pace. A short knowledge test like TestGorilla's MySQL test, 15 questions in 10 minutes, gives you well under a minute a question (TestGorilla). Take full timed mocks so the pace feels normal on the day.

How scoring usually works

Scoring depends on the employer and the platform. There is no standard SQL score and no universal pass mark. Each employer decides what counts as a good result. Two public examples of how query questions are checked:

• HackerRank database questions: HackerRank says it scores these automatically by comparing your query's result with the expected output. A match gets the full score and anything else gets zero, unless the employer reviews it by hand and awards partial credit. The employer can also choose to ignore row order (HackerRank support).
• TestGorilla's SQLite test: TestGorilla says candidates are ranked using more than 10 test cases that check the corner cases of the solution (TestGorilla).

Because results are often checked row by row, a query that is almost right, such as one that drops a customer with no orders, can score the same as no answer at all.

Our practice score is the number of our own questions you answered correctly, with a breakdown by section, so you can see where to focus. It is a practice score: it is not a score on any employer's or platform's test and does not predict whether you will pass.

Common mistakes

• Comparing with NULL using =. Use IS NULL and IS NOT NULL. Anything = NULL is unknown, so the row is filtered out.
• Filtering a left join in WHERE. A test on the right table's columns removes the unmatched rows. Put that test in the ON clause if you need to keep them.
• Using WHERE for a total. Aggregates belong in HAVING, after GROUP BY.
• Selecting a column you did not group. In standard SQL every selected column must be grouped or aggregated. Some databases let it run and pick a value for you, which hides the mistake.
• Forgetting duplicates. A join can repeat rows, which inflates SUM and COUNT. Ask whether each row on one side can match more than one on the other.
• Relying on one database's shortcuts. Some functions and limit syntax differ between databases. Learn the standard form and check which database your test uses.