Head First SQL

Chapter 6. Advanced Select: Seeing your data with new eyes

It’s time to add a little finesse to your toolbox. You already know how to SELECT data and use WHERE clauses. But sometimes you need more precision than SELECT and WHERE provide. In this chapter, you’ll learn about how to order and group your data, as well as how to perform math operations on your results.

Dataville Video is reorganizing

The owner of Dataville Video has a badly organized store. In his current system, movies can end up on different shelves depending on which employee is shelving them. He’s ordered new shelves, and he thinks it’s great time to finally label each of his movie categories.

To:	Dataville Video Staff
From:	The Boss
Subject:	New shelves mean new categories!
Hi gang, The new shelves are in, so I want you to organize our movies. We can use the following categories:
Action & Adventure Drama Comedy Family Horror SciFi & Fantasy Misc
I’ll leave it to you to figure out how to make our current table work with these new categories.
Let’s do lunch,
Your boss

In the current system, true and false values are used for types of movies. This makes figuring out how to categorize difficult. For example, if a movie has both T for comedy and T for scifi, where should it be shelved?

Problems with our current table

Here’s ...