Hello, I am working on SQL codes but I just can\'t figure out these remaining qu

Question

Hello, I am working on SQL codes but I just can't figure out these remaining questions. Would anyone be able to help?

5.4) Write an SQL statement to list the Name of employees who have worked on a property in New York .

5.5) Write an SQL statement to list the names of employees who have worked on a property owned by a Corporation .

5.6) Write an SQL statement to show the name and sum of HoursWorked for each employee.

5.7) Write an SQL statement to show the sum of HoursWorked for each ExperienceLevel of EMPLOYEE. Sort the results by ExperienceLevel in descending order.

5.8) Write an SQL statement to show the sum of HoursWorked for each Type of OWNER but exclude services of employees who have an ExperienceLevel of Junior and exclude any Type with less than three members.

EMPLOYEE EmployeeID LastName FirstName CellPhone ExperienceLevel 1 Murray Dale R. Murray Dale 206-254-3456 Senior 2 Murphy Jerry 585-545-8765 Master 3 Fontaine Joan 206-254-4567 Junior 4 Evanston John 206-254-2345 Junior 5 Smith Sam 206-254-1234 Master

Explanation / Answer

SELECT LastName, FirstName

FROM EMPLOYEE

WHERE EmployeeID

IN (SELECT EmployeeID FROM SERVICE WHERE EmployeeID IN (SELECT EmployeeID FROM PROPERTY WHERE City = 'Newyork'));

2)SELECT LastName, FirstName

FROM EMPLOYEE

WHERE EmployeeID

IN (SELECT EmployeeID FROM PROPERTY_SERVICE WHERE PropertyID

IN (SELECT PropertyID FROM OWNED_PROPERTY WHERE OwnerID IN (SELECT OwnerID FROM OWNER WHERE OwnerType = 'Corporation')));

3)SELECT LastName, FirstName, SUM(HoursWorked)

AS TotalHoursWorked FROM EMPLOYEE JOIN PROPERTY_SERVICE

ON EMPLOYEE.EmployeeID = PROPERTY_SERVICE.EmployeeID

GROUP BY LastName, FirstName ORDER BY LastName, FirstName;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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