employee (Lastname, FirstName, MidInitial, gender, street, city) works (Lastname

Question

employee (Lastname, FirstName, MidInitial, gender, street, city)

works (Lastname, FirstName, MidInitial, company_name, salary)

company (company_name, city)

manages(Lastname, FirstName, MidInitial, ManagerLastname, MFirstName, MMidInitial, start-date)

Using the above tables answer the queries in SQL

Q10. Find the names of the companies which have employees residing in every city where employees of Mutual of Omaha live.

Q11. Find all employees who earn less than the average salary of all employees of their own companies.

Q12. (a) Give all managers a 4% pay raise while give all other employees a 6% pay raise (do not use the view ManagerInfo defined above) and display the result. (b) Undo this update (so that database is actually not changed – for future use

Explanation / Answer

Q10. Find the names of the companies which have employees residing in every city where employees of Mutual of Omaha live.

select company_name
from company
inner join employee on employee.city = company.city
inner join works on works.company_name = company.company_name
where city.employee = 'Mutual of Omaha';

Q11. Find all employees who earn less than the average salary of all employees of their own companies.

select Lastname, FirstName
from works works1
where salary < (select avg(salary)
from works works2
where works1.company_name = works2.company_name);

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

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