Data Cleaning in SQL

-- Find duplicates by email:
SELECT email, COUNT(*) AS cnt
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

-- See full duplicate rows:
SELECT *
FROM customers
WHERE email IN (
  SELECT email FROM customers
  GROUP BY email HAVING COUNT(*) > 1
);

GROUP BY + HAVING COUNT(*) > 1 identifies duplicates. This is the first step in any data deduplication task — understand the scope before deleting anything.

DELETE FROM customers
WHERE id NOT IN (
  SELECT MIN(id)
  FROM customers
  GROUP BY email
);

-- MySQL requires subquery workaround:
DELETE FROM customers
WHERE id NOT IN (
  SELECT min_id FROM (
    SELECT MIN(id) AS min_id FROM customers GROUP BY email
  ) tmp
);

Keep one row per email (the one with the smallest id), delete the rest. MySQL doesn't allow deleting from a table you're selecting from directly — hence the nested subquery.

SELECT * FROM customers
WHERE LENGTH(REPLACE(phone, ' ', '')) != 10
   OR phone REGEXP '[^0-9]';

Data validation in SQL — check length and format. REPLACE removes spaces first. REGEXP checks for non-numeric characters. Adapt the pattern to your data's phone format.

-- View the issue:
SELECT DISTINCT department FROM employees;

-- Fix in query (don't modify source):
SELECT INITCAP(LOWER(department)) AS clean_dept
FROM employees;

-- MySQL (no INITCAP): 
SELECT CONCAT(UPPER(LEFT(department,1)), LOWER(SUBSTRING(department,2)))
FROM employees;

LOWER then INITCAP (PostgreSQL) gives proper case. MySQL doesn't have INITCAP — manually uppercase first char. Always prefer fixing at source (UPDATE) over fixing in every query.

SELECT order_id, order_date, ship_date
FROM orders
WHERE ship_date < order_date;

Business rule validation — an order cannot ship before it's placed. These anomaly-detection queries are run as part of data quality pipelines and audits.