Teach Yourself

SQL
Ben Forta
in 10
Minutes
THIRD EDITION

800 East 96th Street, Indianapolis, Indiana 46240 USA

 ASSOCIATE PUBLISHER
Sams Teach Yourself SQL in Michael Stephens

10 Minutes, Third Edition DEVELOPMENT EDITOR

Mark Renfrow
Copyright © 2004 by Sams Publishing
All rights reserved. No part of this book shall be reproduced, MANAGING EDITOR
stored in a retrieval system, or transmitted by any means, elec- Charlotte Clapp
tronic, mechanical, photocopying, recording, or otherwise, with-
out written permission from the publisher. No patent liability is PROJECT EDITOR
assumed with respect to the use of the information contained Dan Knott
herein. Although every precaution has been taken in the prepara-
tion of this book, the publisher and author assume no responsibil-
INDEXER
ity for errors or omissions. Nor is any liability assumed for
Tom Dinse
damages resulting from the use of the information contained
herein.
PROOFREADER
International Standard Book Number: 0-672-32567-5 Leslie Joseph
Library of Congress Catalog Card Number: 2003093137
TECHNICAL EDITOR
Printed in the United States of America Christopher McGee
First Printing: April 2004
PUBLISHING
10 09 08 18 17 16 15 14 COORDINATOR
Cindy Teeters
Trademarks
All terms mentioned in this book that are known to be trademarks INTERIOR DESIGNER
or service marks have been appropriately capitalized. Sams Gary Adair
Publishing cannot attest to the accuracy of this information. Use
of a term in this book should not be regarded as affecting the COVER DESIGNER
validity of any trademark or service mark. Gary Adair

Warning and Disclaimer

Every effort has been made to make this book as complete and as
accurate as possible, but no warranty or fitness is implied. The
information provided is on an “as is” basis. The author and the
publisher shall have neither liability nor responsibility to any per-
son or entity with respect to any loss or damages arising from the
information contained in this book.

Bulk Sales
Sams Publishing offers excellent discounts on this book when
ordered in quantity for bulk purchases or special sales. For more
information, please contact
U.S. Corporate and Government Sales
1-800-382-3419
[email protected]
For sales outside of the U.S., please contact
International Sales
[email protected]
Contents
Introduction..........................................................................................1

1 Understanding SQL 5
Database Basics ..................................................................................5
What Is SQL? ....................................................................................11
Try It Yourself ....................................................................................12
Summary ............................................................................................12

2 Retrieving Data 13
The SELECT Statement........................................................................13
Retrieving Individual Columns..........................................................14
Retrieving Multiple Columns ............................................................16
Retrieving All Columns ....................................................................17
Summary ............................................................................................18

3 Sorting Retrieved Data 19

Sorting Data ......................................................................................19
Sorting by Multiple Columns ............................................................21
Sorting by Column Position ..............................................................22
Specifying Sort Direction ..................................................................23
Summary ............................................................................................25

4 Filtering Data 26
Using the WHERE Clause......................................................................26
The WHERE Clause Operators ..............................................................28
Summary ............................................................................................32

5 Advanced Data Filtering 33

Combining WHERE Clauses ................................................................33
Using the IN Operator........................................................................36
Using the NOT Operator ......................................................................38
Summary ............................................................................................39
iv Sams Teach Yourself SQL in Ten Minutes

6 Using Wildcard Filtering 40

Using the LIKE Operator ....................................................................40
Tips for Using Wildcards ..................................................................46
Summary ............................................................................................47

7 Creating Calculated Fields 48

Understanding Calculated Fields ......................................................48
Concatenating Fields..........................................................................49
Performing Mathematical Calculations ............................................55
Summary ............................................................................................56

8 Using Data Manipulation Functions 57

Understanding Functions ..................................................................57
Using Functions ................................................................................59
Summary ............................................................................................65

9 Summarizing Data 66
Using Aggregate Functions................................................................66
Aggregates on Distinct Values ..........................................................72
Combining Aggregate Functions ......................................................73
Summary ............................................................................................74

10 Grouping Data 75
Understanding Data Grouping ..........................................................75
Creating Groups ................................................................................76
Filtering Groups ................................................................................78
Grouping and Sorting ........................................................................80
SELECT Clause Ordering ....................................................................83
Summary ............................................................................................83

11 Working with Subqueries 84

Understanding Subqueries ................................................................84
Filtering by Subquery ........................................................................84
Using Subqueries As Calculated Fields ............................................88
Summary ............................................................................................90
 Contents v

12 Joining Tables 91
Understanding Joins ..........................................................................91
Creating a Join ..................................................................................94
Summary ..........................................................................................100

13 Creating Advanced Joins 101

Using Table Aliases ........................................................................101
Using Different Join Types ..............................................................102
Using Joins with Aggregate Functions ............................................108
Using Joins and Join Conditions ....................................................110
Summary ..........................................................................................110

14 Combining Queries 111

Understanding Combined Queries ..................................................111
Creating Combined Queries ............................................................112
Summary ..........................................................................................117

15 Inserting Data 118

Understanding Data Insertion ..........................................................118
Copying from One Table to Another ..............................................124
Summary ..........................................................................................126

16 Updating and Deleting Data 127

Updating Data ..................................................................................127
Deleting Data ..................................................................................129
Guidelines for Updating and Deleting Data ....................................131
Summary ..........................................................................................132

17 Creating and Manipulating Tables 133

Creating Tables ................................................................................133
Updating Tables ..............................................................................139
Deleting Tables ................................................................................141
Renaming Tables..............................................................................141
Summary ..........................................................................................142
vi Sams Teach Yourself SQL in Ten Minutes

18 Using Views 143

Understanding Views ......................................................................143
Creating Views ................................................................................146
Summary ..........................................................................................152

19 Working with Stored Procedures 153

Understanding Stored Procedures....................................................153
Why to Use Stored Procedures........................................................154
Executing Stored Procedures ..........................................................156
Creating Stored Procedures ............................................................157
Summary ..........................................................................................160

20 Managing Transaction Processing 161

Understanding Transaction Processing............................................161
Controlling Transactions..................................................................164
Summary ..........................................................................................167

21 Using Cursors 168

Understanding Cursors ....................................................................168
Working with Cursors......................................................................170
Summary ..........................................................................................173

22 Understanding Advanced SQL Features 174

Understanding Constraints ..............................................................174
Understanding Indexes ....................................................................180
Understanding Triggers....................................................................183
Database Security ............................................................................185
Summary ..........................................................................................186

A Sample Table Scripts 187

Understanding the Sample Tables ..................................................187
Obtaining the Sample Tables ..........................................................191

B Working in Popular Applications 194

Using Aqua Data Studio ..................................................................195
Using DB2 ......................................................................................195
Using Macromedia ColdFusion ......................................................196
 Contents vii

Using Microsoft Access ..................................................................197

Using Microsoft ASP ......................................................................199
Using Microsoft ASP.NET ..............................................................199
Using Microsoft Query ....................................................................200
Using Microsoft SQL Server ..........................................................201
Using MySQL..................................................................................202
Using Oracle ....................................................................................202
Using PHP........................................................................................203
Using PostgreSQL ..........................................................................203
Using Query Tool ............................................................................203
Using Sybase....................................................................................204
Configuring ODBC Data Sources ..................................................205

C SQL Statement Syntax 207

ALTER TABLE ....................................................................................207
COMMIT ..............................................................................................208
CREATE INDEX ..................................................................................208
CREATE PROCEDURE ..........................................................................208
CREATE TABLE ..................................................................................208
CREATE VIEW ....................................................................................209
DELETE ..............................................................................................209
DROP ..................................................................................................209
INSERT ..............................................................................................209
INSERT SELECT ................................................................................210
ROLLBACK ..........................................................................................210
SELECT ..............................................................................................210
UPDATE ..............................................................................................211

D Using SQL Datatypes 212

String Datatypes ..............................................................................213
Numeric Datatypes ..........................................................................215
Date and Time Datatypes ................................................................216
Binary Datatypes..............................................................................217

E SQL Reserved Words 219

Index 225
Introduction
SQL is the most widely used database language. Whether you are an
application developer, database administrator, Web application designer,
or Microsoft Office user, a good working knowledge of SQL is an impor-
tant part of interacting with databases.
This book was born out of necessity. I had been teaching Web application
development for several years, and students were constantly asking for
SQL book recommendations. There are lots of SQL books out there.
Some are actually very good. But they all have one thing in common: for
most users they teach just too much information. Instead of teaching SQL
itself most books teach everything from database design and normaliza-
tion to relational database theory and administrative concerns. And while
those are all important topics, they are not of interest to most of us who
just need to learn SQL.
And so, not finding a single book that I felt comfortable recommending, I
turned that classroom experience into the book you are holding. Sams
Teach Yourself SQL in 10 Minutes will teach you SQL you need to know,
starting with simple data retrieval and working on to more complex topics
including the use of joins, subqueries, stored procedures, cursors, triggers,
and table constraints. You’ll learn methodically, systematically, and sim-
ply—in lessons that will each take 10 minutes or less to complete.
Now in its third edition, this book has taught SQL to hundreds of thou-
sands of users, and now it is your turn. So turn to Lesson 1, and get to
work. You’ll be writing world class SQL in no time at all.

Who is the Teach Yourself SQL

Book For?
This book is for you if
• You are new to SQL.
• You want to quickly learn how to get the most out of SQL.
2 Sams Teach Yourself SQL in Ten Minutes

• You want to learn how to use SQL in your own application

development.
• You want to be productive quickly and easily in SQL without
having to call someone for help.

DBMSs Covered in This Book

For the most part, the SQL taught in this book will apply to any Database
Management System (DBMS). However, as all SQL implementations are
not created equal, the following DBMSs are explicitly covered (and spe-
cific instructions or notes are included where needed):
• IBM DB2
• Microsoft Access
• Microsoft SQL Server
• MySQL
• Oracle
• PostgreSQL
• Sybase Adaptive Server

Example databases and SQL scripts are also available for all of these
DBMSs.

Conventions Used in This Book

This book uses different typefaces to differentiate between code and regu-
lar English, and also to help you identify important concepts.
Text that you type and text that should appear on your screen is presented
in monospace type.
It will look like this to mimic the way text looks on your
screen.
 Introduction 3

Placeholders for variables and expressions appear in monospace italic

font. You should replace the placeholder with the specific value it repre-
sents.
This arrow (➥) at the beginning of a line of code means that a single line
of code is too long to fit on the printed page. Continue typing all the char-
acters after the ➥ as though they were part of the preceding line.

A Note presents interesting pieces of information

related to the surrounding discussion.

A Tip offers advice or teaches an easier way to do

something.

A Caution advises you about potential problems and

helps you steer clear of disaster.

New Term icons provide clear definitions of new, essential

terms.

INPUT The Input icon identifies code that you can type in yourself.

The Output icon highlights the output produced by running a

OUTPUT program.
The Analysis icon alerts you to the author’s line-by-line
ANALYSIS analysis of a program.
This page intentionally left blank
LESSON 3
Sorting
Retrieved
Data
In this lesson, you will learn how to use the SELECT statement’s ORDER BY
clause to sort retrieved data as needed.

Sorting Data
As you learned in the last lesson, the following SQL statement returns a
single column from a database table. But look at the output. The data
appears to be displayed in no particular order at all.
SELECT prod_name
INPUT FROM Products;

prod_name
OUTPUT --------------------
Fish bean bag toy
Bird bean bag toy
Rabbit bean bag toy
8 inch teddy bear
12 inch teddy bear
18 inch teddy bear
Raggedy Ann
King doll
Queen doll

Actually, the retrieved data is not displayed in a mere random order. If

unsorted, data will typically be displayed in the order in which it appears
in the underlying tables. This could be the order in which the data was
added to the tables initially. However, if data was subsequently updated or
deleted, the order will be affected by how the DBMS reuses reclaimed
20 Lesson 3

storage space. The end result is that you cannot (and should not) rely on
the sort order if you do not explicitly control it. Relational database
design theory states that the sequence of retrieved data cannot be assumed
to have significance if ordering was not explicitly specified.
Clause SQL statements are made up of clauses, some required
and some optional. A clause usually consists of a keyword and
supplied data. An example of this is the SELECT statement’s FROM clause,
which you saw in the last lesson.
To explicitly sort data retrieved using a SELECT statement, the ORDER BY
clause is used. ORDER BY takes the name of one or more columns by
which to sort the output. Look at the following example:
SELECT prod_name
INPUT FROM Products
ORDER BY prod_name;

This statement is identical to the earlier statement, except it

ANALYSIS
also specifies an ORDER BY clause instructing the Database
Management System software to sort the data alphabetically by the
prod_name column. The results are as follows:

prod_name
OUTPUT --------------------
12 inch teddy bear
18 inch teddy bear
8 inch teddy bear
Bird bean bag toy
Fish bean bag toy
King doll
Queen doll
Rabbit bean bag toy
Raggedy Ann

Position of ORDER BY Clause When specifying an

ORDER BY clause, be sure that it is the last clause in
your SELECT statement. Using clauses out of order will
generate an error message.
 Sorting Retrieved Data 21

Sorting by Nonselected Columns More often than

not, the columns used in an ORDER BY clause will be
ones that were selected for display. However, this is
actually not required, and it is perfectly legal to sort
data by a column that is not retrieved.

Sorting by Multiple Columns

It is often necessary to sort data by more than one column. For example,
if you are displaying an employee list, you might want to display it sorted
by last name and first name (first by last name, and then within each last
name sort by first name). This would be useful if there are multiple
employees with the same last name.
To sort by multiple columns, simply specify the column names separated
by commas (just as you do when you are selecting multiple columns).
The following code retrieves three columns and sorts the results by two of
them—first by price and then by name.
SELECT prod_id, prod_price, prod_name
INPUT FROM Products
ORDER BY prod_price, prod_name;

prod_id prod_price prod_name

OUTPUT ------- ---------- --------------------
BNBG02 3.4900 Bird bean bag toy
BNBG01 3.4900 Fish bean bag toy
BNBG03 3.4900 Rabbit bean bag toy
RGAN01 4.9900 Raggedy Ann
BR01 5.9900 8 inch teddy bear
BR02 8.9900 12 inch teddy bear
RYL01 9.4900 King doll
RYL02 9.4900 Queen doll
BR03 11.9900 18 inch teddy bear

It is important to understand that when you are sorting by multiple

columns, the sort sequence is exactly as specified. In other words, using
the output in the example above, the products are sorted by the prod_name
column only when multiple rows have the same prod_price value. If all
22 Lesson 3

the values in the prod_price column had been unique, no data would
have been sorted by prod_name.

Sorting by Column Position

In addition to being able to specify sort order using column names, ORDER
BY also supports ordering specified by relative column position. The best
way to understand this is to look at an example:
SELECT prod_id, prod_price, prod_name
INPUT FROM Products
ORDER BY 2, 3;

prod_id prod_price prod_name

OUTPUT ------- ---------- --------------------
BNBG02 3.4900 Bird bean bag toy
BNBG01 3.4900 Fish bean bag toy
BNBG03 3.4900 Rabbit bean bag toy
RGAN01 4.9900 Raggedy Ann
BR01 5.9900 8 inch teddy bear
BR02 8.9900 12 inch teddy bear
RYL01 9.4900 King doll
RYL02 9.4900 Queen doll
BR03 11.9900 18 inch teddy bear

As you can see, the output is identical to that of the query

ANALYSIS
above. The difference here is in the ORDER BY clause. Instead
of specifying column names, the relative positions of selected columns in
the SELECT list are specified. ORDER BY 2 means sort by the second col-
umn in the SELECT list, the prod_price column. ORDER BY 2, 3 means
sort by prod_price and then by prod_name.
The primary advantage of this technique is that it saves retyping the col-
umn names. But there are some downsides too. First, not explicitly listing
column names increases the likelihood of you mistakenly specifying the
wrong column. Second, it is all too easy to mistakenly reorder data when
making changes to the SELECT list (forgetting to make the corresponding
changes to the ORDER BY clause). And finally, obviously you cannot use
this technique when sorting by columns that are not in the SELECT list.
 Sorting Retrieved Data 23

Sorting by Nonselected Columns Obviously, this

technique cannot be used when sorting by columns
that do not appear in the SELECT list. However, you
can mix and match actual column names and relative
column positions in a single statement if needed.

Specifying Sort Direction

Data sorting is not limited to ascending sort orders (from A to Z).
Although this is the default sort order, the ORDER BY clause can also be
used to sort in descending order (from Z to A). To sort by descending
order, the keyword DESC must be specified.
The following example sorts the products by price in descending order
(most expensive first):
SELECT prod_id, prod_price, prod_name
INPUT FROM Products
ORDER BY prod_price DESC;

prod_id prod_price prod_name

OUTPUT ------- ---------- --------------------
BR03 11.9900 18 inch teddy bear
RYL01 9.4900 King doll
RYL02 9.4900 Queen doll
BR02 8.9900 12 inch teddy bear
BR01 5.9900 8 inch teddy bear
RGAN01 4.9900 Raggedy Ann
BNBG01 3.4900 Fish bean bag toy
BNBG02 3.4900 Bird bean bag toy
BNBG03 3.4900 Rabbit bean bag toy

But what if you were to sort by multiple columns? The following example
sorts the products in descending order (most expensive first), plus product
name:
SELECT prod_id, prod_price, prod_name
INPUT FROM Products
ORDER BY prod_price DESC, prod_name;
24 Lesson 3

prod_id prod_price prod_name

OUTPUT ------- ---------- --------------------
BR03 11.9900 18 inch teddy bear
RYL01 9.4900 King doll
RYL02 9.4900 Queen doll
BR02 8.9900 12 inch teddy bear
BR01 5.9900 8 inch teddy bear
RGAN01 4.9900 Raggedy Ann
BNBG02 3.4900 Bird bean bag toy
BNBG01 3.4900 Fish bean bag toy
BNBG03 3.4900 Rabbit bean bag toy

The DESC keyword only applies to the column name that

ANALYSIS
directly precedes it. In the example above, DESC was speci-
fied for the prod_price column, but not for the prod_name column.
Therefore, the prod_price column is sorted in descending order, but the
prod_name column (within each price) is still sorted in standard ascending
order.

Sorting Descending on Multiple Columns If you

want to sort descending on multiple columns, be sure
each column has its own DESC keyword.

It is worth noting that DESC is short for DESCENDING, and both keywords
may be used. The opposite of DESC is ASC (or ASCENDING), which may be
specified to sort in ascending order. In practice, however, ASC is not usu-
ally used because ascending order is the default sequence (and is assumed
if neither ASC nor DESC are specified).
 Sorting Retrieved Data 25

Case Sensitivity and Sort Orders When you are sort-

ing textual data, is A the same as a? And does a come
before B or after Z? These are not theoretical ques-
tions, and the answers depend on how the database is
set up.

In dictionary sort order, A is treated the same as a, and

that is the default behavior for most Database
Management Systems. However, most good DBMSs
enable database administrators to change this behav-
ior if needed. (If your database contains lots of for-
eign language characters, this might become
necessary.)

The key here is that if you do need an alternate sort

order, you cannot accomplish it with a simple ORDER
BY clause. You must contact your database admini-
strator.

Summary
In this lesson, you learned how to sort retrieved data using the SELECT
statement’s ORDER BY clause. This clause, which must be the last in the
SELECT statement, can be used to sort data on one or more columns as
needed.
INDEX

SYMBOLS pass-through mode, 198

running, 197-198
% (percent sign) wildcard, 41-42 sorting by alias, 82
[] (square brackets) wildcard, 44-46 stored procedure support, 154
^ (caret) wildcard character, 45 aggregate functions
_ (underscore) wildcard, 43-44 ALL argument, 72
| (pipe) symbol, 207 AVG( ), 67-68
|| (double pipes), concatenation oper- combining, 73-74
ator, 50 COUNT( ), 68-69
’ (single quotation mark), WHERE defined, 67
clause operators and, 30 DISTINCT argument, 72-73
* (asterisk) wildcard, queries, 17-18 joins and, 108-110
*= (equality operator), 107 MAX( ), 69
+ (concatenation operator), 50 MIN( ), 70
+ (plus sign operator), outer joins, naming aliases, 74
107 overview, 66
, (comma), multiple column separa- SUM( ), 71
tion, 16 aliases
@ (at symbol) character, 158 alternative uses, 54
@@ERROR variable, 167 columns, creating, 101
@@IDENTITY global variable, 160 concatenating fields, 53-54
names, 54
aggregate functions and,
A 74
self joins, 102-104
ABS( ) function, 64 table names, 101-102
Access (Microsoft) ALL argument, aggregate functions,
DISTINCT argument support, 72
72 ALL clause, grouping data, 77
example tables for, 192 alphabetical sort order, 23-24
226 SAMS TEACH YOURSELF SQL IN TEN MINUTES

ALTER TABLE statements, 139-140 authentication, 185

CHECK constraints, 180 authorization, 185
CONSTRAINT syntax, 177 AVG( ) function, 67-68
syntax, 207 DISTINCT argument, 72
AND keyword, 31 NULL values, 68
AND operator, 33-34
ANSI SQL, 12
applications B
filtering query results, 27
SQL compatibility BETWEEN operator, 63
Aqua Data Studio, 195 WHERE clause, 30
ColdFusion (Macromedia), between specified values operator
196 (WHERE clause), 28
DB2 (IBM), 195-196 BINARY datatype, 217
Microsoft Access, 197-198 BIT datatype, 215
Microsoft ASP, 199
Microsoft ASP.NET,
199-200 C
Microsoft Query, 200-201
Microsoft SQL Server, 201 calculated fields
MySQL, 202 concatenating fields, 49-52
ODBC configuration, column aliases, 53-54
205-206 mathematical calculations,
Oracle, 202 55-56
PHP scripting language, overview, 48-49
203 subqueries, 88-90
PostgreSQL, 203 views, 151-152
Query Tool, 203-204 calculated values, totaling, 71
selection criteria, 194 Cartesian Product, joins and, 95-97
Sybase Adaptive Server, cascading deletes, 178
204-205 case sensitivity
Aqua Data Studio query result sort order, 25
running, 195 SQL statements, 16
Web site, 194 CHAR string datatype, 214
arguments characters, searching for
ALL, aggregate functions, 72 % (percent sign) wildcard,
DBMS support, 73 41-42
DISTINCT, aggregate func- [ ] (square brackets) wildcard,
tions, 72-73 44-46
AS keyword, 53-54 _ (underscore) wildcard, 43-44
Oracle support, 102 check constraints, 179-180
ASC keyword, query results sort clauses, 20
order, 24 ALL, grouping data, 77
ASP (Microsoft), running, 199 GROUP BY, 76-77
ASP.NET (Microsoft), running, HAVING, grouping data, 78
199-200
 INDEX 227

IS NULL, 31 derived, 54
SELECT statements, order of, fully qualified names, 95
83 GROUP BY clause, 77
WHERE, 26 grouping data, specifying by
AND operator, 33-34 relative position, 77
checking against single Identity fields, 160
value, 29 INSERT SELECT statements,
checking for nonmatches, 124
29-30 INSERT statement, omitting
checking for NULL value, columns, 122
31 insert STATEMENT AND, 120
checking for range of val- multiple, sorting query results
ues, 30-31 by, 21-22
IN operator, 36-38 nonselected, sorting query
joins and, 95-97 results by, 23
multiple query criteria, 33 NULL value, checking for, 31
NOT operator, 38-39 NULL value columns, 136-137
operator support by OrderItems example table, 191
DBMS, 30 Orders example table, 190
operators, 28 padded spaces, RTRIM( ) func-
OR operator, 34-35 tion, 51-52
order of evaluation, 35-36 position, sorting query results
positioning, 27 by, 22
SOUNDEX function, 61 primary keys, 10
client-based results formatting, com- Products example table, 189
pared to server-based, 49 retrieving
CLOSE statements, closing cursors, all, 17-18
173 individual, 14-15
code (programming) multiple, 16-17
commenting, 59 unknown, 18
stored procedures, 159 separating names in queries, 16
portability, 58 sorting data, descending on
ColdFusion (Macromedia), running, multiple columns, 24
196 subquery result restrictions, 88
columns. See also fields updating multiple, 128
aliases values, deleting, 129
alternative uses, 54 Vendors example table, 188
concatenating fields, 53-54 combined queries
creating, 101 creating, 112-113
names, 54 duplicate rows and, 114-115
AVG( ) function, individual overview, 111
columns, 68 performance, 114
breaking data correctly, 8 rules, 114
concepts, 8 sorting results, 116
Customers example table, 189

How can we make this index more useful? Email us at [email protected]

228 SAMS TEACH YOURSELF SQL IN TEN MINUTES

Command Center utility, running, CREATE TABLE statement, 133-135

195-196 DEFAULT keyword, 137-138
commas (,), multiple column separa- syntax, 208
tion, 16 CREATE VIEW statement, 146
commenting syntax, 209
programming code, importance cross joins, 97
of, 59 currency datatypes, 216
stored procedure code, 159 cursors
COMMIT statement (transaction accessing, 171-173
processing), 165 closing, 173
commits defined, 163 creating, 170-171
syntax, 208 implementing, 170
compatibility limitations, 169
datatypes, 9 opening, 171
functions, DBMS support con- options, support for, 168
siderations, 57-58 overview, 168
WHERE clause operators, 28 Web-based applications, 169
compatibility (SQL code), applica- Customers table, 189
tion selection criteria, 194
concatenating fields, 49-52
column aliases, 53-54 D
mathematical calculations,
55-56 data
MySQL, 51 breaking correctly (columns), 8
concatenation operators, 50 deleting
configuring ODBC, 205-206 guidelines, 131
CONSTRAINT syntax, ALTER TRUNCATE TABLE state-
TABLE statements, 177 ment, 131
constraints (referential integrity) filtering, indexes and, 182
check constraints, 179-180 manipulation functions, date
foreign keys, 176-177 and time, 63
overview, 174-175 security, 185
primary keys, 175-176 updating, guidelines, 131
speed, 184 Database Management System. See
unique constraints, 178-179 DBMS
copying tables, 126 databases. See also tables
COS( ) function, 64 concepts, 5-6
COUNT( ) function, 67-69 defined, 6
DISTINCT argument, 73 dropping objects, 209
joins and, 108 indexes
NULL values, 69 cautions, 182
COUNT* subquery, 89 creating, 182
CREATE INDEX statement, 182 scalability, 93
syntax, 208 schemas, 7
 INDEX 229

security, 185 interactive tools, 93

tables ISAM databases, 169
creating, 208 LIKE operator, search patterns
triggers, 183 and, 41
DATALENGTH( ) function, 60 NULL value differences, 137
datatypes, 8 query sort order, 20
binary, 217 security mechanisms, 185
compatibility, 9 SQL extensions, 12
currency, 216 transaction processing, imple-
data and time, 216 mentation differences, 164
defining, 180 triggers, 184
numeric, 215 TRIM functions, 52
string, 213 UNION statements, 117
usefulness of, 212 user-defined datatypes, 180
BIT, 215 view creation, 144
CHAR, 217 views, rules and restrictions,
compatibility, 9 145
currency, 216 WHERE clause, allowed opera-
DATE, 216 tors, 32
DATETIME, 216 DECIMAL datatype, 215
DBMS differences, 213 DECLARE statements
DECIMAL, 215 cursors, creating, 170-171
defining, 180 stored procedures, 158
INT, 215 default values, tables, 137-138
NCHAR, 214 defining datatypes, 180
NVARCHAR, 214 DELETE FROM statements, 130
TEXT, 214 DELETE statement, 129-130
TINYINT, 215 FROM keyword, 130
user-defined, 180 guidelines, 131
VARBINARY, 217 security privileges, 130
date (system), default value syntax, syntax, 209
138 transaction processing, 163
date and time datatypes, 216 TRUNCATE TABLE statement,
date and time functions, 59, 62-64 131
DATE datatype, 216 DELETE statements
DATEPART( ) function, 62 rollbacks, 165
DATETIME datatype, 216 triggers, 183
DB2 (IBM), running, 195-196 WHERE clause, 130
DBMS (Database Management deleting
System), 6 column values, 129
accidental table deletion, 141 data
datatype differences, 213 guidelines, 131
functions, support considera- TRUNCATE TABLE state-
tions, 57-58 ment, 131
indexes, 182

How can we make this index more useful? Email us at [email protected]

230 SAMS TEACH YOURSELF SQL IN TEN MINUTES

rows, 209 EXP( ) function, 64

preventing accidental, 178 explicit commits, 165
tables, 141 extensions, 12
preventing accidental, 141
derived columns. See aliases
DESC keyword, query results sort F
order, 23-24
dictionary sort order (query results), FETCH statement, accessing cursors,
25 171-173
DISTINCT argument fields. See also calculated fields;
AVG( ) function, 72 columns
COUNT( ) function, 73 aliases, names, 54
double pipes (||), concatenation oper- calculated
ator, 50 concatenating fields, 49-54
downloading example tables, 191 mathematical calculations,
Microsoft Access MDB file, 55-56
192 overview, 48-49
SQL scripts, 192 subqueries, 88-90
DROP statement, syntax, 209 views, 151-152
DROP TABLE statement, 141 filtering
dropping database objects, 209 % (percent sign) wildcard,
41-42
[] (square brackets) wildcard,
E 44-46
_ (underscore) wildcard, 43-44
empty strings, compared to NULL data, indexes, 182
values, 137 data groups, 78-80
equality (*=) operator, 107 LIKE operator, 40-41
equality operator (WHERE clause), query results, 26
28 AND operator, 33-34
establishing primary keys, 10 application level, 27
example tables IN operator, 36-38
Customers table, 189 multiple criteria, 33
downloading, 191 NOT operator, 38-39
functions of, 187 OR operator, 34-35
Microsoft Access MDB file, order of evaluation, 35-36
192 WHERE clause operators,
OrderItems table, 191 28-31
Orders table, 190 by subqueries, 84-88
Products table, 189 with views, 150
SQL scripts, 192 fixed length strings, 213
Vendors table, 188 FLOAT datatype, 215
EXCEPT statement, 117 foreign keys, 176-177
EXECUTE statement, stored proce-
dures, 156-160
 INDEX 231

formatting LEN( ), 60
query data, 17 LENGTH( ), 60
retrieved data with views, LOWER( ), 60
148-149 LTRIM( ), 60
server-based compared to MAX( ), 67-69
client-based, 49 DISTINCT argument, 73
statements, 135 non-numeric data, 70
subqueries, 87 NULL values, 70
FROM clause, creating joins, 94 MIN( ), 67, 70
FROM keyword, 14 DISTINCT argument, 73
DELETE statement, 130 non-numeric data, 70
UPDATE statement, 129 NULL values, 71
full outer joins, 108 numeric, 59, 64-65
fully qualified column names, 95 PI( ), 65
functions RIGHT( ), 60
ABS( ), 64 RTRIM( ), 51-52, 60
advisability of using, 59 SIN( ), 65
aggregate SOUNDEX( ), 60-61
ALL argument, 72 support for, 61
AVG( ), 67-68 SQRT( ), 65
combining, 73-74 SUM( ), 67, 71
COUNT( ), 68-69 multiple columns and, 72
defined, 67 NULL values, 72
DISTINCT argument, support considerations, 57-58
72-73 system, 59
joins and, 108-110 TAN( ), 65
MAX( ), 69 text, 59-60
MIN( ), 70 list of common, 60
naming aliases, 74 to_char, 63
overview, 66 to_number, 63
SUM( ), 71 TRIM, 52
AVG( ), 67-68 types of, 59
DISTINCT argument, 72 UCASE( ), 60
NULL values, 68 UPPER( ), 59-60
COS( ), 64 YEAR( ), 63
COUNT( ), 67-69
DISTINCT argument, 73
NULL values, 69 G
DATALENGTH( ), 60
date and time, 59, 62-64 global variables, @@IDENTITY,
DATEPART( ), 62 160
defined, 57 GRANT statement, 185
EXP( ), 64 greater than operator (WHERE
LCASE( ), 60 clause), 28
LEFT( ), 60

How can we make this index more useful? Email us at [email protected]

232 SAMS TEACH YOURSELF SQL IN TEN MINUTES

greater than or equal to operator transaction processing, 163

(WHERE clause), 28 triggers, 183
GROUP BY clause, 76-77 VALUES, 121
compared to ORDER BY INT datatype, 215
clause, 80-82 integrity. See referential integrity
grouping interactive DBMS tools, 93
data, 75 INTERSECT statements, 117
columns, specifying by INTO keyword, 119
position, 77 IS NULL clause, 31
compared to sorting, 80-82 ISAM (Indexed Sequential Access
filtering groups, 78-80 Method) databases, 169
GROUP BY clause, 76-77 ISTINCT argument, aggregate func-
nested groups, 76 tions, 72-73
operators, 35

J-K
H-I
joins
HAVING clause, grouping data, 78 aggregate functions and,
108-110
IBM DB2, running, 195-196 Cartesian Product, 95-97
Identity fields, 160 creating, 94
IN operator, 36-38 cross joins, 97
indexes inner joins, 97-98
cautions, 182 multiple tables, 98-100
creating, 182, 208 natural joins, 104-105
overview, 180-182 outer, 105-108
revisiting, 182 full, 108
inner joins, 97-98 left, 107
INSERT SELECT statement, right, 107
122-124 syntax, 107
SELECT INTO statement com- types, 108
parison, 125 overview, 91
syntax, 210 performance considerations, 99
INSERT statement self joins, 102-104
columns lists, 121 usefulness of, 93
completing rows, 118-120 views, 147-148
INTO keyword, 119 WHERE clause, 95-97
omitting columns, 122
overview, 118 keys, primary, 9-11
partial rows, 121-122 keywords, 13
query data, 122-124 AND, 31, 34
rollbacks, 167 AS, 53-54
security privileges, 118 Oracle support, 102
syntax, 209 ASC, query results sort order,
24
 INDEX 233

DEFAULT, table values, MAX( ) function, 67-69

137-138 DISTINCT argument, 73
DESC, query results sort order, non-numeric data, 70
23-24 NULL values, 70
FROM, 14, 129 Microsoft Access
IN, 38 DISTINCT argument support,
INTO, 119 72
NOT, 38 example tables for, 192
OR, 35 pass-through mode, 198
REFERENCES, 177 running, 197-198
UNIQUE, 179 sorting by alias, 82
stored procedure support, 154
Microsoft ASP, running, 199
L Microsoft ASP.NET, running,
199-200
languages, SQL, 11 Microsoft Query, running, 200-201
LCASE( ) function, 60 Microsoft SQL Server, running, 201
LEFT keyword (outer joins), 106 MIN( ) function, 67, 70
left outer joins, 107 DISTINCT argument, 73
LEFT( ) function, 60 non-numeric data, 70
LEN( ) function, 60 NULL values, 71
LENGTH( ) function, 60 MySQL
less than operator (WHERE clause), concatenation, 51
28 cursor support, 168
less than or equal to operator NOT operator, 39
(WHERE clause), 28 running, 202
LIKE operator, 40-41 stored procedure support, 154
% (percent sign) wildcard, subquery support, 84
41-42 views, support for, 143
[] (square brackets), 44-46
_ (underscore) wildcard, 43-44
local variables, @ character, 158 N
logical operators, defined, 33
LONG RAW datatype, 217 naming
LOWER( ) function, 60 aliases, 54
LTRIM( ) function, 52, 60 aggregate functions and,
74
columns, fully qualified names,
M 95
indexes, 182
Macromedia ColdFusion, running, tables, 7
196 reserved words and, 13
mathematical aliases, 101-102
calculations, 55-56 natural joins, 104-105
operators, 56 navigating tables, cursors, 168

How can we make this index more useful? Email us at [email protected]

234 SAMS TEACH YOURSELF SQL IN TEN MINUTES

NCHAR string datatype, 214 operators

nested data groups, 76 *= (equality), 107
non-equality operator (WHERE % (percent sign) wildcard,
clause), 28 41-42
non-numeric data + (plus sign), outer joins, 107
MAX( ) function, 70 [] (square brackets) wildcard,
MIN( ) function, 70 44-46
not greater than operator (WHERE _ (underscore) wildcard, 43-44
clause), 28 AND, 33-34
not less than operator (WHERE BETWEEN, 63
clause), 28 concatenation, 50
NOT operator, 38-39 defined, 33
character searching and, 46 grouping related, 35
NULL keyword, updating columns, HAVING clause, 78
129 IN, 36-38
NULL value operator (WHERE LIKE, 40-41
clause), 28 mathematical, 56
NULL values NOT, 38-39
AVG( ) function, 68 OR, 34-35
checking for, 31 order of evaluation, 35-36
compared to empty strings, 137 predicates, 41
COUNT( ) function, 69 WHERE clause, 28
MAX( ) function, 70 checking against single
MIN( ) function, 71 value, 29
primary keys, 137 checking for nonmatches,
SUM( ) function, 72 29-30
table columns, 136-137 checking for NULL value,
numeric 31
datatypes, 215 checking for range of val-
functions, 59, 64-65 ues, 30-31
values, quotes, 215 compatibility, 28
NVARCHAR string datatype, 214 OR operator, 34-35
Oracle
commits, 165
O cursors
closing, 173
ODBC creating, 170
configuration, 205-206 retrieving data, 171
dates, 217 date and time manipulation
OPEN CURSOR statement, 171 functions, 63
OPEN statement, opening cursors, date formatting, 64
173 running, 202
savepoints, 166
stored procedures, 157
triggers, 184
 INDEX 235

ORDER BY clause (SELECT state- pipe (|) symbol, 207

ment), 20 placeholders. See savepoints
ascending/descending sort plus sign (+)
order, 23-24 concatenation operator, 50
compared to GROUP BY outer joins, 107
clause, 80-82 portability
positioning, 20 defined, 58
sorting by column position, 22 INSERT statements and, 121
sorting by multiple columns, PostgreSQL
21-22 filter query data, 27
sorting by nonselected columns, running, 203
23 predicates (operators), 41
OrderItems table, 191 primary keys, 175-176
Orders table, 190 concepts, 9-11
outer joins, 105-108 Customer example table, 190
full, 108 importance, 10
left, 107 NULL values, 137
right, 107 OrderItems example table, 191
syntax, 106-107 Orders example table, 190
types, 108 Products example table, 189
overwriting tables, 135 Vendors example table, 188
processing
subqueries, 86
P transactions, 161
Products table, 189
parentheses, multiple query criteria programming code
order of evaluation, 35 commenting, 59
pass-through mode (Microsoft portability, 58
Access), 198
patterns (searching), wildcards,
40-41 Q
% (percent sign), 41-42
[] (square brackets), 44-46 queries
_ (underscore), 43-44 calculated fields
percent sign (%) wildcard, 41-42 concatenating fields, 49-54
performance mathematical calculations,
combining queries, 114 55-56
deleting data, 131 overview, 48-49
indexes, 181 combined
joins and, 99 creating, 112-113
subqueries, 88 duplicate rows and,
views, 145 114-115
PHP scripting language, running, overview, 111
203 performance, 114
PI( ) function, 65 rules, 114

How can we make this index more useful? Email us at [email protected]

236 SAMS TEACH YOURSELF SQL IN TEN MINUTES

sorting results, 116 R

WHERE clauses, 111
combining, 86 RAW datatype, 217
data formatting, 17 REAL datatype, 215
defined, 84 records, compared to rows, 9
filtering results, 26 REFERENCES keyword, 177
AND operator, 33-34 referential integrity
IN operator, 36-38 cascading deletes, 178
multiple criteria, 33 constraints
NOT operator, 38-39 check constraints, 179-180
OR operator, 34-35 foreign keys, 176-177
order of evaluation, 35-36 overview, 174-175
WHERE clause operators, primary keys, 175-176
28-31 unique constraints,
INSERT statement and, 178-179
122-124 natural joins, 104-105
multiple WHERE clauses, 113 outer joins, 105-108
result sets, 168 self joins, 102-104
sorting results, 19-20 reformatting retrieved data with
ascending/descending views, 148-149
order, 23-24 relational databases (DBMS)
by column position, 22 nonrelational behavior, induc-
by multiple columns, 21-22 ing, 169
by nonselected columns, sort order and, 20
21, 23 relational tables, 91-92
case sensitivity, 25 relationships
subqueries constraints
as calculated fields, 88-90 check constraints, 179-180
filtering by, 84-88 foreign keys, 176-177
overview, 84 overview, 174-175
processing, 86 primary keys, 175-176
self joins and, 103 unique constraints,
table aliases, 102 178-179
unsorted results, 15 natural joins, 104-105
views, 144 outer joins, 105-108
wild cards (*), 17-18 self joins, 102-104
Query (Microsoft), running, 200-201 RENAME statement, 141
Query Tool renaming tables, 141
running, 203-204 reserved words, 13, 219
Web site, 194 list of, 220-224
quotation marks restrictions, views, 145-146
numeric values, 215 result sets, 168
single (‘), 30 reusable views, creating, 148
string values, 214 revisiting indexes, 182
 INDEX 237

REVOKE statements, 185 search patterns

RIGHT keyword (outer joins), 106 defined, 40
right outer joins, 107 wildcards, 40-41
RIGHT( ) function, 60 % (percent sign) wildcard,
ROLLBACK command (transaction 41-42
processing), 164-165 [] (square brackets) wild-
ROLLBACK statement, syntax, 210 card, 44-46
rollbacks (transaction processing), _ (underscore) wildcard,
163 43-44
COMMIT statement, 165 cautions, 46
defined, 163 searching
ROLLBACK command, indexes, overview, 180-182
164-165 trailing spaces and, 43
savebacks and, 166-167 wildcards
statements, 167 ^ (caret) character, 45
rows % character, 41-42
adding to tables, 209 [] (square brackets), 44-46
compared to records, 9 _ (underscore), 43-44
concepts, 9 security
cursors, 168 data, 185
deleting, 209 DELETE statement, 130
INSERT statement, 118-120 INSERT statements, 118
partial rows, 121-122 UPDATE statement, 127
preventing accidental deletion, SELECT INTO statement, 125
178 INSERT SELECT statement
updating, 211 comparison, 125
RTRIM( ) function, 51-52, 60 SELECT statement, 13
rules aggregate functions, combining,
combining queries, 114 73-74
constraints, 175 AS keyword, 53-54
views, 145-146 AVG( ) function, 67
clauses, ordering of, 83
columns
S retrieving all, 17-18
retrieving individual, 14-15
savepoints (transaction processing), retrieving multiple, 16-17
166-167 retrieving unknown, 18
defined, 163 combining
scalablity, 93 creating, 112-113
schemas, 7 duplicate rows and,
scripting, PHP, 203 114-115
scripts, example tables, 192 overview, 111
rules, 114
sorting results, 116
concatenating fields, 50-51

How can we make this index more useful? Email us at [email protected]

238 SAMS TEACH YOURSELF SQL IN TEN MINUTES

COUNT( ) function, 69 spaces

FROM clause, creating joins, removing, RTRIM function,
94 51-52
grouping data, 76-77 search results and, 43
IS NULL clause, 31 specifying dates, 216
ORDER BY clause, 20 speed, constraints versus triggers,
positioning, 20 184
syntax, 210 SQL
SELECT statements deleting/updating data, 132
subqueries, formatting, 87 extensions, 12
WHERE clause, 26 overview, 11
WHERE clauses scripts, example tables, 192
combined queries, 111 SQL Server
combining, 33 cursors, closing, 173
NOT operator, 38 Identity fields, 160
self joins, 102-104 local variables, @ character,
compared to subqueries, 104 158
semicolons (;), multiple statements, running, 201
16 savepoints, 166
sequence (SELECT statement stored procedures, 158
clauses), 83 triggers, 184
server-based results formatting, com- SQRT( ) function, 65
pared to client-based, 49 square brackets ([]) wildcard, 44-46
SET command, updating tables, 128 statements
SIN( ) function, 65 ALTER TABLE, 139-140
single quotation marks (‘) syntax, 207
WHERE clause operators and, case sensitivity, 16
30 clauses, 20
SMALLDATETIME datatype, 216 COMMIT, 165
SMALLINT datatype, 215 syntax, 208
sorting CREATE INDEX, 182
combined query results, 116 syntax, 208
compared to grouping, 80-82 CREATE TABLE, 133-135
datatype functionality, 212 syntax, 208
indexes, overview, 180-182 CREATE VIEW, 146
query results, 19-20 syntax, 209
ascending/descending DELETE, 129-131
order, 23-24 FROM keyword, 130
by column position, 22 syntax, 209
by multiple columns, 21-22 transaction processing, 163
by nonselected columns, DROP, syntax, 209
21-23 DROP TABLE, 141
case sensitivity, 25 formatting, 135
SOUNDEX( ) function, 60-61 GRANT, 185
support for, 61 grouping related operators, 35
 INDEX 239

INSERT UPDATE, 127-131

completing rows, 118-120 syntax, 211
omitting columns, 122 transaction processing, 163
overview, 118 white space, 15
partial rows, 121-122 stored procedures
query data, 122-124 commenting code, 159
security privileges, 118 creating, 157-160, 208
syntax, 209 disadvantages of, 155-156
transaction processing, executing, 156-157
163 Identity fields, 160
VALUES, 121 Oracle, 157
INSERT SELECT, syntax, 210 overview, 153-154
multiple, separating, 16 triggers, 183
OPEN CURSOR, 171 usefulness of, 154-155
RENAME, 141 storing
REVOKE, 185 date and time values, 216
ROLLBACK, syntax, 210 numeric values, cautions, 214
rollbacks, 163, 167 strings, 213
defined, 163 string datatypes, 213
SELECT, 13 strings
AVG( ) function, 67 empty, compared to NULL val-
combining, 111-116 ues, 137
combining aggregate func- fixed length, 213
tions, 73-74 quotes, 214
concatenating fields, 50-51 TRIM functions, 52
COUNT( ) function, 69 variable-length, 213
grouping data, 76-77 wildcard searching and, 41
retrieving all columns, subqueries
17-18 as calculated fields, 88-90
retrieving individual compared to self joins, 104
columns, 14-15 COUNT*, 89
retrieving multiple filtering by, 84-88
columns, 16-17 formatting, 87
retrieving unknown multiple columns, 72
columns, 18 NULL values, 72
syntax, 210 overview, 84
stored procedures performance, 88
creating, 157-160 processing, 86
disadvantages of, 155-156 self joins and, 103
executing, 156-157 SUM( ) function, 67, 71
overview, 153-154 UPDATE statement, 129
usefulness of, 154-155 WHERE clauses, 88
syntax, 207-211 support, DBMS function support,
57-58

How can we make this index more useful? Email us at [email protected]

240 SAMS TEACH YOURSELF SQL IN TEN MINUTES

Sybase Adaptive Server copying data into tables,

running, 204-205 124-126
statements, ending, 16 creating, 208
syntax CREATE TABLE statement,
ALTER TABLE statements, 134-135
207 overview, 133-134
column aliases, 101 datatypes, 8
COMMIT statement, 208 default values, 137-138
CREATE INDEX statement, deleting, 141
208 preventing accidental dele-
CREATE TABLE statement, tion, 141
133, 208 examples
CREATE VIEW statement, 209 Customers table, 189
DELETE statement, 209 downloading, 191
DROP statement, 209 indexes
INERT statement, 210 cautions, 182
INSERT statement, 209 creating, 182
outer joins, 106 searching, 181
ROLLBACK statement, 210 INSERT statement, multiple
SELECT statement, 210 rows, 124
statements, 207-211 inserting data, 118-120
transaction processing, 164 partial rows, 121-122
triggers, 184 from queries, 122-124
UPDATE statement, 211 joins
system date, default value syntax, Cartesian Product, 95-97
138 creating, 94
system functions, 59 cross joins, 97
inner joins, 97-98
multiple tables, 98-100
T overview, 91
performance considera-
tables tions, 99
calculated fields usefulness of, 93
concatenating fields, 49-54 WHERE clause, 95-97
mathematical calculations, Microsoft Access MDB file,
55-56 192
overview, 48-49 naming, 7
columns, 8 reserved words and, 13
aliases, creating, 101 natural joins, 104-105
NULL value, checking for, NULL value columns, 136-137
31 outer joins, 105-108
primary keys, 10 relational, 91-92
concepts, 7 renaming, 141
copying, 126 replacing, 135
 INDEX 241

rows, 9 terminology, 163

adding, 209 writing to databases, 208
deleting, 209 triggers, 183
updating, 211 creating, 184
schemas, 7 functionality, 183
security, 185 overview, 183-184
SQL scripts, 192 speed, 184
functions of, 187 syntax examples, 184
OrderItems table, 191 TRIM( ) function, 52
Orders table, 190 trimming padded spaces, 51-52
Products table, 189 troubleshooting
Vendors table, 188 accidental table deletion, 141
table name aliases, 101-102 Query Tool and, 203-204
self joins, 102-104 TRUNCATE TABLE statement, 131
triggers, 183
creating, 184
functionality, 183 U
updating, 127-129, 139-140
deleting data, 129-130 UCASE( ) function, 60
views, creating, 209 underscore (_) wildcard, 43-44
TAN( ) function, 65 UNION operator
testing, Query Tool and, 203-204 combined queries, 112-113
text functions, 59-60 compared to WHERE clauses,
list of common, 60 115
TEXT string datatype, 214 duplicate rows and, 114-115
time functions, 59, 62-64 rules, 114
TINYINT datatype, 215 sorting results, 116
tools (DBMS), interactive, 93 limits, 113
TOP argument, 73 UNION statements, types, 117
TOP PERCENT argument, 73 unions (queries)
totaling values creating, 112-113
calculated values, 71 duplicate rows and, 114-115
SUM( ) function, 71 overview, 111
to_char( ) function, 63 rules, 114
to_number( ) function, 63 sorting results, 116
transaction processing, 166-167 unique constraints, 178-179
blocks, ROLLBACK state- UNIQUE keyword, 179
ments, 210 unsorted data, query results, 15
COMMIT command, 165 UPDATE statement, 127-129
defined, 163 FROM keyword, 129
explicit commits, 165 guidelines, 131
managing, 164 security privileges, 127
overview, 161-162 subqueries, 129
ROLLBACK command, syntax, 211
164-165

How can we make this index more useful? Email us at [email protected]

242 SAMS TEACH YOURSELF SQL IN TEN MINUTES

transaction processing, 163 WHERE clause, 26. See also HAV-

triggers, 183 ING clause
updating BETWEEN operator, 30
data, guidelines, 131 combining in queries, 111
tables, 127-129, 139-140 compared to UNION statement,
deleting data, 129-130 115
UPPER( ) function, 59-60 DELETE statement, 130
user-defined datatypes, 180 filtering groups, 79
joins, 97
joins and, 95-97
V multiple query criteria, 33
AND operator, 33-34
values IN operator, 36-38
concatenation, 50 NOT operator, 38-39
searching for (indexes), 181 OR operator, 34-35
trimming padded space, 52 order of evaluation, 35-36
VARBINARY datatype, 217 NOT operator, 38
variable-length strings, 213 operators, 28
Vendors table, 188 checking against single
views (tables) value, 29
calculated fields, 151-152 checking for nonmatches,
creating, 145 29-30
creating, 209 checking for NULL value,
DBMS consistency, 144 31
filtering data, 150 checking for range of val-
joins, simplifying, 147-148 ues, 30-31
overview, 143-146 quotes and, 30
performance concerns, 145 operators support by DBMS, 30
reformatting retrieved data, parentheses and, 36
148-149 positioning, 27
reusable, 148 SOUNDEX( ) function, 61
rules and restrictions, 145-146 subqueries, 87
usefulness of, 144-145 UPDATE statements, 127-128
virtual tables. See views wildcards, 40
white space, SQL statements, 15
wildcards
W-X-Y-Z asterisk (*) character, 17-18
caret (^) character, 45
Web sites cautions, 46
Aqua Data Studio, 194 defined, 40
example table download site, LIKE operator and, 40-41
191 natural joins, 105
Query Tool, 194, 204 writing stored procedures, 155
Web-based applications, cursors, 169
YEAR( ) function, 63