1.  Explain an SQL statement to list LastName, FirstName, and CellPhone for all employees having an experience level of Master.

 

2. Explain an SQL statement to list LastName, FirstName, and CellPhone for all employees having an experience level of Master and FirstName that begins with the letter J.

 

3. Explain an SQL statement to list the LastName and FirstName of employees who have worked on a property in Seattle. Use a subquery.

 

4. Explain an SQL statement to list the LastName and FirstName of employees who have worked on a property in Seattle using JOIN ON syntax instead of a subquery.

 

5. Explain an SQL statement to list the LastName and FirstName of employees who have worked on a property owned by a corporation. Use a subquery.

 

6. Explain an SQL statement to list the LastName and FirstName of employees who have worked on a property owned by a corporation using JOIN ON syntax instead of a subquery.

 

7.  Explain an SQL statement to show the LastName and FirstName and sum of hours worked for each employee. Name the sum of hours field "TotalHoursWorked"

 

8. Explain an SQL statement to show the sum of hours worked for each ExperienceLevel of EMPLOYEE. Name the sum of hours field "TotalHoursWorked" Sort the results by ExperienceLevel, in descending order.

 

9. Explain an SQL statement to show the sum of HoursWorked for each type of OWNER but exclude services of employees who have ExperienceLevel of Junior. Name the sum of HoursWorked "TotalHoursWorked"
 
 

Transcribed Image Text:

FIGURE 3-30 Sample Data for the Garden Glory OWNER Table OwnerName OwnerID 1 2 3 4 5 Mary Jones DT Enterprises Sam Douglas UNY Enterprises Doug Samuels OwnerEmail Mary Jones@somewhere.com DTE@dte.com Sam.Douglas@somewhere.com UNYE@unye.com Doug.Samuels@somewhere.com Owner Type Individual Corporation Individual Corporation Individual FIGURE 3-31 Sample Data for the Garden Glory OWNED PROPERTY Table PropertyID PropertyName Property Type Street 1 Eastlake Building Office 123 Eastlake 2 Elm St Apts 3 Jefferson Hill 4 5 6 Lake View Apts Kodak Heights Apts Jones House Apartments Office Apartments Apartments 4 East Elm 42 West 7th St City Seattle WA 98119 Lynwood WA 98223 Bellevue WA State ZIP OwnerID 2 1 98007 2 1265 32nd Avenue Redmond WA 98052 3 65 32nd Avenue Redmond WA 98052 4 Private Residence 1456 48th St Bellevue WA 98007 1 7 Douglas House Private Residence 1567 51st St Bellevue WA 98007 3 8 Samuels House Private Residence 567 151st St Redmond WA 98052 5 FIGURE 3-30 Sample Data for the Garden Glory OWNER Table OwnerName OwnerID 1 2 3 4 5 Mary Jones DT Enterprises Sam Douglas UNY Enterprises Doug Samuels OwnerEmail Mary Jones@somewhere.com DTE@dte.com Sam.Douglas@somewhere.com UNYE@unye.com Doug.Samuels@somewhere.com Owner Type Individual Corporation Individual Corporation Individual FIGURE 3-31 Sample Data for the Garden Glory OWNED PROPERTY Table PropertyID PropertyName Property Type Street 1 Eastlake Building Office 123 Eastlake 2 Elm St Apts 3 Jefferson Hill 4 5 6 Lake View Apts Kodak Heights Apts Jones House Apartments Office Apartments Apartments 4 East Elm 42 West 7th St City Seattle WA 98119 Lynwood WA 98223 Bellevue WA State ZIP OwnerID 2 1 98007 2 1265 32nd Avenue Redmond WA 98052 3 65 32nd Avenue Redmond WA 98052 4 Private Residence 1456 48th St Bellevue WA 98007 1 7 Douglas House Private Residence 1567 51st St Bellevue WA 98007 3 8 Samuels House Private Residence 567 151st St Redmond WA 98052 5