Consider the employee database of Figure 1, where the primary keys are underline
ID: 3556202 • Letter: C
Question
Consider the employee database of Figure 1, where the primary keys are underlined. Give an expression in SQL for each of the following queries.
Employee (employee-name, street, city)
Works (employee-name, company-name, salary)
Company (company-name, city)
Manages (employee-name, manager-name)
h.)Find all employees who earn more than the average salary of all employees of their company.
i.) Find the company that has the most employees.
j.) Find the company that has the smallest payroll.
k.)Find all employees in the database who live in the same cities and on the same streets as do their managers.
Explanation / Answer
h.) select employee_name from works w where salary > all(select avg(salary) from works where company_name = w.company_name);
i.) select company_name from works group by company_name having count(distinct employee_name) > all( select count(distinct employee_name) from works group by company_name);
j.) select company_name from works where salary=(select min(salary) from works);
k.) select emp.employee_name
from employee as emp , employee as emp2 , manages as man where emp1.employee_name=man.employee_name and emp2.employee_name=man.manager_name and emp.city=emp2.city and emp.street=emp2.street;