 |
|
| Website Toolkit |
|
|
|
|
 |
 |
SQL Advanced Queries
The following are some of the more adavnced 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. |
|
|
