HW 3 data

IT 240 Name:___________

HW 3

Worth 20 points

Instructions:

1. Download and save this document

2. Place your full name on it.

3. Type your answers directly under each question, Leaving the questions. Do Not delete the questions.

4. Submit this document and your Access DB

Part I Garden Glory Project

Using the Garden Glory DB you created for HW 2, Write SQL statements for the following.

If you were unable to create the DB from HW 2, please contact me.

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

B. 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.

C. Write an SQL statement to list LastName, FirstName, and CellPhone of employees who have worked on a property in Seattle. Use a subquery.

D. Answer question G but use a join using JOIN ON syntax. What are the consequences of using (or not using) the DISTINCT keyword in this version of the query?

E. 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.

F. Answer question I but use a join using JOIN ON syntax. What are the consequences of using (or not using) the DISTINCT keyword in this version of the query?

G. Write an SQL statement to show the LastName, FirstName, CellPhone, and sum of hours worked for each employee.

H. Write an SQL statement to show the sum of hours worked for each ExperienceLevel of EMPLOYEE. Sort the results by ExperienceLevel, in descending order.

I. 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.

J. Write an SQL statement to modify all EMPLOYEE rows with ExperienceLevel of Master to SuperMaster.

K. 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

L. 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. Do not run these statements if you are using an actual database!

Command not run on the actual database as per question.

II. Joins

For the following joins, use the San Juan Sailboat Charters DB.

A. Cross-Join

Note: Cross joins are used to return every combination of rows from two tables, this sometimes called a Cartesian product.

A. subject database – San Juan Sailboat CHARTERS

B. database system -- Access

C. join-query type – cross-join

D. items in result set THE FOLLOWING NUMBER OF ROWS:

F. SQL query executed (copy of the SQL query text)

b. Equi-Join

Note: Uses an equality operator. Performs a JOIN against matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality. An equi join can also be an inner, left outer, and right outer.

A. subject database – San Juan Sailboat CHARTERS

B. database system -- Access

C. join-query type – cross-join

D. items in result set HAS THE FOLLOWING NUMBER OF ROWS: F. SQL query executed (copy of the SQL query text)

c. Inner-Join

Note: Both inner and outer joins can be used to combine data from two or more tables

(matching the foreign key of one table to the primary key of another,)

Inner joins don’t include non-matching rows; whereas, outer joins do include them.

The INNER JOIN specifies which tables to join and the match condition for doing so

A. subject database – San Juan Sailboat CHARTERS

B. database system -- Access

C. join-query type – cross-join

D. items in result set has the following number of rows:

F. SQL query executed (copy of the SQL query text)