Employee ( Lastname , FirstName , MidInitial , gender, street, city) Works ( Las

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)

Note: Bold - Primary Key

bold/italics - foreign key

Write SQL queries for the following:-

Q1. Find all employees in the database who live in the same cities and on the same streets as do their managers.

Q2. Find the names of the companies which have employees residing in all cities where employees of Mutual of Omaha (MOO) live.(So if MOO has employees living in three cities, each company returned by this query must have employees living in all these three cities.)

Q3. For those employees whose last name initial is not a character “C” and who earn less than the average salary of all employees of their own companies,

find their full names and cities they live.

Explanation / Answer

Q1 : select * from Employee E,Manages M where E.Lastname=M.Lastname and E.FirstName=M.FirstName and E.MidInitial=M.MidInitial

Q2 : select C1.company-name from company where C1.city in (select E.city from Employee E where E.city in (select Distinct city C2 from company where C2.company-name='Mutual of Omaha' ))

Q3 : select E.Lastname, E.FirstName, E.MidInitial,W.city from Employee E,Works W where E.Lastname=W.Lastname and E.FirstName=W.FirstName and E.MidInitial=W.MidInitial and w.salary < = (select AVG(salary) from Works ) and E.Lastname NOT LIKE 'C%'

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

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