Consider the following relational database where the primary keys are as indicat

Question

Consider the following relational database where the primary keys are as indicated

person(person name, street, city) // records about all people (primary key is person name)
works(person name, company name, annual salary) // records about employed people(primary key is person name)
company(company name, founded)(primary key is company name)
company location(company name, city) // companies and cities where they have offices(primary keys are company name and city)
managed by(person name, manager name)(primary key is person name)
Give an expression in SQL for each of the following queries:
(a) Find the average salary of all the employees living in the city of Albany.
(b) Find the maximum salary of all employees who are not managers.
(c) Find all the companies that have offices in all the cities in which company C2 has an offices
(d) Find the year in which the largest number of companies were founded.
(e) Give all the managers in the database a 10 percent salary raise. Give all the other employees a 5 percent salary raise.
(f) Find the names of all unemployed people (use an outer join).

Explanation / Answer

a) SQL> Select Avg(annual salary) From works w,person p where city='Albany' and w.person name=p.person name; b) SQL> Select Max(annual salary) from works where person name NOT IN (select person name from manages); e) SQL> Update works w Set w.annual salary = w.annual salary * 0.1 Where w.employee-name in (select manager-name from manages) SQL> Update works w Set w.annual salary = w.salary * 0.05 Where T.employee-name NOT IN (select manager-name from manages);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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