Intro
Who iS this book for?
We know what you’re thinking
Metacognition
Bend your brain into submission
Readmc
The technical review team
Acknowledgments
1 Data and Tables: A place for everything
Defining your data
Look at your data in categories
Whats in a database?
Your database viewed through x-ray specs
Databases contain connected data
Tables Up Close
Take command!
Setting the table: the CREATE TABLE statement
Creating a more complicated table
Look how easy it is to write SQL
Create the mycontacts table, finally
Your table is ready
Take a meeting with some data types
Your table, DESCribed
You cant recreate an existing table or database!
Out with the old table, in with the new
To add data to your table, youll use the INSERT statement
Create the INSERT statement
Variations on an INSERT statement
Columns without values
Peek at your table with the SELECT statement
SQL Exposed: Confessions of a NULL
Controlling your inner NULL
NOT NULL appears in DESC
Fill in the blanks with DEFAULT
Your SQL Toolbox

2 The SELECT Statement: Gifted data retrieval
Date or no date?
A better SELECT
What the * is that?
How to query your data types
More punctuation problems
Unmatched single quotes
Single quotes are special characters
INSERT data with single quotes in it
SELECT specific columns to limit results
SELECT specific columns for faster results
Combining your queries
Finding numeric values
SmComparison Operators
Finding numeric data with Comparison Operators
Text data roping with Comparison Operators
To be OR not to be
The difference between AND and OR
Use IS NULL to find NULLs
Saving time with a single keyword: LIKE
The call of the Wild(card)
Selecting ranges using AND and comparison operators
Just BETWEEN us... theres a better way
After the dates, you are either IN...
... or you are NOT IN
More NOT
Your SQL Toolbox

3 DELETE and UPDATE: A change will do you good
Clowns are scary
Clown tracking
The clowns are on the move
How our clown data gets entered
Bonzo, weve got a problem
Getting rid of a record with DELETE
Using our new DELETE statement
DELETE rules
The INSERT-DELETE two step
Be careful with your DELETE
The trouble with imprecise DELETE
Change your data with UPDATE
UPDATE rules
UPDATE is the new INSERT-DELETE
UPDATE in action
Updating the clowns movements
UPDATE your prices
All we need is one UPDATE
YOur SQL Tbolbox

4 Smart Table Design: Why be normal?
Two fishy tables
A table is all about relationships
Atomic data
Atomic data and your tables
Atomic data rules
Reasons to be normal
The benefits of normal tables
Clowns arent normal
Halfway to 1NF
PRIMARY KEY rules
Getting to NORMAL
Fixing Gregs table
The CREATE TABLE we wrote
Show me the
Time-saving command
The CREATE TABLE with a PRIMARY KEY
1, 2, 3... auto incrementally
Adding a PRIMARY KEY to an existing table
ALTER TABLE and add a PRIMARY KEY
Your SQL Toolbox

5 ALTER: Rewriting the past
6 Advanced SELECT: Seeing your data with new eyes
7 Multi-table Database Design: Outgrowing your table
8 Joins and Multi-table Operations: Can’t we all just get along?
9 Subqueries: Queries Within Queries
10 Outer Joins, Self Joins, and Unions: New maneuvers
11 Constraints, Views, and Transactions: Too many cooks spoil the database
12 Security: Protecting your assets