Database Basics: Part 5
August 2, 2002Alright, you can get your data back out now. Let's look at some ways to make your DBMS do some extra work for you by sorting, grouping and narrowing down choices.
Some more about WHEREIn Part 4 you learned about using WHERE to filter your data. What if you have a much more complex set of criteria that you would like to apply to filter? Well, you're in luck. SQL gives you quite a lot of flexibility in defining how you filter your data.
What if I have more than one criteria? You can use AND and OR. Both AND and OR do exactly what you might think, they allow you to string as many conditions together as you like. Here's and example of both:
So, how does that work? It simply states that we are looking for anyone with the first name of John and the last name of Lennon or Mellencamp. They must have the first name of John but can have either the last name of Lennon or Mellencamp.
The parentheses not only help you to visually see how things are grouped together but they also let the DBMS know what to do. Imagine if you moved the parentheses around to look something like this:
Now what will the DBMS look for? In this case the DBMS will send back anyone named John Lennon or anyone with the last name of Mellencamp. Herman Mellencamp would be a valid name using this criteria but would not be valid in the first example.
How about this scenario. You want to find the data on John Mellencamp but you can't remember how to spell his last name. (Just play along ...) You do remember that the last 4 characters are "camp", though. How are you going to find him?
Well, SQL has included several wildcards to help you out. Different wildcards tell the DBMS to do different things. And just to make things more confusing, different DBMS's use different characters for their wildcards, so be sure to check your DBMS before you start throwing wildcards around.
In our scenario above this is how the wildcard would work:
Hey! What's that LIKE doing there? The LIKE lets the DBMS know that we won't be doing a strict comparison like = or < or > but we will be using wildcards in our comparison.
So, can I use LIKE and = in the same WHERE clause? You sure can just as long as you don't try to combine them into a single comparison like last_name = LIKE '%camp'.
Something like this would be valid, though:
Now, back to the wildcard itself. The wildcard tells the DBMS to look for anyone that has "camp" as the last 4 characters in their last name. The % says everything before the last 4 characters is irrelevant. So, what if you moved that % after the "p"?
It will return all last names beginning with "camp" which probably will be none since we never capitalized the "c". Remember, searching for a string (set of alphanumeric characters) is case sensitive. If you are ever unsure of the case of something you may want to convert everything to upper or lower case before you make your comparisons. I'll show you how to do this later in this series.
Alright, how about something like this now:
This will search for anyone with the characters "camp" anywhere in the last name. It will return all of these examples: "camp", "campbell" and "mocamp".
As you can see, it is very important where you place your wildcards. It is also important that you don't overuse the wildcards. Wildcards can easily get out of control if you overuse them.
There are other wildcards as well like the brackets  and the underscore _. These wildcards are a bit more complex and beyond the scope of this lesson but we may come back to them later.
What about Sorting?What if I want to sort my data? Do I have to write some customized sorting script to sort the data after I get it out?
Thankfully, no. SQL has already done that work for you. Here's an example of how you would retrieve and sort all of the last names in our "contacts" table that we created way back in Parts 1 and 2:
How's that for easy? The ORDER BY clause tells the DBMS to perform a sort on the data using the "last_name" column. Now the data that you get out will be pre-sorted by last name from A to Z.
So, what if I wanted it to be sorted in reverse order from Z to A? That's easy too:
In this case the DESC is used to tell the DBMS to sort by last name in descending order. By default a DBMS will sort in ascending order and you don't really need to specify it. If you are like me, though, and you want to make it abundantly clear to anyone reading your code what is going on you can use ASC in your ORDER BY clause like this:
What if I want to sort by more than one column? Can it be done?
Yep. To search by more than one column you list the columns you want to be sorted in the order that you want them sorted. If you wanted to sort by last name and then first name, for example, it would look something like this:
Sorting in SQL is straight forward and very very very handy. You will probably find yourself using ORDER BY frequently.
What about Grouping?Imagine this scenario, you are running a gift catalog business for corporations and you want to know how many items have been sold to each corporate client you have. To accomplish this task you will need to learn about COUNT and GROUP BY.
Let's take a look at COUNT first. COUNT keeps a running total of the number of records retrieved. As a general rule, you will usually put any count totals into a temporary column. Here's an example of how all of that might work:
So, what you end up with here is the total number of all of the rows on the "sales" table which will be stored in the temporary column of "sales_total". Remember, "sales_total" is not a permanent part of your table but is rather a temporary column that you can refer to to get the count number from. You can use whatever name you like as your temporary column name. This may not make much sense with this example but it will be more clear when we do the grouping in a minute.
Now, let's do a little grouping:
What this will do is search the "sales" table and count the total number of rows grouped by the company name which is what we want. The GROUP BY tells the DBMS to group everything by company name. The COUNT then keeps a running total in the temporary column "sales_by_company_total" in accordance with the GROUP BY clause. Notice I changed "sales_total" to "sales_by_company_total" so that it would be a bit more descriptive. I also added the "company_name" column in the table to the SELECT statement so that our output will have both the company's name and total sales. The output would look something like this:
So, what if I wanted to sort the grouped output? Not a problem, just add your ORDER BY clause at the end like this:
Grouping data can be almost as handy as sorting but you probably won't find as many occasions to use it. Knowing how to use the built in functions of SQL can save you a great deal of time, effort and frustration. So, before you try writing any custom scripting be sure to thoroughly check out all of the functions available with your DBMS. You will probably be surprised how much has already been done for you.