SQL has many builtin useful functions that will help perform 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 two different categories: Aggregate Functions, and Scalar Functions. Each one having their own purpose and specific use. Let’s go over a few of them and include some examples.
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
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.
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.
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.
1
2

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

Wrapping It Up
There are so many more builtin 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.