Head First SQL
by Lynn Beighley
Copyright © 2007 O’Reilly Media, Inc. All rights reserved.
Printed in the United States of America.
Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472.
O’Reilly Media books may be purchased for educational, business, or sales promotional use. Online editions are
also available for most titles (safari.oreilly.com). For more information, contact our corporate/institutional sales
department: (800) 998-9938 or corporate@oreilly.com.
Series Creators: Kathy Sierra, Bert Bates
Series Editor: Brett D. McLaughlin
Editor: Catherine Nolan
Design Editor: Louise Barr
Cover Designers: Louise Barr, Karen Montgomery
Production Editor: Sanders Kleinfeld
Indexer: Julie Hawks
Page Viewer: Andrew Fader
Printing History:
August 2007: First Edition.
The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. The Head First series designations,
Head First SQL, and related trade dress are trademarks of O’Reilly Media, Inc.
Many of the designations used by manufacturers and sellers to distinguish their products are claimed as
trademarks. Where those designations appear in this book, and O’Reilly Media, Inc., was aware of a trademark
claim, the designations have been printed in caps or initial caps.
While every precaution has been taken in the preparation of this book, the publisher and the authors assume no
responsibility for errors or omissions, or for damages resulting from the use of the information contained herein.
No clowns, doughnuts, or Girl Sprouts were harmed in the making of this book. Just my car, but it’s been fixed.
ISBN-10: 0-596-52684-9
ISBN-13: 978-0-596-52684-9
[M]
The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. The
Head First
series designations,
Head First series designations, Head First
He’s incredibly patient.
This book uses RepKover
™
, a durable and fl exible lay-fl at binding.
TMTM
table of contents
ix
Table of Contents (Summary)
Table of Contents (the real thing)
Your brain on SQL. Here you are trying to learn something, while here
your brain is doing you a favor by making sure the learning doesn’t stick. Your
brain’s thinking, “Better leave room for more important things, like which wild
animals to avoid and whether naked snowboarding is a bad idea.” So how do you
trick your brain into thinking that your life depends on knowing SQL?
Intro
Who is this book for? xxvi
We know what you’re thinking xxvii
Metacognition xxix
Bend your brain into submission xxxi
Read me xxxii
The technical review team xxxiv
Acknowledgments xxxv
Intro xxv
1 Data and Tables: A place for everything 1
2 The SELECT Statement: Gifted data retrieval 53
3 DELETE and UPDATE: A change will do you good 119
4 Smart Table Design: Why be normal? 159
5 ALTER: Rewriting the past 197
6 Advanced SELECT: Seeing your data with new eyes 235
7 Multi-table Database Design: Outgrowing your table 281
8 Joins and Multi-table Operations: Can’t we all just get along? 343
9 Subqueries: Queries Within Queries 379
10 Outer Joins, Self Joins, and Unions: New maneuvers 417
11 Constraints, Views, and Transactions: Too many cooks spoil the database 455
12 Security: Protecting your assets 493
table of contents
x
A place for everything
1
Don’t you just hate losing things? Whether it’s your car
keys, that 25% off coupon for Urban Outfitters, or your application’s
data, there’s nothing worse than not being able to keep up with what
you need... when you need it. And when it comes to your applications,
there’s no better place to store your important information than in a
table. So turn the page, come on in, and take a walk through the world
of relational databases.
data and tables
Think of a database
like a container that
holds information…
A table.
column1 column2 column3
column4
data data data
data
data
data
data data
data
data data data
column1
column2
column3
column4
column5
column6
data
data
data
data
data
data
data
data
data
data
data
data
data
data
data
data
data
data
data
data
data
data
data
data
column1
column2
column3
data
data
data
data
data
data
data
data
data
data
data
data
data
data
data
data
data
data
Another
table.
Some other table.
Your database viewed
through x-ray specs...
column1
column2
data
data
data
data
data
data
Another table.
These
are the
These are the columns.
Defining your data 2
Look at your data in categories 7
What’s in a database? 8
Your database viewed through x-ray specs... 10
Databases contain connected data 12
Tables Up Close 13
Take command! 17
Setting the table: the CREATE TABLE statement 19
Creating a more complicated table 20
Look how easy it is to write SQL 21
Create the my_contacts table, finally 22
Your table is ready 23
Take a meeting with some data types 24
Your table, DESCribed 28
You can’t recreate an existing table or database! 30
Out with the old table, in with the new 32
To add data to your table, you’ll use the INSERT statement 34
Create the INSERT statement 37
Variations on an INSERT statement 41
Columns without values 42
Peek at your table with the SELECT statement 43
SQL Exposed: Confessions of a NULL 44
Controlling your inner NULL 45
NOT NULL appears in DESC 47
Fill in the blanks with DEFAULT 48
Your SQL Toolbox 50
table of contents
xi
Gifted data retrieval
Is it really better to give than retrieve? When it comes to
databases, chances are you’ll need to retrieve your data as often than
you’ll need to insert it. That’s where this chapter comes in: you’ll meet the
powerful SELECT statement and learn how to gain access to that important
information you’ve been putting in your tables. You’ll even learn how to
use WHERE, AND, and OR to selectively get to your data and even avoid
displaying the data that you don’t need.
the SELECT statement
2
I’m a star!
Date or no date? 54
A better SELECT 57
What the * is that? 58
How to query your data types 64
More punctuation problems 65
Unmatched single quotes 66
Single quotes are special characters 67
INSERT data with single quotes in it 68
SELECT specific columns to limit results 73
SELECT specific columns for faster results 73
Combining your queries 80
Finding numeric values 83
Smooth Comparison Operators 86
Finding numeric data with Comparison Operators 88
Text data roping with Comparison Operators 91
To be OR not to be 93
The difference between AND and OR 96
Use IS NULL to find NULLs 99
Saving time with a single keyword: LIKE 101
The call of the Wild(card) 101
Selecting ranges using AND and comparison operators 105
Just BETWEEN us… there’s a better way 106
After the dates, you are either IN... 109
... or you are NOT IN 110
More NOT 111
Your SQL Toolbox 116
