Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Database Concepts
8th Edition
David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder
Instructor’s Manual
Prepared by Scott L. Vandenberg
Chapter Three
Structured Query Language
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted,
in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior
written permission of the publisher. Printed in the United States of America.
Instructor’s Manual to accompany:
Database Concepts (8th Edition)
David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder
Chapter Three – Structured Query Language
CHAPTER OBJECTIVES
• Learn basic SQL statements for creating database structures
• Learn basic SQL statements for adding data to a database
• Learn basic SQL SELECT statements and options for processing a single table
• Learn basic SQL SELECT statements and options for processing multiple tables with
subqueries
• Learn basic SQL SELECT statements and options for processing multiple tables with
joins
• Learn basic SQL statements for modifying and deleting data from a database
• Learn basic SQL statements for modifying and deleting database tables and constraints
CHAPTER ERRATA
• Pages 137-139 – [28-FEB-17 – Corrected in the Instructor’s Manual for Chapter
3] – The following corrections to data values should be made. Note that the
values are correct in Figure 3-12 and elsewhere in the text and IRC files; these
corrections apply only to Figure 3-2:
o For the DEPARTMENT table data (Figure 3-2(a), page 137): The last
four rows have altered DepartmentPhone values as follows:
DepartmentName … DepartmentPhone
Sales and Marketing … 360-287-8500
InfoSystems … 360-287-8600
Research and Development … 360-287-8700
Production … 360-287-8800
o For the EMPLOYEE table data (Figure 3-2(b), page 138): The last four
rows have altered Supervisor values as follows:
EmployeeNumber … Supervisor …
17 … 14 …
18 … 15 …
19 … 15 …
20 … 15 …
o For the PROJECT table data (Figure 3-2(c), page 139): The name of the
Department for ProjectID 1300 is misspelled:
ProjectID … Department …
1300 … Accounting …
• Page 188 – [28-FEB-17 – Corrected in the Instructor’s Manual for Chapter 3] –
The first line of SQL-Query-CH03-47 should include ProjectID in the select fields,
as it does on page 187:
SELECT FirstName, LastName, ProjectID, HoursWorked
Chapter Three – Structured Query Language
• Page 253 – [28-FEB-17 – Corrected in the Instructor’s Manual for Chapter 3] –
The PropertyID column in the PROPERTY_SERVICE table description is not part
of the primary key and should not be underlined:
PROPERTY_SERVICE (PropertyServiceID, PropertyID, ServiceID, ServiceDate,
EmployeeID, HoursWorked)
THE ACCESS WORKBENCH
Solutions to the Access Workbench exercises may be found in Solutions to all Sections:
The Access Workbench, which is a separate document within the Instructor’s Manual.
NOTES ON MICROSOFT WINDOWS 10
This book uses the Microsoft Windows 10 operating system as the basis for screenshots
and step-by-step instructions. However, with Windows 10, Microsoft has introduced a
continuous update system that has already resulted in some fundamental differences in
how different versions of Windows 10 look and operate.
For example, in the original version of Microsoft Windows 10, clicking the Windows
Start button (or pressing the Windows key on the keyboard) displayed the menu shown
in Figure 1. In this menu, we need to click the All apps button in order to see the All
apps menu shown in Figure 2.
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 5 of 154
Figure 1 – Windows 10 Main Menu
Figure 2 – Windows 10 All Apps Menu
The All apps button
The File Explorer button
The File Explorer icon
The All apps menu
Chapter Three – Structured Query Language
Figure 3 – Windows 10 Anniversary Update Main Menu with All Apps Menu Included
Microsoft then released the Windows 10 Anniversary Update (Feature update to
Windows 10, version 1607) (see the blog discussion at
https://blogs.windows.com/windowsexperience/2016/08/02/how-to-get-the-windows-10-
anniversary-update/#K1CZuiw4auiuE9A5.97 ). One of the changes introduced in the
Anniversary Update was a major change to the menu system. Now, as shown in
Figure 3, the All apps menu is immediately available when the Start button is used (or
the keyboard Windows key is pressed).
Therefore, note that the step by step instructions in this book may need to be altered for
your use depending upon which version of Microsoft Windows 10 you or your students
are using!
We recommend that you update Windows 10 to the Windows 10 Anniversary Update
(Feature update to Windows 10, version 1607), and make sure it is patched with all
updates to that version (at a minimum patched to Windows 10 Version 1607 update for
August 23, 2016 (KB3176936), and the Windows 10 Version 1607 cumulative update for
September 29, 2016 (KB3194496)). We also recommend using the 32-bit version of
Microsoft Office. This insures that all the examples discussed in this book will function
properly.
The All apps menu
The File Explorer button
The File Explorer icon
Chapter Three – Structured Query Language
TEACHING SUGGESTIONS
• The Wedgewood Pacific (WP) database discussed in Chapter 3 is a good database
to use for an in-class demo of the concepts in this chapter. This database was the
basis of the Access Workbench Exercises in Chapters 1 and 2. If your students did
those assignments, they have a WP database ready to use for the SQL work in
Chapter 3. If you want to use Access to demonstrate the material in the chapter, see
the IRC files, The-Access-Workbench, and use:
• Microsoft Access 2016:
• DBC-e08-WP-CH03-AppE.accdb
To demonstrate the Chapter 3 SQL, including creating and populating the tables,
with SQL Server 2016, Oracle Database Express Edition 11g Release 2, or MySQL
5.7, see the IRC files and use the following material:
• Microsoft SQL Server 2016:
• DBC-e08-MSSQL-WP-Create-Tables.sql
• DBC-e08-MSSQL-WP-Insert-Data.sql
• DBC-e08-MSSQL-WP-SQL-Queries-CH03-Text-AppA-Exercises.sql
• Oracle Database Express Edition 11g Release 2:
• DBC-e08-ODB-WP-Create-Tables.sql
• DBC-e08-ODB-WP-Insert-Data.sql
• DBC-e08-ODB-WP-SQL-Queries-CH03-Text-AppB-Exercises.sql
• MySQL 5.7:
• DBC-e08-MySQL-WP-Create-Tables.sql
• DBC-e08-MySQL-WP-Insert-Data.sql
• DBC-e08-MySQL-WP-SQL-Queries-CH03-Text-AppC-Exercises.sql
See the Instructor's Manual Appendices A, B, and C for information on how to
demonstrate the SQL queries.
• If you are using Microsoft Access, remember that Microsoft Access SQL will read
and properly run most statements written in SQL Server 2016 SQL. Have your
students write SQL Server 2016 SQL statements, especially the CREATE TABLE
statements. Microsoft Access will properly translate most data types (for example,
Char(10) to Text(10)).
• If you are using Microsoft Access, you may need to take some time to motivate this
material because the students may think there’s no need for anything other than the
Microsoft Access QBE query generation tool. Reasons for learning SQL are:
• You may not always be using Microsoft Access
Chapter Three – Structured Query Language
• Some SQL keywords, such as DISTINCT and GROUP BY with HAVING are
easier to key into the SQL View window than to remember how to do it in
Microsoft Access
• If you want to construct SQL programmatically, which is common, especially with
SQL SELECT and ADO.NET or PHP, you must know SQL.
• If you are using Microsoft Access, you can use it as a teaching tool. Have the
students use the graphical facility for creating a query and then right click in the top
frame of the query window and select SQL View. They can then check how Microsoft
Access interpreted their query and learn SQL syntax in the process.
• Microsoft Access will automatically translate standard SQL data types of CHAR and
VARCHAR into the Access Short Text (less than 256 characters) or Long Text data
type. It will also automatically translate (for strings less than and ) DATE columns
into the Access Date/Time type.
• The best way to learn SQL is to do it. Students should complete the review
questions and at least one of the Garden Glory, James River, or Queen Anne
Curiosity Shop project sets.
• Microsoft’s SQL Server Management Studio and MySQL’s MySQL Workbench are
great tools for teaching and learning SQL. Use of these tools is covered in Appendix
A for SQL Server 2016 and Appendix C for MySQL 5.7. Even if you’re using
Microsoft Access, you might set up a class demo using SQL Server Management
Studio or MySQL Workbench to show students how important SQL knowledge is.
The solutions to the Review Questions and the Projects show the correct code for
SQL Server 2016.
• Warning: There is no SQL standard for creating a surrogate key. The text
discusses how surrogate keys are handled in various products, but be sure to
emphasize this point to your students and to demonstrate how to use surrogate keys
in the DBMS you are using for your class assignments.
• Warning: SQL works with the standard quote character - ' - but does not recognize
the fancier beginning and ending quote characters found in most word processing
character sets. For example, the Times New Roman font produces the quote
characters ‘ and ’ that are curled and have different beginning and ending quotes
(look carefully at the characters in this paragraph). If you cut and paste text into an
SQL tool such as Microsoft SQL Server’s SQL Management Studio, your text will not
work with the fancy quotes, but you may still get error messages and have to edit the
text. The proper quote character is used in the solutions.
• Warning: Microsoft SQL requires square brackets [ and ] around table or column
names that are SQL reserved keywords.
• Use the appropriate text editor for the DBMS you are using. For creating SQL
commands for Microsoft Access, use Microsoft’s Notepad (or a similar text editor). If
you are working with Microsoft SQL Server 2016, use the editing capabilities of the
Query windows in Microsoft’s SQL Server Management Studio. If you are using
Oracle Database XE, use the SQL Developer query windows. Moreover, if you are
working with MySQL, use the editing features of the MySQL Workbench.
Chapter Three – Structured Query Language
• Microsoft Access 2016 does not support SQL wildcard characters, although it does
have equivalent wildcard characters as described in the chapter. The correct
solutions for these questions were obtained using Microsoft SQL Server 2016, and
solutions are shown for Access as well.
• Note that Microsoft Access SQL requires the INNER JOIN syntax instead of the
standard SQL syntax JOIN used by Microsoft SQL Server, Oracle Database, and
MySQL. Also note that Oracle prohibits the “AS” keyword when aliasing table names
using the JOIN syntax.
• String comparisons using LIKE (and other operators) may or may not be case-
sensitive, depending on the DBMS used and on the default settings set up by the
DBA.
• Note that date and time values are both stored in DATE type columns in Oracle. The
use of the TO_DATE function to process date formats and the TO_CHAR function to
display just the time component of a DATE column value is necessary. See the
Oracle solution files and the solution to HSD Case Question D for more details.
• A note on DBMS names: For brevity and clarity, we will sometimes use abbreviated
forms of DBMS names in this Instructors Manual as follows:
• Microsoft Access 2016: Microsoft Access, Access
• Microsoft SQL Server 2016 Developer Edition: Microsoft SQL Server 2016,
Microsoft SQL Server, SQL Server
• Oracle Database Express Edition 11g Release 2: Oracle Database Express
Edition 11g, Oracle Database Express Edition, Oracle Database XE, Oracle
Database, Oracle
• MySQL Community Edition 5.7: MySQL Community Edition, MySQL 5.7, MySQL
ANSWERS TO REVIEW QUESTIONS
3.1 What does SQL stand for?
3.2 What is a data sublanguage?
3.3 Explain the importance of SQL-92.
3.4 Why is it important to learn SQL?
Chapter Three – Structured Query Language
3.5 Describe in your own words the purpose of the two business rules listed on page 140.
3.6 Why do some standard SQL-92 statements fail to run successfully in Microsoft
Access?
Use the following tables for your answers to questions 3.7 through 3.48:
Chapter Three – Structured Query Language
2. For the database solutions for the Review Questions about the Pet-Database, see the IRC
files supplied and use:
• Microsoft Access 2016:
• SQL Server 2016:
• Oracle Database Express Edition 11g Release 2:
• MySQL 5.7:
3. Note that, although not apparent from the data, the primary keys of PET_OWNER, PET, and
PET_3 are surrogate keys and are created that way in the solution files. All these surrogate
keys will start with 1 and increment by 1.
3.7 Write an SQL CREATE TABLE statement to create the PET_OWNER table, with
OwnerID as a surrogate key. Justify your choices of column properties.
Assumption: OwnerID is a surrogate key (starts at 1 and increments by 1)
For Microsoft Access:
The Microsoft Access AutoNumber data type only supports a starting value of 1 and an
increment of 1. (See “Does Not Work with Microsoft Access ANSI-89 SQL” on p. 140.) Therefore,
Chapter Three – Structured Query Language
For SQL Server:
For Oracle Database:
The SQL CREATE TABLE command 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 OwnerID surrogate
values should be written as:
For MySQL:
MySQL uses the AUTO_INCREMENT keyword to implement surrogate keys. By default,
AUTO_INCREMENT starts at 1 and increments by 1. Although the increment cannot be changed,
the starting value can be reset using an ALTER command.
OwnerID is an integer because it will be a surrogate key. The means by which such keys are
created varies from DBMS to DBMS. OwnerLastName and OwnerFirstName are Char rather than
VarChar because we expect the names to be similar length and want to avoid the overhead of
VarChar – but VarChar could also be a reasonable choice here, as it will save some space if
names are substantially shorter. OwnerPhone is Char because all values will be the same length.
Neither OwnerPhone nor OwnerEmail are required; they’re not keys, and we may want to
record a customer even if we have neither.
Chapter Three – Structured Query Language
Server, you can simply omit the value of the surrogate key when data are supplied for all other
columns. Oracle (see Appendix B for details) requires you to use the name of a sequence in
place of a value.
For Microsoft SQL Server:
For Oracle:
For MySQL and Microsoft Access:
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 14 of 154
3.8 Write an SQL CREATE TABLE statement to create the PET table without a referential
integrity constraint on OwnerID in PET. Justify your choices of column properties.
Why not make every column NOT NULL?
Creating the PET table:
Assumption: PetID is a surrogate key (starts at 1 and increments by 1).
For Microsoft Access:
Create PetID as NOT NULL, and then set the data type to AutoNumber in the GUI.
IMPORTANT: Do this for the table immediately after it has been created!
For SQL Server:
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 OwnerID and
PropertyID surrogate values should be written as:
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 15 of 154
CREATE TABLE PET(
PetID Int NOT NULL,
PetName Char(50) NOT NULL,
PetType Char(25) NOT NULL,
PetBreed VarChar(100) NULL,
PetDOB Date NULL,
OwnerID Int NOT NULL,
CONSTRAINT PET_PK PRIMARY KEY(PetID)
);
CREATE SEQUENCE seqPetID INCREMENT BY 1 START WITH 1;
For MySQL:
MySQL uses the AUTO_INCREMENT keyword to implement surrogate keys. By default,
AUTO_INCREMENT starts at 1 and increments by 1. Although the increment cannot be changed,
the starting value can be reset using an ALTER command.
CREATE TABLE PET(
PetID Int NOT NULL AUTO_INCREMENT,
PetID and OwnerID are integer data types because they are keys—one surrogate and one that
must match the surrogate in PET_OWNER. PetName and PetType are both required and use
Char rather than VarChar. PetName will probably be used for searching and searches can
sometimes be faster with Char if certain kinds of indexing are used (beyond the scope of this
text). PetType is small; VarChar probably not worth the extra trouble for the DBMS. PetBreed
Populating the PET table:
See the solution to review question 3.7 for more about surrogate key data entry.
For Microsoft SQL Server:
INSERT INTO PET VALUES('King', 'Dog', 'Std. Poodle', '27-Feb-14',
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 16 of 154
INSERT INTO PET VALUES('Cedro', 'Cat', 'Unknown', '06-Jun-12',
2);
INSERT INTO PET(PetName, PetType, PetBreed, OwnerID)
VALUES('Wooley', 'Cat', 'Unknown', 2);
INSERT INTO PET VALUES('Buster', 'Dog', 'Border Collie',
'11-Dec-11', 4);
For Oracle (see Appendix B for details on Oracle DATE values):
INSERT INTO PET VALUES(seqPETID.NextVal,'King', 'Dog',
'Std. Poodle', TO_DATE('02/27/14', 'MM/DD/YY'), 1);
INSERT INTO PET VALUES(seqPETID.NextVal,'Teddy', 'Cat',
For Microsoft Access and MySQL:
INSERT INTO PET (PetName, PetType, PetBreed, PetDOB, OwnerID)
VALUES('Kino', 'Dog', 'Std. Poodle', '2014-02-27', 1);
INSERT INTO PET (PetName, PetType, PetBreed, PetDOB, OwnerID)
Chapter Three – Structured Query Language
3.9 Create a referential integrity constraint on OwnerID in PET. Assume that deletions
should not cascade.
There are two methods to solving this problem: one is to drop then recreate the tables with the
proper constraint. The other is to use the ALTER TABLE command to add the constraint, as
described in The Access Workbench on page 228.
To add the constraint after creating the tables:
To include the constraint at table creation time, add it near the end of the CREATE TABLE
statement (based on solution to previous exercise):
…
3.10 Create a referential integrity constraint on OwnerID in PET. Assume that deletions
should cascade.
For Microsoft Access:
Microsoft Access SQL does not support ON DELETE and ON UPDATE constraint clauses.
Therefore, create PET as shown in Question 3.9, and select any cascading behaviors in the GUI in
the Relationships window.
For SQL Server, Oracle, and MySQL:
There are two methods to solving this problem: one is to drop then recreate the tables with the
proper constraint. The other is to use the ALTER TABLE command to add the constraint, as
described in The Access Workbench on page 228.
To add the constraint after creating the tables:
To include the constraint at table creation time, add it near the end of the CREATE TABLE
statement (based on solution to previous exercise):
Chapter Three – Structured Query Language
The following table schema for the PET_2 table is an alternate version of the PET table-
use it to answer review questions 3.11 and 3.12:
3.11 Write the required SQL statements to create the PET_2 table.
For Access, SQL Server, Oracle Database, and MySQL:
Note that we have created the proper foreign key. This table will NOT be used for data; see
question 3.12 below.
3.12 Is PET or PET_2 a better design? Explain your rationale.
3.13 Write the SQL statements necessary to remove the PET_OWNER table from the
database. Assume that the referential integrity constraint is to be removed. Do not
run these commands in an actual database!
3.14 Write the SQL statements necessary to remove the PET_OWNER table from the
database. Assume that the PET table is to be removed. Do not run these
commands in an actual database!
(and only in that order.)
3.15 Write an SQL statement to display all columns of all rows of PET. Do not use the
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 19 of 154
3.16 Write an SQL statement to display all columns of all rows of PET. Use the asterisk (*)
notation.
SELECT *
FROM PET;
3.17 Write an SQL statement to display the breed and type of all pets.
SELECT PetBreed, PetType
FROM PET;
3.18 Write an SQL statement to display the breed, type, and DOB of all pets having the
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 20 of 154
3.19 Write an SQL statement to display the PetBreed column of PET.
SELECT PetBreed
FROM PET;
3.20 Write an SQL statement to display the PetBreed column of PET. Do not show
duplicates.
SELECT DISTINCT PetBreed
FROM PET;
3.21 Write an SQL statement to display the breed, type, and DOB for all pets having the
type Dog and the breed Std. Poodle.
SELECT PetBreed, PetType, PetDOB
3.22 Write an SQL statement to display the name, breed, and type for all pets that are not
of type Cat, Dog, or Fish.
