Database Basics: Part 6

Use
these bookmarks to jump around the tutorial:

[What
Are Aggregate Functions?]

[What’s
So Great About DISTINCT?]

[How
do I Delete Stuff?]

[What’s
Next?]

Now for some more
functions that will help you get the data you want out of the database. And
finally, we will live dangerously and start deleting stuff.

What
Are Aggregate Functions?

Wouldn’t it be great if
SQL provided you with some basic functions for averaging, counting or finding
the largest or smallest number in a set of numbers? Well, I’m sure you’ve
guessed that aggregate functions do exactly that.

Instead of just
filtering your data, aggregate functions actually return a value. That value can
be either a calculated value or a value from your data depending on the
function. Here are some of the most used aggregate functions:

Let’s take a look at
each function and see how useful they can be.

First, the MIN and MAX
functions. These two functions are polar opposites of one another. MAX return
the highest value in a column while MIN returns the lowest value.

So, when would you use
MAX and MIN? You could use MAX and MIN for all sorts of mathematical purposes
like finding the largest sale on a given day or which student had the lowest
test score in a class. An example of this would be:

SELECT MAX(sale_total)

FROM daily_receipts

WHERE sales_date =
’01/01/2002′

This example gets the
MAXimum sale from the column "sale_total" in the table "sales_log" for sales
made on 1/1/2002.

You can also use MAX and
MIN to find dates. For example, I just recently built a website that displays
webcast seminar events and allows users to register. The home page highlights
the next event scheduled and displays a description of the event. Here is a
portion of the SQL that I used to get the data I wanted:

SELECT MIN(event_date)

FROM event

WHERE event_date >
GETDATE();

This gives me the date
of the event that is the closest to today’s date that hasn’t already past.

Can I use MIN and MAX
with a string of characters? Some DBMSs support using MIN and MAX on strings.
The result would be either the first or last row if the column were sorted
alphabetically.

Let’s SUM it up now.
Alright, bad pun but I couldn’t resist. As my friend would say, "that’s 2/3 of a
pun … P.U." Anyway, SUM does exactly what you would think, it adds together
the all of the rows in a given column. For example, you may use SUM to total the
sales for a given day in a retail store. Here’s an example:

SELECT SUM(sales)

FROM daily_receipts

WHERE sales_date =
’01/01/2002′;

Then there’s AVG.
Average is about as straight forward as it gets. Average is like a combination
of SUM and COUNT with a little division thrown in. We’ll use AVG on our example
above to get the average sale for a given day:

SELECT AVG(sales)

FROM daily_receipts

WHERE sales_date =
’01/01/2002′;

Then there’s COUNT. You
should remember COUNT from Part 5. You will probably find yourself using this
function quite a bit. You could use COUNT to find the total number of orders in
a day, how many people signed up for class, how many messages are posted to a
discussion group and  many other instances. Here’s an example of using
COUNT to get the number of messages posted to a discussion group:

SELECT COUNT(message)
AS message_count

FROM discussion_group

WHERE category = ‘SQL’;

Like I’ve said before,
every DBMS is different and each one will have its own set of unique functions.
So, if you are looking for something a little more specific than the basic
functions we went through above, consult your DBMS documentation.

back to
top

What’s
So Great About DISTINCT?

We’ll continue on with
the discussion group scenario. Let’s say that your discussion group has grown to
over 20 categories. The administrator of your discussion group keeps adding
categories right and left. Instead of manually adding category names to all the
forms and drop-down list boxes in your web by hand you decide it would be better
to simply retrieve the information from the database and populate your forms
automatically.

How would you go about
this? If you do a SELECT on the "category" column of your table you will have a
very long list of categories with lots of duplication. To accomplish your task
you will want to use the DISTINCT clause. Here’s how you would get the category
list using DISTINCT:

SELECT DISTINCT
category

FROM discussion_group;

Now you will have a
complete list of categories with no duplicates because only unique values are
returned.

Can I use DISTINCT with
some of the aggregate functions above? You sure can, however, it doesn’t make
sense with some of the functions. For example, Why use DISTINCT with MIN or MAX
when they are returning a single value anyway?

With the other functions
you may find a use for DISTINCT. For example, what if you wanted to count the
number of categories in your discussion group? You might combine COUNT with
DISTINCT like this:

SELECT COUNT(DISTINCT
category)

FROM discussion_group;

This will give you the
total number of unique categories in your discussion group.

back to
top

How Do I Delete Stuff?

Now for the scary part,
deleting data. Obviously, removing data from your database is necessary from
time to time. It can be a very scary thing, though. What if you accidentally
delete some very important data? What if you accidentally delete an entire
table?

These are all very real
possibilities, so take great care when issuing a DELETE to your database.
Unfortunately, deleting data is all too simple. Here is an example of how to
delete a person from our sample database we created way back in Part 2:

DELETE FROM
contacts

WHERE contact_id = 3;

In this case DELETE
remove the row in table "contacts" where the "contact_id" is equal to 3. That
means Bill Murray is history.

So, what happens if I
forget the WHERE clause? Well, that would be very bad news because you most
likely just wiped out all of the data in the contacts table. The WHERE clause is
so very important here. If you don’t get it exactly right you could end up
deleting something you really wanted to keep. If you forget the WHERE clause
altogether you will probably find yourself scrambling to locate the last backup
of your database. So, being careful with DELETE cannot be stressed enough!

In some cases, DELETE
may even be restricted by the Database Administrator to prevent the accidental
deletion of data.

back to
top

What’s Next?

Congratulations! You
have the basics down now. In the last part of this series we’ll explore some
great uses for databases on the web and try to wrap up any loose ends.

In Part 7 of this series
we will:

• Basic SQL Wrap-up

• Common Database Uses
  on the Web

back to
top

<< Previous | Next
>>