Schema: Lives(person_name, street, city) Works(person_name, company_name, salary) Located_in(company_name,
Expert Answer
OR
Schema:
Lives(person_name, street, city) Works(person_name, company_name, salary) Located_in(company_name, city) Manages(person_name, manager_name)
(a) Find the name of all employees who work for BofA
(b) Find the name and city of all employees who work for BofA
(c) Find the name, street, and city of all employees who work for BofA and earn more than $40,000.
(d) Find all employees who live in the same city as the company they work for.
(e) Find all employees who live in the same city and on the same street as their manager. (f) Find all employees who do not work for BofA
(g) Find all employees who earn more than every employee of Citicorp.
(h) Assume the companies may be located in several cities. Find all companies located in every city in which BofA is located.
1st step
All steps
Answer only
Step 1/3
The answer when Schema is Lives(person_name, street, city) Works(person_name, company_name, salary) Located_in(company_name, city) Manages(person_name, manager_name) is provided below :
(a) To find the name of all employees who work for BofA, we can use the Works relation and select the person_name for all entries where the company_name is BofA.
SELECT person_name FROM Works WHERE company_name = ‘BofA’
(b) To find the name and city of all employees who work for BofA, we can join the Lives and Works relations on person_name and select the person_name and city columns.
SELECT person_name, city FROM Lives
JOIN Works ON Lives.person_name = Works.person_name
WHERE Works.company_name = ‘BofA’
(c) To find the name, street, and city of all employees who work for BofA and earn more than $40,000, we can add a salary condition in the above query.
SELECT person_name, street, city FROM Lives
JOIN Works ON Lives.person_name = Works.person_name
WHERE Works.company_name = ‘BofA’ AND Works.salary > 40000