Chapter Three – Structured Query Language
SELECT * FROM OWNED_PROPERTY;
SELECT * FROM PROPERTY_SERVICE;
E. Write an SQL statement to list LastName, FirstName, and CellPhone for all employees
having an experience level of Master.
SELECT LastName, FirstName, CellPhone
FROM EMPLOYEE
WHERE ExperienceLevel = ‘Master’;
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.
For Microsoft Access:
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 102 of 154
SELECT LastName, FirstName, CellPhone
FROM EMPLOYEE
WHERE ExperienceLevel = ‘Master’
AND FirstName LIKE ‘J*’;
For SQL Server, Oracle, and MySQL:
SELECT LastName, FirstName, CellPhone
FROM EMPLOYEE
WHERE ExperienceLevel = ‘Master’
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
WHERE EmployeeID IN
(SELECT EmployeeID
H. Answer question G but use a join using JOIN ON syntax.
Note that this version of the query could, in principle, produce duplicates (although with the
current data it does not). To precisely match query G, then, you would want to use DISTINCT
after SELECT for all versions of the query below.
For SQL Server and MySQL:
SELECT E.LastName, E.FirstName, E.CellPhone
FROM EMPLOYEE as E JOIN PROPERTY_SERVICE as PS
For Access:
SELECT E.LastName, E.FirstName, E.CellPhone
FROM (EMPLOYEE as E INNER JOIN PROPERTY_SERVICE as PS
Chapter Three – Structured Query Language
For Oracle:
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 LastName, FirstName, CellPhone
FROM EMPLOYEE
WHERE EmployeeID IN
(SELECT EmployeeID
FROM PROPERTY_SERVICE
J. Answer question I but use a join using JOIN ON syntax.
Note that this version of the query produces duplicates. To precisely match query I, then, you
would want to use DISTINCT after SELECT for all versions of the query below:
For SQL Server and MySQL:
SELECT DISTINCT E.LastName, E.FirstName, E.CellPhone
FROM EMPLOYEE as E JOIN PROPERTY_SERVICE as PS
For Access:
Chapter Three – Structured Query Language
SELECT DISTINCT E.LastName, E.FirstName, E.CellPhone
FROM ((EMPLOYEE as E INNER JOIN PROPERTY_SERVICE as PS
For Oracle:
SELECT DISTINCT E.LastName, E.FirstName, E.CellPhone
FROM EMPLOYEE E JOIN PROPERTY_SERVICE PS
ON E.EmployeeID = PS.EmployeeID
K. Write an SQL statement to show the LastName, FirstName, CellPhone, and sum of
hours worked for each employee.
For SQL Server, Oracle, and MySQL:
SELECT LastName, FirstName, CellPhone,
For Access:
SELECT LastName, FirstName, CellPhone,
SUM(HoursWorked) AS TotalHoursWorked
FROM EMPLOYEE INNER JOIN PROPERTY_SERVICE
Chapter Three – Structured Query Language
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.
For SQL Server, Oracle, and MySQL:
For Access:
SELECT ExperienceLevel, SUM(HoursWorked) AS TotalHoursWorked
FROM EMPLOYEE INNER JOIN PROPERTY_SERVICE
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.
For SQL Server, Oracle, and MySQL:
For Access:
SELECT OwnerType, SUM(HoursWorked) AS TotalHoursWorked
FROM ((EMPLOYEE INNER JOIN PROPERTY_SERVICE
Chapter Three – Structured Query Language
N. Write an SQL statement to modify all EMPLOYEE rows with ExperienceLevel of Master
to SuperMaster.
Note that this will violate the CHECK constraint that implements the business rule stating the
allowable values for ExperienceLevel. We thus need to drop or modify that constraint in order
to answer this question (in Access, drop then recreate the validation rule):
ALTER TABLE EMPLOYEE DROP CONSTRAINT EXPERIENCE_LEVEL_CHECK;
SELECT * FROM EMPLOYEE;
UPDATE EMPLOYEE
SET ExperienceLevel = ‘SuperMaster’
SELECT * FROM EMPLOYEE;
Then recreate the CHECK constraint (use SuperMaster instead of Master if you wish to keep
SuperMaster as the new ExperienceLevel):
Chapter Three – Structured Query Language
ALTER TABLE EMPLOYEE ADD CONSTRAINT EXPERIENCE_LEVEL_CHECK
CHECK (EXPERIENCELEVEL IN (‘Master’,’Senior’,’Junior’));
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.
ALTER TABLE EMPLOYEE DROP CONSTRAINT EXPERIENCE_LEVEL_CHECK;
SELECT * FROM EMPLOYEE;
SELECT * FROM EMPLOYEE;
UPDATE EMPLOYEE
SET ExperienceLevel = ‘temp’
WHERE ExperienceLevel = ‘Senior’;
UPDATE EMPLOYEE
SELECT * FROM EMPLOYEE;
Chapter Three – Structured Query Language
To undo the effects of this, use the following updates:
UPDATE EMPLOYEE
SET ExperienceLevel = ‘temp’
WHERE ExperienceLevel = ‘Junior’;
Then recreate the CHECK constraint (use SuperMaster instead of Master if you wish to keep
SuperMaster as the new ExperienceLevel from question N):
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!
Because there are no cascading deletions in our design, we have to delete all five tables,
Command not run on the actual database as per question.
Chapter Three – Structured Query Language
ANSWERS TO JAMES RIVER JEWELRY PROJECT QUESTIONS
[NOTE: The James River Jewelry Project Questions are available online in Appendix D,
which can be downloaded from the textbook’s Web site:
www.pearsonhighered.com/kroenke. The solutions for these questions will be included
in the Instructor’s Manual for each chapter]
James River Jewelry is a small jewelry shop. While James River Jewelry does sell
typical jewelry purchased form jewelry vendors, including such items as rings,
necklaces, earrings, and watches, it specializes in hard-to-find Asian jewelry. Although
some Asian jewelry is manufactured jewelry purchased from vendors in the same
manner as the standard jewelry is obtained, many of the Asian jewelry pieces are often
unique single items purchased directly from the artisan who created the piece (the term
“manufactured” would be an inappropriate description of these pieces). James River
Jewelry has a small but loyal clientele, and it wants to further increase customer loyalty
by creating a frequent buyer program. In this program, after every 10 purchases, a
customer will receive a credit equal to 50 percent of the average of his or her 10 most
recent purchases. This credit must be applied to the next (or 11th) purchase.
Assume that James River designs a database with the following tables.
CUSTOMER (CustomerID, LastName, FirstName, Phone, EmailAddress)
The referential integrity constraints are:
CustomerID in PURCHASE must exist in CustomerID in CUSTOMER
Assume that CustomerID of CUSTOMER, ItemNumber of ITEM, and InvoiceNumber of
PURCHASE are all surrogate keys with values as follows:
CustomerID Start at 1 Increment by 1
Chapter Three – Structured Query Language
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 SQL Server 2016, Oracle
Database XE, or MySQL 5.7 data types shown in Figure 3-5. For each SQL statement
you write, show the results based on your data.
Note to instructors: For the database solutions for the Project Questions about
JAMES_RIVER_JEWELRY, see the IRC files supplied and use:
• Microsoft Access:
• DBC–e08-JRJ.accdb
• SQL Server 2016:
• DBC–e08-MSSQL-JRJ-Create-Tables.sql
• DBC–e08-MSSQL-JRJ-Insert-Data.sql
• DBC–e08-MSSQL-JRJ-SQL-Queries-CH03.sql
• Oracle Database Express Edition 11g Release 2:
• DBC–e08-ODB-JRJ-Create-Tables.sql
Write SQL statements and answer questions for this database as follows:
A. Write SQL CREATE TABLE statements for each of these tables.
B. Write foreign key constraints for the relationships in each of these tables. Make your own
assumptions regarding cascading deletions and justify those assumptions. (Hint: You
can combine the SQL for your answers to parts A and B.)
Assuming the tables were created without any foreign key constraints, this could be done with
ALTER TABLE statements as follows:
Chapter Three – Structured Query Language
ALTER TABLE PURCHASE_ITEM
ADD CONSTRAINT PURCHASE_ITEM_ITEM_FK
FOREIGN KEY (ItemNumber) REFERENCES ITEM(ItemNumber);
No cascade delete on ItemNumber because we don’t want to remove it if there are purchases
that include them.
In SQL Server, Oracle, and MySQL, that constraint is added as follows:
Note that the ON DELETE and ON UPDATE clauses are optional when the default behavior of NO
ACTION is desired.
However, the discussion and figures in the text show this being done using the CREATE TABLE
statement. Foreign key constraints would be included in the CREATE TABLE statements as
follows:
For SQL Server 2016:
CREATE TABLE CUSTOMER(
CustomerID Int NOT NULL IDENTITY(1, 1),
LastName Char(35) NOT NULL,
Chapter Three – Structured Query Language
CustomerID Int NOT NULL,
CONSTRAINT PURCHASE_PK PRIMARY KEY(InvoiceNumber),
);
CREATE TABLE PURCHASE_ITEM (
InvoiceNumber Int NOT NULL,
InvoiceLineNumber Int NOT NULL,
For Microsoft Access:
The SQL Server solutions will work for Access with the following exceptions:
The Microsoft Access AutoNumber data type supports a starting value of 1 and an increment of
1. Therefore, two of the surrogate values called for here will work in the Access versions of
these tables. Create the three primary surrogate keys as INT NOT NULL, and then set the data
type to AutoNumber in the GUI. For the InvoiceNumber primary key, the values will need to be
set either manually or via application code. IMPORTANT: Do this individually for each table
immediately after it has been created!
Omit all the ON DELETE and ON UPDATE clauses. After the PURCHASE_ITEM table has been
created, use the GUI to set the proper ON DELETE CASCADE behavior for the foreign key
referencing PURCHASE.
For Oracle Database:
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 113 of 154
CREATE TABLE CUSTOMER(
CustomerID Int NOT NULL,
.
.
.
);
CREATE SEQUENCE seqCID INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE seqIID INCREMENT BY 1 START WITH 1;
CREATE TABLE PURCHASE(
InvoiceNumber Int NOT NULL,
.
.
.
);
CREATE SEQUENCE seqPID INCREMENT BY 1 START WITH 1001;
For MySQL:
The SQL CREATE TABLE commands shown for SQL Server will also work for MySQL with only one
modification. MySQL uses AUTO_INCREMENT to set surrogate keys with a starting value of 1
and an increment of 1. The starting value can be changed using an ALTER TABLE statement.
Therefore, the definitions of the CustomerID, ItemNumber, and InvoiceNumber surrogate values
should be written as:
CREATE TABLE CUSTOMER(
CustomerID Int NOT NULL AUTO_INCREMENT,
Chapter Three – Structured Query Language
CREATE TABLE PURCHASE(
InvoiceNumber Int NOT NULL AUTO_INCREMENT,
C. Write SQL statements to insert the data shown in Figures D-2, D-3, D-4, and D-5 into
these tables. Assume that surrogate key column values will be supplied by the DBMS.
NOTE: The complete SQL Server solutions are shown below. If you are inserting data into
Microsoft Access tables with an AutoNumber surrogate key or into MySQL tables with
AUTO_INCREMENT, you have to list all column names EXCEPT the surrogate key in the INSERT
statement. Oracle requires use of a sequence.
Thus, for example, while SQL Server would use the SQL command:
Whereas Microsoft Access and MySQL would use:
Oracle would use:
Finally, MySQL uses a date format of ‘YYYY-MM–DD’ as in ’2014-02–27′ and Oracle’s date formats
are numerous and depend on the default set by the DBA. See Appendix B for information on
Oracle Database use of sequences and date functions.
The IRC files listed above contain the complete solutions for MySQL, Access, and Oracle. The
tables need to be populated in an order consistent with the referential integrity constraints.
Specifically, CUSTOMER needs to be created before PURCHASE. Also, both PURCHASE and ITEM
need to be created before PURCHASE_ITEM. Therefore, we will enter data into the tables in the
following order (other orders are possible):
Chapter Three – Structured Query Language
PURCHASE_ITEM Foreign Keys:
InvoiceNumber references InvoiceNumber in PURCHASE
ItemNumber references ItemNumber in ITEM
For SQL Server:
/***** CUSTOMER DATA ********************************************************/
INSERT INTO CUSTOMER VALUES(
‘Stanley’, ‘Elizabeth’,’555-236-7789′,
‘Elizabeth.Stanley@somewhere.com’);
INSERT INTO CUSTOMER VALUES(
‘Price’, ‘Fred’, ‘555-236-0091’, ‘Fred.Price@somewhere.com’);
INSERT INTO CUSTOMER VALUES(
‘Becky’, ‘Linda’, ‘555-236–0392′,
/***** ITEM DATA ************************************************************/
INSERT INTO ITEM VALUES(‘Gold Bracelet’, 120.00, ‘Josephson’, ‘Mary’);
INSERT INTO ITEM VALUES(‘Gold Necklace’, 160.00, ‘Baker’, ‘Samantha’);
INSERT INTO ITEM VALUES(‘Bead Earrings’, 50.00, ‘Josephson’, ‘Mary’);
INSERT INTO ITEM VALUES(‘Gold Bracelet’, 180.00, ‘Baker’, ‘Samantha’);
INSERT INTO ITEM VALUES(‘Silver Necklace’, 135.00, ‘Baxter’, ‘Sam’);
INSERT INTO ITEM VALUES(‘Bead Earrings’, 25.00, ‘Josephson’, ‘Mary’);
INSERT INTO ITEM VALUES(‘Bead Earrings’, 22.50, ‘Josephson’, ‘Mary’);
INSERT INTO ITEM VALUES(‘Gold Earrings’, 50.00, ‘Lintz’, ‘John’);
INSERT INTO ITEM VALUES(‘Gold Necklace’, 160.00, ‘Lintz’, ‘John’);
/***** PURCHASE DATA *******************************************************/
INSERT INTO PURCHASE VALUES(’05-MAY-17′, 155.00, 1);
INSERT INTO PURCHASE VALUES(’07-MAY-17′, 203.00, 2);
INSERT INTO PURCHASE VALUES(’11-MAY-17′, 75.00, 3);
Chapter Three – Structured Query Language
INSERT INTO PURCHASE VALUES(’15-MAY-17′, 67.00, 4);
INSERT INTO PURCHASE VALUES(’15-MAY-17′, 330.00, 5);
/***** PURCHASE_ITEM DATA ***************************************************/
INSERT INTO PURCHASE_ITEM VALUES(1001, 1, 1, 155.00);
INSERT INTO PURCHASE_ITEM VALUES(1002, 1, 2, 203.00);
INSERT INTO PURCHASE_ITEM VALUES(1003, 1, 3, 75.00);
INSERT INTO PURCHASE_ITEM VALUES(1004, 1, 6, 35.00);
D. Write SQL statements to list all columns for all tables.
SELECT * FROM CUSTOMER;
SELECT * FROM ITEM;
Chapter Three – Structured Query Language
SELECT * FROM PURCHASE;
SELECT * FROM PURCHASE_ITEM;
Chapter Three – Structured Query Language
E. Write an SQL statement to list ItemNumber and ItemDescription for all items that cost
more than $100.
SELECT ItemNumber, ItemDescription
FROM ITEM
WHERE Cost > 100;
F. Write an SQL statement to list ItemNumber and ItemDescription for all items that cost
more than $100 and were produced by an artist with a last name ending with the letters
son.
Note that different DBMSs behave differently with regard to trailing spaces when using LIKE and
Chapter Three – Structured Query Language
SQL Server
Oracle
MySQL
Access (using SHORT
TEXT type)
=
Trailing spaces
automatically
removed:
ArtistLastName
= ‘Josephson’
will work
Trailing spaces
automatically
removed:
ArtistLastName
= ‘Josephson’
will work
Trailing spaces
automatically
removed:
ArtistLastName
= ‘Josephson’
will work
Trailing spaces
automatically
removed:
ArtistLastName
= ‘Josephson’
will work
LIKE
Trailing spaces
automatically
removed:
ArtistLastName
LIKE ‘%son’ will
work
Trailing spaces NOT
automatically
removed: need to use
RTRIM
(ArtistLastName
) LIKE ‘%son’
Trailing spaces
automatically
removed:
ArtistLastName
LIKE ‘%son’ will
work
Trailing spaces NOT
automatically
removed: RTRIM(
ArtistLastName)
LIKE ‘*son’ will
work
For SQL Server and MySQL:
SELECT ItemNumber, ItemDescription
For Oracle and Access:
G. Write an SQL statement to list LastName and FirstName of customers who have made
at least one purchase with PreTaxAmount greater than $200. Use a subquery.
SELECT LastName, FirstName
FROM CUSTOMER
Chapter Three – Structured Query Language
H. Answer part G but use a join using JOIN ON syntax.
For SQL Server, MySQL, and Oracle:
SELECT LastName, FirstName
For Access:
SELECT LastName, FirstName
FROM CUSTOMER INNER JOIN PURCHASE
Note that this is the same result, but in a different order. Also note that there happen to be no
duplicate results, but this could change if one of those customers makes another qualifying
purchase. Thus DISTINCT would be needed in that case to remove duplicates.
I. Write an SQL statement to list LastName and FirstName of customers who have
purchased an item that costs more than $50. Use a subquery.
SELECT LastName, FirstName
FROM CUSTOMER
WHERE CustomerID IN
(SELECT CustomerID