Find a web host with: CGI Access DB Support Telnet Access NT Servers UNIX Servers Semi-automatic? Click here for Search Engine Site Submission! JavaScript JavaScript Helper: Meet Paige Turner, the least geeky geek we've ever come across. Variables and Operators Explained: First of a three part guide to JavaScript basics. Controlling Forms: Enhance your HTML forms with a touch of JS. DHTML: Forget how it works, let's see some in action!

by Ted Brockwood

Simple SQL

Your boss has given you a mission. It sounds simple enough - you just need to create a few custom reports on your intranet that connect to your company-wide database. Well, what if suddenly you discover the database is a legacy monstrosity filled with arcane language and structures? What if, heavens forbid, nobody can understand it? How will you keep your job?
March 1999

Simple SQL

1. Part One
2. Part Two
3. Part Three

If you're like some, you might burn a path to the fax machine to send out a flurry of résumés. Others, however, might first check and see if the legacy database is SQL-compliant. Assuming it is, you can probably hold off on the fax blizzard for now. You might just be in luck (and on your way to greater job security).

So what is SQL, and what can it do for you? SQL, contrary to popular belief, is not an acronym for "Structured Query Language". Rather, it's one of those odd acronyms that actually mean nothing at all. Through various mutations in spelling, the SQL acronym came about. Also it's pronounced "ess-queue-ell" in proper circles, not "sequel' as in most circles (including mine).

SQL, at its simplest, is a basic language that allows you to "talk" to a database and extract useful information. With SQL, you may read, write, and remove information from a database. SQL's non-procedural nature makes it easier to work with than other languages, but can at times lead to some messy code for the sloppy people out there (such as myself).

SQL is standardized, and the current version is referred to as SQL-92. Any SQL-compliant database should conform to the standards of SQL at the time. If not, they should state which flavor of SQL (SQL-89 for example) so that you can quickly figure out what features are and are not available. The standardization of SQL makes it an excellent tool for use in Web site design. Most Web application development toolkits, most notably Allaire's Cold Fusion and Microsoft's Visual InterDev, rely on SQL or SQL-like statements to connect to and extract information from databases. A solid foundation in SQL makes hooking databases to Web sites all the simpler.

Now, let's return to the mission given to you at the beginning of this article to see how SQL can help you.

The company database is not as old as you think, and much to your joy, it is SQL-92 compliant. This is great as it allows you to exploit more advanced SQL features. Let's say your first mission is to find any customers with the last name of "Grayson".

To view or extract data from a table, you will run a SELECT query. As the name implies, you are "selecting" specific data from the table and viewing it. SELECT queries are fairly obvious.

Assuming your database table (called CUSTOMERS) looks like this:

CUST_ID     FIRST_NAME  LAST_NAME   LAST_PURCHASE

1           Joe         Smith       December 5, 1996

4           Tom         Grayson     January 3, 1999

22          Joel        Jones       October 1, 1988

You would run a query like this:

SELECT * FROM CUSTOMERS

WHERE LAST_NAME = "Grayson";

And get these results:

CUST_ID   | FIRST_NAME  | LAST_NAME  | LAST_PURCHASE|

4         |Tom          |  Grayson   | January 3, 1999

Let's assume that, as is always the case, someone might have mis-keyed the last name during input. Perhaps they keyed in "Greyson" or "Graysun". How can you account for this possible error? With the power of wildcards of course!

Assuming the same table of data as before, your query might look like this:

SELECT * FROM CUSTOMERS

WHERE LAST_NAME LIKE "Gr*";

This will return all the data on any customers with a last name beginning with "GR". The "*" (asterisk) wildcard is very common, however, you will find some SQL applications that use "%" (percent sign) in place of, or in conjunction with the "*" character. Microsoft Access97, for example, has no problem with using "*", but MySQL for Linux uses both, and I've found it best to use "%" in MySQL. Check with your application's help files for the proper wildcard to use. For ease and consistency throughout this, I will be relying on "*" as the wildcard.

The boss now returns to your desk, pleased that you found the name of the contact he lost the business card for. Just to be a pain though, he decides he doesn't need anything more than the first and last name of Mr. Grayson. You can kill him, or you can modify your query to select only the fields you need. Again, assuming the same CUSTOMERS table, your query will now look like this:

SELECT FIRST_NAME, LAST_NAME FROM CUSTOMERS

WHERE LAST NAME LIKE "Gr*";

You will get the same results as before, however the CUST_ID and LAST_PURCHASE information will not display.

Dissecting the SELECT statement will help give you a further understanding of it. As with all statements in SQL, the keywords define the functions to be used, and the parameters to follow.

Example:

(1) SELECT FIRST_NAME  

(2) FROM CUSTOMERS

(3) WHERE LAST_NAME 

(4) LIKE "Gray*"

(5) ;

(1) The SELECT keyword tells the system what type of query you are going to run. The field after it (FIRST_NAME) tells it what field you want to view.
(2) The FROM keyword lets you choose the database table you are going to examine.
(3) WHERE is setting the criteria, or parameters for the data within the field.
(4) LIKE sets the conditions of the query, whether it is an exact match or a close match.
(5) The ";" closes out and executes the statement. Some SQL variants may use "\g" or even simply the word "go" to execute a statement.

The previous SELECT examples, while useful for learning, probably won't help you much in the real world. Most SELECT statements are far larger and more complex than what we've worked with so far. So we need to dive into comparisons and more sophisticated keyword usage.

An excellent keyword to get acquainted with is "DISTINCT". If you have data that is duplicated, but you only want to see one instance of it, DISTINCT is just the ticket.

For example, you have a table named CUSTOMERS. It is loaded with the names, addresses, phone numbers, and company names of all your best customers. Your boss needs to know what companies have been buying from you. Frequently you have multiple buyers from the same company, perhaps different divisions, so running a standard SELECT statement like this:

SELECT FIRST_NAME, LAST_NAME, COMPANY

FROM CUSTOMERS;

will return something like this:

FIRST_NAME      | LAST_NAME       | COMPANY

Joe             | Jones           | IsoLunacy, Inc

Donna           | Sprint          | MegaData Systems Corp

Will            | Brown           | IsoLunacy, Inc

As is obvious, you have two customers from IsoLunacy, Inc in your query results. If this were thousands of duplicates, it would be a terrible headache to create a list manually of companies you deal with. So, you fire off a DISTINCT query such as this:

SELECT DISTINCT COMPANY FROM CUSTOMERS;

Which returns:

|COMPANY|

|IsoLunacy, Inc. |

|MegaData Systems Corp |

SELECT statements are also adept at handling comparisons such as greater than and less than. Imagine the table "EMPLOYEES" with Names, Addresses, Start dates, vacation days accrued, and vacation days taken contained within it. The boss wants to know (for the Human Resources Intranet) whether anyone has taken more vacation days than they have accrued. Your SELECT might look like this:

SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES

WHERE VACATION_TAKEN > VACATION_ACCRUED;

The results might look like:

|FIRST_NAME       | LAST_NAME |

|Joe              | Smith           |

Good information for HR, and bad information for the employee names resulting from this query.

Other available comparison functions in SQL are:
> greater than
< less than
>= greater than or equal to
<= less than or equal to
= equal to

Mathematical functions can be performed in SQL as they are in most languages. Taking the previous example, assume we want to find out what the differences are for everyone between the vacation days taken, and what they have accrued. Your statement would be:

SELECT FIRST_NAME,LAST_NAME,

VACATION_TAKEN,VACATION_ACCRUED,

(VACATION_ACCRUED-VACATION_TAKEN)

FROM EMPLOYEES;

The resulting table would display the employees' first and last names, days taken, days accrued, and the difference between the two. Any employee with a negative in the last column might want to start job hunting.

Other mathematical functions include, but are not limited to:
+ addition
/ division
* multiplication

The WHERE clause can be strengthened by the use of AND, OR, and NOT operators. Through these three logical operators, one can string together more powerful queries.

Using the previous imaginary EMPLOYEES table as a base, we can create a query that returns the vacation days available for all the employees whose last name begins with "J". This query would be written as such:

SELECT FIRST_NAME, LAST_NAME,VACATION TAKEN, VACATION_ACCRUED

FROM EMPLOYEES

WHERE LAST_NAME LIKE "J*"

AND (VACATION_ACCRUED - VACATION_TAKEN) > 0;

Your output would be a list of first names, last names where the employee has at least one vacation day left.

Using OR allows you to be more flexible. Should you want the same data, but also for those employees with a last name starting with "K", you could use this statement:

SELECT FIRST_NAME, LAST_NAME,VACATION TAKEN, VACATION_ACCRUED

FROM EMPLOYEES

WHERE LAST_NAME LIKE "J*"

OR LAST_NAME LIKE "K*"

AND (VACATION_ACCRUED - VACATION_TAKEN) > 0;

As you can see by now, SQL allows for some very powerful data extraction capabilities. Along with simple SELECT statements, there are mathematical, logical, and comparison functions. In the next part of this series, I'll be covering grouping, ordering, sorting, and some advanced mathematical functions. Later, we'll look at queries that allow the user to add, modify, and delete data from databases.

Simple SQL

1. Part One
2. Part Two
3. Part Three

Ted Brockwood is the Information Services Manager for a real estate listing service in Oregon. His experience covers Java, Linux, UNIX, NT, Win95/98, Win3.x, and DOS.

Other articles by Ted Brockwood:

NetObjects Fusion

Drumbeat 2000

Linux

BeOS

PhP