All sample questions / SQL

What does SELECT member, COUNT(*), COUNT(returned) FROM loans GROUP BY member ... return? SQL Grouping and Aggregation

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

CREATE TABLE loans (id INTEGER, member TEXT, book TEXT, returned TEXT);
INSERT INTO loans VALUES
  (1, 'sam', 'sql basics', 'yes'),
  (2, 'sam', 'sql advanced', NULL),
  (3, 'sam', 'joins', NULL),
  (4, 'tia', 'sql basics', 'yes');

SELECT member, COUNT(*), COUNT(returned) FROM loans GROUP BY member ORDER BY member

What does this query return?

  1. A. tia | 1 | 1 sam | 3 | 1
  2. B. sam | 3 | 1 tia | 1 | 1
  3. C. sam | 2 | 1 tia | 1 | 1
  4. D. sam | 3 | 3 tia | 1 | 1
Show the answer and why each option is right or wrong

Answer, B. sam | 3 | 1 tia | 1 | 1

A. tia | 1 | 1 sam | 3 | 1
The counts are right but the members are not alphabetically ordered, sam comes before tia.
B. sam | 3 | 1 tia | 1 | 1
Correct. Sam has three loans but only one with a non-NULL returned value, and tia has a single loan that is also returned.
C. sam | 2 | 1 tia | 1 | 1
This drops one of sam loans from COUNT(*), as if a row with a NULL somewhere in it were invisible to COUNT(*), but COUNT(*) counts rows regardless of any NULL inside them.
D. sam | 3 | 3 tia | 1 | 1
This assumes COUNT(returned) equals COUNT(*), but two of sam loans have a NULL returned value and get skipped by COUNT on that column.

COUNT(*) counts rows no matter what NULLs they contain, while COUNT (column) only counts rows where that specific column is not NULL. A row with a NULL in one column still fully counts toward COUNT(*).

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.