Database Storage & Design Appendix E Database Concepts Edition David Kroenke David Auer Scott Vandenberg Robert Yoder Instructors

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

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

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

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

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

• 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

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

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.