SQL Quiz: Distinct
15 questions · exam conditions
0:00
DistinctQuestion 1 of 15

Score has 1,2,2,3,3,NULL. COUNT(DISTINCT score) returns:

3
4
5
6
← Back to quizzes

SQL Quiz

SQL Quiz: Distinct

Practice Distinct in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Distinct, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

Score has 1,2,2,3,3,NULL. COUNT(DISTINCT score) returns:

  1. 3 (correct answer)
  2. 4
  3. 5
  4. 6
Explanation: COUNT(DISTINCT score) ignores NULLs, so only the non-null distinct values matter: 1, 2, and 3. That gives 3. The tempting mistake is to include NULL as a distinct value and answer 4, but NULL isn't counted by COUNT(DISTINCT).

Question 2

Which query returns the same rows as SELECT DISTINCT c FROM t?

  1. SELECT s FROM t GROUP BY s
  2. SELECT c FROM t GROUP BY c (correct answer)
  3. SELECT DISTINCT c,s FROM t
  4. SELECT c FROM t ORDER BY c
Explanation: Grouping by c collapses duplicate values into one row per distinct c, so it returns the same unique c values as SELECT DISTINCT c. The tempting trap is ORDER BY c: it sorts the rows but does not remove duplicates, so it returns extra rows. SELECT s or SELECT c,s also change the columns, so they do not match.

Question 3

SELECT DISTINCT dept, manager FROM emp; Rows are removed when they match on:

  1. Only the dept column
  2. Only the manager column
  3. The dept-manager pair (correct answer)
  4. Either dept or manager
Explanation: DISTINCT applies to the entire select list, so rows are removed only when both dept and manager match a previous row. The tempting mistake is thinking it deduplicates each column separately, but that would drop rows with the same dept even if managers differ. Only identical dept-manager pairs are collapsed.

Question 4

id is the PRIMARY KEY. For which query can DISTINCT still change results?

  1. SELECT DISTINCT id FROM t
  2. SELECT DISTINCT id,dept FROM t
  3. SELECT DISTINCT dept FROM t (correct answer)
  4. SELECT DISTINCT * FROM t
Explanation: Any query that includes the primary key id cannot have duplicate rows, so DISTINCT cannot change results. Only SELECT DISTINCT dept can merge rows that share the same dept. The tempting SELECT DISTINCT id, dept still includes id, so each row remains unique even though dept values repeat.

Question 5

Region has 40 NULLs, 30 'West', 30 'East'. SELECT DISTINCT region returns:

  1. 100 rows
  2. 2 rows
  3. 42 rows
  4. 3 rows (correct answer)
Explanation: Three distinct groups exist: NULL, 'West', and 'East', so DISTINCT returns 3 rows. SQL treats every NULL as the same value for DISTINCT, so all 40 NULLs collapse into one row. The tempting answer 2 rows ignores NULL, but NULL is still a distinct returned group.

Question 6

An account_status table stores status history. Account 10 has rows ('Active', '2026-01-01') and ('Suspended', '2026-03-01'). Account 20 has rows ('Active', '2026-02-01') and ('Active', '2026-04-01'). The columns shown after each account are (status, changed_at).

What is the key problem with using SELECT DISTINCT account_id, status FROM account_status; to return each account's latest status?

  1. It removes account 20 because both of its status values are Active
  2. It returns only the alphabetically greatest status for each account
  3. It keeps every history row because timestamps are implicitly selected
  4. It may return multiple statuses without choosing the latest timestamp (correct answer)
Explanation: When working with historical tables, always ask yourself: what does this query actually return, and does it guarantee the "latest" record? DISTINCT simply removes duplicate rows — it knows nothing about which row is most recent. Consider account 10: it has ('Active', '2026-01-01') and ('Suspended', '2026-03-01'). Since the status values differ, SELECT DISTINCT account_id, status returns both rows — giving you two statuses for one account, with no way to know which is current. This is exactly why D is correct: the query may return multiple statuses per account without ever consulting the timestamp to determine the latest one. The wrong answers reflect common misconceptions worth clearing up. A is incorrect because account 20 has two rows with status = 'Active' — those two rows are identical on the selected columns, so DISTINCT does collapse them into one. The account isn't removed; it's correctly deduplicated in this particular case. B is a fabricated behavior — DISTINCT applies no alphabetical or ranking logic whatsoever; it just suppresses exact duplicate rows. C is the opposite of reality: timestamps are not implicitly selected here, which is precisely the problem. Without changed_at in the query, you can't determine recency at all. The correct way to retrieve the latest status per account is to use ROW_NUMBER() with PARTITION BY account_id ORDER BY changed_at DESC, or a subquery with MAX(changed_at) joined back to the table. Study tip: Whenever a question involves "latest," "most recent," or "current" records, DISTINCT alone is never the right tool — you need an ordering or aggregation strategy.

Question 7

In employees, employee_id is a primary key. Consider this query:

SELECT DISTINCT e.employee_id, e.department_id FROM employees e JOIN badges b ON b.employee_id = e.employee_id;

Which additional constraint is sufficient to guarantee that removing DISTINCT never changes this query's result?

  1. badges.badge_id is unique for every badge record
  2. badges.employee_id is unique across all badge records (correct answer)
  3. employees.department_id references an existing department
  4. employees.employee_id remains unique and cannot be NULL
Explanation: When a JOIN produces duplicate rows, DISTINCT is doing real work — eliminating them. Your goal here is to find the constraint that makes duplicates impossible, so DISTINCT becomes redundant. Think about when duplicates arise in this query. You're selecting (employee_id, department_id) pairs after joining employees to badges. Since employee_id is already a primary key in employees, each employee has exactly one department_id. The only way a duplicate output row can appear is if a single employee matches multiple rows in badges — because each match produces another copy of that employee's (employee_id, department_id) pair. Option B is correct because making badges.employee_id unique across all badge records means each employee can appear in badges at most once. With at most one matching badge row per employee, the JOIN produces at most one output row per employee — so DISTINCT changes nothing. Option A is wrong because badge_id being unique says nothing about employee_id in badges. Multiple distinct badges could still belong to the same employee, causing duplicate output rows. Option C is wrong because a foreign key on employees.department_id only validates that departments exist — it has no effect on row duplication produced by the JOIN. Option D is wrong because employees.employee_id is already a primary key (unique and NOT NULL by definition), so this constraint adds nothing new and doesn't prevent one employee from having multiple badge rows. The strategy to remember: when DISTINCT is involved, ask yourself where duplicate rows originate. Trace the JOIN path — uniqueness on the joining column of the secondary table is usually what eliminates them.

Question 8

Customer 1 has two orders with status 'Shipped' and one order with status 'Pending'. Customer 2 has one order with status 'Shipped'. Each customer ID occurs once in customers.

What does the following query return?

SELECT DISTINCT c.customer_id, o.status FROM customers c JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = 'Shipped';

  1. Two rows: one Shipped row for each qualifying customer (correct answer)
  2. Three rows: one row for each qualifying order
  3. Four rows: one row for every order before filtering
  4. One row: a single Shipped status shared by both customers
Explanation: When you see both JOIN and DISTINCT in the same query, pause and think in two stages: first, what rows does the join produce? Second, what does DISTINCT collapse? Here, joining customers to orders on customer_id produces one row per matching order. Customer 1 has three orders (two Shipped, one Pending) and Customer 2 has one order (one Shipped). The WHERE o.status = 'Shipped' filter then removes the Pending row, leaving three rows: two Shipped rows for Customer 1 and one Shipped row for Customer 2. Now DISTINCT kicks in — it eliminates duplicate combinations of (customer_id, status). Customer 1's two Shipped rows are identical on those two columns, so they collapse into one. The final result is two rows: (1, 'Shipped') and (2, 'Shipped'), making A correct. Choice B is the most tempting trap — it reflects what the query would return without DISTINCT. Three rows exist after filtering, but DISTINCT merges the duplicates. Choice C ignores both the WHERE filter and DISTINCT, describing a raw join with no conditions applied — four rows if you count all three of Customer 1's orders plus Customer 2's one order. Choice D misreads DISTINCT as collapsing all rows into a single row, but DISTINCT removes duplicate combinations, not all uniqueness across customers. Your takeaway: always apply JOIN, then WHERE, then DISTINCT in that mental order. DISTINCT operates on the full selected column list as a unit — here (customer_id, status) together, not status alone.

Question 9

The priority values in tasks are 1, 1, 2, 2, 2, 3, and 4.

Which priorities are returned, in order, by this query?

SELECT DISTINCT priority FROM tasks ORDER BY priority OFFSET 1 ROW FETCH NEXT 2 ROWS ONLY;

  1. 1 and 2, because pagination is applied before duplicate removal
  2. 3 and 4, because repeated priorities are skipped by the offset
  3. 2 and 2, because two source rows are fetched after offsetting
  4. 2 and 3, because pagination follows distinctness and sorting (correct answer)
Explanation: When you see a query combining DISTINCT, ORDER BY, and OFFSET/FETCH, the key is understanding the logical order of operations: SQL processes DISTINCT and ORDER BY first, producing a clean, sorted result set, and then applies pagination (OFFSET/FETCH) to that final result. Here's how it plays out. The raw priority values are 1, 1, 2, 2, 2, 3, 4. After SELECT DISTINCT, duplicates are removed, leaving: 1, 2, 3, 4. After ORDER BY priority, the order is confirmed as 1, 2, 3, 4. Now pagination applies: OFFSET 1 ROW skips the first row (priority 1), and FETCH NEXT 2 ROWS ONLY returns the next two — priorities 2 and 3. That makes D the correct answer. A is wrong because it claims pagination happens before duplicate removal. That's backwards — DISTINCT is resolved before OFFSET/FETCH ever executes. B incorrectly suggests the offset skips repeated values in the source data rather than rows in the final deduplicated result. C confuses the source rows with the result rows; FETCH NEXT 2 ROWS ONLY pulls 2 rows from the distinct, sorted result set, not raw source rows — so you'd never see duplicate 2s here. A useful mental model: think of OFFSET/FETCH as a window you slide over the finished result set — whatever SELECT, DISTINCT, and ORDER BY would return on their own — not over raw table data. Always resolve the full logical query first, then paginate.

Question 10

The customers table contains (customer_id 1, name 'Jordan'), (customer_id 2, name 'Jordan'), and (customer_id 3, name 'Casey'). Customer 1 has two orders, customer 2 has one order, and customer 3 has no orders.

How many rows are returned by SELECT DISTINCT c.name FROM customers c JOIN orders o ON o.customer_id = c.customer_id;?

  1. One row, because all qualifying customers have the name Jordan (correct answer)
  2. Two rows, because two customer records have qualifying orders
  3. Three rows, because three qualifying order rows are joined
  4. Four rows, because every customer and order contributes once
Explanation: When you see a JOIN combined with DISTINCT, you need to think in two separate stages: first, what rows does the JOIN produce, and second, what does DISTINCT do to those results? Start with the JOIN. An inner JOIN between customers and orders only returns rows where a match exists. Customer 3 (Casey) has no orders, so Casey is eliminated entirely. Customer 1 (Jordan) has two orders, producing two joined rows. Customer 2 (also Jordan) has one order, producing one joined row. So before DISTINCT, you have three rows — all with the name 'Jordan'. Now apply SELECT DISTINCT c.name. DISTINCT collapses duplicate values in the selected column. All three rows share the same name value, 'Jordan', so they collapse into a single row. The query returns one row. Answer A is correct for exactly this reason — only one unique name survives after both JOIN filtering and DISTINCT deduplication. Answer B is tempting because two customer records have orders, but DISTINCT operates on the output values, not the source rows. Both qualifying customers share the name 'Jordan', so they collapse to one. Answer C mistakes the pre-DISTINCT row count (three joined rows) for the final result, forgetting that DISTINCT is applied before anything is returned. Answer D is simply fabricated logic — there's no SQL behavior that multiplies customers and orders together this way. Study tip: Always trace a query in two mental steps — what does the JOIN produce, then what does SELECT (with any modifiers like DISTINCT) do to that result set?

Question 11

The code column contains ' Ab ', 'ab', 'AB', 'a b', NULL, and NULL. Assume LOWER converts letters to lowercase and TRIM removes leading and trailing spaces.

How many rows are returned by SELECT DISTINCT LOWER(TRIM(code)) FROM items;?

  1. Two rows, because all non-NULL codes normalize identically
  2. Three rows, representing ab, a b, and NULL (correct answer)
  3. Four rows, representing each original non-NULL spelling
  4. Five rows, because only the two NULL rows combine
Explanation: When a question combines DISTINCT, NULL, and string functions, you need to trace each value through the transformations before counting distinct results. Start with the six raw values: ' Ab ', 'ab', 'AB', 'a b', NULL, NULL. Apply TRIM first to strip leading/trailing spaces, giving 'Ab', 'ab', 'AB', 'a b', NULL, NULL. Then apply LOWER, which converts letters but leaves spaces and NULLs untouched: 'ab', 'ab', 'ab', 'a b', NULL, NULL. Now DISTINCT collapses duplicates — the three 'ab' values merge into one, the two NULLs merge into one (SQL treats NULLs as duplicates for DISTINCT purposes), and 'a b' stands alone. That leaves exactly three distinct rows: 'ab', 'a b', and NULL, confirming B is correct. A is wrong because it claims all non-NULL codes normalize identically — but 'a b' has an internal space that TRIM does not remove, so it stays distinct from 'ab'. C is wrong because it suggests the original spellings survive transformation. The functions do collapse ' Ab ', 'ab', and 'AB' into one value, so four distinct rows is an overcount. D is wrong because it misunderstands how DISTINCT handles NULLs. DISTINCT does deduplicate NULLs into a single output row, not five rows. As a study habit, always walk through SQL transformations in pipeline order — function output feeds into DISTINCT — and remember that TRIM only targets edge spaces, never internal ones.

Question 12

After grouping the tickets table by queue_id, the three queues contain 2, 2, and 3 tickets, respectively.

How many rows are returned by SELECT DISTINCT COUNT(*) AS ticket_count FROM tickets GROUP BY queue_id;?

  1. One row, because COUNT(*) is a single aggregate expression
  2. Two rows, containing the distinct counts 2 and 3 (correct answer)
  3. Three rows, because the grouping creates three queue groups
  4. Seven rows, because all source tickets contribute to counts
Explanation: When a query uses both GROUP BY and DISTINCT with an aggregate function, you need to think in two stages: what does GROUP BY produce first, and then what does DISTINCT filter down to? Here, GROUP BY queue_id runs first and produces three intermediate rows — one per queue — with COUNT(*) values of 2, 2, and 3. Then DISTINCT collapses duplicate values in that result set. Since two queues both have a count of 2, those two rows reduce to one. The queue with count 3 stays as its own row. You're left with two distinct rows: one showing ticket_count = 2 and one showing ticket_count = 3, confirming B is correct. A is wrong because COUNT(*) being a single aggregate expression doesn't override GROUP BY — grouping still produces multiple rows before DISTINCT is applied. The confusion here is conflating "one expression" with "one output row." C would be correct without DISTINCT — yes, three groups produce three rows, but DISTINCT deduplicates the results afterward, collapsing the two identical counts of 2 into one. D misunderstands what COUNT(*) returns. The query never returns individual ticket rows; it returns aggregated counts per group. The source tickets (7 total) are consumed by the aggregation and never directly appear as output rows. Study tip: Train yourself to mentally execute SQL in clause order — FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY. DISTINCT runs after aggregation, so always ask "what rows exist at that point?"

Question 13

The orders table contains these six projected value pairs in order: (1, 'East'), (1, 'East'), (1, 'West'), (2, 'East'), (2, 'East'), and (2, 'West').

How many rows are returned by SELECT DISTINCT customer_id, region FROM orders;?

  1. Two rows, because only two distinct customer IDs appear
  2. Three rows, because only three region occurrences remain
  3. Four rows, because four distinct value pairs appear (correct answer)
  4. Six rows, because each source row is evaluated separately
Explanation: When you see SELECT DISTINCT applied to multiple columns, remember that distinctness applies to the entire combination of those columns — not to any single column in isolation. SQL evaluates each unique pairing of values across all listed columns together. Working through the six pairs — (1, 'East'), (1, 'East'), (1, 'West'), (2, 'East'), (2, 'East'), (2, 'West') — you can identify the unique combinations: (1, 'East'), (1, 'West'), (2, 'East'), and (2, 'West'). That's four distinct pairs, making C correct. The duplicate (1, 'East') and duplicate (2, 'East') rows are each collapsed to one, reducing six rows down to four. Choice A is wrong because DISTINCT doesn't deduplicate on customer_id alone. You're selecting two columns, so SQL considers both. Choosing only the two unique customer IDs would require SELECT DISTINCT customer_id with no region column. Choice B incorrectly counts three distinct region occurrences — but "East" and "West" are only two distinct region values, and three occurrences of "West" or "East" don't appear either way. This answer reflects confusion about what's being counted. Choice D describes the behavior of a plain SELECT without DISTINCT — without that keyword, yes, all six source rows would be returned individually. A useful rule of thumb: when DISTINCT follows SELECT, mentally list every full row combination the query projects, then remove duplicates. The number of unique complete rows is your answer — regardless of how many columns are involved.

Question 14

The email column contains five values: 'a@example.com', 'a@example.com', NULL, NULL, and 'b@example.com'.

Which result pair is produced by these two scalar queries, in the order shown?

SELECT COUNT(DISTINCT email) FROM contacts;

SELECT COUNT(*) FROM (SELECT DISTINCT email FROM contacts) d;

  1. First query returns 2; second query returns 3 (correct answer)
  2. First query returns 3; second query returns 3
  3. First query returns 2; second query returns 2
  4. First query returns 3; second query returns 5
Explanation: When working with COUNT(DISTINCT ...) versus SELECT DISTINCT, the critical distinction is how each handles NULL values — and this question is designed specifically to test that. COUNT(DISTINCT email) ignores NULLs entirely before deduplicating. From your five values — 'a@example.com', 'a@example.com', NULL, NULL, 'b@example.com' — the NULLs are discarded first, leaving 'a@example.com' and 'b@example.com'. After deduplication, that's 2 distinct non-null values, so the first query returns 2. The second query does something subtly different. SELECT DISTINCT email deduplicates all values, including NULLs. In SQL, DISTINCT treats all NULLs as a single group, so your five rows collapse into three distinct "values": 'a@example.com', 'b@example.com', and NULL. The outer COUNT(*) then counts rows — not column values — so NULLs are included in the row count, returning 3. That makes A the correct answer. B is wrong because it assumes both queries behave identically, giving 3 each — but COUNT(DISTINCT ...) never counts NULLs. C is wrong for the opposite reason: it assumes both queries exclude NULLs, but COUNT(*) counts every row in the subquery, including the NULL row. D is nonsensical — neither query can return 5, since deduplication always reduces the row count. As a study tip, remember this pattern: COUNT(column) always skips NULLs, but COUNT(*) never does. When a subquery surfaces NULLs as rows, wrapping it in COUNT(*) will count them.

Question 15

The selected columns (a, b) have these values: (NULL, 'X'), (NULL, 'X'), (NULL, 'Y'), (1, NULL), (1, NULL), (1, 'X'), and (1, 'X').

How many rows are returned by SELECT DISTINCT a, b FROM samples;?

  1. Three rows, because every row containing NULL is discarded
  2. Five rows, because NULL values are always kept as unique by DISTINCT
  3. Four rows, because four distinct projected pairs remain (correct answer)
  4. Seven rows, because each NULL or repeated row remains separate
Explanation: When you see SELECT DISTINCT applied to multiple columns, remember that SQL treats the combination of values as the unit of uniqueness — not each column independently. The key nuance here is how DISTINCT handles NULL: SQL considers two NULLs as duplicates of each other for the purpose of DISTINCT (even though NULL = NULL evaluates to UNKNOWN in a WHERE clause). Let's walk through the seven rows and identify the unique (a, b) pairs:
  • (NULL, 'X') — appears twice → counts as one distinct row
  • (NULL, 'Y') — appears once → one distinct row
  • (1, NULL) — appears twice → counts as one distinct row
  • (1, 'X') — appears twice → counts as one distinct row
That gives you four distinct pairs, confirming answer C is correct. Answer A is wrong because it claims rows with NULL are discarded entirely. DISTINCT never discards rows based on NULL presence — it deduplicates them just like any other value. Answer B is wrong in the opposite direction: it claims each NULL is treated as unique, so duplicate (NULL, 'X') rows would both survive. That's not how DISTINCT works — identical pairs (even those containing NULL) are collapsed. Answer D is wrong because it confuses DISTINCT with a plain SELECT, which would indeed return all seven original rows without any deduplication. A helpful rule of thumb: for DISTINCT, think of NULL as a regular value for deduplication purposes only. Two rows with identical NULL-containing combinations will be collapsed into one.