Simple SQL: Pt. 1

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:

You would run a query like this:

SELECT * FROM CUSTOMERS
       WHERE LAST_NAME = "Grayson";

And get these results:

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.