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