/********************************************************************************/
/*                                                                              */
/*  Kroenke, Auer, Vaandenberg, Yoder - DBC (8th Edition) Chapter 08            */
/*                                                                              */
/*  Garden Glory Data Warehouse Database Case, Part G & H                       */
/*                                                                              */
/*  These are the Oracle Database XE code solutions                      */
/*                                                                              */
/********************************************************************************/
/* Copy and run these separately */
/* ******** Question G Query  ******************************************/
 
SELECT      E.EmployeeID, E.ExperienceLevel,
            P.PropertyID, P.City, P.State, P.Zip,
            SUM(SH.HoursWorked) AS TotalHoursWorked
FROM        EMPLOYEE E, SERVICE_HOURS SH, PROPERTY P
WHERE       E.EmployeeID = SH.EmployeeID
    AND     P.PropertyID = SH.PropertyID
GROUP BY    E.EmployeeID, E.ExperienceLevel,
            P.PropertyID, P.City, P.State, P.Zip
ORDER BY    E.EmployeeID, E.ExperienceLevel,
            P.State, P.City, P.Zip;
 
/* ******** Guestion H View Defintion  *********************************/
 
CREATE VIEW GGDWTotalServiceHoursView AS
    SELECT      E.EmployeeID, E.ExperienceLevel,
                P.PropertyID, P.City, P.State, P.Zip,
                SUM(SH.HoursWorked) AS TotalHoursWorked
    FROM        EMPLOYEE E, SERVICE_HOURS SH, PROPERTY P
    WHERE       E.EmployeeID = SH.EmployeeID
        AND     P.PropertyID = SH.PropertyID
    GROUP BY    E.EmployeeID, E.ExperienceLevel,
                P.PropertyID, P.City, P.State, P.Zip;
     
SELECT      *
FROM        GGDWTotalServiceHoursView
ORDER BY    EmployeeID, ExperienceLevel,
            State, City, Zip;
	
