You are previewing Head First SQL.

Head First SQL

Cover of Head First SQL by Lynn Beighley Published by O'Reilly Media, Inc.
  1. Head First SQL
  2. Dedication
  3. SPECIAL OFFER: Upgrade this ebook with O’Reilly
  4. A Note Regarding Supplemental Files
  5. Advance Praise for Head First SQL
  6. Praise for other Head First books
  7. Praise for the Head First Approach
  8. Author of Head First SQL
  9. How to use this Book: Intro
    1. Who is this book for?
    2. Who should probably back away from this book?
    3. We know what you’re thinking.
    4. And we know what your brain is thinking.
    5. Metacognition: thinking about thinking
    6. Here’s what WE did
    7. Here’s what YOU can do to bend your brain into submission
    8. Read me
    9. The technical review team
    10. Acknowledgments
  10. 1. Data and Tables: A place for everything
    1. Defining your data
    2. Look at your data in categories
    3. What’s in a database?
    4. Your database viewed through x-ray specs...
    5. Databases contain connected data
    6. Take command!
    7. Setting the table: the CREATE TABLE statement
    8. Creating a more complicated table
    9. Look how easy it is to write SQL
    10. Create the my_contacts table, finally
    11. Your table is ready
    12. Take a meeting with some data types
    13. Your table, DESCribed
    14. You can’t recreate an existing table or database!
    15. Out with the old table, in with the new
    16. To add data to your table, you’ll use the INSERT statement
    17. Create the INSERT statement
    18. Variations on an INSERT statement
    19. Columns without values
    20. Peek at your table with the SELECT statement
    21. Controlling your inner NULL
    22. NOT NULL appears in DESC
    23. Fill in the blanks with DEFAULT
    24. Your SQL Toolbox
  11. 2. The SELECT Statement: Gifted data retrieval
    1. Date or no date?
    2. Making contact
    3. A better SELECT
    4. What the * is that?
    5. How to query your data types
    6. More punctuation problems
    7. Unmatched single quotes
    8. Single quotes are special characters
    9. INSERT data with single quotes in it
      1. Handle quotes with a backslash
      2. Handle quotes with an extra single quote
    10. SELECT specific data
    11. The old way
    12. SELECT specific columns to limit results
    13. SELECT specific columns for faster results
    14. Doughnut ask what your table can do for you...
    15. Ask what you can do for your doughnut
    16. Combining your queries
    17. Finding numeric values
    18. Once is enough
    19. Smooth Comparison Operators
    20. Finding numeric data with Comparison Operators
    21. Text data roping with Comparison Operators
    22. Selecting your ingredients
    23. To be OR not to be
    24. The difference between AND and OR
    25. Use IS NULL to find NULLs
    26. Meanwhile, back at Greg’s place...
    27. Saving time with a single keyword: LIKE
    28. The call of the Wild(card)
    29. That’s more LIKE it
      1. Selecting ranges using AND and comparison operators
    30. Just BETWEEN us... there’s a better way
    31. After the dates, you are either IN...
    32. ... or you are NOT IN
    33. More NOT
    34. Your SQL Toolbox
  12. 3. DELETE and UPDATE: A change will do you good
    1. Clowns are scary
    2. Clown tracking
    3. How our clown data gets entered
    4. Bonzo, we’ve got a problem
    5. Getting rid of a record with DELETE
    6. Using our new DELETE statement
    7. DELETE rules
    8. The INSERT-DELETE two step
    9. Be careful with your DELETE
    10. The trouble with imprecise DELETE
    11. Change your data with UPDATE
    12. UPDATE rules
    13. UPDATE is the new INSERT-DELETE
    14. UPDATE in action
    15. UPDATE your prices
    16. All we need is one UPDATE
    17. Your SQL Toolbox
  13. 4. Smart Table Design: Why be normal?
    1. Two fishy tables
    2. A table is all about relationships
    3. Atomic data
      1. 30 minutes or it’s free
      2. Location, location, location
    4. Atomic data and your tables
    5. Reasons to be normal
    6. The benefits of normal tables
    7. Clowns aren’t normal
    8. Halfway to 1NF
    9. PRIMARY KEY rules
    10. Getting to NORMAL
    11. Fixing Greg’s table
    12. The CREATE TABLE we wrote
    13. Show me the
    14. Time-saving command
    16. 1, 2, 3... auto incrementally
    17. Adding a PRIMARY KEY to an existing table
    18. ALTER TABLE and add a PRIMARY KEY
    19. Your SQL Toolbox
  14. 5. Alter: Rewriting the Past
    1. We need to make some changes
    2. Table altering
    3. Extreme table makeover
    4. Renaming the table
    5. We need to make some plans
    6. Retooling our columns
    7. Structural changes
    8. ALTER and CHANGE
    9. Change two columns with one SQL statement
    10. Quick! DROP that column
    11. A closer look at the non-atomic location column
    12. Look for patterns
    13. A few handy string functions
      1. To SELECT the last two characters
      2. To SELECT everything in front of the comma
    14. Use a current column to fill a new column
    15. How our UPDATE and SET combo works
    16. Your SQL Toolbox
  15. 6. Advanced Select: Seeing your data with new eyes
    1. Dataville Video is reorganizing
    2. Problems with our current table
    3. Matching up existing data
    4. Populating the new column
      1. The order does matter
    5. UPDATE with a CASE expression
    6. Looks like we have a problem
    7. Tables can get messy
    8. We need a way to organize the data we SELECT
    9. Try a little ORDER BY
    10. ORDER a single column
    11. ORDER with two columns
    12. ORDER with multiple columns
    13. An orderly movie_table
      1. and the ordered results from our query:
    14. Reverse the ORDER with DESC
    15. The Girl Sprout® cookie sales leader problem
    16. SUM can add them for us
    17. SUM all of them at once with GROUP BY
    18. AVG with GROUP BY
    19. MIN and MAX
    20. COUNT the days
    21. SELECT DISTINCT values
    22. LIMIT the number of results
    23. LIMIT to just second place
    24. Your SQL Toolbox
  16. 7. Multi-Table Database Design: Outgrowing your table
    1. Finding Nigel a date
    2. Why change anything?
    3. The query worked really well
    4. It worked too well
    5. Ignoring the problem isn’t the answer
    6. Too many bad matches
    7. Use only the first interest
    8. A possible match
    9. Mis-matched
    10. Add more interest columns
    11. Starting over
    12. All is lost...
    13. ... But wait
    14. Think outside of the single table
    15. The multi-table clown tracking database
    16. The clown_tracking database schema
    17. An easier way to diagram your tables
    18. How to go from one table to two
    19. Linking your tables in a diagram
    20. Connecting your tables
    21. Foreign key facts
    22. Constraining your foreign key
    23. Why bother with foreign keys?
    24. CREATE a table with a FOREIGN KEY
    25. Relationships between tables
    26. Patterns of data: one-to-one
    27. Patterns of data: when to use one-to-one tables
      1. When to use one-to-one tables
    28. Patterns of data: one-to-many
    29. Patterns of data: getting to many-to-many
    30. Patterns of data: we need a junction table
    31. Patterns of data: many-to-many
    32. Patterns of data: fixing gregs_list
    33. Not in first normal form
    34. Finally in 1NF
    35. Composite keys use multiple columns
    36. Even superheros can be dependent
    37. Shorthand notations
    38. Superhero dependencies
    39. Partial functional dependency
    40. Transitive functional dependency
    41. Second normal form
    42. We might be 2NF already...
    43. Third normal form (at last)
    44. And so, Regis (and gregs_list) lived happily ever after
    45. The End
    46. Your SQL Toolbox
  17. 8. Joins and Multi-Table Operations: Can’t we all just get along?
    1. Still repeating ourselves, still repeating...
    2. Prepopulate your tables
    3. We got the “table ain’t easy to normalize” blues
    4. The special interests (column)
    5. Keeping interested
    6. UPDATE all your interests
    7. Getting all the interests
    8. Many paths to one place
    9. CREATE, SELECT and INSERT at (nearly) the same time
    10. CREATE, SELECT and INSERT at the same time
    11. What’s up with that AS?
    12. Column aliases
    13. Table aliases, who needs ’em?
    14. Everything you wanted to know about inner joins
    15. Cartesian join
    16. Releasing your inner join
    17. The inner join in action: the equijoin
    18. The inner join in action: the non-equijoin
    19. The last inner join: the natural join
    20. Joined-up queries?
    21. Your SQL Toolbox
  18. 9. Subqueries: Queries within queries
    1. Greg gets into the job recruiting business
    2. Greg’s list gets more tables
    3. Greg uses an inner join
    4. But he wants to try some other queries
    5. Subqueries
    6. We combine the two into a query with a subquery
    7. Subquery rules
    8. Subquery rules
    9. A subquery construction walkthrough
    10. A subquery as a SELECT column
    11. Another example: Subquery with a natural join
    12. A noncorrelated subquery
    13. A noncorrelated subquery with multiple values: IN, NOT IN
    14. Correlated subqueries
    15. A (useful) correlated subquery with NOT EXISTS
    17. Greg’s Recruiting Service is open for business
    18. On the way to the party
    19. Your SQL Toolbox
  19. 10. Outer Joins, Self-Joins, and Unions: New maneuvers
    1. Cleaning up old data
    2. It’s about left and right
    3. Here’s a left outer join
      1. The results of the left outer join
    4. Outer joins and multiple matches
    5. The right outer join
    6. While you were outer joining...
    7. We could create a new table
    8. How the new table fits in
    9. A self-referencing foreign key
    10. Join the same table to itself
    11. We need a self-join
    12. Another way to get multi-table information
    13. You can use a UNION
    14. UNION is limited
    15. UNION rules in action
    16. UNION ALL
    17. Create a table from your union
    19. We’re done with joins, time to move on to...
    20. Subqueries and joins compared
    21. Turning a subquery into a join
    22. A self-join as a subquery
    23. Greg’s company is growing
    24. Your SQL Toolbox
  20. 11. Constraints, Views, and Transactions: Too many cooks spoil the database
    1. Greg’s hired some help
    2. Jim’s first day: Inserting a new client
    3. Jim avoids a NULL
    4. Flash forward three months
    5. CHECK, please: Adding a CHECK CONSTRAINT
    6. CHECKing the gender
    7. Frank’s job gets tedious
    8. Creating a view
    9. Viewing your views
    10. What your view is actually doing
    11. What a view is
    12. Inserting, updating, and deleting with views
    13. The secret is to pretend a view is a real table
    14. View with CHECK OPTION
    15. Your view may be updatable if...
    16. When you’re finished with your view
    17. When bad things happen to good databases
    18. What happened inside the ATM
    19. More trouble at the ATM
    20. It’s not a dream, it’s a transaction
    21. The classic ACID test
    22. SQL helps you manage your transactions
    23. What should have happened inside the ATM
    24. How to make transactions work with MySQL
    25. Now try it yourself
    26. Your SQL Toolbox
  21. 12. Security: Protecting your assets
    1. User problems
    2. Avoiding errors in the clown tracking database
    3. Protect the root user account
    4. Add a new user
    5. Decide exactly what the user needs
    6. A simple GRANT statement
    7. GRANT variations
    8. REVOKE privileges
    10. REVOKING with precision
    11. The problem with shared accounts
    12. Using your role
    13. Role dropping
    14. Using your role WITH ADMIN OPTION
      1. REVOKE role with CASCADE
      2. REVOKE role with RESTRICT
    15. Combining CREATE USER and GRANT
    16. Greg’s List has gone global!
    17. Your SQL Toolbox
    18. How about a Greg’s List in your city?
    19. Use SQL on your own projects, and you too could be like Greg!
  22. A. Leftovers: The Top Ten Topics (we didn’t cover)
    1. #1. Get a GUI for your RDBMS
      1. MySQL GUI tools
      2. Other GUI tools
    2. #2. Reserved Words and Special Characters
      1. Special Characters
      2. Reserved Words
    3. #3. ALL, ANY, and SOME
      1. Using ALL
      2. Using ANY
      3. Using SOME
    4. #4. More on Data Types
      1. BOOLEAN
      2. INT
      3. Other INT types
      4. DATE and TIME types
    5. #5. Temporary tables
      1. Reasons you might want a temporary table:
      2. Create a temporary table
      3. A temporary table shortcut
    6. #6. Cast your data
      1. Some situations where you might want to use CAST()
      2. You can’t use CAST() in these situations
    7. #7. Who are you? What time is it?
    8. #8. Useful numeric functions
    9. #9. Indexing to speed things up
    10. #10. 2-minute PHP/MySQL
      1. A closer look at each line
  23. B. MySQL Installation: Try it out for yourself
    1. Get started, fast!
    2. Instructions and Troubleshooting
    3. Steps to Install MySQL on Windows
      1. Download your installer
      2. Pick a destination folder
      3. Click “Install” and you’re done!
    4. Steps to Install MySQL on Mac OS X
  24. C. Tools Roundup: All your new SQL tools
  25. D.  
    1. Symbols
    2. A
    3. B
    4. C
    5. D
    6. E
    7. F
    8. G
    9. I
    10. L
    11. M
    12. N
    13. O
    14. P
    15. S
    16. T
    17. U
    18. V
    19. W
  26. Index
  27. About the Author
  28. SPECIAL OFFER: Upgrade this ebook with O’Reilly
  29. Copyright

Chapter 4. Smart Table Design: Why be normal?

image with no caption

You’ve been creating tables without giving much thought to them. And that’s fine, they work. You can SELECT, INSERT, DELETE, and UPDATE with them. But as you get more data, you start seeing things you wish you’d done to make your WHERE clauses simpler. What you need is to make your tables more normal.

Two fishy tables

Jack and Mark both created tables to store information about record-setting fish. Mark’s table has columns for the species and common names of the fish, its weight, and where it was caught. It doesn’t include the names of the people who caught the fish.

image with no caption
image with no caption

Jack’s table has the common name and weight of the fish, but it also contains the first and last names of the people who caught them, and it breaks down the location into a column containing the name of the body of water where the fish was caught, and a separate state column.

image with no caption

Brain Power

SQL is the language used by relational databases. What do you think “relational” means in an SQL database?

A table is all about relationships

SQL is known as a Relational Database Management System, or RDBMS. Don’t bother memorizing it. We only care about the word RELATIONAL[1]. All this means to you is that to design a killer table, you need to consider how the columns relate to each other to describe a thing.

The challenge is to describe the thing using columns in a way that makes getting the information out of it easy. This depends on what you need from the table, but there are some very broad steps you can follow when you’re creating a table.

  1. Pick your thing, the one thing you want your table to describe.


    What’s the main thing you want your table to be about?

  2. Make a list of the information you need to know about your one thing when you’re using the table.


    How will you use this table?

  3. Using the list, break down the information about your thing into pieces you can use for organizing your table.


    How can you most easily query this table?

image with no caption

We could, but we don’t need the data broken down to that level.

At least, not in this case. If Jack had been writing an article about the best places to go on vacation and catch a big fish, then he might have wanted the street number and name so readers could find accommodations nearby.

But Jack only needed location and state, so he only added as many columns as he needed to save space in his database. At that point, he decided his data was broken down enough—it is atomic.

Brain Power

What do you think the word atomic means in terms of SQL data?

Atomic data

What’s an atom? A little piece of information that can’t or shouldn’t be divided. It’s the same for your data. When it’s ATOMIC, that means that it’s been broken down into the smallest pieces of data that can’t or shouldn’t be divided.

30 minutes or it’s free

Consider a pizza delivery guy. To get to where he’s going, he just needs a street number and address in a single column. For his purposes, that’s atomic. He never needs to look for a single street number on its own.

In fact, if his data were broken into street number and street name, his queries would have to be longer and more complicated, making it take him longer to get the pizza to your front door.

image with no caption

Location, location, location

Now consider a realtor. He might want to have a separate column for the street number. He may want to query on a given street to see all the houses for sale by street number. For him, street number and street name are each atomic.

image with no caption

Atomic data and your tables

There are some questions you can ask to help you figure out what you need to put in your tables:

  1. What is the one thing your table describes?

    Does your table describe clowns, cows, doughnuts, people?

    image with no caption
  2. How will you use the table to get at the one thing?


    Design your table to be easy to query!

    image with no caption
  3. Do your columns contain atomic data to make your queries short and to the point?

    image with no caption

Reasons to be normal

When your data consultancy takes off and you need to hire more SQL database designers, wouldn’t it be great if you didn’t need to waste hours explaining how your tables work?

Well, making your tables NORMAL means they follow some standard rules your new designers will understand. And the good news is, our tables with atomic data are halfway there.

Making your data atomic is the first step in creating a NORMAL table.

The benefits of normal tables

  1. Normal tables won’t have duplicate data, which will reduce the size of your database.

    image with no caption
  2. With less data to search through, your queries will be faster.

    image with no caption

    Because, even when your tables are tiny, it adds up. And tables grow. If you begin with a normalized table, you won’t have to go back and change your table when your queries go too slowly.

Clowns aren’t normal

Remember the clown table? Clown tracking has become a nationwide craze, and our old table isn’t going to cut it because the appearance and activities columns contain so much data. For our purposes, this table is not atomic.


These two columns are really difficult to query because they contain so much data!







Cherry Hill Senior Center

F, red hair, green dress, huge feet

balloons, little car


Jack Green’s party

M, orange hair, blue suit, huge feet




F, yellow shirt, baggy blue pants

horn, umbrella

Mr. Hobo

Eric Gray’s Party

M, cigar, black hair, tiny hat



Belmont Senior Center

F, pink hair, huge flower, blue dress

yelling, dancing


Oakland Hospital

M, blue hair, red suit, huge nose



Millstone Mall

F, orange suit, baggy pants



Earl’s Autos

F, all pink and sparkly

balancing, little car


Dickson Park

M, in drag, polka dotted dress

singing, dancing



M, green and purple suit, pointy nose

climbing into tiny car

Halfway to 1NF

Remember, our table is only about halfway normal when it’s got atomic data in it. When we’re completely normal we’ll be in the FIRST NORMAL FORM or 1NF.

To be 1NF, a table must follow these two rules:

Each row of data must contain atomic values.


We already know how to do this.

Each row of data must have a unique identifier, known as a Primary Key.


To make our tables completely normal, we need to give each record a Primary Key.

Brain Power

What types of columns do you think would make good Primary Keys?

image with no caption


The column in your table that will be your primary key has to be designated as such when you create the table. In a few pages, we’ll create a table and designate a primary key, but before that, let’s take a closer look at what a primary key is.

image with no caption

A primary key is a column in your table that makes each record unique.

image with no caption

The primary key is used to uniquely identify each record

Which means that the data in the primary key column can’t be repeated. Consider a table with the columns shown below. Do you think any of those would make good primary keys?

image with no caption

Watch it!

Take care using SSNs as the Primary Keys for your records.

With identity theft only increasing, people don’t want to give out SSNs—and with good reason. They’re too important to risk. Can you absolutely guarantee that your database is secure? If it’s not, all those SSNs can be stolen, along with your customers’ identities.

image with no caption

A primary key can’t be NULL

If it’s null, it can’t be unique because other records can also be NULL.

image with no caption

The primary key must be given a value when the record is inserted

When you insert a record without a primary key, you run the risk of ending up with a NULL primary key and duplicate rows in your table, which violates First Normal Form.

image with no caption

The primary key must be compact

A primary key should contain only the information it needs to to be unique and nothing extra.

image with no caption

The primary key values can’t be changed

If you could change the value of your key, you’d risk accidentally setting it to a value you already used. Remember, it has to remain unique.

Brain Power

Given all these rules, can you think of a good primary key to use in a table?

Look back through the tables in the book. Do any of them have a column that contains truly unique values?

image with no caption

The best primary key may be a new primary key.

When it comes to creating primary keys, your best bet may be to create a column that contains a unique number. Think of a table with people’s info, but with an additional column containing a number. In the example below, let’s call it ID.

If it weren’t for the ID column, the records for John Brown would be identical. But in this case, they’re actually two different people. The ID column makes these records unique. This table is in first normal form.

image with no caption

Geek Bits

There’s a big debate in the SQL world about using synthetic, or made-up, primary keys (like the ID column above) versus using natural keys—data that is already in the table (like a VIN number on a car or SSN number). We won’t take sides, but we will discuss primary keys in more detail in Chapter 7.

Getting to NORMAL

It’s time to step back and normalize our tables. We need to make our data atomic and add primary keys. Creating a primary key is normally something we do when we write our CREATE TABLE code.

Brain Power

Do you remember how to add columns to an existing table?

Fixing Greg’s table

From what you’ve seen so far, this is how you’d have to fix Greg’s table:

Fixing Greg’s table Step 1: SELECT all of your data and save it somehow.

Fixing Greg’s table Step 2: Create a new normal table.

Fixing Greg’s table Step 3: INSERT all that old data into the new table, changing each row to match the new table structure.

So now you can drop your old table.

image with no caption

So, we know that Greg’s table isn’t perfect.

It’s not atomic and it has no primary key. But luckily for Greg, you don’t have to live with the old table, and you don’t have to dump your data.

We can add a primary key to Greg’s table and make the columns more atomic using just one new command. But first, let’s take a little trip to the past...

The CREATE TABLE we wrote

Greg needs a primary key, and after all the talk about atomic data, he realizes there are a few things he could do to make his columns more atomic. Before we look at how to fix the existing table, let’s look at how we could have created the table in the first place!

Here’s the table we created way back in Chapter 1.

image with no caption

Brain Power

But what if you don’t have your old CREATE TABLE printed anywhere? Can you think of some way to get at the code?

Show me the

What if you use the DESCRIBE my_contacts command to look at the code you used when you set up the table? You’ll see something that looks a lot like this:

image with no caption

But we really want to look at the CREATE code here, not the fields in the table, so we can figure out what we should have done at the very beginning without having to write the CREATE statement over again.

The statement SHOW CREATE_TABLE will return a CREATE TABLE statement that can exactly recreate our table, minus any data in it. This way, you can always see how the table you are looking at could be created. Try it:

SHOW CREATE TABLE my_contacts;

Time-saving command

Take a look at the code we used to create the table in The CREATE TABLE we wrote, and the code below that the SHOW CREATE TABLE my_contacts gives you. They aren’t identical, but if you paste the code below into a CREATE TABLE command, the end result will be the same. You don’t need to remove the backticks or data settings, but it’s neater if you do.

image with no caption

Although you could make the code neater (by removing the last line and backticks), you can just copy and paste it to create a table.


Unless you’ve deleted the original table, you’ll have to give this one a new name.


Here’s the code our SHOW CREATE TABLE my_contacts gave us. We removed the backticks and last line. At the top of the column list we added a contact_id column that we’re setting to NOT NULL, and at the bottom of the list, we’re add a line PRIMARY KEY, which we set to use our new contact_id column as the primary key.

image with no caption

1, 2, 3... auto incrementally

Adding the keyword AUTO_INCREMENT to our contact_id column makes our SQL software automatically fill that column with a value that starts on row 1 with a value of 1 and goes up in increments of 1.

image with no caption
image with no caption

What do you think will happen?

Better yet, try it out for yourself and see what happens.

image with no caption

You won’t have to start over; instead, you can use an ALTER statement.

A table with data in it doesn’t have to be dumped, then dropped, then recreated. We can actually change an existing table. But to do that, we’re going to borrow the ALTER statement and some of its keywords from Chapter 5.

Adding a PRIMARY KEY to an existing table

Here’s the code to add an AUTO_INCREMENT primary key to Greg’s my_contacts table. (It’s a long command, so you’ll need to turn your book.)

image with no caption

Brain Power

Do you think that this will add values to the new contact_id column for records already in the table or only for newly inserted records? How can you check?


Try the code yourself. Open your SQL terminal. USE the gregs_list database, and type in this command:

image with no caption

To see what happened to your table, try a SELECT * from my_contacts;

image with no caption

Will Greg get his phone number column? Turn to Chapter 5 to find out.

Your SQL Toolbox

You’ve got Chapter 4 under your belt. Look at all the new tools you’ve added to your toolbox now! For a complete list of tooltips in the book, see Appendix C.

image with no caption

[1] Some people think that RELATIONAL means multiple tables relating to each other. That’s not correct.

The best content for your career. Discover unlimited learning on demand for around $1/day.