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
    15. The CREATE TABLE with a PRIMARY KEY
    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
    16. EXISTS and 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
    18. INTERSECT and EXCEPT
    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
    9. REVOKING a used GRANT OPTION
    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
O'Reilly logo

Chapter 1. Data and Tables: A place for everything

image with no caption

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.

Defining your data

Greg knows many lonely single people. He likes keeping track of what his friends are up to, and enjoys introducing them to each other. He has lots of information about them scrawled on sticky notes like this:

image with no caption

Greg’s been using his system for a very long time. Last week he expanded his connections to include people who are seeking new jobs, so his listings are growing quickly. Very quickly...

image with no caption
image with no caption

Brain Power

Is there a better way to organize this information? What would you do?

image with no caption

Exactly right. A database is just what we need.

But before you can get into creating databases, you’re going to need to have a better idea of what kinds of data you’re going to want to store and some ways of categorizing it.

Look at your data in categories

Let’s look at your data in a different way. If you cut each note into pieces, then spread the pieces out horizontally you’d get something that looked like this:

image with no caption

Then if you cut up another sticky note with the categories you just noticed, and put the pieces above their corresponding information, you’d have something that looks a lot like this:

image with no caption

Here’s that same information nicely displayed in a TABLE in columns and rows.

image with no caption

last_name

first_name

email

birthday

profession

location

status

interests

seeking

Branson

Ann

7-1-1962

Aeronautical Engineer

San Antonio, TX

Single, but involved

RPG, Programming

New Job

Hamilton

Jamie

9-10-1966

System Administrator

Sunnyvale, CA

Single

Hiking, Writing

Friends, Women to date

Soukup

Alan

12-2-1975

Aeronautical Engineer

San Antonio, TX

Married

RPG, Programming

Nothing

Mendoza

Angelina

8-19-1979

Unix System Administrator

San Francisco, CA

Married

Acting, Dancing

New Job

What’s in a database?

image with no caption

Database Detour

Before we get into the details of what tables, rows, and columns are, let’s step back and look at the bigger picture. The first SQL structure you need to know about is the container that holds all your tables known as a database.

Note

A database is a container that holds tables and other SQL structures related to those tables.

Every time you search online, go shopping, call information, use your TiVo, make a reservation, get a speeding ticket, or buy groceries, a database is being asked for information, otherwise known as being queried.

image with no caption
image with no caption

Database Detour

image with no caption
image with no caption

Database Detour

Your database viewed through x-ray specs...

Note

The information inside the database is organized into tables.

image with no caption

Database Detour

image with no caption
image with no caption
image with no caption

Database Detour

Databases contain connected data

All of the tables in a database should be connected in some way. For example, here are the tables that might be in a database holding information about doughnuts:

image with no caption
image with no caption

Database Detour

Here’s an example of what an address book table containing your personal information might look like. You’ll often see the word field used instead of column. They mean the same thing. Also, row and record are often used interchangeably.

image with no caption
image with no caption

Exactly. You can identify categories for the type of data you’re collecting for each person. Your categories then become your columns. Each sticky note becomes a row. You can take all that information from your stickies and turn it into a table.

image with no caption
image with no caption
image with no caption

Take command!

Start up your SQL relational database management system (RDBMS) and open a command-line window or graphical environment that allows you to communicate with your RDBMS. Here’s our terminal window after we start MySQL.

image with no caption

First you’re going to need to create a database to hold all your tables.

  1. Type in the line of code below to create your database called gregs_list.

    Note

    Spaces aren’t allowed in the names of databases and tables in SQL, so an underscore can be used instead.

    image with no caption

    Watch it!

    Did you read the intro?

    We’re using MySQL to command our databases, so commands in your Database Management System (DBMS) might look a little different. See Appendix B for instructions on installing MySQL on your server.

  2. Now you need to tell your RDBMS to actually use the database you just created:

    image with no caption

Setting the table: the CREATE TABLE statement

Let’s see all this in action with the doughnut data. Say you were having trouble remembering what type of doughnuts a snack in your list was just from its name, you might create a table to save having to remember them instead. Below is a single command to type into your console window. When you’ve typed it, you can press RETURN to tell your SQL RDBMS to carry out the command.

doughnut_list

doughnut_name

doughnut_type

Blooberry

filled

Cinnamondo

ring

Rockstar

cruller

Carameller

cruller

Appleblush

filled

image with no caption
image with no caption

Creating a more complicated table

Remember the columns for Greg’s table? We’ve jotted them down on a sticky note. You’ll need those to write your CREATE TABLE command.

image with no caption

Brain Power

In which two ways do the column names on the sticky note differ from those in the table above? Why are they significant?

Look how easy it is to write SQL

You’ve seen that to create a table you categorize your data into columns. Then you come up with the right data type and length for each column. After you estimate how long each column needs to be, writing the code is straightforward.

Create the my_contacts table, finally

Now you know exactly what each line is doing, you can type in the CREATE TABLE command. You can enter it one line at a time, copying the code at the top of this page.

Or you can enter it all as one really long single line:

image with no caption

Whichever way you choose to enter it, before you hit return after the semicolon, make sure you haven’t missed any characters:

last_name VARCHAR(3) is a very different column than lastname VARCHAR(30)!

Your table is ready

image with no caption
image with no caption

Actually, you’ll need a few more data types for other kinds of data, like numbers.

Suppose we added a price column to our doughnut table. We wouldn’t want to store that as a VARCHAR. Values stored as VARCHARs are interpreted as text, and you won’t be able to perform mathematical operations on them But there are more data types you haven’t met yet...

Brain Power

Before going further, come up with other types of data that need a data type other than VARCHAR or DATE.

Take a meeting with some data types

These are a few of the most useful data types. It’s their job to store your data for you without mucking it up. You’ve already met VARCHAR and DATE, but say hello to these.

image with no caption

Watch it!

These data type names may not work with your SQL RDBMS!

Unfortunately, there are no universally accepted names for various data types. Your particular SQL RDBMS might use different names for one or more of these types. Check your documentation to find the correct names for your RDBMS.

image with no caption

Good call. Checking your work is important.

To see how the my_contacts table you created looks, you can use the DESC command to view it:

image with no caption

You try it.

image with no caption

Your table, DESCribed

When you’ve entered the DESC command. You’ll see something that looks similar to this:

image with no caption
image with no caption

Brain Power

What do you think? What sorts of problems could adding a new column create?

You can’t recreate an existing table or database!

image with no caption
image with no caption
image with no caption

That’s a very good idea, and you’ll want to use a text editor throughout this book.

That way, you can copy and paste the statements into your SQL console whenever you need to. This will keep you from having to retype everything. Also, you can copy and edit old SQL statements to make new ones.

Out with the old table, in with the new

  1. Getting rid of a table is much easier than creating a table. Use this simple command:

    image with no caption

    DROP TABLE will work whether or not there is data in your table, so use the command with extreme caution. Once your table is dropped, it’s gone, along with any data that was in it.

    DROP TABLE deletes your table and any data in it!

  2. Now you can enter your new CREATE TABLE statement:

    image with no caption

A bunch of SQL keywords and data types, in full costume, are playing the party game “Who am I?” They give you a clue, and you try to guess who they are, based on what they say. Assume they always tell the truth about themselves. If they happen to say something that could be true for more than one guy, then write down all for whom that sentence applies. Fill in the blanks next to the sentence with the names of one or more attendees.

Tonight’s attendees:

CREATE DATABASE, USE DATABASE, CREATE TABLE, DESC, DROP TABLE, CHAR, VARCHAR, BLOB, DATE, DATETIME, DEC, INT

Answers in Who am I?.

To add data to your table, you’ll use the INSERT statement

This pretty much does what it says in the name. Take a look at the statement below to see how each part works. The values in the second set of parentheses have to be in the same order as the column names.

The command below isn’t a real command, it’s a template of a statement to show you the format of an INSERT statement.

image with no caption

Create the INSERT statement

image with no caption

Watch it!

Order matters!

The values should be listed in exactly the same order as the column names.

image with no caption

Exactly right.

Here’s an INSERT statement you might use if you had a table of doughnut purchases. Notice how, in the values, the numbers that match the dozens of donuts purchased and price columns have no quotes.

image with no caption

Variations on an INSERT statement

There are three variations of INSERT statements you should know about.

  1. Changing the order of columns

    You can change the order of your column names, as long as the matching values for each column come in that same order!

    image with no caption
  2. Omitting column names

    You can leave out the list of column names, but the values must be all there, and all in the same order that you added the columns in. (Double-check the order in Create the INSERT statement if you’re unsure.)

    image with no caption
  3. Leaving some columns out

    You can insert a few columns and leave some out.

    image with no caption

Brain Power

What do you think shows up in the table in columns that you don’t assign a value to?

Columns without values

Let’s insert a record into the my_contacts database from this incomplete sticky note:

image with no caption

Because the sticky is missing some data, Greg will have to enter an incomplete record. But that’s okay, he’ll be able to add in the missing information later.

image with no caption

Peek at your table with the SELECT statement

So you want to see what your table looks like? Well, DESC won’t cut it anymore, because it only shows the structure of the table and not the information inside of it. Instead, you should use a simple

image with no caption
image with no caption

Relax

Don’t worry what the SELECT statement does for now.

We’ll be looking at it in a lot more detail in Chapter 2. For now, just sit back and marvel at the beauty of your table when you use the statement.

Now try it yourself. You’ll have to stretch out your window to see all the results nicely laid out.

Brain Power

Now you know that NULL appears in any columns with no assigned value. What do you think NULL actually means?

Controlling your inner NULL

There are certain columns in your table that should always have values. Remember the incomplete sticky note for Pat, with no last name? She (or he) isn’t going to be very easy to find when you have twenty more NULL last name entries in your table. You can easily set up your table to not accept NULL values for columns.

image with no caption

NOT NULL appears in DESC

Here’s how the my_contacts table would look if you set all the columns to have NOT NULL values.

image with no caption

Fill in the blanks with DEFAULT

If we have a column that we know is usually a specific value, we can assign it a DEFAULT value. The value that follows the DEFAULT keyword is automatically inserted into the table each time a row is added if no other value is specified. The default value has to be of the same type of value as the column.

image with no caption
image with no caption

Using a DEFAULT value fills the empty columns with a specified value.

Your SQL Toolbox

You’ve got Chapter 1 under your belt, and you already know how to create databases and tables, as well as how to insert some of the most common data types into them while ensuring columns that need a value get a value.

image with no caption

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