SQL Data Cleaning Techniques — 2026 Guide (MySQL) 🧹

Data aaya — ab kya karo? Step-by-step SQL data cleaning process — jaise ek real data analyst kaam karta hai. Data explore karo, issues dhundho, fix karo, clean table banao. Sab MySQL queries ke saath — practical aur simple. Data Insights par.

📑 Data Cleaning Process — Steps:

• Step 1: Data Cleaning Kya Hai — Introduction
• Step 2: Sample Dirty Dataset — Real-world messy data
• Step 3: Data Explore — Structure & Types dekho
• Step 4: NULL Values Handle Karo
• Step 5: Duplicates Remove Karo
• Step 6: Text Standardize Karo (Case & Spaces)
• Step 7: Inconsistent Values Fix Karo
• Step 8: Dates Clean Karo
• Step 9: Data Type Convert Karo
• Step 10: Outliers Handle Karo
• Step 11: Final Clean Table Banao

📘 Definition: Data Cleaning is the process of detecting and fixing (or removing) inaccurate, incomplete, duplicated, and inconsistent records from a dataset to improve data quality. In real-world scenarios, 60-70% of a data analyst's time goes into cleaning data — it's the most important and most time-consuming step before analysis.

🎯 Explanation: Real data KABHI clean nahi aata — hamesha dirty hota hai. Missing values, duplicate entries, extra spaces, inconsistent names (Delhi, DELHI, delhi), wrong data types, outliers — yeh sab common issues hain. "Garbage In, Garbage Out" — agar dirty data par analysis karoge toh wrong insights aayenge. Pehle clean karo, phir analyze karo.

📋 Data Cleaning Workflow — Real Process:

Data Analyst ka Real Data Cleaning Process:
Step 1: Data aaya (CSV, Excel, Database)
↓
Step 2: EXPLORE — Structure dekho (columns, data types)
↓
Step 3: NULL check karo — kahan data missing hai?
↓
Step 4: DUPLICATES check karo — same data repeat toh nahi?
↓
Step 5: TEXT clean karo — spaces, case standardize
↓
Step 6:
VALUES fix karo — inconsistent entries (M/Male/male)
↓
Step 7: DATES fix karo — format standardize
↓
Step 8: DATA TYPES fix karo — string mein numbers etc.
↓
Step 9: OUTLIERS check karo — unrealistic
values
↓
Step 10: CLEAN TABLE banao — production-ready data

Time Split:
🧹 Data Cleaning: ~60-70% time
📊 Analysis: ~20-25% time
📈 Visualization: ~10-15% time

📋 Common Data Issues:

📘 Definition: Before cleaning, we need a realistic dirty dataset. Below is a "messy employees" table — it has every common data quality issue. We'll use this SAME table throughout all cleaning steps.

🎯 Explanation: Yeh table intentionally messy hai — jaise real companies ka data hota hai. Extra spaces, mixed case, duplicates, NULL values, inconsistent gender values, wrong dates, outliers — sab kuch hai. In sabko step-by-step fix karenge.

💻 Create the Dirty Dataset:

-- Create messy employees table
CREATE TABLE employees_dirty ( emp_id INT, emp_name VARCHAR(100), email VARCHAR(100), gender VARCHAR(20), department VARCHAR(50), city VARCHAR(50), salary VARCHAR(20), join_date VARCHAR(20), phone VARCHAR(20) );

INSERT INTO employees_dirty
VALUES
(1, ' Jatin Kumar ', 'jatin@gmail.com', 'Male', 'IT', 'Delhi', '50000', '2024-01-15', '9876543210'),
(2, 'PRIYA PATEL', 'priya@gmail.com', 'F', ' Sales ', 'MUMBAI', '60000', '15/03/2024', '9123456780'),
(3, 'rahul verma', 'rahul@yahoo.com', 'm', 'IT', 'delhi', '55000', '2024-02-20', NULL),
(4, ' Neha Gupta', NULL, 'Female', 'HR', 'Delhi', '48000', '20-Apr-2024', '9988776655'),
(5, 'Vikram Singh', 'vikram@gmail.com', 'MALE', 'sales', 'Chennai', '9999999', '2024-03-10', '9876543210'),
(6, ' Jatin Kumar ', 'jatin@gmail.com', 'Male', 'IT', 'Delhi', '50000', '2024-01-15', '9876543210'),
(7, 'Sneha Iyer', 'sneha@outlook.com', 'female', 'Marketing', 'bangalore', '52000', '2024/05/25', '8877665544'),
(8, 'Arjun Das', 'arjun@gmail.com', NULL, 'IT ', 'Kolkata', '47000', '2024-06-18', '7766554433'),
(9, 'Meera Nair', 'meera@gmail.com', 'F', NULL, 'Chennai', '0', '2024-07-01', '6655443322'),
(10, 'RAHUL VERMA', 'rahul@yahoo.com', 'Male', 'IT', 'DELHI', '55000', '2024-02-20', NULL);

📊 Dirty Data Preview:

SELECT * FROM employees_dirty;

Issues spotted: ❌ Names: extra spaces, mixed case (PRIYA, rahul, Neha) ❌ Gender: Male, F, m, Female, MALE, female, NULL ❌ Department: extra spaces (IT , ' Sales '), NULL ❌ City: Delhi, delhi, DELHI, MUMBAI, bangalore (inconsistent) ❌ Salary: VARCHAR type, outlier (9999999), zero (0) ❌ Dates: 4 different formats! (YYYY-MM-DD, DD/MM/YYYY, DD-Mon-YYYY, YYYY/MM/DD) ❌ Duplicates: Row 1 = Row 6, Row 3 ≈ Row 10 ❌ NULLs: email, gender, department, phone

📘 Definition: Before cleaning, you must understand the dataset — how many rows, what columns exist, what data types are set, and what the data looks like. This step tells you WHAT to clean.

🎯 Explanation: Cleaning se pehle data ko samjho — "kitne rows hain? Columns kya hain? Data types kya hain? Kahan NULL hai? Kitne unique values hain?" Yeh exploration step hai — cleaning START karne se pehle HAMESHA yeh karo. Doctor bhi pehle diagnose karta hai, phir treatment shuru karta hai.

💻 Check Table Structure:

-- Step 3.1: Table structure — columns aur data types DESCRIBE employees_dirty;

salary VARCHAR hai! Should be INT join_date date bhi VARCHAR! Should be DATE phone varchar(20) YES NULL

💻 Check Total Rows & Basic Stats:

-- Step 3.2: Total rows
SELECT COUNT(*) AS total_rows
FROM employees_dirty;
-- Result: 10
-- Step 3.3: NULL count per column
SELECT
SUM(CASE WHEN emp_name IS NULL THEN 1 ELSE 0 END) AS null_name,

SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS null_email,

SUM(CASE WHEN gender IS NULL THEN 1 ELSE 0 END) AS null_gender,

SUM(CASE WHEN department IS NULL THEN 1 ELSE 0 END) AS null_dept,

SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) AS null_phone

FROM employees_dirty;

💻 Check Unique Values — Spot Inconsistencies:

-- Step 3.4: Check unique values in categorical columns
SELECT DISTINCT gender
FROM employees_dirty;

gender
values: Male, F, m, Female, MALE, female, NULL → 7 different
values for just 2 categories! NEEDS CLEANING

SELECT DISTINCT city
FROM employees_dirty;
SELECT DISTINCT department
FROM employees_dirty;

city
values: Delhi, MUMBAI, delhi, Chennai, bangalore, Kolkata,
DELHI → Delhi appears 3 times in different cases! department
values: IT, ' Sales ', HR, sales, Marketing, 'IT ', NULL → IT and sales have spaces and case issues

📘 Definition: NULL means missing/unknown data. Handling NULLs is the first cleaning step — you can either FILL them with a default value, FILL with calculated value, or REMOVE rows with NULLs depending on the situation.

🎯 Explanation: NULL = "pata nahi" — missing data. 3 options hain: (1) Default value se replace karo (jaise "Unknown"). (2) Delete karo woh rows agar bahut critical data missing hai. (3) Calculated value fill karo (jaise average salary). Kaunsa option choose karna hai — depends on column importance aur business context. Email NULL hai toh maybe "Unknown" fill karo. But agar primary key NULL hai toh row delete karo.

💻 Find NULL Values:

-- Find rows where email is NULL
SELECT emp_id, emp_name, email
FROM employees_dirty
WHERE email IS NULL;

💻 Fill NULLs with Default Value:

-- Method 1: IFNULL — replace NULL with default (SELECT only)
SELECT emp_id, emp_name, IFNULL(email, 'not_provided@company.com') AS email, IFNULL(gender, 'Unknown') AS gender,
IFNULL(department, 'Unassigned') AS department, IFNULL(phone, 'N/A') AS phone
FROM employees_dirty;

-- Method 2: COALESCE — try multiple fallback values
SELECT
emp_id,
COALESCE(email, phone, 'No Contact') AS contact_info

FROM employees_dirty;

-- Pehle email check karo, NULL toh phone, woh bhi NULL toh 'No Contact'

💻 Actually UPDATE the Table — Fix NULLs Permanently:

-- Fix NULL emails

UPDATE employees_dirty
SET email = 'not_provided@company.com'
WHERE email IS NULL;

-- Fix NULL gender

UPDATE employees_dirty

SET gender = 'Unknown'

WHERE gender IS NULL;

-- Fix NULL department

UPDATE employees_dirty

SET department = 'Unassigned'

WHERE department IS NULL;

-- Fix NULL phone

UPDATE employees_dirty

SET phone = 'N/A'

WHERE phone IS NULL;

📊 After NULL Handling — Verify:

-- Verify: No NULLs remaining
SELECT SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS null_email,
SUM(CASE WHEN gender IS NULL THEN 1 ELSE 0 END) AS null_gender,
SUM(CASE WHEN department IS NULL THEN 1 ELSE 0 END) AS null_dept,
SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) AS null_phone
FROM employees_dirty;

✅ All NULLs handled!

📘 Definition: Duplicate rows are records that appear more than once — either exactly same or semantically same (like "Jatin Kumar" and " JATIN KUMAR " — different text, same person). Duplicates inflate counts, skew averages, and corrupt analysis.

🎯 Explanation: Humare data mein Row 1 aur Row 6 exact duplicate hain (same Jatin Kumar). Row 3 (rahul verma) aur Row 10 (RAHUL VERMA) — same person but different case. Pehle identify karo, phir ek rakhke baaki delete karo. ROW_NUMBER window function se best kaam hota hai — har duplicate group mein ek ko rakhke baaki remove.

💻 Find Duplicate Rows:

-- Method 1: Find exact duplicates using GROUP BY
SELECT LOWER(TRIM(emp_name)) AS clean_name, email, COUNT(*) AS count
FROM employees_dirty
GROUP BY LOWER(TRIM(emp_name)), email
HAVING COUNT(*) > 1;

Row 1 and Row 6 rahul verma Row 3 and Row 10 rahul@yahoo.com 2

💻 Identify Duplicates with ROW_NUMBER:

-- Method 2: ROW_NUMBER to tag duplicates
SELECT *, ROW_NUMBER() OVER ( PARTITION BY LOWER(TRIM(emp_name)), email ORDER BY emp_id ) AS row_num
FROM employees_dirty;

KEEP (first occurrence) 6 DELETE (duplicate!) 3 KEEP 10 DELETE ...other rows... KEEP (unique) 2 1

💻 Delete Duplicates — Keep First Occurrence:

-- Delete duplicates — keep lowest emp_id per group DELETE FROM employees_dirty WHERE emp_id NOT IN (
SELECT min_id
FROM ( SELECT MIN(emp_id) AS min_id FROM employees_dirty GROUP BY LOWER(TRIM(emp_name)), email ) AS temp );

-- Verify: Check row count after deletion
SELECT COUNT(*) AS remaining_rows
FROM employees_dirty;

-- Before: 10 rows → After: 8 rows (2 duplicates removed ✅)

📘 Definition: Text standardization means making text data consistent — removing extra spaces (leading, trailing, double spaces), and standardizing letter case (uppercase/lowercase). Without this, "Delhi", "delhi", "DELHI", and " Delhi " are treated as DIFFERENT values — breaking GROUP BY, JOINs, and filtering.

🎯 Explanation: Humare data mein: names mein extra spaces (" Jatin Kumar "), cities mein mixed case (Delhi, delhi, DELHI, MUMBAI, bangalore), departments mein spaces (" Sales ", "IT "). TRIM se spaces hataenge, UPPER/LOWER se case standardize karenge. Yeh step bahut important hai — agar city column mein "Delhi" 3 alag tarike se likha hai toh GROUP BY city mein 3 groups banenge instead of 1!

💻 Before — See the Problem:

-- See the problem: same city, different text
SELECT city, COUNT(*) AS count
FROM employees_dirty
GROUP BY city;

city   │ count
Delhi │ 1 MUMBAI  1 delhi │ 1 Chennai │ 2 bangalore │ 1 Kolkata │ 1 DELHI │ 1
❌ Delhi appears 3 times! Should be ONE
group with 3 employees.

💻 Fix Names — TRIM + Proper Case:

-- Fix 6.1: Remove leading/trailing spaces from names

UPDATE employees_dirty
SET emp_name = TRIM(emp_name);

-- Fix 6.2: Proper Case for names — MySQL doesn't have INITCAP
-- We'll use CONCAT + UPPER(LEFT) + LOWER(SUBSTRING) for first word
-- For simple fix, convert to title case manually:

UPDATE employees_dirty

SET emp_name = CONCAT(
UPPER(LEFT(TRIM(emp_name), 1)),
LOWER(SUBSTRING(TRIM(emp_name), 2))
);

💻 Fix City — Proper Case:

-- Fix 6.3: Standardize city — Proper Case (capitalize first letter)

UPDATE employees_dirty
SET city = CONCAT( UPPER(LEFT(TRIM(city), 1)), LOWER(SUBSTRING(TRIM(city), 2)) );

💻 Fix Department — TRIM + Proper Case:

-- Fix 6.4: Clean department — remove spaces + standardize case

UPDATE employees_dirty
SET department = CONCAT( UPPER(LEFT(TRIM(department), 1)), LOWER(SUBSTRING(TRIM(department), 2)) );

📊 After — Verify Standardization:

-- Check city after cleaning
SELECT city, COUNT(*) AS count
FROM employees_dirty
GROUP BY city;

BEFORE: AFTER: city │ count city │ count
Delhi │ 1 Delhi │ 3 ← All merged! ✅ MUMBAI │ 1 Mumbai │ 1 ✅ delhi │ 1 Chennai │ 2 ✅ Chennai │ 2 Bangalore │ 1 ✅ bangalore │ 1 Kolkata │ 1 ✅ Kolkata │ 1 DELHI │ 1
7 groups → 5 groups! Delhi properly consolidated!

-- Check all text columns now
SELECT emp_id, emp_name, department, city
FROM employees_dirty;

emp_id  │ emp_name    │ department │ city 
1       | Jatin kumar │ It         │ Delhi ✅ (was ' Jatin Kumar ') 
2       │ Priya patel │ Sales      │ Mumbai ✅ (was 'PRIYA PATEL', 'MUMBAI') 
3       │ Rahul verma │ It         │ Delhi ✅ (was 'rahul verma', 'delhi')
4       │ Neha gupta  │ Hr         │ Delhi ✅ (was ' Neha Gupta') 
5       │ Vikram singh│ Sales      │ Chennai ✅ (was 'sales') 
7       │ Sneha iyer  │ Marketing  │ Bangalore ✅ (was 'bangalore') 
8       │ Arjun das   │ It         │ Kolkata ✅ (was 'IT ') 
9       │ Meera nair  │ Unassigned │ Chennai ✅ 
All text standardized — no extra spaces, consistent case! 🎉

📘 Definition: Inconsistent values mean the same thing is represented in multiple ways — like gender stored as "Male", "M", "m", "MALE", "male" — all should be ONE standard value. This commonly happens in categorical columns (gender, status, department, country) where data is entered manually without dropdown validation.

🎯 Explanation: Humare data mein gender column sabse bura hai — Male, F, m, Female, MALE, female, Unknown — 7 different values for sirf 2 categories! GROUP BY karo toh 7 groups banenge instead of 2. Fix kaise? CASE WHEN se sab ko standardize karo — "M", "m", "Male", "MALE" → sabko "Male" banao. "F", "female", "Female" → "Female" banao. REPLACE simple cases mein kaam karta hai, CASE WHEN complex mapping ke liye.

💻 Before — See the Inconsistency:

-- Check current gender values
SELECT gender, COUNT(*) AS count
FROM employees_dirty
GROUP BY gender;

Male, Female, Unknown

💻 Fix Gender — CASE WHEN Standardization:

-- Step 1: Preview the fix first (SELECT — don't UPDATE yet!) SELECT emp_id, emp_name, gender AS old_gender, CASE WHEN LOWER(TRIM(gender)) IN ('m', 'male') THEN 'Male' WHEN LOWER(TRIM(gender)) IN ('f', 'female') THEN 'Female' ELSE 'Unknown' END AS new_gender FROM employees_dirty;

💻 Apply the Fix — UPDATE:

-- Step 2: Actually UPDATE the table

UPDATE employees_dirty
SET gender = CASE
WHEN LOWER(TRIM(gender)) IN ('m', 'male')
THEN 'Male'
WHEN LOWER(TRIM(gender)) IN ('f', 'female')
THEN 'Female'
ELSE 'Unknown'
END;

-- Verify after update
SELECT gender, COUNT(*) AS count

FROM employees_dirty

GROUP BY gender;

💻 REPLACE — Simple Text Substitution:

-- REPLACE is good for simple substitutions -- Example: Fix department abbreviations

UPDATE employees_dirty
SET department = REPLACE(department, 'It', 'IT')
WHERE department = 'It';

UPDATE employees_dirty

SET department = REPLACE(department, 'Hr', 'HR')

WHERE department = 'Hr';

-- Verify
SELECT DISTINCT department
FROM employees_dirty;

department

IT ✅ (was It) Sales ✅ HR ✅ (was Hr) Marketing ✅ Unassigned ✅

📘 Definition: Date cleaning involves converting dates from various string formats into a standard DATE type. Real data often has dates in multiple formats — "2024-01-15", "15/03/2024", "20-Apr-2024", "2024/05/25" — all in the same column. We need to standardize them to one format (YYYY-MM-DD) and convert from VARCHAR to proper DATE type.

🎯 Explanation: Humare data mein join_date column VARCHAR hai (Step 3 mein dekha tha) aur 4 different formats hain — "2024-01-15" (YYYY-MM-DD), "15/03/2024" (DD/MM/YYYY), "20-Apr-2024" (DD-Mon-YYYY), "2024/05/25" (YYYY/MM/DD). Pehle sab ko ek format mein laao, phir column ka data type DATE mein convert karo. STR_TO_DATE() MySQL ka function hai — string ko date mein convert karta hai format specify karke.

💻 Before — Check Current Date Formats:

-- See all different date formats
SELECT emp_id, emp_name, join_date
FROM employees_dirty;

4 different formats! Need standardization.

📋 MySQL Date Format Codes:

💻 Fix Each Date Format — Step by Step:

-- Fix 8.1: DD/MM/YYYY format (e.g., "15/03/2024")

UPDATE employees_dirty
SET join_date = DATE_FORMAT( STR_TO_DATE(join_date, '%d/%m/%Y'), '%Y-%m-%d' )
WHERE join_date LIKE '%/%' AND join_date NOT LIKE '____/%';
-- Matches "15/03/2024" but NOT "2024/05/25"
-- Fix 8.2: DD-Mon-YYYY format (e.g., "20-Apr-2024")

UPDATE employees_dirty

SET join_date = DATE_FORMAT(
STR_TO_DATE(join_date, '%d-%b-%Y'),
'%Y-%m-%d'
)

WHERE join_date REGEXP '[0-9]{2}-[A-Za-z]{3}-[0-9]{4}';

-- Matches "20-Apr-2024" pattern

-- Fix 8.3: YYYY/MM/DD format (e.g., "2024/05/25")

UPDATE employees_dirty

SET join_date = REPLACE(join_date, '/', '-')

WHERE join_date LIKE '____/%';

-- "2024/05/25" → "2024-05-25" (simple slash to hyphen)

📊 After — All Dates Standardized:

-- Verify all dates are now YYYY-MM-DD format
SELECT emp_id, emp_name, join_date
FROM employees_dirty;

All dates in YYYY-MM-DD format! 🎉

📘 Definition: Data Type Conversion means changing columns from wrong types to correct types. Common issues: salary stored as VARCHAR instead of INT, dates as VARCHAR instead of DATE. Wrong data types prevent mathematical operations (SUM on text fails), date functions (DATEDIFF on text fails), and proper sorting.

🎯 Explanation: Humare data mein salary VARCHAR(20) hai — string hai, number nahi! SUM(salary) karo toh galat result aayega ya error. join_date bhi VARCHAR hai — DATEDIFF(), YEAR() jaisi functions kaam nahi karengi. ALTER TABLE se column ka data type change karenge — VARCHAR → INT (salary), VARCHAR → DATE (join_date). Yeh step cleaning ke baad karo — pehle values clean hone chahiye, warna type conversion fail hoga.

💻 Before — Check Current Types:

-- Current types (from Step 3) DESCRIBE employees_dirty;

salary → varchar(20) ❌ Should be INT or DECIMAL join_date → varchar(20) ❌ Should be DATE

💻 Test Before Converting — CAST Preview:

-- Test salary conversion first (SELECT — preview)
SELECT emp_id, emp_name, salary AS salary_varchar, CAST(salary AS UNSIGNED) AS salary_int
FROM employees_dirty;

-- Test date conversion
SELECT
emp_id,
join_date AS date_varchar,
CAST(join_date AS DATE) AS date_proper

FROM employees_dirty;

'2024-07-01' 2024-07-01 ✅ Conversion looks correct! Safe to ALTER.

💻 ALTER TABLE — Change Data Types Permanently:

-- Convert salary from VARCHAR to INT ALTER TABLE employees_dirty MODIFY COLUMN salary INT;
-- Convert join_date from VARCHAR to DATE
ALTER TABLE employees_dirty
MODIFY COLUMN join_date DATE;

-- Verify new types
DESCRIBE employees_dirty;

date varchar(20) → date ✅

💻 Now Date Functions Work!

-- Date functions work now! (weren't possible with VARCHAR)
SELECT emp_name, join_date, YEAR(join_date) AS join_year,
MONTH(join_date) AS join_month, DATEDIFF(CURDATE(), join_date) AS days_since_joining
FROM employees_dirty;

-- Salary aggregation works now!
SELECT
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary

FROM employees_dirty;

📘 Definition: Outliers are values that are significantly different from other values in the dataset — either unrealistically high or low. Examples: salary = 9999999 (data entry error), salary = 0 (missing but entered as zero). Outliers skew averages, totals, and other statistical measures. We need to detect them, investigate why they exist, and decide whether to fix, cap, or remove them.

🎯 Explanation: Humare data mein salary column mein 2 outliers hain — Vikram ka salary 9999999 (data entry error — realistic nahi) aur Meera ka salary 0 (maybe missing value ko zero enter kiya). Average salary calculate karo with outliers — bahut zyada inflated aayega. Pehle detect karo (statistical methods ya business rules se), phir decide karo — remove karo, cap karo, ya NULL banao.

💻 Detect Outliers — Statistical Method:

-- Method 1: Check salary statistics — spot extreme values
SELECT COUNT(*) AS total, MIN(salary) AS min_sal, MAX(salary) AS max_sal,
ROUND(AVG(salary), 0) AS avg_sal, ROUND(STDDEV(salary), 0) AS std_dev
FROM employees_dirty;

❌ avg salary ₹12.89L? Unrealistic!
❌ min = 0 (probably missing data entered as zero)
❌ max = 9999999 (data entry error)
❌ STDDEV bahut bada — outliers ki wajah se

💻 Find Outlier Rows:

-- Method 2: Business rule — realistic salary range -- Assume: Normal salary range is ₹10,000 to ₹5,00,000
SELECT emp_id, emp_name, salary, CASE
WHEN salary = 0
THEN 'ZERO (possibly missing)'
WHEN salary < 10000
THEN 'TOO LOW'
WHEN salary > 500000
THEN 'TOO HIGH'
ELSE 'NORMAL'
END AS status
FROM employees_dirty
ORDER BY salary;

TOO HIGH ⚠️

💻 Fix Outliers:

-- Option 1: Set outliers to NULL (safest — mark as unknown)

UPDATE employees_dirty
SET salary = NULL
WHERE salary = 0 OR salary > 500000;

-- Option 2: Replace with average (imputation)
-- First calculate avg WITHOUT outliers

SET @avg_salary = (
SELECT ROUND(AVG(salary), 0)
FROM employees_dirty
WHERE salary BETWEEN 10000 AND 500000
);

UPDATE employees_dirty

SET salary = @avg_salary

WHERE salary IS NULL;

-- Verify after fixing outliers
SELECT
MIN(salary) AS min_sal,
MAX(salary) AS max_sal,
ROUND(AVG(salary), 0) AS avg_sal

FROM employees_dirty;

BEFORE (with outliers): AFTER (outliers fixed): min: 0, max: 9999999 min: 47000,
max: 60000 avg: ₹12,89,000 (WRONG!) avg: ₹52,000 (REALISTIC ✅)

📘 Definition: The final step is to create a clean, production-ready table from your cleaned data. This table should have correct data types, no NULLs (or handled NULLs), no duplicates, standardized text, consistent values, and proper constraints. You can either UPDATE the existing table (like we did) or CREATE a new clean table from the dirty one.

🎯 Explanation: Ab tak humne step-by-step sab fix kiya — NULLs, duplicates, text, dates, types, outliers. Ab final clean table verify karte hain aur ek production-ready version banate hain. Agar original table modify nahi karni thi — toh CREATE TABLE employees_clean AS SELECT ... se naya clean table bana sakte ho without touching original.

💻 Final Verification — Data Quality Check:

-- Final Quality Check — all issues resolved?
SELECT COUNT(*) AS total_rows, COUNT(DISTINCT LOWER(emp_name)) AS unique_employees,
COUNT(DISTINCT gender) AS gender_values, COUNT(DISTINCT department) AS dept_values,
COUNT(DISTINCT city) AS city_values, MIN(salary) AS min_salary,
MAX(salary) AS max_salary, ROUND(AVG(salary), 0) AS avg_salary,
MIN(join_date) AS earliest_date, MAX(join_date) AS latest_date
FROM employees_dirty;

total_rows: 8 ✅ (was 10, 2 duplicates removed) unique_employees: 8 ✅ (no duplicates) gender_values: 3 ✅ (Male, Female, Unknown — was 7!) dept_values: 5 ✅ (IT, Sales, HR, Marketing, Unassigned) city_values: 5 ✅ (was 7 — Delhi consolidated) min_salary: 47000 ✅ (was 0) max_salary: 60000 ✅ (was 9999999) avg_salary: 52000 ✅ (was 12,89,000 — realistic now!) earliest_date: 2024-01-15 ✅ (proper DATE format) latest_date: 2024-07-01 ✅

💻 Create Clean Production Table:

-- Method 1: Create new clean table from dirty (if you want to keep original) CREATE TABLE employees_clean AS
SELECT emp_id, TRIM(emp_name) AS emp_name, email, gender,
department, city, salary, join_date, phone
FROM employees_dirty
ORDER BY emp_id;

-- Method 2: Add constraints to prevent future dirty data
ALTER TABLE employees_clean
MODIFY COLUMN emp_id INT PRIMARY KEY,
MODIFY COLUMN emp_name VARCHAR(100) NOT NULL,
MODIFY COLUMN email VARCHAR(100) NOT NULL,
MODIFY COLUMN salary INT NOT NULL,
MODIFY COLUMN join_date DATE NOT NULL;

📊 Final Clean Data — Before vs After:

-- View final clean data
SELECT *
FROM employees_clean;

🎉 CLEAN DATA — Production Ready!

📋 Complete Cleaning Summary — What We Fixed:

🎉 SQL Data Cleaning Guide — Complete!

11 steps mein dirty data ko clean, production-ready data mein convert kiya — NULL handling, duplicate removal, text standardization, inconsistent values fix, date cleaning, data type conversion, outlier handling, aur final clean table creation. Yeh exact same process hai jo real-world data analysts daily follow karte hain. Data Insights SQL Topic Wise series ka pehla topic complete! Aage aur SQL topics aayenge — Window Functions, CTEs, Advanced JOINs aur bahut kuch. Data Insights par sab FREE, detailed, Hinglish mein.

Happy Cleaning & Keep Querying! 🚀