Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
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 ***/
Appendix E – Advanced SQL
UPDATE CUSTOMER
SET ReferredBy = 7 WHERE CustomerID = 7;
B. Add a column to the SEMINAR_CUSTOMER table named Attended, which will contain
data showing whether a customer who registered for a seminar actually attended the
seminar. Column characteristics for the Attended column are shown in Figure E-44.
Populate the column with the data shown in Figure E-45.
Appendix E – Advanced SQL
NOTE: We postpone creation of the CHECK constraint to question D.
/*** Add the column as NULL allowed ***/
ALTER TABLE SEMINAR_CUSTOMER
ADD Attended Varchar(20) NULL;
Appendix E – Advanced SQL
ALTER TABLE SEMINAR_CUSTOMER
ALTER COLUMN Attended Varchar(20) NOT NULL;
For Oracle and MySQL:
C. How did your steps to add the Attended 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 SEMINAR_CUSTOMER table to ensure that only
the values of Attended or Did not attend are allowed as data in the Attended column.
E. The HSD DBA has realized that the SEMINAR_CUSTOMER table Attended column
should be NULL instead of NOT NULL as it was created, because we do not know
Appendix E – Advanced SQL
whether or not a customer will attend a seminar at the time the customer registers for the
seminar. Alter the Attended column to NULL instead of NOT NULL.
For SQL Server and Access:
For Oracle and MySQL:
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 Heather Sweeney Designs 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:
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 46 of 107
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 CustomerEmployeeLastName) followed by the name of the customer who
referred him or her to Heather Sweeney Designs using the referring customer’s
FirstName (as ReferrerFirstName) and LastName (as ReferrerLastName). Do include
customers who were not referred by another customer.
H. Write a user-defined function named FirstNameFirst that concatenates the customer’s
LastName and FirstName into a single value named CustomerName and displays, in
order, the FirstName, a space, and the LastName (Hint: Jacobs and Nancy would be
combined to read Nancy Jacobs).
For Microsoft Access:
Microsoft Access does not support SQL functions.
Appendix E – Advanced SQL
For SQL Server (Oracle Database and MySQL requires a variant using different concatenation
operators; see the Oracle solution file for exact Oracle syntax):
CREATE FUNCTION dbo.FirstNameFirst
-- These are the input parameters
I. Create the following SQL views:
1. Create an SQL view named CustomerSeminarView that shows
CUSTOMER.CustomerID, LastName, FirstName, EmailAddress, City, State,
ZIP, SeminarDate, Location, and SeminarTitle.
For Microsoft Access:
Create and save the following Access query as viewCustomerSeminar.
SELECT C.CustomerID, C.LastName, C.FirstName,
For SQL Server, Oracle Database, and MySQL:
CREATE VIEW CustomerSeminarView AS
SELECT C.CustomerID, C.LastName, C.FirstName,
Appendix E – Advanced SQL
2. Create an SQL view named CustomerFirstNameFirstSeminarView that shows
CUSTOMER.CustomerID, then LastName and FirstName concatenated using
the FirstNameFirst user-defined function and displayed as CustomerName,
EmailAddress, City, State, ZIP, SeminarDate, Location, and SeminarTitle.
Note that the syntax for function calls varies from one DBMS to another. For SQL
Server:
CREATE VIEW CustomerFirstNameFirstSeminarView AS
SELECT C.CustomerID,
dbo.FirstNameFirst(FirstName, LastName) AS CustomerName,
For Oracle (shorter identifiers required; no “dbo.” syntax required):
CREATE VIEW CustFirstNameFirstSeminarView AS
3. Create an SQL view named CustomerProductView that shows
CUSTOMER.CustomerID, LastName, FirstName, EmailAddress,
INVOICE.InvoiceNumber, InvoiceDate, PRODUCT.ProductNumber, and
ProductDescription.
For Microsoft Access:
Create and save the following Access query as viewCustomerProduct.
Appendix E – Advanced SQL
SELECT C.CustomerID, C.LastName, C.FirstName,
C.EmailAddress,
For SQL Server, Oracle Database, and MySQL:
CREATE VIEW CustomerProductView AS
SELECT C.CustomerID, C.LastName, C.FirstName,
C.EmailAddress,
4. Create an SQL view named CustomerFirstNameFirstProductView that shows
CUSTOMER.CustomerID, then LastName and FirstName concatenated using
the FirstNameFirst user-defined function and displayed as CustomerName,
EmailAddress, INVOICE.InvoiceNumber, InvoiceDate,
PRODUCT.ProductNumber, and ProductDescription.
Note that the syntax for function calls varies from one DBMS to another. For SQL
Server:
CREATE VIEW CustomerFirstNameFirstProductView AS
SELECT C.CustomerID,
dbo.FirstNameFirst(FirstName, LastName) AS CustomerName,
C.EmailAddress,
For Oracle (shorter identifiers required; no “dbo.” syntax required):
Appendix E – Advanced SQL
CREATE VIEW CustFirstNameFirstProductView AS
SELECT C.CustomerID, FirstNameFirst(C.FirstName, C.LastName) AS
CustomerName, C.EmailAddress,
J. Create and run the following SQL queries:
1. Create an SQL statement to run CustomerSeminarView, with the results sorted
alphabetically by State, City, and ZIP (in that order) in descending order.
For Microsoft Access:
For SQL Server, Oracle Database, and MySQL:
2. Create an SQL statement to run CustomerFirstNameFirstSeminarView, with the
results sorted alphabetically by State, City, and ZIP (in that order) in descending
order.
Appendix E – Advanced SQL
For SQL Server and MySQL:
SELECT *
For Oracle Database (shorter view name required):
SELECT *
3. Create an SQL statement to run CustomerSeminarView, with the results sorted
alphabetically by Location, SeminarDate, and SeminarTitle (in that order) in
ascending order.
For Microsoft Access:
For SQL Server, Oracle Database, and MySQL:
SELECT *
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 52 of 107
4. Create an SQL statement to run CustomerFirstNameFirstSeminarView, with the
results sorted alphabetically by Location, SeminarDate, and SeminarTitle (in that
order) in ascending order.
For SQL Server and MySQL:
SELECT *
For Oracle Database (shorter view name required):
5. Create an SQL statement to run CustomerProductView, with the results sorted
alphabetically by LastName, FirstName, InvoiceNumber, and ProductNumber in
ascending order.
For Microsoft Access:
Appendix E – Advanced SQL
For SQL Server, Oracle Database, and MySQL:
SELECT *
FROM CustomerProductView
ORDER BY LastName, FirstName, InvoiceNumber, ProductNumber;
6. Create an SQL statement to run CustomerFirstNameFirstProductView, with the
results sorted alphabetically by CustomerName, InvoiceNumber, and
ProductNumber in ascending order.
For SQL Server and MySQL:
Appendix E – Advanced SQL
For Oracle Database (shorter view name required):
SELECT *
FROM CustFirstNameFirstProductView
ORDER BY CustomerName, InvoiceNumber, ProductNumber;
K. Heather Sweeney Designs staff keep employees in a Microsoft Excel worksheet, as
shown in Figure E-46. Heather now wants to import this data into one or more database
tables.
Appendix E – Advanced SQL
• 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.”
• 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.”
Appendix E – Advanced SQL
1. Duplicate Figure E-46 in a worksheet (or spreadsheet) in an appropriate tool
(such as Microsoft Excel or Apache OpenOffice Calc).
2. Import the data into a temporary table in the HSD database.
Follow the instructions in the relevant appendix to accomplish this task:
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 ‘HSD EMPLOYEE FOR IMPORT$’, which
we have renamed EMPLOYEE_TEMP:
SELECT * FROM EMPLOYEE_TEMP;
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 is the resulting table:
Appendix E – Advanced SQL
For MySQL: See the MySQL solutions file.
3. Create a new table in the HSD database named EMPLOYEE. The column
characteristics for the HSD EMPLOYEE table are shown in Figure E-47.
The solutions presented for this question are for Microsoft SQL Server. Solutions for
Oracle Database XE and MySQL can be found in the IRC files referenced above. Note
that the only difference in this case is surrogate key management. More details are
available in Appendices A, B, C, H, and the solutions to questions 3.7-3.10.
CREATE TABLE EMPLOYEE (
EmployeeNumber Int NOT NULL IDENTITY(1, 1),
FirstName CHAR(25) NOT NULL,
);
4. Populate the EMPLOYEE table as much as you can with the imported data in
the temporary table. Note that you may not be able to populate all the columns
in the EMPLOYEE table based on the available data. Hint: Consider using a
bulk SQL INSERT statement.
As with the actual import process in question 2 above, this phase of bringing external
data into the DBMS and cleaning it to fit with the rest of the database differs greatly
Appendix E – Advanced SQL
INSERT INTO EMPLOYEE
(FirstName, LastName, Supervisor, OfficePhone, EmailAddress)
SELECT FirstName, LastName, NULL, OfficePhone, EmailAddress
FROM EMPLOYEE_TEMP;
SELECT * FROM EMPLOYEE;
For Oracle:
INSERT INTO EMPLOYEE
SELECT * FROM EMPLOYEE;
5. If there are any columns that still need data, write and run the SQL statements
necessary to finish populating the EMPLOYEE table.
The Supervisor column still needs to be populated: it was left NULL of necessity since
the EmployeeNumber values were unknown until the rest of the columns were
UPDATE EMPLOYEE
SET Supervisor = 1 WHERE EmployeeNumber = 2;
UPDATE EMPLOYEE
SET Supervisor = 1 WHERE EmployeeNumber = 3;
Appendix E – Advanced SQL
UPDATE EMPLOYEE
SET Supervisor = 4 WHERE EmployeeNumber = 5;
6. 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.
SELECT E.FirstName AS EmployeeFirstName,
E.LastName AS EmployeeLastName,
Appendix E – Advanced SQL
ANSWERS TO GARDEN GLORY PROJECT QUESTIONS
For the database solutions for the Project Questions about the Garden Glory Case, see
the IRC files supplied and use:
• Microsoft Access 2016:
• DBC-e08-GG.accdb
• SQL Server 2016:
• DBC-e08-MSSQL-GG-Create-Tables.sql
• DBC-e08-MSSQL-GG-Insert-Data.sql
• DBC-e08-MSSQL-GG-SQL-AppE.sql
• MySQL 5.7:
• DBC-e08-MySQL-GG-Create-Tables.sql
• DBC-e08-MySQL-GG-Insert-Data.sql
• DBC-e08-MySQL-GG-SQL-AppE.sql
Assumption: The modification of the EMPLOYEE column "Experience" as described in Chapter 3
Project Questions N and O has not been done. However, this makes no substantive difference
in the queries or results shown below.
