Hello and welcome. In this video, we'll go over SQL functions built into the database. So let's get started. While it is very much possible to first get your data from a database and then perform operations on it from your application and notebooks, most databases come with Built-in Functions. These functions can be included in SQL statements, allowing you to perform operations on data right within the database itself. Using database functions can significantly reduce the amount of data that needs to be retrieved from the database. That is, they reduce both network traffic and use of bandwidth. When working with large data sets, it may be faster to use built in functions, rather than first retrieving the data into your application and then executing functions on the retrieved data. Please note, that it's also possible to create your own functions, known as User-Defined Functions in the database; but that is a more advanced topic. In this lesson, let's consider this petsale table in a database for a pet store. The table records sale transaction details and includes the columns: ID, animal, quantity, sale price and sale date. For the purposes of this lesson, we have populated the table with several rows of data, as shown here. So what are Aggregate and Column Functions? An aggregate function takes a collection of light values, such as all of the values in a column, as input, and returns a single value or null. Examples of aggregate functions include: sum, minimum, maximum and average. Let's look at some examples based on the Petsale table. A sum function is used to add up all the values in a column. To use the function, you write down the column name within parenthesis, after the function name. Example: add up all the values in the sale price column, comes out as: select SUM (SALEPRICE) from (PETSALE). When you use an aggregate function, the column in the result set by default is given a number. It is possible to explicitly name the resulting column. Now let's say we want to call the output column in the previous query, as SUM_OF_SALEPRICE. This ends up as: select SUM(SALEPRICE) as SUM_OF_SALEPRICE from PETSALE. Please note the use of 'as' in this example. Minimum, as the name implies, is used to get the lowest value. Similarly, maximum is used to get the highest value. For example, get the maximum quantity of any animal sold in a single transaction is shown as: select MAXIMUM(QUANTITY) from PETSALE. Aggregate functions can also be applied on a subset of data instead of an entire column. For instance, get the minimum quantity of the ID column for dogs. This is shown as, select MINIMUM(ID) from PETSALE where animal equals dog. The average function is used to return the average or the mean value. For example, specify the average value of sale price, is shown as: select AVERAGE(SALEPRICE) from PETSALE. Please note that we can perform mathematical operations between columns, and then apply aggregate functions on them. For example, calculate the average sale price per dog is shown as: select AVERAGE(SALEPRICE divided by QUANTITY) from PETSALE where ANIMAL equals Dog. In this case, the sale price is for multiple units; so we first divide the sale price by the quantity of the sale. Now let's look at Scalar and String functions. Scalar functions perform operations on individual values. For example, round up or down every value in the sale price column to the nearest integer, is shown as: select ROUND (SALEPRICE) from PETSALE. There is a class of scalar functions called string functions, that can be used for operations on strings. That is char and varchar values. Example, retrieve the length of each value in the animal column, is shown as: select LENGTH (ANIMAL) from PETSALE. Uppercase and lowercase functions can be used to return uppercase or lowercase values of strings. For example: retrieve animal values in uppercase is shown as: select UPPERCASE (ANIMAL) from PETSALE. Scalar functions can be used in the where clause. For example, get lowercase values of the animal column for cat, is shown as: select star from PETSALE where LOWERCASE(ANIMAL) equals cat. This type of statement is useful for matching values in the where clause, if you're not sure whether the values are stored in upper, lower or mixed case in the table. You can also have one function operate on the output of another function. For example, get unique values for the animal column and uppercase is shown as: select DISTINCT (UPPERCASE(ANIMAL)) from PETSALE. In this video, we looked at different types of Built-in SQL functions namely: aggregate functions and scalar functions as well as string functions. Thanks for watching.
