sql_data_cleaninig_project.sql

Create database data_cleaning_db;
USE data_cleaning_db;
  
  Create Table Employee(
  id INT,
  Name VARCHAR(50),
  dept_id VARCHAR(50),
  Salary INT,
  City VARCHAR(50));
  
  
  INSERT INTO employee VALUES
(1, '  Amit', 1, 50000, 'Delhi'),
(2, 'Neha ', 2, NULL, 'Mumbai'),
(3, 'ROHAN', 1, 60000, 'delhi'),
(4, 'Priya', NULL, 45000, 'Bangalore'),
(5, 'amit', 1, 50000, 'Delhi'),
(6, 'Karan', 3, -1000, 'Pune'),
(7, '  Neha', 2, 70000, 'MUMBAI');


/*
Data Issues in Dataset:
1. Extra Spaces in text values
2. Case Mismatch (e.g., DELHI vs delhi)
3. NULL values present (missing data)
4. Duplicate records exist
5. Invalid salary values (negative salary like -1000)
*/
 
 ALTER TABLE Employee
 Modify dept_id  INT;

UPDATE Employee
SET Name= LOWER(TRIM(Name));

UPDATE Employee
SET City=UPPER(TRIM(City));

UPDATE Employee
SET Salary=(SELECT AVG(Salary) FROM (SELECT Salary FROM Employee WHERE Salary IS NOT NULL)AS Temp)
WHERE Salary IS NULL;

DELETE e1 FROM employee e1
JOIN employee e2
ON e1.name = e2.name
AND e1.dept_id = e2.dept_id
AND e1.salary = e2.salary
AND e1.city = e2.city
AND e1.id > e2.id;

-- optional
UPDATE employee
SET dept_id = 0
WHERE dept_id IS NULL;

SELECT * FROM Employee;

/*  Query 1: Dept-wise average salary */
SELECT  dept_id ,AVG(Salary) AS Average_Salary
FROM Employee
GROUP BY dept_id;
/* Insight:  “Department 1 has the highest average salary, indicating better pay scale compared to other departments.” */

/*City-wise employee count */
SELECT City ,COUNT(*) AS Total_Employee
FROM Employee GROUP BY City;
/*Insight:
“Most employees are concentrated in MUMBAI, showing it as a major working location.” */

/*Query 3: Highest salary employee*/
SELECT * FROM Employee
ORDER BY Salary DESC 
LIMIT 1;
/*Insight:
“The highest salary employee earns 70000, indicating top earning level in the dataset.” */

/*Query 4: Salary category*/
SELECT name ,Salary,
CASE
WHEN salary >60000 THEN 'HIGH'
WHEN Salary BETWEEN 40000 AND 60000 THEN 'MEDIUM'
ELSE 'LOW'
END AS Salary_Category
FROM Employee;