1. List all employees and their departments. (employee_name, department_name)
    1
    2
    
    SELECT employee.name, department.name AS department
    FROM employee JOIN department ON id_dep = department.id
    
  2. List all departments and their directors. (department_name, director_name)
    1
    2
    
    SELECT department.name AS department, employee.name AS director
    FROM employee JOIN department ON id_dir = employee.id
    
  3. List all projects and the department to which they belong. (project_name, department_name)
    1
    2
    
    SELECT project.name, department.name AS department
    FROM project JOIN department ON id_dep = department.id
    
  4. What projects are controlled by the department number 1. (project_name)
    1
    2
    
    SELECT name
    FROM project WHERE id_dep = 1
    
  5. What projects are controlled by the Transports department. (project_name)
    1
    2
    3
    
    SELECT project.name
    FROM project JOIN department ON id_dep = department.id
    WHERE department.name = 'Transports'
    
  6. List all employees working on project XPTO and how many hours each one of them works in that project. (employee_name, hours)
    1
    2
    3
    4
    5
    
    SELECT employee.name, hours
    FROM project JOIN
      works ON id_pro = id JOIN
      employee ON id_emp = employee.id
    WHERE project.name = 'XPTO'
    
  7. List all employees working on project XPTO and how many hours each one of them works in that project. Order the list from the employee that works more hours to the one that works less. (employee_name, hours)
    1
    2
    3
    4
    5
    6
    
    SELECT employee.name, hours
    FROM project JOIN
      works ON id_pro = id JOIN
      employee ON id_emp = employee.id
    WHERE project.name = 'XPTO'
    ORDER BY hours DESC
    
  8. How many employees work in each project? (project_name, number)
    1
    2
    3
    
    SELECT name, COUNT(*)
    FROM project JOIN works ON id = id_pro
    GROUP BY id
    

    or

    1
    2
    3
    
    SELECT name, COUNT(id_emp)
    FROM project LEFT JOIN works ON id = id_pro
    GROUP BY id
    
  9. How many hours are spent weekly in each project? (project_name, hours)
    1
    2
    3
    
    SELECT name, SUM(hours)
    FROM project JOIN works ON id = id_pro
    GROUP BY id
    

    or

    1
    2
    3
    
    SELECT name, SUM(hours)
    FROM project LEFT JOIN works ON id = id_pro
    GROUP BY id
    
  10. How many hours does each employee spends on projects every week? (employee_name, hours)
    1
    2
    3
    
    SELECT name, SUM(hours)
    FROM employee JOIN works ON id = id_emp
    GROUP BY id
    
  11. List all employees and their superiors. (employee_name, superior_name)
    1
    2
    3
    
    SELECT employee.name, superior.name AS superior
    FROM employee JOIN
     employee AS superior ON employee.id_sup = superior.id
    
  12. List all employees and their superiors. If the employee does not have a superior list it anyway with a NULL superior. (employee_name, superior_name)
    1
    2
    3
    
    SELECT employee.name, superior.name AS superior
    FROM employee LEFT JOIN
     employee AS superior ON employee.id_sup = superior.id
    
  13. What is the largest salary in each department? (department_name, salary)
    1
    2
    3
    
    SELECT department.name, MAX(salary)
    FROM department JOIN employee ON department.id = id_dep
    GROUP BY department.id
    
  14. What is the highest salary in the whole company? (salary)
    1
    2
    
    SELECT MAX(salary)
    FROM employee
    
  15. What is the difference between the highest and lowest salaries in the company? (difference)
    1
    2
    
    SELECT MAX(salary) - MIN(salary) AS difference
    FROM employee
    
  16. What is the salary difference between each employee and his superior? (employee_name, superior_name, difference)
    1
    2
    3
    4
    
    SELECT employee.name, superior.name AS superior,
       superior.salary - employee.salary  AS difference
    FROM employee JOIN
     employee AS superior ON employee.id_sup = superior.id
    
  17. What is the biggest difference between the salary of an employee and his superior? (difference)
    1
    2
    3
    4
    5
    6
    
    SELECT MAX(difference)
    FROM (
      SELECT ABS(superior.salary - employee.salary) AS difference
      FROM employee JOIN
     employee AS superior ON employee.id_sup = superior.id
    ) AS t
    
  18. List the departments where the average salary is more than 1300 euros? (department_name)
    1
    2
    3
    4
    
    SELECT department.name, AVG(salary)
    FROM department JOIN employee ON id_dep = department.id
    GROUP BY department.id
    HAVING AVG(salary) > 1300
    
  19. List the employees who work on projects external to their department? (employee_name)
    1
    2
    3
    4
    5
    6
    
    SELECT employee.name
    FROM employee JOIN
     works ON id_emp = id JOIN
     project ON id_pro = project.id
    WHERE employee.id_dep <> project.id_dep
    GROUP BY employee.id
    
  20. List the employees who work on projects internal to their department? (employee_name)
1
2
3
4
5
6
SELECT employee.name
FROM employee JOIN
     works ON id_emp = id JOIN
     project ON id_pro = project.id
WHERE employee.id_dep = project.id_dep
GROUP BY employee.id
  1. List the employees who only work on projects internal to their department? (employee_name)
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    
    SELECT employee.name
    FROM employee JOIN
     works ON id_emp = id JOIN
     project ON id_pro = project.id
    WHERE employee.id_dep = project.id_dep AND
      employee.id NOT IN (
        SELECT employee.id
        FROM employee JOIN
          works ON id_emp = id JOIN
          project ON id_pro = project.id
        WHERE employee.id_dep <> project.id_dep
      )
    GROUP BY employee.id
    

(Credits: André Restivo https://web.fe.up.pt/~arestivo)