Duplicate Emails
Easysql
The email column in employees should be unique, but isn't currently enforced. Write a query returning every email that appears more than once, along with a cnt column showing how many times it appears, ordered by email.
sqlCREATE TABLE departments ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE employees ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, department_id INTEGER );
Sample data (this is what your query runs against when you press Run):
sqlINSERT INTO departments (id, name) VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Marketing'); INSERT INTO employees (id, name, email, department_id) VALUES (1, 'Asha', 'asha@co.com', 1), (2, 'Rohan', 'rohan@co.com', 1), (3, 'Neha', 'neha@co.com', 2), (4, 'Vikram', 'asha@co.com', 2), (5, 'Priya', 'priya@co.com', 99), (6, 'Karan', 'karan@co.com', 3), (7, 'Divya', 'karan@co.com', 3), (8, 'Aman', 'aman@co.com', 88);
Example 1
Input
(none)
Output
email cnt asha@co.com 2 karan@co.com 2
Group by the column that should be unique, then keep only groups with more than one row. Reference: SELECT email, COUNT(*) AS cnt FROM employees GROUP BY email HAVING COUNT(*) > 1 ORDER BY email;