﻿ SQL Challenges-1: Duplicate Emails - w3resource

# SQL Challenges-1: Duplicate Emails

## SQL Challenges-1: Exercise-5 with Solution

From the following table, write a SQL query to find all the duplicate emails (no upper case letters) of the employees. Return email ids.

Input:

Table: employees

Structure:

FieldTypeNullKeyDefaultExtra
employee_idint(11)YES
employee_namevarchar(255)YES
email_idvarchar(255)YES

Data:

employee_idemployee_nameemail_id
101Liam Alton[email protected]
102Josh Day[email protected]
103Sean Mann[email protected]
104Evan Blake[email protected]
105Toby Scott[email protected]

Sample Solution:

SQL Code(MySQL):

``````CREATE TABLE IF NOT EXISTS employees(employee_id int, employee_name varchar(255), email_id varchar(255));
TRUNCATE TABLE employees;
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('101','Liam Alton', '[email protected]');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('102','Josh Day', '[email protected]');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('103','Sean Mann', '[email protected]');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('104','Evan Blake', '[email protected]');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('105','Toby Scott', '[email protected]');
SELECT * FROM employees;

SELECT email_id FROM
(
SELECT email_id, COUNT(email_id) AS nuOfAppearence
FROM employees
GROUP BY email_id
) AS countEmail
WHERE nuOfAppearence> 1;
```
```

Sample Output:

```email_id     |
-------------|
[email protected]|
```

Relational Algebra Expression:

Relational Algebra Tree:

Solution-1:

``````SELECT email_id
FROM employees
GROUP BY email_id
HAVING COUNT(email_id) > 1;
``````

Solution-2:

``````SELECT DISTINCT p1.email_id
FROM employees p1, employees p2
WHERE p1.email_id = p2.email_id AND p1.employee_id != p2.employee_id;
``````

SQL Code Editor: