SQL : a beginner's guide

539 Pages·2009·1.829 MB·English

by  Andrew J Oppel, Robert Sheldon

SQL
A Beginner’s Guide
Third Edition
Andy Oppel
Robert Sheldon
New York Chicago San Francisco
Lisbon London Madrid Mexico City
Milan New Delhi San Juan
Seoul Singapore Sydney Toronto
Copyright © 2009 by The McGraw-Hill Companies. All rights reserved. Manufactured in the United States of America. Except as
permitted under the United States 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.
0-07-154865-3
The material in this eBook also appears in the print version of this title: 0-07-154864-5.
All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked
name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringement of the
trademark. Where such designations appear in this book, they have been printed with initial caps.
McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate training
programs. For more information, please contact George Hoare, Special Sales, at [email protected] or [phone].
TERMS OF USE
This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and to the work.
Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve one copy
of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit,
distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior consent. You may use the work
for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right to use the work may be
terminated if you fail to comply with these terms.
THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO
THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUD-
ING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND
EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WAR-
RANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or
guarantee that the functions contained in the work will meet your requirements or that its operation will be uninterrupted or error free.
Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the
work or for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the
work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential
or similar damages that result from the use of or inability to use the work, even if any of them has been advised of the possibility of such
damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or
otherwise.
DOI: 10.1036/0071548645
About the Authors
Andrew (Andy) J. Oppel is a proud graduate of the Boys’ Latin School of Maryland and of
Transylvania University (Lexington, Kentucky) where he earned a BA in computer science
in 1974. Since then he has been continuously employed in a wide variety of information
technology positions, including programmer, programmer/analyst, systems architect, project
manager, senior database administrator, database group manager, consultant, database designer,
data modeler, and data architect. In addition, he has been a part-time instructor with the
University of California (Berkeley) Extension for over 20 years, and received the Honored
Instructor Award for the year 2000. His teaching work included developing three courses for
UC Extension, “Concepts of Database Management Systems,” “Introduction to Relational
Database Management Systems,” and “Data Modeling and Database Design.” He also earned
his Oracle 9i Database Associate certification in 2003. He is currently employed as a senior data
modeler for Blue Shield of California. Aside from computer systems, Andy enjoys music (guitar
and vocals), amateur radio (Pacific Division vice director, American Radio Relay League) and
soccer (referee instructor, U.S. Soccer).
Andy has designed and implemented hundreds of databases for a wide range of applications,
including medical research, banking, insurance, apparel manufacturing, telecommunications,
wireless communications, and human resources. He is the author of Databases Demystified
(McGraw-Hill/Osborne, 2004) and SQL Demystified (McGraw-Hill/Osborne, 2005). His database
product experience includes IMS, DB2, Sybase, Microsoft SQL Server, Microsoft Access,
MySQL, and Oracle (versions 7, 8, 8i, 9i, and 10g).
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. 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. 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.
About the Technical Editor
James Seymour is a graduate of the University of North Carolina at Chapel Hill with a BA in
history and political science and the University of Kentucky with a MA in history. He became
first involved with computer technology in 1965 with the mainframe environment at North
Carolina. While in the United States Army during the Vietnam War, he was on the small team
that worked with the mainframe setup at the Pentagon for various military strategic scenarios.
Since 1972, he has been involved in varied computer environments with the second point-of-sale
Copyright © 2009 by The McGraw-Hill Companies. Click here for terms of use.
and inventory control project in the retail industry, analytical programs and database initiatives in
the insurance and benefits industries, loss control startups, and other inventory control and sales
tracking projects throughout many different industries.
From 1987 through 1995, James was an instructor of database management in the community
college system of the state of Kentucky. In this capacity, he created the first database management
and C programming courses in the state of Kentucky and helped both public and private entities
with urgent training needs, including the programming of guidance systems on cruise missiles for
Desert Storm.
Before 1985, he was a system administrator, network administrator, programmer, and
database administrator. Since 1985, James has been a senior database administrator working
primarily with DB2 and Oracle DBMSs on multiple platforms including SQL Server
beginning with version 7.0. He is currently the senior database administrator and data
architect for a Fortune 100 company overseeing major projects in the United States, Canada,
and the United Kingdom.
For more information about this title, click here
Contents
ACKNOWLEDGMENTS ........................................................... xi
INTRODUCTION .................................................................. xi
PART I Relational Databases and SQL
1 Introduction to Relational Databases and SQL ........................... 3
Understand Relational Databases .................................................... 4
The Relational Model ......................................................... 5
Learn About SQL ................................................................... 15
The SQL Evolution ........................................................... 15
Types of SQL Statements ..................................................... 18
Types of Execution ........................................................... 19
SQL Standard versus Product Implementations ................................ 21
2 Working with the SQL Environment ...................................... 29
Understand the SQL Environment ................................................... 30
Understand SQL Catalogs ........................................................... 32
Schemas ...................................................................... 34
Schema Objects ............................................................... 35
Then What Is a Database? ..................................................... 37
Name Objects in an SQL Environment .............................................. 40
Qualified Names .............................................................. 41
v
vi SQL: A Beginner’s Guide
Create a Schema .................................................................... 42
Create a Database ................................................................... 44
3 Creating and Altering Tables .............................................. 49
Create SQL Tables .................................................................. 50
Specify Column Data Types ......................................................... 54
String Data Types ............................................................. 55
Numeric Data Types .......................................................... 57
Datetime Data Types .......................................................... 58
Interval Data Type ............................................................ 60
Boolean Data Type ............................................................ 61
Using SQL Data Types ........................................................ 62
Create User-Defined Types .......................................................... 63
Specify Column Default Values ..................................................... 64
Delete SQL Tables .................................................................. 69
4 Enforcing Data Integrity ................................................... 73
Understand Integrity Constraints .................................................... 74
Use NOT NULL Constraints ........................................................ 76
Add UNIQUE Constraints ........................................................... 77
Add PRIMARY KEY Constraints ................................................... 79
Add FOREIGN KEY Constraints ................................................... 83
The MATCH Clause .......................................................... 88
The <referential triggered action> Clause ...................................... 89
Define CHECK Constraints ......................................................... 95
Defining Assertions ........................................................... 97
Creating Domains and Domain Constraints .................................... 98
5 Creating SQL Views ....................................................... 103
Add Views to the Database .......................................................... 104
Defining SQL Views .......................................................... 108
Create Updateable Views ........................................................... 114
Using the WITH CHECK OPTION Clause .................................... 116
Drop Views from the Database ...................................................... 117
6 Managing Database Security .............................................. 123
Understand the SQL Security Model ................................................ 124
SQL Sessions ................................................................. 126
Accessing Database Objects ................................................... 128
Create and Delete Roles ............................................................. 130
Grant and Revoke Privileges ........................................................ 131
Revoking Privileges ........................................................... 135
Grant and Revoke Roles ............................................................. 137
Revoking Roles ............................................................... 138
Contents vii
PART II Data Access and Modification
7 Querying SQL Data ........................................................ 145
Use a SELECT Statement to Retrieve Data .......................................... 146
The SELECT Clause and FROM Clause ....................................... 147
Use the WHERE Clause to Define Search Conditions ............................... 152
Defining the WHERE Clause .................................................. 156
Use the GROUP BY Clause to Group Query Results ................................ 159
Use the HAVING Clause to Specify Group Search Conditions ....................... 164
Use the ORDER BY Clause to Sort Query Results ................................... 166
8 Modifying SQL Data ....................................................... 175
Insert SQL Data ..................................................................... 176
Inserting Values from a SELECT Statement ................................... 180
Update SQL Data ................................................................... 182
Updating Values from a SELECT Statement ................................... 185
Delete SQL Data .................................................................... 186
9 Using Predicates ........................................................... 193
Compare SQL Data ................................................................. 194
Using the BETWEEN Predicate ............................................... 199
Return Null Values .................................................................. 200
Return Similar Values ............................................................... 203
Reference Additional Sources of Data ............................................... 209
Using the IN Predicate ........................................................ 209
Using the EXISTS Predicate .................................................. 213
Quantify Comparison Predicates .................................................... 216
Using the SOME and ANY Predicates ......................................... 216
Using the ALL Predicate ...................................................... 218
10 Working with Functions and Value Expressions ........................... 225
Use Set Functions ................................................................... 226
Using the COUNT Function ................................................... 227
Using the MAX and MIN Functions ........................................... 229
Using the SUM Function ...................................................... 231
Using the AVG Function ...................................................... 232
Use Value Functions ................................................................ 232
Working with String Value Functions ......................................... 233
Working with Datetime Value Functions ...................................... 236
Use Value Expressions .............................................................. 238
Working with Numeric Value Expressions .................................... 238
Using the CASE Value Expression ............................................ 241
Using the CAST Value Expression ............................................ 244
Use Special Values .................................................................. 245
viii SQL: A Beginner’s Guide
11 Accessing Multiple Tables .................................................. 253
Perform Basic Join Operations ...................................................... 254
Using Correlation Names ..................................................... 257
Creating Joins with More than Two Tables .................................... 258
Creating the Cross Join ........................................................ 259
Creating the Self-Join ......................................................... 260
Join Tables with Shared Column Names ............................................. 261
Creating the Natural Join ...................................................... 262
Creating the Named Column Join ............................................. 263
Use the Condition Join .............................................................. 263
Creating the Inner Join ........................................................ 264
Creating the Outer Join ........................................................ 266
Perform Union Operations .......................................................... 269
12 Using Subqueries to Access and Modify Data ............................. 277
Create Subqueries That Return Multiple Rows ....................................... 278
Using the IN Predicate ........................................................ 279
Using the EXISTS Predicate .................................................. 281
Using Quantified Comparison Predicates ...................................... 282
Create Subqueries That Return One Value ........................................... 283
Work with Correlated Subqueries ................................................... 284
Use Nested Subqueries .............................................................. 286
Use Subqueries to Modify Data ..................................................... 288
Using Subqueries to Insert Data ............................................... 288
Using Subqueries to Update Data .............................................. 290
Using Subqueries to Delete Data .............................................. 291
PART III Advanced Data Access
13 Creating SQL-Invoked Routines ........................................... 299
Understand SQL-Invoked Routines .................................................. 300
SQL-Invoked Procedures and Functions ....................................... 301
Working with the Basic Syntax ................................................ 301
Create SQL-Invoked Procedures .................................................... 303
Invoking SQL-Invoked Procedures ............................................ 305
Add Input Parameters to Your Procedures ........................................... 306
Using Procedures to Modify Data ............................................. 309
Add Local Variables to Your Procedures ............................................ 311
Work with Control Statements ...................................................... 313
Create Compound Statements ................................................. 313
Create Conditional Statements ................................................ 314
Create Looping Statements .................................................... 316
Add Output Parameters to Your Procedures ......................................... 320
Create SQL-Invoked Functions ...................................................... 321
Contents ix
14 Creating SQL Triggers ..................................................... 329
Understand SQL Triggers ........................................................... 330
Trigger Execution Context .................................................... 331
Create SQL Triggers ................................................................ 333
Referencing Old and New Values ............................................. 334
Dropping SQL Triggers ....................................................... 335
Create Insert Triggers ............................................................... 336
Create Update Triggers .............................................................. 338
Create Delete Triggers .............................................................. 343
15 Using SQL Cursors ........................................................ 351
Understand SQL Cursors ............................................................ 352
Declaring and Opening SQL Cursors .......................................... 353
Declare a Cursor .................................................................... 355
Working with Optional Syntax Elements ...................................... 356
Creating a Cursor Declaration ................................................. 360
Open and Close a Cursor ............................................................ 363
Retrieve Data from a Cursor ......................................................... 363
Use Positioned UPDATE and DELETE Statements .................................. 368
Using the Positioned UPDATE Statement ..................................... 368
Using the Positioned DELETE Statement ..................................... 370
16 Managing SQL Transactions .............................................. 377
Understand SQL Transactions ....................................................... 378
Set Transaction Properties ........................................................... 381
Specifying an Isolation Level .................................................. 382
Specifying a Diagnostics Size ................................................. 387
Creating a SET TRANSACTION Statement ................................... 388
Start a Transaction .................................................................. 389
Set Constraint Deferability .......................................................... 390
Create Savepoints in a Transaction .................................................. 392
Releasing a Savepoint ......................................................... 394
Terminate a Transaction ............................................................. 395
Committing a Transaction ..................................................... 395
Rolling Back a Transaction .................................................... 396
17 Accessing SQL Data from Your Host Program ............................ 403
Invoke SQL Directly ................................................................ 404
Embed SQL Statements in Your Program ........................................... 406
Creating an Embedded SQL Statement ........................................ 407
Using Host Variables in Your SQL Statements ................................ 408
Retrieving SQL Data .......................................................... 411
Error Handling ................................................................ 413
x SQL: A Beginner’s Guide
Create SQL Client Modules ......................................................... 417
Defining SQL Client Modules ................................................. 418
Use an SQL Call-Level Interface .................................................... 419
Allocating Handles ............................................................ 421
Executing SQL Statements .................................................... 423
Working with Host Variables .................................................. 424
Retrieving SQL Data .......................................................... 426
18 Working with XML Data .................................................. 433
Learn the Basics of XML ........................................................... 434
Learn About SQL/XML ............................................................. 437
The XML Data Type .......................................................... 437
SQL/XML Functions .......................................................... 439
SQL/XML Mapping Rule ..................................................... 441
PART IV Appendices
A Answers to Self Test ........................................................ 449
B SQL:2006 Keywords ....................................................... 491
SQL Reserved Keywords ......................................................... 492
SQL Nonreserved Keywords ........................................................ 494
C SQL Code Used in Try This Exercises ..................................... 497
SQL Code by Try This Exercise ..................................................... 498
The INVENTORY Database ...................................................... 514
Index ....................................................................... 519