Finding duplicates efficiently in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select duplicate names from the users table.

SQL

SELECT name, COUNT(*) FROM users GROUP BY [1] HAVING COUNT(*) > 1;

Drag options to blanks, or click blank then click option'

Aemail

Bid

Cname

Dage

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by a unique column like id will never find duplicates.

Forgetting to use HAVING COUNT(*) > 1 to filter duplicates.

We group by the name column to find duplicates of names.

2fill in blank

medium

Complete the code to find duplicate emails in the customers table.

SQL

SELECT email, COUNT(*) FROM customers GROUP BY [1] HAVING COUNT(*) > 1;

Drag options to blanks, or click blank then click option'

Aemail

Bcustomer_id

Cphone

Daddress

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by a unique identifier like customer_id will not find duplicates.

Not using HAVING clause to filter groups with more than one row.

Grouping by email helps find duplicate email addresses.

3fill in blank

hard

Fix the error in the code to find duplicate product codes in the inventory table.

SQL

SELECT product_code, COUNT(*) FROM inventory GROUP BY [1] HAVING COUNT(*) > 1;

Drag options to blanks, or click blank then click option'

Aproduct_id

Bquantity

Cprice

Dproduct_code

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by product_id instead of product_code.

Not using HAVING COUNT(*) > 1 to filter duplicates.

The GROUP BY must be on product_code to find duplicates of product codes.

4fill in blank

hard

Fill both blanks to find duplicate usernames and count them in the accounts table.

SQL

SELECT [1], COUNT(*) FROM accounts GROUP BY [2] HAVING COUNT(*) > 1;

Drag options to blanks, or click blank then click option'

Ausername

Buser_id

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting username but grouping by user_id or email.

Grouping by a unique column will not find duplicates.

We select and group by username to find duplicates of usernames.

5fill in blank

hard

Fill all three blanks to find duplicate order numbers and their counts in the orders table.

SQL

SELECT [1], COUNT(*) FROM orders GROUP BY [2] HAVING COUNT(*) [3] 1;

Drag options to blanks, or click blank then click option'

Aorder_number

B>

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using HAVING COUNT(*) = 1 which finds unique values, not duplicates.

Grouping by a different column than selected.

We select and group by order_number and filter groups having count greater than 1 to find duplicates.

Practice

1. What is the main purpose of using GROUP BY with HAVING COUNT(*) > 1 in SQL?

easy

A. To sort the table alphabetically

B. To find duplicate values in a column

C. To delete rows from a table

D. To create a new table

Solution

1. Step 1: Understand GROUP BY usage

   GROUP BY groups rows that have the same value in specified columns.

2. Step 2: Use HAVING COUNT(*) > 1 to filter groups

   HAVING COUNT(*) > 1 keeps only groups with more than one row, meaning duplicates.

3. Final Answer:

   To find duplicate values in a column -> Option B

4. Quick Check:

   GROUP BY + HAVING COUNT(*) > 1 = Find duplicates [OK]

Hint: Use GROUP BY with HAVING COUNT(*) > 1 to spot duplicates fast [OK]

Common Mistakes:

• Confusing HAVING with WHERE clause
• Using COUNT(column) instead of COUNT(*)
• Not grouping by the correct column

2. Which of the following SQL queries correctly finds duplicate email values in a table named users?

easy

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;

Solution

1. Step 1: Check correct syntax for grouping and filtering

   GROUP BY groups rows; HAVING filters groups. WHERE cannot filter aggregates.

2. Step 2: Identify correct query

   SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1; uses GROUP BY email and HAVING COUNT(*) > 1 correctly.

3. Final Answer:

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

4. Quick Check:

   GROUP BY + HAVING filters duplicates correctly [OK]

Hint: Use HAVING, not WHERE, to filter grouped results [OK]

Common Mistakes:

• Using WHERE with aggregate functions
• Placing WHERE after GROUP BY
• Using COUNT(column) incorrectly in WHERE

3. Given the table orders with column customer_id, what will this query return?

SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 2;

medium

A. All customer IDs with exactly 2 orders

B. All customer IDs regardless of order count

C. All customer IDs with less than 2 orders

D. All customer IDs with more than 2 orders

Solution

1. Step 1: Understand HAVING COUNT(*) > 2 condition

   This filters groups where the count of rows is greater than 2.

2. Step 2: Apply to customer_id groups

   Only customer IDs with more than 2 orders appear in the result.

3. Final Answer:

   All customer IDs with more than 2 orders -> Option D

4. Quick Check:

   HAVING COUNT(*) > 2 means count > 2 [OK]

Hint: HAVING COUNT(*) > N finds groups with more than N rows [OK]

Common Mistakes:

• Thinking it finds exactly 2 instead of more than 2
• Confusing WHERE and HAVING clauses
• Ignoring GROUP BY effect

4. Identify the error in this query to find duplicate username values:

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

medium

A. Missing ORDER BY clause

B. GROUP BY should come before WHERE

C. COUNT() cannot be used in WHERE clause

D. COUNT() should be COUNT(DISTINCT username)

Solution

1. Step 1: Check WHERE clause usage

   WHERE cannot filter on aggregate functions like COUNT().

2. Step 2: Correct clause to use HAVING

   HAVING is used to filter groups after GROUP BY with aggregates.

3. Final Answer:

   COUNT() cannot be used in WHERE clause -> Option C

4. Quick Check:

   Aggregates need HAVING, not WHERE [OK]

Hint: Use HAVING, not WHERE, to filter aggregates [OK]

Common Mistakes:

• Using WHERE with aggregate functions
• Placing GROUP BY after WHERE
• Confusing COUNT() usage

5. You want to find duplicate product_code values in a large inventory table but also want to see how many times each duplicate appears. Which query is the most efficient and correct?

hard

A. SELECT product_code, COUNT(*) AS count FROM inventory GROUP BY product_code HAVING count > 1;

B. SELECT product_code, COUNT(*) AS count FROM inventory WHERE COUNT(*) > 1 GROUP BY product_code;

C. SELECT product_code FROM inventory GROUP BY product_code HAVING COUNT(*) > 1 ORDER BY product_code;

D. SELECT product_code, COUNT(product_code) FROM inventory GROUP BY product_code WHERE COUNT(product_code) > 1;

Solution

1. Step 1: Check correct use of GROUP BY and HAVING

   SELECT product_code, COUNT(*) AS count FROM inventory GROUP BY product_code HAVING count > 1; groups by product_code and filters groups with count > 1 using HAVING, which is correct.

2. Step 2: Verify alias usage and efficiency

   Using COUNT(*) AS count allows filtering with HAVING count > 1 efficiently and shows counts.

3. Final Answer:

   SELECT product_code, COUNT(*) AS count FROM inventory GROUP BY product_code HAVING count > 1; -> Option A

4. Quick Check:

   GROUP BY + HAVING with alias count shows duplicates and counts [OK]

Hint: Use HAVING with COUNT(*) alias to filter and show duplicates count [OK]

Common Mistakes:

• Using WHERE with COUNT()
• Placing WHERE after GROUP BY
• Not selecting count to see duplicate frequency