SQL MCQ test: 12 questions with answers (CodinGame, SkillValue type)
MCQSQL12 questionsUpdated
This quiz is for developers, data analysts and anyone about to sit a SQL screening test on SkillValue, CodinGame or HackerRank. It contains 12 mixed-level questions built on small explicit tables, each with the correct answer and a detailed explanation of the trap.
In short
SQL screening tests keep coming back to the same handful of topics: how many rows an INNER, LEFT or FULL JOIN returns on a tiny pair of tables, the difference between WHERE and HAVING, how NULL behaves in comparisons, COUNT and NOT IN, subqueries with EXISTS, and window functions such as ROW_NUMBER, RANK and DENSE_RANK. A few more conceptual questions cover indexes, the logical order of query evaluation and the ACID properties of transactions. Most questions show one or two small tables and ask for the exact result of a query, so the real skill being tested is walking through a query by hand, row by row.
The topics that come up most
Joins: INNER, LEFT, RIGHT, FULLYou get two tables of three or four rows and must say how many rows the join returns. The classic trap is forgetting unmatched rows and rows whose join key is NULL.
GROUP BY, HAVING and WHEREWHERE filters rows before aggregation, HAVING filters groups after it. Questions put both clauses in the same query to check that you apply them in the right order.
NULL and three-valued logicA comparison with NULL is neither true nor false but UNKNOWN. That is the root of the traps around col = NULL, COUNT(col) versus COUNT(*) and especially NOT IN when the subquery contains a NULL.
Subqueries and EXISTSTests check that you can choose between IN, EXISTS and a join, and that you know how NOT IN and NOT EXISTS behave differently in the presence of NULL.
Window functionsROW_NUMBER, RANK and DENSE_RANK only differ on ties. Questions almost always use a table with one duplicated value to see whether you know what number the next row gets.
Indexes, performance and transactionsKnowing when an index can be used (no function applied to the column, no LIKE starting with %) and the four ACID properties covers most questions of this kind.
Practise: 12 questions with answers
Pick an answer to see the explanation.
Consider the two tables below:
employees departments
id | name | dept_id id | name
---+-------+-------- ---+------
1 | Ana | 10 10 | IT
2 | Bob | 20 20 | HR
3 | Chloe | NULL 40 | Legal
4 | Dan | 30
How many rows does this query return?
SELECT e.name, d.name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;
An INNER JOIN keeps only the pairs where the condition evaluates to true. Only Ana (10 → IT) and Bob (20 → HR) find a department, so 2 rows. Chloe has a NULL dept_id (no comparison with NULL is ever true), Dan points to department 30 which does not exist, and Legal has no employees. 4 would be the result of a LEFT JOIN from employees, 5 that of a FULL OUTER JOIN.
Still using the employees table above, how many rows does this query return?
SELECT name FROM employees WHERE dept_id = NULL;
In SQL, any comparison with NULL using = or <> yields UNKNOWN, never TRUE. WHERE only keeps rows that evaluate to TRUE, so nothing passes: 0 rows. The syntax is perfectly valid; it is the result that surprises people. To find Chloe you must write WHERE dept_id IS NULL.
SELECT DISTINCT category
FROM products
ORDER BY category
LIMIT 2;
DISTINCT first reduces the categories to three values: book, pen, lamp. ORDER BY sorts them alphabetically: book, lamp, pen. LIMIT 2 keeps the first two: book and lamp. The logical order is always DISTINCT, then ORDER BY, then LIMIT, regardless of how the query is written. "book, book" ignores DISTINCT and "book, pen" ignores the sort.
A transaction transfers 100 between two accounts:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
The server crashes right after the first UPDATE, before the COMMIT. What is the state of the accounts once the database restarts?
This is atomicity, the A in ACID: a transaction is all or nothing. As long as the COMMIT has not been durably recorded, the database undoes every partial change on restart using its transaction log (undo/redo log). The first option describes exactly what atomicity prevents. The isolation level governs visibility between concurrent transactions, not crash recovery.
Using the employees and departments tables from the start (PostgreSQL or SQL Server), how many rows does this query return?
SELECT e.name, d.name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;
A FULL OUTER JOIN returns the matched rows plus the unmatched rows from both sides. Matched: Ana-IT and Bob-HR (2). Employees without a department: Chloe (NULL dept_id) and Dan (department 30 does not exist), with d.name NULL (2). Department without employees: Legal, with e.name NULL (1). Total: 5. The answer 4 corresponds to a LEFT JOIN, and 7 to naively adding the two table sizes without accounting for matches.
Consider the orders table:
id | customer | amount
---+----------+-------
1 | A | 100
2 | A | 50
3 | B | 200
4 | C | 30
5 | B | 20
Which rows does this query return?
SELECT customer, SUM(amount) AS total
FROM orders
WHERE amount > 40
GROUP BY customer
HAVING SUM(amount) > 100;
WHERE is applied first, row by row: only orders 1 (A, 100), 2 (A, 50) and 3 (B, 200) survive. GROUP BY then gives A = 150 and B = 200; C is gone before aggregation even starts. HAVING keeps groups whose sum exceeds 100: A and B. The answer "B (220)" wrongly assumes the WHERE is ignored, and "B (200) only" forgets that 150 is greater than 100.
Using the employees table from the start, what does this query output?
SELECT COUNT(*), COUNT(dept_id), COUNT(DISTINCT dept_id)
FROM employees;
COUNT(*) counts every row: 4. COUNT(dept_id) ignores NULLs: 10, 20 and 30, so 3. COUNT(DISTINCT dept_id) counts distinct non-NULL values: 10, 20, 30, also 3. Chloe's NULL is never counted as a value, neither by COUNT(col) nor by COUNT(DISTINCT col), which rules out "4, 4, 3" and "4, 3, 4".
Using the employees and departments tables from the start, what does this query return?
SELECT d.name
FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.id
);
NOT EXISTS is true when the correlated subquery finds no row. For IT (10) and HR (20) an employee exists, so they are excluded. For Legal (40) no employee has dept_id = 40: the subquery is empty and Legal is returned. Chloe's NULL dept_id causes no trouble: NULL = 40 is UNKNOWN, so that row is simply not found. This is exactly what makes NOT EXISTS safer than NOT IN (see the next question).
Consider the scores table:
name | score
------+------
Ana | 90
Bob | 85
Chloe | 85
Dan | 70
You run:
SELECT name,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
RANK() OVER (ORDER BY score DESC) AS rk,
DENSE_RANK() OVER (ORDER BY score DESC) AS drk
FROM scores;
Which values (rn, rk, drk) does Dan get?
Ana is first. Bob and Chloe are tied at 85: RANK and DENSE_RANK both give them 2, while ROW_NUMBER gives them 2 and 3 in an arbitrary order. For Dan, ROW_NUMBER simply continues: 4. RANK skips the positions taken by the tie: 4 as well. DENSE_RANK leaves no gap: 3. Hence (4, 4, 3). If all three functions returned the same value, there would be no ties in the table.
Using the employees and departments tables from the start, what does this query return?
SELECT name
FROM departments
WHERE id NOT IN (SELECT dept_id FROM employees);
The subquery returns {10, 20, NULL, 30}. id NOT IN (...) is equivalent to id <> 10 AND id <> 20 AND id <> NULL AND id <> 30. For Legal (40) the three real comparisons are true, but 40 <> NULL is UNKNOWN, so the whole AND is UNKNOWN and the row is rejected. No rows are returned and no error is raised. This is the most common SQL test trap: whenever a subquery might contain NULL, use NOT EXISTS or add WHERE dept_id IS NOT NULL inside the subquery.
Using the employees and departments tables from the start, how many rows do queries A and B return respectively?
-- A
SELECT e.name FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.name = 'IT';
-- B
SELECT e.name FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id AND d.name = 'IT';
In A, the LEFT JOIN first produces 4 rows (every employee), then WHERE d.name = 'IT' removes the three rows where d.name is HR or NULL, leaving only Ana: 1 row. The LEFT JOIN effectively behaves like an INNER JOIN. In B, the condition is part of the ON clause, so it only decides which departments rows get matched. Every employee is kept (that is the definition of a LEFT JOIN) and only Ana has a non-NULL d.name: 4 rows. Putting a filter on the right-hand table in WHERE or in ON does not give the same result.
The orders table holds 50 million rows and has a regular B-tree index on the created_at column (DATETIME). Which of these WHERE clauses lets the engine use that index for a range scan?
A B-tree index is sorted on the raw column values. As soon as a function is applied to the column (YEAR, DATE, CAST), the engine has to compute it for every row: the predicate is no longer sargable and the index on created_at is ignored in favour of a full scan. Only a direct comparison of created_at against bounds lets the engine seek into the index and read just the relevant range. The other forms would need a functional index or an indexed computed column.
Score0 / 12
On test day
Before answering a join question, list the rows of each table and cross out those with no match: the row count then reads off directly.
Whenever a question contains a NULL, look for the trap: = NULL, NOT IN, COUNT(col) and aggregates each treat NULL in a different way.
Recite the logical evaluation order: FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. Most questions fall out of applying it step by step.
On window functions, spot the ties in the table: that is the only place where ROW_NUMBER, RANK and DENSE_RANK diverge.
Most platforms use MySQL or PostgreSQL, sometimes SQL Server. MCQ questions almost always stick to standard SQL; when a detail depends on the dialect (LIMIT, the absence of FULL OUTER JOIN in MySQL, TOP in SQL Server), the question says so.
Do I need to know window functions for a SQL test?
Yes, they have become standard even for generalist developer roles. Expect at least one question on ROW_NUMBER, RANK or DENSE_RANK with tied values, and sometimes PARTITION BY for a top-N-per-group problem.
Why does NOT IN sometimes return zero rows when there should be results?
Because the subquery contains a NULL. The comparison with that NULL is UNKNOWN, which makes the whole condition UNKNOWN and rejects every row. Replace NOT IN with NOT EXISTS, or filter out NULLs inside the subquery.
How long is a SQL MCQ in an interview process?
Usually 15 to 30 minutes for 10 to 20 questions, so roughly one minute per question. Practise walking through a query on a small table in under a minute; that is the skill actually being measured.