For the following relation schema:
Employee (employee-name, street, city)
Works (employee-name, company-name, salary)
Company (company-name, city)
Manages (employee-name, manager-name)
Give an expression in SQL for each of the following queries:
1. Find the names, street address, and cities of residence for all employees who work for 'First Bank Corporation' and earn more than $10,000.
SELECT employee-name, street, city
FROM Employee
JOIN Works ON Employee.employee-name = Works.employee-name
WHERE Works.company-name = 'First Bank Corporation' AND Works.salary > 10000;
2. Find the names of all employees in the database who live in the same cities as the companies for which they work.
SELECT employee-name
FROM Employee
JOIN Works ON Employee.employee-name = Works.employee-name
JOIN Company ON Works.company-name = Company.company-name
WHERE Employee.city = Company.city;
3. Find the names of all employees in the database who live in the same cities and on the same streets as do their managers.
SELECT employee-name
FROM Employee
JOIN Works ON Employee.employee-name = Works.employee-name
JOIN Manages ON Employee.employee-name = Manages.employee-name
JOIN Employee AS Manager ON Manages.manager-name = Manager.employee-name
WHERE Employee.city = Manager.city AND Employee.street = Manager.street;
4. Find the names of all employees in the database who do not work for 'First Bank Corporation'. Assume that all people work for exactly one company.
SELECT employee-name
FROM Employee
WHERE employee-name NOT IN (SELECT employee-name FROM Works WHERE company-name = 'First Bank Corporation');
5. Find the names of all employees in the database who earn more than every employee of 'Small Bank Corporation'. Assume that all people work for at most one company.
SELECT employee-name
FROM Employee
JOIN Works ON Employee.employee-name = Works.employee-name
WHERE Works.salary > (SELECT MAX(salary) FROM Works WHERE company-name = 'Small Bank Corporation');
6. Assume that the companies may be located in several cities. Find all companies located in every city in which 'Small Bank Corporation' is located.
SELECT company-name
FROM Company
WHERE city IN (SELECT city FROM Company WHERE company-name = 'Small Bank Corporation')
GROUP BY company-name
HAVING COUNT(DISTINCT city) = (SELECT COUNT(DISTINCT city) FROM Company WHERE company-name = 'Small Bank Corporation');
7. Find the names of all employees who earn more than the average salary of all employees of their company. Assume that all people work for at most one company.
SELECT employee-name
FROM Employee
JOIN Works ON Employee.employee-name = Works.employee-name
WHERE Works.salary > (SELECT AVG(salary) FROM Works WHERE company-name = Works.company-name);
8. Find the name of the company that has the smallest payroll.
SELECT company-name
FROM Works
GROUP BY company-name
HAVING SUM(salary) = (SELECT MIN(total_salary) FROM (SELECT SUM(salary) AS total_salary FROM Works GROUP BY company-name) AS subquery);