Database Storage & Design Chapter 3 Three Structured Query Language Answer Part But Use Join Using Join

Chapter Three – Structured Query Language

J. Answer part I but use a join using JOIN ON syntax.

For SQL Server, Oracle, and MySQL:

SELECT DISTINCT LastName, FirstName

FROM CUSTOMER JOIN PURCHASE

For Access:

SELECT DISTINCT LastName, FirstName

FROM (CUSTOMER INNER JOIN PURCHASE

Note the use of the SQL DISTINCT keyword.

K. Write an SQL statement to list LastName and FirstName of customers who have

purchased an item that was created by an artist with a last name that begins with the

letter J. Use a subquery.

For Microsoft Access:

SELECT LastName, FirstName

FROM CUSTOMER

WHERE CustomerID IN

Chapter Three – Structured Query Language

SELECT LastName, FirstName

FROM CUSTOMER

WHERE CustomerID IN

(SELECT CustomerID

FROM PURCHASE

L. Answer part K but use a join using JOIN…ON syntax

For Microsoft Access (Note this is a direct copy of the Microsoft SQL syntax):

SELECT DISTINCT CUSTOMER.LastName, CUSTOMER.FirstName

FROM ITEM INNER JOIN

((CUSTOMER INNER JOIN PURCHASE

ON CUSTOMER.CustomerID = PURCHASE.CustomerID)

For SQL Server, Oracle Database, and MySQL:

SELECT DISTINCT LastName, FirstName

FROM CUSTOMER JOIN PURCHASE

ON CUSTOMER.CustomerID = PURCHASE.CustomerID

JOIN PURCHASE_ITEM

Note the use of the SQL keyword DISTINCT.

M. Write an SQL statement to show the Name and sum of PreTaxAmount for each

customer. Use a join using JOIN ON syntax.

For SQL Server, Oracle, and MySQL:

Chapter Three – Structured Query Language

SELECT LastName, FirstName, SUM(PreTaxAmount) AS

TotalPreTaxAmount

For Access:

SELECT LastName, FirstName, SUM(PreTaxAmount) AS

TotalPreTaxAmount

N. Write an SQL statement to show the sum of PreTaxAmount for each artist (hint: the

result will have only one line per each artist). Use a join using JOIN ON syntax, and sort

the results by ArtistLastName then ArtistFirstName in ascending order. Note this should

include the full PreTaxAmount for any purchase in which the artist had an item.

For SQL Server, Oracle, and MySQL:

For Access:

SELECT ArtistLastName, ArtistFirstName,

SUM(PreTaxAmount) AS TotalPreTaxAmount

FROM (ITEM INNER JOIN PURCHASE_ITEM

Chapter Three – Structured Query Language

O. Write an SQL statement to show the sum of PreTaxAmount for each artist but exclude

any items that were part of purchases with PreTaxAmount over $25. Use a join using

JOIN ON syntax, and sort the results by ArtistLastName and ArtistFirstName in

descending order.

SELECT ArtistLastName, ArtistFirstName, SUM(PreTaxAmount) AS

TotalPreTaxAmount

P. Write an SQL statement to show which customers bought which items, and include any

items that have not been sold. Include CUSTOMER.LastName, CUSTOMER.FirstName,

InvoiceNumber, InvoiceDate, ItemNumber, ItemDescription, ArtistLastName, and

ArtistFirstName. Use a join using JOIN ON syntax, and sort the results by

ArtistLastName and ArtistFirstName in ascending order.

For SQL Server, Oracle, and MySQL:

SELECT LastName, FirstName, PURCHASE.InvoiceNumber,

InvoiceDate, PURCHASE_ITEM.ItemNumber, ItemDescription,

ArtistLastName, ArtistFirstName

For Access:

Answer omitted; Access is unable to determine the proper join order in this situation

and the solution in this situation is to use a second stored query (view), which is not

covered in this chapter.

Chapter Three – Structured Query Language

Q. Write an SQL statement to modify all ITEM rows with an artist last name of Baxter to an

artist first name of Rex.

SELECT * FROM ITEM;

UPDATE ITEM

SET ArtistFirstName = ‘Rex’

WHERE ArtistLastName = ‘Baxter’;

Chapter Three – Structured Query Language

SELECT * FROM ITEM;

R. Write SQL statements to switch the values of ArtistLastName so that all rows currently

having the value Baker will have the value Baxter and all rows currently having the value

Baxter will have the value Baker.

Chapter Three – Structured Query Language

Now run these SQL commands:

UPDATE ITEM

SET ArtistLastName = ‘temp’

WHERE ArtistLastName = ‘Baker’;

SELECT * FROM ITEM;

Chapter Three – Structured Query Language

S. Given your assumptions about cascading deletions in your answer to part B, write the

fewest number of DELETE statements possible to remove all the data in your database

but leave the table structures intact. Do not run these statements if you are using an

actual database!

Per the question, these commands will not be run!

Note the delete of PURCHASE will delete all rows in PURCHASE_ITEM as well.

Chapter Three – Structured Query Language

 ANSWERS TO THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS

Assume that The Queen Anne Curiosity Shop designs a database with the following

tables.

CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone,

EmailAddress)

The referential integrity constraints are:

VendorID in ITEM must exist in VendorID in VENDOR

CustomerID in SALE must exist in CustomerID in CUSTOMER

Assume that CustomerID of CUSTOMER, EmployeeID of EMPLOYEE, VendorID of

VENDOR, ItemID of ITEM, and SaleID of SALE are all surrogate keys with values as

follows:

CustomerID Start at 1 Increment by 1

EmployeeID Start at 1 Increment by 1

VendorID Start at 1 Increment by 1

ItemID Start at 1 Increment by 1

SaleID Start at 1 Increment by 1

SaleItemID of SALE_ITEM is not a true surrogate key, but rather a counter that starts at

1 and increments by 1 for each SaleID in SALE. This number will require special

handling in the database, needing to be either manually inserted or to have specific

program logic written to insert the correct number. In this text we will simply insert the

number manually.

A vendor may be an individual or a company. If the vendor is an individual, the

CompanyName field is left blank, while the ContactLastName and ContactFirstName

fields must have data values. If the vendor is a company, the company name is

recorded in the CompanyName field, and the name of the primary contact at the

company is recorded in the ContactLastName and ContactFirstName fields.

Some sample data are shown in Figures 3-44, 3-45, 3-46, 3-47, 3-48, and 3-49. These

tables, referential integrity constraints, and data are used as the basis for the SQL

statements you will create in the exercises that follow. If possible, run these statements

in an actual DBMS, as appropriate, to obtain your results. Name your database QACS.

Chapter Three – Structured Query Language

Use data types consistent with the DBMS you are using. If you are not using an actual

DBMS, consistently represent data types using either the SQL Server, Oracle database,

or MySQL data types shown in Figure 3-5. For each SQL statement you write, show the

results based on your data.

[[E

Note to Instructors: For the database solutions for the Project Questions about QACS,

see the IRC files supplied and use:

• Microsoft Access:

• DBC–e08-QACS.accdb

• SQL Server 2016:

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

• DBC–e08-MSSQL-QACS-Insert-Data.sql

• DBC–e08-MSSQL-QACS-SQL-Queries-CH03.sql

• Oracle Database 11g Release 2:

• DBC–e08-MySQL-QACS-Create-Tables.sql

• DBC–e08-MySQL-QACS-Insert-Data.sql

• DBC–e08-MySQL-QACS-SQL-Queries-CH03.sql

• MySQL 5.7:

• DBC–e08-MySQL-QACS-Create-Tables.sql

• DBC–e08-MySQL-QACS-Insert-Data.sql

• DBC–e08-MySQL-QACS-SQL-Queries-CH03.sql

Write SQL statements and answer questions for this database as follows:

A. Write SQL CREATE TABLE statements for each of these tables.

B. Write foreign key constraints for the relationships in each of these tables. Make your

own assumptions regarding cascading deletions and justify those assumptions. (Hint:

You can combine the SQL for your answers to parts A and B.)

Assuming the tables were created without any foreign key constraints, this could be done in all

four systems with ALTER TABLE statements as follows:

Chapter Three – Structured Query Language

ALTER TABLE SALE

ADD CONSTRAINT SALE_CUSTOMER_FK FOREIGN KEY (CustomerID)

REFERENCES CUSTOMER(CustomerID);

The remaining foreign key constraint must be specified as follows in Microsoft Access,

immediately followed by using the GUI to set the DELETE CASCADE behavior:

In SQL Server, Oracle, and MySQL, that constraint is added as follows:

Note that the ON DELETE and ON UPDATE clauses are optional when the default behavior of NO

ACTION is desired.

However, the discussion and figures in the text show this being done using the CREATE TABLE

statement. Foreign key constraints would be included in the CREATE TABLE statements as

follows:

For SQL Server 2016:

CREATE TABLE CUSTOMER(

);

CREATE TABLE EMPLOYEE(

EmployeeID Int NOT NULL IDENTITY (1, 1),

LastName Char(25) NOT NULL,

FirstName Char(25) NOT NULL,

);

Chapter Three – Structured Query Language

CREATE TABLE VENDOR(

VendorID Int NOT NULL IDENTITY (1, 1),

CompanyName Char(100) NULL,

ContactLastName Char(25) NOT NULL,

ContactFirstName Char(25) NOT NULL,

);

CREATE TABLE ITEM(

ItemID Int NOT NULL IDENTITY (1,1),

ItemDescription VarChar(255) NOT NULL,

CREATE TABLE SALE(

SaleID Int NOT NULL IDENTITY (1, 1),

CustomerID Int NOT NULL,

EmployeeID Int NOT NULL,

SaleDate Date NOT NULL,

SubTotal Numeric(15,2) NULL,

Chapter Three – Structured Query Language

CREATE TABLE SALE_ITEM(

SaleID Int NOT NULL,

SaleItemID Int NOT NULL,

ItemID Int NOT NULL,

);

For Microsoft Access:

The SQL Server solutions will work for Access with the following exceptions:

The Microsoft Access AutoNumber data type supports a starting value of 1 and an increment of

1. Therefore, all of the surrogate values called for here will work in the Access versions of these

tables. Create the five primary surrogate keys as INT NOT NULL, and then set the data type to

AutoNumber in the GUI. IMPORTANT: Do this individually for each table immediately after it

has been created!

Omit all the ON DELETE and ON UPDATE clauses. After the SALE_ITEM table has been created,

use the GUI to set the proper ON DELETE CASCADE behavior for the foreign key referencing

SALE.

For Oracle Database:

The SQL CREATE TABLE commands shown for SQL Server 2016 will also work for Oracle

Database with only one modification. Oracle Database uses SEQUENCES to set surrogate keys

and set starting values and increment values. Therefore, the definitions of the CustomerID,

EmployeeID, VendorID, ItemID, and SaleID surrogate values should be written as:

Chapter Three – Structured Query Language

CREATE TABLE EMPLOYEE(

EmployeeID Int NOT NULL,

.

);

CREATE SEQUENCE seqVID INCREMENT BY 1 START WITH 1;

CREATE TABLE ITEM(

ItemID Int NOT NULL,

.

);

For MySQL:

The SQL CREATE TABLE commands shown for SQL Server will also work for MySQL with only one

modification. MySQL uses AUTO_INCREMENT to set surrogate keys with a starting value of 1

and an increment of 1. Therefore, the definitions of the five surrogate key columns should be

written as:

Chapter Three – Structured Query Language

CREATE TABLE EMPLOYEE(

EmployeeID Int NOT NULL AUTO_INCREMENT,

.

);

CREATE TABLE ITEM(

ItemID Int NOT NULL AUTO_INCREMENT,

.

);

CREATE TABLE SALE(

SaleID Int NOT NULL AUTO_INCREMENT,

.

);

C. Write SQL statements to insert the data into each of these tables. Assume that all

surrogate key column values will be supplied by the DBMS. Use the data in Figures 3-

44, 3-45, 3-46, 3-47, 3-48, and 3-49.

Thus, for example, SQL Server would use the SQL command:

Whereas Microsoft Access and MySQL would use:

Oracle would use:

Chapter Three – Structured Query Language

INSERT INTO CUSTOMER VALUES(seqCID.NextVal,

‘Shire’, ‘Robert’, ‘6225 Evanston Ave N’,

‘Seattle’, ‘WA’, ‘98103’,

‘206-524-2433′, ‘Robert.Shire@somewhere.com’);

Finally, MySQL uses a date format of ‘YYYY-MM–DD’ as in ’2012-02–27′ and Oracle’s date formats

are numerous and depend on the default set by the DBA. See Appendix B for more information

on Oracle Database use of sequences and date functions.

The IRC files listed above contain the complete solutions for MySQL, Access, and Oracle.

The tables need to be populated in an order consistent with the referential integrity constraints.

Specifically, VENDOR needs to be populated before ITEM. Also, CUSTOMER and EMPLOYEE

need to be populated before SALE. Finally, SALE and ITEM need to be populated before

SALE_ITEM. Therefore, we will enter data into the tables in the following order (other orders

are possible):

CUSTOMER No foreign keys

EMPLOYEE No foreign keys

VENDOR No foreign keys

ITEM Foreign key: VendorID references VendorID in VENDOR

SALE Foreign Keys:

CustomerID references CustomerID in CUSTOMER

EmployeeID references EmployeeID in EMPLOYEE

SALE_ITEM Foreign Keys:

SaleID references SaleID in SALE

ItemID references ItemID in ITEM

For SQL Server :

/***** CUSTOMER DATA ********************************************************/

INSERT INTO CUSTOMER VALUES(

‘Shire’, ‘Robert’, ‘6225 Evanston Ave N’, ‘Seattle’, ‘WA’, ‘98103’,

‘206-524-2433’, ‘Robert.Shire@somewhere.com’);

INSERT INTO CUSTOMER VALUES(

‘Goodyear’, ‘Katherine’, ‘7335 11th Ave NE’, ‘Seattle’, ‘WA’, ‘98105’,

‘206-524-3544’, ‘Katherine.Goodyear@somewhere.com’);

INSERT INTO CUSTOMER VALUES(

Chapter Three – Structured Query Language

INSERT INTO CUSTOMER VALUES(

‘Svane’, ‘Jack’, ‘3211 42nd Street’, ‘Seattle’, ‘WA’, ‘98115’,

‘206-524-5766’, ‘Jack.Svane@somewhere.com’);

INSERT INTO CUSTOMER VALUES(

/***** EMPLOYEE DATA ********************************************************/

INSERT INTO EMPLOYEE VALUES(

‘Stuart’, ‘Anne’, ‘206-527-0010’, ‘Anne.Stuart@QACS.com’);

INSERT INTO EMPLOYEE VALUES(

‘Stuart’, ‘George’, ‘206-527-0011’, ‘George.Stuart@QACS.com’);

/***** VENDOR DATA **********************************************************/

INSERT INTO VENDOR VALUES(

‘Linens and Things’, ‘Huntington’, ‘Anne’, ‘1515 NW Market Street’,

‘Seattle’, ‘WA’, ‘98107’, ‘206-325-6755’, ‘206-329-9675’,

‘LAT@business.com’);

INSERT INTO VENDOR VALUES(

‘European Specialties’, ‘Tadema’, ‘Ken’, ‘6123 15th Avenue NW’,

‘Seattle’, ‘WA’, ‘98107’, ‘206-325-7866’, ‘206-329-9786’,

VALUES(

‘Lee’, ‘Andrew’, ‘1102 3rd Street’,

‘Kirkland’, ‘WA’, ‘98033’, ‘425-746-5433’, ‘Andrew.Lee@somewhere.com’);

INSERT INTO VENDOR