Database Storage & Design Appendix E Appendix Advanced Sql Note Are Not Implementing The Unique Constraint Its Longer

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

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

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.