Database Storage & Design Appendix E Appendix Advanced Sql Select Cfirstname Customerfirstname Clastname Customerlastname Rfirstname Referrerfirstname Rlastname Referrerlastname

Unlock access to all the studying documents.

View Full Document

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

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: