SQL: A Beginner's Guide

562 Pages·2003·6.986 MB·English

by  Robert Sheldon

Color profile: Generic CMYK printer profile Begin8/ SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Composite Default screen Blind FolioFM: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 Begin8/ SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Composite Default screen Blind FolioFM: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 Begin8/ SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Composite Default screen Blind FolioFM: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 Begin8/ SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Composite Default screen Blind FolioFM: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
contactMcGraw-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 byMcGraw-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 Begin8/ SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Composite Default screen Blind FolioFM:v
About the Author
Robert Sheldon has worked as a consultant and technical writer
foranumberofyears.Asaconsultant,hehasmanagedthe
development and maintenance of web-based and client-server
applicationsandthedatabasesthatsupportedthoseapplications.
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,oneofwhichreceivedaCertificateofMerit
fromthePugetSoundChapteroftheSocietyforTechnical
Communication.Inaddition,twoofthebooksthatRobert
haswrittenfocusexclusivelyonSQLServerdesignand
implementation. Robert has also written and edited a variety
ofotherdocumentationrelatedtoSQLdatabasesandother
computertechnologies.Inaddition,hiswritingincludesmaterial
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 Begin8/ SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Composite Default screen Blind FolioFM: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 Begin8/ SQL: A Beginner’s Guide / Sheldon / 222885-7 /1
Composite Default screen Blind FolioFM:vii
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
Color profile: Generic CMYK printer profile Begin8/ SQL: A Beginner’s Guide / Sheldon / 222885-7 / Front Matter
Composite Default screen Blind FolioFM:viii
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