Finding duplicates efficiently in SQL - Mini Project: Build & Apply

Finding duplicates efficiently

📖 Scenario: You work in a small library database. The librarian suspects that some books have been entered twice by mistake. Your job is to find these duplicate book entries by their title and author.

🎯 Goal: Create a SQL query that finds duplicate books by title and author in the books table.

📋 What You'll Learn

Create a books table with columns id, title, and author.

Insert the given book entries into the books table.

Write a query to find duplicate books by title and author.

Show the title, author, and the count of duplicates as duplicate_count.

💡 Why This Matters

🌍 Real World

Finding duplicates in databases helps keep data clean and accurate, which is important in libraries, stores, and many other places.

💼 Career

Database administrators and data analysts often need to find and fix duplicate data to ensure reports and operations are correct.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and author (text). Then insert these exact rows: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'), (3, 'The Great Gatsby', 'F. Scott Fitzgerald'), (4, 'To Kill a Mockingbird', 'Harper Lee'), (5, '1984', 'George Orwell').

SQL

-- Create the books table and insert the given rows
-- Your code here

Hint

Use CREATE TABLE to make the table and INSERT INTO to add rows.

2

Add a variable for minimum duplicate count

Create a variable called min_duplicates and set it to 2. This will help us find entries that appear at least twice.

SQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    author TEXT
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, '1984', 'George Orwell'),
(3, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(4, 'To Kill a Mockingbird', 'Harper Lee'),
(5, '1984', 'George Orwell');

-- Create a variable min_duplicates and set it to 2
-- Your code here

Hint

Use \set min_duplicates 2 to define a variable in psql or your SQL environment.

3

Write the query to find duplicates

Write a SQL query that selects title, author, and counts how many times each pair appears as duplicate_count from the books table. Group by title and author. Use HAVING to only keep groups where the count is greater than or equal to min_duplicates.

SQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    author TEXT
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, '1984', 'George Orwell'),
(3, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(4, 'To Kill a Mockingbird', 'Harper Lee'),
(5, '1984', 'George Orwell');

-- Variable for minimum duplicates
\set min_duplicates 2

-- Write the query to find duplicates
-- Your code here

Hint

Use GROUP BY and HAVING COUNT(*) >= :min_duplicates to find duplicates.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort the results by duplicate_count in descending order, so the most duplicated books appear first.

SQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    author TEXT
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, '1984', 'George Orwell'),
(3, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(4, 'To Kill a Mockingbird', 'Harper Lee'),
(5, '1984', 'George Orwell');

-- Variable for minimum duplicates
\set min_duplicates 2

SELECT title, author, COUNT(*) AS duplicate_count
FROM books
GROUP BY title, author
HAVING COUNT(*) >= :min_duplicates

-- Add ORDER BY to sort by duplicate_count descending
-- Your code here

Hint

Use ORDER BY duplicate_count DESC to sort duplicates from most to least.

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