SQL Exercise Answers (emp & dept)
1. CREATE TABLE emp (EmpID INT PRIMARY KEY, EmpName VARCHAR(100), DepartmentID
INT, Salary DECIMAL(10,2), HireDate DATE);
2. CREATE TABLE dept (DepartmentID INT PRIMARY KEY, DepartmentName VARCHAR(100),
Location VARCHAR(100));
3. ALTER TABLE emp ADD Email VARCHAR(100);
4. ALTER TABLE emp DROP COLUMN HireDate;
5. INSERT INTO emp (EmpName, DepartmentID, Salary, HireDate) VALUES ('Ava Martin', 102,
67000, '2022-05-01');
6. UPDATE emp SET Salary = Salary * 1.10 WHERE DepartmentID = 101;
7. DELETE FROM emp WHERE Salary < 55000;
8. INSERT INTO emp_backup SELECT * FROM emp WHERE DepartmentID = (SELECT
DepartmentID FROM dept WHERE DepartmentName = 'IT');
9. SELECT EmpName, Salary FROM emp WHERE Salary > 70000;
10. SELECT DepartmentID, AVG(Salary) AS AvgSalary FROM emp GROUP BY DepartmentID;
11. SELECT DepartmentID FROM emp GROUP BY DepartmentID HAVING COUNT(*) > 1;
12. SELECT e.EmpName, d.DepartmentName, d.Location FROM emp e JOIN dept d ON
e.DepartmentID = d.DepartmentID;
13. SELECT EmpName, Salary FROM emp e WHERE Salary = (SELECT MAX(Salary) FROM emp
e2 WHERE e2.DepartmentID = e.DepartmentID);
14. SELECT * FROM emp WHERE HireDate > '2020-01-01' AND Salary > 60000;
15. SELECT * FROM emp WHERE Salary > (SELECT AVG(Salary) FROM emp);
16. SELECT * FROM dept WHERE DepartmentID NOT IN (SELECT DISTINCT DepartmentID
FROM emp);
17. SELECT e.EmpName, d.DepartmentName, d.Location FROM emp e INNER JOIN dept d ON
e.DepartmentID = d.DepartmentID;
18. SELECT e.EmpName FROM emp e LEFT JOIN dept d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentID IS NULL;
19. SELECT d.DepartmentName, COUNT(e.EmpID) AS EmployeeCount FROM dept d LEFT JOIN
emp e ON d.DepartmentID = e.DepartmentID GROUP BY d.DepartmentName;
20. SELECT DepartmentID FROM emp GROUP BY DepartmentID HAVING AVG(Salary) =
(SELECT MAX(avg_sal) FROM (SELECT DepartmentID, AVG(Salary) AS avg_sal FROM emp
GROUP BY DepartmentID) AS dept_avg);
