Database Storage & Design Appendix E Appendix Advanced Sql How Did Your Steps Add The Training Column Differ

Appendix E – Advanced SQL

D. How did your steps to add the Training column differ from your steps to add the Supervisor

column? Why was (were) the additional step(s) necessary?

E. 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 Training column.

In Access, add a validation rule via the GUI to handle this. For other systems:

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 QACS 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:

Appendix E – Advanced SQL

For SQL Server, MySQL, and Oracle:

G. 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 QACS using the referring customer’s FirstName (as RefereerFirstName) and

LastName (as RefereerLastName). Do include customers who were not referred by another

customer.

Appendix E – Advanced SQL

H. 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:

For SQL Server, MySQL, and Oracle:

I. 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 include employees who

do not have a supervisor.

Appendix E – Advanced SQL

J. Suppose that the Queen Anne Curiosity Shop 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.

SELECT C1.CustomerID, C1.FirstName, C1.LastName

FROM CUSTOMER C1

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

END

L. Create the following SQL view statements:

1. Create an SQL view named BasicCustomerView that shows each customer’s

CustomerID, LastName, FirstName, Phone, and EmailAddress.

For Microsoft Access:

Create and save the following Access query as viewBasicCustomer.

For SQL Server, Oracle Database and MySQL:

2. Create an SQL view named BasicCustomerFirstNameFirstView that shows each

customer’s CustomerID, then LastName and FirstName concatenated using the

Appendix E – Advanced SQL

FirstNameFirst user-defined function and displayed as CustomerName, Phone,

and EmailAddress.

For SQL Server and MySQL:

For Oracle Database (shorter view name required; no “dbo.” needed):

3. Create an SQL view named SaleItemItemView that shows SaleID, SaleItemID,

SALE_ITEM.ItemID, SaleDate, ItemDescription, ItemCost, ITEM.ItemPrice as

ListItemPrice, and SALE_ITEM.ItemPrice as ActualItemPrice.

For Microsoft Access:

Create and save the following Access query as viewSaleItemItem.

For SQL Server and MySQL:

Appendix E – Advanced SQL

For Oracle Database:

M. Create (and run) the following SQL queries:

1. Create an SQL statement to run BasicCustomerView, with the results sorted

alphabetically by LastName and FirstName.

For Microsoft Access:

For SQL Server, Oracle Database, and MySQL:

Appendix E – Advanced SQL

2. Create an SQL statement to run BasicCustomerFirstNameFirstView, with the

results sorted alphabetically by CustomerName.

For SQL Server and MySQL:

For Oracle Database:

SELECT *

FROM BasicCustFirstNameFirstView

ORDER BY CustomerName;

3. Create an SQL statement to run SaleItemItemView, with the results sorted by

SaleID and SaleItemID.

For Microsoft Access:

For SQL Server, Oracle Database, and MySQL:

Appendix E – Advanced SQL

4. Create an SQL query that uses the SaleItemItemView to calculate and display

the sum of SALE_ITEM.ItemPrice (which is relabeled as ActualItemPrice) as

TotalPretaxRetailSales.

For Microsoft Access:

Appendix E – Advanced SQL

N. The Queen Anne Curiosity Shop owners and staff have decided to sell standardized items

that can be stocked and reordered as necessary. So far, they have kept their records for

these items in a Microsoft Excel worksheet, as shown in Figure E-55. They have decided to

integrate this data into the QACS database, and they want to import this data into one or

more database tables.

Appendix E – Advanced SQL

1. Duplicate Figure E-55 in a worksheet (or spreadsheet) in an appropriate tool

(such as Microsoft Excel or Apache OpenOffice Calc).

2. Import the data into one or more new tables in the QACS 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:

Appendix E – Advanced SQL

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:

ALTER TABLE MERCHANDISE

ALTER COLUMN ItemNumber INT NOT NULL;

ALTER TABLE MERCHANDISE

ALTER COLUMN VendorID INT NOT NULL;

SELECT * FROM MERCHANDISE;

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:

Appendix E – Advanced SQL

SELECT * FROM MERCHANDISE;

For MySQL: See the MySQL solutions file.

3. Link this (these) new table(s) as appropriate to one or more existing tables in the

QACS database. Explain why you chose to make the connection(s) that you

made.

ALTER TABLE MERCHANDISE

ADD CONSTRAINT MERCH_FK_VENDOR FOREIGN KEY (VendorID)

REFERENCES VENDOR(VendorID);