!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! USE SQL !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

*Determine which service is the most contracted by corporate owners who own offices or apartments. (Hint: This will require a two-step process, which will require to build upon the initial statement based on output to create one statement as a response).

USE Garden_Glory GO CREATE TABLE OWNER( OwnerID Int NOT NULL IDENTITY(1,1), OwnerName Char(50) NOT NULL, OwnerEmailAddressVarChar(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_PKPRIMARY KEY(PropertyID), CONSTRAINT PROPERTY_OWNER_FKFOREIGN 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( PropertyServiceIDInt 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;