SQL Exercises/Employee management

Relational Schema

Select * from Employees

Exercises

Select the last name of all employees.

Click to see solution

SELECT LastName FROM Employees;

Select the last name of all employees, without duplicates.

Click to see solution

SELECT DISTINCT LastName FROM Employees;

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

Click to see solution

SELECT * FROM Employees WHERE LastName = 'Smith';

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');

Select all the data of employees that work in department 14.

Click to see solution

SELECT * FROM Employees WHERE Department = 14;

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);

Select all the data of employees whose last name begins with an "S".

Click to see solution

SELECT * FROM Employees
  WHERE LastName LIKE 'S%';

Select the sum of all the departments' budgets.

Click to see solution

SELECT SUM(Budget) FROM Departments;

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;

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

Click to see solution

SELECT *
  FROM Employees INNER JOIN Departments
  ON Employees.Department = Departments.Code;

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;

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);

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
  );

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;

Select the name and last name of employees working for departments with second lowest budget.

Click to see solution

/* 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

   )
 );

Add a new department called "Quality Assurance", with a budger of $40,000 and departmental code 11. Add an employee called "Mary Moore" in that department, with SSN 847-2198-110.

Click to see solution

INSERT INTO Departments
  VALUES ( 11 , 'Calidad' , 40000);

INSERT INTO Employees
  VALUES ( '89267109' , 'Mary' , 'Moore' , 11);

Reduce the budget of all departments by 10%.

Click to see solution

UPDATE Departments SET Budget = Budget * 0.9;

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;

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

Click to see solution

DELETE FROM Employees
  WHERE Department = 14;

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
  );

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 TEXT NOT NULL ,
   Budget REAL NOT NULL 
 );
 
 CREATE TABLE Employees (
   SSN INTEGER PRIMARY KEY NOT NULL,
   Name TEXT NOT NULL ,
   LastName TEXT NOT NULL ,
   Department INTEGER 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','ODonnell',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);