USE Garden_Glory

GO

CREATE TABLE OWNER(

OwnerID Int NOT NULL IDENTITY(1,1),

OwnerName Char(50) NOT NULL,

OwnerEmailAddress VarChar(100) NULL,

OwnerType Char(12) NULL, CONSTRAINT

OWNER_PK PRIMARY KEY(OwnerID), CONSTRAINT

OWNER_TYPE_CHECK CHECK (OwnerType IN ('Individual','Corporation')) );

CREATE TABLE OWNED_PROPERTY(

PropertyID Int NOT NULL IDENTITY(1,1),

PropertyName VarChar(50) NOT NULL,

PropertyType VarChar(50) NOT NULL,

Street Char(35) NOT NULL,

City Char(35) NOT NULL,

[State] Char(2) NOT NULL,

ZIP Char(10) NOT NULL,

OwnerID Int NOT NULL, CONSTRAINT OWNED_PROPERTY_PK PRIMARY KEY(PropertyID), CONSTRAINT PROPERTY_OWNER_FK FOREIGN KEY (OwnerID) REFERENCES OWNER(OwnerID) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT PROPERTY_TYPE_CHECK CHECK (PropertyType IN ('Office','Apartments','Private Residence'))

);

CREATE TABLE EMPLOYEE(

EmployeeID Int NOT NULL IDENTITY(1,1),

LastName Char(25) NOT NULL,

FirstName Char(25) NOT NULL,

CellPhone Char(12) NOT NULL,

ExperienceLevel Char(15) NOT NULL, CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EmployeeID), CONSTRAINT EXPERIENCE_LEVEL_CHECK CHECK (ExperienceLevel IN

('Unkown', 'Junior', 'Senior', 'Master', 'SuperMaster'))

);

CREATE TABLE GG_SERVICE(

ServiceID Int NOT NULL IDENTITY (1,1),

ServiceDescription VarChar(100) NOT NULL,

CostPerHour Numeric(6,2) NULL, CONSTRAINT GG_SERVICE_PK PRIMARY KEY(ServiceID)

);

CREATE TABLE PROPERTY_SERVICE(

PropertyServiceID Int NOT NULL IDENTITY (1,1),

PropertyID Int NOT NULL,

ServiceID Int NOT NULL,

ServiceDate Date NOT NULL,

EmployeeID Int NOT NULL,

HoursWorked Numeric(4,2) NULL, CONSTRAINT PROP_SERVICE_PK PRIMARY KEY(PropertyServiceID), CONSTRAINT PROP_SERVICE_PROP_FK FOREIGN KEY (PropertyID) REFERENCES OWNED_PROPERTY(PropertyID) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT PROP_SERVICE_SERVICE_FK FOREIGN KEY (ServiceID) REFERENCES GG_SERVICE(ServiceID) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT SERVICE_EMP_FK FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID) ON DELETE NO ACTION ON UPDATE NO ACTION );

/********************************************************************************/

USE Garden_Glory GO

/***** OWNER DATA ***********************************************************/

INSERT INTO OWNER VALUES( 'Mary Jones', 'Mary.Jones@somewhere.com', 'Individual'); INSERT INTO OWNER VALUES( 'DT Enterprises', 'DTE@dte.com', 'Corporation'); INSERT INTO OWNER VALUES( 'Sam Douglas', 'Sam.Douglas@somewhere.com', 'Individual'); INSERT INTO OWNER VALUES( 'UNY Enterprises', 'UNYE@unye.com', 'Corporation'); INSERT INTO OWNER VALUES( 'Doug Samuels', 'Doug.Samuels@somewhere.com', 'Individual');

-- SELECT * FROM OWNER;

/***** OWNED_PROPERTY DATA ***************************************************/

INSERT INTO OWNED_PROPERTY VALUES( 'Eastlake Building', 'Office', '123 Eastlake', 'Seattle', 'WA', '98119', 2); INSERT INTO OWNED_PROPERTY VALUES( 'Elm St Apts', 'Apartments', '4 East Elm', 'Lynwood', 'WA', '98223', 1); INSERT INTO OWNED_PROPERTY VALUES( 'Jefferson Hill', 'Office', '42 West 7th St', 'Bellevue', 'WA', '98007', 2); INSERT INTO OWNED_PROPERTY VALUES( 'Lake View Apts', 'Apartments', '1265 32nd Avenue', 'Redmond', 'WA', '98052', 3); INSERT INTO OWNED_PROPERTY VALUES( 'Kodak Heights Apts', 'Apartments', '65 32nd Avenue', 'Redmond', 'WA', '98052', 4); INSERT INTO OWNED_PROPERTY VALUES( 'Jones House', 'Private Residence', '1456 48th St', 'Bellevue', 'WA', '98007', 1); INSERT INTO OWNED_PROPERTY VALUES( 'Douglas House', 'Private Residence', '1567 51st St', 'Bellevue', 'WA', '98007', 3); INSERT INTO OWNED_PROPERTY VALUES( 'Samuels House', 'Private Residence', '567 151st St', 'Redmond', 'WA', '98052', 5);

-- SELECT * FROM OWNED_PROPERTY;

/***** EMPLOYEE DATA *********************************************************/

INSERT INTO EMPLOYEE VALUES( 'Smith', 'Sam', '206-254-1234', 'Master'); INSERT INTO EMPLOYEE VALUES( 'Evanston', 'John','206-254-2345', 'Senior'); INSERT INTO EMPLOYEE VALUES( 'Murray', 'Dale', '206-254-3456', 'Junior'); INSERT INTO EMPLOYEE VALUES( 'Murphy', 'Jerry', '585-545-8765', 'Master'); INSERT INTO EMPLOYEE VALUES( 'Fontaine', 'Joan', '206-254-4567', 'Senior');

-- SELECT * FROM EMPLOYEE;

/***** GG_SERVICE DATA **********************************************************/

INSERT INTO GG_SERVICE VALUES('Mow Lawn', 25.00); INSERT INTO GG_SERVICE VALUES('Plant Annuals', 25.00); INSERT INTO GG_SERVICE VALUES('Weed Garden', 30.00); INSERT INTO GG_SERVICE VALUES('Trim Hedge', 45.00); INSERT INTO GG_SERVICE VALUES('Prune Small Tree', 60.00); INSERT INTO GG_SERVICE VALUES('Trim Medium Tree',100.00); INSERT INTO GG_SERVICE VALUES('Trim Large Tree', 125.00);

-- SELECT * FROM GG_SERVICE;

/***** PROPERTY_SERVICE **************************************************************/

INSERT INTO PROPERTY_SERVICE VALUES(1, 2, '05-MAY-19', 1, 4.50); INSERT INTO PROPERTY_SERVICE VALUES(3, 2, '08-MAY-19', 3, 4.50); INSERT INTO PROPERTY_SERVICE VALUES(2, 1, '08-MAY-19', 2, 2.75); INSERT INTO PROPERTY_SERVICE VALUES(6, 1, '10-MAY-19', 5, 2.50); INSERT INTO PROPERTY_SERVICE VALUES(5, 4, '12-MAY-19', 4, 7.50); INSERT INTO PROPERTY_SERVICE VALUES(8, 1, '15-MAY-19', 4, 2.75); INSERT INTO PROPERTY_SERVICE VALUES(4, 4, '19-MAY-19', 1, 1.00); INSERT INTO PROPERTY_SERVICE VALUES(7, 1, '21-MAY-19', 2, 2.50); INSERT INTO PROPERTY_SERVICE VALUES(6, 3, '03-JUN-19', 5, 2.50); INSERT INTO PROPERTY_SERVICE VALUES(5, 7, '08-JUN-19', 4, 10.50); INSERT INTO PROPERTY_SERVICE VALUES(8, 3, '12-JUN-19', 4, 2.75); INSERT INTO PROPERTY_SERVICE VALUES(4, 5, '15-JUN-19', 1, 5.00); INSERT INTO PROPERTY_SERVICE VALUES(7, 3, '19-JUN-19', 2, 4.00);

-- SELECT * FROM PROPERTY_SERVICE;

/****************************************************************************************/

G. Write an SQL statement to list LastName, FirstName, and Cellphone of employees who have worked on a property in Seattle. Use a subquery. H. Answer question G but use a join using JOIN ON syntax. What are the consequences of using (or not using) the DTSTTNCT keyword in this version of the query? I. 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. J. 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? K. Write an SQL statcment to show the LastName, Firstiame, Cellelone, and sum of hours worked for each employee. L. Write an SQL statement to show the sum of hours worked for each ExperienceLevel of EMPLOYEE. Sort the results by ExperienceLevel, in descending order. M. 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. N. Write an SQL statement to modify all EMPLOYEE rows with ExperienceLevel of Master to SuperMaster. O. 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. (Himh Use the value Unknown as a temporary third value.) P. 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! G. Write an SQL statement to list LastName, FirstName, and Cellphone of employees who have worked on a property in Seattle. Use a subquery. H. Answer question G but use a join using JOIN ON syntax. What are the consequences of using (or not using) the DTSTTNCT keyword in this version of the query? I. 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. J. 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? K. Write an SQL statcment to show the LastName, Firstiame, Cellelone, and sum of hours worked for each employee. L. Write an SQL statement to show the sum of hours worked for each ExperienceLevel of EMPLOYEE. Sort the results by ExperienceLevel, in descending order. M. 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. N. Write an SQL statement to modify all EMPLOYEE rows with ExperienceLevel of Master to SuperMaster. O. 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. (Himh Use the value Unknown as a temporary third value.) P. 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