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. 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.