MyCMS.ca by Victory Design
SQL Advanced Queries

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
OR At least one condition must be met
NOT Exclude the condition following
LIKE Matches a pattern (see above)
IN Matches with a list of values
 BETWEEN  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
COUNT Returns the total number of values in a given column
 COUNT(*)  Returns the number of rows in a table
MIN Returns the smallest value in a given column
MAX Returns the largest value in a given column
SUM 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.