Database systems

ExtensionAdvancedSQL.PDF

Home >Computer Science homework help >Database systems

Database Concepts 9th Edition

David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder

Online Extension B

Advanced SQL

Z05_KROE8149_09_SE_EXTB.indd 1 04/07/19 8:23 PM

Vice President of Courseware Portfolio Management: Andrew Gilfillan Executive Portfolio Manager: Samantha Lewis Team Lead, Content Production: Laura Burgess Content Producer: Faraz Sharique Ali Portfolio Management Assistant: Bridget Daly Director of Product Marketing: Brad Parkins Director of Field Marketing: Jonathan Cottrell Product Marketing Manager: Heather Taylor Field Marketing Manager: Bob Nisbet Product Marketing Assistant: Liz Bennett Field Marketing Assistant: Derrica Moser Senior Operations Specialist: Diane Peirano

Senior Art Director: Mary Seiner Interior and Cover Design: Pearson CSC Cover Art: Artwork by Donna Auer Senior Product Model Manager: Eric Hakanson Manager, Digital Studio: Heather Darby Course Producer, MyLab MIS: Jaimie Noy Digital Studio Producer: Tanika Henderson Full-Service Project Manager: Gowthaman Sadhanandham Full Service Vendor: Integra Software Service Pvt. Ltd. Manufacturing Buyer: LSC Communications, Maura Zaldivar-Garcia Text Printer/Bindery: LSC Communications Cover Printer: Phoenix Color

Credits and acknowledgments borrowed from other sources and reproduced, with permission, in this textbook appear on the appropriate page within text.

Microsoft and/or its respective suppliers make no representations about the suitability of the information contained in the documents and related graphics published as part of the services for any purpose. All such documents and related graphics are provided “as is” without warranty of any kind. Microsoft and/or its respective suppliers hereby disclaim all warranties and conditions with regard to this information, including all warranties and conditions of merchantability, whether express, implied or statutory, fitness for a particular purpose, title and non-infringement. In no event shall Microsoft and/or its respective suppliers be liable for any special, indirect or consequential damages or any damages whatsoever resulting from loss of use, data or profits, whether in an action of contract, negligence or other tortious action, arising out of or in connection with the use or performance of information available from the services.

The documents and related graphics contained herein could include technical inaccuracies or typographical errors. Changes are periodically added to the information herein. Microsoft and/or its respective suppliers may make improvements and/or changes in the product(s) and/or the program(s) described herein at any time. Partial screen shots may be viewed in full within the software version specified.

Microsoft® Windows®, and Microsoft Office® are registered trademarks of the Microsoft Corporation in the U.S.A. and other countries. This book is not sponsored or endorsed by or affiliated with the Microsoft Corporation.

MySQL®, the MySQL Command Line Client®, the MySQL Workbench®, and the MySQL Connector/ODBC® are registered trademarks of Sun Microsystems, Inc./Oracle Corporation. Screenshots and icons reprinted with permission of Oracle Corporation. This book is not sponsored or endorsed by or affiliated with Oracle Corporation.

Oracle Database XE 2016 by Oracle Corporation. Reprinted with permission.

PHP is copyright The PHP Group 1999–2012, and is used under the terms of the PHP Public License v3.01 available at http://www.php.net/ license/3_01.txt. This book is not sponsored or endorsed by or affiliated with The PHP Group.

ArangoDB is a copyright of ArangoDB GmbH.

Copyright © 2020, 2017, 2015 by Pearson Education, Inc. 221 River Street, Hoboken, NJ 07030. All rights reserved. Manufactured in the United States of America. This publication is protected by Copyright, and permission should be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise.

For information regarding permissions, request forms and the appropriate contacts within the Pearson Education Global Rights & Permissions Department, please visit www.pearsoned.com/permissions. Ackn-owledgments of third-party content appear on the appropriate page within the text, which constitutes an extension of this copyright page. Unless otherwise indicated herein, any third-party trademarks that may appear in this work are the property of their respective owners and any references to third-party trademarks, logos or other trade dress are for demonstrative or de- scriptive purposes only. Such references are not intended to imply any sponsorship, endorsement, authorization, or promotion of Pearson’s products by the owners of such marks, or any relationship between the owner and Pearson Education, Inc. or its affiliates, authors, licensees or distributors.

Library of Congress Cataloging-in-Publication Data

Names: Kroenke, David M., author. | Auer, David J., author. | Vandenberg, Scott L., author. | Yoder, Robert C., author.

Title: Database concepts/David M. Kroenke, David J. Auer, Western Washington University, Scott L. Vandenberg, Siena College, Robert C. Yoder, Siena College.

Description: Ninth edition. | New York, NY : Pearson, [2020] | Includes index. Identifiers: LCCN 2018052988 | ISBN 9780135188149 | ISBN 0135188148 Subjects: LCSH: Database management. | Relational databases. Classification: LCC QA76.9.D3 K736 2020 | DDC 005.74–dc23 LC record available at https://lccn.loc.gov/2018052988

1 19

ISBN 10: 0-13-518814-8 ISBN 13: 978-0-13-518814-9

Z05_KROE8149_09_SE_EXTB.indd 2 04/07/19 8:23 PM

B-1

EX TE

N S IO

B Advanced SQL ■ 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 types 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

■ To introduce the topic of importing Microsoft Excel 2019 data into a database table

■ To introduce the topic of using Microsoft Access 2019 as an application development platform

CHAPTER OBJECTIVES

WHAT IS THE PURPOSE OF THIS EXTENSION? In Chapter 3, we discussed SQL in depth. We discussed two basic categories of SQL state- ments: data definition language (DDL) statements, which are used for creating tables, rela- tionships, and other structures, and data manipulation language (DML) statements, which are used for querying and modifying data.

In this extension, which should be studied immediately after Chapter 3, we:

• Describe and illustrate additional uses of the SQL ALTER statement. • Describe and illustrate the SQL MERGE statement. • Describe and illustrate SQL outer join queries. • Describe and illustrate SQL correlated subqueries. • Describe and illustrate SQL queries on recursive relationships. • Describe and illustrate SQL set operators. • Describe and illustrate SQL views, which extend the DML capabilities of SQL. • Describe and illustrate SQL Persistent Stored Modules (SQL/PSM) and create user-

defined functions. • Describe importing Microsoft Excel worksheet data into a database table. • Describe using Microsoft Access as a development platform.

EXTENDING THE WP DATABASE In Chapter 3, we created and used a database for Wedgewood Pacific named WP. We will continue to use that database as the basis for our discussion in this extension. Our first step is to extend the WP database by adding some additional tables. WP manufactures and sell consumer drones, and currently offers three models: The Alpha III, the Bravo II, and the Delta IV. While sold through various retailers, these drone models are also available directly from WP through catalog and Web site sales.

Z05_KROE8149_09_SE_EXTB.indd 1 04/07/19 8:23 PM

B-2 Online Extension B Advanced SQL

FIGURE B-1

WP Database Column Characteristics for the PRODUCTION_ITEM Table

FIGURE B-2

WP Database Column Characteristics for the CATALOG_SKU_20## Tables

WP identifies each drone model with a stock keeping unit (SKU), which is a unique identifier for each product item that WP sells. WP maintains product data (for both cur- rent and past products) in a table named PRODUCTION_ITEM, and it maintains data that identifies what products are or were available in its annual catalogs in tables named CATALOG_SKU_20##, where ## indicates the year. These tables also record when the SKU was added to the Web site, and some products may be introduced in a calendar year after the catalog itself is published. Thus, such a product will be available on the Web site but not via the catalog. The column characteristics for the PRODUCTION_ITEM table are shown in Figure B-1, and column characteristics for the CATALOG_SKU_20## tables are shown in Figure B-2. Data for the PRODUCTION_ITEM Table is shown in Figure B-3, for the CATALOG_SKU_2017 table in Figure B-4, and for the CATALOG_SKU_2018 table in Figure B-5.

In schema format, the tables are:

PRODUCTION_ITEM (SKU, SKU_Description, ProductionStartDate, ProductionEndDate, QuantityOnHand, QuantityInProduction)

CATALOG_SKU_2017(CatalogID, SKU, CatalogDescription, CatalogPage, DateOnWebsite)

CATALOG_SKU_2018(CatalogID, SKU, CatalogDescription, CatalogPage, DateOnWebsite)

Z05_KROE8149_09_SE_EXTB.indd 2 04/07/19 8:23 PM

Online Extension B Advanced SQL B-3

FIGURE B-3

WP Database Data for the PRODUCTION_ITEM Table

FIGURE B-4

WP Database Data for the CATALOG_SKU_2017 Table

FIGURE B-5

WP Database Data for the CATALOG_SKU_2018 Table

Z05_KROE8149_09_SE_EXTB.indd 3 04/07/19 8:23 PM

B-4 Online Extension B Advanced SQL

The referential integrity constraints are:

SKU in CATALOG_SKU_2017 must exist in SKU in PRODUCTION_ITEM SKU in CATALOG_SKU_2018 must exist in SKU in PRODUCTION_ITEM

Note that while these tables have relationships between themselves, they do not currently have relationships with any of the existing DEPARTMENT, EMPLOYEE, PROJECT, or ASSIGNMENT tables in the WP database.

Using the SQL CREATE TABLE and SQL INSERT statements that we discussed in Chapter 3, we can easily add these tables to the WP database and populate them with data. The SQL CREATE TABLE statements needed to create the tables are shown in Figure B-6, and the SQL INSERT statements needed to populate the tables are shown in Figure B-7. However, note that we are intentionally creating the tables without the foreign key con- straint between CATALOG_SKU_2018 and PRODUCTION_ITEM.

Note that these statements illustrate the use of the MySQL keyword AUTO_INCRE- MENT to create surrogate keys in MySQL 8.0. Surrogate keys must be handled differently in Microsoft SQL Server and Oracle Database XE. For a discussion of how to create surro- gate keys in MySQL 8.0, see online Extension A, “Working with MySQL.” For a discussion of how to create surrogate keys in Microsoft SQL Server and Oracle Database XE, see our book Database Processing: Fundamentals, Design, and Implementation.1

1Specifically, see online Chapter 10A, “Managing Databases with SQL Server 2017” and online Chapter 10B, “Managing Databases with Oracle Database” in David M. Kroenke, David J. Auer, Scott L. Vanden- berg, and Robert C. Yoder, Database Processing: Fundamentals, Design, and Implementation, 15th ed. (Up- per Saddle River, NJ: Pearson, 2019).

FIGURE B-6

SQL CREATE TABLE Statements to Modify the WP Database

CREATE

USE wp;

TABLE PRODUCTION_ITEM( SKU Int NOT NULL, SKU_Description Char(35) NOT NULL, ProductionStartDate Date NULL, ProductionEndDate Date NULL, QuantityOnHand Int NULL, QuantityInProduction Int NULL, CONSTRAINT PRODUCTION_ITEM_PK PRIMARYKEY(SKU) );

CREATE TABLE CATALOG_SKU_2017( CatalogID Int NOT NULL AUTO_INCREMENT, SKU Int NOT NULL, CatalogDescription Varchar(255) NOT NULL, CatalogPage Int NULL, DateOnWebSite Date NULL, CONSTRAINT CATALOG_SKU_2017_PK PRIMARY KEY(CatalogID), CONSTRAINT CAT17_PROD_ITEM_FK FOREIGN KEY(SKU)

REFERENCES PRODUCTION_ITEM(SKU) ON UPDATENO ACTION ON DELETE NO ACTION

);

ALTER TABLE CATALOG_SKU_2017 AUTO_INCREMENT = 20170001;

ALTER TABLE CATALOG_SKU_2018 AUTO_INCREMENT = 20180001;

CREATE TABLE CATALOG_SKU_2018( CatalogID Int NOT NULL AUTO_INCREMENT, SKU Int NOT NULL, CatalogDescription Varchar(255) NOT NULL, CatalogPage Int NULL, DateOnWebSite Date NULL, CONSTRAINT CATALOG_SKU_2018_PK PRIMARY KEY(CatalogID) );

Z05_KROE8149_09_SE_EXTB.indd 4 04/07/19 8:23 PM

Online Extension B Advanced SQL B-5

FIGURE B-7

SQL INSERT Statements to Populate the New WP Database Tables

/***** PRODUCTION_ITEM DATA *************************************************/

USE wp;

/***** CATALOG_SKU_2017 *****************************************************/

/* Note use of NULL to generate next AUTO_INCREMENT value in MySQL */

/***** CATALOG_SKU_2018 *****************************************************/

INSERT INTO PRODUCTION_ITEM VALUES( 170102001, 'Alpha II, Black', '2016-10-15', '2017-11-30', 0, 0);

170102005, 'Alpha II, White', '2016-11-15', '2017-10-31', 0, 0);

170201001, 'Bravo I, Black', '2016-12-15', '2017-11-30', 0, 0);

170201005, 'Bravo I, White', '2016-12-15', '2017-11-30', 0, 0);

170303001, 'Delta III, Black', '2017-01-15', '2017-01-02', 5, 0);

170303005, 'Delta III, White', '2017-01-15', '2017-01-02', 15, 0);

180103001, 'Alpha III, Black', '2017-11-15', NULL, 100, 100);

180103005, 'Alpha III, White', '2017-11-15', NULL, 100, 0);

180202001, 'Bravo II, Black', '2017-12-15', NULL, 200, 100);

180202005, 'Bravo II, White', '2017-12-15', NULL, 150, 50);

180304001, 'Delta IV, Black', '2018-01-15', NULL, 300, 200);

180304005, 'Delta IV, White', '2018-01-15', NULL, 200, 100);

INSERT INTO PRODUCTION_ITEM VALUES(

INSERT INTO CATALOG_SKU_2017 VALUES( NULL,170102001, 'Our low price Alpha II model in black.', 10, '2017-01-01');

NULL,170102005, 'Our low price Alpha II model in white.', 12, '2017-01-01');

NULL,170201001, 'Our new Bravo I model in black.', 18, '2017-01-01');

NULL,170201005, 'Our new Bravo I model in white.', 20, '2017-01-01');

NULL,170303001, 'Our high performance Delta III model in black.', 24, '2017-01-01');

NULL,170303005, 'Our high performance Delta III model in white.', 26, '2017-01-01');

NULL,180103001, 'New, updated Alpha III model in black.', NULL, '2017-12-01');

NULL,180103005, 'New, updated Alpha III model in white.', NULL, '2017-12-01');

INSERT INTO CATALOG_SKU_2017 VALUES(

INSERT INTO CATALOG_SKU_2018 VALUES( NULL, 180103001, 'Our low price Alpha III model in black.', 10, '2018-01-01'); INSERT INTO VALUES NULL, 180103005, 'Our low price Alpha III model in white.', 11, '2018-01-01'); INSERT INTO CATALOG_SKU_2018

CATALOG_SKU_2018

VALUES(

(

NULL, 180202001, 'Our new Bravo II model in black.', 18, '2018-01-01'); INSERT INTO CATALOG_SKU_2018 VALUES( NULL, 180202005, 'Our new Bravo II model in white.', 17, '2018-01-01'); INSERT INTO CATALOG_SKU_2018 VALUES( NULL, 180304001, 'Our high performance Delta IV model in black.', 22, '2018-01-01'); INSERT INTO CATALOG_SKU_2018 VALUES( NULL, 180304005, 'Our high performance Delta IV model in white.', 23, '2018-01-01');

Z05_KROE8149_09_SE_EXTB.indd 5 04/07/19 8:23 PM

B-6 Online Extension B Advanced SQL

A MySQL Workbench database design for the WP database is shown in Figure B-8. Note that there is no relationship between PRODUCTION_ITEM and CATALOG_ SKU_2018. Also note that the relationship between PRODUCTION_ITEM and CATA- LOG_SKU_2017 is 1:N because, although it does not occur in our current data, an SKU can be used on more than more catalog page (for example, as part of product bundle). The data as it actually exists (after the SQL INSERT statements are run) in the PRODUC- TION_ITEM, CATALOG_SKU_2017, and CATALOG_SKU_2018 tables is shown in Figure B-9. Our revisions to the WP database are complete, and we will make use of these new tables in our discussion of advanced SQL features.

PK /FK ProjectID PK /FK EmployeeNumber

FIGURE B-8

The Revised Database Design for the WP Database

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

FIGURE B-9

Data in the New WP Database Tables

(a) Data in the New PRODUCTION_ITEM Table

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 6 04/07/19 8:23 PM

Online Extension B Advanced SQL B-7

In Chapter 3, we introduced the SQL ALTER TABLE statement (see page 202), which is used to modify the structure of a table once it has been created in a database. It can be used to add, modify, and drop columns and constraints, and, very useful for us, to modify the AUTO_IN- CREMENT starting value in MySQL 8.0. Here we will illustrate common uses of this statement.

Modifying the MySQL AUTO_INCREMENT Starting Value As discussed in online Extension A, “Working with MySQL,” the MySQL AUTO_ INCREMENT function that is used to create surrogate key values will initially use a starting value of 1 and an increment of 1. While the increment value cannot be changed, the starting value can be modified by using an ALTER TABLE statement after the table has been created.

Indeed, we have already used such statements in Figure B-6 to modify the starting val- ues for CatalogID in the CATALOG_SKU_2017 and CATALOG_SKU_2018 tables. For the CATALOG_SKU_2017 table, we used the SQL Statement:

/* *** EXAMPLE CODE – THIS STATEMENT HAS ALREADY BEEN RUN *** */

/* *** SQL-ALTER-TABLE-ExtB-01 *** */

ALTER TABLE CATALOG_SKU_2017 AUTO_INCREMENT = 20170001;

This statement sets the first value of the CatalogID to 20170001. For the CATALOG_ SKU_2018 table, we used the SQL Statement:

/* *** EXAMPLE CODE – THIS STATEMENT HAS ALREADY BEEN RUN *** */

/* *** SQL-ALTER-TABLE-ExtB-02 *** */

ALTER TABLE CATALOG_SKU_2018 AUTO_INCREMENT = 20180001;

This statement sets the first value of the CatalogID to 20180001.

USING THE SQL ALTER TABLE STATEMENT

(b) Data in the New CATALOG_SKU_2017 Table

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 7 04/07/19 8:23 PM

B-8 Online Extension B Advanced SQL

Both Microsoft SQL Server and Oracle Database allow you to set both the starting value and the increment value of the surrogate key values. Microsoft Access and MySQL are both limited by comparison: Microsoft Access only allows starting values of 1, and both Microsoft Access and MySQL only allow an increment value of 1.

BTW

Adding a Column to an Existing Table The SQL ALTER TABLE statement can be used to add a column to an existing table. We used this feature in Chapter 3’s section of “Working with Microsoft Access” (see pages 227–228), and here we will elaborate on how to do this. The basic syntax is:

/* *** EXAMPLE CODE – DO NOT RUN *** */

/* *** SQL-ALTER-TABLE-ExtB-03 *** */

ALTER TABLE {TableName}

ADD {ColumnName} {DataType} {OptionalColumnConstraints};

Note that the SQL COLUMN keyword is not used in an ADD {COLUMN} clause in the variants of SQL used in most relational DBMS products. MySQL, however, does allow the use of the SQL COLUMN keyword as an option but does not require it (see online Exten- sion A, “Working with MySQL”).

If we are adding a column that allows NULL values, then we can do it with that one statement. If, however, we are adding a column with a NOT NULL column constraint, then we must use the following steps:

1. Add the column as column that allows NULL values. 2. Update all table rows with data values in the new column. 3. Modify the column to set the NOT NULL constraint.

For example, suppose that new legislation is passed requiring that any new drone being offered for sale must be approved by the FAA before going into produc- tion. The FAA checks for safety and control compliance with FAA regulations and specifications.

This means that WP will need to add a column to the PRODUCTION_ITEM table to store the date that FAA approval was received for each drone model. Further, since this approval must be received before the drone goes into production, it must be on hand when the model is added to the table, and is therefore a NOT NULL data constraint. We will as- sume that WP has such approvals and approval dates for all drone models currently in the table and add the column as a NOT NULL column.

Adding a NOT NULL Column to the PRODUCTION_ITEM Table

1. Add a new column named ApprovalDate as a NULL column to the PRODUCTION_ ITEM table.

/* *** SQL-ALTER-TABLE-ExtB-04 *** */

ALTER TABLE PRODUCTION_ITEM

ADD ApprovalDate DATE NULL;

Z05_KROE8149_09_SE_EXTB.indd 8 04/07/19 8:23 PM

Online Extension B Advanced SQL B-9

2. Update the current rows in the PRODUCTION_ITEM table with the FAA approval dates for the existing drone models. The ApprovalDate data we will use is shown in the UPDATE statements:

/* *** SQL-UPDATE-ExtB-01 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = '2016-08-31'

WHERE SKU = 170102001;

/* *** SQL-UPDATE-ExtB-02 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = '2016-08-31'

WHERE SKU = 170102005;

/* *** SQL-UPDATE-ExtB-03 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = '2016-09-30'

WHERE SKU = 170201001;

/* *** SQL-UPDATE-ExtB-04 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = '2016-09-30'

WHERE SKU = 170201005;

/* *** SQL-UPDATE-ExtB-05 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = '2016-11-30'

WHERE SKU = 170303001;

/* *** SQL-UPDATE-ExtB-06 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = '2016-11-30'

WHERE SKU = 170303005;

/* *** SQL-UPDATE-ExtB-07 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = '2017-09-30'

WHERE SKU = 180103001;

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 9 04/07/19 8:23 PM

B-10 Online Extension B Advanced SQL

/* *** SQL-UPDATE-ExtB-08 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = '2017-09-30'

WHERE SKU = 180103005;

/* *** SQL-UPDATE-ExtB-09 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = '2017-09-30'

WHERE SKU = 180202001;

/* *** SQL-UPDATE-ExtB-10 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = '2017-09-30'

WHERE SKU = 180202005;

/* *** SQL-UPDATE-ExtB-11 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = '2017-11-30'

WHERE SKU = 180304001;

/* *** SQL-UPDATE-ExtB-12 *** */

UPDATE PRODUCTION_ITEM

SET ApprovalDate = '2017-11-30'

WHERE SKU = 180304005;

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

3. Modify the ApprovalDate column to set the NOT NULL column constraint.

/* *** SQL-ALTER-TABLE-ExtB-05 *** */

ALTER TABLE PRODUCTION_ITEM

MODIFY COLUMN ApprovalDate DATE NOT NULL;

Note that the SQL COLUMN keyword is used in a MODIFY COLUMN or an ALTER COLUMN clause—MySQL and Oracle Database use the MODIFY COLUMN clause, whereas Microsoft SQL Server uses the ALTER COLUMN clause. Looking at the column descriptions for the PRODUCTION_ITEM table, as shown in Figure B-10, we can see that the ApprovalDate column is now set to NOT NULL.

Adding a Table Constraint to an Existing Table The SQL ALTER TABLE statement can be used to add a table constraint to an existing table. The basic syntax is:

Z05_KROE8149_09_SE_EXTB.indd 10 04/07/19 8:23 PM

Online Extension B Advanced SQL B-11

/* *** EXAMPLE CODE – DO NOT RUN *** */

/* *** SQL-ALTER-TABLE-ExtB-06 *** */

ALTER TABLE {TableName}

ADD CONSTRAINT {ConstraintName} {TableConstraint};

Note that the SQL CONSTRAINT keyword is used in an ADD CONSTRAINT clause. A typical use here might be to add an SQL CHECK constraint to a table.2 In our WP

database example, the data value in ApprovalDate in each row in the table must be less than the data value in ProductionStartDate in the same row (FAA approval must be on hand before production of a specific drone model is started). We can do that by using the SQL ALTER TABLE statement:

/* *** SQL-ALTER-TABLE-ExtB-07 *** */

ALTER TABLE PRODUCTION_ITEM

ADD CONSTRAINT CheckProductionDate CHECK

(ApprovalDate < ProductionStartDate);

Note that the SQL CONSTRAINT keyword is used in an ALTER CONSTRAINT clause. Looking at the DDL tab of the table inspector for the PRODUCTION_ITEM table, as shown in Figure B-11, we can see that the CheckProductionDate constraint has been added.

2We are using MySQL Community Server 8.0.16.0 in this extension and in Extension A, “Working with MySQL.” As of MySQL 8.0.16.0, the CHECK constraint implemented in MySQL works correctly. In prior versions, the SQL statement implementing the CHECK constraint would be parsed correctly, but not actu- ally implemented. We highly recommend that you install or upgrade to MySQL 8.0.16.0 (or a higher ver- sion number if available) as soon as possible. See Extension A, “Working with MySQL” for a discussion of installing and upgrading MySQL. Note that this problem only existed in MySQL—Microsoft Access, Microsoft SQL Server, and Oracle Database XE all implement the CHECK constraint correctly.

Right-click the production_item table, then click Table Inspector, then click the Columns tab

ApprovalDate is “not nullable”— NOT NULL

The ALTER TABLE statement that set ApprovalDate to NOT NULL

FIGURE B-10

PRODUCTION_ITEM.ApprovalDate set to NOT NULL

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 11 04/07/19 8:23 PM

B-12 Online Extension B Advanced SQL

Adding a Referential Integrity Constraint to an Existing Table The SQL ALTER TABLE statement can be used to add a referential integrity constraint to an existing table. The basic syntax is:

/* *** EXAMPLE CODE – DO NOT RUN *** */

/* *** SQL-ALTER-TABLE-ExtB-08 *** */

ALTER TABLE {TableName}

ADD CONSTRAINT {ConstraintName} FOREIGN KEY({ColumnName})

REFERENCES {TableName}({PrimaryKeyColumn})

{Optional ON UPDATE clause}

{Optional ON DELETE clause};

Note that the SQL CONSTRAINT keyword is used in an ADD CONSTRAINT clause. In our WP database example, we still need to add a referential integrity constraint to

the CATALOG_SKU_2018 table to establish the relationship between that table and the PRODUCTION_ITEM table. We can do that by using the SQL ALTER TABLE statement:

/* *** SQL-ALTER-TABLE-ExtB-09 *** */

ALTER TABLE CATALOG_SKU_2018

ADD CONSTRAINT CAT18_PROD_ITEM_FK FOREIGN KEY(SKU)

REFERENCES PRODUCTION_ITEM(SKU)

ON UPDATE NO ACTION

ON DELETE NO ACTION;

Right-click the production_item table, then click Table Inspector, then click the DDL tab

The ALTER TABLE statement that created the CHECK constraint

The DDL tab shows the SQL CREATE TABLE statement needed to create the table as it currently exists—Note that this statement now contains the CHECK constraint

FIGURE B-11

The PRODUCTION_ITEM CheckProductionDate Constraint

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 12 04/07/19 8:23 PM

Online Extension B Advanced SQL B-13

Note that because SKU, the primary key of PRODUCTION_ITEM, is a surrogate key, it will never be changed, and therefore no update actions will be necessary. And because WP wants to retain data in the CATALOG_SKU_20## tables for historical records, we will not allow deletion of an SKU from the PRODUCTION_ITEM table.

After running the ALTER TABLE statement, the relationship between PRODUC- TION_ITEM and CATALOG_SKU_2018 can be seen in Figure B-12. Note that the added ApprovalDate column also appears in the PRODUCTION_ITEM table. Compare data- base diagram in Figure B-12 to the one in Figure B-8.

Removing a Table Column from an Existing Table The SQL ALTER TABLE statement can be used to remove a column from a table. Note that the SQL COLUMN keyword is used in the DROP COLUMN clause. The basic syntax is:

/* *** EXAMPLE CODE – DO NOT RUN *** */

/* *** SQL-ALTER-TABLE-ExtB-10 *** */

ALTER TABLE {TableName}

DROP COLUMN {ColumnName};

PK /FK ProjectID PK /FK EmployeeNumber

ApprovalDate column

FIGURE B-12

The Database Design for the WP Database with the Added Relationship

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 13 04/07/19 8:23 PM

B-14 Online Extension B Advanced SQL

Removing a Table Constraint from an Existing Table The SQL ALTER TABLE statement can be used to remove a table constraint. Note that the SQL CONSTRAINT keyword is used in the DROP CONSTRAINT clause. The basic syntax is:

/* *** EXAMPLE CODE – DO NOT RUN *** */

/* *** SQL-ALTER-TABLE-ExtB-11 *** */

ALTER TABLE {TableName}

DROP CONSTRAINT {ConstraintName};

Note that you cannot drop a table that is the parent table in a referential integrity constraint. In this case, you would use an ALTER TABLE DROP CONSTRAINT state- ment to drop the referential integrity constraint between tables, and then drop one or both of the tables themselves.

CREATE TABLE PRODUCTION_ITEM_DATA( SKU Int NOT NULL, SKU_Description Char(35) NULL, ProductionStartDate Date NULL, ProductionEndDate Date NULL, QuantityOnHand Int NULL, QuantityInProduction Int NULL, ApprovalDate Date NULL, CONSTRAINT PROD_ITEM_PK PRIMARY KEY(SKU) );

FIGURE B-13

SQL CREATE TABLE Statement to Create the PRODUCTION_ ITEM_DATA Table

In Chapter 3, we mentioned the SQL MERGE statement (see page 201), which combines the capabilities of the SQL INSERT and SQL UPDATE statement into one statement that can selectively insert or update data depending upon what data is already in a table.

Unfortunately, not all relational DBMS products currently support the MERGE state- ment. While Microsoft SQL Server and Oracle Database do support it, MySQL does not. Therefore, we will illustrate the SQL MERGE statement in Microsoft SQL Server rather than MySQL.

To use the MERGE statement, the new data is typically stored in a table of a simi- lar structure to the table being updated. For the WP database, we will construct a table named PRODUCTION_ITEM_DATA, which has the same basic structure as the PRODUCTION_ITEM table in Figure B-1 as modified with the ApprovalDate column. However, in the PRODUCTION_ITEM_DATA table every column except the primary key column SKU allows NULLs, since only some of these columns may be in use at any time. The SQL CREATE TABLE statement to create this table is shown in Figure B-13.

To understand why we would use this table, consider database administration per- missions that may be used at Wedgewood Pacific (database administration is discussed in Chapter 6). Many people may have permissions to add data to the PRODUCTION_ITEM_ DATA table, but only the actual DBA (database administrator) himself or herself may have permissions to update the PRODUCTION_ITEM table. It will be only the DBA who uses the SQL MERGE statement, and only after the data in the PRODUCTION_ITEM_DATA table has been checked and verified.

USING THE SQL MERGE STATEMENT

Z05_KROE8149_09_SE_EXTB.indd 14 04/07/19 8:23 PM

Online Extension B Advanced SQL B-15

For example, suppose that employees at Wedgewood Pacific have put the following data into the PRODUCTION_ITEM_DATA table:

1. SKU 180103001 (Alpha III, Black) ended its production run on October 31st, 2018. 2. SKU 180103005 (Alpha III, White) ended its production run on October 31st, 2018. 3. A new model, the Alpha IV, was put into production on November 15th, 2018. It is available

in both black (SKU 190104001) and white (SKU 190104005). There is no stock on hand, but there are 200 of each SKU in production. The FAA approval was received on October 15, 2018.

The SQL statements to populate the PRODUCTION_ITEM_DATA table are shown in Figure B-14(a) and the data in the PRODUCTION_ITEM_DATA table appears in SQL Server Management Studio in Figure B-14(b). Note that the rows for SKU 180103001 and SKU 180103005 hold update data, while the rows for SKU 190104001 and SKU 190104005 hold new data to be inserted as new rows into the PRODUCTION_DATA table. The SQL MERGE command tests a specified con- dition to see if the data in a row is to be used for an update operation or an insert operation and then acts accordingly.

In Microsoft SQL Server SQL syntax, the MERGE statement the WP DBA will use is (Oracle Database XE SQL will be slightly different):

INSERT INTO PRODUCTION_ITEM_DATA(SKU, ProductionEndDate) VALUES(180103001, '31-OCT-18');

INSERT INTO PRODUCTION_ITEM_DATA(SKU, ProductionEndDate) VALUES(180103005, '31-OCT-18');

INSERT INTO PRODUCTION_ITEM_DATA VALUES( 190104001, 'Alpha IV, Black', '15-NOV-18', NULL, 0, 200, '15-OCT-18');

INSERT INTO PRODUCTION_ITEM_DATA VALUES( 190104005, 'Alpha IV, White', '15-NOV-18', NULL, 0, 200, '15-OCT-18');

FIGURE B-14

Adding Data to the PRODUCTION_ITEM_ DATA Table

(a) SQL Statements to Add Data to the PRODUCTION_ITEM_DATA Table

The PRODUCTION_ITEM_DATA table—the dbo at the start of the table name is an SQL Server schema name (where schema is a permissions grouping, not a database), and stands for database owner

The data in the PRODUCTION_ITEM_DATA table

(b) The Data in the PRODUCTION_ITEM_DATA Table

SQL Server 2017, SQL Server Management Studio, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 15 04/07/19 8:23 PM

B-16 Online Extension B Advanced SQL

/* *** SQL-MERGE-ExtB-01 *** */

MERGE INTO PRODUCTION_ITEM AS PROI USING PRODUCTION_ITEM_DATA AS PROID

ON PROI.SKU = PROID.SKU

WHEN MATCHED THEN

UPDATE SET PROI.ProductionEndDate = PROID.ProductionEndDate

WHEN NOT MATCHED THEN

INSERT (SKU, SKU_Description,

ProductionStartDate, ProductionEndDate,

QuantityOnHand, QuantityInProduction,

ApprovalDate)

VALUES (PROID.SKU, PROID.SKU_Description,

PROID.ProductionStartDate, PROID.ProductionEndDate,

PROID.QuantityOnHand, PROID.QuantityInProduction,

PROID.ApprovalDate);

Note that we check to see if there are matching SKU values in the two tables. If there are matching SKUs, we run an UPDATE because the SKU already exists in the PRODUC- TION_ITEM table. In this case, the updated ProductionEndDate data will be added to the PRODUCTION_ITEM table. On the other hand, if there are unmatched SKUs, then we run an INSERT because the new SKU data must be added to the PRODUCTION_ITEM table. In this case, we use in a bulk SQL INSERT statement—a list of the columns that will be populated with the new data is matched with a list of the source of the data for each of those columns. Note that in a bulk SQL Insert statement, the VALUES clause (which includes the SQL VALUES keyword itself) can be replaced with a SELECT statement that determines the data to be inserted.

Here is the PRODUCTION_ITEM table in SQL Server Management Studio before the MERGE statement is run:

SQL Server 2017, SQL Server Management Studio, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 16 04/07/19 8:23 PM

Online Extension B Advanced SQL B-17

SQL Server 2017, SQL Server Management Studio, Microsoft Corporation.

Here is the PRODUCTION_ITEM table in SQL Server Management Studio after the MERGE statement is run:

Note that the SQL MERGE statement has done the updates and inserts correctly. To update the WP database in MySQL, which we need to do for data consistency in this extension, we need to run the following SQL UPDATE and INSERT statements:

/* *** SQL-UPDATE-ExtB-13 *** */

UPDATE PRODUCTION_ITEM

SET ProductionEndDate = '2018-10-31'

WHERE SKU = 180103001;

/* *** SQL-UPDATE-ExtB-14 *** */

UPDATE PRODUCTION_ITEM

SET ProductionEndDate = '2018-10-31'

WHERE SKU = 180103005;

/* *** SQL-INSERT-ExtB-01 *** */

INSERT INTO PRODUCTION_ITEM VALUES(190104001, 'Alpha IV, Black', '2018-11-15', NULL, 0, 200, '2018-10-15');

/* *** SQL-INSERT-ExtB-02 *** */

INSERT INTO PRODUCTION_ITEM VALUES(190104005, 'Alpha IV, White', '2018-11-15', NULL, 0, 200, '2018-10-15');

In MySQL 8.0, the PRODUCTION_ITEM table appears as follows after these SQL state- ments have been run:

Z05_KROE8149_09_SE_EXTB.indd 17 04/07/19 8:23 PM

B-18 Online Extension B Advanced SQL

EXTENSIONS TO SQL QUERY TECHNIQUES

In Chapter 3, we introduced, defined, and discussed SQL queries. We will now extend that material with some additional discussion of what can be done with an SQL query by ex- panding the coverage of outer joins and then introducing correlated subqueries and queries on recursive relationships.

Using Outer Join Queries In Chapter 3, we introduced, defined, and discussed SQL outer join queries (see pages 194–198), and you should review the material presented there before continuing with the examples presented here. In this section, we will extend the discussion in Chapter 3 by providing examples of SQL outer joins in the WP database.

In Chapter 3, we added a new record to the WP database PROJECT table:

/* *** EXAMPLE CODE – DO NOT RUN – USED IN CHAPTER 3*** */

/* *** SQL-INSERT-CH03-05 *** */

INSERT INTO PROJECT

(ProjectID, ProjectName, Department, MaxHours, StartDate)

VALUES(1700, '2019 Q4 Tax Preparation', 'Accounting',

175.00, '2019-12-10');

The added row has ProjectID 1700, and can be clearly seen by running SQL-Query-ExtB-01 (which duplicates SQL-Query-CH03-52):

/* *** SQL-Query-ExtB-01 *** */

SELECT * FROM PROJECT;

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 18 04/07/19 8:23 PM

Online Extension B Advanced SQL B-19

The result of SQL-Query-ExtB-01 is:

We then used the updated PROJECT in a JOIN ON query:

/* *** SQL-Query-CH03-53 *** */

SELECT ProjectName, FirstName, LastName, HoursWorked

FROM EMPLOYEE AS E JOIN ASSIGNMENT AS A

ON E.EmployeeNumber = A.EmployeeNumber

JOIN PROJECT AS P

ON A.ProjectID = P.ProjectID

ORDER BY P.ProjectID, A.EmployeeNumber;

The result of this query (previously shown in Figure 3-24) is shown in Figure B-15. The problem with this query is that there is no data about the new project with

ProjectID 1700! But now consider the following SQL query statement—which simpli- fies SQL Query-CH03-53 by retrieving employee numbers instead of employee names and thus eliminates the need to include the EMPLOYEE table in the query—and notice the use of the JOIN ON syntax—the SQL LEFT keyword is simply added to the SQL query:

/* *** SQL-Query-ExtB-02 *** */

SELECT ProjectName, EmployeeNumber, HoursWorked

FROM PROJECT AS P LEFT JOIN ASSIGNMENT AS A

ON P.ProjectID = A.ProjectID

ORDER BY P.ProjectID;

The purpose of this join is to append rows of PROJECT to those of ASSIGNMENT, as in SQL_Query-CH03-53, except that if any row in the table on the left side of the FROM clause (in this case, PROJECT) has no match, the data from that row are now included in the results anyway. The result of this query is shown in Figure B-16. Notice that the last row of this table shows the new 2019 Q4 Tax Preparation project, and SQL appends NULL val- ues for the EmployeeNumber and HoursWorked columns because there are no employees assigned to this new project.

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 19 04/07/19 8:23 PM

B-20 Online Extension B Advanced SQL

FIGURE B-15

The Results for SQL-Query-CH03-53

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

FIGURE B-16

The Results for SQL-Query-ExtB-02

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 20 04/07/19 8:23 PM

Online Extension B Advanced SQL B-21

Right outer joins operate similarly, except that the SQL RIGHT keyword is used, and rows in the table on the right-hand side of the FROM clause are included. For example, you could join all three tables together with the following modification of SQL-Query- CH03-53, which includes a right outer join:

/* *** SQL-Query-ExtB-03 *** */

SELECT ProjectName, FirstName, LastName, HoursWorked

FROM (PROJECT AS P JOIN ASSIGNMENT AS A

ON P.ProjectID = A.ProjectID)

RIGHT JOIN EMPLOYEE AS E

ON A.EmployeeNumber = E.EmployeeNumber

ORDER BY P.ProjectID, A.EmployeeNumber;

The result of this join, which now shows not only the employees assigned to projects but also those employees who are not assigned to any projects, is shown in Figure B-17.

FIGURE B-17

The Results for SQL-Query-ExtB-03

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 21 04/07/19 8:23 PM

B-22 Online Extension B Advanced SQL

Does Not Work with Microsoft Access ANSI-89 SQL

Even with the following syntax, which is what worked before in Microsoft Access, the error message Join expression not supported is returned when the query is run.

/* *** SQL-Query-ExtB-03-Access *** */

SELECT ProjectName, FirstName, LastName, HoursWorked

FROM (PROJECT AS P JOIN ASSIGNMENT AS A

ON P.ProjectID = A.ProjectID)

RIGHT JOIN EMPLOYEE AS E

ON A.EmployeeNumber = E.EmployeeNumber

ORDER BY P.ProjectID, A.EmployeeNumber;

Solution: Build an equivalent query or set of queries using the Microsoft Access Query by Example (QBE). Query by Example (QBE) is discussed in Chapter 3’s section of “Working with Microsoft Access.”

Using Correlated Subqueries In Chapter 3, we introduced and used SQL subqueries (see pages 184–186 and 193–194) and mentioned the SQL correlated subquery, which can do work that is not possible with join queries. A correlated subquery looks very much like the noncorrelated subqueries we discussed in Chapter 3, but, in actuality, correlated subqueries are very different. To under- stand the difference, consider the following noncorrelated subquery, which is like those in Chapter 3 and lists the FirstName and LastName of all WP employees assigned to work on the project with ProjectID 1000:

/* *** SQL-Query-ExtB-04 *** */

SELECT E.FirstName, E.lastName

FROM EMPLOYEE AS E

WHERE EmployeeNumber IN

(SELECT A.EmployeeNumber

FROM ASSIGNMENT AS A

WHERE A.ProjectID = 1000);

The DBMS can process such subqueries from the bottom up—that is, it can first find all of the values of EmployeeNumber in ASSIGNMENT that have the ProjectID of 1000 and then process the upper query using that set of values. There is no need to move back and forth between the two SELECT statements. The result of this query is:

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Searching for Multiple Rows with a Given Value Now, to introduce correlated subqueries, suppose that someone at Wedgewood Pacific proposes that the LastName column of EMPLOYEE be used as a candidate key which can be used in place of the primary key EmployeeNumber to uniquely identify each row in the table (see the dis-

Z05_KROE8149_09_SE_EXTB.indd 22 04/07/19 8:23 PM

Online Extension B Advanced SQL B-23

cussion of candidate and primary keys in Chapter 2 on pages 75–79). If you look at the data in the EMPLOYEE table shown here, you can easily see that there are two occur- rences in LastName for Jackson, Jones, and Smith. Therefore, LastName cannot be a candidate key, and we can determine this by simply looking at the dataset.

However, if the EMPLOYEE table had 10,000 or more rows, this would be difficult to determine. In that case, we need a query that examines the EMPLOYEE table and displays the Employee Number, LastName, and FirstName of any employees that share the same last name.

If we were asked to write a program to perform such a query, our logic would be as follows: Take the value of LastName from the first row in EMPLOYEE and examine all of the other rows in the table. If we find a row that has the same last name as the one in the first row, we know there are duplicates, so we print the EmployeeNumber, LastName, and FirstName of the first row. We continue searching for duplicate LastName values until we come to the end of the EMPLOYEE table.

Next, we take the value of LastName in the second row and compare it with all other rows in the EMPLOYEE table, printing out the EmployeeNumber, LastName, and First- Name of any duplicate names. We proceed in this way until all rows of EMPLOYEE have been examined.

A Correlated Subquery That Finds Rows with the Same Value The following correlated subquery performs the action just described:

/* *** SQL-Query-ExtB-05 *** */

SELECT E1.EmployeeNumber, E1.FirstName, E1.LastName

FROM EMPLOYEE AS E1

WHERE E1.LastName IN

(SELECT E2.LastName

FROM EMPLOYEE AS E2

WHERE E1.LastName = E2.LastName

AND E1.EmployeeNumber <> E2.EmployeeNumber);

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 23 04/07/19 8:23 PM

B-24 Online Extension B Advanced SQL

The result of this query for the data in the EMPLOYEE table is:

Looking at these results, it is easy to see the nonunique, duplicated LastName data that prevents LastName from being used as an alternate key. This subquery, which is a correlated subquery, looks deceptively similar to a regular, noncorrelated subquery. To the surprise of many students, this subquery and the earlier one are drastically different. Their similarity is only superficial.

Before learning why, first notice the notation in the correlated subquery. The EM- PLOYEE table is used in both the upper and the lower SELECT statements. In the upper statement, it is given the alias E1; in the lower SELECT statement, it is given the alias E2. In essence, when we use this notation, it is as if we have made two copies of the EMPLOYEE table. One copy is called E1, and the second copy is called E2. Therefore, in the last two lines of the correlated subquery, values in the E1 copy of EMPLOYEE are compared with values in the E2 copy.

Now consider what makes this subquery so different. Unlike with a regular, noncorrelated subquery, the DBMS cannot run the bottom SELECT by itself, obtain a set of LastNames, and then use that set to execute the upper query. The reason for this appears in the last two lines of the query:

WHERE E1.LastName = E2.LastName

AND E1.EmployeeNumber <> E2.EmployeeNumber);

In these expressions, E1.LastName (from the top SELECT statement) is being com- pared with E2.LastName (from the bottom SELECT statement). The same is true for E1.EmployeeNumber and E2.EmployeeNumber. Because of this fact, the DBMS cannot process the subquery portion independently of the upper SELECT.

Instead, the DBMS must process this statement as a subquery that is nested within the main query. The logic is as follows: Take the first row from E1. Using that row, evaluate the second query. To do that, for each row in E2, compare E1.LastName with E2.LastName and E1.EmployeeNumber with E2.EmployeeNumber. If the last names are equal and the values of EmployeeNumber are not equal, return the value of E2.LastName to the upper query. Do this for every row in E2.

Once all of the rows in E2 have been evaluated for the first row in E1, move to the sec- ond row in E1 and evaluate it against all the rows in E2. Continue in this way until all rows of E1 have been compared with all of the rows of E2.

If this is not clear to you, write out two copies of the EMPLOYEE data on a piece of scratch paper. Label one of them E1 and the second E2, and then work through the logic as described. From this, you will see that correlated subqueries always require nested processing.

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 24 04/07/19 8:23 PM

Online Extension B Advanced SQL B-25

A Common Trap By the way, do not fall into the following common trap:

/* *** SQL-Query-ExtB-06 *** */

SELECT E1.EmployeeNumber, E1.FirstName, E1.LastName

FROM EMPLOYEE AS E1

WHERE E1.EmployeeNumber IN

(SELECT E2.EmployeeNumber

FROM EMPLOYEE AS E2

WHERE E1.LastName = E2.LastName

AND E1.EmployeeNumber <> E2.EmployeeNumber);

The logic here seems correct, but it is not. Compare SQL-Query-ExtB-06 to SQL- Query-ExtB-05, and note the differences between the two SQL statements. The result of SQL-Query-ExtB-06 when run on the WP data is an empty set:

In fact, no row will ever be displayed by this query, regardless of the underlying data (see if you can figure out why this is so before continuing to the next paragraph).

The bottom query will indeed find all rows that have the same title and different Em- ployeeNumbers. If one is found, it will produce the E2.EmployeeNumber of that row. But that value will then be compared with E1.EmployeeNumber. These two values will always be different because of the condition

E1.EmployeeNumber <> E2.EmployeeNumber

No rows are returned because the values of the two unequal EmployeeNumbers are used in the IN clause instead of the values of the two equal LastNames.

SQL Correlated Subqueries Using the EXISTS and NOT EXISTS Comparison Operators In Chapter 3, we discussed a set of SQL comparison operators, and these are summarized in Figure 3-15. To this set we will now add the SQL EXISTS comparison operator and the SQL NOT EXISTS comparison operator, as shown in Figure B-18. When we use the EXIST or NOT EXISTS operator in a query, we are creating another form of correlated subquery.

These operators simply test whether there are any values returned by the subquery, which indicates there are values meeting the conditions of the subquery. If one or more val- ues are returned, then values from the subquery are used to run the top-level query. If there are no values returned, the top-level query produces an empty set as the result.

FIGURE B-18

SQL EXISTS and NOT EXISTS Comparison Operators

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 25 04/07/19 8:23 PM

B-26 Online Extension B Advanced SQL

For example, we can rewrite the SQL-Query-ExtB-05 correlated subquery using the SQL EXISTS keyword as follows:

/* *** SQL-Query-ExtB-07 *** */

SELECT E1.EmployeeNumber, E1.FirstName, E1.LastName

FROM EMPLOYEE AS E1

WHERE EXISTS

(SELECT E2.LastName

FROM EMPLOYEE AS E2

WHERE E1.LastName = E2.LastName

AND E1.EmployeeNumber <> E2.EmployeeNumber);

Because using EXISTS creates a form of a correlated subquery, the processing of the SELECT statements is nested. The first row of E1 is input to the subquery. If the subquery finds any row in E2 for which the last names are the same and the employee numbers are different, then the EXISTS is true (returns a nonempty set of values) and the FirstName, LastName, and EmployeeNumber for the first row are selected. Next, the second row of E1 is input to the subquery, the SELECT is processed, and the EXISTS is evaluated. If true, the FirstName, LastName, and EmployeeNumber of the second row are selected. This process is repeated for all of the rows in E1.

The results of SQL-Query-ExtB-07 are identical to the previous results from SQL- Query-ExtB-05:

The SQL EXISTS operator will be true (will return a nonempty set of values) if any row in the subquery meets the condition. The SQL NOT EXISTS operator will be true (will return an empty set) only if all rows in the subquery fail to meet the condition. Consequently, the double use of NOT EXISTS can be used to find rows that do not not match a condition. And, yes, the word not is supposed to be there twice—this is a double negative. Because of the logic of a double negative, if a row does not not match any row, then it matches every row!

This leads to some interesting uses of NOT EXISTS, but that discussion is beyond the scope of the book. If you are interested, see David M. Kroenke, David J. Auer. Scott L. Vandenberg, and Robert C. Yoder, Database Processing: Fundamentals, Design, and Imple- mentation, 15th edition (Upper Saddle River, NJ: Pearson, 2019), pages 426–431. The dis- cussion in that book includes additional uses of correlated subqueries.

SQL Queries on Recursive Relationships In Chapters 4 (see page 295) and 5 (see pages 367–370), we discussed the logic of recursive relationships, where data in a table is related to other data in the same table. We will now discuss and illustrate how to create SQL queries on a recursive relationship. As discussed in Chapter 5, 1:1 and 1:N recursive queries require only the table that is referenced by the recursive relationship, while N:M recursive queries require an intersection table (just as any other N:M relationship does).

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 26 04/07/19 8:23 PM

Online Extension B Advanced SQL B-27

As illustrated in Chapter 5, queries on recursive relationships use the same technique we just used in correlated subqueries of using the same table under two different aliases. This gives us two virtual tables, and the queries using them are written just as we would write any other SQL query.

For example, our WP database EMPLOYEE table has a 1:N recursive relationship be- tween Supervisor and EmployeeNumber. One person, the Supervisor, can supervise many other employees. We can visually examine these relationships in the EMPLOYEE table by using SQL-Query-ExtB-08, but to specifically list who supervises whom, we can use query SQL-Query-ExtB-09.

/* *** SQL-Query-ExtB-08 *** */

SELECT EmployeeNumber, FirstName, LastName, Supervisor

FROM EMPLOYEE

ORDER BY EmployeeNumber;

/* *** SQL-Query-ExtB-09 *** */

SELECT S.FirstName AS SupervisorFirstName,

S.LastName AS SupervisorLastName,

E.FirstName AS EmployeeFirstName,

E.LastName AS EmployeeLastName

FROM EMPLOYEE S JOIN EMPLOYEE E

ON S.EmployeeNumber = E.Supervisor

ORDER BY S.EmployeeNumber;

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 27 04/07/19 8:23 PM

B-28 Online Extension B Advanced SQL

The result for SQL-Query-ExtB-09 clearly shows which employees are supervisors and which employees they supervise:

USING SQL SET OPERATORS Mathematicians use the term set theory to describe mathematical operations on sets, where a set is defined as a group of distinct items. A relational database table meets the definition of a set, so it is little wonder that SQL includes a group of set operators for use with SQL queries. Venn diagrams are the standard method of visualizing sets and their relationships, and Venn diagrams are illustrated in Figure B-19.

As shown in Figure B-19:

• A set is represented by a labeled circle, as shown in Figure B-19(a). • A subset is a portion of a set that is contained entirely within the set, as shown in Figure

B-19(b). • The union of two sets is shown in Figure B-19(c), and represents the two sets together

to get a set that contains all values in both sets. This is equivalent to an OR logical operation (A OR B).

• The intersection of two sets is shown in Figure B-19(d), and represents the area com- mon to both sets. This is equivalent to an AND logical operation (A AND B).

• The complement of set B in set A is shown in Figure B-19(e), and represents everything in set A that is not in set B. This is equivalent to a logical operation using A AND (NOT B). It is equivalent to the set difference (A – B).

SQL provides SQL set operators for each of these set operations, and these are shown in Figure B-20. However, not all DBMS products support all of these operators, and to con- fuse things even more, Oracle Database use the set operator MINUS in place of EXPECT. The one operator that is always supported is the SQL UNION operator, and that is the set operator we will use here. Note that in order to use SQL set operators, the table columns involved in the operations must be the same number in each SELECT component, and corresponding columns must have the same or compatible (e.g., CHAR and VARCHAR) data types!

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 28 04/07/19 8:23 PM

Online Extension B Advanced SQL B-29

The Union is the entire area of both sets

(a) A Set (b) A Subset

(d) The Intersection of Two Sets

(e) The Complement of Two Sets

A B

The Intersection is the middle area common to both sets

The Complement is the area in A that is not in B

FIGURE B-19

Venn Diagrams

(a) A Set (b) A Subset (c) The Union of Two Sets (d) The Intersection of Two Sets (e) The Complement of Two Sets

FIGURE B-20

SQL Set Operators

Z05_KROE8149_09_SE_EXTB.indd 29 04/07/19 8:23 PM

B-30 Online Extension B Advanced SQL

To illustrate SQL set operations, imagine that your boss asks you the question: “What products were available for sale (by either catalog or Web site) in 2017 and 2018?” Looking at Figures B-4 and B-5, we can see that to answer this question we must combine all the data in the CATALOG_SKU_2017 and CATALOG_SKU_2018 tables. We do this using the SQL UNION operator, as shown in SQL-Query-ExtB-10:

/* *** SQL-Query-ExtB-10 *** */

SELECT SKU, CatalogDescription, CatalogPage, DateOnWebSite

FROM CATALOG_SKU_2017

UNION

SELECT SKU, CatalogDescription, CatalogPage, DateOnWebSite

FROM CATALOG_SKU_2018;

The result combines all the rows of CATALOG_SKU_2017 and CATALOG_ SKU_2018. The result shown here is certainly sorted by SKU, but there is no guarantee that UNION results will be sorted—they often seem that way because of the algorithms used for the UNION operation, but the only way to control sort order is to add an ORDER BY clause to the query. Note that the result shows two rows for both SKU 18103001 and SKU 18103005 because these SKUs appeared in both years. Note that even though these SKUs had a different CatalogDescription in the two years, this did not affect the result—there would have been two rows for each SKU even if the CatalogDescription was identical in both years.

3This limitation appears in the SQL-92 standard. Some DBMSs modify this limitation in their implementa- tion of SQL. For example, Oracle Database allows views to include ORDER BY, and SQL Server allows ORDER BY in very limited circumstances.

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 tables or other views. Views are created using SQL SELECT statements and then used in other SELECT statements as just another table. The only limitation on the SQL statements that create the views is that they cannot contain ORDER BY clauses.3 If the results of a query using a view need to be sorted, the sort order must be provided by the SELECT statement that processes the view.

CREATING AND WORKING WITH SQL VIEWS

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 30 04/07/19 8:23 PM

Online Extension B Advanced SQL B-31

We’ll use the WP database that we created in Chapter 3 as the example database for this discussion of views. You use the SQL CREATE VIEW statement to create view structures. The syntax of this statement is:

/* *** EXAMPLE CODE – DO NOT RUN *** */

/* *** SQL-CREATE-VIEW-ExtB-01 *** */

CREATE VIEW {ViewName} AS

{SQL SELECT statement};

The following statement defines a view named EmployeePhoneView based on the EMPLOYEE table:

/* *** SQL-CREATE-VIEW-ExtB-02 *** */

CREATE VIEW EmployeePhoneView AS

SELECT FirstName, LastName, OfficePhone AS EmployeePhone

FROM EMPLOYEE;

Figure B-21 shows the view being created in MySQL Workbench, Figure B-22 shows the view being created in the SQL Server Management Studio, and Figure B-23 shows the view being created in the Oracle SQL Developer.

Does Not Work with Microsoft Access ANSI-89 SQL

Unfortunately, Microsoft Access does not support views. However, Access allows you to create a query, name it, and then save it, which is not supported in a standard SQL implementation. You can then process Access queries in the same ways that you process views in the following discussion.

Solution: Create Microsoft Access view–equivalent queries, as discussed in the “Work- ing with Microsoft Access” section at the end of this extension.

FIGURE B-21

Creating a View in the MySQL Workbench

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Enter the SQL for the view just as you would enter the SQL for a query, and then click the Execute the SQL statement button to create the SQL view

The new view appears in the expanded Views folder after it is created

The Action Output tab CREATE VIEW message indicates that the view has been created

Z05_KROE8149_09_SE_EXTB.indd 31 04/07/19 8:23 PM

B-32 Online Extension B Advanced SQL

FIGURE B-22

Creating a View in the Microsoft SQL Server Management Studio

SQL Server 2017, SQL Server Management Studio, Microsoft Corporation.

Click the New Query button, enter the SQL for the view just as you would enter the SQL for a query, and then click the Execute button to create the SQL view

The new view appears in the expanded Views folder after it is created

The “Commands complete successfully” message in the Messages window indicates that the view has been created

options

FIGURE B-23

Creating a View in Oracle SQL Developer

Oracle Database XE, SQL Developer 18.4, Oracle Corporation.

The new view appears in the expanded Views folder after it is created

The “View EMPLOYEEPHONEVIEW created” message in the Script Output window indicates that the view has been created

Click the New SQL Query button, enter the SQL for the view just as you would enter the SQL for a query, and then click the Run Statement button to create the SQL view

Z05_KROE8149_09_SE_EXTB.indd 32 04/07/19 8:23 PM

Online Extension B Advanced SQL B-33

SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2017, and most other DBMSs process the CREATE VIEW statements as written here without difficulty. However, SQL Server 2000 will not run such statements unless you remove the semicolon at the end of the CREATE VIEW statement. We have no idea why SQL Server 2000 works this way, but be aware of this peculiarity if you are using SQL Server 2000 (which you really shouldn’t be because it is no longer being supported by Microsoft).

BTW

After we create the view, we can use it in the FROM clause of SELECT statements just as you would use a table. The following obtains a list of employee names and phone numbers, sorted first by employee last name and then by employee first name:

/* *** SQL-Query-ExtB-11 *** */

SELECT *

FROM EmployeePhoneView

ORDER BY LastName, FirstName;

Figure B-24 shows this SQL statement run in MySQL Workbench, Figure B-25 shows it run in SQL Server Management Studio, and Figure B-25 shows it run in the Oracle SQL Developer.

The SQL query results appear in the Result Grid window

Enter the SQL for the query that uses the view, and then click the Execute the SQL statement button to run the query

FIGURE B-24

Using EmployeePhoneView in the MySQL Workbench

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 33 04/07/19 8:23 PM

B-34 Online Extension B Advanced SQL

Enter the SQL for the query that uses the view, and then click the Execute button to run the query

The SQL query results appear in the Results window

FIGURE B-25

Using EmployeePhoneView in the Microsoft SQL Server Management Studio

SQL Server 2017, SQL Server Management Studio, Microsoft Corporation.

Enter the SQL for the query that uses the view, and then click the Run Statement button to run the query

The SQL query results appear in the Query Result window

FIGURE B-26

Using EmployeePhoneView in Oracle SQL Developer

Oracle Database XE, SQL Developer 18.4, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 34 04/07/19 8:23 PM

Online Extension B Advanced SQL B-35

Note that the number of columns returned depends on the number of columns in the view, not on the number of columns in the underlying table. In this example, SELECT * produces just three columns because the view has just three columns. Also notice that the column OfficePhone in the EMPLOYEE table has been renamed EmployeePhone in the view, so the DBMS uses the label EmployeePhone when producing results.

If you ever need to modify an SQL view you have created, use the SQL ALTER VIEW {ViewName} statement. This works exactly the same as the CREATE VIEW {ViewName} statement except that it replaces the existing view definition with the new one. This statement is very useful when you are trying to fine-tune your view definitions. And if you ever want to delete a view, simply use the SQL DROP VIEW {ViewName} statement.

BTW

USING SQL VIEWS In general, SQL views are used to prepare data for use in an information system applica- tion, which may or may not be a Web-based application. While applications commonly use a Web interface (via a Web browser such as Microsoft Edge, Google Chrome, or Mozilla Firefox), there are still many applications that run in their own application window.

In Chapter 4 in our discussion of systems analysis and design, we define data as recorded facts and numbers. Based on this definition, we can now define4 information as:

• Knowledge derived from data. • Data presented in a meaningful context. • Data processed by summing, ordering, averaging, grouping, comparing, or other similar

operations.

In general, application programmers prefer that the work of transforming database data into the information that will be used in and presented by the application be done by the DBMS itself. SQL views are the main DBMS tool for this work. The basic principle is that all summing, averaging, grouping, comparing, and similar operations should be done in SQL views, and that it is the final result as it appears in the SQL view that is passed to the application program for use. This is illustrated in Figure B-27.

For a specific example, let’s consider a Web page for a customer relationship manage- ment (CRM) Web application for Wallingford Motors. As shown in Figure B-28, one part of the Web CRM application displays a report named The Wallingford Motors CRM Customer Contacts List, which shows all contacts between WM salespeople (identified by NickName) and customers (identified by LastName and FirstName). This report is based on a view named viewCustomerContacts, which combines data from both the CUSTOMER table and the CONTACT table in the WM database. This example clearly illustrates the principle of combining and processing data into a view that becomes the basis of the data sent to the Web application for display in a Web page.

4These definitions are from David M. Kroenke and Randall J Boyles’s books: Using MIS (10th ed.) (Upper Saddle River, NJ: Pearson, 2018) and Experiencing MIS (8th ed.) (Upper Saddle River, NJ: Pearson, 2019). See these books for a full discussion of these definitions, as well as a discussion of a fourth definition, “a difference that makes a difference.”

Z05_KROE8149_09_SE_EXTB.indd 35 04/07/19 8:23 PM

B-36 Online Extension B Advanced SQL

FIGURE B-27

SQL Views as the Basis for Application Reports

Figure B-29 lists some of the specific uses for views.5 They can hide columns or rows. They also can be used to display the results of computed columns, to hide complicated SQL syntax, and to layer the use of built-in functions to create results that are not possible with a single SQL statement. We will give examples of each of these uses.

Using Views to Hide Columns or Rows Views can be used to hide columns to simplify results or to prevent the display of sensitive data. For example, suppose the users at WP want a simplified list of departments that has just the department names and phone numbers. One use for such a view would be to popu- late a Web page. The following statement defines a view, BasicDepartmentDataView, that will produce that list:

/* *** SQL-CREATE-VIEW-ExtB-03 *** */

CREATE VIEW BasicDepartmentDataView AS

SELECT DepartmentName, DepartmentPhone

FROM DEPARTMENT;

5Additional uses of SQL views are discussed in David M. Kroenke, David J. Auer, Scott L. Vandenberg, and Robert C. Yoder, Database Processing: Fundamentals, Design, and Implementation, 15th edition (Upper Saddle River, NJ: Pearson, 2019), Chapter 7.

Z05_KROE8149_09_SE_EXTB.indd 36 04/07/19 8:23 PM

Online Extension B Advanced SQL B-37

FIGURE B-28

The Wallingford Motors CRM Web Application Customer Contacts List

Access 2019, Windows 10, Microsoft Corporation.

Edge, Windows 10, Microsoft Corporation.

Access 2019, Windows 10, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 37 04/07/19 8:23 PM

B-38 Online Extension B Advanced SQL

The following SELECT statement obtains a list of department names and phone numbers sorted by the DepartmentName:

/* *** SQL-Query-ExtB-12 *** */

SELECT *

FROM BasicDepartmentDataView

ORDER BY DepartmentName;

The results of a SELECT sorted by DepartmentName on this view are:

FIGURE B-29

Some Uses for SQL Views

Views can also hide rows by providing a WHERE clause in the view definition. The next SQL statement defines a view of WP projects in the Sales and Marketing department:

/* *** SQL-CREATE-VIEW-ExtB-04 *** */

CREATE VIEW MarketingDepartmentProjectView AS

SELECT ProjectID, ProjectName, MaxHours,

StartDate, EndDate

FROM PROJECT

WHERE Department = 'Sales and Marketing';

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 38 04/07/19 8:23 PM

Online Extension B Advanced SQL B-39

The following SELECT statement obtains a list of projects managed by the Sales and Mar- keting department, sorted by the ProjectID number:

/* *** SQL-Query-ExtB-13 *** */

SELECT *

FROM MarketingDepartmentProjectView

ORDER BY ProjectID;

The results of a SELECT sorted by ProjectID on this view are:

As desired, only the Sales and Marketing department projects are shown in this view. This limitation is not obvious from the results because Department is not included in the view. This characteristic can be good or bad, depending on the use of the view. It is good if this view is used in a setting in which only marketing department projects matter; it is bad if the view indicates that these projects are the only WP projects currently under way.

Using Views to Display Results of Computed Columns Another use of views is to show the results of computed columns without requiring the user to enter the computation expression. For example, the following view allows the user to compare the maximum hours allocated for each WP project to the total hours worked to date on the project:

/* *** SQL-CREATE-VIEW-ExtB-05 *** */

CREATE VIEW ProjectHoursToDateView AS

SELECT PROJECT.ProjectID, ProjectName,

MaxHours AS ProjectMaxHours,

SUM(HoursWorked) AS ProjectHoursWorkedToDate

FROM PROJECT JOIN ASSIGNMENT

ON PROJECT.ProjectID = ASSIGNMENT.ProjectID

GROUP BY PROJECT.ProjectID;

Note, however, that Microsoft SQL Server and Oracle Database require that any column specified in the SELECT phrase be used in either an SQL built-in function or the GROUP BY phrase. The previous SQL statement is correct SQL-92 syntax and will run in MySQL as written. However, Microsoft SQL Server and Oracle Database require you to write:6

/* *** SQL-CREATE-VIEW-ExtB-05-MSSQL *** */

CREATE VIEW ProjectHoursToDateView AS

SELECT PROJECT.ProjectID, ProjectName,

MaxHours AS ProjectMaxHours,

SUM(HoursWorked) AS ProjectHoursWorkedToDate

FROM PROJECT JOIN ASSIGNMENT

ON PROJECT.ProjectID = ASSIGNMENT.ProjectID

GROUP BY PROJECT.ProjectID, ProjectName, MaxHours;

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

6This version will also run in MySQL.

Z05_KROE8149_09_SE_EXTB.indd 39 04/07/19 8:23 PM

B-40 Online Extension B Advanced SQL

Note the use of the extra column names in the GROUP BY clause. These are necessary to create the view but have no practical effect on the results. When the view user enters:

/* *** SQL-Query-ExtB-14 *** */

SELECT *

FROM ProjectHoursToDateView

ORDER BY ProjectID;

these results are displayed:

Placing computations in views has two major advantages. First, it saves users from having to know or remember how to write an expression to get the results they want. Second, it ensures consistent results. If developers who use a computation write their own SQL ex- pressions, they may write the expression differently and obtain inconsistent results.

Using Views to Hide Complicated SQL Syntax Another use of views is to hide complicated SQL syntax. By using views, developers do not need to enter complex SQL statements when they want particular results. Also, such views allow developers who do not know how to write complicated SQL statements to enjoy the benefits of such statements. This use of views also ensures consistency.

Suppose that WP users need to know which employees are assigned to which projects and how many hours each employee has worked on that project. To display these interests, two joins are necessary: one to join EMPLOYEE to ASSIGNMENT and another to join that result to PROJECT. You saw the SQL statement to do this in Chapter 3:

/* *** SQL-Query-CH03-51 *** */

SELECT ProjectName, FirstName, LastName, HoursWorked

FROM EMPLOYEE AS E JOIN ASSIGNMENT AS A

ON E.EmployeeNumber = A.EmployeeNumber

JOIN PROJECT AS P

ON A.ProjectID = P.ProjectID

ORDER BY P.ProjectID, A.EmployeeNumber;

Now we need to make it into a view named EmployeeProjectHoursWorkedView. Remember that we cannot include the ORDER BY clause in the view. If we want to sort the output, we will need to do this when we use the view:

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 40 04/07/19 8:23 PM

Online Extension B Advanced SQL B-41

/* *** SQL-CREATE-VIEW-ExtB-06 *** */

CREATE VIEW EmployeeProjectHoursWorkedView AS

SELECT ProjectName, FirstName, LastName, HoursWorked

FROM EMPLOYEE AS E JOIN ASSIGNMENT AS A

ON E.EmployeeNumber = A.EmployeeNumber

JOIN PROJECT AS P

ON A.ProjectID = P.ProjectID;

This is a complicated SQL statement to write, but after the view is created the results of this statement can be obtained with a simple SELECT statement. Now the user simply uses the SQL query (note the sorting by employee name—we cannot sort on ProjectID and Employ- eeNumber because these columns were not included in the view):

/* *** SQL-Query-ExtB-15 *** */

SELECT *

FROM EmployeeProjectHoursWorkedView

ORDER BY LastName, FirstName;

The result of SQL-Query-ExtB-15 is shown in Figure B-30. Clearly, using the view is much simpler than constructing the join syntax. Even developers who know SQL well will appre- ciate having a simpler view with which to work.

FIGURE B-30

The Results for SQL-Query-ExtB-15

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 41 04/07/19 8:23 PM

B-42 Online Extension B Advanced SQL

Layering Computations and Built-In Functions Recall from Chapter 3 that you cannot use a computation or a built-in function as part of a WHERE clause. You can, however, construct a view that computes a variable and then write an SQL statement on that view that uses the computed variable in a WHERE clause. To un- derstand this, consider the ProjectHoursToDateView definition created previously as SQL- CREATE-VIEW-ExtB-05 with the modified GROUP BY clause needed for SQL Server 2017:

/* *** SQL-CREATE-VIEW-ExtB-05-MSSQL *** */

CREATE VIEW ProjectHoursToDateView AS

SELECT PROJECT.ProjectID,

ProjectName,

MaxHours AS ProjectMaxHours,

SUM(HoursWorked) AS ProjectHoursWorkedToDate

FROM PROJECT JOIN ASSIGNMENT

ON PROJECT.ProjectID = ASSIGNMENT.ProjectID

GROUP BY PROJECT.ProjectID, ProjectName, MaxHours;

The view definition contains the maximum allocated hours for each project and the total hours actually worked on the project to date as ProjectHoursWorkedToDate. Now we can use ProjectHoursWorkedToDate in both an additional calculation and the WHERE clause, as follows:

/* *** SQL-Query-ExtB-16 *** */

SELECT ProjectID, ProjectName, ProjectMaxHours,

ProjectHoursWorkedToDate

FROM ProjectHoursToDateView

WHERE ProjectHoursWorkedToDate > ProjectMaxHours

ORDER BY ProjectID;

Here, we are using the result of a computation in a WHERE clause, something that is not allowed in a single SQL statement. This allows users to determine which projects have exceeded the number of hours allocated to them by producing the result—and it looks like WP management needs to seriously rein in those project hours!

Such layering can be continued over many levels. We can turn this SELECT statement into another view named ProjectsOverAllocatedMaxHoursView (again without the ORDER BY clause):

/* *** SQL-CREATE-VIEW-ExtB-07 *** */

CREATE VIEW ProjectsOverAllocatedMaxHoursView AS

SELECT ProjectID, ProjectName, ProjectMaxHours,

ProjectHoursWorkedToDate

FROM ProjectHoursToDateView

WHERE ProjectHoursWorkedToDate > ProjectMaxHours;

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 42 04/07/19 8:23 PM

Online Extension B Advanced SQL B-43

Now we can use ProjectsOverAllocatedMaxHoursView in a further calculation—this time to find the number of hours by which each project has overrun its allocated hours:

/* *** SQL-Query-ExtB-17 *** */

SELECT ProjectID, ProjectName, ProjectMaxHours,

ProjectHoursWorkedToDate,

(ProjectHoursWorkedToDate - ProjectMaxHours)

AS HoursOverMaxAllocated

FROM ProjectsOverAllocatedMaxHoursView

ORDER BY ProjectID;

Here are the results, and now we can see just how far over ProjectMaxHours each project has gone:

SQL views are very useful tools for database developers (who will define the views) and ap- plication programmers (who will use the views in applications).

SQL/PERSISTENT STORED MODULES (SQL/PSM) Each DBMS product has its own variant or extension of SQL, including features that allow SQL to function similarly to a procedural programming language. The ANSI/ISO standard refers to these as SQL/Persistent Stored Modules (SQL/PSM). Microsoft SQL Server calls its version of SQL Transact-SQL (T-SQL), and Oracle Database calls its version of SQL Procedural Language/SQL (PL/SQL). The MySQL variant also includes SQL/PSM com- ponents, but it has no special name and is just called SQL in the MySQL documentation.

SQL/PSM provides the program variables and cursor functionality discussed in Chapter 6. It also includes control-of-flow language such as BEGIN . . . END blocks, IF . . . THEN . . . ELSE logic structures, and LOOPs, as well as the ability to provide usable output to users.

The most important feature of SQL/PSM, however, is that it allows the code that imple- ments these features in a database to be contained in that database. Thus the name: Persistent— the code remains available for use over time—Stored—the code is stored for reuse in the database—Modules—the code is written as a self-contained block of code. The SQL code can be written as one of three module types: user-defined functions, triggers, and stored procedures.

SQL/PSM User-Defined Functions A user-defined function (also known as a stored function) is a stored set of SQL statements that:

• Is called by name from another SQL statement. • May have input parameters passed to it by the calling SQL statement. • Returns an output value to the SQL statement that called the function.

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 43 04/07/19 8:23 PM

B-44 Online Extension B Advanced SQL

The logical process flow of a user-defined function is illustrated in Figure B-31. SQL/ PSM user-defined functions are very similar to the SQL built-in functions (COUNT, SUM, AVG, MAX, and MIN) that we discussed and used in Chapter 3, except that, as the name implies, we create them ourselves to perform specific tasks that we need to do.

A common problem is needing a name in the format LastName, FirstName (includ- ing the comma!) in a report when the database stores the basic data in two fields named FirstName and LastName. Using the data in the WP database, we could, of course, simply include the code to do this in an SQL statement using a concatenation operator. MySQL 8.0 uses the concatenation string function CONCAT() as the concatenation operator, and SQL- Query-ExtB-18 is written for MySQL 8.0 as:

/* *** SQL-Query-ExtB-18 *** */

SELECT CONCAT(RTRIM(LastName),', ',RTRIM(FirstName))

AS EmployeeName,

Department, OfficePhone, EmailAddress

FROM EMPLOYEE

ORDER BY EmployeeName;

This produces the desired results, as shown in Figure B-32, but at the expense of working out some cumbersome coding.

FIGURE B-31

User-Defined Functions Logical Process Flow

FIGURE B-32

The Results for SQL-Query-ExtB-18

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 44 04/07/19 8:23 PM

Online Extension B Advanced SQL B-45

SQL-Query-ExtB-18 is written for MySQL 8.0 using MySQL SQL syntax. As usual, SQL syntax varies from DBMS to DBMS. Microsoft SQL Server uses a plus sign [+] as the concatenation operator, and SQL-Query-ExtB-18 is written for Microsoft SQL Server as:

/* *** SQL-Query-ExtB-18-MSSQL *** */

SELECT RTRIM(LastName)+', '+RTRIM(FirstName) AS EmployeeName,

Department, OfficePhone, EmailAddress

FROM EMPLOYEE

ORDER BY EmployeeName;

Oracle Database uses a double vertical bar [||] as the concatenation operator, and SQL- Query-ExtB-18 is written for Oracle Database as:

/* *** SQL-Query-ExtB-18-Oracle *** */

SELECT RTRIM(LastName)||', '||RTRIM(FirstName) AS EmployeeName,

Department, OfficePhone, EmailAddress

FROM EMPLOYEE

ORDER BY EmployeeName;

The alternative is to create a user-defined function to store this code. Not only does this make it easier to use, but it also makes it available for use in other SQL statements. Figure B-33 shows a user-defined function written in MySQL’s variant of SQL syntax for

CHAR

CREATE FUNCTION

DELIMITER //

DELIMITER ;

RETURNS VARCHAR

BEGIN

END

-- This is the variable that will hold the value to be returned

-- SQL statements to concatenate the names in the proper order

-- Return the concatentated name

RETURN FullName;

DECLARE VARCHARFullName

SET FullName = LastName

DETERMINISTIC

-- These are the input parameters

NameConcatenation

FirstName

LastName

(

)

25),

CHAR(25)

(60)

(

CONCAT( ,

’,’, FirstName);

);

FIGURE B-33

MySQL 8.0 User- Defined Functions

(a) Code to Create User-Defined Function to Concatenate FirstName and LastName

Z05_KROE8149_09_SE_EXTB.indd 45 04/07/19 8:23 PM

B-46 Online Extension B Advanced SQL

use with MySQL Community Server 8.0, and the SQL code for the function uses, as we would expect, specific syntax requirements for MySQL 8.0:

• The function is created and stored in the database by using the SQL CREATE FUNCTION statement.

• The concatenation syntax is MySQL 8.0 SQL syntax.

Now that we have created and stored the user-defined function, we can use it in SQL-Query-ExtB-19:

/* *** SQL-Query-ExtB-19 *** */

SELECT NameConcatenation(FirstName, LastName) AS EmployeeName,

Department, OfficePhone, EmailAddress

FROM EMPLOYEE

ORDER BY EmployeeName;

The advantage of having a user-defined function is that we can now use it whenever we need to without having to recreate the code. Now we have a function that produces the results we want, which of course are identical to the results for SQL-Query-ExtB-18, and which are shown in Figure B-34.

The NameConcatenation user- defined function now appears in the expanded Functions section

Enter the SQL for the user- defined function, and then click the Execute the SQL statement button to run the statement to create the function

The Action Output tab CREATE FUNCTION message indicates that the function has been created

(b) The NameConcatenation User-Defined Function in MySQL Workbench

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 46 04/07/19 8:23 PM

Online Extension B Advanced SQL B-47

The user-defined function NameConcatenation as shown earlier is written for MySQL 8.0 using MySQL’s variant of SQL. As usual, SQL syntax varies from DBMS to DBMS. The Microsoft SQL Server 2017 version is written as (note the dbo used in the function name—this is a Microsoft SQL Server schema name [where the word schema has a different meaning than it does in MySQL—see the documentation], and a schema name is often used as part of the name of SQL Server object):

BTW

dbo.NameConcatenationCREATE FUNCTION

RETURNS VARCHAR

DECLARE

BEGIN

END

-- These are the input parameters

-- This is the variable that will hold the value to be returned

-- SQL statements to concatenate the names in the proper order

-- Return the concatenated name

)

(

@FirstName CHAR(25),

@FullName VARCHAR(60);

SELECT @FullName @LastNameRTRIM= ( @FirstNameRTRIM() + ’, ’ + );

RETURN @FullName;

@LastName CHAR(25),

(60)

SQL Server 2017, SQL Server Management Studio, Microsoft Corporation.

FIGURE B-34

The Result of SQL-Query-ExtB-19

MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 47 04/07/19 8:23 PM

B-48 Online Extension B Advanced SQL

The Oracle Database XE version is written as:

CREATE OR REPLACE FUNCTION

BEGIN

END;

RETURN

RETURN varFullName;

varFullName := (RTRIM(varLastName) || ’, ’ || RTRIM(varFirstName));

IS VarcharvarFullName

Varchar

IN Char,

NameConcatenation

varFirstName

IN CharvarLastName

-- These are the input parameters

-- This is the variable that will hold the returned value

-- SQL statement to concatenate the names in the proper order

-- Return the concatenated name

(

)

(60);

SQL/PSM Triggers A trigger is a stored program that is executed by the DBMS whenever a specified event oc- curs. Triggers for Oracle Database are written in Java or in Oracle’s PL/SQL. SQL Server triggers are written in Microsoft .NET Common Language Runtime (CLR) languages, such as Visual Basic .NET, or Microsoft’s T-SQL. MySQL triggers are written in MySQL’s variant of SQL. In this chapter, we will discuss triggers in a generic manner without considering the particulars of those languages.

A trigger is attached to a table or a view. A table or a view may have many triggers, but a trigger is associated with just one table or view. A trigger is invoked by an SQL DML INSERT, UPDATE, or DELETE request on the table or view to which it is at- tached. Figure B-35 summarizes the triggers available for MySQL 8.0, SQL Server 2017, and Oracle Database XE.

There are three types of triggers: BEFORE, INSTEAD OF, and AFTER. As you would expect, BEFORE triggers are executed before the DBMS processes the insert, update, or delete request. INSTEAD OF triggers are executed in place of any DBMS processing of the insert, update, or delete request. AFTER triggers are executed after the insert, update, or delete request has been processed. Taken all together, nine trigger combinations are possible: BEFORE (INSERT, UPDATE, DELETE); INSTEAD OF (INSERT, UPDATE, DELETE); and AFTER (INSERT, UPDATE, DELETE).

MySQL 8.0 supports only BEFORE and AFTER triggers—thus, it supports only six trigger combinations.

Since SQL Server 2005, SQL Server supports DDL triggers (triggers on such SQL DDL statements as CREATE, ALTER, and DROP) as well as DML triggers. We will only deal with the DML triggers here, which for SQL Server 2017 are INSTEAD OF and AF- TER triggers on INSERT, UPDATE, and DELETE (Microsoft includes the FOR keyword, but this is a synonym for AFTER in Microsoft syntax.) Thus, we have six possible trigger combinations for use in SQL Server 2017.

Oracle Database XE, SQL Developer 18.4, Oracle Corporation.

Z05_KROE8149_09_SE_EXTB.indd 48 04/07/19 8:23 PM

Online Extension B Advanced SQL B-49

Oracle Database XE supports three kinds of triggers: BEFORE, INSTEAD OF, and AFTER. Therefore, Oracle Database support all nine trigger combinations. Note, however, that in Oracle Database INSTEAD OF triggers can be used only with views, not tables.

Other DBMS products support triggers differently. See the documentation of your product to determine which trigger types it supports.

When a trigger is invoked, the DBMS makes the data involved in the requested action available to the trigger code. For an insert, the DBMS will supply the values of columns for the row that is being inserted. For deletions, the DBMS will supply the values of columns for the row that is being deleted. For updates, it will supply both the old and the new values. The way in which this is done depends on the DBMS product.

While a full discussion of triggers is beyond the scope of this book,7 we will note that triggers have many uses, and four common uses for triggers are:

• Providing default values. • Enforcing data constraints. • Updating SQL views. • Performing referential integrity actions.

SQL/PSM Stored Procedures A stored procedure is a program that is stored within the database and compiled when used. In Oracle Database, stored procedures can be written in PL/SQL or in Java. With SQL Server 2012 and later, stored procedures are written in T-SQL or a .NET CLR lan- guage, such as Visual Basic.NET, C#.NET, or C++.NET. With MySQL, stored procedures are written in MySQL’s variant of SQL.

Stored procedures can receive input parameters and return results. Unlike triggers, which are attached to a given table or view, stored procedures are attached to the database. They can be executed by any process using the database that has permission to use the pro- cedure. Differences between triggers and stored procedures are summarized in Figure B-36.

7For more information about SQL triggers and how to use them, see David M. Kroenke, David J. Auer, Scott L. Vandenberg, and Robert C. Yoder, Database Processing: Fundamentals, Design, and Implementation, 15th edition (Upper Saddle River, NJ: Pearson, 2019).

FIGURE B-35

Summary of SQL Triggers by DBMS Product

Z05_KROE8149_09_SE_EXTB.indd 49 04/07/19 8:23 PM

B-50 Online Extension B Advanced SQL

FIGURE B-36

Triggers Versus Stored Procedures

FIGURE B-37

Advantages of Stored Procedures

Stored procedures are used for many purposes. Although database administrators use them to perform common administration tasks, their primary use is within database ap- plications. They can be invoked from application programs written in languages such as COBOL, C, Java, C#, or C++. They also can be invoked from Web pages using VBScript, JavaScript, or PHP. Ad hoc users can run them from DBMS management products such as SQL*Plus or SQL Developer in Oracle Database, SQL Server Management Studio in SQL Server, or the MySQL Workbench in MySQL.

While a full discussion of stored procedures is beyond the scope of this book, we will note that there are many advantages of using stored procedures. These are listed in Figure B-37.

Z05_KROE8149_09_SE_EXTB.indd 50 04/07/19 8:23 PM

Online Extension B Advanced SQL B-51

Unlike application code, stored procedures are never distributed to client computers. They always reside in the database and are processed by the DBMS on the database server. Thus, they are more secure than distributed application code, and they also reduce network traffic. Increasingly, stored procedures are the preferred mode of processing application logic over the Internet or corporate intranets. Another advan- tage of stored procedures is that their SQL statements can be optimized by the DBMS compiler.

When application logic is placed in a stored procedure, many different application pro- grammers can use that code. This sharing results not only in less work but also in standard- ized processing. Further, the developers best suited for database work can create the stored procedures while other developers, say, those who specialize in Web-tier programming, can do other work. Because of these advantages, it is likely that stored procedures will see in- creased use in the future.8

8For more information about SQL stored procedures and how to use them, see David M. Kroenke, David J. Auer, Scott L. Vandenberg, and Robert C. Yoder, Database Processing: Fundamentals, Design, and Implementation, 15th edition (Upper Saddle River, NJ: Pearson, 2019).

9David M. Kroenke, David J. Auer, Scott L. Vandenberg, and Robert C. Yoder, Database Processing: Fundamentals, Design, and Implementation, 15th edition (Upper Saddle River, NJ: Pearson, 2019).

10David M. Kroenke, David J. Auer, Scott L. Vandenberg, and Robert C. Yoder, Database Processing: Fundamentals, Design, and Implementation, 15th edition (Upper Saddle River, NJ: Pearson, 2019).

IMPORTING MICROSOFT EXCEL DATA INTO A DATABASE TABLE Because most users don’t understand databases, they will typically build a Microsoft Excel worksheet (called a spreadsheet in other electronic spreadsheet products such as Apache OpenOffice Calc) to store the data that they work with. This often results in a database developer needing to import the Microsoft Excel data into one or more tables in a database.

The techniques for importing Microsoft Excel data vary considerably from DBMS to DBMS. For a discussion of how to import Microsoft Excel 2017 data into MySQL 8.0, Microsoft SQL Server 2017, and Oracle Database XE and refer to the following sources:

• For MySQL 8.0, see online Extension A, “Working with MySQL.” • For Microsoft SQL Server 2017, see online Chapter 10A in Database Processing: Funda-

mentals, Design, and Implementation.9

• For Oracle Database XE, see online Chapter 10B in Database Processing: Fundamentals, Design, and Implementation.10

For a discussion of how to import Microsoft Excel 2016 data into Microsoft Access 2019, see the section of “Working with Microsoft Access” later in this extension.

USING MICROSOFT ACCESS 2019 AS A DEVELOPMENT PLATFORM While we often prefer an enterprise-class DBMS product such as MySQL 8.0, Microsoft SQL Server 2017, or Oracle Database XE for creating databases rather than a personal da- tabase product such as Microsoft Access 2019, none of the enterprise class DBMSs provides application development tools (at least not as part of the DBMS product itself). This means that application developers need some tool or tools to develop forms, reports, and other components of an application front end to a database.

Z05_KROE8149_09_SE_EXTB.indd 51 04/07/19 8:23 PM

B-52 Online Extension B Advanced SQL

One of the advantages of Microsoft Access 2019 is that it does include application development tools. In fact, we have used these tools to create forms, reports, and other application components (see the section of “Working with Microsoft Access” in online Extension C for a discussion of working with Microsoft Access 2019 Switchboards) for the WMCRM database we’ve built in the various sections of “Working with Microsoft Access.”

Is it possible to use the Microsoft Access 2019 application development tools with a database that exists in another DBMS such as Microsoft SQL Server 2017, Oracle Database XE, or MySQL 8.0? The answer is yes: We can connect a Microsoft Access 2019 database as a development platform to databases in these DBMS products using a tool known as Open Database Connectivity (ODBC). For a discussion of how to use ODBC to link Microsoft Access 2019 to MySQL 8.0, see online Extension A, “Working with MySQL.”11

WORKING WITH MICROSOFT ACCESS

Section B

Advanced SQL in Microsoft Access and Importing Microsoft Excel Data In Chapter 3’s section of “Working with Microsoft Access,” you learned to work with Microsoft Access SQL and QBE. In this section, you’ll learn how to use some of the advanced SQL statements discussed in this extension including:

• How to use the SQL ALTER TABLE statement. • How to create and use a query on a recursive relationship. • How to create and use the Microsoft Access equivalent of SQL views.

In addition, we will describe:

• How to import Microsoft Excel 2019 data into a Microsoft Access Table.

We will continue to use the WMCRM database we have been using. At this point, we will assume we have completed all sections of “Working with Microsoft Access” in Chapters 1, 2, and 3, and so we have created and populated (which means we’ve inserted the data into) the CUSTOMER, PHONE_NUMBER, CONTACT, and SALESPERSON tables and have set the referential integrity constraints between them.

WORKING WITH THE SQL ALTER TABLE STATEMENT IN MICROSOFT ACCESS We introduced the SQL ALTER TABLE statement in Chapter 3 and then used it in that chapter’s section of “Working with Microsoft Access.” There we used it both to add the NOT NULL column NickName to the CUSTOMER table and to add a CHECK constraint to the CONTACT table. We simply walked through the steps, and only in this extension did we cover these uses of the ALTER TABLE statement in depth. Nonetheless, if you worked through all of Chapter 3’s section of “Working with Microsoft Access,” you have used the ALTER TABLE statement before, and it should seem familiar to you.

11For a discussion of using ODBC with Microsoft SQL Server 2017, see online Chapter 10A in David M. Kroenke, David J. Auer, Scott L. Vandenberg, and Robert C. Yoder, Database Processing: Fundamentals, Design, and Implementation, 15th edition (Upper Saddle River, NJ: Pearson, 2019). For a discussion of using ODBC with Oracle Database XE, see online Chapter 10B in the same book.

Z05_KROE8149_09_SE_EXTB.indd 52 04/07/19 8:23 PM

Online Extension B Advanced SQL B-53

Here we will use it to add a column to the CUSTOMER table that we will need in order to create a query on a recursive relationship. At Wallingford Motors, one way the new customers are found is by the recommendation of existing customers. When a sale is made to a customer, that customer is asked to recommend one (and only one) potential customer. If the customer makes such a recommendation, he or she is rewarded with a 3 percent reduction in the sales price of the car he or she is buying. Note that this recommendation is not a requirement, and not all customers are comfortable giving Wallingford Motors the name of someone they know.

To record this data, we must add a ReferredBy column to the CUSTOMER table. As shown in Figure WA-B-1, the column will be integer data (because CustomerID is integer) and not required (or NULL) (because not all customers were recommended by other cus- tomers). Because customers can only refer one other person, the data values of ReferredBy must be unique (each CustomerID value can only appear once in the ReferredBy column). Further, this column will have a recursive relationship within the CUSTOMER table be- cause ReferredBy will be a foreign key referencing CustomerID. Figure WA-B-2 shows the data for the ReferredBY column that will need to be added after the column is created.

Let’s take this one step at time. First, we add the NULL column ReferredBy. To do this, we will use the SQL ALTER TABLE statement:

/* *** SQL-ALTER-TABLE-WA-ExtB-01 *** */

ALTER TABLE CUSTOMER

ADD ReferredBy INTEGER NULL;

Then we use SQL UPDATE statements to add new data to the column:

/* *** SQL-UPDATE-WA-ExtB-01 *** */

UPDATE CUSTOMER

SET ReferredBy = 3

WHERE CustomerID = 4;

/* *** SQL-UPDATE-WA-ExtB-02 *** */

UPDATE CUSTOMER

SET ReferredBy = 1

WHERE CustomerID = 5;

FIGURE WA-B-1

WMCRM Database Column Characteristics for the CUSTOMER ReferredBy Column

FIGURE WA-B-2

WMCRM Database Column Characteristics for the CUSTOMER ReferredBy Column

Z05_KROE8149_09_SE_EXTB.indd 53 04/07/19 8:23 PM

B-54 Online Extension B Advanced SQL

Creating and Populating the ReferredBy Column in the CUSTOMER Table by using Microsoft Access SQL

1. Start Microsoft Access 2019, and open the WMCRM.accdb database. 2. Open an Access Query window in SQL View as discussed in Chapter 3. 3. Type the SQL-ALTER-Table-WA-ExtB-01 statement shown earlier into the query window. 4. Click the Run button.

NOTE: The only indication that the command has run successfully is the fact that no error message is displayed.

5. Type the SQL-UPDATE-WA-ExtB-01 statement shown earlier into the query window. 6. Click the Run button. When the dialog box appears asking you to confirm that you want to

insert the data, click the Yes button in the dialog box. The data are inserted into the table. 7. Type in the SQL-UPDATE-WA-ExtB-02 statement shown earlier into the query window. 8. Click the Run button. When the dialog box appears asking you to confirm that you want to

insert the data, click the Yes button in the dialog box. The data are inserted into the table. 9. Close the Query1 window. A dialog box appears asking if you want to save the query. Click

the No button—there is no need to save this SQL statement. 10. Open the CUSTOMER table. 11. Click the Shutter Bar Open/Close button, if needed, to minimize the Navigation Pane

and then scroll to the right if necessary so that the added ReferredBy column and the data in it are displayed. The table looks as shown in Figure WA-B-3.

12. Click the Shutter Bar Open/Close button to expand the Navigation Pane if necessary, and then click the Design View button to switch the CUSTOMER table into Design view.

13. Click the ReferredBy field name to select it. 14. Set the Indexed field property setting to Yes (no Duplicates), as shown in Figure WA-B-4. 15. Note that the Required field property setting is set to No—this is the Microsoft Access

equivalent of NULL. 16. Close the CUSTOMER table. If a dialog box appears asking “Do you want to save

changes to the design of table CUSTOMER?” click the Yes button.

The ReferredBy column with data in the CUSTOMER table

The CUSTOMER table

FIGURE WA-B-3

The ReferredBy Column in the CUSTOMER Table

Access 2019, Windows 10, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 54 04/07/19 8:23 PM

Online Extension B Advanced SQL B-55

Why did we set the Indexed property for the ReferredBy column to Yes (No Duplicates)? We did this because this is how we enforce the requirement that values in this column be unique. Further, as discussed in Chapter 5’s section of “Working with Microsoft Access” (see pages 381–386), this setting is necessary to create a 1:1 relationship instead of a 1:N relationship. We need this because the recursive relationship we are about to create needs to be a 1:1 relationship.

To create this recursive relationship, we will again use an SQL ALTER TABLE statement:

/* *** SQL-ALTER-TABLE-WA-ExtB-02 *** */

ALTER TABLE CUSTOMER

ADD CONSTRAINT REF_BY_CUSTOMER_FK FOREIGN KEY(ReferredBy)

REFERENCES CUSTOMER(CustomerID);

Creating a 1:1 Recursive Relationship Within the CUSTOMER Table

1. Open an Access query window in SQL View. 2. Type the SQL code for the SQL-ALTER-TABLE-WA-ExtB-02 statement into the query

window. 3. Click the Run button.

NOTE: As before, the only indication that the command has run successfully is the fact that no error message is displayed.

4. Close the Query1 window. A dialog box appears asking if you want to save the query. Click the No button—there is no need to save this SQL statement.

5. Click the Shutter Bar Open/Close button to minimize the Navigation Pane. 6. Click the Database Tools command tab. 7. Click the Relationships button in the Relationships group. The Relationships tabbed docu-

ment window appears, as shown in Figure WA-B-5. 8. In Figure WA-B-5, note how Microsoft Access displays recursive relationships—a new

table named CUSTOMER_1 has been introduced. This is a virtual table, used to allow Microsoft Access to create a relationship between two tables!

Set the Indexed field property to Yes (No Duplicates)

The ReferredBy column

FIGURE WA-B-4

Setting the Indexed Field Property to Yes (No Duplicates)

Access 2019, Windows 10, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 55 04/07/19 8:23 PM

B-56 Online Extension B Advanced SQL

9. Also notice that the relationship line shows a “many” symbol where it should show a “1” symbol because this is a 1:1 relationship.

10. To verify that you do in fact have the correct 1:1 relationship, right-click the relationship line between CUSTOMER and CUSTOMER_1, and then click Edit Relationship in the shortcut menu that appears. The Edit Relationships dialog box appears.

11. Note that the correct 1:1 relationship between the tables is confirmed in the Relationships window, as shown in Figure WA-B-6.

12. Click the OK button in the Edit Relationships dialog box to close it. 13. Click the Save button to save any changes. 14. Close the Relationships window.

The CUSTOMER_1 table

This symbol should be a “1” to reflect the 1:1 recursive relationship

FIGURE WA-B-5

The 1:1 Recursive Relationship Within the CUSTOMER Table

Access 2019, Windows 10, Microsoft Corporation.

This 1:1 recursive relationship is confirmed

FIGURE WA-B-6

The 1:1 Recursive Relationship Is Confirmed

Access 2019, Windows 10, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 56 04/07/19 8:23 PM

Online Extension B Advanced SQL B-57

Given the result, we can easily see that Ben Griffey referred Judy Hayes to Wallingford Motors, while Rob Christman was referred by Jessica Christman. To specifically list who referred whom, we can use query SQL-Query-WA-ExtB-02.

Now that we have added the ReferredBy column to the CUSTOMER table, populated the column with data, and established the recursive relationship and referential integrity con- straint within the CUSTOMER table, we can create a query on the recursive relationship in the CUSTOMER table.

We’ll do this with an SQL query, and then take a look at the QBE graphical interpreta- tion of it. In our WMCRM database, the CUSTOMER table has a 1:1 recursive relationship between ReferredBy and CustomerID. One customer can refer only one other person as a potential customer. We can visually examine these relationships in the CUSTOMER table by using SQL-Query-WA-ExtB-01, which we will save under that name in the WMCRM database.

/* *** SQL-Query-WA-ExtB-01 *** */

SELECT CustomerID, FirstName, LastName, ReferredBy

FROM CUSTOMER

ORDER BY CustomerID;

Creating and Running the SQL-Query-WA-ExtB-01 SQL Query

1. Click the Create command tab to display the Create command groups. 2. Click the Query Design button. 3. The Query1 tabbed document window is displayed in Design view, along with the Show

Table dialog box. 4. Click the Close button on the Show Table dialog box. 5. Click the SQL View button in the Results group on the Design tab. The Query1 window

switches to the SQL view. 6. Edit the SQL SELECT command to read:

SELECT CustomerID, FirstName, LastName, ReferredBy

FROM CUSTOMER

ORDER BY CustomerID;

7. Click the Save button on the Quick Access Toolbar. The Save As dialog box appears. 8. Type in the query name SQL-Query-WA-ExtB-01, and then click the OK button. The

query is saved, and the query window is renamed with the query name. 9. Click the Run button on the Design tab. The query result is:

WORKING WITH QUERIES ON RECURSIVE RELATIONSHIPS IN MICROSOFT ACCESS

Access 2019, Windows 10, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 57 04/07/19 8:23 PM

B-58 Online Extension B Advanced SQL

/* *** SQL-Query-WA-ExtB-02 *** */

SELECT C.FirstName AS CustomerFirstName,

C.LastName AS CustomerLastName,

RB.FirstName AS ReferredByFirstName,

RB.LastName AS ReferredByLastName

FROM CUSTOMER AS C INNER JOIN CUSTOMER AS RB

ON C.ReferredBy = RB.CustomerID

ORDER BY C.CustomerID;

The process for creating, saving, and running this query is basically the same as the one we used for SQL-Query-WA-ExtB-01. The result clearly shows which customers were referred by other customers:

While we created SQL-Query-WA-ExtB-02 as an SQL query, we can look at how it appears in Microsoft Access QBE. Switching the SQL-Query-WA-ExtB-02 window to Design View shows us how that looks as shown in Figure WA-B-7:

Access 2019, Windows 10, Microsoft Corporation.

FIGURE WA-B-7

The SQL-Query-WA-ExtB-02 QBE Design Window

Access 2019, Windows 10, Microsoft Corporation.

This is the QBE view of the SQL query

Z05_KROE8149_09_SE_EXTB.indd 58 04/07/19 8:23 PM

Online Extension B Advanced SQL B-59

Although a view is a virtual table, it can also be represented as a stored query. Although most DBMSs do not allow queries to be saved in a database, Access does. Access allows us to run queries against tables or against saved queries. This gives us a way to implement a Microsoft Access equivalent of an SQL view: We simply save the SELECT query that would be used to create the SQL view and use it as we would an SQL view in other queries.

We’ll use a view to solve a very real problem in the WMCRM database: the fact that customer’s phone numbers are stored in a separate table (PHONE_NUMBER) from the rest of the customer data (which is in CUSTOMER). We created the PHONE_NUMBER table in Chapter 2’s section of “Working with Microsoft Access” to resolve a multivalue, multicolumn problem that existed in the original CUSTOMER table.

Now, having fixed the multivalue, multicolumn problem, we are faced with the prob- lem of recombining the data in the two tables when a user wants to see the customer’s phone data together with other customer data at the sametime. Of course, this is easy to do with an SQL query if you know SQL:

/* *** SQL-Query-WA-ExtB-03 *** */

SELECT FirstName, LastName, EmailAddress,

PhoneNumber AS CustomerPhoneNumber,

PhoneType

FROM CUSTOMER INNER JOIN PHONE_NUMBER

ON CUSTOMER.CustomerID = PHONE_NUMBER.CustomerID

ORDER BY LastName, FirstName;

SQL-Query-WA-ExtB-03 produces the desired result—note that there are multiple rows in the table for customers with more than one phone number:

WORKING WITH SQL VIEWS IN MICROSOFT ACCESS

However, this is not an SQL query that we would want a typical user to construct. One of the uses of SQL views is to “hide complicated SQL syntax” from users, and this situation is a great place to use an SQL view for just that purpose.

Here is an SQL CREATE VIEW statement that would be used to list this customer data from the WMCRM database if we were creating a standard SQL view (note that we have removed the ORDER BY clause):

/* *** SQL-CREATE-VIEW-WA-ExtB-01 *** */

CREATE VIEW CustomerPhoneView AS

SELECT FirstName, LastName, EmailAddress,

PhoneNumber AS CustomerPhoneNumber,

PhoneType

Access 2019, Windows 10, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 59 04/07/19 8:23 PM

B-60 Online Extension B Advanced SQL

FROM CUSTOMER INNER JOIN PHONE_NUMBER

ON CUSTOMER.CustomerID = PHONE_NUMBER.CustomerID;

Since we cannot create SQL Views directly as SQL Views in Access, we instead create a query—using either Access SQL or QBE—based on the SELECT portion of this statement. Note that because this is just another query as far as Microsoft Access 2019 is concerned, we can and do include the ORDER BY clause! Note that this query is identical to SQL- Query-WA-ExtB-03.

/* *** SQL-Query-WA-ExtB-04 *** */

SELECT FirstName, LastName, EmailAddress,

PhoneNumber AS CustomerPhoneNumber,

PhoneType

FROM CUSTOMER INNER JOIN PHONE_NUMBER

ON CUSTOMER.CustomerID = PHONE_NUMBER.CustomerID

ORDER BY LastName, FirstName;

After we create the query, we save it using a query name that indicates that this query is intended to be used as an SQL view. We can use the naming convention of putting the word view at the beginning of any such query name. Thus, we can name this query viewCustomerPhone.

Although we can include the ORDER BY clause, it is a little tricky because Microsoft Access QBE sorts in the left-to-right order of the columns as listed. In our case, if we cre- ate the query directly in QBE and request sorting on the first two columns (FirstName and LastName), it would sort by FirstName and then LastName instead of LastName and then First Name. Fortunately, there is a way to get around this in QBE that we will demonstrate (this problem does not occur if we just use an SQL statement).

Creating a Microsoft Access Query as a View Equivalent

1. Click the Create command tab to display the Create command groups. 2. Click the Query Design button. 3. The Query1 tabbed document window is displayed in Design view, along with the Show

Table dialog box. 4. Using either the SQL or QBE technique of creating queries, as described in Chapter 3’s

section of “Working with Microsoft Access,” create the query, and then click on Design View if needed to complete the query in Design View:

SELECT FirstName, LastName, EmailAddress,

PhoneNumber AS CustomerPhoneNumber,

PhoneType

FROM CUSTOMER INNER JOIN PHONE_NUMBER

ON CUSTOMER.CustomerID = PHONE_NUMBER.CustomerID

ORDER BY LastName, FirstName;

• NOTE: As shown in Figure WA-B-7, to create the QBE equivalent of EmailAddress AS CustomerEmailAddress, enter the alias name followed by a colon [:] and then followed by the column name that is being aliased.

• NOTE: As shown in Figure WA-B-7, to create the correct sort order on QBE, include another instance of FirstName, and set the sort order here. Then set this column to not appear in the query results by unchecking the Show checkbox.

5. To save the query, click the Save button on the Quick Access Toolbar. The Save As dialog box appears.

Z05_KROE8149_09_SE_EXTB.indd 60 04/07/19 8:23 PM

Online Extension B Advanced SQL B-61

6. Type in the query name viewCustomerPhone, and then click the OK button. The query is saved, the document window is renamed with the query name, and a newly created viewCustomerPhone query object appears in the Queries section of the Navigation Pane, as shown in Figure WA-B-8. Note that Figure WA-B-8 shows the query created in Access QBE.

7. Close the viewCustomerPhone window by clicking the document window’s Close button. 8. If Access displays a dialog box that asks whether you want to save changes to the design of

query viewCustomerPhone, click the Yes button.

Now we can use the viewCustomerPhone query just as we would any other SQL view (or Access saved query). For example, we can implement the following SQL statement:

/* *** SQL-Query-WA-ExtB-05 *** */

SELECT FirstName, LastName, EmailAddress, CustomerPhoneNuber, PhoneType

FROM viewCustomerPhone;

We’ll use Access QBE in this example.

Using an Access Query in Another Access Query

1. Click the Create command tab to display the Create command groups. 2. Click the Query Design button. 3. The Query1 tabbed document window is displayed in Design view, along with the Show

Table dialog box.

FIGURE WA-B-8

The viewCustomerPhone Query in the Queries Pane

Access 2019, Windows 10, Microsoft Corporation.

The query window is now named viewCustomerPhone

The viewCustomerPhone query object

Note the use of an additional FirstName column for sorting

Z05_KROE8149_09_SE_EXTB.indd 61 04/07/19 8:23 PM

B-62 Online Extension B Advanced SQL

4. In the Show Table dialog box, click the Queries tab to select it. The list of all saved queries appears, as shown in Figure WA-B-9.

5. Click viewCustomerPhone to select the viewCustomerPhone query. Click the Add button to add the viewCustomerPhone query to the new query.

6. Click the Close button to close the Show Table dialog box. 7. From the viewCustomerPhone query, click and drag the LastName, FirstName,

EmailAddress, CustomerPhoneNumber, and PhoneType column names to the first five field columns in the lower pane.

8. We do not need to set any Sort settings because they are included in viewCustomerPhone. The completed QBE query looks as shown in Figure WA-B-10. If necessary, resize the table object and the Field columns so that complete labels are displayed.

9. Save the query as QBE-Query-WA-ExtB-01. 10. Click the Run button on the Design command tab. The query results appear as shown in

Figure WA-B-11. 11. Close the QBE-Query-WA-ExtB-01 query window.

Now we can use the equivalent of SQL views in Microsoft Access by using one query as the source for additional queries. That completes the work on SQL views that we will do in this section of “Working with Microsoft Access.”

WORKING WITH SQL/PSM IN MICROSOFT ACCESS Microsoft Access 2019 does not implement SQL/PSM as such. Corresponding capabilities can be found in Microsoft Access Visual Basic for Applications (VBA), but that topic is beyond the scope of this book.

FIGURE WA-B-9

Queries Displayed in the Show Table Dialog Box

Access 2019, Windows 10, Microsoft Corporation.

The Queries tab in the Show Table dialog box

The viewCustomerPhone query object

The Add button

The Show Table dialog box

Z05_KROE8149_09_SE_EXTB.indd 62 04/07/19 8:23 PM

Online Extension B Advanced SQL B-63

IMPORTING MICROSOFT EXCEL DATA INTO MICROSOFT ACCESS 2019 To illustrate importing Microsoft Excel data into Microsoft Access 2019, we’ll need a Microsoft Excel 2019 worksheet. Fortunately, the sales force at Wallingford Motors has been keeping details about Gaia model specifications in just such a worksheet, as shown in Figure WA-B-12.

However, this worksheet is problematic because it contains more than just the column names and data we will want to import. Therefore, we create an edited version as shown in Figure WA-B-13. Now we need to import this data into Microsoft Access 2019.

FIGURE WA-B-10

The Completed QBE Query

Access 2019, Windows 10, Microsoft Corporation.

Object and Field column boundaries can be adjusted as necessary to display complete titles and names

The viewCustomerPhone table object

FIGURE WA-B-11

The Completed Query Result

Access 2019, Windows 10, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 63 04/07/19 8:23 PM

B-64 Online Extension B Advanced SQL

Importing Microsoft Excel Data into a Microsoft Access Table

1. Click the EXTERNAL DATA command tab to display the EXTERNAL DATA command groups.

2. As shown in Figure WA-B-14(a), click the New Data Source drop-down list button to display the New Data Source drop-down list. In that list, click the From File list button, and then click the Excel option button.

3. As shown in Figure WA-B-14(b), the Get External Data - Excel Spreadsheet dialog box is displayed. Click the Browse button, and browse to the DBC-e09-WMCRM-2019-Gaia- Specifications.xlsx Microsoft Excel 2019 workbook.

FIGURE WA-B-12

The Wallingford Motors Gaia Specifications Worksheet

Excel 2019, Windows 10, Microsoft Corporation.

FIGURE WA-B-13

The Revised Wallingford Motors Gaia Specifications Worksheet

Excel 2019, Windows 10, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 64 04/07/19 8:23 PM

Online Extension B Advanced SQL B-65

FIGURE WA-B-14

Importing Excel Worksheet Data

(a) The External Data – New Data Source Button

The New Data Source drop-down list button

The External Data command tab

The Excel option button

The From File list button

Access 2019, Windows 10, Microsoft Corporation.

(b) The Get External Data – Excel Spreadsheet Dialog Box

The Browse button

The Get External Data – Excel Spreadsheet dialog box

Access 2019, Windows 10, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 65 04/07/19 8:23 PM

B-66 Online Extension B Advanced SQL

4. As shown in Figure WA-B-15, make sure that the Import the source data into a new table in the current data base radio button is selected, and then click the OK button.

5. The Import Spreadsheet Wizard is launched. On the first page, select the SPECIFICATIONS_2019 worksheet as shown in Figure WA-B-16.

6. Click the Next button. 7. On the next page of the Import Spreadsheet Wizard, make sure the First Row Contains

Column Headings checkbox is checked, as shown in Figure WA-B-17. 8. Click the Next button. 9. On the next page of the Import Spreadsheet Wizard, we are given a chance to review col-

umn (called field here) characteristics. For each field, we can set Field Name, DataType,

FIGURE WA-B-15

The Completed Get External Data – Excel Spreadsheet Dialog Box

Access 2019, Windows 10, Microsoft Corporation.

The Get External Data – Excel Spreadsheet dialog box

Select the Import the source data into a new table in the current database radio button

The Excel worksheet (spreadsheet) path and name

The OK button

FIGURE WA-B-16

The Import Spreadsheet Wizard Dialog Box

Access 2019, Windows 10, Microsoft Corporation.

The Import Spreadsheet Wizard dialog box

Select the SPECIFICATIONS_2019 worksheet

The Next button

Z05_KROE8149_09_SE_EXTB.indd 66 04/07/19 8:23 PM

Online Extension B Advanced SQL B-67

and whether to index each column. We do not need to index. ModelNumber, ModelName, and ModelDescription will be Short Text, EstElectricToGasRatio will be Single, and ExtMPG will be Integer. Figure WA-B-18 shows the settings for the EstMPG field.

10. Click the Next button. 11. On the next page we are given a chance to set a primary key. We will choose our own, and

it will be ModelNumber, as shown in Figure WA-B-19. 12. Click the Next button. 13. On the next page we are given a chance to set a table name. The default is the Worksheet

name, and, as shown in Figure WA-B-20, here it is SPECIFICATIONS_2019, which is what we want the table to be named. No changes are required here.

14. Click the Finish button.

FIGURE WA-B-17

The First Row Contains Column Headings Check Box

Access 2019, Windows 10, Microsoft Corporation.

The Import Spreadsheet Wizard dialog box

Check the First Row Contains Column Headings check box

The Next button

FIGURE WA-B-18

The Field Options for the EstMPG Column (Field)

Access 2019, Windows 10, Microsoft Corporation.

The Import Spreadsheet Wizard dialog box

The Field Options for the EstMPG column (field)

The Next button

Z05_KROE8149_09_SE_EXTB.indd 67 04/07/19 8:23 PM

B-68 Online Extension B Advanced SQL

15. The table and data are imported, and we are given a chance to save the import steps. There is no need to do this, so click the Close button to end the Wizard.

16. The imported SPECIFICATIONS_2019 table is shown in Datasheet View in Figure WA-B-21 (column widths have been adjusted to shown the complete column names).

17. Figure WA-B-22 shows the table in Design View. Note that the ModelNumber Short Text Field size is 255. We may want to edit that and other field characteristics. For example, we need to set the NULL/NOT NULL settings on all fields—this was not done during the import.

FIGURE WA-B-19

Setting the Primary Key

Access 2019, Windows 10, Microsoft Corporation.

The Import Spreadsheet Wizard dialog box

The Choose my own primary key radio button

The Next button

Select ModelNumber from the drop-down list as the primary key column

FIGURE WA-B-20

Entering the New Table Name

Access 2019, Windows 10, Microsoft Corporation.

The Import Spreadsheet Wizard dialog box

The Finish button

Type in the new table name SPECIFICATIONS_2019

Z05_KROE8149_09_SE_EXTB.indd 68 04/07/19 8:23 PM

Online Extension B Advanced SQL B-69

FIGURE WA-B-21

The SPECIFICATIONS_2019 Table – Datasheet View

Access 2019, Windows 10, Microsoft Corporation.

The new SPECIFICATIONS_2019 table in Datasheet view

The new SPECIFICATIONS_2019 table object

FIGURE WA-B-22

The SPECIFICATIONS_2019 Table – Design View

Access 2019, Windows 10, Microsoft Corporation.

The new SPECIFICATIONS_2019 table in Design view

The new SPECIFICATIONS_2019 table object

Z05_KROE8149_09_SE_EXTB.indd 69 04/07/19 8:23 PM

B-70 Online Extension B Advanced SQL

Although there is still some editing to do, we have successfully imported a Microsoft Excel 2019 worksheet into Microsoft Access 2019.

Closing the WMCRM Database and Exiting Access

1. To close the WMCRM database and exit Microsoft Access 2019, click the Close button in the upper-right corner of the Microsoft Access window.

SUMMARY

The SQL ALTER statement is used to add and remove columns and constraints. Column data types and constraints can be changed using the ALTER TABLE ALTER COLUMN statement. Constraints can be added or dropped using the ADD CONSTRAINT and DROP CONSTRAINT clauses with the SQL ALTER TABLE statement. Use of this state- ment is easier if the developers have provided their own names for all constraints.

The SQL Merge statement combines the SQL INSERT statement and SQL UPDATE statement into one statement that will both insert and update data depending upon whether specific conditions are met.

Correlated subqueries and SQL EXISTS and NOT EXISTS comparison operators are important tools. They can be used to answer advanced queries. They also are useful for determining whether specified data conditions exist. A correlated subquery appears deceptively similar to a regular subquery. The difference is that a regular subquery can be processed from the bottom up. In a regular subquery, results from the lowest query can be determined and then used to evaluate the upper-level queries. In contrast, in a correlated subquery, the processing is nested; that is, a row from an upper-level query statement is compared with rows in a lower-level query. The key feature of a correlated subquery is that the lower-level SELECT statements use columns from upper-level statements.

The SQL EXISTS and NOT EXISTS keywords create specialized forms of correlated subqueries. When these are used, the upper-level query produces results, depending on the existence or nonexistence of rows in lower-level queries. An EXISTS condition is true if any row in the subquery meets the specified conditions; a NOT EXISTS condition is true only if all rows in the subquery do not meet the specified condition.

It is somewhat ironic that in the steps above we use the Get External Data—Excel Spreadsheet dialog box and the Import Spreadsheet Wizard. The term spreadsheet originated with earliest electronic spreadsheet programs, VisiCalc and Lotus 1-2-3 (for more information, see the Wikipedia articles VisiCalc at https://en.wikipedia.org/wiki/VisiCalc and Lotus 1-2-3 at https://en.wikipedia .org/wiki/Lotus_1-2-3). When Microsoft created Microsoft Excel, it created the term worksheet to dif- ferentiate Excel from other similar products, and Excel has alway been know for workbooks containing worksheets. So, it really makes no sense for Microsoft to be using the term spreadsheet here! Why did they do it? We have no idea.

BTW

Z05_KROE8149_09_SE_EXTB.indd 70 04/07/19 8:23 PM

Online Extension B Advanced SQL B-71

The SQL set operator UNION can be used to combine data from two or more tables provided the tables have an identical table structure. Queries on recursive relationships combine data from one table by treating the table as two virtual tables.

An SQL view is a virtual table that is constructed from other tables and views. An SQL SELECT statement is used as part of an SQL CREATE VIEW statement to define a view. However, in some DBMS systems, view definitions cannot include ORDER BY clauses. Once defined, view names are used in SELECT statements the same way table names are used.

There are several uses for views. Views are used (1) to hide columns or rows, (2) to show the results of computed columns, (3) to hide complicated SQL syntax, and (4) to layer computations and built-in functions.

SQL/PSM is the portion of the SQL standard that provides for storing reusable mod- ules of program code within a database. SQL/PSM specifies that SQL statements will be embedded in user-defined functions, triggers, and stored procedures in a database. It also specifies SQL variables, cursors, control-of-flow statements, and output procedures.

A user-defined function accepts input parameter values from an SQL statement, pro- cesses the parameter values, and returns a result value back to the calling statement. User- defined functions may be written to return a single value based on row values (a scalar- valued function), a table of values based on row values (a table-valued function), or a single value based on grouped column values (an aggregate function).

A trigger is a stored program that is executed by the DBMS whenever a specified event occurs on a specified table or view. Possible triggers are BEFORE, INSTEAD OF, and AF- TER. Each type of trigger can be declared for insert, update, and delete actions, so nine types of triggers are possible. Oracle supports all nine trigger types, SQL Server supports only IN- STEAD OF and AFTER triggers, and MySQL supports the BEFORE and AFTER triggers.

A stored procedure is a program that is stored within the database and compiled when used. Stored procedures can receive input parameters and return results. Unlike triggers, their scope is database-wide. They can be used by any process that has permission to run the stored procedure.

Users often store data in Microsoft Excel worksheets, and this data can often be im- ported into relational databases. The steps to import Microsoft Excel data into Microsoft Access 2019 are covered in this extension, and the steps to import Excel data into MySQL 8.0 are covered in online Extension A, “Working with MySQL.”

KEY TERMS

ADD {COLUMN} clause ADD CONSTRAINT clause ALTER {COLUMN} clause ALTER CONSTRAINT clause bulk SQL INSERT statement complement data DROP COLUMN clause DROP CONSTRAINT clause information intersection Open Database Connectivity

(ODBC) MODIFY COLUMN set spreadsheet

SQL ALTER TABLE statement SQL ALTER VIEW {ViewName}

statement SQL CHECK constraint SQL COLUMN keyword SQL CONSTRAINT keyword SQL correlated subquery SQL CREATE VIEW statement SQL DROP VIEW {ViewName}

statement SQL EXISTS comparison operator SQL LEFT keyword SQL MERGE statement SQL NOT EXISTS comparison

operator SQL outer join

SQL/Persistent Stored Modules (SQL/PSM)

SQL queries SQL RIGHT keyword SQL set operators SQL UNION operator SQL view stored procedure subset trigger union user-defined function Venn diagrams workbook worksheet

Z05_KROE8149_09_SE_EXTB.indd 71 04/07/19 8:23 PM

B-72 Online Extension B Advanced SQL

B.1 Show an SQL statement to add an integer column C1 to the table T2. Assume that C1 is NULL.

B.2 Extend your answer to review question B.1 to add C1 when C1 is to be NOT NULL. B.3 Show an SQL statement to drop the column C1 from table T2. B.4 Assume that tables T1 and T2 have a 1:1 relationship. Assume that T2 has the for-

eign key. Show the SQL statements necessary to move the foreign key to T1. Make up your own names for the primary and foreign keys.

B.5 Explain how the SQL MERGE statement works, and discuss an example different than the ones used in this book of how it could be applied to a database.

B.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 ones used in this book of how outer joins could be applied to a database.

B.7 Write a subquery, other than one in this extension, that is not a correlated subquery. B.8 Explain the following statement: “The processing of correlated subqueries is nested,

whereas that of regular subqueries is not.”

B.9 Write a correlated subquery other than one in this extension. B.10 Explain how the query in your answer to review question B.7 differs from the query

in your answer to review question B.9.

B.11 Explain what is wrong with the correlated subquery SQL-Query-ExtB-06 on page B-24. B.12 Explain the meaning of the SQL EXISTS comparison operator. B.13 Explain how the words any and all pertain to the SQL EXISTS and NOT EXISTS

comparison operators.

B.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.

B.15 What are SQL set operators? What are Venn diagrams? B.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?

B.17 What is an SQL view? What purposes do views serve? B.18 What SQL statements are used to create SQL views? B.19 What is the limitation on SELECT statements used in SQL views? B.20 How are views handled in Microsoft Access?

Use the following tables for your answers to Review Questions B.21 through B.37:

PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)

PET_3 (PetID, PetName, PetType, PetBreed, PetDOB, PetWeight, OwnerID)

These are the same tables that are used in the review questions for Chapter 3, and data for these tables are shown in Figures 3-26 and 3-28. For each SQL statement you write, show the results based on these data. If possible, run the statements you write in the questions that follow in an actual DBMS, as appropriate, to obtain your results.

B.21 Code an SQL statement to create a view named OwnerPhoneView that shows OwnerLastName, OwnerFirstName, and OwnerPhone.

B.22 Code an SQL statement that displays the data in OwnerPhoneView, sorted alpha- betically by OwnerLastName.

REVIEW QUESTIONS

Z05_KROE8149_09_SE_EXTB.indd 72 04/07/19 8:23 PM

Online Extension B Advanced SQL B-73

B.23 Code an SQL statement to create a view named DogBreedView that shows PetID, PetName, PetBreed, and PetDOB for dogs.

B.24 Code an SQL statement that displays the data in DogBreedView, sorted alphabeti- cally by PetName.

B.25 Code an SQL statement to create a view named CatBreedView that shows PetID, PetName, PetBreed, and PetDOB for cats.

B.26 Code an SQL statement that displays the data in CatBreedView, sorted alphabeti- cally by PetName.

B.27 Code an SQL statement to create a view named PetOwnerView that shows PetID, PetName, PetType, OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, and OwnerEmail.

B.28 Code an SQL statement that displays the data in PetOwnerView, sorted alphabeti- cally by OwnerLastName and PetName.

B.29 Code an SQL statement to create a view named OwnerPetView that shows OwnerID, OwnerLastName, OwnerFirstName, PetID, PetName, PetType, PetBreed, and PetDOB.

B.30 Code an SQL statement that displays the data in OwnerPetView, sorted alphabeti- cally by OwnerLastName and PetName.

B.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.

B.32 Code an SQL statement that displays the data in PetCountView, sorted alphabeti- cally by PetType.

B.33 Code an SQL statement to create a view named DogBreedCountView that shows each breed of dog and the number of each breed in the database.

B.34 Code an SQL statement that displays the data in DogBreedCountView, sorted alphabetically by PetBreed.

B.35 Write a user-defined function named FirstNameFirst that concatenates the OwnerLastName and OwnerFirstName into a single value named OwnerName and displays, in order, the OwnerFirstName and OwnerLastName with a single space be- tween them (Hint: Downs and Marsha would be combined to read Marsha Downs).

B.36 Code an SQL statement to create a view named PetOwnerFirstNameFirstView that shows PetID, PetName, PetType, OwnerID, OwnerLastName, and OwnerFirst- Name concatenated using the FirstNameFirst user-defined function and displayed as PetOwnerName, OwnerPhone, and OwnerEmail.

B.37 Code an SQL statement that displays the data in PetOwnerFirstNameFirstView, sorted alphabetically by PetOwnerName and PetName.

B.38 Describe the SQL/PSM component of the SQL standard. What are PL/SQL and T-SQL? What is the MySQL equivalent?

B.39 What is a user-defined function? B.40 What is a trigger? B.41 Name nine possible trigger types. B.42 What are stored procedures? How do they differ from triggers? B.43 Summarize the key advantages of stored procedures.

EXERCISES

If you haven’t created the Art Course database described in Chapter 3, create it now (by completing exercises 3.49 and 3.50). Use the Art Course database to answer exercises B.44 through B.54.

Z05_KROE8149_09_SE_EXTB.indd 73 04/07/19 8:24 PM

B-74 Online Extension B Advanced SQL

FIGURE B-38

WP Database PRODUCTION_ITEM Table Data

B.44 Code an SQL statement to create a view named CourseView that shows unique course numbers listed together with the corresponding course names and fees.

B.45 Code an SQL statement that displays the data in CourseView, sorted by Course- Number.

B.46 Code an SQL statement to create a view named CourseEnrollmentView that shows CourseNumber, Course, CourseDate, CustomerNumber, CustomerLastName, CustomerFirstName, and Phone.

B.47 Code an SQL statement that displays the data in CourseEnrollmentView for the Advanced Pastels course starting 10/01/19. Sort the data alphabetically by Custom- erLastName.

B.48 Code an SQL statement that displays the data in CourseEnrollmentView for the Begin- ning Oils course starting 10/15/19. Sort the data alphabetically by CustomerLastName.

B.49 Code an SQL statement to create a view named CourseFeeOwedView that shows CourseNumber, Course, CourseDate, CustomerNumber, CustomerLastName, CustomerFirstName, Phone, Fee, AmountPaid, and the calculated column (Fee – AmountPaid), renamed as AmountOwed.

B.50 Code an SQL statement that displays the data in CourseFeeOwedView, sorted al- phabetically by CustomerLastName.

B.51 Code an SQL statement that displays the data in CourseFeeOwedView, sorted alpha- betically by CustomerLastName for any customer who still owes money for a course fee.

B.52 Write a user-defined function named FirstNameFirst that concatenates the Custo- merLastName and CustomerFirstName into a single value named CustomerName and displays, in order, the CustomerFirstName, space, and the CustomerLastName (Hint: Johnson and Ariel would be combined to read Ariel Johnson).

B.53 Code an SQL statement to create a view named CourseEnrollmentFirstName FirstView that shows CourseNumber, Course, CourseDate, CustomerNumber, CustomerLastName, and CustomerFirstName concatenated using the FirstName First user-defined function and displayed as CustomerName, and Phone.

B.54 Code an SQL statement that displays the data in CourseEnrollmentFirstNameFirst- View, sorted alphabetically by CustomerName and CourseNumber.

For exercises B.55 through B.59 we will use the WP database as described in Chapter 3 and this extension.

B.55 Insert the data shown in Figure B-38 into the PRODUCTION_ITEM table. Note that if you executed the merge statement in SQL-Merge-ExtB-01 earlier in this extension, then the first two rows will already be there, but you will need to change their QuantityOnHand values to 50.

B.56 Create a new table named CATALOG_SKU_2019 based on the column char- acteristics shown in Figure B-2. Include a referential integrity constraint to the PRODUCTION_ITEM table.

B.57 Populate the CATALOG_SKU_2019 table with the data in Figure B-39.

Z05_KROE8149_09_SE_EXTB.indd 74 04/07/19 8:24 PM

Online Extension B Advanced SQL B-75

Key Terms Microsoft Access equivalent of an SQL view multivalue, multicolumn problem

Exercises In the “Working with Microsoft Access” Exercises sections for Chapters 1, 2, and 3, you cre- ated a database for Wedgewood Pacific (WP) of Seattle, Washington. In this set of exercises, you will use that database, as completed in Chapter 3’s section of “Working with Microsoft Access Exercises,” to create and use Microsoft Access queries as SQL view equivalents.

WA.B.1. Using Access QBE or SQL, create and run view-equivalent queries to complete the questions that follow. Save each query using the query name format viewViewQueryName.

A. Create an Access view–equivalent query named viewComputer that shows Make, Model, SerialNumber, ProcessorType, ProcessorSpeed, MainMemory, and DiskSize.

B. Create an Access view–equivalent query named viewEmployeeComputer that uses viewComputer for part A to show EMPLOYEE. EmployeeNumber, LastName, First- Name, and the data about the computer assigned to that employee, including Make, Model, SerialNumber, ProcessorType, ProcessorSpeed, MainMemory, and DiskSize.

WA.B.2. Use Access QBE to create and run the queries that follow. Save each query using the query name format QBE-Query-WA-ExtB-2-##, where ## is replaced by the letter designa- tor of the question. For example, the first query will be saved as QBE-Query-WA-ExtB-2-A.

A. Create an Access QBE query to display the data in viewComputer, sorted alpha- betically by Make and Model and then numerically by SerialNumber.

B. Create an Access QBE query to display the data in viewEmployeeComputer. Sort the results alphabetically by LastName, FirstName, Make, and Model and then numerically by SerialNumber.

FIGURE B-39

WP Database CATALOG_SKU_2019 Table Data

WORKING WITH MICROSOFT ACCESS

B.58 Create and run an SQL SELECT statement that answers the question: “What prod- ucts were available for sale (by either catalog or Web site) in 2018 and 2019?” Sort your result by SKU.

B.59 Create and run an SQL SELECT statement that answers the question: “What prod- ucts were available for sale (by either catalog or Web site) in 2017, 2018, and 2019?” Sort your result by SKU.

Z05_KROE8149_09_SE_EXTB.indd 75 04/07/19 8:24 PM

B-76 Online Extension B Advanced SQL

WA.B.3. In the text of this extension, we imported Microsoft Excel 2019 worksheet data into the SPECIFICATIONS_2019 table. At this point, the SPECIFICATIONS_2019 table is not linked to any other table(s) in the database. What table(s) should be linked to the SPECIFICATIONS_2019 table? Create the necessary relationship(s). Hint: If there is duplicate data after the tables are linked, delete the appropriate columns from the previously existing tables(s), not from SPECIFICATIONS_2019.

WA.B.4. Figures B-40 and B-41 show two worksheets in a Microsoft Excel 2019 workbook. The WP_CUSTOMER worksheet shows data about some of the customers who buy drones from WP, while the WP_SALE worksheet shows some of the sales made to these customers. We want to import this data into two tables in the Microsoft Access 2019 WP.accdb database.

FIGURE B-40

The WP_CUSTOMER Worksheet

Excel 2019, Windows 10, Microsoft Corporation.

FIGURE B-41

The WP_SALE Worksheet

Excel 2019, Windows 10, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 76 04/07/19 8:24 PM

Online Extension B Advanced SQL B-77

FIGURE B-42

Database Column Characteristics for the WP CUSTOMER Table

A. In Microsoft Excel 2019, create a workbook named DBC-e09-WP-Customers.xlsx. In this workbook, create the WP_CUSTOMER and WP_SALE worksheets shown in Figures B-40 and B-41.

B. Using the table name CUSTOMER_TEMP, import the data from the WP_ CUSTOMER worksheet into the WP database.

C. Figure B-42 shows the column characteristics for the final CUSTOMER table. Create this table, and then populate it with the data in the CUSTOMER_TEMP table as appropriate.

D. The data shown in the WP_SALE worksheet is incomplete and will need to be mod- ified before it can be imported. Make the necessary modificiations, and then import the data in in the WP_SALE worksheet into a table named SALE_DATA_TEMP.

E. In order to properly store sales data in a database, we need to use two tables: SALE and SALE_ITEM. The SALE table records the details of the sale itself, while the SALE_ITEM table records the details of each individual item in the sale. The column characteristics for the SALE and the SALE_ITEM tables are shown in Figure B-43. Create these tables, and then populate them with data from the SALE_DATA_TEMP table and additional data as appropriate.

F. Create the needed relationships between CUSTOMER, SALE, SALE_ITEM, and PRODUCTION_ITEM tables.

Z05_KROE8149_09_SE_EXTB.indd 77 04/07/19 8:24 PM

B-78 Online Extension B Advanced SQL

These questions are based on Chapter 3’s Heather Sweeney Designs case questions. Base your answers to the questions that follow on the HSD database, as described there. If pos- sible, run your SQL statements in an actual DBMS to validate your work.

A. Add a column to the CUSTOMER table named ReferredBy, which will contain data on which customer referred the new customer to the store. The column char- acteristics for the ReferredBy column are shown in Figure B-44. Populate the col- umn with the data shown in Figure B-45.

B. Add a column to the SEMINAR_CUSTOMER table named Attended, which will con- tain data showing whether a customer who registered for a seminar actually attended the seminar. Column characteristics for the Attended column are shown in Figure B-46. Populate the column with the data shown in Figure B-47.

C. How did your steps to add the Attended column differ from your steps to add the Re- ferredBy column? Why was (were) the additional step(s) necessary?

D. Add an SQL CHECK constraint to the SEMINAR_CUSTOMER table to ensure that only the values of Attended or Did not attend are allowed as data in the Attended column.

E. The HSD DBA has realized that the SEMINAR_CUSTOMER table Attended column should be NULL instead of NOT NULL as it was created, because we do not know

HEATHER SWEENEY DESIGNS CASE QUESTIONS

(b) SALE_ITEM Table

FIGURE B-43

Database Column Characteristics for the WP SALE and SALE_ITEM Tables

(a) SALE Table

Z05_KROE8149_09_SE_EXTB.indd 78 04/07/19 8:24 PM

Online Extension B Advanced SQL B-79

whether or not a customer will attend a seminar at the time the customer registers for the seminar. Alter the Attended column to NULL instead of NOT NULL.

F. Write an SQL SELECT statement to create a query on the recursive relationship in the CUSTOMER table that shows each customer’s FirstName (as CustomerFirst- Name) and LastName (as CustomerLastName) followed by the name of the customer who referred him or her to Heather Sweeney Designs using the referring customer’s FirstName (as ReferrerFirstName) and LastName (as ReferrerLastName). Do not in- clude customers who were not referred by another customer.

G. Write an SQL SELECT statement to create a query on the recursive relationship in the CUSTOMER table that shows each customer’s FirstName (as CustomerFirstName) and

FIGURE B-45

Data for the New Column in the HSD CUSTOMER Table

FIGURE B-46

Column Characteristics for the New Column in the HSD SEMINAR_CUSTOMER Table

FIGURE B-44

Column Characteristics for the New Column in the HSD CUSTOMER Table

Z05_KROE8149_09_SE_EXTB.indd 79 04/07/19 8:24 PM

B-80 Online Extension B Advanced SQL

LastName (as CustomerLastName) followed by the name of the customer who referred him or her to Heather Sweeney Designs using the referring customer’s FirstName (as ReferrerFirstName) and LastName (as ReferrerLastName). Do include customers who were not referred by another customer.

H. Write a user-defined function named FirstNameFirst that concatenates the customer’s LastName and FirstName into a single value named CustomerName and displays, in order, the FirstName, a space, and the LastName (Hint: Jacobs and Nancy would be combined to read Nancy Jacobs).

I. Create the following SQL views:

1. Create an SQL view named CustomerSeminarView that shows CUSTOMER . CustomerID, LastName, FirstName, EmailAddress, City, State, ZIP, Seminar- Date, Location, and SeminarTitle.

2. Create an SQL view named CustomerFirstNameFirstSeminarView that shows CUSTOMER.CustomerID, then LastName and FirstName concatenated using the FirstNameFirst user-defined function and displayed as CustomerName, EmailAddress, City, State, ZIP, SeminarDate, Location, and SeminarTitle.

3. Create an SQL view named CustomerProductView that shows CustomerID, LastName, FirstName, EmailAddress, INVOICE.InvoiceNumber, InvoiceDate, PRODUCT.ProductNumber, and ProductDescription.

4. Create an SQL view named CustomerFirstNameFirstProductView that shows CustomerID, then LastName and FirstName concatenated using the FirstNameFirst user-defined function and displayed as CustomerName, EmailAddress, INVOICE.InvoiceNumber, InvoiceDate, PRODUCT. ProductNumber, and ProductDescription.

FIGURE B-47

Data for the New Column in the HSD SEMINAR_CUSTOMER Table

Z05_KROE8149_09_SE_EXTB.indd 80 04/07/19 8:24 PM

Online Extension B Advanced SQL B-81

J. Create and run the following SQL queries:

1. Create an SQL statement to run CustomerSeminarView, with the results sorted alphabetically by State, City, and ZIP (in that order) in descending order.

2. Create an SQL statement to run CustomerFirstNameFirstSeminarView, with the re- sults sorted alphabetically by State, City, and ZIP (in that order) in descending order.

3. Create an SQL statement to run CustomerSeminarView, with the results sorted alphabetically by Location, SeminarDate, and SeminarTitle (in that order) in as- cending order.

4. Create an SQL statement to run CustomerFirstNameFirstSeminarView, with the results sorted alphabetically by Location, SeminarDate, and SeminarTitle (in that order) in ascending order.

5. Create an SQL statement to run CustomerProductView, with the results sorted alphabetically by LastName, FirstName, InvoiceNumber, and ProductNumber in ascending order.

6. Create an SQL statement to run CustomerFirstNameFirstProductView, with the results sorted alphabetically by CustomerName, InvoiceNumber, and Product- Number in ascending order.

K. Heather Sweeney Designs staff keep employees in a Microsoft Excel worksheet, as shown in Figure B-48. Heather now wants to import this data into one or more database tables.

1. Duplicate Figure B-48 in a worksheet (or spreadsheet) in an appropriate tool (such as Microsoft Excel or Apache OpenOffice Calc).

2. Import the data into a temporary table in the HSD database.

3. Create a new table in the HSD database named EMPLOYEE. The column charac- teristics for the HSD EMPLOYEE table are shown in Figure B-49.

4. Populate the EMPLOYEE table as much as you can with the imported data in the temporary table. Note that you may not be able to populate all the columns in the

FIGURE B-48

The Heather Sweeney Designs Employee Worksheet

Excel 2019, Windows 10, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 81 04/07/19 8:24 PM

B-82 Online Extension B Advanced SQL

EMPLOYEE table based on the available data. Hint: Consider using a bulk SQL INSERT statement.

5. If there are any columns that still need data, write and run the SQL statements necessary to finish populating the EMPLOYEE table.

6. Write an SQL SELECT statement to create a query on the recursive relationship in the EMPLOYEE table that shows each employee’s FirstName (as Employee FirstName) and LastName (as EmployeeLastName) followed by that employee’s supervisor’s FirstName (as SupervisorFirstName) and LastName (as Supervisor- LastName). Do include employees who do not have a supervisor.

FIGURE B-49

Column Characteristics for the Columns in the HSD EMPLOYEE Table

FIGURE B-50

Column Characteristics for the New Columns in the GG EMPLOYEE Table

GARDEN GLORY PROJECT QUESTIONS

These questions are based on Chapter 3’s Garden Glory project questions. Base your an- swers to the questions that follow on the Garden Glory database, as described there. Run your SQL statements in an actual DBMS to validate your work.

A. Add a column to the EMPLOYEE table named Supervisor, which will contain data showing who supervises an employee. One employee may supervise more than one other employee. Column characteristics for the GG Supervisor column are shown in Figure B-50. Populate the column with the data shown in Figure B-51.

Z05_KROE8149_09_SE_EXTB.indd 82 04/07/19 8:24 PM

Online Extension B Advanced SQL B-83

B. Garden Glory requires that each employee complete an apprenticeship program. Add a column to the EMPLOYEE table named Apprenticeship, which will contain data showing the apprenticeship status for each employee. Column characteristics for the GG Apprenticeship column are shown in Figure B-50. Populate the column with the data shown in Figure B-51.

C. How did your steps to add the Apprenticeship column differ from your steps to add the Supervisor column? Why was (were) the additional step(s) necessary?

D. Add an SQL CHECK constraint to the EMPLOYEE table to ensure that only the values of Completed, In process, or Not started are allowed as data in the Apprenticeship column.

E. Write an SQL SELECT statement to create a query on the recursive relationship in the EMPLOYEE table that shows each employee’s FirstName (as EmployeeFirstName) and LastName (as EmployeeLastName) followed by that employee’s supervisor’s First- Name (as SupervisorFirstName) and LastName (as SupervisorLastName). Do not in- clude employees who do not have a supervisor.

F. Write an SQL SELECT statement to create a query on the recursive relationship in the EMPLOYEE table that shows each employee’s FirstName (as EmployeeFirstName) and LastName (as EmployeeLastName) followed by that employee’s supervisor’s First- Name (as SupervisorFirstName) and LastName (as SupervisorLastName). Do include employees who do not have a supervisor.

G. Write an SQL SELECT statement to create a correlated subquery to determine if there are any employees who have the same combination of LastName and FirstName. Hint: If no employees meet this condition, the correct query result will be an empty set.

H. Write a user-defined function named FirstNameFirst that concatenates the employee’s LastName and FirstName into a single value named FullName and displays, in order, the FirstName, a space, and the LastName (Hint: Smith and Steve would be combined to read Steve Smith).

I. Create the following SQL views:

1. Create an SQL view named OwnerPropertyView that shows OWNER.OwnerID, OwnerName, PropertyType, PropertyID, PropertyName, Street, City, State, and Zip.

2. Create an SQL view named PropertyServiceView that shows OWNED_PROPERTY .PropertyID, PropertyName, Street, City, State, Zip, ServiceDate, FirstName, LastName, and HoursWorked.

3. Create an SQL view named PropertyServiceFirstNameFirstView that shows OWNED_PROPERTY.PropertyID, PropertyName, Street, City, State, Zip, ServiceDate, then LastName and FirstName concatenated using the FirstNameFirst user-defined function and displayed as EmployeeName, and HoursWorked.

FIGURE B-51

Data for the New Columns in the GG EMPLOYEE Table

Z05_KROE8149_09_SE_EXTB.indd 83 04/07/19 8:24 PM

B-84 Online Extension B Advanced SQL

J. Create (and run) the following SQL queries:

1. Create an SQL statement to run OwnerPropertyView, with the results sorted al- phabetically by OwnerName.

2. Create an SQL statement to run PropertyServiceView, with the results sorted al- phabetically by Zip, State, and City.

3. Create an SQL statement to run PropertyServiceFirstNameFirstView, with the re- sults sorted alphabetically by Zip, State, and City.

K. Garden Glory staff keep a record of tool inventory and who uses those tools in a Mi- crosoft Excel worksheet, as shown in Figure B-52. Garden Glory now wants to import this data into one or more database tables.

1. Duplicate Figure B-52 in a worksheet (or spreadsheet) in an appropriate tool (such as Microsoft Excel or Apache OpenOffice Calc).

2. Import the data into one or more new tables in the GG database. You must determine all table characteristics needed (primary key, foreign keys, data types, etc.).

3. Link this (these) new table(s) as appropriate to one or more existing tables in the GG database. Explain why you chose to make the connection(s) you made.

JAMES RIVER JEWELRY PROJECT QUESTIONS

The James River Jewelry Project Questions for Extension B are based on the work done in the James River Jewelry Project Questions for Chapter 3. Base your answers to the ques- tions that follow on the James River Jewelry database as described there. If possible, run your SQL statements in an actual DBMS to validate your work.

A. Add a column to the CUSTOMER table named ReferredBy, which will contain data on which customer referred the new customer to the store. The column char- acteristics for the ReferredBy column are shown in Figure B-53. Populate the col- umn with the data shown in Figure B-54.

FIGURE B-52

The Garden Glory Tool Inventory Worksheet

Excel 2019, Windows 10, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 84 04/07/19 8:24 PM

Online Extension B Advanced SQL B-85

B. Add a column to the ITEM table named ArtistHasBeenPaid, which will contain data showing whether the artist has been paid for the piece of jewelry James River Jewelry acquired. Column characteristics for the JRJ ArtistHasBeenPaid column are shown in Figure B-55. Populate the column with the data shown in Figure B-56.

C. How did your steps to add the ArtistHasBeenPaid column differ from your steps to add the ReferredBy column? Why was (were) the additional step(s) necessary?

D. Add an SQL CHECK constraint to the ITEM table to ensure that only the values of Waiting for invoice, In process, or Paid are allowed as data in the ArtistHasBeenPaid column.

E. Write an SQL SELECT statement to create a query on the recursive relationship in the CUSTOMER table that shows each customer’s FirstName (as CustomerFirstName) and LastName (as CustomerLastName) followed by the name of the customer who referred him or her to James River Jewelry using the referring customer’s FirstName (as ReferrerFirstName) and LastName (as ReferrerLastName). Do not include customers who were not referred by another customer.

FIGURE B-53

Column Characteristics for the New Column in the JRJ CUSTOMER Table

FIGURE B-54

Data for the New Column in the JRJ CUSTOMER Table

FIGURE B-55

Column Characteristics for the New Column in the JRJ CUSTOMER Table

Z05_KROE8149_09_SE_EXTB.indd 85 04/07/19 8:24 PM

B-86 Online Extension B Advanced SQL

F. Write an SQL SELECT statement to create a query on the recursive relationship in the CUSTOMER table that shows each customer’s FirstName (as CustomerFirstName) and LastName (as CustomerLastName) followed by the name of the customer who referred him or her to James River Jewelry using the referring customer’s FirstName (as ReferrerFirstName) and LastName (as ReferrerLastName). Do include customers who were not referred by another customer.

G. Suppose that James River Jewelry owners are considering changing the primary key of CUSTOMER to (FirstName, LastName). Write a correlated subquery to display any data that indicate that this change is not justifiable. Hint: If no employees meet this condition, the correct query result will be an empty set.

H. Write a user-defined function named FirstNameFirst that concatenates the customer’s LastName and FirstName into a single value named FullName and displays, in order, the FirstName, a space, and the LastName (Hint: Stanley and Elizabeth would be com- bined to read Elizabeth Stanley).

I. Create the following SQL views:

1. Create an SQL view named CustomerPurchaseView that shows CustomerID, LastName, FirstName, InvoiceNumber, InvoiceDate, and PreTaxAmount.

FIGURE B-56

Data for the New Column in the JRJ CUSTOMER Table

Z05_KROE8149_09_SE_EXTB.indd 86 04/07/19 8:24 PM

Online Extension B Advanced SQL B-87

2. Create an SQL view named CustomerFirstNameFirstPurchaseView that shows CustomerID, then LastName and FirstName concatenated using the FirstNam- eFirst user-defined function and displayed as CustomerName, InvoiceNumber, InvoiceDate, and PreTaxAmount.

3. Create an SQL view named PurchaseItemItemView that shows InvoiceNumber, ItemNumber, ArtistLastName, ArtistFirstName, ItemDescription, Cost, and Re- tailPrice.

4. Create an SQL view named PurchaseItemItemFirstNameFirstView that shows InvoiceNumber, ItemNumber, then ArtistLastName and ArtistFirstName con- catenated using the FirstNameFirst user-defined function and displayed as Artist- Name, ItemDescription, Cost, and RetailPrice.

J. Create (and run) the following SQL queries:

1. Create an SQL statement to run CustomerPurchaseView, with the results sorted alphabetically by LastName and FirstName.

2. Create an SQL statement to run CustomerFirstNameFirstPurchaseView, with the results sorted alphabetically by CustomerName.

3. Create an SQL statement to run PurchaseItemItemView, with the results sorted by InvoiceNumber and ItemNumber.

4. Create an SQL statement to run PurchaseItemItemFirstNameFirstView, with the results sorted by InvoiceNumber and ItemNumber.

5. Create an SQL query that uses PurchaseItemItemView to calculate and display, for each invoice, the sum of Cost as TotalItemCost and the sum of RetailPrice as TotalRetailSales. Hint: Use the SQL GROUP BY clause.

K. James River Jewelry occasionally has appraisers determine values for some of their unsold items in an effort to set more realistic prices. They keep appraisal data in a Microsoft Excel worksheet, as shown in Figure B-57, but would like to incorporate it into their database.

1. Duplicate Figure B-57 in a worksheet (or spreadsheet) in an appropriate tool (such as Microsoft Excel or Apache OpenOffice Calc).

FIGURE B-57

The James River Jewelry Appraisals Worksheet

Excel 2019, Windows 10, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 87 04/07/19 8:24 PM

B-88 Online Extension B Advanced SQL

2. Import the data into one or more new tables in the JRJ database. You must deter- mine all table characteristics needed (primary key, foreign keys, data types, etc.).

3. Link this (these) new table(s) as appropriate to any other tables in the JRJ database. Explain why you chose to make the connection(s) that you made.

FIGURE B-58

Column Characteristics for the New Column in the QACS CUSTOMER Table

FIGURE B-59

Data for the New Column in the QACS CUSTOMER Table

THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS

These questions are based on Chapter 3’s Queen Anne Curiosity Shop project questions. Base your answers to the questions that follow on the Queen Anne Curiosity Shop project data- base, as described there. Run your SQL statements in an actual DBMS to validate your work.

A. Add a column to the CUSTOMER table named ReferredBy, which will contain data on which customer referred the new customer to the store. The column char- acteristics for the ReferredBy column are shown in Figure B-58. Populate the col- umn with the data shown in Figure B-59.

B. Add a column to the EMPLOYEE table named Supervisor, which will contain data showing who supervises an employee. One employee may supervise more than one other employee. Column characteristics for the QACS Supervisor column are shown in Figure B-60. Populate the column with the data shown in Figure B-61.

Z05_KROE8149_09_SE_EXTB.indd 88 04/07/19 8:24 PM

Online Extension B Advanced SQL B-89

C. The Queen Anne Curiosity Shop requires that each employee complete a training pro- gram. Add a column to the EMPLOYEE table named Training, which will contain data showing the training status for each employee. Column characteristics for the QACS Training column are shown in Figure B-60. Populate the column with the data shown in Figure B-61.

D. How did your steps to add the Training column differ from your steps to add the Supervisor column? Why was (were) the additional step(s) necessary?

E. Add an SQL CHECK constraint to the EMPLOYEE table to ensure that only the val- ues of Completed, In process, or Not started are allowed as data in the Training column.

F. Write an SQL SELECT statement to create a query on the recursive relationship in the CUSTOMER table that shows each customer’s FirstName (as CustomerFirstName) and LastName (as CustomerLastName) followed by the name of the customer who re- ferred him or her to QACS using the referring customer’s FirstName (as ReferrerFirst- Name) and LastName (as ReferrerLastName). Do not include customers who were not referred by another customer.

G. Write an SQL SELECT statement to create a query on the recursive relationship in the CUSTOMER table that shows each customer’s FirstName (as CustomerFirstName) and LastName (as CustomerLastName) followed by the name of the customer who referred him or her to QACS using the referring customer’s FirstName (as Refereer- FirstName) and LastName (as RefereerLastName). Do include customers who were not referred by another customer.

H. Write an SQL SELECT statement to create a query on the recursive relationship in the EMPLOYEE table that shows each employee’s FirstName (as EmployeeFirstName)

FIGURE B-60

Column Characteristics for the New Columns in the QACS EMPLOYEE Table

FIGURE B-61

Data for the New Columns in the QACS EMPLOYEE Table

Z05_KROE8149_09_SE_EXTB.indd 89 04/07/19 8:24 PM

B-90 Online Extension B Advanced SQL

and LastName (as EmployeeLastName) followed by that employee’s supervisor’s First- Name (as SupervisorFirstName) and LastName (as SupervisorLastName). Do not in- clude employees who do not have a supervisor.

I. Write an SQL SELECT statement to create a query on the recursive relationship in the EMPLOYEE table that shows each employee’s FirstName (as EmployeeFirstName) and LastName (as EmployeeLastName) followed by that employee’s supervisor’s First- Name (as SupervisorFirstName) and LastName (as SupervisorLastName). Do include employees who do not have a supervisor.

J. Suppose that the Queen Anne Curiosity Shop owners are considering changing the primary key of CUSTOMER to (FirstName, LastName). Write a correlated subquery to display any data that indicate that this change is not justifiable. Hint: If no employees meet this condition, the correct query result will be an empty set.

K. Write a user-defined function named FirstNameFirst that concatenates the employee’s LastName and FirstName into a single value named FullName and displays, in order, the FirstName, a space, and the LastName (Hint: Smith and Steve would be combined to read Steve Smith).

L. Create the following SQL view statements:

1. Create an SQL view named BasicCustomerView that shows each customer’s CustomerID, LastName, FirstName, Phone, and EmailAddress.

2. Create an SQL view named BasicCustomerFirstNameFirstView that shows each customer’s CustomerID, then LastName and FirstName concatenated using the FirstNameFirst user-defined function and displayed as CustomerName, Phone, and EmailAddress.

3. Create an SQL view named SaleItemItemView that shows SaleID, SaleItemID, SALE_ITEM.ItemID, SaleDate, ItemDescription, ItemCost, ITEM.ItemPrice as ListItemPrice, and SALE_ITEM.ItemPrice as ActualItemPrice.

M. Create (and run) the following SQL queries:

1. Create an SQL statement to run BasicCustomerView, with the results sorted alpha- betically by LastName and FirstName.

2. Create an SQL statement to run BasicCustomerFirstNameFirstView, with the re- sults sorted alphabetically by CustomerName.

3. Create an SQL statement to run SaleItemItemView, with the results sorted by SaleID and SaleItemID.

4. Create an SQL query that uses SaleItemItemView to calculate and display, for each invoice, the sum of SALE_ITEM.ItemPrice (which is relabeled as ActualItem- Price) as TotalPretaxRetailSales. Hint: Use the SQL GROUP BY clause.

N. The Queen Anne Curiosity Shop owners and staff have decided to sell standardized items that can be stocked and reordered as necessary. So far, they have kept their re- cords for these items in a Microsoft Excel worksheet, as shown in Figure B-62. They have decided to integrate this data into the QACS database, and they want to import this data into one or more database tables.

1. Duplicate Figure B-62 in a worksheet (or spreadsheet) in an appropriate tool (such as Microsoft Excel or Apache OpenOffice Calc).

2. Import the data into one or more new tables in the QACS database. You must de- termine all table characteristics needed (primary key, foreign keys, data types, etc.).

3. Link this (these) new table(s) as appropriate to one or more existing tables in the QACS database. Explain why you chose to make the connection(s) that you made.

Z05_KROE8149_09_SE_EXTB.indd 90 04/07/19 8:24 PM

Online Extension B Advanced SQL B-91

FIGURE B-62

The Queen Anne Curiosity Shop Standard Merchandise Inventory Worksheet

Excel 2019, Windows 10, Microsoft Corporation.

Z05_KROE8149_09_SE_EXTB.indd 91 04/07/19 8:24 PM