The Programmer’s Guide to SQL

564 Pages·2003·15.533 MB·English

by  Cristian Darie, Karli Watson, Chris Hart, Kevin Hoffman, Julian Skinner (auth.)

The Programmer's
Guide to SQ-L
CRISTIAN DARIE, KARLI WATSON
WITH CHRIS HART, KEVIN HOFFMAN, JULIAN SKINNER
APress Media, LLC
The Programmer's Guide to SQL
Copyright ~2003 by Cristian Darie, Karli Watson with Chris Hart,
Kevin Hoffman, Julian Skinner
Originally published by Apress in 2003
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any
means, electronic or mechanical, including photocopying, recording, or by any information
storage or retrieval system, without the prior written permission of the copyright owner and the
publisher.
ISBN 978-1-59059-218-2 ISBN 978-1-4302-0800-6 (eBook)
DOI 10.1007/978-1-4302-0800-6
Trademarked names may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, we use the names only in an editorial fashion and to the
benefit of the trademark owner, with no intention of infringement of the trademark.
Technical Reviewers: Cristof Falk, Slavomir Furman, Brad Maiani, Judith M. Myerson, Johan
Normen, David Schultz
Editorial Board: Dan Appleman, Craig Berry, Gary Cornell, Steven Rycroft, Julian Skinner, Martin
Streicher, Jim Sumser, Karen Watterson, Gavin Wray, John Zukowski
Lead Editor: Tony Davis
Assistant Publisher: Grace Wong
Project Manager: Darren Murphy
Copy Editor: Kim Wnnpsett
Production Manager: Kari Brooks
Production Editor: KellyWmquist
Proofreader: Thistle Hill Publishing Services
Compositor: Kinetic Publishing Services, LLC
Indexer: John Collin
Artist: Kinetic Publishing Services, LLC
Cover Designer: Kurt Krames
Manufacturing Manager: Tom Debolski
The information in this book is distributed on an "as is" basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall
have any liability to any person or entity with respect to any loss or damage caused or alleged to
be caused directly or indirectly by the information contained in this work.
Contents at a Glance
About the Authors ................................................. ix
Introduction ....................................................... xi
Chapter 1 Understanding SQL and Relational Databases ....... 1
Chapter 2 Retrieving Data with SQL .......................... 29
Chapter 3 Modifying Data ...................................... 67
Chapter 4 Summarizing and Grouping Data ..................... 83
Chapter 5 Performing Calculations and Using Functions .... 107
Chapter 6 Combining SQL Queries ............................. 147
Chapter 1 Querying Multiple Tables ......................... 167
Chapter 8 Hiding Complex SQL with Views .................. . 193
Chapter 9 Using Stored Procedures .......................... 203
Chapter 10 Transactions ....................................... 265
Chapter 11 Users and Security ................................ 301
Chapter 12 Working with Database Objects .................... 329
Chapter 13 Triggers ............................................ 363
Chapter 14 Case Study: Building a Product Catalog ......... 389
Chapter 15 Case Study: Implementing Role-Based
Security ............................................ 453
Appendix A Executing SQL Statements ......................... 499
Appendix B Setting Up the InstantUniversity Database ...... 513
Appendix C Data Types ......................................... 531
Index .............................................................. 539
iii
Contents
About the Authors ..............................................
ix
Introduction .....................................................
xi
Chapter 1 Understanding SQL and Relational
Databases ...........................................
1
What Is SQL? ........................................................ 1
Introducing Relational Databases ................................. 7
The Power of SQL .................................................. 25
Summary ............................................................. 28
Chapter 2 Retrieving Data with SQL ..................... 29
Understanding Simple Retrieval .................................. 29
Using Aliases ...................................................... 35
Returning Calculated Columns ..................................... 37
Sorting Data ....................................................... 46
Filtering Data ..................................................... 47
Dealing with NULL Data ........................................... 63
Summary ............................................................. 64
Chapter 3 Modifying Data ...................................
67
Introducing SQL Data Modification ............................... 67
Adding New Data ................................................... 67
Changing Existing Data ........................................... 75
Deleting Data ...................................................... 78
Summary ............................................................. 81
Chapter 4 Summarizing and Grouping Data ...............
83
Summarizing Data .................................................. 83
Grouping Data ...................................................... 90
Using Analytic Functions ........................................ 103
Summary ............................................................ 106
v
Contents
Chapter 5 Performing Calculations and Using
Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
107
Performing Calculations in SQL ............ ·. .................... 107
Using Functions in SQL .......................................... 111
Summary ............................................................ 145
Chapter 6 Combining SQL Queries ........................
147
Using Subqueries ................................................. 147
Combining Data from Queries ..................................... 158
Summary ............................................................ 166
Chapter 7 Querying Multiple Tables ....................
167
Understanding Simple Joins ...................................... 167
Inner vs. Outer Joins ........................................... 178
Exploring Additional Joins in Oracle .......................... 190
Summary ............................................................ 192
Chapter 8 Hiding Complex SQL with Views .............
193
Overview of SQL Views ........................................... 193
Creating a View .................................................. 194
Modifying Data Through a View .................................. 198
Updating a View .................................................. 199
Deleting a View .................................................. 201
Summary ............................................................ 202
Chapter 9 Using Stored Procedures ......................
203
Stored Procedure Overview ....................................... 203
Coding in Stored Procedures ..................................... 225
Using Cursors ..................................................... 234
Returning Result Sets from a Procedure ........................ 247
Error Handling ................................................... 252
Summary ............................................................ 263
vi
Contents
Chapter 10 Transactions ..................................... 265
What Is a Transaction? .......................................... 266
Understanding Database Transactions ............................ 268
Transactions at Work ............................................. 275
Moving onto Advanced Topics ..................................... 284
Summary ............................................................ 299
Chapter 11 Users and Security ............................ 301
Let's Talk About Security ....................................... 302
Understanding Database Security and SQL ....................... 304
Working with Access Security ................................... 307
Working with SQL Server Security ............................... 308
Working with Oracle Security ................................... 315
Working with D82 Security ....................................... 321
Working with MySQL Security ..................................... 324
Summary ............................................................ 327
Chapter 12 Working with Database Objects ............. 329
Creating and Dropping Databases ................................ 329
Creating and Dropping Data Tables .............................. 331
Copying Tables ................................................... 339
Altering Database Tables ........................................ 340
Creating Temporary Tables ....................................... 342
Using Sequences .................................................. 348
Using Autonumbered Fields ....................................... 349
Using Indexes ..................................................... 355
Understanding Declarative Referential Integrity
and the FOREIGN KEY Constraint ............................... 358
Using Database Schemas .......................................... 361
Summary ............................................................. 362
Chapter 13 Triggers ..........................................
363
What Are Triggers? ............................................... 363
Working with SQL Server Triggers ............................... 367
Working with Oracle Triggers ................................... 375
Working with D82 Triggers ....................................... 382
Summary ............................................................ 387
vii
Contents
Chapter 14 Case Study: Building a
Product Catalog ................................
389
Setting Up the Catalog .......................................... 390
Creating the Data Structures ................................... 395
Adding Sample Data ............................................... 402
Retrieving Catalog Information ................................. 406
Updating Catalog Information .................................... 432
Searching the Catalog ........................................... 442
Summary ............................................................ 451
Chapter 15 Case Study: Implementing Role-Based
Security ..........................................
453
What Is Role-Based Security? ................................... 453
Why Implement Role-Based Security? ............................. 454
Implementing Role-Based Security ............................... 454
Stage 1: Creating the Database Tables ......................... 457
Stage 2: Creating the Stored Procedures ....................... 470
Summary ............................................................ 497
Appendix A Executing SQL Statements ....................
499
Entering SQL Statements ......................................... 499
Connecting to Databases Using SQL .............................. 511
Appendix B Setting Up the InstantUniversity
Database ..........................................
513
Creating the Database ........................................... 514
Inserting Data into the Database ............................... 523
Testing the Database Installation .............................. 527
Appendix C Data Types .......................................
531
RDBMS Data Types ................................................. 531
Index .............................................................
539
viii
About the Authors
CRISTIAN DARIE is an independent IT consultant spe
cializing in Microsoft technologies. Having worked
with computers since he was a child, he won his first
prize at the age of 12 in the first programming contest
he ever entered. He was actively involved with the for
mer Wrox Press as a technical reviewer and author,
having contributed the OOP parts of Visual C# .NET:
A Guide for VB6 Developers and coauthored Beginning
ASP.NET £-Commerce with Visual Basic .NET and Visual Studio .NET. He has
authored MCAD preparation material and various SkillDrill tests, such as
C# Programming, Microsoft COM+, and DB2 Development, which can be
taken at http: I lwww. skilldrill. com.
Cristian is at Politehnica University of Bucharest, studying automatic control and
industrial informatics. He can be contacted through http: I 1'-IMN. ChristianDarie. ro.
My big thanks go to Dave, Julian, and Chris for being such great people to
work with.
KARLI WATSON is a freelance author and IT specialist
with a penchant for multicolored clothing. He started
out with the intention of becoming a world-famous
nanotechnologist, so perhaps one day you might recog
nize his name as he receives a Nobel Prize. For now,
though, Karli's computing interests include all things
mobile and everything .NET. Karli is also a snowboard
ing enthusiast and wishes he had a cat.
JuLIAN SKINNER studied Germanic etymology before joining the formerWrox
Press. He has consequently spent most of the last four-and-a-half years reading,
editing, and writing books about programming, focusing on Microsoft technolo
gies and, in particular, on C# and the .NET Framework. He contributed many
sections and code samples, and often entire chapters, for the books he worked
on at Wrox, mostly hiding behind the relative anonymity of an "additional mater
ial" credit, but he is credited as a coauthor of, among other titles, Professional
ADO.NET.
ix
About the Authors
CHRIS HART is a developer and author based in Birmingham (in the United
Kingdom, not Alabama). Chris spent most of the last four years as a member of
the technical editorial team at the former Wrox Press and has been programming
on a variety of systems since the age of 10. She spends most of her time working
with Microsoft technologies and has been heavily involved with .NET since it
was first released as an early alpha preview. She's currently working for Business
Post and is developing .NET Compact Framework/SQL CE applications and 20
barcode scanning components in Delphi.
KEVIN HoFFMAN started programming at the age of 10 when his grandfather gave
him a Commodore VIC-20 that he refurbished, and he's been addicted to pro
gramming ever since. He has worked on everything from writing interfaces for
scientific instruments to building e-commerce Web sites to providing second
tier technical support for Unix. He got hold of .NET when it was first handed out
in early pre-beta form and has been coding with it ever since. He's currently
employed in Houston, Texas, where he creates enterprise applications with the
.NET Framework for a financial services company.
X
Introduction
STRUCTURED QUERY LANGUAGE (SQL) is the language of the database. It's a stan
dard that defines how to manipulate almost every aspect of database, from
creating the database and tables themselves to creating, reading, inserting,
updating, and deleting data in the database. This standard is then implemented
and extended differently by each database system vendor.
The current version of the SQL standard is SQL-99. Most major database
platforms now adhere fairly closely to this standard with a couple of exceptions.
Microsoft Access, for example, has only recently (with Access XP) adopted the
SQL-92 standard. Previous editions of Access supported only SQL-89.
In this book, we'll walk through all the major SQL statements you're likely to
need when programming databases. We'll highlight differences between the
standard and implementations used in five major database systems:
• SQLServer
• Oracle
• DB2
• MySQL
• Access
Who Is This Book For?
This book is designed to meet the needs of both the novice SQL programmer
who needs a bit of syntactical help and the developer using SQL day to day who
needs a handy desktop SQL reference. You don't need to have done much data
base programming before, but some experience with databases will be useful.
You should also have one of the five database systems mentioned earlier to gain
the most benefits from this book.
xi