SQL Exercises/Employee management

Relational Schema 300px Exercises 1. Select the last name of all employees. SELECT LastName FROM Employees; 2. Select the last name of all employees, without duplicates. SELECT DISTINCT LastName FROM Employees; 3. Select all the data of employees whose last name is "Smith". SELECT * FROM Employees WHERE LastName = 'Smith'; 4. Select all the data of employees whose last name is "Smith" or "Doe". /* With OR */ SELECT * FROM Employees WHERE LastName = 'Smith' OR LastName = 'Doe'; /* With IN */ SELECT * FROM Employees WHERE LastName IN ('Smith' , 'Doe'); 5. Select all the data of employees that work in department 14. SELECT * FROM Employees WHERE Department = 14; 6. Select all the data of employees that work in department 37 or department 77. /* With OR */ SELECT * FROM Employees WHERE Department = 37 OR Department = 77; /* With IN */ SELECT * FROM Employees WHERE Department IN (37,77); 7. Select all the data of employees whose last name begins with an "S". SELECT * FROM Employees WHERE LastName LIKE 'S%'; 8. Select the sum of all the departments' budgets. SELECT SUM(Budget) FROM Departments; 9. Select the number of employees in each department (you only need to show the department code and the number of employees). SELECT Department, COUNT(*) FROM Employees GROUP BY Department; 10. Select all the data of employees, including each employee's department's data. SELECT * FROM Employees E INNER JOIN Departments D ON E.Department = D.Code; 11. Select the name and last name of each employee, along with the name and budget of the employee's department. /* Without labels */ SELECT Employees.Name, LastName, Departments.Name AS DepartmentsName, Budget FROM Employees INNER JOIN Departments ON Employees.Department = Departments.Code; /* With labels */ SELECT E.Name, LastName, D.Name AS DepartmentsName, Budget FROM Employees E INNER JOIN Departments D ON E.Department = D.Code; 12. Select the name and last name of employees working for departments with a budget greater than $60,000. /* Without subquery */ SELECT Employees.Name, LastName FROM Employees INNER JOIN Departments ON Employees.Department = Departments.Code AND Departments.Budget > 60000; /* With subquery */ SELECT Name, LastName FROM Employees WHERE Department IN (SELECT Code FROM Departments WHERE Budget > 60000); 13. Select the departments with a budget larger than the average budget of all the departments. SELECT * FROM Departments WHERE Budget > ( SELECT AVG(Budget) FROM Departments ); 14. Select the names of departments with more than two employees. /*With subquery*/ SELECT D.Name FROM Departments D WHERE 2 < ( SELECT COUNT(*) FROM Employees WHERE Department = D.Code ); /* With IN and subquery */ SELECT Name FROM Departments WHERE Code IN ( SELECT Department FROM Employees GROUP BY Department HAVING COUNT(*) > 2 ); /* With UNION. This assumes that no two departments have the same name */ SELECT Departments.Name FROM Employees INNER JOIN Departments ON Department = Code GROUP BY Departments.Name HAVING COUNT(*) > 2; 15. Select the name and last name of employees working for departments with second lowest budget. /* With subquery */ SELECT e.Name, e.LastName FROM Employees e WHERE e.Department = ( SELECT sub.Code FROM (SELECT * FROM Departments d ORDER BY d.budget LIMIT 2) sub ORDER BY budget DESC LIMIT 1); /* With subquery */ SELECT Name, LastName FROM Employees WHERE Department IN ( SELECT Code FROM Departments WHERE Budget = ( SELECT TOP 1 Budget FROM Departments WHERE Budget IN ( SELECT DISTINCT TOP 2 Budget FROM Departments ORDER BY Budget ASC ) ORDER BY Budget DESC ) ); 16. Add a new department called "Quality Assurance", with a budget of $40,000 and departmental code 11. Add an employee called "Mary Moore" in that department, with SSN 847-21-9811. INSERT INTO Departments VALUES ( 11 , 'Quality Assurance' , 40000); INSERT INTO Employees VALUES ( '847219811' , 'Mary' , 'Moore' , 11); /*Note: Quoting numbers in SQL works but is bad practice. SSN should not be quoted it is an integer.*/ 17. Reduce the budget of all departments by 10%. UPDATE Departments SET Budget = Budget * 0.9; 18. Reassign all employees from the Research department (code 77) to the IT department (code 14). UPDATE Employees SET Department = 14 WHERE Department = 77; 19. Delete from the table all employees in the IT department (code 14). DELETE FROM Employees WHERE Department = 14; 20. Delete from the table all employees who work in departments with a budget greater than or equal to $60,000. DELETE FROM Employees WHERE Department IN ( SELECT Code FROM Departments WHERE Budget >= 60000 ); 21. Delete from the table all employees. DELETE FROM Employees; Table creation code CREATE TABLE Departments ( Code INTEGER PRIMARY KEY NOT NULL, Name VARCHAR NOT NULL , Budget REAL NOT NULL ); CREATE TABLE Employees ( SSN INTEGER PRIMARY KEY NOT NULL, Name TEXT NOT NULL , LastName VARCHAR NOT NULL ,--since question 2 asks about removing duplicate - text must be converted if the answer is using distinct Department INTEGER NOT NULL , CONSTRAINT fk_Departments_Code FOREIGN KEY(Department) REFERENCES Departments(Code) ); Click to see MySQL syntax. CREATE TABLE Departments ( Code INTEGER PRIMARY KEY, Name varchar(255) NOT NULL , Budget decimal NOT NULL ); CREATE TABLE Employees ( SSN INTEGER PRIMARY KEY, Name varchar(255) NOT NULL , LastName varchar(255) NOT NULL , Department INTEGER NOT NULL , foreign key (department) references Departments(Code) ) ENGINE=INNODB; Click to see Oracle syntax. CREATE TABLE Departments ( Code INT PRIMARY KEY NOT NULL, Name VARCHAR(100) NOT NULL , Budget NUMBER NOT NULL ); CREATE TABLE Employees ( SSN INT PRIMARY KEY NOT NULL, Name VARCHAR(30) NOT NULL , LastName VARCHAR(30) NOT NULL , Department INT NOT NULL , CONSTRAINT fk_Departments_Code FOREIGN KEY(Department) REFERENCES Departments(Code) ); Sample dataset INSERT INTO Departments(Code,Name,Budget) VALUES(14,'IT',65000); INSERT INTO Departments(Code,Name,Budget) VALUES(37,'Accounting',15000); INSERT INTO Departments(Code,Name,Budget) VALUES(59,'Human Resources',240000); INSERT INTO Departments(Code,Name,Budget) VALUES(77,'Research',55000); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('123234877','Michael','Rogers',14); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('152934485','Anand','Manikutty',14); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('222364883','Carol','Smith',37); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('326587417','Joe','Stevens',37); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('332154719','Mary-Anne','Foster',14); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('332569843','George','O''Donnell',77); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('546523478','John','Doe',59); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('631231482','David','Smith',77); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('654873219','Zacary','Efron',59); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('745685214','Eric','Goldsmith',59); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('845657245','Elizabeth','Doe',14); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('845657246','Kumar','Swamy',14);