Chapter Three – Structured Query Language
INSERT INTO VENDOR VALUES(
‘New York Brokerage’, ‘Smith’, ‘Mark’, ‘621 Roy Street’,
‘Seattle’, ‘WA’, ‘98109’, ‘206-325-9088’, ‘206-329-9908’,
‘NYB@business.com’);
INSERT INTO VENDOR
(ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax,
EmailAddress)
VALUES(
‘Bancroft’, ‘Chris’, ‘12605 NE 6th Street’,
‘Bellevue’, ‘WA’, ‘98005’, ‘425-635-9788’, ‘425-639-9978’,
/***** ITEM DATA ************************************************************/
INSERT INTO ITEM VALUES(
‘Antique Desk’, ’07-Nov-16‘, 1800.00, 3000.00, 2);
INSERT INTO ITEM VALUES(
‘Antique Desk Chair’, ’10-Nov-16‘, 300.00, 500.00, 4);
INSERT INTO ITEM VALUES(
‘Dining Table Linens’, ’14–Nov-16‘, 600.00, 1000.00, 1);
INSERT INTO ITEM VALUES(
‘Candles’, ’14-Nov-16‘, 30.00, 50.00, 1);
INSERT INTO ITEM VALUES(
‘Antique Chair’, ’21-Nov-16‘, 750.00, 1250.00, 6);
INSERT INTO ITEM VALUES(
‘Antique Chair’, ’21-Nov-16‘, 1050.00, 1750.00, 6);
INSERT INTO ITEM VALUES(
Chapter Three – Structured Query Language
INSERT INTO ITEM VALUES(
‘Antique Desk Chair’, ’06-Jan-17′, 285.00, 475.00, 9);
INSERT INTO ITEM VALUES(
‘Antique Desk Chair’, ’06-Jan-17′, 339.00, 565.00, 9);
INSERT INTO ITEM VALUES(
‘Desk Lamp’, ’06-Jan-17′, 150.00, 250.00, 10);
INSERT INTO ITEM VALUES(
INSERT INTO ITEM VALUES(
‘Dining Table Chairs’, ’11–Jan-17‘, 5100.00, 8500.00, 9);
INSERT INTO ITEM VALUES(
‘Dining Table Linens’, ’12–Jan-17′, 450.00, 750.00, 1);
INSERT INTO ITEM VALUES(
‘Dining Table Linens’, ’12–Jan-17′, 480.00, 800.00, 1);
INSERT INTO ITEM VALUES(
/***** SALE DATA ************************************************************/
INSERT INTO SALE VALUES(1, 1, ’14-Dec-16‘, 3500.00, 290.50, 3790.50);
INSERT INTO SALE VALUES(2, 1, ’15-Dec-16‘, 1000.00, 83.00, 1083.00);
INSERT INTO SALE VALUES(3, 1, ’15-Dec-16‘, 50.00, 4.15, 54.15);
INSERT INTO SALE VALUES(4, 3, ’23-Dec-16‘, 45.00, 3.74, 48.74);
INSERT INTO SALE VALUES(1, 5, ’05-Jan-17′, 250.00, 20.75, 270.75);
INSERT INTO SALE VALUES(5, 5, ’10-Jan-17′, 750.00, 62.25, 812.25);
INSERT INTO SALE VALUES(6, 4, ’12-Jan-17′, 250.00, 20.75, 270.75);
/***** SALE_ITEM DATA *******************************************************/
INSERT INTO SALE_ITEM VALUES(1, 1, 1, 3000.00);
INSERT INTO SALE_ITEM VALUES(1, 2, 2, 500.00);
INSERT INTO SALE_ITEM VALUES(2, 1, 3, 1000.00);
INSERT INTO SALE_ITEM VALUES(3, 1, 4, 50.00);
Chapter Three – Structured Query Language
INSERT INTO SALE_ITEM VALUES(8, 2, 10, 1750.00);
INSERT INTO SALE_ITEM VALUES(9, 1, 11, 350.00);
INSERT INTO SALE_ITEM VALUES(10, 1, 19, 5000.00);
INSERT INTO SALE_ITEM VALUES(10, 2, 21, 8500.00);
INSERT INTO SALE_ITEM VALUES(10, 3, 22, 750.00);
D. Write SQL statements to list all columns for all tables.
SELECT * FROM CUSTOMER;
SELECT * FROM EMPLOYEE;
SELECT * FROM VENDOR;
Chapter Three – Structured Query Language
SELECT * FROM ITEM;
SELECT * FROM SALE;
Chapter Three – Structured Query Language
SELECT * FROM SALE_ITEM;
Chapter Three – Structured Query Language
E. Write an SQL statement to list ItemID and ItemDescription for all items that cost $1000
or more.
SELECT ItemID, ItemDescription
FROM ITEM
WHERE ItemPrice >= 1000;
F. Write an SQL statement to list ItemID and ItemDescription for all items that cost $1000
or more and were purchased from a vendor whose CompanyName starts with the letters
New.
For SQL Server, Oracle, and MySQL:
SELECT ItemID, ItemDescription
FROM ITEM, VENDOR
For Access:
SELECT ItemID, ItemDescription
FROM ITEM, VENDOR
WHERE ItemPrice >= 1000
G. Write an SQL statement to list LastName, FirstName, and Phone of the customer who
made the purchase with SaleID 1. Use a subquery.
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE CustomerID IN
Chapter Three – Structured Query Language
H. Answer part G but use a join using JOIN ON syntax.
For SQL Server, Oracle, and MySQL:
SELECT LastName, FirstName, Phone
FROM CUSTOMER JOIN SALE
For Access:
SELECT LastName, FirstName, Phone
FROM CUSTOMER as C INNER JOIN SALE as S
I. Write an SQL statement to list LastName, FirstName, and Phone of the customers who
made the purchases with SaleIDs 1, 2, and 3. Use a subquery.
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE CustomerID IN
J. Answer part I but use a join using JOIN ON syntax.
For SQL Server and MySQL:
SELECT LastName, FirstName, Phone
FROM CUSTOMER AS C JOIN SALE AS S
For Oracle:
SELECT LastName, FirstName, Phone
For Access:
SELECT LastName, FirstName, Phone
FROM CUSTOMER AS C INNER JOIN SALE AS S
Chapter Three – Structured Query Language
K. Write an SQL statement to list LastName, FirstName, and Phone of customers who have
made at least one purchase with SubTotal greater than $500. Use a subquery.
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE CustomerID IN
L. Answer part K but use a join using JOIN ON syntax.
For SQL Server and MySQL:
SELECT DISTINCT LastName, FirstName, Phone
For Oracle:
SELECT DISTINCT LastName, FirstName, Phone
FROM CUSTOMER C JOIN SALE S
For Access:
SELECT DISTINCT LastName, FirstName, Phone
FROM CUSTOMER AS C INNER JOIN SALE AS S
ON C.CustomerID = S.CustomerID
WHERE SubTotal > 500;
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 146 of 154
This is the same result, although sorted in a different order. Note the use of the SQL keyword
DISTINCT to eliminate duplicate rows. Be careful, however, to understand that this is NOT
always going to have the same result as in part K: if two customers have the same first name,
M. Write an SQL statement to list LastName, FirstName, and Phone of customers who have
purchased an item that has an ItemPrice of $500 or more. Use a subquery.
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE CustomerID IN
(SELECT CustomerID
N. Answer part M but use a join using JOIN ON syntax.
For SQL Server and MySQL:
SELECT DISTINCT LastName, FirstName, Phone
FROM CUSTOMER AS C JOIN SALE AS S
For Oracle:
SELECT DISTINCT LastName, FirstName, Phone
FROM CUSTOMER C JOIN SALE S
ON C.CustomerID = S.CustomerID
Chapter Three – Structured Query Language
For Access:
SELECT DISTINCT LastName, FirstName, Phone
FROM (CUSTOMER AS C INNER JOIN SALE AS S
ON C.CustomerID = S.CustomerID)
This is the same result, although sorted in a different order. Note the use of the SQL keyword
DISTINCT to eliminate duplicate rows. Be careful, however, to understand that this is NOT
always going to have the same result as in part M: if two customers have the same first name,
O. Write an SQL statement to list LastName, FirstName, and Phone of customers who have
purchased an item that was supplied by a vendor with a CompanyName that begins with
the letter L. Use a subquery.
For Microsoft Access:
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE CustomerID IN
(SELECT CustomerID
FROM SALE
WHERE SaleID IN
For SQL Server, Oracle, and MySQL:
Chapter Three – Structured Query Language
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE CustomerID IN
(SELECT CustomerID
FROM SALE
WHERE SaleID IN
P. Answer part O but use a join using JOIN ON syntax.
For Microsoft Access:
SELECT DISTINCT LastName, FirstName, C.Phone
FROM (((CUSTOMER AS C INNER JOIN SALE AS S
ON C.CustomerID = S.CustomerID)
INNER JOIN SALE_ITEM AS SI
For SQL Server and MySQL:
SELECT DISTINCT LastName, FirstName, C.Phone
FROM CUSTOMER AS C JOIN SALE AS S
ON C.CustomerID = S.CustomerID
For Oracle:
Chapter Three – Structured Query Language
SELECT DISTINCT LastName, FirstName, C.Phone
FROM CUSTOMER C JOIN SALE S
ON C.CustomerID = S.CustomerID
This is the same result, although sorted in a different order. Note the use of the SQL keyword
DISTINCT to eliminate duplicate rows. Be careful, however, to understand that this is NOT
Q. Write an SQL statement to show the sum of SubTotal for each customer. List
CustomerID, LastName, FirstName, Phone, and the calculated result. Name the sum of
SubTotal as SumOfSubTotal and sort the results by CustomerID, in descending order.
For SQL Server, Oracle, and MySQL:
SELECT CUSTOMER.CustomerID, LastName, FirstName, Phone,
SUM(SubTotal)AS SumOfSubTotal
FROM CUSTOMER JOIN SALE
For Access:
SELECT CUSTOMER.CustomerID, LastName, FirstName, Phone,
SUM(SubTotal)AS SumOfSubTotal
FROM CUSTOMER INNER JOIN SALE
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 150 of 154
R. Write an SQL statement to modify the vendor with CompanyName of Linens and Things
to Linens and Other Stuff.
SELECT * FROM VENDOR;
UPDATE VENDOR
SET CompanyName = ‘Linens and Other Stuff’
WHERE CompanyName = ‘Linens and Things’;
SELECT * FROM VENDOR;
S. Write SQL statements to switch the values of vendor CompanyName so that all rows
currently having the value Linens and Things will have the value Lamps and Lighting,
and all rows currently having the value Lamps and Lighting will have the value Linens
and Things.
Chapter Three – Structured Query Language
Note that in order to do this, we have to have the original data— not as revised in part R.
To undo part R:
UPDATE VENDOR
SELECT * FROM VENDOR;
UPDATE VENDOR
SET CompanyName = ‘Temp’
WHERE CompanyName = ‘Linens and Things’;
SELECT * FROM VENDOR;
Chapter Three – Structured Query Language
T. 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!
Note that the order of deletion is important here due to foreign keys: ITEM must be deleted
before VENDOR. Also, SALE/SALE_ITEM must be deleted before ITEM, and SALE must be
deleted before CUSTOMER and EMPLOYEE.
DELETE FROM SALE;
/* This CASCADES through SALE_ITEM, so DELETE FROM SALE_ITEM; is
NOT needed */
U. Chapter 2 discussed multivalued dependencies (pages 96-98) and the associated
multivalue, multicolumn problem and how to resolve it (page 114-118). Does the
VENDOR table have the multivalue, multicolumn problem? If so, use the discussion on
Yes. The Phone and Fax columns could be viewed this way, and other types of phone numbers
may come along for various customers. Thus, to allow for an arbitrary number (and several
possible types) of phones per customer, we will split the phone data into its own table. First, we
create the PHONE_NUMBER table:
CREATE TABLE PHONE_NUMBER (
VendorID INT NOT NULL,
Phone CHAR(12) NOT NULL,
);
To populate the PHONE_NUMBER table, we first need to make some assumptions about
PhoneType values. Numbers from the Fax column will clearly have type Fax. Numbers from the
phone column could be home, office, or cell numbers. For now, we will simply leave the
PhoneType column NULL for those numbers since we do not know the type. To actually do the
insertions, since the amount of data is small, we could simply use a series of INSERT statements
to add each phone number one at a time:
INSERT INTO PHONE_NUMBER VALUES (1, ‘206-325-6755’, NULL);
Chapter Three – Structured Query Language
INSERT INTO PHONE_NUMBER VALUES (3, ‘206-325-8977’, NULL);
INSERT INTO PHONE_NUMBER VALUES (3, ‘206-329-9897’, ‘Fax’);
INSERT INTO PHONE_NUMBER VALUES (4, ‘425-746-5433’, NULL);
INSERT INTO PHONE_NUMBER VALUES (5, ‘425-746-4322’, NULL);
INSERT INTO PHONE_NUMBER VALUES (6, ‘206-325-9088’, NULL);
Another way to populate the PHONE_NUMBER table is to use one INSERT statement for each
PhoneType appearing in the VENDOR table:
INSERT INTO PHONE_NUMBER (VendorID, Phone, PhoneType)
SELECT VendorID, Phone, NULL
FROM VENDOR;
INSERT INTO PHONE_NUMBER (VendorID, Phone, PhoneType)
Finally, we need to remove the Phone and Fax columns from the VENDOR table:
ALTER TABLE VENDOR
DROP COLUMN Fax;
Chapter Three – Structured Query Language
ALTER TABLE VENDOR
DROP COLUMN Phone;