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
Online Appendix E
Advanced SQL
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
Appendix E – Advanced SQL
APPENDIX OBJECTIVES
• To understand reasons for using the SQL ALTER statement
• To use the SQL ALTER statement
• To understand the need for the SQL MERGE statement
• To use the SQL MERGE statement
• To understand the need for additional type of SQL queries
• To use SQL outer join queries
• To use SQL correlated subqueries
• To use SQL queries on recursive relationships
• To understand the reasons for using SQL set operators
• To use SQL set operators
• To understand the reasons for using SQL views
• To use SQL statements to create and use SQL views
• To understand SQL/Persistent Stored Modules (SQL/PSM)
• To use SQL statements to create and use SQL user-defined functions
• Introduce the topic of importing Microsoft Excel 2016 data into a database table
• Introduce the topic of using Microsoft Access 2016 as an application development platform
APPENDIX ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the online DBC e08 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
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.
Appendix E – Advanced SQL
Figure 1 – Windows 10 Main Menu
Figure 2 – Windows 10 All Apps Menu
Appendix E – Advanced SQL
Figure 3 – Windows 10 Anniversary Update Main Menu with All Apps 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.
Appendix E – Advanced SQL
TEACHING SUGGESTIONS
• Logically the topics in this appendix should be taught immediately after Chapter 3 on
SQL.
• There are variety of topics in this appendix, and for the most part they can be
covered independently (e.g. one does not need to cover the SQL ALTER statement
in order to cover SQL views or SQL set operators, etc.).
• Some of the SQL features described in this appendix are not available in Microsoft
Access, and some others are not covered in this appendix. This appendix covers the
SQL ALTER TABLE statement, querying recursive relationships, and the Microsoft
Access equivalent to SQL views. Exercises, review questions, and case questions
relating to other topics will therefore not have Access solutions.
• The topics of importing Microsoft Excel data into a database and using Microsoft
Access as a development front-end to another DBMS are only mentioned briefly in
this appendix; they are given more complete treatment in Appendices A (SQL
Server), B (Oracle), and C (MySQL).
• 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 appendix. That database was the
basis of the Access Workbench Exercises in Chapters 1 and 2, and for the example
SQL queries in Chapter 3. If your students did the related assignments, they have a
WP database ready to use for the SQL work in Appendix E. If you want to
demonstrate the material in this appendix, see the IRC files supplied and use:
• Microsoft Access:
• DBC-e08-WP-SQL-Version-CH03-AppE.accdb
• SQL Server 2016:
• DBC-e08-MSSQL-WP-Create-Tables.sql
• DBC-e08-MSSQL-WP-Insert-Data.sql
• DBC-e08-MSSQL-WP-Create-AppE-Tables.sql
• DBC-e08-MSSQL-WP-Insert-Data-AppE.sql
• DBC-e08-MSSQL-WP-SQL-AppE-Text.sql
• Oracle Database XE:
• DBC-e08-ODB-WP-Create-Tables.sql
• DBC-e08-ODB-WP-Insert-Data.sql
• DBC-e08-ODB-WP-Create-AppE-Tables.sql
• DBC-e08-ODB-WP-Insert-Data-AppE.sql
Appendix E – Advanced SQL
• DBC-e08-ODB-WP-SQL-AppE-Text.sql
• MySQL 5.7:
• DBC-e08-MySQL-WP-Create-Tables.sql
• DBC-e08-MySQL-WP-Insert-Data.sql
• DBC-e08-MySQL-WP-Create-AppE-Tables.sql
• DBC-e08-MySQL-WP-Insert-Data-AppE.sql
• DBC-e08-MySQL-WP-SQL-AppE-Text.sql
• See the Instructor's Manuals for Appendices A, B, and C for information on how to
demonstrate the SQL commands and queries.
• Note that the SQL MERGE statement is not available in MySQL.
• 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
E.1 Show an SQL statement to add an integer column C1 to the table T2. Assume that C1 is
NULL.
E.2 Extend your answer to review question E.1 to add C1 when C1 is to be NOT NULL.
This requires us to first add the column as NULL, update the values of the column so they are no
longer NULL, then modify the column to NOT NULL. The first two steps are the same for all
systems (SQL Server, MySQL, Access, and Oracle):
Appendix E – Advanced SQL
For SQL Server and Access:
E.3 Show an SQL statement to drop the column C1 from table T2.
E.4 Assume that tables T1 and T2 have a 1:1 relationship. Assume that T2 has the foreign
key. Show the SQL statements necessary to move the foreign key to T1. Make up your
own names for the primary and foreign keys.
Assume we have the following two tables: T1 (A, B) and T2 (C, D, E) where E is a foreign key
referencing T1(A) and is also UNIQUE. Assume all columns are integers. First we need to
remove the E column from T2, which first requires us to remove all constraints on that column:
Next we need to add the new column, along with its UNIQUE and FOREIGN KEY constraints, to
table T1:
E.5 Explain how the SQL MERGE statement works, and discuss an example different than
the one use in this book of how it could be applied to a database.
Appendix E – Advanced SQL
As an example, consider a hospital which has a central PATIENTS table, which will periodically be
updated with discharge and new patient information from various parts of the hospital. As a
E.6 Explain how the SQL outer joins work. What is the difference between a RIGHT JOIN and a
LEFT JOIN? Discuss an example different than the one use in this book of how outer joins
could be applied to a database.
Outer joins extend the results of a standard inner join by including rows from one of the tables
that do not have matching join column values in the other table. In the result, the columns from
E.7 Write a subquery, other than one in this appendix, that is not a correlated subquery.
The following query (again on the WP database) retrieves all the in-stock items that are in the
2015 catalog:
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 10 of 107
that way: It cannot be excised from the query and run on its own because it refers to a row
from the main query. Values from that main query row must be substituted into the subquery
before the subquery can be executed, meaning that we must conceptually evaluate the
subquery once for each row of the main query.
E.9 Write a correlated subquery other than one in this appendix.
E.10 Explain how the query in your answer to review question E.7 differs from the query in
your answer to review question E.9.
There are two syntactic differences: First, SKU IN has been replaced with EXISTS. Second, notice
the join comparison is now needed in the subquery to replace SKU IN, which is what really
E.11 Explain what is wrong with the correlated subquery SQL-Query-AppE-06 on page E-26.
That query contains a contradiction in its WHERE clauses. Since a contradiction is never true, no
results will be returned because no rows will match the WHERE clauses. In particular, the
E.12 Explain the meaning of the SQL EXISTS comparison operator.
E.13 Explain how the words any and all pertain to the SQL EXISTS and NOT EXISTS
comparison operators.
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 11 of 107
EXISTS will be true if any rows exist in the result of the subquery: i.e., if any row examined by
the subquery matches the conditions of the WHERE clause. NOT EXISTS will be true if all rows
examined by the subquery fail to match that condition.
E.14 What is a query on a recursive relationship? Discuss an example different than the ones
used in this book of how such a query could be applied to a database.
A query on a recursive relationship is a query that uses the relationship between one row of a
table and another row of the same table. An example is a college or university in which every
E.15 What are SQL set operators? What are Venn diagrams?
SQL set operators (UNION, INTERSECT, and EXCEPT) are binary operations that work on the
results of others SQL queries under certain circumstances. Since every table in a relational
Appendix E – Advanced SQL
E.16 What does an SQL UNION statement do? Given two tables T1 and T2, what must be
true about these two tables if the SQL UNION operator is applied to them? What will be the
result of using the UNION operator with T1 and T2?
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 13 of 107
The SQL UNION operator returns a new table that contains every row from each of its input
tables, with duplicates eliminated. In a query such as T1 UNION T2, where T1 and T2 are SQL
queries, it includes in its result every row that is in either T1 or T2 or both (if it is in both, it only
appears once in the result). The SQL UNION operator will only work if T1 and T2 have the same
number of columns and the corresponding columns (determined by position) have the same or
compatible data types.
E.17 What is an SQL view? What purposes do views serve?
An SQL view is a virtual table that is constructed from other tables or views. A view has no data
of its own, but uses data stored in those tables or views.
E.18 What SQL statements are used to create SQL views?
E.19 What is the limitation on SELECT statements used in SQL views?
E.20 How are views handled in Microsoft Access?
Appendix E – Advanced SQL
See the discussion for creating and populating these tables in Microsoft Access, SQL
Server, and MySQL in the Review Questions for Chapter 3 (see Review Questions 3.7,
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 15 of 107
• MySQL 5.7:
• DBC-e08-MySQL-Pet-Database-Create-Tables.sql
• DBC-e08-MySQL-Pet-Database-Insert-Data.sql
• DBC-e08-MySQL-Pet-Database-SQL-AppE.sql
Assumption: The breed designator "Std. Poodle" has not been changed to "Poodle, Std." as
requested in Review Question 3.44. If it has been, results will vary slightly from those below.
E.21 Code an SQL statement to create a view named OwnerPhoneView that shows
OwnerLastName, OwnerFirstName, and OwnerPhone.
For Microsoft Access:
E.22 Code an SQL statement that displays the data in OwnerPhoneView, sorted
alphabetically by OwnerLastName.
For Microsoft Access:
Appendix E – Advanced SQL
E.23 Code an SQL statement to create a view named DogBreedView that shows PetID,
PetName, PetBreed, and PetDOB for Dogs.
For Microsoft Access:
E.24 Code an SQL statement that displays the data in DogBreedView, sorted alphabetically
by PetName.
For Microsoft Access:
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 17 of 107
E.25 Code an SQL statement to create a view named CatBreedView that shows PetID,
PetName, PetBreed, and PetDOB for Cats.
For Access:
E.26 Code an SQL statement that displays the data in CatBreedView, sorted alphabetically by
PetName.
For Microsoft Access:
Appendix E – Advanced SQL
E.27 Code an SQL statement to create a view named PetOwnerView that shows PetID,
PetName, PetType, OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, and
OwnerEmail.
For Microsoft Access:
E.28 Code an SQL statement that displays the data in PetOwnerView, sorted alphabetically
by OwnerLastName and PetName.
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 19 of 107
SELECT *
FROM PetOwnerView
ORDER BY OwnerLastName, PetName;
E.29 Code an SQL statement to create a view named OwnerPetView that shows OwnerID,
OwnerLastName, OwnerFirstName, PetID, PetName, PetType, PetBreed, and PetDOB.
For Microsoft Access:
E.30 Code an SQL statement that displays the data in OwnerPetView, sorted alphabetically
by OwnerLastName and PetName.
Appendix E – Advanced SQL
For Microsoft Access:
For SQL Server, Oracle Database and MySQL:
E.31 Code an SQL statement to create a view named PetCountView that shows each type
(that is, dog or cat) and the number of each type (that is, how many dogs and how many
cats) in the database.
For Microsoft Access:
Create and save the following Access query as viewPetCount.
E.32 Code an SQL statement that displays the data in PetCountView, sorted alphabetically by
PetType.
