HW 3 data

IT 240 Name:__ZihanWang_________

HW 3

Worth 20 points

11.25/20

Please fix the font of the SQLs as they were extremely difficult to read.

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.

-.75 The results were incorrect; you rquotes are the wrong font, you did nto display the correct fields.

SELECT Employee.LastName, Employee.FirstName, Employee.CellPhone, Employee.Experience

FROM Employee

WHERE (((Employee.Experience)="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.

-.75 The results were incorrect; you rquotes are the wrong font, you did nto display the correct fields.

SELECT Employee.LastName, Employee.FirstName, Employee.CellPhone, Employee.Experience, Left([FirstName],1) AS Expr1

FROM Employee

WHERE (((Employee.Experience)="Master") AND ((Left([FirstName],1))='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.

Select em.FirstName,em.LastName, em.CellPhone from EMPLOYEE as em where

em.EmployeeID IN(

Select ps.EmployeeID from PROPERTY_SERVICE as ps where ps.PropertyID IN

(Select own.PropertyID from OWNED_PROPERTY as own where own.City='Seattle'))

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?

There are no changes in the result. The result remains the same.

-.5 Wrong result; DISTINCT does smatter

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.

-.5 displayed fields in the wrong order

Select em.FirstName,em.LastName, em.CellPhone from EMPLOYEE as em where

em.EmployeeID In(

Select Distinct ps.EmployeeID from PROPERTY_SERVICE as ps where ps.PropertyID IN

(Select ownd.PropertyID from OWNED_PROPERTY as ownd where ownd.OwnerID IN

(Select own.OwnerID from OWNER as own where own.OwnerType='Corporation')))

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?

There are no changes in the result. The result remains the same.

-.75 Wrong result; wrong fields diaplayed. Di dyou alter the contents of the tablea?

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

SELECT em.LastName, em.FirstName,em.CellPhone,

SUM(ps.HoursWorked) AS TotalHoursWorked

FROM PROPERTY_SERVICE as ps inner JOIN EMPLOYEE as em

ON em.EmployeeID = ps.EmployeeID

GROUP BY em.LastName, em.FirstName,em.Cellphone

ORDER BY em.LastName, em.FirstName;

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.

-1 wrong query name? the run query does not produce the desired reults.

SELECT em.LastName, em.FirstName,em.CellPhone,em.Experiencelevel,

SUM(ps.HoursWorked) AS TotalHoursWorked

FROM PROPERTY_SERVICE as ps inner JOIN EMPLOYEE as em

ON em.EmployeeID = ps.EmployeeID

GROUP BY em.LastName, em.FirstName,em.Cellphone,em.Experiencelevel

ORDER BY em.Experiencelevel desc;

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.

-1 wrong query name? the run query does not produce the desired reults.

SELECT em.LastName, em.FirstName, em.CellPhone, em.Experiencelevel, Sum(ps.HoursWorked) AS TotalHoursWorked

FROM EMPLOYEE AS em INNER JOIN PROPERTY_SERVICE AS ps ON em.EmployeeID = ps.EmployeeID

GROUP BY em.LastName, em.FirstName, em.CellPhone, em.Experiencelevel

HAVING ((Not (em.Experiencelevel)='Junior'))

ORDER BY em.Experiencelevel DESC;

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

-.5 did not use the necessary ALTER staements to adjust the constraints.

UPDATE EMPLOYEE

SET Experiencelevel = 'SuperMaster'

WHERE Experiencelevel = 'Master';

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 )

-.5 did not use the necessary ALTER staements to adjust the constraints.

UPDATE EMPLOYEE

SET ExperienceLevel =

[CASE ExperienceLevel

WHEN 'Junior' THEN 'Senior'

WHEN 'Senior' THEN 'Junior'

ELSE Experiencelevel

END]

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!

-.5 you need to delete the tables in the reverse order they were created.

Command not run on the actual database as per question.

Delete from EMPLOYEE

Delete from GG_SERVICE

Delete from OWNED_PROPERTY

Delete from OWNER

Delete from PROPERTY_SERVICE

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)

-2 I do not know what this is, but it isn’t a cross-join

TRANSFORM Avg(CHARTER.Amount) AS AvgOfAmount

SELECT CHARTER.BoatID

FROM BOAT INNER JOIN CHARTER ON BOAT.BoatID = CHARTER.BoatID

GROUP BY CHARTER.BoatID

PIVOT BOAT.BoatID;

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)

SELECT *

FROM CUSTOMER INNER JOIN CHARTER ON CUSTOMER.CustomerID = CHARTER.CustomerID;

c. Inner-Join

The data does not support the join requested.

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)

SELECT *

FROM BOAT INNER JOIN CHARTER ON BOAT.BoatID = CHARTER.BoatID;