Head First SQL

Chapter 1. Data and Tables: A place for everything

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:

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...

Brain Power

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

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.

Sharpen your pencil

Here are some of Greg’s notes. Look for similar information that Greg’s collected about each person. Give each common bit of data a label that describes the category of information it is, then write those labels in the space below.

Sharpen your pencil Solution

Here are some of Greg’s notes. Look for similar information that Greg’s collected about each person. Give each common bit of data a label that describes the category of information it is, then write those labels in the space below.

Note

Now that we’ve created these categories, we can use them to organize our data.

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:

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:

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

last_name	first_name	email	birthday	profession	location	status	interests	seeking
Branson	Ann	[e-mail]	7-1-1962	Aeronautical Engineer	San Antonio, TX	Single, but involved	RPG, Programming	New Job
Hamilton	Jamie	[e-mail]	9-10-1966	System Administrator	Sunnyvale, CA	Single	Hiking, Writing	Friends, Women to date
Soukup	Alan	[e-mail]	12-2-1975	Aeronautical Engineer	San Antonio, TX	Married	RPG, Programming	Nothing
Mendoza	Angelina	[e-mail]	8-19-1979	Unix System Administrator	San Francisco, CA	Married	Acting, Dancing	New Job

What’s in a database?

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.

Database Detour

Database Detour

Your database viewed through x-ray specs...

Anatomy of a Database

A database contains tables.

A table is the structure inside your database that contains data, organized in columns and rows.

Remember those categories you came up with? Each category becomes a column in your table. These values might be in the same column: Single, Married, Divorced.

A table row contains all the information about one object in your table. In Greg’s new table, a row would be all the data about one person. Here’s an example of some of the data that might be in one row: John, Jackson, single, writer, [e-mail].

Note

The information inside the database is organized into tables.

Database Detour

BE the table

Below, you’ll find some sticky notes and a table. Your job is to be the partially formed table and fill in the empty bits to create inner peace. After you’ve done the exercise, turn the page to see if you’ve become one with the table.

Database Detour

BE the table Solution

Your job was to be the partially formed table and fill in the empty bits to increase inner peace.

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:

Database Detour

Tables Up Close

A column is a piece of data stored by your table. A row is a single set of columns that describe attributes of a single thing. Columns and rows together make up a table.

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.

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.

Exercise

Consider the databases and tables below. Think about what categories of data you might find in each. Come up some likely columns for each table.

Exercise Solution

Consider the databases and tables below. Think about what categories of data you might find in each. Come up some likely columns for each table.

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.

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.

   

   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:

   

There are no Dumb Questions

Q:	Q: Why do I need to create a database if I only have one table?
A:	A: The SQL language requires all tables to be inside of databases. There are sound reasons behind this. One of the features of SQL is the ability to control access to your tables by multiple users. Being able to grant or deny access to an entire database is sometimes simpler than having to control the permissions on each one of multiple tables.
Q:	Q: I noticed that we used all uppercase for the CREATE DATABASE command. Is that necessary?
A:	A: Some systems do require certain keywords to be capitalized, but SQL is case insensitive. That means it’s not necessary to capitalize commands, but it’s considered a good programming practice in SQL. Look at the command we just typed, CREATE DATABASE gregs_list; The capitalization makes it easy to tell the command (CREATE DATABASE) from the name of the database (gregs_list).
Q:	Q: Is there anything I should know about naming my databases, tables, and columns?
A:	A: It’s generally a good idea to create descriptive names. Sometimes this results in you needing to use more than one word in a name. You can’t use spaces in your names, so the underscore lets you create more descriptive names. Here are variations you might see used: gregs_list gregslist Gregslist gregsList Generally it’s best to avoid capitalizing your names to avoid confusion since SQL is case insensitive..
Q:	Q: What if I prefer to use “gregsList” with no underscore?
A:	A: Go right ahead. The important thing is to be consistent. If you use gregsList as the database name with no underscore and the second word capitalized, then you should stick to that naming convention throughout all your tables in this database, for example naming your table myContacts, to be consistent.
Q:	Q: Shouldn’t the database be called greg’s_list? Why leave out the apostrophe?
A:	A: The apostrophe is reserved for a different use in SQL. There are ways you could include one, but it’s far easier to omit it.
Q:	Q: I also noticed a semicolon at the end of the CREATE DATABASE command. Why did we need that?
A:	A: The semicolon is there to indicate that the command has ended.

Capitalization and underscores help you program in SQL (even though SQL doesn’t need them!)

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

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.

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.

Sharpen your pencil

The code to the left is our CREATE TABLE statement for Greg’s new database. Try to guess what each line of the CREATE TABLE command is doing. Also include an example of the data that will go in each column.

Sharpen your pencil Solution

Here’s what each line of the CREATE TABLE command is doing, and some example data for each column type.

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:

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

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.

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.

Which Data Type?

Determine which data type makes the most sense for each column. While you’re at it, fill in the other missing info.

There are no Dumb Questions

Q:	Q: Why not just use BLOB for all of my text values?
A:	A: It’s a waste of space. A VARCHAR or CHAR takes up a specific amount of space, no more than 256 characters. But a BLOB takes up much more storage space. As your database grows, you run the risk of running out of space on your hard drive. You also can’t run certain important string operations on BLOBs that you can on VARCHARs and CHARs (you’ll learn about these later).
Q:	Q: Why do I need these numeric types like INT and DEC?
A:	A: It all comes down to database storage and efficiency. Choosing the best matching data type for each column in your table will reduce the size of table and make operations on your data faster.
Q:	Q: Is this it? Are these all the types?
A:	A: No, but these are the most important ones. Data types also differ slightly by RDBMS, so you’ll need to consult your particular documentation for more information. We recommend SQL in a Nutshell (O’Reilly) as a particularly good reference book that spells out the differences between RDBMSs.

Which Data Type?

Determine which data type makes the most sense for each column. While you’re at it, fill in the other missing info.

Bullet Points

• Break your data up in categories before you create your table. Pay special attention to the type of data for each column.

• Use the CREATE DATABASE statement to create the database which will hold all of your tables.

• Use the USE DATABASE statement to get inside your database to create your table.

• All tables are created with a CREATE TABLE statement, containing column names and their corresponding data types.

• Some of the most common datatypes are CHAR, VARCHAR, BLOB, INT, DEC, DATE, and DATETIME. Each has different rules for what goes inside.

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:

You try it.

Your table, DESCribed

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

Brain Power

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

SQL Magnets

The code to create the database and table with the new gender column is all scrambled up on the fridge. Can you reconstruct the code snippets to make it work? Some of the parentheses and semicolons fell on the floor and they were too small to pick up, so feel free to add as many of those as you need!

When you finish, try typing the new CREATE TABLE code into your SQL console to add the new gender column!

SQL Magnets Solution

Your job was to reconstruct the code snippets to make the code that would create the database and table with the new gender column.

You can’t recreate an existing table or database!

There are no Dumb Questions

Q:	Q: About that SQL Magnets exercise, why did I get an error?
A:	A: You can’t create a table that already exists. And once you create a database, you don’t need to create it again. Other possible errors include you forgetting the semicolon. Also, check to see if you typoed any of the SQL keywords.
Q:	Q: Why isn’t there a comma after “seeking VARCHAR(100)” like all the other columns have?
A:	A: The column ‘seeking” is the last of them before we reach the closing parenthesis. That tells the RDBMS that the end of the statement is here, so no comma is needed.
Q:	Q: So, is there a way to add the forgotten column or will I have to start over?
A:	A: You’re going to have to start over, but before you can create the table with the added gender column you have to get rid of the old one. Since there is no data in the table yet, we can simply get rid of the old one away and start over.
Q:	Q: But what if I’ve got a table with data in it, and I need to add a column? Is there a way to do it without deleting the whole table and starting over?
A:	A: Great question! There is a way to change your table without damaging the data in it. We’ll get to that a bit later, but for now, since our table is empty, we’ll get rid of the table and create a new one.

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:

   

   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:

   

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

Who am I?

Â	Name
I’ve got your number.	________________________
I can dispose of your unwanted tables.	________________________
T or F questions are my favorite.	________________________
I keep track of your mom’s birthday.	________________________
I got the whole table in my hands.	________________________
Numbers are cool, but I hate fractions.	________________________
I like long, wordy explanations.	________________________
This is the place to store everything.	________________________
The table wouldn’t exist without me.	________________________
I know exactly when your dental appointment is next week.	________________________
Accountants like me.	________________________
I can give you a peek at your table format.	________________________
Without us, you couldn’t even create a table.	________________________
Â	________________________

Answers in Who am I?.

Anatomy of a Statement

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.

Who Does what?

Before you can write your INSERT statement, you need to match up your column names and values.

Columns	Values
first_name	'Relationship, Friends'
status	'Anderson'
seeking	'1980-09-05'
gender	'Technical Writer'
birthday	'Jillian'
last_name	'Single'
location	'F'
interests	'Palo Alto, CA'
profession	'[e-mail]'
email	'Kayaking, Reptiles'

Who Does what?

Before you can write your INSERT statement, you need to match up your column names and values.

Create the INSERT statement

Watch it!

Order matters!

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

Exercise

Try this at home

This is one way to add a row to your table. Try typing it in yourself. Type it in a text editor first so if you make a mistake you won’t have to retype the entire thing. Pay special attention to the single quotes and commas. Write the response you get here:

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.

Sharpen your pencil

Your SQL RDBMS will tell you when something is wrong with your statement, but will sometimes be a bit vague. Take a look at each INSERT statement below. First try to guess what’s wrong with the statement, and then try typing it in to see what your RDBMS reports.

INSERT INTO my_contacts

(last_name, first_name, email, gender, birthday, profession, location, status,
interests, seeking) VALUES ('Anderson', 'Jillian', '[e-mail]',
'F', '1980-09-05', 'Technical Writer', 'Single', 'Kayaking, Reptiles', 'Relationship,
Friends');

• What’s wrong? ________________________________________________________

• Your RDBMS says: _____________________________________________________

INSERT INTO my_contacts

(last_name, first_name, gender, birthday, profession, location, status, interests,
seeking) VALUES ('Anderson', 'Jillian', '[e-mail]', 'F',
'1980-09-05', 'Technical Writer', 'Palo Alto, CA', 'Single', 'Kayaking, Reptiles',
'Relationship, Friends');

• What’s wrong? ________________________________________________________

• Your RDBMS says: _____________________________________________________

INSERT INTO my_contacts

(last_name, first_name, email, gender, birthday, profession, location, status,
interests, seeking) VALUES ('Anderson', 'Jillian', '[e-mail]',
'F', '1980-09-05', 'Technical Writer' 'Palo Alto, CA', 'Single', 'Kayaking, Reptiles',
'Relationship, Friends');

• What’s wrong? ________________________________________________________

• Your RDBMS says: _____________________________________________________

INSERT INTO my_contacts

(last_name, first_name, email, gender, birthday, profession, location, status,
interests, seeking) VALUES ('Anderson', 'Jillian', '[e-mail]',
'F', '1980-09-05', 'Technical Writer', 'Palo Alto, CA', 'Single', 'Kayaking, Reptiles',
'Relationship, Friends');

• What’s wrong? ________________________________________________________

• Your RDBMS says: _____________________________________________________

Note

If this one causes your RDBMS to “hang,” try typing a single quote followed by a semicolon after you’ve entered the rest of the statement.

Sharpen your pencil

Your SQL RDBMS will tell you when something is wrong with your statement, but will sometimes be a bit vague. Take a look at each INSERT statement below. First try to guess what’s wrong with the statement, and then try typing it in to see what your RDBMS reports.

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!

   

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.)

   

3. Leaving some columns out

   You can insert a few columns and leave some out.

   

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:

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.

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

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?

SQL Exposed

This week’s interview: Confessions of a NULL

Head First: Welcome, NULL. I have to admit I didn’t expect to see you. I didn’t think you actually existed. Word on the street is that you’re nothing more than a zero, or nothing at all.

NULL: I can’t believe you’d listen to such lies. Yes, I’m here, and I’m quite real! So you think I’m nothing, just dirt under your feet?

Head First: Easy there, calm down. It’s just that you show up whenever something has no value...

NULL: Sure, better me than, say, a zero, or an empty string.

Head First: What’s an empty string?

NULL: That would be if you used two single quotes with nothing inside of them as a value. It’s still a text string, but of length zero. Like setting a value for first_name in the my_contacts table to ’’.

Head First: So you aren’t just a fancy way of saying nothing?

NULL: I told you, I’m not nothing! I’m something... I’m just a bit... undefined, is all.

Head First: So you’re saying that if I compared you to a zero, or to an empty string, you wouldn’t equal that?

NULL: No! I’d never equal zero. And actually, I’d never even equal another NULL. You can’t compare one NULL to another. A value can be NULL, but it never equals NULL because NULL is an undefined value! Get it?

Head First: Calm down and let me get this straight. You aren’t equal to zero, you aren’t an empty string variable. And you aren’t even equal to yourself? That makes no sense!

NULL: I know it’s confusing. Just think of me this way: I’m undefined. I’m like the inside of an unopened box. Anything could be in there, so you can’t compare one unopened box to another because you don’t know what’s going to be inside of each one. I might even be empty. You just don’t know.

Head First: I’ve been hearing rumors that sometimes you aren’t wanted. That maybe there are times where you NULLs cause problems.

NULL: I’ll admit that I’ve shown up where I wasn’t wanted before. Some columns should always have values. Like last names, for example. No point to having a NULL last name in a table.

Head First: So you wouldn’t go where you weren’t wanted?

NULL: Right! Just tell me, man! When you’re creating your table and setting up your columns, just let me know.

Head First: You don’t really look like an unopened box.

NULL: I’ve had enough. I’ve got places to go, values to be.

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.

Sharpen your pencil

Look at each of the columns in our my_contacts CREATE TABLE command. Which should be set to be NOT NULL? Think about columns that should never be NULL and circle them.

We’ve given you two to start, now finish up the rest. Primarily consider columns that you’ll use later to search with or columns that are unique.

Sharpen your pencil Solution

Look at each of the columns in our my_contacts CREATE TABLE command. Which should be set to be NOT NULL? Think about columns that should never be NULL and circle them.

We’ve given you two to start, now finish up the rest. Primarily consider columns that you’ll use later to search with or columns that are unique.

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.

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.

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

Tablecross

Take some time to sit back and give your left brain something to do. It’s your standard crossword; all of the solution words are from this chapter.

Across

Down

4. A _____ is a container that holds tables and other SQL structures related to those tables. 5. A _____ is a piece of data stored by your table. 6. This holds text data of up to 255 characters in length. 7. You can’t compare one _____ to another. 10. End every SQL statement with one of these. 12. This is a single set of columns that describe attributes of a single thing.

1. This is the structure inside your database that contains data, organized in columns and rows. 2. Use this in your CREATE TABLE to specify a value for a column if no other value is assigned in an INSERT. 3. Use this keyword to see the table you just created. 5. This word can be used in front of both TABLE or DATABASE. 8. To get rid of your table use _____ TABLE. 9. This datatype thinks numbers should be whole, but he’s not afraid of negative numbers. 11. To add data to your table, you’ll use the _____ statement.

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.

Bullet Points

• If you want to see the structure of your table, use the DESC statement.

• The DROP TABLE statement can be used to throw away your table. Use it with care!

• To get your data inside your table, use one of the several varieties of INSERT statements.

• A NULL value is an undefined value. It does not equal zero or an empty value. A column with a NULL value IS NULL, but does not EQUAL NULL.

• Columns that are not assigned values in your INSERT statements are set to NULL by default.

• You can change a column to not accept a NULL value by using the keywords NOT NULL when you create your table.

• Using a DEFAULT value when you CREATE your table fills the column with that value if you insert a record with no value for that column.

Who am I?

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

DataAndTablescross Solution