Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Appendix E – Advanced SQL
SELECT C.FirstName AS CustomerFirstName,
C.LastName AS CustomerLastName,
G. Suppose that James River Jewelry owners are considering changing the primary key of
CUSTOMER to (FirstName, LastName). Write a correlated subquery to display any data
that indicate that this change is not justifiable. Hint: If no employees meet this condition,
the correct query result will be an empty set.
H. Write a user-defined function named FirstNameFirst that concatenates the
customer’s LastName and FirstName into a single value named FullName, and
displays, in order, the FirstName, a space, and the LastName (hint: Stanley and
Elizabeth would be combined to read Elizabeth Stanley).
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 CustomerPurchaseView that shows
CUSTOMER.CustomerID, LastName, FirstName, InvoiceNumber, InvoiceDate,
and PreTaxAmount.
For Microsoft Access:
Create and save the following Access query as viewCustomerPurchase.
SELECT C.CustomerID, LastName, FirstName,
InvoiceNumber, InvoiceDate, PreTaxAmount
Appendix E – Advanced SQL
CREATE VIEW CustomerPurchaseView AS
SELECT C.CustomerID, LastName, FirstName,
2. Create an SQL view named CustomerFirstNameFirstPurchaseView that shows
CustomerID, then LastName and FirstName concatenated using the
FirstNameFirst user-defined function and displayed as CustomerName,
InvoiceNumber, InvoiceDate, and PreTaxAmount.
For SQL Server and MySQL:
CREATE VIEW CustomerFirstNameFirstPurchaseView AS
SELECT C.CustomerID,
For Oracle Database:
CREATE VIEW CustFirstNameFirstPurchaseView AS
SELECT C.CustomerID, FirstNameFirst(FirstName, LastName)
3. Create an SQL view named PurchaseItemItemView that shows InvoiceNumber,
ItemNumber, ArtistLastName, ArtistFirstName, ItemDescription, Cost, and
RetailPrice.
For Microsoft Access:
Create and save the following Access query as viewPurchaseItemItem.
Appendix E – Advanced SQL
SELECT P.InvoiceNumber, PI.ItemNumber,
ArtistLastName, ArtistFirstName.
For SQL Server and MySQL:
CREATE VIEW PurchaseItemItemView AS
SELECT P.InvoiceNumber, PI.ItemNumber,
For Oracle Database:
CREATE VIEW PurchaseItemItemView AS
SELECT P.InvoiceNumber, PI.ItemNumber,
4. Create an SQL view named PurchaseItemItemFirstNameFirstView that shows
InvoiceNumber, ItemNumber, then ArtistLastName and ArtistFirstName
concatenated using the FirstNameFirst user-defined function and displayed as
ArtistName, ItemDescription, Cost, and RetailPrice.
For SQL Server and MySQL:
CREATE VIEW PurchaseItemItemFirstNameFirstView AS
SELECT P.InvoiceNumber, PI.ItemNumber,
For Oracle Database:
Appendix E – Advanced SQL
CREATE VIEW PurItemItemFirstNameFirstView AS
SELECT P.InvoiceNumber, PI.ItemNumber,
J. Create (and run) the following SQL queries:
1. Create an SQL statement to run CustomerPurchaseView, with the results sorted
alphabetically by LastName and FirstName.
For Microsoft Access:
For SQL Server, Oracle Database, and MySQL:
SELECT *
2. Create an SQL statement to run CustomerPurchaseFirstNameFirstView, with the
results sorted alphabetically by CustomerName.
Appendix E – Advanced SQL
For SQL Server and MySQL:
For Oracle Database:
3. Create an SQL statement to run PurchaseItemItemView, with the results sorted
by InvoiceNumber and ItemNumber.
For Microsoft Access:
For SQL Server, Oracle Database, and MySQL:
Appendix E – Advanced SQL
4. Create an SQL statement to run PurchaseItemItemFirstNameFirstView, with the
results sorted by InvoiceNumber and ItemNumber.
For SQL Server and MySQL:
Appendix E – Advanced SQL
5. Create an SQL query that uses PurchaseItemItemView to calculate and display
the sum of Cost as TotalItemCost and the sum of RetailPrice as
TotalRetailSales.
For Microsoft Access:
For SQL Server, Oracle Database, and MySQL:
SELECT InvoiceNumber,
SUM(Cost) AS TotalItemCost,
K. James River Jewelry sells standard merchandise jewelry, purchased for suppliers, as
well as its artisan line. At this point, inventory of these items and the vendors who supply
them have been kept in a Microsoft Excel worksheet, as shown in Figure D-10.
Management now wants to import this data into one or more database tables.
• The steps for importing data into Microsoft SQL Server 2016 from Microsoft Excel
2016 are discussed in Appendix A, “Getting Started with Microsoft SQL Server 2016
Express Edition.”
• The steps for importing data into Oracle Database XE from Microsoft Excel 2016 are
discussed in Appendix B, “Getting Started with Oracle Database XE.”
• The steps for importing data into MySQL 5.7 from Microsoft Excel 2016 are
discussed in Appendix C, “Getting Started with MySQL 5.7 Community Server.”
1. Duplicate Figure D-10 in a worksheet (or spreadsheet) in an appropriate tool (such
as Microsoft Excel or Apache OpenOffice Calc).
Appendix E – Advanced SQL
This is self-explanatory. See the IRC file DBC-e08-JRJ-AppE-Appraisals.xlsx. This file
contains a worksheet similar to the one in Figure D-10 and another worksheet that
has been cleaned up to facilitate importing, as described in Appendices A, B, and C.
Note that this work has been done by hand since we have a very small database; for
larger databases these activities can be automated using a combination of
spreadsheet and database techniques, but that is beyond the scope of this book.
2. Import the data into one or more new tables in the JRJ database. You must
determine all table characteristics needed (primary key, foreign keys, data types,
etc.).
Follow the instructions in the relevant appendix to accomplish this task:
Foreign keys will be handled in question 3; here we import the data and set the primary
key. Note that we choose (ItemNumber, AppraisalDate) as a composite primary key
because some items may be appraised more than once. 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 table named APRAISALS-CLEAN$. After renaming
the tables to APPRAISALS, we will first change the type of ItemNumber from Float to Int,
ensure that both ItemNumber and AppraisalDate are NOT NULL, then set the primary
key. Note that we omit certain additional details that will need checking, specifically
data types and NULL/NOT NULL constraints for other fields.
ALTER TABLE APPRAISALS
ALTER COLUMN ItemNumber INT NOT NULL;
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 90 of 107
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. Here are the post-import SQL statements and resulting table:
ALTER TABLE APPRAISALS
For MySQL: See the MySQL solutions file.
3. Link this (these) new table(s) as appropriate to any other tables in the JRJ database.
Explain why you chose to make the connection(s) that you made.
Appendix E – Advanced SQL
ANSWERS TO THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS
These questions are based on Chapter 3's Queen Anne Curiosity Shop project questions. Base
your answers to the questions that follow on the Queen Anne Curiosity Shop project database,
as described there. Run your SQL statements in an actual DBMS to validate your work.
For the database solutions for the Project Questions about QACS, see the IRC files
supplied and use:
• Microsoft Access:
• DBC-e08-QACS.accdb
• SQL Server 2016:
• DBC-e08-MSSQL-QACS-Create-Tables.sql
• DBC-e08-MSSQL-QACS-Insert-Data.sql
• DBC-e08-MSSQL-QACS-SQL-AppE.sql
• Oracle Database Express Edition 11g Release 2:
• DBC-e08-ODB-QACS-Create-Tables.sql
• DBC-e08-ODB-QACS-Insert-Data.sql
• DBC-e08-ODB-QACS-SQL-AppE.sql
• MySQL 5.7:
• DBC-e08-MySQL-QACS-Create-Tables.sql
• DBC-e08-MySQL-QACS-Insert-Data.sql
• DBC-e08-MySQL-QACS-SQL-AppE.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. Customers may only refer one new
customer. The column characteristics for the ReferredBy column are shown in Figure E-51.
Populate the column with the data shown in Figure E-52.
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
UPDATE CUSTOMER
SET ReferredBy = 5 WHERE CustomerID = 8;
SELECT * FROM CUSTOMER;
B. Add a column to the EMPLOYEE table named Supervisor, which will contain data showing
who supervises an employee. One employee may supervise more than one other employee.
Column characteristics for the QACS Supervisor column are shown in Figure E-53. Populate
the column with the data shown in Figure E-54.
Appendix E – Advanced SQL
ALTER TABLE EMPLOYEE
ADD Supervisor Int NULL;
C. The Queen Anne Curiosity Shop requires that each employee complete a training program.
Add a column to the EMPLOYEE table named Training, which will contain data showing the
training status for each employee. Column characteristics for the QACS Training column are
shown in Figure E-53. Populate the column with the data shown in Figure E-54.
NOTE: we omit the CHECK constraint for now; question E 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 Training Varchar(20) NULL;
The last phase differs among systems. For SQL Server and Access:
