Duplicate Emails SQL Challenge - Oracle Solution

1. Introduction

In this blog post, we'll explore a common task in Oracle databases – identifying duplicate email addresses. This type of query is crucial in data cleaning and validation processes.

Problem

The task is to create an Oracle SQL query to find all duplicate email addresses in a database table.

Database Table:

Person Table:

Id	Email
1	example@example.com
2	test@example.com
3	example@example.com

3. Oracle Solution

SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email) > 1;

Output:

Email
example@example.com

Explanation:

1. The query groups the records in the Person table by Email.

2. The GROUP BY clause helps in aggregating the emails.

3. The HAVING clause is then used to filter out those groups that contain more than one occurrence of the same email.

4. This effectively identifies and lists duplicate email addresses in the table.