Lesson 1

When you are working with database engines with capabilities beyond just retrieving your data, it makes sense to take advantage of these features. Many of these additonal features are derived from many of the elements that have already been covered, such as clauses in the SELECT statement, joins, and views.
Another extremely useful capability is provided by an engine's ability to support functions. Functions are predefined actions that can be used on the values that you send the engine. For example, at a very simple level, you can query the database with the following, having the database complete some addition for you:

SELECT 5 + 5 

The result would be an unnamed column with a value of 10. The engine actually figures out the results for you, returning them as the answer to your "query." In this module, you will learn how to use several different kinds of functions, providing help for character-based, numeric, and date-formatted values.

The available math functions in SQL are fairly limited, which reflects the use of SQL as a tool for pulling out the raw data to answer questions. Any actual processing of the answers is really something left to high-level programming languages in middleware or front-end applications. For example, a Web server might connect to a database to pull back the name of a customer and credit card details, but you would not expect the database to process the card details. You start by looking at the four basic math functions supported by all flavors of SQL, and then you look at some more sophisticated math functions, which, although not part of the SQL standards, are commonly available on most database systems.

SQL scalar functions return a single value, based on the input value. The most useful scalar functions are the following:

1. UCASE() - converts a field to upper case;
2. LCASE() - converts a field to lower case;
3. LEN() - returns the length of a text field;
4. ROUND() - rounds a numeric field to the number of decimals specified.

In the following paragraphs we will an example of each scalar functions. In the first example we will use simultaneously UCASE() and LCASE() functions. However, there is an additional issue, because we must use a different statement for each specific database. We will starting by presenting the statement for MySQL. Select UCASE(name) as UPPERName, LCASE(name) as LOWERName from Customers;