Question

Garden Glory Project Questions Assume that Garden Glory designs a database with the following tables:

OWNER (OwnerID, OwnerName, OwnerEmail, OwnerType)

OWNED_PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, State, Zip, OwnerID)

GG_SERVICE (ServiceID, ServiceDescription, CostPerHour);

EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel)

PROPERTY_SERVICE ( PropertyServiceID , PropertyID , ServiceID, ServiceDate , EmployeeID, HoursWorked)

The referential integrity constraints are:

OwnerID in OWNED_PROPERTY must exist in OwnerID in OWNER

PropertyID in PROPERTY_SERVICE must exist in PropertyID in OWNED_PROPERTY

ServiceID in PROPERTY_SERVICE must exist in ServiceID in GG_SERVICE

EmployeeID in PROPERTY_SERVICE must exist in EmployeeID in EMPLOYEE

Assume that OwnerID in OWNER, PropertyID in PROPERTY, and EmployeeID in EMPLOYEE are surrogate keys with values as follows:

OwnerID Start at 1 Increment by 1

PropertyID Start at 1 Increment by 1

ServiceID Start at 1 Increment by 1

EmployeeID Start at 1 Increment by 1

PropertyServiceID Start at 1 Increment by 1

Sample data are shown in Figure 3-38, Figure 3-39, Figure 3-40, Figure 3-41, and Figure 3-42. OwnerType is either Individual or Corporation. PropertyType is one of Office, Apartments, or Private Residence. ExperienceLevel is one of Unknown, Junior, Senior, Master or SuperMaster. These tables, referential integrity constraints, and data are used

Sample Data for Garden Glory OWNER Table OwnerID OwnerName OwnerEmailAddress OwnerType

1 Mary Jones [email protected] Individual

2 DT Enterprises [email protected] Corporation

3 Sam Douglas [email protected] Individual

4 UNY Enterprises [email protected] Corporation

5 Doug Samuels [email protected] Individual

Sample Data for Garden Glory OWNED_PROPERTY Table PropertyID PropertyName PropertyType Street City State ZIP OwnerID

1 Eastlake Building Office 123 Eastlake Seattle WA 98119 2

2 Elm St Apts Apartments 4 East Elm Lynwood WA 98223 1

3 Jefferson Hill Office 42 West 7th St Bellevue WA 98007 2

4 Lake View Apts Apartments 1265 32nd Avenue Redmond WA 98052 3

5 Kodak Heights Apts Apartments 65 32nd Avenue Redmond WA 98052 4

6 Jones House Private Residence 1456 48th St Bellevue WA 98007 1

7 Douglas House Private Residence 1567 51st St Bellevue WA 98007 3

8 Samuels House Private Residence 567 151st St Redmond WA 98052 5

as the basis for the SQL statements you will create in the exercises that follow. If possible, run these statements in an actual DBMS, as appropriate, to obtain your results. Name your database GARDEN_GLORY. Use data types consistent with the DBMS you are using. If you are not using an actual DBMS, consistently represent data types using either the MySQL, Microsoft SQL Server, or Oracle Database data types shown in Figure 3-5. For each SQL statement you write, show the results based on your data. Write SQL statements and answer questions for this database as follows:

FIGURE 3-40

Sample Data for Garden Glory EMPLOYEE Table EmployeeID LastName FirstName CellPhone ExperienceLevel

1 Smith Sam 206-254-1234 Master

2 Evanston John 206-254-2345 Senior

3 Murray Dale 206-254-3456 Junior

4 Murphy Jerry 585-545-8765 Master

5 Fontaine Joan 206-254-4567 Senior

FIGURE 3-41

Sample Data for Garden Glory GG_SERVICE Table ServiceID ServiceDescription CostPerHour

1 Mow Lawn 25.00

2 Plant Annuals 25.00

3 Weed Garden 30.00

4 Trim Hedge 45.00

5 Prune Small Tree 60.00

6 Trim Medium Tree 100.00

7 Trim Large Tree 125.00

FIGURE 3-42

Sample Data for Garden Glory PROPERTY_SERVICE Table PropertyServiceID PropertyID ServiceID ServiceDate EmployeeID HoursWorked

1 1 2 2019-05-05 1 4.50

2 3 2 2019-05-08 3 4.50

3 2 1 2019-05-08 2 2.75

4 6 1 2019-05-10 5 2.50

5 5 4 2019-05-12 4 7.50

6 8 1 2019-05-15 4 2.75

7 4 4 2019-05-19 1 1.00

8 7 1 2019-05-21 2 2.50

9 6 3 2019-06-03 5 2.50

10 5 7 2019-06-08 4 10.50

11 8 3 2019-06-12 4 2.75

12 4 5 2019-06-15 1 5.00

13 7 3 2019-06-19 2 4.00

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

will you please explain thank you!

Answer 1

I M USING MICROSOFT SQL SERVER. The query no H wont give any specific result as distinct doesnt needed.

CREATE TABLE OWNER (OwnerID int identity(1,1), OwnerName varchar(40), OwnerEmail varchar(40), OwnerType varchar(40) PRIMARY KEY (OWNERID))


CREATE TABLE OWNED_PROPERTY (PropertyID int identity(1,1), PropertyName varchar(40), PropertyType varchar(40), Street varchar(40), 
City varchar(40), State varchar(40), Zip varchar(40), OwnerID int ,PRIMARY KEY (PropertyID), FOREIGN KEY (OwnerID) REFERENCES OWNER(OWNERID))


CREATE TABLE GG_SERVICE (ServiceID int identity(1,1), ServiceDescription varchar(40), CostPerHour float, PRIMARY KEY (ServiceID))


CREATE TABLE EMPLOYEE (EmployeeID int identity(1,1), LastName varchar(40), FirstName varchar(40), CellPhone varchar(40), ExperienceLevel varchar(40), Primary Key (EmployeeID))

CREATE TABLE PROPERTY_SERVICE (PropertyServiceID  int identity(1,1), PropertyID int, ServiceID int, ServiceDate datetime , EmployeeID int, HoursWorked float
,PRIMARY KEY (PropertyServiceID), FOREIGN KEY (PropertyID) REFERENCES OWNED_PROPERTY(PropertyID),FOREIGN KEY (ServiceID) REFERENCES GG_SERVICE(ServiceID))

--INSERTING INTO OWNER TABLE

INSERT INTO OWNER VALUES('Mary Jones','[email protected]','Individual')
INSERT INTO OWNER VALUES('DT Enterprises','[email protected]','Corporation')
INSERT INTO OWNER VALUES('Sam Douglas','[email protected]','Individual')
INSERT INTO OWNER VALUES('UNY Enterprises','[email protected]','Corporation')
INSERT INTO OWNER VALUES('Doug Samuels','[email protected]','Individual')

--INSERTING DATA INTO OWNED_PROPERTY
INSERT INTO OWNED_PROPERTY VALUES('PropertyName','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)

--INSERTING INTO EMPLOYEE TABLE

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')

--INSERTING DATA INTO GG_SERVICE

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)

--INSERTING INTO PROPERTY_SERVICE

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


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

select LASTNAME,FIRSTNAME,CELLPHONE FROM EMPLOYEE E 
JOIN PROPERTY_SERVICE P ON  E.EmployeeID=P.EmployeeID
 JOIN  OWNED_PROPERTY O ON O.PropertyID=P.PropertyID WHERE
STATE LIKE '%Seattle'

--H. 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?

--HERE BECAUSE WE DONT HAVE DUPLICATE RECORDS SO THE DISTINCT IS NOT NEEDED.
select DISTINCT LASTNAME,FIRSTNAME,CELLPHONE FROM EMPLOYEE E 
JOIN PROPERTY_SERVICE P ON  E.EmployeeID=P.EmployeeID
 JOIN  OWNED_PROPERTY O ON O.PropertyID=P.PropertyID WHERE
STATE LIKE '%Seattle'

--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.
select DISTINCT LASTNAME,FIRSTNAME,CELLPHONE FROM EMPLOYEE E 
JOIN PROPERTY_SERVICE P ON  E.EmployeeID=P.EmployeeID
 JOIN  OWNED_PROPERTY O ON O.PropertyID=P.PropertyID
 JOIN OWNER OW ON OW.OwnerID=O.OwnerID 
 WHERE OwnerType LIKE '%CORPORATION%'