Queries on the Garden Glory database

User Generated

noqhyynu666

Computer Science

Description

Write SQL statements (queries) to answer the following questions:

  • Write an SQL statement to list all columns and all rows for the table Service. Save the query results as XXXQ1.
  • Write an SQL statement to list LastName, FirstName, and CellPhone for all employees having an experience level of Master. Save the query results as XXXQ2
  • Write an SQL statement to list the names of employees who have worked on a property in New York. Use a subquery. Save the query results as XXXQ3.
  • Answer question 3 but use a join. Save the query results as XXXQ4.
  • Write an SQL statement to list the names of employees who have worked on a property owned by a corporation. You can use either subquery or join for this question. Save the query results as XXXQ5.
  • Write an SQL statement to show the first name and sum of hours worked for each employee. Save the query results as XXXQ6.
  • Write an SQL statement to show the sum of hours worked for each ExperienceLevel of EMPLOYEE. Sort the results by ExperienceLevel, in descending order. Save the query results as XXXQ7.
  • 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. Save the query results as XXXQ8.

Unformatted Attachment Preview

IT310 Assignment 4 SQL: DML Queries on the Garden Glory database Due Monday, Nov. 26th, 2018 Downlod the Garden Glory database from the Assignment 4 link. Garden Glory designs a database with the following tables: OWNER ( OwnerID, OwnerName, OwnerEmail, OwnerType) PROPERTY ( PropertyID, PropertyName, Street, City, State, Zip, OwnerID) EMPLOYEE ( EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel) SERVICE ( PropertyID, EmployeeID, SeviceDate, HoursWorked) The referential integrity constraints are: OwnerID in PROPERTY must exist in OwnerID in OWNER PropertyID in SERVICE must exist in PropertyID in PROPERTY EmployeeID in SERVICE must exist in EmployeeID in EMPLOYEE Write SQL statements (queries) to answer the following questions: 1. Write an SQL statement to list all columns and all rows for the table Service. Save the query results as XXXQ1. 2. Write an SQL statement to list LastName, FirstName, and CellPhone for all employees having an experience level of Master. Save the query results as XXXQ2 3. Write an SQL statement to list the names of employees who have worked on a property in New York. Use a subquery. Save the query results as XXXQ3. 4. Answer question 3 but use a join. Save the query results as XXXQ4. 5. Write an SQL statement to list the names of employees who have worked on a property owned by a corporation. You can use either subquery or join for this question. Save the query results as XXXQ5. 6. Write an SQL statement to show the first name and sum of hours worked for each employee. Save the query results as XXXQ6. 7. Write an SQL statement to show the sum of hours worked for each ExperienceLevel of EMPLOYEE. Sort the results by ExperienceLevel, in descending order. Save the query results as XXXQ7. 8. 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. Save the query results as XXXQ8.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

HEY AM THROUGH. EVERYTHING IS IN THE ATTACHED FILE

IT310 Assignment 4 SQL: DML
Queries on the Garden Glory database
Due Monday, Nov. 26th, 2018

Downlod the Garden Glory database from the Assignment 4 link. Garden Glory designs
a database with the following tables:
OWNER ( OwnerID, OwnerName, OwnerEmail, OwnerType)
PROPERTY ( PropertyID, PropertyName, Street, City, State, Zip, OwnerID)
EMPLOYEE ( EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel)
SERVICE ( PropertyID, EmployeeID, SeviceDate, HoursWorked)
The referential integrity constraints are:
OwnerID in PROPERTY must exist in OwnerID in OWNER
PropertyID in SERVICE must exist in PropertyID in PROPERTY
EmployeeID in SERVICE must exist...


Anonymous
Excellent resource! Really helped me get the gist of things.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags