ColdFusion 4.0 Primer

By: Chris Beasley, Published: 2003-05-09, Parent Category: ColdFusion

Basic SQL

Now that you know some basic CF you need to learn some basic SQL. SQL is a very straightforward language. Look at the following statement:

Figure 4. Basic SQL 
SELECT Name Height Weight Sex 
FROM Friends

Suppose you had a database of all your friends, you would name the table Friends and name the form fields Name, Height, Weight, and Sex. What the above SQL statement would do is return a list of your friends, along with their Height, Weight, and Sex, simple enough right?. Now look at the next statement:

Figure 5. Where Statement 
SELECT Name Height Weight 
FROM Friends 
WHERE Sex LIKE 'Male'

What this statement would do is return all your male friends. Notice the line beginning with WHERE, this is referred to as the criteria expression. The word LIKE is a search clause. See Table 1 for other search clauses.

Table 1. Search Clauses
ClauseDetailsExample
= Equal to LastName = 'Smith'
<> Not Equal to FirstName <> 'Kim'
< Less Than Age < '20'
<= Less Than or Equal to Age <= '20'
> Greater than Age > '20'
>= Greater Than or Equal to Age >= '20'
Between Tests inclusiveness Age between '20' and '30'
Exists Test for Existence FavMovie Exists
In Tests for list of values LastName IN ('Smith', 'Jones')
Is Null Tests for Null Value FavMovie IS NULL
Is Not Null Tests for value FavMovie IS NOT NULL
Like Tests for pattern FavMovie like "Godfather%"

You can include a wildcard(%) for "Like" criteria expressions. For instance if you wanted all of your friends who had a name that began with an "A" you would use the following example:

Figure 6. Wildcard 
SELECT FirstName, LastName 
FROM Friends 
WHERE FirstName LIKE 'A%'
Database Setup»
« Databases & Basic ColdFusion