Built-in SQL Functions

By:
To read more DBA articles, visit http://dba.fyicenter.com/article/

SQL has many Built-In SQL Functions that are useful for performing calculations on data within your database. This will help organize and optimize your workflow when working within a SQL database.. All the functions can be broken down into these different categories: Aggregate Functions, and Scalar Functions, Numeric Functions and String Functions. Each one having their own purpose and specific use. Let’s go over a few of them and include some examples. In this tutorial we will be going over some of the Aggregate Functions and Scalar Functions. SQL Aggregate Functions

With a SQL Aggregate Function, you can return a single value, calculated from values in a column. Let’s look at the list of SQL Aggregate Functions.

AVG() – Returns the average value
COUNT() – Returns the number of rows
FIRST() – Returns the first value
LAST() – Returns the last value
MAX() – Returns the largest value
MIN() – Returns the smallest value
SUM() – Returns the sum

The general syntax for an aggregate function will follow the guideline : aggregate_function_name ( [ALL | DISTINCT] expression ).

The aggregate function name can be anyone of the functions listed above. The ALL clause is the default behavior (when not specified) and evaluates all rows when aggregating the value of the function. The DISTINCT clause uses only distinct values when evaluating the function. In most cases you will not need to specify All or Distinct. Let’s look at an example of how to use this guideline.

Here is our table we will be working with which is named Products:
ProductID ProductName Price
1 Apples 8
2 Oranges 12
3 Peaches 10

By using the AVG() function, we will be able to get the mathematical average value of a column. By using the following code, we are able to get the Average of the Price column and designate it a new row called PriceAverage

PgSQL
SELECT AVG(Price) AS PriceAverage FROM Products;
1

SELECT AVG(Price) AS PriceAverage FROM Products;

Input the command and you should get an average value of 10. To break it down, the SELECT statement is used to select the data from a database, AVG(Price) to get the average value of the price column, AS PriceAverage to place our value in a new row called PriceAverage and FROM Products to designate which table to pull the data from (if we have multiple tables).

Here is another example. What if we wanted to get the SUM of a table’s column? We would want to use the SUM function on our Products table Price and get the sum of 30.

PgSQL
SELECT SUM(Price) AS TotalItemsPrice FROM Products;
1

SELECT SUM(Price) AS TotalItemsPrice FROM Products;

SQL Scalar Functions
With a SQL Scalar Function, you can return a single value, based on the input value. They are particualrly used to identify the current user session and organizing the tables. Let’s look at the list of some of the SQL Scalar Functions.

UCASE() – Converts a field to upper case
LCASE() – Converts a field to lower case
MID() – Extract characters from a text field
LEN() – Returns the length of a text field
ROUND() – Rounds a numeric field to the number of decimals specified
NOW() – Returns the current system date and time
FORMAT() – Formats how a field is to be displayed

Each one of these functions have their own syntax and are worth looking into. The first Scalar Function we will be trying out is the MID() Function. By using the MID() Function we will be able extract characters from a text field inside of our table. The typical syntax guidelines for this function is SELECT MID(column_name,start[,length]) AS some_name FROM table_name;. It is pretty straight forward as you designate the MID() Function and the parameters such as the name of the column you are targeting, the starting point where to count, how many characters to count from the start and give the value a new row as well as the table you are choosing from.

Here is our table we will be working with which is named Products:
ProductID ProductName Price
1 Apples 8
2 Oranges 12
3 Peaches 10

If we wanted to select the first three characters of each Product from the ProductName column, our code would look something like this. Input the command and you should get the results with a new row called “ProductAbbr” with App, Ora, and Pea in each row.

PgSQL
SELECT MID(ProductName,1,3) AS ProductAbbr FROM Products;
1
2

SELECT MID(ProductName,1,3) AS ProductAbbr
FROM Products;

Here is another example. What if we wanted to add a row of the time when these products prices were updated? We can use the NOW() function to solve this problem. Here is the code that will create a column called PerDate and display the current date and time.

PgSQL
SELECT ProductName, Price, Now() AS PerDate FROM Products;
1
2

SELECT ProductName, Price, Now() AS PerDate
FROM Products;

Wrapping It Up

There are so many more built-in SQL functions that you can utilize in your database management. We have yet to go over Numeric Functions and String Functions which you can look forward to in future lessons. Try practicing setting up your own database and test some of these functions out.