SQL Exercises/Employee management

From Wikibooks, open books for an open world
Jump to navigation Jump to search

Relational Schema

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 SSN, E.Name AS Name_E, LastName, D.Name AS Name_D, Department, Code, Budget
 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 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 NVARCHAR NOT NULL ,
   Budget REAL NOT NULL 
 );
 
 CREATE TABLE Employees (
   SSN INTEGER PRIMARY KEY NOT NULL,
   Name TEXT NOT NULL ,
   LastName NVARCHAR 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);