Database Storage & Design Appendix E Appendix Advanced Sql Alter Table Employee Add Supervisor Int Null Alter Table

Appendix E – Advanced SQL

ALTER TABLE EMPLOYEE

ADD Supervisor Int NULL;

B. Garden Glory requires that each employee complete an apprenticeship program. Add a

column to the EMPLOYEE table named Apprenticeship, which will contain data showing

Appendix E – Advanced SQL

the apprenticeship status for each employee. Column characteristics for the GG

Apprenticeship column are shown in Figure E-48. Populate the column with the data

shown in Figure E-49.

NOTE: we omit the CHECK constraint for now; question D 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 Apprenticeship Varchar(20) NULL;

The last phase differs among systems. For SQL Server and Access:

C. How did your steps to add the Apprenticeship column differ from your steps to add the

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

Appendix E – Advanced SQL

column is added to an existing row, it is automatically populated with NULL by the DBMS, and

that would violate the constraint. So we create the column, populate it, then make it NOT NULL.

D. 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 Apprenticeship

column.

In Access, use a validation rule via the GUI. Other systems:

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

SELECT E.FirstName AS EmployeeFirstName,

For SQL Server, MySQL, and Oracle:

SELECT E.FirstName AS EmployeeFirstName,

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

Appendix E – Advanced SQL

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,

G. Write an SQL SELECT statement to create a correlated subquery to determine if there

are any employees who have the same combination of LastName and FirstName. Hint:

If no employees meet this condition, the correct query result will be an empty set.

SELECT E1.EmployeeID, E1.FirstName, E1.LastName

FROM EMPLOYEE E1

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

I. Create the following SQL views:

1. Create an SQL view named OwnerPropertyView that shows OWNER.OwnerID,

OwnerName, PropertyType, PropertyID, PropertyName, Street, City, State, and

ZIP.

For Microsoft Access:

Create and save the following Access query as viewOwnerProperty.

For SQL Server and MySQL:

For Oracle Database:

Appendix E – Advanced SQL

CREATE VIEW OwnerPropertyView AS

2. Create an SQL view named PropertyServiceView that shows

OWNED_PROPERTY. PropertyID, PropertyName, Street, City, State, ZIP,

ServiceDate, FirstName, LastName, and HoursWorked.

For Microsoft Access:

Create and save the following Access query as viewPropertyService.

For SQL Server and MySQL:

For Oracle Database:

CREATE VIEW PropertyServiceView AS

SELECT P.PropertyID, PropertyName, Street, City, State, ZIP,

Appendix E – Advanced SQL

3. Create an SQL view named PropertyServiceFirstNameFirstView that shows

OWNED_PROPERTY.PropertyID, PropertyName, Street, City, State, ZIP,

ServiceDate, then LastName and FirstName concatenated using the

FirstNameFirst user-defined function and displayed as EmployeeName, and

HoursWorked.

For SQL Server and MySQL:

For Oracle Database:

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

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

alphabetically by OwnerName.

For Microsoft Access:

Appendix E – Advanced SQL

2. Create an SQL statement to run PropertyServiceView, with the results sorted

alphabetically by ZIP, State, and City.

For Microsoft Access:

For SQL Server, Oracle Database, and MySQL:

SELECT *

FROM PropertyServiceView

ORDER BY ZIP, State, City;

3. Create an SQL statement to run PropertyServiceFirstNameFirstView, with the

results sorted alphabetically by ZIP, State, and City.

For SQL Server and MySQL:

Appendix E – Advanced SQL

For Oracle Database:

SELECT *

FROM PropServiceFirstNameFirstView

ORDER BY ZIP, State, City;

K. Garden Glory staff keep a record of tool inventory and who uses those tools in a

Microsoft Excel worksheet, as shown in Figure E-50. Garden Glory now wants to import

this data into one or more database tables.

Appendix E – Advanced SQL

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

(such as Microsoft Excel or Apache OpenOffice Calc).

This is self-explanatory. However, it does require splitting the worksheet into two

tables: TOOLS and an intersection table named TOOL_USE to record which tools are

2. Import the data into one or more new tables in the GG database. You must

determine all tables characteristics needed (primary key, foreign keys, data

types, etc.)

Follow the instructions in the relevant appendix to accomplish this task:

Appendix E – Advanced SQL

Foreign keys will be handled in question 3; here we import the data, populate any

unpopulated columns, and set the primary keys. We will create a column in the

TOOL_USE table that will hold an EmployeeID, which can be determined using the

employee name data in the TOOL_USE table. This column will become a foreign key in

the next question. It is possible, again, to automate the process of converting employee

names into EmployeeIDs, but we will do that by hand here using UPDATE statements

since we have a small database and the other method is beyond the scope of this book.

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 tables named TOOLS$ and TOOL_USE$. After

renaming the tables to remove the $ at the end of each, we will first change the types of

both ToolID columns from Float to Int, then add an EmployeeID column to TOOL_USE,

populate that column, then set the primary keys and drop any unnecessary imported

columns. Note that we omit certain additional details that will need checking,

specifically data types and NULL/NOT NULL constraints for other fields.

ALTER TABLE TOOLS

ALTER COLUMN ToolID Int NOT NULL;

Appendix E – Advanced SQL

ALTER TABLE TOOL_USE

DROP COLUMN UsedBy;

SELECT * FROM TOOLS;

SELECT * FROM TOOL_USE;

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. The exception here is for the DATE type data, which must first be loaded in as

VarChar then converted into a DATE format that Oracle will recognize. Here are the

post-import SQL statements and resulting tables (note the column renamed from DATE

to DATE_USED):

Appendix E – Advanced SQL

UPDATE TOOL_USE

SET EmployeeID = 4 WHERE UsedBy = ‘Jerry Murphy’;

SELECT * FROM TOOLS;

SELECT * FROM TOOL_USE;

For MySQL: See the MySQL solutions file.

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

GG database. Explain why you chose to make the connection(s) you made.

Appendix E – Advanced SQL

The tables with all their columns and data were created in question 2. Here we will link

them using foreign keys: The TOOL_USE table will have 2 foreign keys, one to refer to

the tool being used and the other to refer to the employee using the tool. These foreign

keys are needed to maintain the integrity of the data in the TOOL_USE table. We will

accept the defaults of NO ACTION for ON DELETE and ON UPDATE of both foreign keys:

we do not want to delete tool usage information, and we will not be changing the values

of our surrogate keys. Once all the data have been entered in question 2, the SQL to

create the foreign keys is the same for all systems:

ALTER TABLE TOOL_USE

 ANSWERS TO JAMES RIVER JEWELRY PROJECT QUESTIONS

[NOTE: The James River Jewelry Project Questions are available online in Appendix D,

which can be downloaded from the textbook’s Web site:

www.pearsonhighered.com/kroenke.. The solutions for these questions will be included

in the Instructor’s Manual for each chapter and appendix.]

Note: Online Appendix E covers SQL Views and other advanced SQL topics, and

logically that material should be studied in conjunction with Chapter 3 on SQL.

Therefore, the James River Jewelry Project Questions for Appendix E are

presented in this location in this appendix.

For the database solutions for the Project Questions about James River Jewelry, see the

IRC files supplied and use:

• Microsoft Access:

• DBC–e08-JRJ.accdb

Appendix E – Advanced SQL

• SQL Server 2016:

• DBC–e08-MSSQL-JRJ-Create-Tables.sql

• Oracle Database Express Edition 11g Release 2:

• DBC–e08-ODB-JRJ-Create-Tables.sql

• MySQL 5.7:

• DBC–e08–MySQL-JRJ-Create-Tables.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. The column characteristics for

the ReferredBy column are shown in Figure D-6. Populate the column with the data

shown in Figure D–7.

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

B. Add a column to the ITEM table named ArtistHasBeenPaid, which will contain data

showing whether the artist has been paid for the piece of jewelry James River Jewelry

acquired. Column characteristics for the JRJ ArtistHasBeenPaid column are shown in

Figure D-8. Populate the column with the data shown in Figure D-9.

Appendix E – Advanced SQL

NOTE: we omit the CHECK constraint for now; question D will address that. There are three

phases to the solution: add the column, populate it, and enforce the NOT NULL constraint.

ALTER TABLE ITEM

ADD ArtistHasBeenPaid Varchar(25) NULL;

Appendix E – Advanced SQL

The last phase differs among systems. For SQL Server and Access:

ALTER TABLE ITEM

ALTER COLUMN ArtistHasBeenPaid Varchar(25) NOT NULL;

For Oracle and MySQL:

C. How did your steps to add the ArtistHasBeenPaid 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 ITEM table to ensure that only the values of

Waiting for invoice, In process, or Paid are allowed as data in the ArtistHasBeenPaid

column.

Appendix E – Advanced SQL

ALTER TABLE ITEM

ADD CONSTRAINT ArtistPaidCheck CHECK

(ArtistHasBeenPaid

IN (‘Waiting for invoice’, ‘In process’, ‘Paid’));

E. 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 James River Jewelry 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:

SELECT C.FirstName AS CustomerFirstName,

C.LastName AS CustomerLastName,

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 James River Jewelry using the referring customer’s FirstName (as

ReferrerFirstName) and LastName (as ReferrerLastName). Do include customers who

were not referred by another customer.