  The following are some of the more advanced SQL commands including: Aggregate Functions, Conditional Operators, Mathematical Operators and Table Joins. Some of the criteria is specific to ColdFusion development Conditional Operators
AND Both conditions must be met At least one condition must be met Exclude the condition following Matches a pattern (see above) Matches with a list of values Matches with a range of values
Conditional operators specify conditions and perform logical functions.
Examples:

SELECT name, city, age
FROM members
WHERE age >= 20 AND name = 'Mary'

SELECT name, city, age
FROM members
WHERE name = 'Bill' OR name = 'Mary'

SELECT name, city, age
FROM members
WHERE name IN ('Mary', 'Bill', 'Frank')

SELECT name, city, age
FROM members
WHERE name NOT IN ('Susan', 'Jill')

SELECT name, city, age
FROM members
WHERE age BETWEEN 20 AND 40

Aggregate Functions
AVG Returns the average value of a given column Returns the total number of values in a given column Returns the number of rows in a table Returns the smallest value in a given column Returns the largest value in a given column Returns the sum of the numeric values in a given column
Aggregate functions are used to compute against a "returned column of numeric data" from your SELECT statement. Essentially they summarize the results of a field of selected data. The query must be combined with a GROUP BY clause.

SELECT city, AVG(age) AS average_age
FROM members
GROUP BY city

This statement will return a single result which contains the average age of all the members in each city

Mathematical Operators
  + Addition Subtraction Multiplication Division
Performs mathematical operations on the columns in the database and outputs the results.

SELECT product, quantity * price AS total
FROM shoppingcart

This statement will multiply the quantity times the price and return a column called total.