Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Chapter Three – Structured Query Language
G. Write an SQL statement to list the INVOICE.InvoiceNumber for sales that include the
Heather Sweeney Seminar Live in Dallas on 25-OCT-15 video. Use a subquery. (Hint:
The correct solution uses three tables in the query because the question asks for
INVOICE.InvoiceNumber. Otherwise, there is a possible solution with only two tables in
the query.)
-- Two table solution - starting with LINE_ITEM
SELECT InvoiceNumber
FROM LINE_ITEM
-- Three table solution - starting with INVOICE
SELECT InvoiceNumber
FROM INVOICE
WHERE InvoiceNumber IN
Chapter Three – Structured Query Language
H. Answer part G but use a join in JOIN ON syntax. (Hint: The correct solution uses three
tables in the query because the question asks for INVOICE.InvoiceNumber. Otherwise,
there is a possible solution with only two tables in the query.)
For SQL Server and MySQL:
-- Two table solution
SELECT LI.InvoiceNumber
FROM LINE_ITEM AS LI JOIN PRODUCT AS P
-- Three table solution
SELECT I.InvoiceNumber
FROM INVOICE AS I JOIN LINE_ITEM AS LI
ON I.InvoiceNumber = LI.InvoiceNumber
JOIN PRODUCT AS P
For Oracle:
-- Two table solution
SELECT LI.InvoiceNumber
FROM LINE_ITEM LI JOIN PRODUCT P
-- Three table solution
SELECT I.InvoiceNumber
FROM INVOICE I JOIN LINE_ITEM LI
ON I.InvoiceNumber = LI.InvoiceNumber
For Access:
-- Two table solution
SELECT LI.InvoiceNumber
Chapter Three – Structured Query Language
-- Three table solution
SELECT I.InvoiceNumber
FROM (INVOICE AS I INNER JOIN LINE_ITEM AS LI
ON I.InvoiceNumber = LI.InvoiceNumber)
I. Write an SQL statement to list the FirstName, LastName, and Phone of customers (list
each name only once) who have attended the Kitchen on a Big D Budget seminar.
For SQL Server and MySQL:
For Oracle:
For Access:
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 84 of 154
J. Write an SQL statement to list the FirstName, LastName, Phone, ProductNumber and
ProductDescription for customers who have purchased a video product (list each
combination of name and product only once). Sort the results by LastName in
descending order, then by FirstName in descending order, and then by ProductNumber
in descending order. (Hint: Video products have a ProductNumber that starts with VK.)
For SQL Server and MySQL:
SELECT DISTINCT FirstName, LastName, Phone,
P.ProductNumber, P.ProductDescription
FROM CUSTOMER AS C JOIN INVOICE AS I
For Oracle:
SELECT DISTINCT FirstName, LastName, Phone,
P.ProductNumber, P.ProductDescription
FROM CUSTOMER C JOIN INVOICE I
For Access:
Chapter Three – Structured Query Language
SELECT DISTINCT FirstName, LastName, Phone,
P.ProductNumber, P.ProductDescription
FROM ((CUSTOMER AS C INNER JOIN INVOICE AS I
ON C.CustomerID = I.CustomerID)
K. Write an SQL statement to show all Heather Sweeney Designs seminars and the
customers who attended them. The output from this statement should include any
seminars that do not have any customers shown as attending them. The SQL statement
output should list SeminarID, SeminarDate, Location, SeminarTitle, CustomerID,
LastName, and FirstName (Hint: Use JOIN ON syntax).
For SQL Server and MySQL:
SELECT S.SeminarID, S.SeminarDate, S.Location, S.SeminarTitle,
SC.CustomerID, C.LastName, C.FirstName
For Access:
Chapter Three – Structured Query Language
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.
For Oracle:
SELECT S.SeminarID, S.SeminarDate, S.Location, S.SeminarTitle,
SC.CustomerID, C.LastName, C.FirstName
L. Write an SQL statement to show all customers and the products that they have
purchased. The output from this statement should include any products that have not
been purchased by any customer. The SQL statement output should list CustomerID,
LastName, FirstName, InvoiceNumber, ProductNumber, ProductType, and
ProductDescription. (Hint: Use JOIN ON syntax).
For SQL Server and MySQL:
SELECT C.CustomerID, C.LastName, C.FirstName, I.InvoiceNumber,
LI.ProductNumber, P.ProductType, P.ProductDescription
FROM CUSTOMER as C JOIN INVOICE as I
For Oracle:
Chapter Three – Structured Query Language
SELECT C.CustomerID, C.LastName, C.FirstName, I.InvoiceNumber,
LI.ProductNumber, P.ProductType, P.ProductDescription
FROM CUSTOMER C JOIN INVOICE I
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
Chapter Three – Structured Query Language
M. Write an SQL statement to show the sum of SubTotal (this is the money earned by HSD
N. Write an SQL statement to show the average of Subtotal (this is the money earned by
HSD on products sold exclusive of shipping costs and taxes) for INVOICE as
AverageOfSubTotal.
O. Write an SQL statement to show both the sum and the average of Subtotal (this is the
money earned by HSD on products sold exclusive of shipping costs and taxes) for
INVOICE as SumOfSubTotal and AverageOfSubTotal respectively.
P. Write an SQL statement to modify PRODUCT UnitPrice for ProductNumber VK004 to
$34.95 instead of the current UnitPrice of $24.95.
UPDATE PRODUCT
SET UnitPrice = 34.95
Chapter Three – Structured Query Language
Q. Write an SQL statement to undo the UnitPrice modification in part N.
UPDATE PRODUCT
SET UnitPrice = 24.95
R. Do not run your answer to the following question in your actual database! Write
the fewest number of DELETE statements possible to remove all the data in your
database but leave the table structures intact.
This answer is based on CASCADING DELETES. When DELETEs cascade, this can clear data from
tables with foreign keys based on the deleted primary key. In the HSD database, there is a
CASCADING DELETE from INVOICE to LINE_ITEM, so we don’t need to delete any LINE_ITEMs
explicitly.
Chapter Three – Structured Query Language
ANSWERS TO GARDEN GLORY PROJECT QUESTIONS
Assume that Garden Glory designs a database with the following tables.
OWNER (OwnerID, OwnerName, OwnerEmailAddress, OwnerType)
The referential integrity constraints are:
OwnerID in OWNED_PROPERTY must exist in OwnerID in OWNER
Assume that OwnerID in OWNER, PropertyID in OWNED_PROPERTY, ServiceID in
GG_SERVICE, EmployeeID in EMPLOYEE, and PropertyServiceID in
PROPERTY_SERVICE are surrogate keys with values as follows:
OwnerID Start at 1 Increment by 1
PropertyID Start at 1 Increment by 1
Sample data are shown in Figures 3-39, 3-40, 3-41, 3-42, and 3-43. OwnerType is either
Individual or Corporation. PropertyType is one of Office, Apartments, or Private
Residence. ExperienceLevel is one of Junior, Senior, or Master. These tables,
referential integrity constraints, and data are used as the basis for the SQL statements
Chapter Three – Structured Query Language
Note to Instructor: For the database solutions for the Project Questions about
GARDEN_GLORY, see the IRC files supplied and use:
• Microsoft Access:
• DBC-08-GG.accdb
• SQL Server 2016:
• DBC-e08-MSSQL-GG-Create-Tables.sql
• DBC-e08-MSSQL-GG-Insert-Data.sql
• DBC-e08-MSSQL-GG-SQL-Queries-CH03.sql
• Oracle Database 11g Release 2:
• DBC-e08-ODB-GG-Create-Tables.sql
• DBC-e08-ODB-GG-Insert-Data.sql
• DBC-e08-ODB-GG-SQL-Queries-CH03.sql
• MySQL 5.7:
• DBC-e08-MySQL-GG-Create-Tables.sql
• DBC-e08-MySQL-GG-Insert-Data.sql
• DBC-e08-MySQL-GG-SQL-Queries-CH03.sql
Write SQL statements and answer questions for this database as follows:
A. Write 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 updates and deletions and justify those assumptions.
(Hint: You can combine the SQL for your answers to questions 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
ADD CONSTRAINT PROPERTY_OWNER_FK FOREIGN KEY (OwnerID)
REFERENCES OWNER(OwnerID);
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 the CREATE TABLE statements as follows:
For SQL Server:
CREATE TABLE OWNER(
OwnerID Int NOT NULL IDENTITY(1,1),
OwnerName Char(50) NOT NULL,
);
CREATE TABLE OWNED_PROPERTY(
PropertyID Int NOT NULL IDENTITY(1,1),
PropertyName VarChar(50) NOT NULL,
PropertyType VarChar(50) NOT NULL,
);
CREATE TABLE EMPLOYEE(
EmployeeID Int NOT NULL IDENTITY(1,1),
LastName Char(25) NOT NULL,
Chapter Three – Structured Query Language
CREATE TABLE GG_SERVICE(
CREATE TABLE PROPERTY_SERVICE(
PropertyServiceID Int NOT NULL IDENTITY (1,1),
PropertyID Int NOT NULL,
CONSTRAINT PROP_SERVICE_SERVICE_FK FOREIGN KEY (ServiceID)
REFERENCES GG_SERVICE(ServiceID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
Note that there are no cascading deletions because we don’t want to allow for inadvertently
removing property, service, owner, or employee records. Since all the referenced primary keys
are surrogate keys (and thus should never be updated), we do not need to cascade updates.
For Microsoft Access:
Access SQL does not support the (m, n) extension of the Numeric data type. Create
PROPERTY_SERVICE.HoursWorked and GG_SERVICE.CostPerHour as Numeric, and then set the
column properties in the GUI. CHECK constraints must be added via the GUI validation rules.
For Oracle Database:
Chapter Three – Structured Query Language
and set starting values and increment values. Therefore, the definitions of the surrogate keys
should be written as:
CREATE TABLE OWNER(
CREATE TABLE OWNED_PROPERTY(
PropertyID Int NOT NULL,
.
.
.
);
CREATE SEQUENCE seqOPID INCREMENT BY 1 START WITH 1;
CREATE TABLE GG_SERVICE(
ServiceID Int NOT NULL,
.
.
.
);
CREATE SEQUENCE seqGGSID INCREMENT BY 1 START WITH 1;
For MySQL:
The SQL CREATE TABLE commands shown for SQL Server 2016 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 surrogate keys should be
written as:
Chapter Three – Structured Query Language
CREATE TABLE OWNER(
OwnerID Int NOT NULL AUTO_INCREMENT,
.
.
.
);
CREATE TABLE EMPLOYEE(
EmployeeID Int NOT NULL AUTO_INCREMENT,
.
.
.
);
CREATE TABLE PROPERTY_SERVICE(
PropertyServiceID Int NOT NULL AUTO_INCREMENT,
.
.
.
);
C. Write SQL statements to insert the data into each of the five Garden Glory database
tables. Assume that any surrogate key value will be supplied by the DBMS. Use the
data in Figures 3-39, 3-40, 3-41, 3-42, and 3-43.
Chapter Three – Structured Query Language
Thus, for example, SQL Server would use the SQL command:
Whereas Microsoft Access and MySQL would use:
Oracle would use:
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, OWNER needs to be populated before OWNED_PROPERTY. Also,
OWNED_PROPERTY, GG_SERVICE, and EMPLOYEE need to be populated before
PROPERTY_SERVICE. Therefore, we will enter data into the tables in the following order (other
orders are possible):
OWNER No foreign keys
EMPLOYEE No foreign keys
/***** OWNER DATA ***********************************************************/
INSERT INTO OWNER VALUES(
'Mary Jones', 'Mary.Jones@somewhere.com', 'Individual');
INSERT INTO OWNER VALUES(
Chapter Three – Structured Query Language
/***** EMPLOYEE DATA ****************************************************/
INSERT INTO EMPLOYEE VALUES(
'Smith', 'Sam', '206-254-1234', 'Master');
INSERT INTO EMPLOYEE VALUES(
'Evanston', 'John','206-254-2345', 'Senior');
INSERT INTO EMPLOYEE VALUES(
/***** GG_SERVICE DATA ***************************************************/
INSERT INTO GG_SERVICE VALUES('Mow Lawn', 25.00);
INSERT INTO GG_SERVICE VALUES('Plant Annuals', 25.00);
INSERT INTO GG_SERVICE VALUES('Weed Garden', 30.00);
Chapter Three – Structured Query Language
/***** OWNED_PROPERTY DATA ************************************************/
INSERT INTO OWNED_PROPERTY VALUES(
'Eastlake Building', 'Office', '123 Eastlake',
'Seattle', 'WA', '98119', 2);
INSERT INTO OWNED_PROPERTY VALUES(
'Elm St Apts', 'Apartments', '4 East Elm',
'Lynwood', 'WA', '98223', 1);
INSERT INTO OWNED_PROPERTY VALUES(
'Jefferson Hill', 'Office', '42 West 7th St',
'Bellevue', 'WA', '98007', 2);
INSERT INTO OWNED_PROPERTY VALUES(
'Lake View Apts', 'Apartments', '1265 32nd Avenue',
'Redmond', 'WA', '98052', 3);
***** PROPERTY_SERVICE ****************************************************/
INSERT INTO PROPERTY_SERVICE VALUES(1, 2, '05-MAY-17', 1, 4.50);
INSERT INTO PROPERTY_SERVICE VALUES(3, 2, '08-MAY-17', 3, 4.50);
INSERT INTO PROPERTY_SERVICE VALUES(2, 1, '08-MAY-17', 2, 2.75);
INSERT INTO PROPERTY_SERVICE VALUES(6, 1, '10-MAY-17', 5, 2.50);
INSERT INTO PROPERTY_SERVICE VALUES(5, 4, '12-MAY-17', 4, 7.50);
Chapter Three – Structured Query Language
D. Write SQL statements to list all columns for all tables.
SELECT * FROM OWNER;
SELECT * FROM EMPLOYEE;
SELECT * FROM GG_SERVICE;
