Color profile: Generic CMYK printer profile
Composite Default screen

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:i

SQL
A Beginner’s Guide
Second Edition

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:10 PM


Color profile: Generic CMYK printer profile
Composite Default screen

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:ii

This page intentionally left blank

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:10 PM


Color profile: Generic CMYK printer profile
Composite Default screen

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:iii

SQL
A Beginner’s Guide
Second Edition
Robert Sheldon

McGraw-Hill/Osborne
New York Chicago San Francisco
Lisbon London Madrid Mexico City
Milan New Delhi San Juan
Seoul Singapore Sydney Toronto

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:10 PM


Color profile: Generic CMYK printer profile
Composite Default screen

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:iv

McGraw-Hill/Osborne
2100 Powell Street, 10th Floor
Emeryville, California 94608
U.S.A.
To arrange bulk purchase discounts for sales promotions, premiums, or fund-raisers, please
contact McGraw-Hill/Osborne at the above address. For information on translations or

book distributors outside the U.S.A., please see the International Contact Information page
immediately following the index of this book.

SQL: A Beginner’s Guide, Second Edition
Copyright © 2003 by The McGraw-Hill Companies. All rights reserved. Printed in the
United States of America. Except as permitted under the Copyright Act of 1976, no part of
this publication may be reproduced or distributed in any form or by any means, or stored in
a database or retrieval system, without the prior written permission of the publisher, with
the exception that the program listings may be entered, stored, and executed in a computer
system, but they may not be reproduced for publication.
1234567890 FGR FGR 019876543
ISBN 0-07-222885-7
Publisher Brandon A. Nordin
Vice President & Associate Publisher Scott Rogers
Acquisitions Editor Lisa McClain
Senior Project Editor LeeAnn Pickrell
Acquisitions Coordinator Athena Honore
Technical Editor Greg Guntle
Copy Editor Margaret Berson
Proofreader Marion Selig
Indexer James Minkin
Computer Designers Carie Abrew, Tara A. Davis
Illustrators Kathleen Fay Edwards, Melinda Moore Lytle, Michael Mueller, Lyssa Wald
Series Design Jean Butterfield
Series Cover Design Sarah F. Hinks

This book was composed with Corel VENTURA™ Publisher.
Information has been obtained by McGraw-Hill/Osborne from sources believed to be reliable. However, because of the possibility
of human or mechanical error by our sources, McGraw-Hill/Osborne, or others, McGraw-Hill/Osborne does not guarantee the
accuracy, adequacy, or completeness of any information and is not responsible for any errors or omissions or the results obtained

from use of such information.

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:10 PM


Color profile: Generic CMYK printer profile
Composite Default screen

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:v

About the Author
Robert Sheldon has worked as a consultant and technical writer
for a number of years. As a consultant, he has managed the
development and maintenance of web-based and client-server
applications and the databases that supported those applications.
In addition, he has designed and implemented various Access
and SQL Server databases and has used SQL to build databases,
create and modify database objects, query and modify data, and
troubleshoot system- and data-related problems. Robert has also
written or cowritten eight books on various network and server
technologies, one of which received a Certificate of Merit
from the Puget Sound Chapter of the Society for Technical
Communication. In addition, two of the books that Robert
has written focus exclusively on SQL Server design and
implementation. Robert has also written and edited a variety
of other documentation related to SQL databases and other
computer technologies. In addition, his writing includes material
outside the computer industry—everything from news articles

to ad copy to legal documentation—and he has received two
awards from the Colorado Press Association.

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:10 PM


Color profile: Generic CMYK printer profile
Composite Default screen

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:ii

This page intentionally left blank

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:10 PM


Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / 1
Blind Folio FM:vii

Color profile: Generic CMYK printer profile
Composite Default screen

Contents at a Glance
PART I

SQL Databases
1 Introduction to Relational Databases and SQL . . . . . . . . . . . . . . . . . . . . . . . . . .


3

2 Working with the SQL Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

27

3 Creating and Altering Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

47

4 Enforcing Data Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

73

5 Creating SQL Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
6 Managing Database Security

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125

PART II

Data Access and Modification
7 Querying SQL Data

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149

8 Modifying SQL Data

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181


9 Using Predicates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
10 Working with Functions and Value Expressions . . . . . . . . . . . . . . . . . . . . . . . . 233

vii

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:10 PM


Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:viii

Color profile: Generic CMYK printer profile
Composite Default screen

viii

SQL: A Beginner’s Guide

11 Accessing Multiple Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
12 Using Subqueries to Access and Modify Data . . . . . . . . . . . . . . . . . . . . . . . . . . 285
PART III

Advanced Data Access
13 Creating SQL-Invoked Routines

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307

14 Creating SQL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337

15 Using SQL Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361
16 Managing SQL Transactions

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389

17 Accessing SQL Data from Your Host Program . . . . . . . . . . . . . . . . . . . . . . . . . 417
PART IV

Appendixes
A Answers to Mastery Checks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451
B SQL:1999 Keywords

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489

C SQL Code Used in the Book’s Projects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:10 PM


Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / 1
Blind Folio FM:ix

Color profile: Generic CMYK printer profile
Composite Default screen

Contents
ACKNOWLEDGMENTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
PART I


SQL Databases
1 Introduction to Relational Databases and SQL . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 1.1 Understand Relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . .
The Relational Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 1-1 Normalizing Data and Identifying Relationships . . . . . . . . . . . . . . . . . .
Critical Skill 1.2 Learn about SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
The SQL Evolution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Types of SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Types of Execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 1.3 Use a Relational Database Management System . . . . . . . . . . . . . . . .
SQL Standard Versus Product Implementations . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 1-2 Connecting to a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 1 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2 Working with the SQL Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 2.1 Understand the SQL Environment . . . . . . . . . . . . . . . . . . . . . . . . . . .

ix

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:10 PM

3
4
5
12
14
14
16
17

19
20
22
25

27
28


Color profile: Generic CMYK printer profile
Composite Default screen

x

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:x

SQL: A Beginner’s Guide

Critical Skill 2.2 Understand SQL Catalogs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Schema Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Then What Is a Database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 2.3 Name Objects in an SQL Environment . . . . . . . . . . . . . . . . . . . . . . .
Qualified Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 2.4 Create a Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 2.5 Create a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 2-1 Creating a Database and a Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 2 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .


30
31
32
35
37
38
39
42
43
44

3 Creating and Altering Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 3.1 Create SQL Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 3.2 Specify Column Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
String Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Numeric Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Datetime Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Interval Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Boolean Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using SQL Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 3.3 Create User-Defined Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 3.4 Specify Column Default Values . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 3-1 Creating SQL Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 3.5 Alter SQL Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 3.6 Delete SQL Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 3-2 Altering and Deleting SQL Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 3 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

47
48

52
53
55
56
57
59
61
62
63
65
67
68
70
71

4 Enforcing Data Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Critical Skill 4.1 Understand Integrity Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Critical Skill 4.2 Use NOT NULL Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Critical Skill 4.3 Add UNIQUE Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Critical Skill 4.4 Add PRIMARY KEY Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Critical Skill 4.5 Add FOREIGN KEY Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
The MATCH Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
The <referential triggered action> Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Project 4-1 Adding NOT NULL, Unique, and Referential Constraints . . . . . . . . . . 92
Critical Skill 4.6 Define CHECK Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
Defining Assertions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Creating Domains and Domain Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Project 4-2 Adding a CHECK Constraint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Module 4 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102


P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:10 PM


Color profile: Generic CMYK printer profile
Composite Default screen

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:xi

Contents

5 Creating SQL Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 5.1 Add Views to the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Defining SQL Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 5.2 Create Updateable Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using the WITH CHECK OPTION Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 5.3 Drop Views from the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 5-1 Adding Views to Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 5 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

105

6 Managing Database Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 6.1 Understand the SQL Security Model . . . . . . . . . . . . . . . . . . . . . . . . .
SQL Sessions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Accessing Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 6.2 Create and Delete Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 6.3 Grant and Revoke Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Revoking Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Critical Skill 6.4 Grant and Revoke Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Revoking Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 6-1 Managing Roles and Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 6 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

125

106
112
116
119
121
122
123

126
127
130
133
134
138
141
142
143
145

PART II

Data Access and Modification
7 Querying SQL Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Critical Skill 7.1 Use a SELECT Statement to Retrieve Data . . . . . . . . . . . . . . . . . . . .
The SELECT Clause and FROM Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 7.2 Use the WHERE Clause to Define Search Conditions . . . . . . . . . . .
Defining the WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 7.3 Use the GROUP BY Clause to Group Query Results . . . . . . . . . . . .
Critical Skill 7.4 Use the HAVING Clause to Specify Group Search Conditions . . . .
Critical Skill 7.5 Use the ORDER BY Clause to Sort Query Results . . . . . . . . . . . . . .
Project 7-1 Querying the Inventory Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 7 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

149

8 Modifying SQL Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 8.1 Insert SQL Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Inserting Values from a SELECT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 8.2 Update SQL Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Updating Values from a SELECT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . .

181

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:11 PM

150
151
157
161
165
170
172

175
178

182
186
188
191

xi


Color profile: Generic CMYK printer profile
Composite Default screen

xii

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:xii

SQL: A Beginner’s Guide

Critical Skill 8.3 Delete SQL Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Project 8-1 Modifying SQL Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Module 8 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
9 Using Predicates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 9.1 Compare SQL Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using the BETWEEN Predicate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 9.2 Return Null Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 9.3 Return Similar Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 9-1 Using Predicates in SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Critical Skill 9.4 Reference Additional Sources of Data . . . . . . . . . . . . . . . . . . . . . . . .
Using the IN Predicate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using the EXISTS Predicate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 9.5 Quantify Comparison Predicates . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using the SOME and ANY Predicates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using the ALL Predicate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 9-2 Using Subqueries in Predicates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 9 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

201

10 Working with Functions and Value Expressions . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 10.1 Use Set Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using the COUNT Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using the MAX and MIN Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using the SUM Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using the AVG Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 10.2 Use Value Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Working with String Value Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Working with Datetime Value Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 10.3 Use Value Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Working with Numeric Value Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using the CASE Value Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using the CAST Value Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 10.4 Use Special Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 10-1 Using Functions and Value Expressions . . . . . . . . . . . . . . . . . . . . . . . .
Module 10 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

233


11 Accessing Multiple Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 11.1 Perform Basic Join Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using Correlation Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Creating Joins with More than Two Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Creating the Cross Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Creating the Self-Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

261

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:11 PM

202
206
208
211
215
217
218
221
224
225
227
228
230

234
234
236
239

239
241
241
244
246
246
249
252
254
255
258

262
265
266
267
268


Color profile: Generic CMYK printer profile
Composite Default screen

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:xiii

Contents

Critical Skill 11.2 Join Tables with Shared Column Names . . . . . . . . . . . . . . . . . . . . .
Creating the Natural Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Creating the Named Column Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Critical Skill 11.3 Use the Condition Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Creating the Inner Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Creating the Outer Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 11.4 Perform Union Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 11-1 Querying Multiple Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 11 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

270
271
272
272
273
275
279
281
283

12 Using Subqueries to Access and Modify Data . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 12.1 Create Subqueries That Return Multiple Rows . . . . . . . . . . . . . . . .
Using the IN Predicate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using the EXISTS Predicate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using Quantified Comparison Predicates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 12.2 Create Subqueries That Return One Value . . . . . . . . . . . . . . . . . . .
Critical Skill 12.3 Work with Correlated Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 12.4 Use Nested Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 12.5 Use Subqueries to Modify Data . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using Subqueries to Insert Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using Subqueries to Update Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using Subqueries to Delete Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 12-1 Working with Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Module 12 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

285
286
287
288
289
291
293
294
296
297
298
299
299
303

PART III

Advanced Data Access
13 Creating SQL-Invoked Routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 13.1 Understand SQL-Invoked Routines . . . . . . . . . . . . . . . . . . . . . . . . .
SQL-Invoked Procedures and Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Working with the Basic Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 13.2 Create SQL-Invoked Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . .
Invoking SQL-Invoked Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 13.3 Add Input Parameters to Your Procedures . . . . . . . . . . . . . . . . . . .
Using Procedures to Modify Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 13.4 Add Local Variables to Your Procedures . . . . . . . . . . . . . . . . . . . .
Critical Skill 13.5 Working with Control Statements . . . . . . . . . . . . . . . . . . . . . . . . . .

Create Compound Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Create Conditional Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Create Looping Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 13-1 Creating SQL-Invoked Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . .

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:11 PM

307
308
309
310
312
313
315
318
319
321
321
322
324
326

xiii


Color profile: Generic CMYK printer profile
Composite Default screen

xiv


Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:xiv

SQL: A Beginner’s Guide

Critical Skill 13.6 Add Output Parameters to Your Procedures . . . . . . . . . . . . . . . . . . .
Critical Skill 13.7 Create SQL-Invoked Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 13-2 Creating SQL-Invoked Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 13 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

328
330
332
333

14 Creating SQL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 14.1 Understand SQL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Trigger Execution Context . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 14.2 Create SQL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Referencing Old and New Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Dropping SQL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 14.3 Create Insert Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 14.4 Create Update Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 14.5 Create Delete Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 14-1 Creating SQL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 14 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

337


15 Using SQL Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 15.1 Understand SQL Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Declaring and Opening SQL Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 15.2 Declare a Cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Working with Optional Syntax Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Creating a Cursor Declaration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 15.3 Open and Close a Cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 15.4 Retrieve Data from a Cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 15.5 Use Positioned UPDATE and DELETE Statements . . . . . . . . . . . .
Using the Positioned UPDATE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using the Positioned DELETE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 15-1 Working with SQL Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 15 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

361

16 Managing SQL Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 16.1 Understand SQL Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 16.2 Set Transaction Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Specifying an Isolation Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Specifying a Diagnostics Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Creating a SET TRANSACTION Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 16.3 Start a Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 16.4 Set Constraint Deferrability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 16.5 Create Savepoints in a Transaction . . . . . . . . . . . . . . . . . . . . . . . . .
Releasing a Savepoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

389

P:\010Comp\Begin8\885-7\fm.vp

Tuesday, April 08, 2003 12:33:11 PM

338
339
341
342
344
344
347
352
354
357

362
363
366
366
370
374
374
380
380
382
383
386

390
393
394
399

400
401
402
406
408


Color profile: Generic CMYK printer profile
Composite Default screen

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:xv

Contents

Critical Skill 16.6 Terminate a Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Committing a Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Rolling Back a Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 16-1 Working with Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 16 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

408
409
410
411
414

17 Accessing SQL Data from Your Host Program . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 17.1 Invoke SQL Directly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 17.2 Embed SQL Statements in Your Program . . . . . . . . . . . . . . . . . . . .

Creating an Embedded SQL Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Using Host Variables in Your SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . .
Retrieving SQL Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 17-1 Embedding SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 17.3 Create SQL Client Modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Defining SQL Client Modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Critical Skill 17.4 Use an SQL Call-Level Interface . . . . . . . . . . . . . . . . . . . . . . . . . . .
Allocating Handles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Executing SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Working with Host Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Retrieving SQL Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Project 17-2 Using the SQL Call-Level Interface . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 17 Mastery Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

417
418
419
421
423
424
427
429
432
433
435
437
440
441
442

443
446

PART IV

Appendixes
A Answers to Mastery Checks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 1: Introduction to Relational Databases and SQL . . . . . . . . . . . . . . . . . . . . . . .
Module 2: Working with the SQL Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 3: Creating and Altering Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 4: Enforcing Data Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 5: Creating SQL Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 6: Managing Database Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 7: Querying SQL Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 8: Modifying SQL Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 9: Using Predicates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 10: Working with Functions and Value Expressions . . . . . . . . . . . . . . . . . . . . .
Module 11: Accessing Multiple Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 12: Using Subqueries to Access and Modify Data . . . . . . . . . . . . . . . . . . . . . . .
Module 13: Creating SQL-Invoked Routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 14: Creating SQL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Module 15: Using SQL Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:11 PM

451
452
453
455

458
460
462
464
466
469
471
473
475
476
478
481

xv


Color profile: Generic CMYK printer profile
Composite Default screen

xvi

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:xvi

SQL: A Beginner’s Guide

Module 16: Managing SQL Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483
Module 17: Accessing SQL Data from Your Host Program . . . . . . . . . . . . . . . . . . . . . . 486
B SQL:1999 Keywords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489
SQL Reserved Keywords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 490

SQL Nonreserved Keywords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
C SQL Code Used in the Book’s Projects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495
SQL Code by Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 496
The Inventory Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509

Index

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 515

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:11 PM


Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / 1
Blind Folio FM:xvii

Color profile: Generic CMYK printer profile
Composite Default screen

Acknowledgments
A

s with any publication, too many people were involved in the development of
SQL: A Beginner’s Guide, Second Edition to be able to name them all, but I would
at least like to acknowledge the editors and staff at McGraw-Hill/Osborne whom I had the
pleasure of working with directly. My special thanks go to Lisa McClain, the acquisitions
editor; Athena Honore, the acquisitions coordinator; and LeeAnn Pickrell, the project editor.
Together the three of them kept this project moving forward smoothly and professionally and
provided me with an immeasurable degree of help along the way. Along with these three, I
want to acknowledge Margaret Berson, the copy editor, and all the other editors, proofreaders,

indexers, designers, illustrators, and other participants whose names I never learned. In addition,
I want to acknowledge Greg Guntle, the technical editor, for his attention to detail, his grasp of
the subject matter, and his invaluable input into the book’s content. I also want to thank my
agent, Danielle Jatlow at Waterside Productions, Inc., for making this project happen and for
her continual support in all my efforts. Finally, I want to thank my friend, mentor, and sometimes
co-author Ethan Wilansky for his encouragement, support, and answers to my endless questions
throughout the course of this project and other projects that I have tackled throughout the years.

xvii

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:11 PM


Color profile: Generic CMYK printer profile
Composite Default screen

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:ii

This page intentionally left blank

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:10 PM


Color profile: Generic CMYK printer profile
Composite Default screen

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter

Blind Folio FM:xix

Introduction
R

elational databases have become a common mainstay for systems that provide data storage
for various types of applications. Programming languages such as C or COBOL or scripting
languages such as VBScript or JavaScript must often access a data source in order to retrieve or
modify data through the application. Many of these data sources are managed by a relational
database management system (RDBMS) that relies on the Structured Query Language (SQL) to
create and alter database objects, add data to and retrieve data from the database, and modify data
that has been added to that database.
SQL is the most widely implemented language for relational databases. SQL not only
allows you to manage data within the database, but also manage the database itself. By using
SQL statements, you can access an SQL database directly by using an interactive client
application or through an application programming language or scripting language. Regardless
of which method you use to access a data source, you should have a foundation in how to write
SQL statements that allows you to access relational data. SQL: A Beginner’s Guide, Second
Edition provides you with such a foundation. It describes the types of statements that SQL
supports and explains how they’re used to manage databases and their data. By working
through the modules in this book, you’ll build a strong foundation in basic SQL and gain a
comprehensive understanding of how to use SQL to access data in your relational database.

xix

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:12 PM


Color profile: Generic CMYK printer profile

Composite Default screen

xx

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:xx

SQL: A Beginner’s Guide

Who Should Read This Book
SQL: A Beginner’s Guide is recommended for anyone trying to build a foundation in SQL
programming. The book is designed specifically for those who are new or relatively new to SQL;
however, those of you who need a refresher in SQL will also find this book beneficial. Whether
you’re an experienced programmer, have had some web development experience, are a database
administrator, or are new to programming and databases, SQL: A Beginner’s Guide provides a
strong foundation that will be useful to any of you wishing to learn more about SQL. In fact, any
of the following individuals will find this book helpful when trying to understand and use SQL:
●

The novice new to database design and SQL programming

●

The analyst or manager who wants to better understand how to implement and access SQL
databases

●

The database administrator who wants to learn more about programming


●

The technical support professional or testing engineer who must perform ad hoc queries
against an SQL data source

●

The web developer writing applications that must access SQL databases

●

The third-generation language (3GL) programmer embedding SQL within an application’s
source code

●

Any other individual who wants to learn how to write SQL code that can be used to create
and access databases within an RDBMS

Whichever category you might fit into, an important point to remember is that the book
is geared toward anyone wanting to learn standard SQL, not a product-specific version of the
language. The advantage of this is that you can take the skills learned in this book and apply
them to real-world situations, without being limited to product standards. You will, of course,
still need to be aware of how the product you work in implements SQL, but with the foundation
provided by the book, you’ll be able to move from one RDBMS to the next and still have a
basic understanding of how SQL is used. As a result, this book is a useful tool to anyone new
to SQL-based databases, regardless of the product used. SQL programmers need only adapt
their knowledge to the specific RDBMS.

What Content the Book Covers

SQL: A Beginner’s Guide is divided into three parts. Part I introduces you to the basic concepts
of SQL and explains how to create objects within your database. Part II provides you with a
foundation in how to retrieve data from a database and modify the data that’s stored in the

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:12 PM


Color profile: Generic CMYK printer profile
Composite Default screen

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:xxi

Introduction

database. Part III provides you with information about advanced data access techniques that
allow you to expand on what you learned in Part I and Part II. In addition to the three parts,
SQL: A Beginner’s Guide contains appendixes that include reference material for the information
presented in the three parts.

Description of the Book’s Content
The following outline describes the contents of the book and shows how the book is broken
down into task-focused modules.

Part I: SQL Databases
Part I introduces you to SQL and the SQL environment and explains how to create database
objects within that environment. You’ll also learn how to use constraints in your table
definitions to enforce data integrity.


Module 1: Introduction to Relational Databases and SQL

This module introduces
you to relational databases and the relational model, which forms the basis for SQL. You’ll
also be provided with a general overview of SQL and how it relates to RDBMSs.

Module 2: Working with the SQL Environment This module describes the components
that make up the SQL environment. You’ll also be introduced to the objects that make up a schema,
and you’ll learn how to create a schema within your SQL environment. You’ll also be introduced to
the concept of creating a database object in an SQL implementation that supports the creation of
database objects.
Module 3: Creating and Altering Tables

In this module, you’ll learn how to create
SQL tables, specify column data types, create user-defined types, and specify column default
values. You’ll also learn how to alter a table definition and delete that definition from your
database.

Module 4: Enforcing Data Integrity

This module explains how integrity constraints
are used to enforce data integrity in your SQL tables. The module includes information on
table-related constraints, assertions, and domain constraints. You will learn how to create NOT
NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints.

Module 5: Creating SQL Views

In this module, you’ll learn how to add views to your
SQL database. You’ll also learn how to create updateable views and how to drop views from
the database.


P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:12 PM

xxi


Color profile: Generic CMYK printer profile
Composite Default screen

xxii

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:xxii

SQL: A Beginner’s Guide

Module 6: Managing Database Security In this module, you’ll be introduced to the
SQL security model and learn how authorization identifiers are defined within the context of a
session. You’ll then learn how to create and delete roles, grant and revoke privileges, and grant
and revoke roles.

Part II: Data Access and Modification
Part II explains how to access and modify data in an SQL database. You’ll also learn how to
use predicates, functions, and value expressions to manage that data. In addition, Part II
describes how to join tables together and use subqueries to access data in multiple tables.

Module 7: Querying SQL Data

This module describes the basic components of the

SELECT statement and how the statement is used to retrieve data from an SQL database.
You’ll learn how to define each clause that makes up the SELECT statement and how those
clauses are processed when querying a database.

Module 8: Modifying SQL Data

In this module, you’ll learn how to modify data in an
SQL database. Specifically, you’ll learn how to insert data, update data, and delete data. The
module reviews each component of the SQL statements that allow you to perform these data
modifications.

Module 9: Using Predicates In this module, you’ll learn how to use predicates to
compare SQL data, return null values, return similar values, reference additional sources of
data, and quantify comparison predicates. The module describes the various types of predicates
and shows you how they’re used to retrieve specific data from an SQL database.
Module 10: Working with Functions and Value Expressions

This module explains
how to use various types of functions and value expressions in your SQL statements. You’ll
learn how to use set functions, value functions, value expressions, and special values in
various clauses within an SQL statement.

Module 11: Accessing Multiple Tables This module describes how to join tables in
order to retrieve data from those tables. You will learn how to perform basic join operations,
join tables with shared column names, use the condition join, and perform union operations.
Module 12: Using Subqueries to Access and Modify Data

In this module, you’ll
learn how to create subqueries that return multiple rows and that return only one value. You’ll
also learn how to use correlated subqueries and nested subqueries. In addition, you’ll learn

how to use subqueries to modify data.

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:12 PM


Color profile: Generic CMYK printer profile
Composite Default screen

Begin8 / SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Blind Folio FM:xxiii

Introduction

Part III: Advanced Data Access
Part III introduces you to advanced data-access techniques such as SQL-invoked routines,
triggers, and cursors. You’ll also learn how to manage transactions and how to access SQL
data from your host program.

Module 13: Creating SQL-Invoked Routines

This module describes SQL-invoked
procedures and functions and how you can create them in your SQL database. You’ll learn
how to define input parameters, add local variables to your routine, work with control
statements, and use output parameters.

Module 14: Creating SQL Triggers This module introduces you to SQL triggers and
explains how to create insert, update, and delete triggers in your SQL database. You’ll learn
how triggers are automatically invoked and what types of actions they can take.
Module 15: Using SQL Cursors In this module, you’ll learn how SQL cursors are used

to retrieve one row of data at a time from a result set. The module explains how to declare a
cursor, open and close a cursor, and retrieve data from a cursor. You’ll also learn how to use
positioned UPDATE and DELETE statements after you fetch a row through a cursor.
Module 16: Managing SQL Transactions In this module, you’ll learn how
transactions are used to ensure the integrity of your SQL data. The module describes how
to set transaction properties, start a transaction, set constraint deferrability, create savepoints
in a transaction, and terminate a transaction.
Module 17: Accessing SQL Data from Your Host Program

This module describes
the four methods supported by the SQL standard for accessing an SQL database. You’ll learn
how to invoke SQL directly from a client application, embed SQL statements in a program,
create SQL client modules, and use an SQL call-level interface to access data.

Part IV: Appendixes
The appendixes include reference material for the information presented in the first three parts.

Appendix A: Answers to Mastery Checks

This appendix provides the answers to the
Mastery Check questions listed at the end of each module.

Appendix B: SQL:1999 Keywords

This appendix lists the reserved and nonreserved
keywords as they are used in SQL statements and defined in the SQL:1999 standard.

P:\010Comp\Begin8\885-7\fm.vp
Tuesday, April 08, 2003 12:33:12 PM


xxiii