OWNER ( OwnerID, OwnerName, OwnerEmail, OwnerType ) PROPERTY ( PropertyID, Prope

Question

OWNER ( OwnerID, OwnerName, OwnerEmail, OwnerType )
PROPERTY ( PropertyID, PropertyName, Street, City, State, Zip,OwnerID )
EMPLOYEE ( EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel )
SERVICE ( PropertyID, EmployeeID, ServiceDate, HoursWorked )

Write an SQL statement to show the sum of hours worked for each type of OWNER but exclude services of employees who have ExperienceLevel of Junior and exclude any type with less than three members. The result should look like the following:

Explanation / Answer

SELECT OwnerType, SUM(HoursWorked) AS SumOfHoursWorked
FROM EMPLOYEE, SERVICE, OWNER
WHERE EMPLOYEE.EmployeeID = SERVICE.EmployeeID
AND EMPLOYEE.ExperienceLevel <> 'Junior'
AND EMPLOYEE.ExperienceLevel IN(SELECT ExperienceLevel FROM Employee HAVING COUNT(DISTINCT(ExperienceLevel))> 2)

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

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