A. Write CREATE TABLE statements for each of the tables. Omit foreign keys. Hint: Choose Data Definition type in Access.

[answerhappygod]

A. Write CREATE TABLE statements for each of the tables.
Omit foreign keys. Hint: Choose Data Definition type in Access. a.
OWNER b. OWNED_PROPERTY c. EMPLOYEE d. GG_SERVICE e.
PROPERTY_SERVICE
B. Copy each query and add the FK constraints specified
in the textbook, included below. Save the resulting queries as new
DDL queries. a. OwnerID in OWNED_PROPERTY must exist in OwnerID in
OWNER b. PropertyID in PROPERTY_SERVICE must exist in PropertyID in
OWNED_PROPERTY c. ServiceID in PROPERTY_SERVICE must exist in
ServiceID in GG_SERVICE d. EmployeeID in PROPERTY_SERVICE must
exist in EmployeeID in EMPLOYEE
C. Write SQL Statements to insert data into each of the
five Garden Glory database tables. Assume that any surrogate key
value will be supplied by the DBMS. Use the data in the attached
Excel sheet.
D. Choose ONE of the tables and write a query to select
all columns from that table
E. Write an SQL statement to list LastName, FirstName,
and CellPhone for all employees having an experience level of
Master.
F. Write 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.
H. Write an SQL statement to list LastName, FirstName,
and CellPhone of employees who have worked on a property in
Seattle. Use a subquery.
I. Answer question H but use a JOIN ON
syntax.
J. Write an SQL statement to list LastName, FirstName,
and CellPhone of employees who have worked on a property owned by a
corporation. Use a subquery.
K. Answer the previous question using a JOIN ON
syntax.
L. Write an SQL statement to show the LastName,
FirstName, CellPhone, and sum of hours worked for each
employee.
M. Write an SQL statement to show the sum of hours
worked for each ExperienceLevel of EMPLOYEE. Sort the results by
ExperienceLevel, in descending order.
N. 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.
O. Write an SQL statement to modify all EMPLOYEE rows
with ExperienceLevel of Master to SuperMaster (Hint: you may want
to take a backup of this database).
P. Write SQL statements to switch the values of
ExperienceLevel so that all rows currently having the value Junior
will have the value Senior and all rows currently having the value
Senior will have the value Junior. Hint. Use the value Unknown as a
temporary third value
Q. Given your assumptions about cascading deletions in
your answer to part B, write the fewest number of DELETE statements
possible to remove all the data in your database but leave the
table structures intact. Be careful not to run these unless you
have backups so you don’t lose prior work!
Service ID ServiceDescription
CostPerHour
1 Mow Lawn 25
2 Plant annuals 25
3 Weed Garden 30
4 Trim Hedge 45
5 Prune Small Tree 60
6 Trim Medium Tree 100
7 Trim large tree 125