Given the following employee database: Employee ( employee_name , street, city)

Question

Given the following employee database:

Employee (employee_name, street, city)

Works (employee_name, company_name, salary)

Company (company_name, city)

Manages (employee_name, manager_name)

Where the primary keys are underlined, give an expression in SQL for each of the following queries:

(a)         Find the names and cities of residence of all employees who work for ABC Corporation.

(b)         Find the names, street addresses, and cities of residence of all employees who work for ABC Corporation and earn more than $10,000.

(c)         Find all employees in the database who do not work for ABC Corporation.

(d)         Assume that the companies may be located in several cities. Find all companies located in every city in which ABC Corporation is located.

(e)         Find the company that has the most employees.

Explanation / Answer

Please rate..

Find the names and cities of residence of all employees who work for ABC Corporation

Select Distinct E.employee_name From Employee E, Works W, Company C where E.employee_name=W.employee_name and E.city=C.city and W.company_name=C.company_name and W.company_name='ABC'

Find the names, street addresses, and cities of residence of all employees who work for ABC Corporation and earn more than $10,000

Select E.employee_name, E.street,E.city From Employee E, Works W where E.employee_name=W.employee_name and W.company_name='ABC' and W.salary>10000

Find all employees in the database who do not work for ABC Corporation.

Select E.employee_name, E.city From Employee E, Works W where E.employee_name=W.employee_name and W.company_name not in ('ABC')

Assume that the companies may be located in several cities. Find all companies located in every city in which ABC Corporation is located

Select C.company_name from Company C Where C.city in ( Select Inner.city from Company Inner where Inner.company_name='ABC')

Find the company that has the most employees.

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)

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.