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.
Rav AhujaData Science Program Manager
