3.16

Consider the employee database of Figure 3.19, where the primary keys are underlined. Given an expression in SQL for each of the following queries.

  1. Find ID and name of each employee who lives in the same city as the location of the company for which the employee works.
  2. Find ID and name of each employee who lives in the same city and on the same street as does her or his manager.
  3. Find ID and name of each employee who earns more than the average salary of all employees of her or his company.
  4. Find the company that has the smallest payroll.
  1. Find ID and name of each employee who lives in the same city as the location of the company for which the employee works.
SELECT employee.id, employee.person_name
FROM employee INNER JOIN works ON employee.id = works.id
              INNER JOIN company ON works.company_name = company.company_name
WHERE employee.city = company.city
  1. Find ID and name of each employee who lives in the same city and on the same street as does her or his manager.
SELECT E.id, E.person_name
FROM employee AS E INNER JOIN manages ON E.id = manages.id
                   INNER JOIN employee AS manager_of_E ON manages.manager_id = manager_of_E.id
WHERE E.street = manager_of_E.street AND 
      E.city = manager_of_E.city;
  1. Find ID and name of each employee who earns more than the average salary of all employees of her or his company.
WITH average_salary_per_company(company_name, avg_salary) AS (
    SELECT company_name, AVG(salary) 
    FROM works
    GROUP BY company_name
) 
SELECT E.id, E.person_name
FROM employee AS E INNER JOIN works ON E.id = works.id
WHERE works.salary > (
    SELECT avg_salary 
    FROM average_salary_per_company 
    WHERE company_name = works.company_name
)
  1. Find the company that has the smallest payroll.
SELECT company_name, SUM(salary) AS total_payroll
FROM works
GROUP BY company_name
ORDER BY total_payroll ASC
LIMIT 1