SQL Primer

SQL is the language of databases, it is used for managing and maintaining databases and the data contained in the tables of databases. We use it primarily for 4 functions: creating reading, updating and deleting records in database tables. SQL can also be used to create new tables and, add, modify or delete columns in tables.

Unlike a programming language SQL has a very small number of commands that are used to interact with the database.

Command	Use
Select	The Select command retrieves information from the database. e.g. Show me all the products for manufacturer xyz (Select name from products where manufacturerid=123)
Insert	Adds records to the database.
Update	Allows us to update information in the database.
Delete	Allows us to delete information in the database.

The SQL Select Statement

The Select statement is perhaps one of the most common SQL command statements used. We use the SQL Select statement to retrieve columns of information from a database table. The tabular results of the running an SQL Select are stored in a record set or result table.
A Select statement has the following structure:
SELECT column_name1, column_name2, ... ,column_nameN FROM table_name
If we looked at the employee table in the Northwind.mdb database we would see the following (To save space I am only showing the first 8 columns):

As I stated above I left out 9 additional columns from the table above because there would be too many columns to "comfortably" fit on the page. If we wanted to only select those first 8 columns of information from the employee table we would write a query that would look like:
SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address FROM Employees Since we didn't specify any criteria we would get the columns specified for all employees from the employee table.

The SQL WHERE clause

The WHERE clause in SQL helps us limit the number of records that are returned by the SELECT statement. We use it like:

SELECT column_name1, column_name2, ... ,column_nameN FROM table_name WHERE column_name condition value
Here is the list of allowable conditions

Operator	Description
=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
AND	Joins one or more conditions
OR	Joins one or more conditions
LIKE	Specifies a search for a pattern in a column. You can use a percent sign (%) to define wildcards (missing letters in the pattern) before and after the pattern.

So for example if we only wanted to select the employees with the country of "UK" our query would look like:
SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address FROM Employees WHERE country='UK' which would give us: Note: We used a column in our WHERE clause that was not in our select statement this allows us to select only the information we want and still limit the results we get.

We can have multiple criteria or conditions in our WHERE clause to further restrict or limit the number of rows returned. These additional conditions are called sub clauses and we join them together using the operators AND and/or OR. This is where logic comes into play, since the use of the AND clause will return only those records where ALL conditions are TRUE. While the OR operator returns those records where any of the conditions are TRUE. Mixing AND and OR clauses incorrectly can have some 'undesirably' incorrect results.

An example of a query with more than one clause.
SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address FROM Employees WHERE country='UK' AND title='Sales Manager'

Ordering (sorting)

The last clause we are going to introduce in this SQL Primer is the ORDER BY clause. Obviously by the name we can surmise that it has to do with the order in which the records are returned. Perhaps we want to return our records by the the date our employees where hired, from longest to shortest time in the company, or by eldest to youngest or youngest to eldest. In order to do that we use the ORDER BY clause.
In the example above where we returned all of the employees from the UK would could have easily added an ORDER BY to make sure that the records were returned in the order which we wanted.
SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address FROM Employees WHERE country='UK' ORDER BY HireDate DESC
The above SQL statement would return in the same results but the order in which the results were returned would be based on the HireDate column. It would be ordered from the most recently hired employee to the longest employed.

Where to go from here?

We were introduced to the most commonly used SQL commands and were showed how to retrieve information from a database table using SQL. There are many tutorial sites that provide in-depth SQL tutorials and I would encourage you to find the ones that best suite your needs going forward. In programming with coldfusion it will become impossible to avoid SQL and it is quite possible that we will learn by necessity.