Question

Hi. Can someone please help me with this SQL question? I asked this before, but someone just cut and pasted a completely incorrect answer that they found online.
Please be genuine in your response.

Write an SQL statement to show the sum of HoursWorked for each type of OWNER but exclude services of employees who have ExperienceLevel of Junior.
OWNER ( OwnerID, OwnerName, OwnerEmail, OwnerType )
PROPERTY_TYPE ( PropertyID, PropertyName, PropertyType, Street, City, State, Zip,OwnerID )
PROPERTY_SERVICE (PropertyServiceID, PropertyID, ServiceID, ServiceDate, EmployeeID, HoursWorked)
EMPLOYEE ( EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel )
GG_SERVICE ( ServiceID, ServiceDescription, CostPerHour )

This is what I've written so far, but apparently it's only 50% correct, as the rows don't match.
SELECT OwnerType, SUM(HoursWorked) AS TotalHoursWorked
FROM EMPLOYEE E, PROPERTY_SERVICE P, OWNER O
WHERE E.EmployeeID = P.EmployeeID
AND E.ExperienceLevel <> 'Junior'

I don't know what to do to make it work. I've tried 10 different things over the past hour and a half, but it's still wrong. What I posted is the closest I've gotten to getting it correct. There is a hint that I need to use Natural Join, but I can't figure it out.
Can someone please help?

Answer 1

Please find the SQL Query below.

QUERY

SELECT o.OwnerType, sum(ps.HoursWorked) FROM OWNER o JOIN PROPERTY_TYPE pt ON o.OwnerID = pt.OwnerID JOIN PROPERTY_SERVICE ps ON pt.PropertyID = ps.PropertyID JOIN EMPLOYEE e ON ps.EmployeeID = e.EmployeeID WHERE e.ExperienceLevel != 'Junior' GROUP BY o.OwnerType;