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

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

will you please explain thank you

Answer 1

We have to 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

General syntax to return column names where a given condition is satisfied is -
SELECT column_name1, column_name2
FROM table_name
WHERE condition;

Now our condition has two parts
for all employees
1.   having an experience level of Master and
2.   FirstName that begins with the letter J
(So our query must have to satisfy both the conditions given)

Query to list LastName, FirstName, and CellPhone for all employees having an experience level of Master is   -  

SELECT LastName, FirstName, CellPhone FROM EMPLOYEE WHERE ExperienceLevel='Master';
O/P   -  
+----------+-----------+--------------+
| LastName | FirstName | CellPhone |
+----------+-----------+--------------+
| Smith | Sam | 206-254-1234 |
| Murphy | Jerry | 585-545-8765 |
+----------+-----------+--------------+
2 rows in set (0.00 sec)

Query to list LastName, FirstName, and CellPhone for all employees having FirstName that begins with the letter J is   -

SELECT LastName, FirstName, CellPhone FROM EMPLOYEE WHERE FirstName LIKE 'J%';
O/P   -  
+----------+-----------+--------------+
| LastName | FirstName | CellPhone |
+----------+-----------+--------------+
| Evanston | John | 206-254-2345 |
| Murphy | Jerry | 585-545-8765 |
| Fontaine | Joan | 206-254-4567 |
+----------+-----------+--------------+
3 rows in set (0.00 sec)

***Now in SQL with AND keyword we can use multiple conditions where the query outputs values only satisfied by all the conditions chained with AND, so 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 is -

SELECT LastName, FirstName, CellPhone
FROM EMPLOYEE
WHERE ExperienceLevel='Master'AND FirstName LIKE 'J%';
O/P   -  
+----------+-----------+--------------+
| LastName | FirstName | CellPhone |
+----------+-----------+--------------+
| Murphy | Jerry | 585-545-8765 |
+----------+-----------+--------------+
1 row in set (0.00 sec)

Complete DBMS Statements   -
(MySQL Server version   -   8.0.21-0ubuntu0.20.04.4)
(If you have MySQL installed check using
1.   sudo mysqladmin -p -u username version
2.   Then enter your password)
(Now connect to the user - mysql -u root -p
enter user password)
(Now inside mysql prompt execute the following statements)

1.   Create the database named, GARDEN_GLORY(assuming it does not exist) -
CREATE DATABASE `GARDEN_GLORY`;
(O/P - Query OK, 1 row affected (0.21 sec))

2.   Check if GARDEN_GLORY created   -  
SHOW DATABASES LIKE 'GARDEN_GLORY';
(O/P -
+-------------------------+
| Database (GARDEN_GLORY) |
+-------------------------+
| GARDEN_GLORY |
+-------------------------+
1 row in set (0.01 sec)
)

3.    Select database   -  

USE GARDEN_GLORY;

4.   Now create the tables -

OWNER (OwnerID, OwnerName, OwnerEmail, OwnerType)
(Key constraints)
OwnerID in OWNER is a surrogate key
OwnerID Start at 1 Increment by 1

CREATE TABLE OWNER (
OwnerID int NOT NULL AUTO_INCREMENT,
OwnerName varchar(255),
OwnerEmail varchar(255),
OwnerType varchar(255),
PRIMARY KEY (OwnerID));

O/P   -   Query OK, 0 rows affected (1.05 sec)

DESC GARDEN_GLORY.OWNER;

O/P   -  
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| OwnerID | int | NO | PRI | NULL | auto_increment |
| OwnerName | varchar(255) | YES | | NULL | |
| OwnerEmail | varchar(255) | YES | | NULL | |
| OwnerType | varchar(255) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

GG_SERVICE (ServiceID, ServiceDescription, CostPerHour);
(Key constraints)
ServiceID Start at 1 Increment by 1

CREATE TABLE GG_SERVICE (
ServiceID int NOT NULL AUTO_INCREMENT,
ServiceDescription varchar(255),
CostPerHour float,
PRIMARY KEY (ServiceID));

O/P   -   Query OK, 0 rows affected (0.61 sec)

DESC GARDEN_GLORY.GG_SERVICE;

O/P   -  
+--------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+----------------+
| ServiceID | int | NO | PRI | NULL | auto_increment |
| ServiceDescription | varchar(255) | YES | | NULL | |
| CostPerHour | float | YES | | NULL | |
+--------------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel)
(Key constraints)
EmployeeID in EMPLOYEE is a surrogate key
EmployeeID Start at 1 Increment by 1

CREATE TABLE EMPLOYEE (
EmployeeID int NOT NULL AUTO_INCREMENT,
LastName varchar(255),
FirstName varchar(255),
CellPhone varchar(255),
ExperienceLevel varchar(255),
PRIMARY KEY (EmployeeID));

O/P   -   Query OK, 0 rows affected (0.76 sec)

DESC GARDEN_GLORY.EMPLOYEE;

O/P   -  
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| EmployeeID | int | NO | PRI | NULL | auto_increment |
| LastName | varchar(255) | YES | | NULL | |
| FirstName | varchar(255) | YES | | NULL | |
| CellPhone | varchar(255) | YES | | NULL | |
| ExperienceLevel | varchar(255) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

OWNED_PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, State, Zip, OwnerID)
(Referential integrity constraint)
OwnerID in OWNED_PROPERTY must exist in OwnerID in OWNER
(Key constraints)
PropertyID in PROPERTY is a surrogate key
PropertyID Start at 1 Increment by 1

CREATE TABLE OWNED_PROPERTY (
PropertyID int NOT NULL AUTO_INCREMENT,
PropertyName varchar(255),
PropertyType varchar(255),
Street varchar(255),
City varchar(255),
State varchar(255),
Zip int,
OwnerID int,
PRIMARY KEY (PropertyID),
FOREIGN KEY (OwnerID) REFERENCES OWNER(OwnerID)
);

O/P   -   Query OK, 0 rows affected (1.77 sec)

DESC GARDEN_GLORY.OWNED_PROPERTY;

O/P   -  
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| PropertyID | int | NO | PRI | NULL | auto_increment |
| PropertyName | varchar(255) | YES | | NULL | |
| PropertyType | varchar(255) | YES | | NULL | |
| Street | varchar(255) | YES | | NULL | |
| City | varchar(255) | YES | | NULL | |
| State | varchar(255) | YES | | NULL | |
| Zip | int | YES | | NULL | |
| OwnerID | int | YES | MUL | NULL | |
+--------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

PROPERTY_SERVICE ( PropertyServiceID , PropertyID , ServiceID, ServiceDate , EmployeeID, HoursWorked)
(Referential integrity constraints)
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
and
PropertyServiceID Start at 1 Increment by 1

CREATE TABLE PROPERTY_SERVICE (
PropertyServiceID int NOT NULL AUTO_INCREMENT,
PropertyID int,
ServiceID int,
    ServiceDate date,
EmployeeID int ,
    HoursWorked float,
PRIMARY KEY (PropertyServiceID),
FOREIGN KEY (PropertyID) REFERENCES OWNED_PROPERTY(PropertyID),
FOREIGN KEY (ServiceID) REFERENCES GG_SERVICE(ServiceID),
FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID)
);

O/P   -   Query OK, 0 rows affected (2.70 sec)

DESC GARDEN_GLORY.PROPERTY_SERVICE;

O/P   -  
+-------------------+-------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------+------+-----+---------+----------------+
| PropertyServiceID | int | NO | PRI | NULL | auto_increment |
| PropertyID | int | YES | MUL | NULL | |
| ServiceID | int | YES | MUL | NULL | |
| ServiceDate | date | YES | | NULL | |
| EmployeeID | int | YES | MUL | NULL | |
| HoursWorked | float | YES | | NULL | |
+-------------------+-------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

5.   Now insert sample values to the tables -

INSERT INTO
   OWNER(OwnerID, OwnerName, OwnerEmail, OwnerType)
VALUES
(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');

O/P   -   Query OK, 5 rows affected (0.26 sec)
Records: 5 Duplicates: 0 Warnings: 0

SELECT * FROM OWNER;

O/P   -  
+---------+-----------------+----------------------------+-------------+
| OwnerID | OwnerName | OwnerEmail | 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 |
+---------+-----------------+----------------------------+-------------+
5 rows in set (0.00 sec)

INSERT INTO
   GG_SERVICE(ServiceID, ServiceDescription, CostPerHour)
VALUES
(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);

O/P   -   Query OK, 7 rows affected (0.16 sec)
Records: 7 Duplicates: 0 Warnings: 0

SELECT * FROM GG_SERVICE;

O/P   -  
+-----------+--------------------+-------------+
| ServiceID | ServiceDescription | CostPerHour |
+-----------+--------------------+-------------+
| 1 | Mow Lawn | 25 |
| 2 | Plant Annuals | 25 |
| 3 | Weed Garden | 30 |
| 4 | Trim Hedge | 45 |
| 5 | Prune Small Tree | 60 |
| 6 | Trim Medium Tree | 100 |
| 7 | Trim Large Tree | 125 |
+-----------+--------------------+-------------+
7 rows in set (0.00 sec)

INSERT INTO
   EMPLOYEE(EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel)
VALUES
(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');

O/P   -  
Query OK, 5 rows affected (0.18 sec)
Records: 5 Duplicates: 0 Warnings: 0

SELECT * FROM EMPLOYEE;

O/P   -  
+------------+----------+-----------+--------------+-----------------+
| 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 |
+------------+----------+-----------+--------------+-----------------+
5 rows in set (0.00 sec)

INSERT INTO
   OWNED_PROPERTY(PropertyID, PropertyName, PropertyType, Street, City, State, ZIP, OwnerID)
VALUES
(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);

O/P   -   Query OK, 8 rows affected (0.12 sec)
Records: 8 Duplicates: 0 Warnings: 0

SELECT * FROM OWNED_PROPERTY;

O/P   -  
+------------+--------------------+-------------------+------------------+----------+-------+-------+---------+
| 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 |
+------------+--------------------+-------------------+------------------+----------+-------+-------+---------+
8 rows in set (0.00 sec)

INSERT INTO
   PROPERTY_SERVICE(PropertyServiceID, PropertyID, ServiceID, ServiceDate, EmployeeID, HoursWorked)
VALUES
(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);

O/P   -   Query OK, 13 rows affected (0.17 sec)
Records: 13 Duplicates: 0 Warnings: 0

SELECT * FROM PROPERTY_SERVICE;

O/P   -  
+-------------------+------------+-----------+-------------+------------+-------------+
| PropertyServiceID | PropertyID | ServiceID | ServiceDate | EmployeeID | HoursWorked |
+-------------------+------------+-----------+-------------+------------+-------------+
| 1 | 1 | 2 | 2019-05-05 | 1 | 4.5 |
| 2 | 3 | 2 | 2019-05-08 | 3 | 4.5 |
| 3 | 2 | 1 | 2019-05-08 | 2 | 2.75 |
| 4 | 6 | 1 | 2019-05-10 | 5 | 2.5 |
| 5 | 5 | 4 | 2019-05-12 | 4 | 7.5 |
| 6 | 8 | 1 | 2019-05-15 | 4 | 2.75 |
| 7 | 4 | 4 | 2019-05-19 | 1 | 1 |
| 8 | 7 | 1 | 2019-05-21 | 2 | 2.5 |
| 9 | 6 | 3 | 2019-06-03 | 5 | 2.5 |
| 10 | 5 | 7 | 2019-06-08 | 4 | 10.5 |
| 11 | 8 | 3 | 2019-06-12 | 4 | 2.75 |
| 12 | 4 | 5 | 2019-06-15 | 1 | 5 |
| 13 | 7 | 3 | 2019-06-19 | 2 | 4 |
+-------------------+------------+-----------+-------------+------------+-------------+
13 rows in set (0.00 sec)