Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
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
© 2018 Pearson Education, Inc. Page 98 of 107
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
© 2018 Pearson Education, Inc. Page 104 of 107
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);
