SQL Exercises/Employee management

Exercises

1. Select the last name of all employees.

Click to see solution
```SELECT LastName FROM Employees;
```

2. Select the last name of all employees, without duplicates.

Click to see solution
```SELECT DISTINCT LastName FROM Employees;
```

3. Select all the data of employees whose last name is "Smith".

Click to see solution
```SELECT * FROM Employees WHERE LastName = 'Smith';
```

4. Select all the data of employees whose last name is "Smith" or "Doe".

Click to see solution
```/* 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.

Click to see solution
```SELECT * FROM Employees WHERE Department = 14;
```

6. Select all the data of employees that work in department 37 or department 77.

Click to see solution
```/* 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".

Click to see solution
```SELECT * FROM Employees
WHERE LastName LIKE 'S%';
```

8. Select the sum of all the departments' budgets.

Click to see solution
```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).

Click to see solution
```SELECT Department, COUNT(*)
FROM Employees
GROUP BY Department;
```

10. Select all the data of employees, including each employee's department's data.

Click to see solution
```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.

Click to see solution
```/* 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.

Click to see solution
```/* 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.

Click to see solution
```SELECT *
FROM Departments
WHERE Budget >
(
SELECT AVG(Budget)
FROM Departments
);
```

14. Select the names of departments with more than two employees.

Click to see solution
```/*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.

Click to see solution
```/* 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.

Click to see solution
```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%.

Click to see solution
```UPDATE Departments SET Budget = Budget * 0.9;
```

18. Reassign all employees from the Research department (code 77) to the IT department (code 14).

Click to see solution
```UPDATE Employees SET Department = 14 WHERE Department = 77;
```

19. Delete from the table all employees in the IT department (code 14).

Click to see solution
```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.

Click to see solution
```DELETE FROM Employees
WHERE Department IN
(
SELECT Code FROM Departments
WHERE Budget >= 60000
);
```

21. Delete from the table all employees.

Click to see solution
```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);
```