Create queries for employee database

Create queries for employee database

Solution 

5.1)      SELECT * FROM EMPLOYEE;

SELECT * FROM OWNER;

SELECT * FROM PROPERTY;

SELECT * FROM SERVICE;

5.2)      SELECT LastName, FirstName, CellPhone FROM EMPLOYEE WHERE ExperienceLevel=’Master’;

5.3)      SELECT LastName, FirstName, CellPhone FROM EMPLOYEE WHERE ExperienceLevel=’Master’ AND FirstName LIKE ‘J*’;

5.4)      SELECT E.LastName, E.FirstName

FROM EMPLOYEE E INNER JOIN (PROPERTY P INNER JOIN SERVICE S ON P.PropertyID = S.PropertyID) ON E.EmployeeID = S.EmployeeID

WHERE P.State=’NY’

GROUP BY E.EmployeeID, E.LastName, E.FirstName;

5.5)      SELECT E.LastName, E.FirstName FROM EMPLOYEE E, PROPERTY P, SERVICE S, OWNER O

WHERE E.EmployeeID=S.EmployeeID AND P.PropertyID=S.PropertyID AND P.OwnerID=O.OwnerID AND O.Type=’Corporation’

GROUP BY E.EmployeeID, E.LastName, E.FirstName;

5.6)      SELECT E.LastName, E.FirstName, SUM(S.HoursWorked) as ‘Sum of HoursWorked’ FROM EMPLOYEE E, SERVICE S

WHERE E.EmployeeID=S.EmployeeID

GROUP BY E.EmployeeID, E.LastName, E.FirstName;

5.7)      SELECT E.ExperienceLevel, SUM(S.HoursWorked) as ‘Sum of HoursWorked’ FROM EMPLOYEE E, SERVICE S

WHERE E.EmployeeID=S.EmployeeID

GROUP BY E.ExperienceLevel ORDER BY E.ExperienceLevel DESC;

5.8)      SELECT O.Type, SUM(S.HoursWorked) as ‘Sum of HoursWorked’ FROM EMPLOYEE E, PROPERTY P, SERVICE S, OWNER O

WHERE E.EmployeeID=S.EmployeeID AND P.PropertyID=S.PropertyID AND P.OwnerID=O.OwnerID AND E.ExperienceLevel<>’Junior’

GROUP BY O.Type

HAVING COUNT(E.EmployeeID)>2;