A Brief Introduction To SQL

Cold Fusion is actually a gateway to access of data in databases. The query language used is SQL, Structured Query Language. Within the Cold Fusion <cfquery> command you must specify a complete SQL command that accomplishes the desired operations. SQL is quite simple at the introductory level, consisting of one command with up to three parameters. This very brief introduction will guide you through the development of SQL commands to support completion of your Project 8 Cold Fusion project.

First, you need to remember what you did with Project 3 when you used the Proj3data.mdb Access database. The data table within the "mdb" file is the "Orders" table (what we call a file). The field names in the Orders table are: DATE, STATE, CHANNEL, PRICE, CATEGORY, UNITS, NETAMT

Simple SQL Command

Let's say that you want to query the Orders table and look at the STATE, CATEGORY, UNITS, and NETAMT fields. Here is the SQL command to query the table:

SELECT STATE,CATEGORY,UNITS,NETAMT
FROM ORDERS;

The SELECT command specifies the fields within the ORDERS table to use.

The FROM parameter specifies that the ORDERS table be used as the data source.

Note that the SQL command ends with a semi-colon. It won't hurt if you forget it.

That's it! Access will return a table of data with these fields. Try it in Access.

Limiting the Search with the WHERE parameter

If we want to specify search criteria to limit the scope of the data, we can use the WHERE parameter. It follows the FROM parameter. For example, if you want to limit the query data to include only data from the state of Washington, you specify this WHERE parameter: WHERE STATE = 'WA'

For text data, the query search argument is within single quotes. The state of Washington is represented in the data as WA, so the argument is 'WA'. Numeric data query search arguments are not put in single quotes. For example, WHERE NETAMT > 500    will return all records with a NetAmt of more than $500.

The WHERE parameter uses the logical operators AND, OR, and NOT. For example, if you want data from both Washington and Arizona, the WHERE parameter is: WHERE STATE = 'WA' OR STATE = 'AZ'     The variable STATE must be used for each search argument.

You must be careful when using logical operators. "OR" means "either one or the other condition is true", while "AND" means "both conditions must be true." This is incorrect: WHERE STATE = 'WA' AND STATE = 'AZ'      There is no record in the data where the STATE is both "WA" and "AZ" (Taking a course in Logic is always a good time investment.)

The WHERE parameter can also use mathematical operators. For example, WHERE NETAMT > 10000 specifies that only records with NET AMT amounts greater than 10000 be included. Note that numeric search arguments are not enclosed within double quotes.

This table illustrates the logical operators that you can use with the WHERE parameter:

ANDORNOT
= (equal)<> (not equal)< (less than)
<= (less than or equal to)> (greater than)>= (greater than or equal to)

Combinations of logical operators must be grouped within parentheses in order for them to work. For example, if you want data from Oregon or Nevada or California and you want data only for NetAmt greater than or equal to $500, then the "State" WHERE must be all within parentheses and the "Net Amt" WHERE must also be within parentheses. Look at this example.

WHERE (STATE = 'OR' OR STATE = 'NV' OR STATE = 'CA') AND (NETAMT >= 500)

Note that parentheses around the "State" condition and around the "NetAmt" condition are required to logically group them. Note also that the $500 amount doesn't have to be within double quotes since it is an amount. Note also that the "OR" refers to the abbreviation for Oregon, and has nothing to do with the OR logical operator. The 'OR' within single quotes indicates a literal value to use in the WHERE parameter, while the OR not within single quotes is the logical OR operator.

Sorting the Output with the ORDER Parameter

Data returned from an SQL query can be ordered (sorted) by use of the ORDER BY parameter. The ORDER BY parameter follows the WHERE parameter (if present) or the FROM parameter. For example, if you want the data to be sorted in STATE order, the ORDER parameter is: ORDER BY STATE     To sort the data by CATEGORY within STATE, the parameter is: ORDER BY STATE,CATEGORY     The first variable is the "major" sort field, and all subsequent fields are the "minor" or "secondary" sort fields.

Complete SQL Example

Here is a complete example. This SQL example uses the ORDERS table and the STATE, CATEGORY, UNITS, and NETAMT fields. The data from the states of Oregon, Nevada, and California are included. Only data with a NET AMT greater than or equal to $500 are included. The data are sorted by STATE.

SELECT STATE,CATEGORY,UNITS,NETAMT
FROM ORDERS
WHERE (STATE = 'OR' OR STATE = 'NV' OR STATE = 'CA') AND (NETAMT >= 500)
ORDER BY STATE;

That's basically it! There are lots and lots of SQL parameters, but these will get the job done for the vast majority of data queries that "normal" people construct.

Return to Project 8 Page