SQL Quiz: Identifying Duplicates
10 questions · exam conditions
0:00
Identifying DuplicatesQuestion 1 of 10

A customer table contains three rows whose email value is NULL, two rows whose email is alex@example.com, and otherwise unique non-null emails. A missing email is allowed and does not indicate that two customers are the same.

Which query correctly reports duplicated known email addresses without treating missing emails as duplicates?

SELECT email FROM customers GROUP BY email HAVING COUNT(*) > 1;
SELECT email FROM customers WHERE email IS NOT NULL GROUP BY email HAVING COUNT(*) > 1;
SELECT email FROM customers GROUP BY email HAVING COUNT(email) > 1 OR email IS NULL;
SELECT DISTINCT email FROM customers WHERE email IS NOT NULL HAVING COUNT(*) > 1;
← Back to quizzes

SQL Quiz

SQL Quiz: Identifying Duplicates

Practice Identifying Duplicates 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 Identifying Duplicates, 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

A customer table contains three rows whose email value is NULL, two rows whose email is alex@example.com, and otherwise unique non-null emails. A missing email is allowed and does not indicate that two customers are the same.

Which query correctly reports duplicated known email addresses without treating missing emails as duplicates?

  1. SELECT email FROM customers GROUP BY email HAVING COUNT(*) > 1;
  2. SELECT email FROM customers WHERE email IS NOT NULL GROUP BY email HAVING COUNT(*) > 1; (correct answer)
  3. SELECT email FROM customers GROUP BY email HAVING COUNT(email) > 1 OR email IS NULL;
  4. SELECT DISTINCT email FROM customers WHERE email IS NOT NULL HAVING COUNT(*) > 1;
Explanation: When filtering for duplicate values in SQL, you need to think carefully about two separate concerns: what gets grouped, and what gets counted. NULL values deserve special attention because SQL treats NULL as "unknown" — two NULLs are not considered equal to each other, which means they shouldn't be flagged as duplicates in a business context like this one. Option B is correct because it explicitly excludes NULL emails before grouping with WHERE email IS NOT NULL. This ensures the GROUP BY only operates on known email addresses, and the HAVING COUNT(*) > 1 condition then correctly surfaces any email appearing more than once — in this case, alex@example.com. Option A looks nearly identical but omits the WHERE filter. In practice, most SQL engines group all NULLs together into a single bucket, meaning the three NULL rows would appear as a group with count 3 — falsely flagging missing emails as duplicates, which violates the business rule stated in the passage. Option C compounds the problem by explicitly adding OR email IS NULL to the HAVING clause, which intentionally returns NULL as a "duplicate." This is the opposite of what the question asks for. Option D is syntactically broken. HAVING requires an accompanying GROUP BY clause when used in standard SQL — you cannot apply HAVING COUNT(*) > 1 without grouping first. SELECT DISTINCT does not substitute for GROUP BY, so this query would produce an error or undefined behavior. A good rule of thumb: whenever a column can contain NULLs and you're grouping for duplicates, always add a WHERE column IS NOT NULL filter before the GROUP BY to keep NULL values out of your aggregate logic entirely.

Question 2

A query expected to return one row per employee joins employees to employee_skills. It initially returns multiple rows for employees with several skills. A developer changes the projection to SELECT DISTINCT e.employee_id, e.employee_name, after which one row appears per employee.

Which conclusion about the revised query is most accurate?

  1. It proves the join is one-to-one because duplicate employee projections no longer appear.
  2. It removes projected duplicates but does not verify whether the join produced unexpected multiplicity. (correct answer)
  3. It removes duplicate skill records before the join, thereby restoring the employee table's grain.
  4. It guarantees correct aggregates because each employee identifier is now returned only once.
Explanation: Whenever you see a question about SELECT DISTINCT and joins, ask yourself: what is DISTINCT actually doing, and what is it hiding? Understanding the difference between fixing a problem and masking it is the core concept being tested here. When employees joins employee_skills, an employee with three skills produces three rows — one per skill. This is called fan-out, and it's the join behaving exactly as designed. Adding DISTINCT on just employee_id and employee_name collapses those three rows into one by deduplicating the projected columns. The underlying join still produced three rows; you're simply suppressing that detail in the output. That's why B is correct — the revised query removes projected duplicates but gives you no information about whether the join's multiplicity was intentional or a data problem. A is wrong because seeing one row per employee in the output does not confirm a one-to-one join. The join was always many-to-one (many skills per employee); DISTINCT just hides that fact from the result set. C is wrong because DISTINCT operates after the join in the logical query order — it cannot remove duplicate skill records before the join happens. D is wrong and is a particularly dangerous misconception: if you were SUMming a salary column, DISTINCT on employee columns alone would not prevent that salary from being summed multiple times across skill rows. Correct aggregates require fixing the query logic, not just deduplicating the projection. Study tip: When you see DISTINCT used to "fix" unexpected row counts after a join, treat it as a red flag — it often conceals a grain problem rather than solving it.

Question 3

An analyst needs to inspect every transaction row whose combination of account_id and reference_code occurs more than once. The output must retain row-level columns such as transaction_id, amount, and created_at.

Which query correctly identifies the duplicate occurrences while retaining row-level detail?

  1. SELECT account_id, reference_code, COUNT(*) FROM transactions GROUP BY account_id, reference_code HAVING COUNT(*) > 1; (aggregates each group, losing row-level columns)
  2. SELECT t.* FROM transactions t WHERE COUNT(*) OVER (PARTITION BY account_id, reference_code) > 1; (places a window function directly in WHERE, which is not permitted in a single query block)
  3. SELECT * FROM (SELECT t.*, COUNT(*) OVER (PARTITION BY account_id) AS occurrence_count FROM transactions t) x WHERE occurrence_count > 1; (partitions only by account, flagging accounts with any multiple transactions)
  4. SELECT * FROM (SELECT t.*, COUNT(*) OVER (PARTITION BY account_id, reference_code) AS occurrence_count FROM transactions t) x WHERE occurrence_count > 1; (correct answer)
Explanation: When you need to flag duplicate rows while keeping all row-level columns intact, the right tool is a window function inside a subquery — not a GROUP BY aggregate, which collapses rows into summaries. The correct approach, used in D, computes COUNT(*) OVER (PARTITION BY account_id, reference_code) in an inner query, which attaches a count to every individual row without collapsing them. The outer WHERE occurrence_count > 1 then filters to only those rows belonging to a duplicated combination. This preserves transaction_id, amount, created_at, and everything else — exactly what the problem requires. A fails because GROUP BY with HAVING COUNT(*) > 1 reduces each group to a single summary row. You lose all row-level detail like transaction_id and amount — you only learn which combinations are duplicated, not which individual transactions those are. B is syntactically illegal. Window functions cannot appear directly in a WHERE clause within the same query block. SQL evaluates WHERE before window functions are computed, so the database has no count to filter on yet. You must wrap the window function in a subquery first, which is exactly what D does. C is a logical error, not a syntax error. It partitions by account_id alone, so it flags every row belonging to any account that has more than one transaction — regardless of reference_code. This over-selects rows that aren't true duplicates of the combined key. Study tip: Whenever a question asks you to filter on an aggregate or window result, remember the two-step pattern: compute the value in a subquery (or CTE), then filter in the outer query. Window functions in WHERE is always a trap.

Question 4

An orders table has one row per order. An order_lines table has three rows for order 410, and a payments table has two rows for the same order. A developer joins both child tables directly to orders using order_id. The resulting query returns six rows for order 410.

Which explanation best identifies the cause of the unexpected row multiplicity?

  1. The join produces every line-payment combination because both child tables have multiple rows for the order. (correct answer)
  2. The join repeats each line once because the parent order lacks a unique constraint on order_id.
  3. The join produces six rows because unmatched child rows are retained by implicit outer-join behavior.
  4. The join repeats each payment three times because SQL evaluates the payments table before the lines table.
Explanation: Whenever you see a question about joining multiple child tables to the same parent, think about how SQL builds its result set: every matching row from one table gets paired with every matching row from the other. This is the core of what's being tested here. When you join orders to order_lines, order 410 produces 3 rows. When you then join payments to that same result, each of those 3 rows gets matched against each of the 2 payment rows — yielding 3 × 2 = 6 rows. This is a Cartesian product between the two child tables, scoped to the shared order_id. Answer A correctly identifies this: the multiplicity comes from every line being paired with every payment, not from anything broken about the schema. B is wrong because unique constraints on the parent's order_id are irrelevant here — the parent table has exactly one row for order 410. The duplication comes from the child tables, not the parent. C is wrong because outer joins retain unmatched rows; this scenario involves fully matched rows producing excess combinations, which is a fan trap, not an outer-join behavior. D is wrong on two counts: SQL doesn't evaluate tables in a guaranteed sequence that changes row counts, and the multiplier effect happens symmetrically — lines are repeated for each payment just as much as payments are repeated for each line. As a study tip, remember the fan trap: joining two independent child tables through a common parent causes row multiplication equal to the product of their counts. Always aggregate child tables before joining, or use subqueries, to avoid this classic pitfall.

Question 5

Two staging tables, web_customers and store_customers, may both contain the same customer_id, and either table may also contain repeated occurrences internally. An analyst must identify every customer identifier occurring more than once across the combined inputs.

Which query preserves the occurrences needed for this duplicate check?

  1. SELECT customer_id FROM (SELECT customer_id FROM web_customers UNION SELECT customer_id FROM store_customers) s GROUP BY customer_id HAVING COUNT(*) > 1;
  2. SELECT DISTINCT customer_id FROM (SELECT customer_id FROM web_customers UNION ALL SELECT customer_id FROM store_customers) s;
  3. SELECT customer_id FROM (SELECT customer_id FROM web_customers UNION ALL SELECT customer_id FROM store_customers) s GROUP BY customer_id HAVING COUNT(*) > 1; (correct answer)
  4. SELECT customer_id FROM web_customers INTERSECT SELECT customer_id FROM store_customers;
Explanation: When checking for duplicates across combined datasets, the key question is: does my query preserve every raw occurrence before I count? The answer hinges on the difference between UNION and UNION ALL. UNION ALL stacks both tables together without removing anything — if customer_id = 101 appears twice in web_customers and once in store_customers, you get three rows. That's exactly what you need before grouping and filtering with HAVING COUNT(*) > 1. Option C does precisely this: it uses UNION ALL in the subquery to combine all rows, then groups by customer_id and returns only those appearing more than once across the combined set. That's your duplicate check working correctly. Option A is the most tempting trap. It looks structurally similar to C, but uses UNION instead of UNION ALL. UNION deduplicates rows before the outer query ever runs, so a customer appearing in both tables gets collapsed to a single row — and HAVING COUNT(*) > 1 will never fire for cross-table duplicates. This silently kills the very behavior you're testing for. Option B applies SELECT DISTINCT on top of a UNION ALL subquery. It returns unique customer IDs that appear at all, not those appearing more than once — the opposite of a duplicate check. Option D uses INTERSECT, which returns IDs present in both tables but ignores internal duplicates within a single table entirely. Strategy tip: Whenever a question involves counting occurrences, always ask whether your set operation preserves or removes rows before counting. UNION ALL = keep everything; UNION = deduplicate first. Confusing the two is one of the most common SQL mistakes on this exam.

Question 6

An account-status history table contains account_id, status_time, event_id, and status. A query uses RANK() OVER (PARTITION BY account_id ORDER BY status_time DESC) and retains rows with rank 1. Some accounts unexpectedly appear twice because two events have the same latest timestamp. event_id is unique and larger values represent later ingestion.

Which change guarantees one reproducible latest row per account while preserving the stated tie-breaking rule?

  1. Use DENSE_RANK() ordered by status_time DESC, event_id DESC and retain rank 1.
  2. Use ROW_NUMBER() ordered by status_time DESC, event_id DESC and retain row number 1. (correct answer)
  3. Use ROW_NUMBER() ordered only by status_time DESC and retain row number 1.
  4. Use RANK() ordered only by event_id DESC and retain rank 1.
Explanation: When dealing with window functions and deduplication, the key question to ask is: which function guarantees exactly one row per partition, regardless of ties? RANK() and DENSE_RANK() both assign the same rank to tied rows, meaning ties always produce duplicates. Only ROW_NUMBER() assigns a strictly unique sequential number within each partition — even when rows are otherwise identical — making it the right tool when you need exactly one result per group. The correct answer is B. By using ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY status_time DESC, event_id DESC), you first prioritize the most recent timestamp, then break any ties by selecting the event with the highest event_id (latest ingestion). Because event_id is unique, the ordering is fully deterministic, and ROW_NUMBER() is guaranteed to return exactly one row per account — reproducibly. A is wrong because DENSE_RANK() still assigns the same rank to tied rows. Adding event_id DESC to the ORDER BY actually fixes the tie-breaking, but DENSE_RANK() with a fully deterministic sort behaves identically to ROW_NUMBER() only in theory — the real problem is that DENSE_RANK() is the wrong conceptual choice here and misleads your intent. More practically, if two rows share the same status_time and event_id (even hypothetically), it would still duplicate. C fails because ordering only by status_time DESC leaves ties unbroken — ROW_NUMBER() will still pick one row, but non-deterministically (results may vary across runs). D abandons status_time entirely, violating the stated rule of selecting the latest status time. Remember: whenever you need exactly one row per group with a tiebreaker, reach for ROW_NUMBER() with a fully deterministic ORDER BY — unique columns like IDs make ideal final tiebreakers.

Question 7

A fulfillment table is expected to contain at most one row for each combination of order_id, sku, and warehouse_id. The same SKU may legitimately appear more than once in an order when different warehouses fulfill it.

Which query identifies violations of the table's expected grain without flagging legitimate warehouse splits?

  1. SELECT order_id, sku FROM fulfillment GROUP BY order_id, sku HAVING COUNT(*) > 1;
  2. SELECT order_id, warehouse_id FROM fulfillment GROUP BY order_id, warehouse_id HAVING COUNT(*) > 1;
  3. SELECT order_id, sku, warehouse_id FROM fulfillment GROUP BY order_id, sku, warehouse_id HAVING COUNT(*) > 1; (correct answer)
  4. SELECT sku, warehouse_id FROM fulfillment GROUP BY sku, warehouse_id HAVING COUNT(DISTINCT order_id) > 1;
Explanation: When working with grain violations in SQL, your first step is to precisely define what the grain is — the combination of columns that should uniquely identify each row. Here, the stated grain is (order_id, sku, warehouse_id) together. A violation means that exact combination appears more than once. Answer C groups by all three columns — order_id, sku, and warehouse_id — and flags any group with more than one row. This is exactly right because it tests the full defined grain. If the same warehouse shipped the same SKU on the same order twice, that's the actual duplicate you're hunting for. Answer A groups only by order_id and sku, ignoring warehouse_id. This will flag legitimate warehouse splits as false violations — precisely what the question tells you to avoid. If warehouse A and warehouse B both fulfill SKU-101 on order-99, that's one row each and perfectly valid, but answer A would count that as a duplicate. Answer B groups by order_id and warehouse_id, dropping sku entirely. This would incorrectly flag orders where a single warehouse ships multiple different SKUs — a completely normal and expected scenario that has nothing to do with grain violations. Answer D groups by sku and warehouse_id without order_id, and counts distinct orders. This identifies common combinations across many orders, which is routine business data, not a grain violation at all. The study tip here: always match your GROUP BY columns exactly to the declared grain when checking for duplicates. Any missing column makes your check too loose; any extra column makes it too strict.

Question 8

A report left-joins customers to orders, then left-joins each order to order_items. An order may contain many items. The report must identify customers who have more than one distinct order, regardless of the number of items in those orders.

Which HAVING condition correctly detects the required customer-level multiplicity?

  1. HAVING COUNT(*) > 1, because each resulting row represents a customer order
  2. HAVING COUNT(o.order_id) > 1, because null order identifiers are excluded
  3. HAVING COUNT(DISTINCT i.item_id) > 1, because items establish order multiplicity
  4. HAVING COUNT(DISTINCT o.order_id) > 1, because item expansion is ignored (correct answer)
Explanation: When a query joins multiple tables with one-to-many relationships, rows get multiplied. Here, each order fans out into multiple item rows, so a single order with three items produces three rows — you can't simply count rows to count orders. The goal is counting distinct orders per customer, which means you need COUNT(DISTINCT o.order_id). This collapses the item-level expansion back to the order level, correctly measuring how many unique orders a customer placed. Answer D is right for exactly this reason: it ignores the item duplication and counts only distinct order identifiers. Answer A fails because COUNT(*) counts every row in the result set, including duplicate rows created by the order_items join. A customer with one order containing five items would produce a count of 5, falsely signaling "multiple orders." Answer B improves slightly by using COUNT(o.order_id) — which does exclude NULLs from the left join — but it still counts all non-null order ID occurrences, not distinct ones. One order with five items still yields a count of 5. Answer C uses COUNT(DISTINCT i.item_id), which measures item variety, not order multiplicity. A customer with one order containing two items would satisfy > 1, even though they only have a single order — the opposite of what the report needs. The study tip here: whenever you see layered one-to-many joins, ask yourself "at what level am I measuring?" If rows have been multiplied by a downstream join, reach for COUNT(DISTINCT parent_id) to collapse back to the level you actually care about.

Question 9

For duplicate detection, the business treats email addresses as identical after removing leading and trailing spaces and ignoring letter case. Missing email addresses are permitted and must not be reported.

Which query identifies duplicate groups under this business rule?

  1. SELECT LOWER(email) FROM users WHERE email IS NOT NULL GROUP BY LOWER(email) HAVING COUNT(*) > 1;
  2. SELECT TRIM(email) FROM users WHERE email IS NOT NULL GROUP BY TRIM(email) HAVING COUNT(*) > 1;
  3. SELECT LOWER(TRIM(email)) FROM users WHERE email IS NOT NULL GROUP BY LOWER(TRIM(email)) HAVING COUNT(*) > 1; (correct answer)
  4. SELECT email FROM users WHERE LOWER(TRIM(email)) IS NOT NULL GROUP BY email HAVING COUNT(*) > 1;
Explanation: When a business rule defines "duplicate" using multiple transformations, your query must apply all of them consistently — in the SELECT, GROUP BY, and any filtering logic. Here, the rule requires both removing whitespace and ignoring case, so you need LOWER(TRIM(email)) everywhere. Option C does exactly this: it filters out NULLs with WHERE email IS NOT NULL, groups records by LOWER(TRIM(email)) so that " Alice@Mail.com" and "alice@mail.com" collapse into the same bucket, and then uses HAVING COUNT(*) > 1 to surface only the duplicates. Every part of the query speaks the same language as the business rule. Option A applies only LOWER(), so two records with " alice@mail.com" and "alice@mail.com" (differing by a leading space) would be treated as different emails — the trimming step is missing. Option B applies only TRIM(), meaning "Alice@mail.com" and "alice@mail.com" would be counted separately — the case-insensitivity step is missing. Option D is the sneakiest trap: it groups by the raw email column rather than the normalized form, so two naturally distinct strings that normalize to the same value will never be grouped together, defeating the entire purpose of the business rule. The WHERE LOWER(TRIM(email)) IS NOT NULL clause there is also redundant — it's just a convoluted way of filtering NULLs. As a study habit, whenever you see a multi-condition normalization rule, mentally checklist each transformation and verify it appears in both SELECT and GROUP BY. A mismatch between those two is the most common trap in these questions.

Question 10

A profiles table has a unique profile_id plus nullable email and phone columns. Two profiles are considered duplicate candidates when both email values match, including two nulls, and both phone values match, including two nulls. The result must return each candidate pair once and must never pair a row with itself.

Which self-join condition satisfies all requirements?

  1. a.profile_id < b.profile_id AND (a.email = b.email OR a.email IS NULL AND b.email IS NULL) AND (a.phone = b.phone OR a.phone IS NULL AND b.phone IS NULL) (correct answer)
  2. a.profile_id <> b.profile_id AND (a.email = b.email OR a.email IS NULL AND b.email IS NULL) AND (a.phone = b.phone OR a.phone IS NULL AND b.phone IS NULL)
  3. a.profile_id < b.profile_id AND a.email = b.email AND a.phone = b.phone
  4. a.profile_id <= b.profile_id AND (a.email = b.email OR a.email IS NULL AND b.email IS NULL) AND (a.phone = b.phone OR a.phone IS NULL AND b.phone IS NULL)
Explanation: When writing a self-join to find duplicate pairs, you need to solve three distinct problems simultaneously: prevent a row from pairing with itself, prevent counting the same pair twice, and handle NULL values correctly in your matching logic. Start with NULL handling. In SQL, NULL = NULL evaluates to NULL (not TRUE), so a plain equality check silently drops rows where both columns are null. To treat two NULLs as a match, you need the pattern (a.col = b.col OR a.col IS NULL AND b.col IS NULL). This is exactly what answer A applies to both email and phone. Answer A also uses a.profile_id < b.profile_id to handle the pairing logic. This strict less-than ensures each pair appears exactly once (row 3 with row 7, never row 7 with row 3) and automatically prevents a row from joining with itself (since no ID is less than itself). That makes A correct. Answer B swaps < for <> (not equal), which does prevent self-pairing, but it generates every pair twice — once as (A, B) and again as (B, A). Answer C uses < correctly for deduplication but drops the NULL-safe logic, meaning two profiles where both emails are NULL and both phones are NULL would not be flagged as duplicates. Answer D uses <=, which allows a row to join with itself (when a.profile_id = b.profile_id), violating the "never pair a row with itself" rule. A useful pattern to remember: for deduplicating self-join pairs, < is almost always what you want — <> duplicates pairs, and <= includes self-matches.