What does SELECT CASE WHEN points >= 60 THEN 'pass' ELSE 'fail' END AS result, ... return? SQL Grouping and Aggregation

Block 3, Grouping and Aggregation, question 18 of the SQL sample

CREATE TABLE scores (id INTEGER, student TEXT, points INTEGER);
INSERT INTO scores VALUES
  (1, 'ann', 45),
  (2, 'bo', 82),
  (3, 'cy', 91),
  (4, 'dee', 58),
  (5, 'eli', 30);

SELECT CASE WHEN points >= 60 THEN 'pass' ELSE 'fail' END AS result, COUNT(*) FROM scores GROUP BY result ORDER BY result

What does this query return?

A. fail | 3 pass | 2
B. pass | 2 fail | 3
C. fail | 2 pass | 3
D. fail | 3 pass | 3

Show the answer and why each option is right or wrong

Answer, A. fail | 3 pass | 2

A. fail | 3 pass | 2: Correct. Ann, dee and eli score under 60 and fall into fail, while bo and cy score 60 or above and fall into pass.
B. pass | 2 fail | 3: The counts are right but the order is not, ORDER BY result sorts alphabetically and fail comes before pass.
C. fail | 2 pass | 3: This swaps the two counts, crediting fail with only two students when three of them, ann, dee and eli, score under 60.
D. fail | 3 pass | 3: This counts dee among the passing students, but 58 is below the 60 threshold in the CASE expression, so dee falls into fail rather than pass.

GROUP BY can group by the same expression used in the SELECT list, addressed either by repeating the expression or by its output alias. Each row is first classified by the CASE expression, and only afterward are the classified rows counted per group.

The answer above is what SQLite and DuckDB both returned when the snippet was run, not what someone expected it to do.

This question comes from a bank of 502 SQL interview questions, all built the same way, sold as one PDF with the full answer key.

Get the 502 SQL questions, 9 euros

Or SQL, Python, pandas and NumPy together, 1,636 questions, 19 euros

Or play the free SQL sample, twenty questions in the browser, also as a free PDF.

Previous question Next question

Did this save you a lookup? Buy me a coffee on Ko-fi.