Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Appendix E – Advanced SQL
ALTER TABLE EMPLOYEE
ADD Supervisor Int NULL;
B. Garden Glory requires that each employee complete an apprenticeship program. Add a
column to the EMPLOYEE table named Apprenticeship, which will contain data showing
Appendix E – Advanced SQL
the apprenticeship status for each employee. Column characteristics for the GG
Apprenticeship column are shown in Figure E-48. Populate the column with the data
shown in Figure E-49.
NOTE: we omit the CHECK constraint for now; question D will address that. There are three
phases to the solution: add the column, populate it, and enforce the NOT NULL constraint.
ALTER TABLE EMPLOYEE
ADD Apprenticeship Varchar(20) NULL;
The last phase differs among systems. For SQL Server and Access:
C. How did your steps to add the Apprenticeship column differ from your steps to add the
Supervisor column? Why was (were) the additional step(s) necessary?
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 63 of 107
column is added to an existing row, it is automatically populated with NULL by the DBMS, and
that would violate the constraint. So we create the column, populate it, then make it NOT NULL.
D. Add an SQL CHECK constraint to the EMPLOYEE table to ensure that only the values
of Completed, In process, or Not started are allowed as data in the Apprenticeship
column.
In Access, use a validation rule via the GUI. Other systems:
E. Write an SQL SELECT statement to create a query on the recursive relationship in the
EMPLOYEE table that shows each employee’s FirstName (as EmployeeFirstName) and
LastName (as EmployeeLastName) followed by that employee’s supervisor’s FirstName
(as SupervisorFirstName) and LastName (as SupervisorLastName). Do not include
employees who do not have a supervisor.
For Access:
SELECT E.FirstName AS EmployeeFirstName,
For SQL Server, MySQL, and Oracle:
SELECT E.FirstName AS EmployeeFirstName,
F. Write an SQL SELECT statement to create a query on the recursive relationship in the
EMPLOYEE table that shows each employee’s FirstName (as EmployeeFirstName) and
Appendix E – Advanced SQL
LastName (as EmployeeLastName) followed by that employee’s supervisor’s FirstName
(as SupervisorFirstName) and LastName (as SupervisorLastName). Do include
employees who do not have a supervisor.
SELECT E.FirstName AS EmployeeFirstName,
E.LastName AS EmployeeLastName,
G. Write an SQL SELECT statement to create a correlated subquery to determine if there
are any employees who have the same combination of LastName and FirstName. Hint:
If no employees meet this condition, the correct query result will be an empty set.
SELECT E1.EmployeeID, E1.FirstName, E1.LastName
FROM EMPLOYEE E1
H. Write a user-defined function named FirstNameFirst that concatenates the employee’s
LastName and FirstName into a single value named FullName and displays, in order,
the FirstName, a space, and the LastName (Hint: Smith and Steve would be combined
to read Steve Smith).
For Microsoft Access:
Appendix E – Advanced SQL
CREATE FUNCTION dbo.FirstNameFirst
-- These are the input parameters
I. Create the following SQL views:
1. Create an SQL view named OwnerPropertyView that shows OWNER.OwnerID,
OwnerName, PropertyType, PropertyID, PropertyName, Street, City, State, and
ZIP.
For Microsoft Access:
Create and save the following Access query as viewOwnerProperty.
For SQL Server and MySQL:
For Oracle Database:
Appendix E – Advanced SQL
CREATE VIEW OwnerPropertyView AS
2. Create an SQL view named PropertyServiceView that shows
OWNED_PROPERTY. PropertyID, PropertyName, Street, City, State, ZIP,
ServiceDate, FirstName, LastName, and HoursWorked.
For Microsoft Access:
Create and save the following Access query as viewPropertyService.
For SQL Server and MySQL:
For Oracle Database:
CREATE VIEW PropertyServiceView AS
SELECT P.PropertyID, PropertyName, Street, City, State, ZIP,
Appendix E – Advanced SQL
3. Create an SQL view named PropertyServiceFirstNameFirstView that shows
OWNED_PROPERTY.PropertyID, PropertyName, Street, City, State, ZIP,
ServiceDate, then LastName and FirstName concatenated using the
FirstNameFirst user-defined function and displayed as EmployeeName, and
HoursWorked.
For SQL Server and MySQL:
For Oracle Database:
J. Create (and run) the following SQL queries:
1. Create an SQL statement to run OwnerPropertyView, with the results sorted
alphabetically by OwnerName.
For Microsoft Access:
Appendix E – Advanced SQL
2. Create an SQL statement to run PropertyServiceView, with the results sorted
alphabetically by ZIP, State, and City.
For Microsoft Access:
For SQL Server, Oracle Database, and MySQL:
SELECT *
FROM PropertyServiceView
ORDER BY ZIP, State, City;
3. Create an SQL statement to run PropertyServiceFirstNameFirstView, with the
results sorted alphabetically by ZIP, State, and City.
For SQL Server and MySQL:
Appendix E – Advanced SQL
For Oracle Database:
SELECT *
FROM PropServiceFirstNameFirstView
ORDER BY ZIP, State, City;
K. Garden Glory staff keep a record of tool inventory and who uses those tools in a
Microsoft Excel worksheet, as shown in Figure E-50. Garden Glory now wants to import
this data into one or more database tables.
Appendix E – Advanced SQL
1. Duplicate Figure E-50 in a worksheet (or spreadsheet) in an appropriate tool
(such as Microsoft Excel or Apache OpenOffice Calc).
This is self-explanatory. However, it does require splitting the worksheet into two
tables: TOOLS and an intersection table named TOOL_USE to record which tools are
2. Import the data into one or more new tables in the GG database. You must
determine all tables characteristics needed (primary key, foreign keys, data
types, etc.)
Follow the instructions in the relevant appendix to accomplish this task:
Appendix E – Advanced SQL
Foreign keys will be handled in question 3; here we import the data, populate any
unpopulated columns, and set the primary keys. We will create a column in the
TOOL_USE table that will hold an EmployeeID, which can be determined using the
employee name data in the TOOL_USE table. This column will become a foreign key in
the next question. It is possible, again, to automate the process of converting employee
names into EmployeeIDs, but we will do that by hand here using UPDATE statements
since we have a small database and the other method is beyond the scope of this book.
Any required SQL statements during the import process will be in the SQL file for that
system. Here are some things to make note of during the import process:
For SQL Server:
The import process has resulted in a tables named TOOLS$ and TOOL_USE$. After
renaming the tables to remove the $ at the end of each, we will first change the types of
both ToolID columns from Float to Int, then add an EmployeeID column to TOOL_USE,
populate that column, then set the primary keys and drop any unnecessary imported
columns. Note that we omit certain additional details that will need checking,
specifically data types and NULL/NOT NULL constraints for other fields.
ALTER TABLE TOOLS
ALTER COLUMN ToolID Int NOT NULL;
Appendix E – Advanced SQL
ALTER TABLE TOOL_USE
DROP COLUMN UsedBy;
SELECT * FROM TOOLS;
SELECT * FROM TOOL_USE;
For Oracle:
Oracle allows a great deal of flexibility during the import process, so following the
instructions from Appendix B we end up with a table that has columns of the proper
type. The exception here is for the DATE type data, which must first be loaded in as
VarChar then converted into a DATE format that Oracle will recognize. Here are the
post-import SQL statements and resulting tables (note the column renamed from DATE
to DATE_USED):
Appendix E – Advanced SQL
UPDATE TOOL_USE
SET EmployeeID = 4 WHERE UsedBy = 'Jerry Murphy';
SELECT * FROM TOOLS;
SELECT * FROM TOOL_USE;
For MySQL: See the MySQL solutions file.
3. Link this (these) new table(s) as appropriate to one or more existing tables in the
GG database. Explain why you chose to make the connection(s) you made.
Appendix E – Advanced SQL
The tables with all their columns and data were created in question 2. Here we will link
them using foreign keys: The TOOL_USE table will have 2 foreign keys, one to refer to
the tool being used and the other to refer to the employee using the tool. These foreign
keys are needed to maintain the integrity of the data in the TOOL_USE table. We will
accept the defaults of NO ACTION for ON DELETE and ON UPDATE of both foreign keys:
we do not want to delete tool usage information, and we will not be changing the values
of our surrogate keys. Once all the data have been entered in question 2, the SQL to
create the foreign keys is the same for all systems:
ALTER TABLE TOOL_USE
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 and appendix.]
Note: Online Appendix E covers SQL Views and other advanced SQL topics, and
logically that material should be studied in conjunction with Chapter 3 on SQL.
Therefore, the James River Jewelry Project Questions for Appendix E are
presented in this location in this appendix.
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
Appendix E – Advanced SQL
• SQL Server 2016:
• DBC-e08-MSSQL-JRJ-Create-Tables.sql
• Oracle Database Express Edition 11g Release 2:
• DBC-e08-ODB-JRJ-Create-Tables.sql
• MySQL 5.7:
• DBC-e08-MySQL-JRJ-Create-Tables.sql
A. Add a column to the CUSTOMER table named ReferredBy, which will contain data on
which customer referred the new customer to the store. The column characteristics for
the ReferredBy column are shown in Figure D-6. Populate the column with the data
shown in Figure D-7.
Appendix E – Advanced SQL
NOTE: We are not implementing the UNIQUE constraint; it’s no longer part of the question.
/*** Add the column and foreign key constraint ***/
ALTER TABLE CUSTOMER
ADD ReferredBy Int NULL;
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 77 of 107
B. Add a column to the ITEM table named ArtistHasBeenPaid, which will contain data
showing whether the artist has been paid for the piece of jewelry James River Jewelry
acquired. Column characteristics for the JRJ ArtistHasBeenPaid column are shown in
Figure D-8. Populate the column with the data shown in Figure D-9.
Appendix E – Advanced SQL
NOTE: we omit the CHECK constraint for now; question D will address that. There are three
phases to the solution: add the column, populate it, and enforce the NOT NULL constraint.
ALTER TABLE ITEM
ADD ArtistHasBeenPaid Varchar(25) NULL;
Appendix E – Advanced SQL
The last phase differs among systems. For SQL Server and Access:
ALTER TABLE ITEM
ALTER COLUMN ArtistHasBeenPaid Varchar(25) NOT NULL;
For Oracle and MySQL:
C. How did your steps to add the ArtistHasBeenPaid column differ from your steps to add
the ReferredBy column? Why was (were) the additional step(s) necessary?
D. Add an SQL CHECK constraint to the ITEM table to ensure that only the values of
Waiting for invoice, In process, or Paid are allowed as data in the ArtistHasBeenPaid
column.
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 80 of 107
ALTER TABLE ITEM
ADD CONSTRAINT ArtistPaidCheck CHECK
(ArtistHasBeenPaid
IN ('Waiting for invoice', 'In process', 'Paid'));
E. Write an SQL SELECT statement to create a query on the recursive relationship in the
CUSTOMER table that shows each customer’s FirstName (as CustomerFirstName) and
LastName (as CustomerLastName) followed by the name of the customer who referred
him or her to James River Jewelry using the referring customer’s FirstName (as
ReferrerFirstName) and LastName (as ReferrerLastName). Do not include customers
who were not referred by another customer.
For Access:
For SQL Server, MySQL, and Oracle:
SELECT C.FirstName AS CustomerFirstName,
C.LastName AS CustomerLastName,
F. Write an SQL SELECT statement to create a query on the recursive relationship in the
CUSTOMER table that shows each customer’s FirstName (as CustomerFirstName) and
LastName (as CustomerLastName) followed by the name of the customer who referred
him or her to James River Jewelry using the referring customer’s FirstName (as
ReferrerFirstName) and LastName (as ReferrerLastName). Do include customers who
were not referred by another customer.
