VOOZH about

URL: https://en.wikibooks.org/wiki/SQL_Exercises/Employee_management

⇱ SQL Exercises/Employee management - Wikibooks, open books for an open world

Jump to content
From Wikibooks, open books for an open world

Relational Schema

[edit | edit source]

👁 Image

Exercises

[edit | edit source]

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

[edit | edit source]
CREATETABLEDepartments(
CodeINTEGERPRIMARYKEYNOTNULL,
NameVARCHARNOTNULL,
BudgetREALNOTNULL
);

CREATETABLEEmployees(
SSNINTEGERPRIMARYKEYNOTNULL,
NameTEXTNOTNULL,
LastNameVARCHARNOTNULL,--since question 2 asks about removing duplicate - text must be converted if the answer is using distinct
DepartmentINTEGERNOTNULL,
CONSTRAINTfk_Departments_CodeFOREIGNKEY(Department)
REFERENCESDepartments(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

[edit | edit source]
INSERTINTODepartments(Code,Name,Budget)VALUES(14,'IT',65000);
INSERTINTODepartments(Code,Name,Budget)VALUES(37,'Accounting',15000);
INSERTINTODepartments(Code,Name,Budget)VALUES(59,'Human Resources',240000);
INSERTINTODepartments(Code,Name,Budget)VALUES(77,'Research',55000);

INSERTINTOEmployees(SSN,Name,LastName,Department)VALUES('123234877','Michael','Rogers',14);
INSERTINTOEmployees(SSN,Name,LastName,Department)VALUES('152934485','Anand','Manikutty',14);
INSERTINTOEmployees(SSN,Name,LastName,Department)VALUES('222364883','Carol','Smith',37);
INSERTINTOEmployees(SSN,Name,LastName,Department)VALUES('326587417','Joe','Stevens',37);
INSERTINTOEmployees(SSN,Name,LastName,Department)VALUES('332154719','Mary-Anne','Foster',14);
INSERTINTOEmployees(SSN,Name,LastName,Department)VALUES('332569843','George','O''Donnell',77);
INSERTINTOEmployees(SSN,Name,LastName,Department)VALUES('546523478','John','Doe',59);
INSERTINTOEmployees(SSN,Name,LastName,Department)VALUES('631231482','David','Smith',77);
INSERTINTOEmployees(SSN,Name,LastName,Department)VALUES('654873219','Zacary','Efron',59);
INSERTINTOEmployees(SSN,Name,LastName,Department)VALUES('745685214','Eric','Goldsmith',59);
INSERTINTOEmployees(SSN,Name,LastName,Department)VALUES('845657245','Elizabeth','Doe',14);
INSERTINTOEmployees(SSN,Name,LastName,Department)VALUES('845657246','Kumar','Swamy',14);
Category: