SQL Interview Series — Part 2

📌 Question 2: Find Duplicate Records

Suppose you have an Employee table:

Employee

• employee_id

• employee_name

• department

• email

❓ Find all email addresses that appear more than once in the Employee table.

Example:

employee_id | employee_name | email
------------|---------------|-------------------
1           | Amit          | amit@gmail.com
2           | Rahul         | rahul@gmail.com
3           | Priya         | amit@gmail.com
4           | Neha          | neha@gmail.com
5           | Raj           | rahul@gmail.com

Expected result:

email

amit@gmail.com
rahul@gmail.com

💡 Approach

We need to:

1️⃣ Group records by email.

2️⃣ Count how many times each email appears.

3️⃣ Keep only the emails whose count is greater than 1. 

📌 SQL Solution

SELECT email, COUNT(*) AS occurrence_count
FROM Employee
GROUP BY email
HAVING COUNT(*) > 1;

🔎 Why use HAVING instead of WHERE?

"WHERE" filters individual rows before grouping.

"HAVING" filters groups after "GROUP BY".

Since we want to filter based on "COUNT(*)", we use "HAVING".

GROUP BY email
HAVING COUNT(*) > 1

This means:

"Group employees by email and return only those groups containing more than one record."

🎯 Double Tap ❤️ For Part-3