Lesson 17

This module discussed SQL functions and explanations of how they can be used in your queries. There are many, many other functions that you can use in your reports and queries. All you need to do is look up "Functions" in the documentation for your database engine, and you will quickly see the types of things your engine can do.
No other function will get any more complicated than those we have covered in this module. That's important to understand. You will simply pass parameters to the functions and get results. You can apply what you have learned about character, numeric, and date functions to every other function that you will ever need.
Also, remember that you should name the columns returned by functions. If you assign names to the results, you will have a much easier time working with the results set.

SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:

1. AVG() - Returns the average value
2. COUNT() - Returns the number of rows
3. FIRST() - Returns the first value
4. LAST() - Returns the last value
5. MAX() - Returns the largest value
6. MIN() - Returns the smallest value
7. SUM() - Returns the sum

Operators and functions let you calculate results derived from column values, systemdetermined values, constants, and other data. You can perform:

1. Arithmetic operations.Cut everyone's salary by 10 percent.
2. String operations.Concatenate personal information into a mailing address.
3. Datetime operations.Compute the time interval between two dates.
4. System operations.Find out what time your DBMS thinks it is.

An operator is a symbol or keyword indicating an operation that acts on one or more elements. The elements, called operands, are SQL expressions.
An expression is any legal combination of symbols and tokens that evaluates to a single value (or null).
In price * 2, for example, * is the operator, and price and 2 are its operands. A function is a built-in, named routine that performs a specialized task. Most functions take parenthesized arguments, which are values you pass to the function that the function then uses to perform its task. Arguments can be column names, literals, nested functions, or more-complex expressions.
In UPPER(au_lname), for example, UPPER is the function name, and au_lname is the argument.

Aggregation based on self-joins is a challenge. You have to build a table with one column that has the unique keys of the subtree and use it to find the rows to be used in the aggregate calculations. One way to "flatten" the table is to use an auxiliary table, called Sequence, which contains the single column sequence of integers from 1 to (n), where (n) is a sufficiently large number.

SELECT MAX(CASE
WHEN seq = 1 THEN e1
WHEN seq = 2 THEN e2
WHEN seq = 3 THEN e3
WHEN seq = 4 THEN e4
ELSE NULL END)
FROM (Sequence AS S1
CROSS JOIN
<< Personnel_OrgChart query as above >>
) AS X (e1, e2, e3, e4)
WHERE seq BETWEEN 1 AND 4;

SQL scalar functions return a single value, based on the input value.
Useful scalar functions:

1. UCASE() - Converts a field to upper case
2. LCASE() - Converts a field to lower case
3. MID() - Extract characters from a text field
4. LEN() - Returns the length of a text field
5. ROUND() - Rounds a numeric field to the number of decimals specified
6. NOW() - Returns the current system date and time
7. FORMAT() - Formats how a field is to be displayed