Programming instructions

64 Lesson 2 Writing Your First ColdFusion Application
For example, to select the columns named Last Name and First Name for Clients whose
City is Boston, use the following SELECT statement:
SELECT LastName, FirstName FROM Clients Where City = 'Boston'
The results of the preceding SQL statement contains the following data:
You can compose a WHERE clause with one or more conditions; these are called
subclauses. You join subclauses using the operators AND and OR.The AND operator
displays a row if ALL conditions listed are true. The OR operator displays a row if ANY
of the conditions listed are true. An example of a WHERE clause with multiple
subclauses follows:
SELECT LastName FROM Clients Where City = 'Boston' AND FirstName = 'Anita'
The results of the preceding SQL statement contains the following data:
Note: The preceding SQL SELECT examples use single quotation marks around the
value. SQL uses single quotation marks around text values. Most database systems will also
accept double quotation marks. Do not enclose numeric values in quotation marks.
Sorting the results
You use the ORDER BY clause to sort the result rows. The following SQL statement
returns an alphabetic list of people sorted by last name then first name from the Clients
table:
SELECT * FROM Clients Order By LastName, FirstName
The default is to return the results in ascending order (top to bottom). If you include the
DESC keyword in the ORDER BY clause, the rows are returned in descending order
(bottom to top).
The following statement returns a reverse alphabetic list of the Clients table:
SELECT * FROM Clients Order By LastName, FirstName DESC
Note: The SQL SELECT statement is quite powerful. There are several other options for
retrieving data from a SQL database using the SELECT statement, which are not described
in this book. For more information, consult a SQL reference.
Using SQL with cfquery to dynamically retrieve information
Relational database management systems process SQL instructions sent to them from
various applications. ColdFusion sends SQL statements to database managers to
manipulate data. ColdFusion needs a way to know which database manager to send a
specific SQL string for evaluation. In CFML, the
cfquery tag serves this purpose. You
will use the SQL SELECT statement and the
cfquery tag to create a dynamic version of
LastName FirstName
Jones Tom
Adams Anita
LastName
Adams