Login

Speaking SQL (part 2)

After learning how to insert and edit data in a database, the
second part of our SQL tutorial takes an in-depth look at the SELECT
query,and explains how to use joins, sub-queries and built-in functions to
focus in on the data you need.
Last time out, I showed you how to get your data into a database with the INSERT
statement, and edit it with the UPDATE and DELETE statements. And this time, I’m
going to show you how to extract specific “slices” of data from your database
using a variety of SELECT statements.

Let’s get started!

This article copyright Melonfire 2001. All rights
reserved.{mospagebreak title=Christmas Presents} Throughout this article,
I’ll be using the database I developed last time to better illustrate the
various examples. Just to refresh your memory, here’s what it looked like:

The asterisk (*) indicates that you’d like to see all the
columns present in the table. If, instead, you’d prefer to see one or two
specific columns only in the result set, you can specify the column name(s) in
the SELECT statement, like this:

This article copyright Melonfire
2001. All rights reserved. {mospagebreak title=Where, Oh Where, Art
Thou?} Of course, the whole idea of structuring data into rows and columns is to
make it easier to get a focused result set. And a great part of that focus comes
from the WHERE clause (maybe you remember this from the UPDATE and DELETE
statements you learnt last time) to the SELECT statement, which allows you to
define specific criteria for the result set. Records that do not meet the
specified criteria will not appear in the result set.

For example, let’s
suppose that you wanted to see a list of all members with the last name “Doe”.

Yes, I know the collection is incomplete. Maybe I should
write to Santa for the other three.

This article copyright Melonfire 2001. All rights reserved.
{mospagebreak title=Teacher’s Pet} You can use relational and Boolean operators
to modify your SQL query further – this comes in very handy if your table
contains a large amount of numeric data, as illustrated below:

You can also use the
Boolean operators AND, OR and NOT to create more complex queries.Now,
looking at the table above, if you wanted a list of all students who scored over
90 in their math paper, you could formulate a query which looked like this:

Suppose you wanted to identify the smartest kid in class (you
know this guy – he always sits in the front row, answers every question
perfectly, and usually has wires on his teeth) so that you could beat him up
during break.

Obviously, such an operation should only be attempted on
fields of the same type.

This article copyright Melonfire 2001. All rights reserved.
{mospagebreak title=Reading Backwards} If you’d like to see the data from your
table ordered by a specific field, SQL offers the ORDER BY construct. This
construct allows you to specify both the column name and the direction in which
you would like to see data (ascending or descending).

For example, if
you’d like to see data from the “members” table above arranged by id, you could
try this:

This article copyright Melonfire
2001. All rights reserved. {mospagebreak title=Count() Me In} SQL also
offers a bunch of built-in functions that come in handy when trying to obtain
numeric totals and averages of specific fields. The first of these is the very
useful COUNT() function, which counts the number of records in the result set
and displays this total.

Consider the following example, which displays
the total number of records in the “videos” table:

This comes in very handy when you need to quickly calculate
the total number of records in a table.

The SUM() function calculates the
sum of the values in the result set, while the AVG() function calculates the
average. For example, if you wanted to calculate the average grade in math,
physics and literature, you could use a query like this:

This article copyright Melonfire
2001. All rights reserved. {mospagebreak title=Like, You Know, Man…}
SQL also offers the LIKE keyword, which is used to return results from a
wildcard search and comes in very handy when you’re not sure what you’re looking
for. There are two types of wildcards allowed in a LIKE construct: the %
character, which is used to signify zero or more occurrences of a character, and
the _ character, which is used to signify exactly one occurrence of a
character.

Let’s suppose I wanted a list of all members whose first names
contained the letter “e”. My query would look like this:

This article copyright Melonfire
2001. All rights reserved. {mospagebreak title=Joining Them Together} So
far, all the queries you’ve seen have been concentrated on a single table. But
SQL also allows you to query two or more tables at a time, and display a
combined result set. This is technically referred to as a “join”, since it
involves “joining” different tables at specific points to create new views of
the data.

When using a join, it’s recommended that you prefix each column
name with the name of the table it belongs to (I haven’t done this in any of the
examples you’ve seen so far because all the columns have been localized to a
single table.). For example, you would use “members.fname” to refer to the
column named “fname” in the table “members”, and “status.video_id” to refer to
the “video_id” column in the “status” table.

You can also join three tables together – the following
example uses the “status” table, combined with member information and video
details, to create a composite table which displays which members have which
videos.

Incidentally, if the thought of writing long table names over
and over again doesn’t appeal to you, you can assign simple aliases to each
table and use these instead. The following example assigns the aliases “m”, “s”
and “v” to the “members”, “status” and “videos” tables respectively.

This article copyright Melonfire
2001. All rights reserved. {mospagebreak title=Nest Egg} SQL also allows
you to nest one query within another, such that the result of the inner query
provides data for the outer query. Such a query is referred to as a sub-query,
and it allows a great deal of flexibility when formulating long and complex
queries.

Let’s suppose you want to find out who’s rented “The Phantom
Menace” this weekend. You could of course perform a join, as described on the
previous page, and get the answer to your question. Or you could formulate a
subquery, which would look like this:

mysql> select fname, lname from members where member_id=(select member_id
from status where video_id=1);

There is a limit on the number of subqueries you can use in a
single SQL statement, but it’s usually quite a comfortable number. Note,
however, that mySQL does not currently support SQL subqueries.

And that’s
about it. I hope this introduction to SQL helped you get some idea of how to go
about creating and using a database, and that you now have a better
understanding of the language. Till next time – stay healthy!