Data-Basics II: the SeQueL
Relational databases, according to professional database
lingo, are managed. That means that some piece of
software is used to create, modify, save, and query the
database. They call these software packages relational
database management systems, or RDBMS, but that's a big
mouthful of nothing. It's as if Microsoft Word were called a
textual processing modification system, or the way
they call shampoo a scientifically formulated hair
therapy system. You only need to know which database
package you will be using to manage your database -- Microsoft
Access is a very popular application for Windows 95/98
systems, of course, and is the one we will focus on first.
Why Access, which is a Windows-only product? Many Web servers
run in Unix environments, in which there is no easy way to
"access" an Access database, so our choice may seem
counterintuituve. On the other hand, in the interests of
accessibility, the reality is that many readers may already
have their database in Access format and wish to at least
practice generating Perl-based queries within the Windows
environment. Access is also an easy path to familiarity with
relational databases. Next month, the power hungry among you
should feel more sated with our focus on a Unix-based
database, MySQL.
Regardless of which database software you choose, it is
likely to (and had better) support SQL, or the
structured query language. (You may pronounce
SQL as "sequel" if you wish but there is no official rule).
It is SQL which defines the syntax and grammar, a sort of
mini-programming language, with which you construct queries
of the database.
This article isn't an appropriate place for a
full-fledged course in SQL programming, but you will need to
know how to construct SQL statements. Ultimately, you'll be
passing these statements from your Perl script to the
database. A good web-based tutorial for SQL is James Hoffman's
Introduction to Structured Query Language
which will take you beyond the simple introduction to SQL
presented here.
The primary SQL statement you will use to pull data from the
database is the select statement, which roughly
follows this template:
SELECT fields_to_return FROM table_name
WHERE field_to_test condition
Recall a row from our billing database:
| ClientName |
ClientEmail |
Invoice |
Total |
Paid |
| Loganberry Inc. |
logan@pluc.net |
099050101 |
50.00 |
Yes |
Using the knowledge of the SQL statement template, we can
translate our earlier requests into valid SQL queries:
|
"Please list all client names and their e-mail
addresses."
SELECT ClientName,ClientEmail FROM billing
|
|
"Please list all client names who were billed
at least $500."
SELECT ClientName FROM billing
WHERE Total>=500 |
|
"Please list all client names and e-mail
addresses who are unpaid."
SELECT ClientName,ClientEmail FROM billing
WHERE Paid=No
|
Because this is a relational database, we can also construct
slightly more complex SQL statements which relate data
across both tables, billing and invoices.
|
"Please list the names of all unpaid clients for
whom I worked at least 10 hours."
SELECT billing.ClientName FROM billing,invoices
WHERE invoices.Hours>=10 AND billing.Invoice=invoices.Invoice
|
|
"Please list the names and e-mail addresses of
all clients for whom I charged at least $70 per hour."
SELECT billing.ClientName,billing.ClientEmail FROM billing,invoices
WHERE invoices.Rate>=70 AND billing.Invoice=invoices.Invoice
|
This is about as advanced as our SQL queries will become in
this article, which is enough to illustrate queries from Perl.
Additional SQL resources, listed at the end of this article,
can provide you with far more detailed insight into the
strange and quirky world of the Structured Query Language.
IE 7
Firefox 2.0