SQL for dummies

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

Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8700.

Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing,

Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4447, e-mail: permcoord

[email protected].

Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the

Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com and related trade

dress are trademarks or registered trademarks of Wiley Publishing, Inc., in the United States and other

countries, and may not be used without written permission All other trademarks are the property of their

respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this

book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: WHILE THE PUBLISHER AND AUTHOR HAVE USED

THEIR BEST EFFORTS IN PREPARING THIS BOOK, THEY MAKE NO REPRESENTATIONS OR WAR￾RANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS BOOK

AND SPECIFICALLY DISCLAIM ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A

PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES REPRESENTA￾TIVES OR WRITTEN SALES MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT

BE SUITABLE FOR YOUR SITUATION. YOU SHOULD CONSULT WITH A PROFESSIONAL WHERE APPRO￾PRIATE. NEITHER THE PUBLISHER NOR AUTHOR SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR

ANY OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL, CON￾SEQUENTIAL, OR OTHER DAMAGES.

For general information on our other products and services or to obtain technical support, please contact

our Customer Care Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993, or fax

317-572-4002.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may

not be available in electronic books.

Library of Congress Control Number: 2003105668

ISBN: 07645-4075-0

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

is a trademark of Wiley Publishing, Inc.

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 [email protected].

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

Some of the people who helped bring this book to market include the following:

Acquisitions, Editorial, and

Media Development

Project Editor: Kala Schrager

Acquisitions Editor: Terri Varveris

Copy Editor: Kim Darosett

Technical Editor: Tom Farrington

Editorial Manager: Kevin Kirschner

Media Development Manager:

Laura VanWinkle

Media Development Supervisor:

Richard Graves

Editorial Assistant:

Cartoons: Rich Tennant (www.the5thwave.com)

Production

Project Coordinator: Ryan Steffen

Layout and Graphics: Jennifer Click,

Seth Conley, Stephanie D. Jumper,

Kristin McMullan,Tiffany Muth,

Jacque Schneider, Mary Gillot Virgin

Proofreaders: Brian H. Walls, Kathy Simpson,

TECHBOOKS Production Services

Indexer: TECHBOOKS Production Services

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Diane Graves Steele, Vice President and Publisher

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

Chapter 19: Persistent Stored Modules ......................................................................333

Chapter 20: Error-Handling ..........................................................................................351

Part VII: The Part of Tens .........................................363

Chapter 21: Ten Common Mistakes ............................................................................365

Chapter 22: Ten Retrieval Tips ....................................................................................369

Part VIII: Appendixes ...............................................373

Appendix A: SQL:2003 Reserved Words .....................................................................375

Appendix B: Glossary ...................................................................................................379

Index .......................................................................387

viii SQL For Dummies, 5th Edition

Table of Contents

Introduction .................................................................1

About This Book ..............................................................................................1

Who Should Read This Book? ........................................................................2

How This Book Is Organized ..........................................................................2

Part I: Basic Concepts ...........................................................................2

Part II: Using SQL to Build Databases .................................................2

Part III: Retrieving Data .........................................................................3

Part IV: Controlling Operations ...........................................................3

Part V: SQL in the Real World .............................................................3

Part VI: Advanced Topics .....................................................................3

Part VII: The Part of Tens .....................................................................4

Part VIII: Appendixes ............................................................................4

Icons Used in This Book .................................................................................4

Getting Started ................................................................................................4

Part I: Basic Concepts ..................................................5

Chapter 1: Relational Database Fundamentals . . . . . . . . . . . . . . . . . . . .7

Keeping Track of Things ................................................................................7

What Is a Database? ........................................................................................9

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

Null Values ......................................................................................................40

Constraints .....................................................................................................40

Using SQL in a Client/Server System ..........................................................41

The server ............................................................................................41

The client ..............................................................................................42

Using SQL on the Internet/Intranet .............................................................43

Chapter 3: The Components of SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45

Data Definition Language .............................................................................46

Creating tables .....................................................................................46

A room with a view ..............................................................................48

Collecting tables into schemas ..........................................................53

Ordering by catalog .............................................................................54

Getting familiar with DDL commands ...............................................55

Data Manipulation Language ......................................................................56

Value expressions ................................................................................57

Predicates .............................................................................................60

Logical connectives .............................................................................61

Set functions ........................................................................................61

Subqueries ............................................................................................63

Data Control Language .................................................................................63

Transactions ........................................................................................63

Users and privileges ............................................................................65

Referential integrity constraints can jeopardize your data ...........67

Delegating responsibility for security ..............................................69

Part II: Using SQL to Build Databases .........................71

Chapter 4: Building and Maintaining a Simple

Database Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .73

Building a Simple Database Using a RAD Tool ..........................................74

Deciding what to track ........................................................................74

Creating the table with Design View .................................................75

Altering the table structure ................................................................79

x SQL For Dummies, 5th Edition

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

Constraints .........................................................................................110

Normalizing the Database ..........................................................................112

First normal form ...............................................................................114

Second normal form ..........................................................................115

Third normal form .............................................................................116

Domain-key normal form (DK/NF) ...................................................117

Abnormal form ...................................................................................118

Part III: Retrieving Data ...........................................119

Chapter 6: Manipulating Database Data . . . . . . . . . . . . . . . . . . . . . . .121

Retrieving Data ............................................................................................121

Creating Views .............................................................................................123

From tables .........................................................................................124

With a selection condition ...............................................................125

With a modified attribute .................................................................126

Updating Views ............................................................................................127

Table of Contents xi

Adding New Data .........................................................................................127

Adding data one row at a time .........................................................128

Adding data only to selected columns ...........................................129

Adding a block of rows to a table ....................................................130

Updating Existing Data ...............................................................................132

Transferring Data ........................................................................................135

Deleting Obsolete Data ...............................................................................137

Chapter 7: Specifying Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .139

Values ............................................................................................................139

Row values .........................................................................................140

Literal values ......................................................................................140

Variables .............................................................................................142

Special variables ................................................................................144

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

ALL, SOME, ANY ................................................................................183

EXISTS .................................................................................................186

xii SQL For Dummies, 5th Edition