by Allen G.Taylor
SQL
®
FOR
DUMmIES
‰
5TH EDITION

by Allen G.Taylor
SQL
®
FOR
DUMmIES
‰
5TH EDITION
SQL For Dummies
®
, 5th Edition
Published by
Wiley Publishing, Inc.
111 River Street
Hoboken, NJ 07030
www.wiley.com
Copyright © 2003 by Wiley Publishing, Inc., Indianapolis, Indiana
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or
by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permit-
ted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written
permission of the Publisher, or authorization through payment of the appropriate licensing fee to the

About the Author
Allen G. Taylor is a 30-year veteran of the computer industry and the author
of 22 books, including Crystal Reports 9 For Dummies, Database Development
For Dummies, Access Power Programming with VBA, and SQL Weekend Crash
Course. He lectures internationally on databases, networks, innovation, and
entrepreneurship. He also teaches database development through a leading
online education provider and teaches digital electronics at Portland State
University. He teaches computer hardware via distance learning at the
International Institute for Information, Science & Technology in Shanghai,
China. For the latest news on Allen’s activities, check out
allengtaylor.com.
You can contact Allen at


Dedication
This book is dedicated to all the seekers who are trying to find their way in
the world. May you find what you seek.
Acknowledgments
First and foremost, I would like to acknowledge the help of Jim Melton, editor
of the ISO/ANSI specification for SQL. Without his untiring efforts, this book,
and indeed SQL itself as an international standard, would be of much less
value. Andrew Eisenberg has also contributed to my knowledge of SQL
through his writing. I would also like to thank my project editor, Kala
Schrager, and my acquisitions editor, Terri Varveris, for their key contribu-
tions to the production of this book. As always, thanks to my agent, Matt
Wagner of Waterside Productions, for his support of my career.
Publisher’s Acknowledgments
We’re proud of this book; please send us your comments through our online registration form
located at
www.dummies.com/register/.

Joyce Pepple, Acquisitions Director
Composition Services
Gerry Fahey, Vice President of Production Services
Debbie Stailey, Director of Composition Services
Contents at a Glance
Introduction 1
Part I: Basic Concepts 5
Chapter 1: Relational Database Fundamentals 7
Chapter 2: SQL Fundamentals 21
Chapter 3: The Components of SQL 45
Part II: Using SQL to Build Databases 71
Chapter 4: Building and Maintaining a Simple Database Structure 73
Chapter 5: Building a Multitable Relational Database 91
Part III: Retrieving Data 119
Chapter 6: Manipulating Database Data 121
Chapter 7: Specifying Values 139
Chapter 8: Advanced SQL Value Expressions 161
Chapter 9: Zeroing In on the Data You Want 173
Chapter 10: Relational Operators 199
Chapter 11: Delving Deep with Nested Queries 223
Chapter 12: Recursive Queries 241
Chapter 13: Providing Database Security 251
Part IV: Controlling Operations 265
Chapter 14: Protecting Data 267
Chapter 15: Using SQL Within Applications 285
Part V: SQL in the Real World 295
Chapter 16: ODBC and JDBC 297
Chapter 17: SQL:2003 and XML 307
Part VI: Advanced Topics 321
Chapter 18: Cursors 323

Database Size and Complexity 9
What Is a Database Management System? 10
Flat Files 11
Database Models 12
Relational model 13
Why relational is better 13
Components of a relational database 14
Guess who’s coming to dinner? 14
Enjoy the view 15
Schemas, domains, and constraints 18
The object model challenges the relational model 19
Object-relational model 20
Database Design Considerations 20
Chapter 2: SQL Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21
What SQL Is and Isn’t 21
A (Very) Little History 23
SQL Commands 24
Reserved Words 25
Data Types 25
Exact numerics 26
Approximate numerics 28
Character strings 30
Booleans 31
Datetimes 31
Intervals 33
ROW types 33
Collection types 34
REF types 35
User-defined types 36
Data type summary 38

Altering the table structure 79
SQL For Dummies, 5th Edition
x
Identifying a primary key 80
Creating an index 82
Deleting a table 84
Building PowerDesign with SQL’s DDL 85
Using SQL with Microsoft Access 85
Creating a table 86
Creating an index 87
Altering the table structure 87
Deleting a table 88
Deleting an index 88
Portability Considerations 88
Chapter 5: Building a Multitable Relational Database . . . . . . . . . . . .91
Designing the Database 91
Step 1: Defining objects 92
Step 2: Identifying tables and columns 92
Step 3: Defining tables 93
Domains, character sets, collations, and translations 97
Getting into your database fast with keys 97
Working with Indexes 100
What’s an index, anyway? 100
Why would I want an index? 102
Maintaining an index 102
Maintaining Integrity 103
Entity integrity 103
Domain integrity 104
Referential integrity 105
Potential problem areas 108

Column references 144
Value Expressions 145
String value expressions 146
Numeric value expressions 147
Datetime value expressions 147
Interval value expressions 148
Conditional value expressions 148
Functions 149
Summarizing by using set functions 149
Value functions 152
Chapter 8: Advanced SQL Value Expressions . . . . . . . . . . . . . . . . . . .161
CASE Conditional Expressions 161
Using CASE with search conditions 162
Using CASE with values 164
A special CASE — NULLIF 166
Another special CASE — COALESCE 168
CAST Data-Type Conversions 168
Using CAST within SQL 170
Using CAST between SQL and the host language 170
Row Value Expressions 171
Chapter 9: Zeroing In on the Data You Want . . . . . . . . . . . . . . . . . . . .173
Modifying Clauses 173
FROM Clauses 175
WHERE Clauses 175
Comparison predicates 177
BETWEEN 177
IN and NOT IN 178
LIKE and NOT LIKE 180
SIMILAR 182
NULL 182

ON versus WHERE 221
Chapter 11: Delving Deep with Nested Queries . . . . . . . . . . . . . . . . .223
Why Use a Subquery? 224
What Subqueries Do 225
Nested queries that return sets of rows 225
Nested queries that return a single value 229
The ALL, SOME, and ANY quantifiers 231
Nested queries that are an existence test 233
Other correlated subqueries 234
UPDATE, DELETE, and INSERT statements 238
Chapter 12: Recursive Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .241
What Is Recursion? 241
Houston, we have a problem 243
Failure is not an option 243
What Is a Recursive Query? 244
Where Might I Use a Recursive Query? 244
Querying the hard way 246
Saving time with a recursive query 247
Where Else Might I Use a Recursive Query? 249
xiii
Table of Contents
Chapter 13: Providing Database Security . . . . . . . . . . . . . . . . . . . . . .251
The SQL Data Control Language 252
User Access Levels 252
The database administrator 252
Database object owners 253
The public 254
Granting Privileges to Users 255
Roles 256
Inserting data 256

Procedural language strengths and weaknesses 287
Problems in combining SQL with a procedural language 287
SQL For Dummies, 5th Edition
xiv
Hooking SQL into Procedural Languages 288
Embedded SQL 288
Module language 291
Object-oriented RAD tools 293
Part V: SQL in the Real World 295
Chapter 16: ODBC and JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .297
ODBC 297
ODBC interface 298
Components of ODBC 298
ODBC in a Client/Server Environment 299
ODBC and the Internet 300
Server extensions 300
Client extensions 301
ODBC and an Intranet 304
JDBC 304
Chapter 17: SQL:2003 and XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .307
How XML Relates to SQL 307
The XML Data Type 308
When to use the XML type 308
When not to use the XML type 309
Mapping SQL to XML and XML to SQL 309
Mapping character sets 309
Mapping identifiers 309
Mapping data types 310
Mapping tables 311
Handling null values 312

Compound Statements 333
Atomicity 334
Variables 336
Cursors 336
Conditions 336
Handling conditions 338
Conditions that aren’t handled 340
Assignment 340
Flow of Control Statements 341
IF THEN ELSE END IF 341
CASE END CASE 342
LOOP ENDLOOP 343
LEAVE 343
WHILE DO END WHILE 344
REPEAT UNTIL END REPEAT 344
FOR DO END FOR 345
ITERATE 345
Stored Procedures 346
Stored Functions 347
Privileges 348
Stored Modules 348
Chapter 20: Error-Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .351
SQLSTATE 351
WHENEVER Clause 353
Diagnostics Areas 354
The diagnostics header area 354
The diagnostics detail area 356
SQL For Dummies, 5th Edition
xvi
Constraint violation example 358

xvii
Table of Contents
SQL For Dummies, 5th Edition
xviii
Introduction
W
elcome to database development using the industry standard query
language (SQL). Many database management system (DBMS) tools
run on a variety of hardware platforms. The differences among the tools can
be great, but all serious products have one thing in common: They support
SQL data access and manipulation. If you know SQL, you can build relational
databases and get useful information out of them.
About This Book
Relational database management systems are vital to many organizations.
People often think that creating and maintaining these systems are extremely
complex activities — the domain of database gurus who possess enlighten-
ment beyond that of ordinary mortals. This book sweeps away the database
mystique. In this book, you
ߜ Get to the roots of databases.
ߜ Find out how a DBMS is structured.
ߜ Discover the major functional components of SQL.
ߜ Build a database.
ߜ Protect a database from harm.
ߜ Operate on database data.
ߜ Determine how to get the information you want out of a database.
The purpose of this book is to help you build relational databases and get
valuable information out of them by using SQL. SQL is the international stan-
dard language used around the world to create and maintain relational data-
bases. This edition covers the latest version of the standard, SQL:2003.
This book doesn’t tell you how to design a database (I do that in Database

Throughout this part, I emphasize protecting your database from corruption,
which is a bad thing that can happen in many ways. SQL gives you the tools
to prevent corruption, but you must use them properly to prevent problems
caused by bad database design, harmful interactions, operator error, and
equipment failure.
2
SQL For Dummies, 5th Edition
Part III: Retrieving Data
After you have some data in your database, you want to do things with it:
Add to the data, change it, or delete it. Ultimately, you want to retrieve useful
information from the database. SQL tools enable you to do all this. These
tools give you low-level, detailed control over your data.
Part IV: Controlling Operations
A big part of database management is protecting the data from harm, which
can come in many shapes and forms. People may accidentally or intention-
ally put bad data into database tables, for example. You can protect yourself
by controlling who can access your database and what they can do. Another
threat to data comes from unintended interaction of concurrent users’ opera-
tions. SQL provides powerful tools to prevent this too. SQL provides much of
the protection automatically, but you need to understand how the protection
mechanisms work so you get all the protection you need.
Part V: SQL in the Real World
SQL is different from most other computer languages in that it operates on a
whole set of data items at once, rather than dealing with them one at a time.
This difference in operational modes makes combining SQL with other lan-
guages a challenge, but you can face it by using the information in this book.
You can exchange information with nondatabase applications by using XML. I
also describe in depth how to use SQL to transfer data across the Internet or
an intranet.
Part VI: Advanced Topics

4
SQL For Dummies, 5th Edition