Finding duplicates efficiently in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find duplicate emails in a users table

Given a table users with columns id and email, which query returns all emails that appear more than once?

A SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1;

B SELECT email FROM users WHERE COUNT(email) > 1;

C SELECT email FROM users GROUP BY email WHERE COUNT(*) > 1;

D SELECT email FROM users HAVING COUNT(email) > 1 GROUP BY email;

💡 Hint

Use GROUP BY to group emails and HAVING to filter groups by count.

Option A correctly groups emails and filters groups with more than one occurrence using HAVING. Options B, C, and D misuse WHERE or HAVING clauses or their order.

❓ query_result

intermediate

Retrieve duplicate product names with their counts

Which query returns product names that appear more than once in the products table along with how many times they appear?

A SELECT name, COUNT(*) FROM products GROUP BY name HAVING COUNT(*) > 1;

B SELECT name, COUNT(*) FROM products GROUP BY name WHERE COUNT(*) > 1;

C SELECT name, COUNT(*) FROM products WHERE COUNT(name) > 1 GROUP BY name;

D SELECT name FROM products GROUP BY name HAVING COUNT(*) > 1;

💡 Hint

Use GROUP BY and HAVING to filter duplicates and COUNT(*) to get counts.

Option A correctly groups by name, counts occurrences, and filters groups with more than one. Option A misses the count column. Options A and B misuse WHERE or HAVING clauses.

📝 Syntax

advanced

Identify the correct query to find duplicate usernames

Which query is syntactically correct and returns usernames that appear more than once in the accounts table?

A SELECT username FROM accounts HAVING COUNT(username) > 1 GROUP BY username;

B SELECT username FROM accounts WHERE COUNT(username) > 1 GROUP BY username;

C SELECT username FROM accounts GROUP BY username WHERE COUNT(*) > 1;

D SELECT username FROM accounts GROUP BY username HAVING COUNT(*) > 1;

💡 Hint

Remember that HAVING filters groups after GROUP BY, while WHERE filters rows before grouping.

Only option D uses the correct syntax: GROUP BY followed by HAVING. Options A, C, and D misuse WHERE or HAVING clauses or their order.

❓ optimization

advanced

Optimize duplicate detection on large tables

You want to find duplicate email values in a very large subscribers table efficiently. Which approach is best?

A Scan the entire table without indexes using <code>GROUP BY email HAVING COUNT(*) > 1</code>.

B Use <code>SELECT DISTINCT email FROM subscribers</code> and then count duplicates in application code.

C Create an index on the <code>email</code> column and use <code>GROUP BY email HAVING COUNT(*) > 1</code>.

D Use a subquery with <code>WHERE email IN (SELECT email FROM subscribers GROUP BY email HAVING COUNT(*) > 1)</code> without indexes.

💡 Hint

Indexes speed up grouping and counting on large datasets.

Option C uses an index to speed up grouping and counting, making it efficient. Options B and D rely on scanning or application-side processing, which is slower. Option C scans without indexes, which is inefficient.

🧠 Conceptual

expert

Understanding duplicate detection with NULL values

Consider a table contacts with a nullable phone column. Which statement about finding duplicates in phone is true?

A GROUP BY phone causes an error if NULL values exist.

B GROUP BY phone treats all NULLs as one group, so duplicates of NULL are counted.

C GROUP BY phone ignores NULLs, so duplicates of NULL are never detected.

D GROUP BY phone treats each NULL as distinct, so duplicates of NULL are not counted.

💡 Hint

Think about how SQL treats NULLs in grouping.

In SQL, NULLs are treated as equal for GROUP BY purposes, so multiple NULLs form one group and are counted as duplicates if more than one. Therefore, option B is correct.