Chapter four: 4.2

Database:

employee (employee-name, street, city)
works (employee-name, company-name, salary)
company (company-name, city)
managers (employee-name, manager-name)

 

Queries:

4.2a: The names of the employees working for FBC

4.2.b: The names and cities of residence of all employees working for FBC

4.2c: The names, street addresses and cities of residence of all employees working for FBC and earn more than $10,000

4.2d: All employees who live in the same cities as the companies they work for

4.2e: All employees who live in the same cities and on the same streets as their managers

4.2f: All employees who do not work for FBC (multiple and single employer cases)

4.2g: All employees who earn more than every employee of SBC

4.2h: All companies located in every city in which SBC is located (companies could be located in several cities)

Solutions:

4.2a The names of the employees working for FBC.

SELECT employee-name
FROM works
WHERE company-name = "FBC" ;

4.2.b The names and cities of residence of all employees working for FBC.

(Obs.: single job assumption for single employee)

SELECT e.employee-name, e.city
FROM employee AS e, works AS w
WHERE w.employee-name = e.employee-name AND company-name = "FBC" ;

(Obs.: multiple job assumption; only work for FBC)

SELECT e.employee-name, e.city
FROM employee AS e
WHERE e.employee-name not in

(SELECT w.employee-name
FROM works AS w
WHERE e.employee-name = w.employee-name AND company-name <> "FBC") ;

4.2c The names, street addresses and cities of residence of all employees working for FBC and earn more than $10,000.

(Obs.: single job assumption)

SELECT e.employee-name, e.street, e.city
FROM employee AS e, works AS w
WHERE w.employee-name = e.employee-name AND company-name = "FBC" AND salary >10000;

(Obs.: multiple job assumption)

SELECT e.employee-name, e.street, e.city

FROM employee AS e

WHERE exists

(SELECT w.employee-name

FROM works AS w

WHERE w.employee-name=e.employee-name AND w.company-name="FBC" AND w.employee-name in

(SELECT e.employee-name

FROM works AS w

GROUP BY w.employee-name

HAVING SUM(salary) > 10000));

4.2d All employees who live in the same cities as the companies they work for.

SELECT e.employee-name
FROM employee AS e, works AS w, company AS c
WHERE w.employee-name = e.employee-name AND w.company-name = c.company-name AND e.city = c.city ;

4.2e All employees who live in the same cities and on the same streets as their managers.

(Obs.: in the table Manager, you can find the employee-names of the employees who have a certain manager, named manager-name. Because of this you don't need to use the table works to make the connection between the company of the employee and the company of the manager.)

SELECT e.employee-name
FROM employee AS e, employee AS e1, manager AS m
WHERE m.employee-name = e.employee-name AND m.manager-name = e1.employee-name AND e.city = e1.city AND e.street = e1.street ;

4.2f All employees who do not work for FBC.

(Obs.: only one job per employee allowed)

SELECT employee-name
FROM works
WHERE company-name <> "FBC" ;

(Obs.: multiple jobs per employee allowed)

SELECT e.employee-name
FROM employee AS e
WHERE e.employee-name not in

(SELECT employee-name
FROM works
WHERE company-name = "FBC") ;

4.2g All employees who earn more than every employee of SBC.

SELECT employee-name
FROM works
WHERE salary >

(SELECT max(salary)
FROM works
WHERE company-name = "SBC") ;

 

 

SELECT employee-name
FROM works
WHERE salary > ALL

(SELECT salary
FROM works
WHERE company-name = "SBC") ;

4.2h All companies located in every city in which SBC is located (companies could be located in several cities).

(Obs.: incorrect because you compare c.city (one city) with a group of cities. Use ALL only for numbers.)

SELECT c.company-name

FROM company AS c

WHERE c.city = ALL

(SELECT c.city

FROM company AS c

WHERE c.company-name="SBC");

(Obs.: there exists no company in a city where SBC has no company in that city as well )

SELECT c.company-name

FROM company AS c

WHERE not exists

(SELECT c1.city

FROM company AS c1

WHERE c1.company-name = c.company-name AND not exists

(SELECT *

FROM company AS c2

WHERE c2company-name="SBC" AND c2.city=c1.city));

 

 

SELECT c.company-name

FROM company AS c

WHERE not exists

(SELECT c1.city

FROM company AS c1

WHERE c1.company-name = "SBC" AND c1.city not in

(SELECT city

FROM company AS c

WHERE c.company-name=c1.company-name));