Head First SQL

How to use this Book: Intro

In this section, we answer the burning question: “So why DID they put that in an SQL book?”

Who is this book for?

If you can answer “yes” to all of these:

1. Do you have access to a computer with an RDBMS installed on it, like Oracle, MS SQL, or MySQL? Or one that you can install MySQL, or other RDBMS on?

2. Do you want to learn, understand, and remember how to create tables, databases, and write queries using the best and most recent standards?

   Note

   We’ll help you learn SQL concepts and syntax in a way that will definitely make it easier for you to understand and actually use. SQL precisely the way you need to use it.

3. Do you prefer stimulating dinner party conversation to dry, dull, academic lectures?

this book is for you.

Who should probably back away from this book?

If you can answer “yes” to any of these:

1. Are you completely comfortable with beginning SQL syntax and seeking something that will help you with advanced database design?

   Note

   But if you would like a refresher, and never quite understood normal form and one-to-many and left outer joins, this book can help you.

2. Are you already an experienced SQL programmer and looking for a reference book on SQL?

3. Are you afraid to try something different? Would you rather have a root canal than mix stripes with plaid? Do you believe that a technical book can’t be serious if SQL concepts are anthropomorphized?

this book is not for you.

[Note from marketing: this book is for anyone with a credit card.]

We know what you’re thinking.

• “How can this be a serious SQL book?”

• “What’s with all the graphics?”

• “Can I actually learn it this way?”

And we know what your brain is thinking.

Your brain craves novelty. It’s always searching, scanning, waiting for something unusual. It was built that way, and it helps you stay alive.

So what does your brain do with all the routine, ordinary, normal things you encounter? Everything it can to stop them from interfering with the brain’s real job—recording things that matter. It doesn’t bother saving the boring things; they never make it past the “this is obviously not important” filter.

How does your brain know what’s important? Suppose you’re out for a day hike and a tiger jumps in front of you, what happens inside your head and body?

Neurons fire. Emotions crank up. Chemicals surge.

And that’s how your brain knows...

This must be important! Don’t forget it!

But imagine you’re at home, or in a library. It’s a safe, warm, tiger-free zone. You’re studying. Getting ready for an exam. Or trying to learn some tough technical topic your boss thinks will take a week, ten days at the most.

Just one problem. Your brain’s trying to do you a big favor. It’s trying to make sure that this obviously non-important content doesn’t clutter up scarce resources. Resources that are better spent storing the really big things. Like tigers. Like the danger of fire. Like how you should never again snowboard in shorts.

And there’s no simple way to tell your brain, “Hey brain, thank you very much, but no matter how dull this book is, and how little I’m registering on the emotional Richter scale right now, I really do want you to keep this stuff around.”

We think of a “Head First” reader as a learner.

So what does it take to learn something? First, you have to get it, then make sure you don’t forget it. It’s not about pushing facts into your head. Based on the latest research in cognitive science, neurobiology, and educational psychology, learning takes a lot more than text on a page. We know what turns your brain on.

Some of the Head First learning principles:

Make it visual. Images are far more memorable than words alone, and make learning much more effective (up to 89% improvement in recall and transfer studies). It also makes things more understandable. Put the words within or near the graphics they relate to, rather than on the bottom or on another page, and learners will be up to twice as likely to solve problems related to the content.

Use a conversational and personalized style. In recent studies, students performed up to 40% better on post-learning tests if the content spoke directly to the reader, using a first-person, conversational style rather than taking a formal tone. Tell stories instead of lecturing. Use casual language. Don’t take yourself too seriously. Which would you pay more attention to: a stimulating dinner party companion, or a lecture?

Get the learner to think more deeply. In other words, unless you actively flex your neurons, nothing much happens in your head. A reader has to be motivated, engaged, curious, and inspired to solve problems, draw conclusions, and generate new knowledge. And for that, you need challenges, exercises, and thought-provoking questions, and activities that involve both sides of the brain and multiple senses.

Get—and keep—the reader’s attention. We’ve all had the “I really want to learn this but I can’t stay awake past page one” experience. Your brain pays attention to things that are out of the ordinary, interesting, strange, eye-catching, unexpected. Learning a new, tough, technical topic doesn’t have to be boring. Your brain will learn much more quickly if it’s not.

Touch their emotions. We now know that your ability to remember something is largely dependent on its emotional content. You remember what you care about. You remember when you feel something. No, we’re not talking heart-wrenching stories about a boy and his dog. We’re talking emotions like surprise, curiosity, fun, “what the...?”, and the feeling of “I Rule!” that comes when you solve a puzzle, learn something everybody else thinks is hard, or realize you know something that “I’m more technical than thou” Bob from engineering doesn’t.

Metacognition: thinking about thinking

If you really want to learn, and you want to learn more quickly and more deeply, pay attention to how you pay attention. Think about how you think. Learn how you learn.

Most of us did not take courses on metacognition or learning theory when we were growing up. We were expected to learn, but rarely taught to learn.

But we assume that if you’re holding this book, you really want to learn about project management. And you probably don’t want to spend a lot of time. And since you’re going to take an exam on it, you need to remember what you read. And for that, you’ve got to understand it. To get the most from this book, or any book or learning experience, take responsibility for your brain. Your brain on this content.

The trick is to get your brain to see the new material you’re learning as Really Important. Crucial to your well-being. As important as a tiger. Otherwise, you’re in for a constant battle, with your brain doing its best to keep the new content from sticking.

So just how DO you get your brain to think that SQL is a hungry tiger?

There’s the slow, tedious way, or the faster, more effective way. The slow way is about sheer repetition. You obviously know that you are able to learn and remember even the dullest of topics if you keep pounding the same thing into your brain. With enough repetition, your brain says, “This doesn’t feel important to him, but he keeps looking at the same thing over and over and over, so I suppose it must be.”

The faster way is to do anything that increases brain activity, especially different types of brain activity. The things on the previous page are a big part of the solution, and they’re all things that have been proven to help your brain work in your favor. For example, studies show that putting words within the pictures they describe (as opposed to somewhere else in the page, like a caption or in the body text) causes your brain to try to makes sense of how the words and picture relate, and this causes more neurons to fire. More neurons firing = more chances for your brain to get that this is something worth paying attention to, and possibly recording.

A conversational style helps because people tend to pay more attention when they perceive that they’re in a conversation, since they’re expected to follow along and hold up their end. The amazing thing is, your brain doesn’t necessarily care that the “conversation” is between you and a book! On the other hand, if the writing style is formal and dry, your brain perceives it the same way you experience being lectured to while sitting in a roomful of passive attendees. No need to stay awake.

But pictures and conversational style are just the beginning.

Here’s what WE did

We used pictures, because your brain is tuned for visuals, not text. As far as your brain’s concerned, a picture really is worth a thousand words. And when text and pictures work together, we embedded the text in the pictures because your brain works more effectively when the text is within the thing the text refers to, as opposed to in a caption or buried in the text somewhere.

We used redundancy, saying the same thing in different ways and with different media types, and multiple senses, to increase the chance that the content gets coded into more than one area of your brain.

We used concepts and pictures in unexpected ways because your brain is tuned for novelty, and we used pictures and ideas with at least some emotional content, because your brain is tuned to pay attention to the biochemistry of emotions. That which causes you to feel something is more likely to be remembered, even if that feeling is nothing more than a little humor, surprise, or interest.

We used a personalized, conversational style, because your brain is tuned to pay more attention when it believes you’re in a conversation than if it thinks you’re passively listening to a presentation. Your brain does this even when you’re reading.

We included more than 80 activities, because your brain is tuned to learn and remember more when you do things than when you read about things. And we made the exercises challenging-yet-do-able, because that’s what most people prefer.

We used multiple learning styles, because you might prefer step-by-step procedures, while someone else wants to understand the big picture first, and someone else just wants to see an example. But regardless of your own learning preference, everyone benefits from seeing the same content represented in multiple ways.

We include content for both sides of your brain, because the more of your brain you engage, the more likely you are to learn and remember, and the longer you can stay focused. Since working one side of the brain often means giving the other side a chance to rest, you can be more productive at learning for a longer period of time.

And we included stories and exercises that present more than one point of view, because your brain is tuned to learn more deeply when it’s forced to make evaluations and judgments.

We included challenges, with exercises, and by asking questions that don’t always have a straight answer, because your brain is tuned to learn and remember when it has to work at something. Think about it—you can’t get your body in shape just by watching people at the gym. But we did our best to make sure that when you’re working hard, it’s on the right things. That you’re not spending one extra dendrite processing a hard-to-understand example, or parsing difficult, jargon-laden, or overly terse text.

We used people. In stories, examples, pictures, etc., because, well, because you’re a person. And your brain pays more attention to people than it does to things.

Here’s what YOU can do to bend your brain into submission

So, we did our part. The rest is up to you. These tips are a starting point; listen to your brain and figure out what works for you and what doesn’t. Try new things.

cut this out and stick it on your refrigerator.

1. Slow down. The more you understand, the less you have to memorize.

   Don’t just read. Stop and think. When the book asks you a question, don’t just skip to the answer. Imagine that someone really is asking the question. The more deeply you force your brain to think, the better chance you have of learning and remembering.

2. Do the exercises. Write your own notes.

   We put them in, but if we did them for you, that would be like having someone else do your workouts for you. And don’t just look at the exercises. Use a pencil. There’s plenty of evidence that physical activity while learning can increase the learning.

3. Read the “There are No Dumb Questions”

   That means all of them. They’re not optional sidebars—they’re part of the core content! Don’t skip them.

4. Make this the last thing you read before bed. Or at least the last challenging thing.

   Part of the learning (especially the transfer to long-term memory) happens after you put the book down. Your brain needs time on its own, to do more processing. If you put in something new during that processing time, some of what you just learned will be lost.

5. Drink water. Lots of it.

   Your brain works best in a nice bath of fluid. Dehydration (which can happen before you ever feel thirsty) decreases cognitive function.

6. Talk about it. Out loud.

   Speaking activates a different part of the brain. If you’re trying to understand something, or increase your chance of remembering it later, say it out loud. Better still, try to explain it out loud to someone else. You’ll learn more quickly, and you might uncover ideas you hadn’t known were there when you were reading about it.

7. Listen to your brain.

   Pay attention to whether your brain is getting overloaded. If you find yourself starting to skim the surface or forget what you just read, it’s time for a break. Once you go past a certain point, you won’t learn faster by trying to shove more in, and you might even hurt the process.

8. Feel something!

   Your brain needs to know that this matters. Get involved with the stories. Make up your own captions for the photos. Groaning over a bad joke is still better than feeling nothing at all.

9. Create something!

   Apply this to your daily work; use what you are learning to make decisions on your projects. Just do something to get some experience beyond the exercises and activities in this book. All you need is a pencil and a problem to solve...a problem that might benefit from using the tools and techniques you’re studying for the exam.

Read me

This is a learning experience, not a reference book. We deliberately stripped out everything that might get in the way of learning whatever it is we’re working on at that point in the book. And the first time through, you need to begin at the beginning, because the book makes assumptions about what you’ve already seen and learned.

We begin by teaching basic SQL syntax, then SQL database design concepts, and then advanced querying.

While it’s important to create well-designed tables and databases, before you can, you need to understand the syntax of SQL. So we begin by giving you SQL statements that you can actually try yourself. That way you can immediately do something with SQL, and you will begin to get excited about it. Then, a bit later in the book, we show you good table design practices. By then you’ll have a solid grasp of the syntax you need, and can focus on learning the concepts.

We don’t cover every SQL statement, function, or keyword.

While we could have put every single SQL statement, function, and keyword in this book, we thought you’d prefer to have a reasonably liftable book that would teach you the most important statements, functions, and keywords. We give you the ones you need to know, the ones you’ll use 95 percent of the time. And when you’re done with this book, you’ll have the confidence to go look up that function you need to finish off that kick-ass query you just wrote.

We don’t address every flavor of RDBMS.

There’s Standard SQL, MySQL, Oracle, MS SQL Server, PostgreSQL, DB2, and quite a few more RDBMSs out there. If we covered every variation in syntax for every command in the book, this book would have many more pages. We like trees, so we’re focusing on Standard SQL with a nod toward MySQL. All the examples in the book will work with MySQL. And most will work with any of the RDBMSs listed above. Remember that reference book we just suggested you buy? Buy one for the particular RDBMS that you use.

The activities are NOT optional.

The exercises and activities are not add-ons; they’re part of the core content of the book. Some of them are to help with memory, some are for understanding, and some will help you apply what you’ve learned. Don’t skip the exercises. The crossword puzzles are the only thing you don’t have to do, but they’re good for giving your brain a chance to think about the words and terms you’ve been learning in a different context.

The redundancy is intentional and important.

One distinct difference in a Head First book is that we want you to really get it. And we want you to finish the book remembering what you’ve learned. Most reference books don’t have retention and recall as a goal, but this book is about learning, so you’ll see some of the same concepts come up more than once.

The examples are as lean as possible.

Our readers tell us that it’s frustrating to wade through 200 lines of an example looking for the two lines they need to understand. Most examples in this book are shown within the smallest possible context, so that the part you’re trying to learn is clear and simple. Don’t expect all of the examples to be robust, or even complete—they are written specifically for learning, and aren’t always fully-functional.

We’ve placed many of the commands on the Web so you can copy and paste them into your terminal or database software. You’ll find them at http://www.headfirstlabs.com/books/hfsql/

The Brain Power exercises don’t have answers.

For some of them, there is no right answer, and for others, part of the learning experience of the Brain Power activities is for you to decide if and when your answers are right. In some of the Brain Power exercises, you will find hints to point you in the right direction.

The technical review team

Our amazing reviewers:

Huge thanks go to our tech review team. They caught innumerable blatant mistakes, subtle errors, and pathtetic typos. Without them, this book wouldn’t be anywhere near as clean and correct as it is. They did a thorough job of getting the errors out of this book.

Cary Collett put his 15 years of experience working at startups, government labs, and currently in the financial sector to use while reviewing the book, and is looking forward to getting back to enjoying his non-work things like cooking, hiking, reading and terrorizing his dogs.

LuAnn Mazza found time in her busy Illinois professional life as a Software Developer and Analyst, to do some incredibly timely and detailed reviews, we’re happy that she can now spend her spare time enjoying her hobbies including biking, photography, computers, music, and tennis

When Steve Milano isn’t coding in half a dozen different languages at his day job, doing a top-notch review of Head First SQL, or playing punk rock with his band Onion Flavored Rings in unventilated basements throughout the land, he can be found at home with his cats Ralph and Squeak.

“Shelley” Moira Michelle Rheams, MEd, MCP, MCSE teaches and runs the Early Childhood Education Program at Delgado Community College in New Orleans: West Bank Campus. Currently she enjoys putting education courses online to meet the needs of the changing New Orleans community post-Katrina, and we thank her for being able to fit us into her overbooked schedule.

Jamie Henderson is a senior systems architect sporting purple hair and dividing what spare time she has between cello, reading, video games, and watching movies on DVD.

This fantastic team is the reason that the code and exercises in this book will actually do what they are supposed to, and why, when you are finished with this book, you’ll be a confident SQL programmer. Their attention to detail also kept us from being too cute or too patronizing, or even, sometimes, too weird.

Acknowledgments

My editors:

First of all, I want to thank my editor, Brett McLaughlin, for not one, but two Head First boot camps. Brett was more than an editor—he was a combination sounding board and sherpa. There’s absolutely no way this book would have been written without his guidance, support, and interest. Not only did he “get me” from the very first audition, his appreciation of my sometimes over-the-top humor made this the best book writing experience I’ve ever had. He gave me a whole lot of advice, hints, and more than a little coaching throughout this whole process. Thanks, Brett!

Editor Catherine Nolan has a huge ulcer now, thanks to some incredibly bad luck I had near the end of the editorial process. She’s the reason this book didn’t come out in 2008, and perhaps the reason it exists at all. It was a bit like kitten juggling at the end, and she didn’t drop a single one. I badly needed a schedule, and Catherine is the best scheduler I’ve ever met. And I think I’ve been her biggest challenge so far. Let’s hope her next project goes more smoothly, she’s more than earned it.

The O’Reilly team:

Design Editor Louise Barr has been both a great friend and an amazing graphic designer. Somehow she was able to channel my crazy ideas into impressive art that make the difficult concepts very clear. All the great design is hers, and I have no doubt that at many points in this book you’ll want to thank her too.

But we would have gone to press with a whole lot of errors had it not been for the technical review process, and Sanders Kleinfeld did a great job as production editor, getting this book ready for press. He also went far, far beyond the call of duty, pointing out some conceptual chasms that really needed to be bridged. Thanks, Sanders!

Finally, I want to thank Kathy Sierra and Bert Bates for creating this wonderful series and for the best and most mentally challenging training I’ve ever had at the first Head First boot camp. Without those three days, well, I don’t even want to think about how much harder it would have been to be Head First-y. And Bert’s final editorial comments were painfully accurate, and vastly improved this book.