1
00:00:00,000 --> 00:00:07,419
(Music)

2
00:00:07,419 --> 00:00:09,629
Hello and welcome. In this Video,

3
00:00:09,629 --> 00:00:13,769
we'll go over SQL functions built into the database.

4
00:00:14,769 --> 00:00:17,039
So let's get started.

5
00:00:17,039 --> 00:00:21,320
While it is very much possible to
first fetch data from a database and

6
00:00:21,320 --> 00:00:25,820
then perform operations on it
from your applications and notebooks,

7
00:00:25,820 --> 00:00:29,460
most databases come with Built-in Functions.

8
00:00:29,460 --> 00:00:35,010
These functions can be included in SQL statements,
allowing you to perform operations on

9
00:00:35,010 --> 00:00:39,440
data right within the database itself.

10
00:00:39,440 --> 00:00:42,059
Using database functions can significantly
reduce

11
00:00:42,059 --> 00:00:45,940
the amount of data that needs to be
retrieved from the database.

12
00:00:45,940 --> 00:00:50,739
That is, reduces
network traffic and use of bandwidth.

13
00:00:50,739 --> 00:00:55,370
When working with large data sets,
it may be faster to use built in functions,

14
00:00:55,370 --> 00:00:59,399
rather than first retrieving the data into
your application and then

15
00:00:59,399 --> 00:01:02,479
executing functions on the retrieved data.

16
00:01:02,479 --> 00:01:06,310
Note that it is also
possible to create your own functions,

17
00:01:06,310 --> 00:01:12,670
that is User-Defined Functions in the database;
but that is a more advanced topic.

18
00:01:12,670 --> 00:01:18,140
For the examples in this lesson, let's consider
this PETRESCUE table in a database for a pet

19
00:01:18,140 --> 00:01:20,710
rescue organization.

20
00:01:20,710 --> 00:01:25,380
It records rescue transaction details
and includes the columns: ID,

21
00:01:25,380 --> 00:01:29,330
animal, quantity, cost, and rescue date.

22
00:01:29,330 --> 00:01:32,180
For the purposes of this lesson,
we have populated it with

23
00:01:32,180 --> 00:01:36,130
several rows of data, as shown here.

24
00:01:36,130 --> 00:01:38,869
What are aggregate or column functions?

25
00:01:38,869 --> 00:01:42,340
An aggregate function takes a collection of
like values,

26
00:01:42,340 --> 00:01:49,180
such as all of the values in a column,
as input, and returns a single value or null.

27
00:01:49,180 --> 00:01:55,640
Examples of aggregate functions include: sum,
minimum, maximum, average, etc.

28
00:01:55,640 --> 00:01:59,600
Let's look at some examples based on the PETRESCUE
table.

29
00:01:59,600 --> 00:02:03,579
The sum function is used to
add up all the values in a column.

30
00:02:03,579 --> 00:02:07,229
To use the function,
you write the column name within parenthesis,

31
00:02:07,229 --> 00:02:09,729
after the function name.

32
00:02:09,729 --> 00:02:13,510
For example, to add up all the values in the
COST column,

33
00:02:13,510 --> 00:02:19,849
select SUM (COST) from PETRESCUE.

34
00:02:19,849 --> 00:02:23,569
When you use an aggregate function,
the column in the result set

35
00:02:23,569 --> 00:02:26,180
by default is given a number.

36
00:02:26,180 --> 00:02:29,790
It is possible to explicitly name the resulting
column.

37
00:02:29,790 --> 00:02:33,489
For example, let's say we want to call the
output column in

38
00:02:33,489 --> 00:02:36,870
the previous query, as SUM_OF_COST.

39
00:02:36,870 --> 00:02:42,510
select
SUM(COST) as SUM_OF_COST from PETRESCUE.

40
00:02:42,510 --> 00:02:49,540
Note the use of 'as' in this example.

41
00:02:49,540 --> 00:02:53,709
Minimum, as the name implies,
is used to get the lowest value.

42
00:02:53,709 --> 00:02:57,760
Similarly, maximum is used to get the highest
value.

43
00:02:57,760 --> 00:03:01,810
For example, to get the maximum quantity
of any animal rescue in

44
00:03:01,810 --> 00:03:12,680
a single transaction,
select MAX(QUANTITY) from PETRESCUE.

45
00:03:12,680 --> 00:03:18,150
Aggregate functions can also be applied on
a subset of data instead of an entire column.

46
00:03:18,150 --> 00:03:21,960
For example, to
get the minimum quantity of the ID column

47
00:03:21,960 --> 00:03:23,050
for dogs.

48
00:03:23,050 --> 00:03:30,030
select MIN(ID) from PETRESCUE where animal
equals dog.

49
00:03:30,030 --> 00:03:34,240
The average function is used to
return the average or the mean value.

50
00:03:34,240 --> 00:03:37,569
For example, to specify the average value
of cost,

51
00:03:37,569 --> 00:03:44,969
as: select AVG(COST) from PETRESCUE.

52
00:03:44,969 --> 00:03:49,129
Note that we can perform
mathematical operations between columns,

53
00:03:49,129 --> 00:03:52,010
and then apply aggregate functions on them.

54
00:03:52,010 --> 00:03:55,810
For example, to
calculate the average cost per dog:

55
00:03:55,810 --> 00:04:02,269
select AVG(COST divided by
QUANTITY) from PETRESCUE where ANIMAL equals

56
00:04:02,269 --> 00:04:04,549
Dog.

57
00:04:04,549 --> 00:04:08,319
In this case, the cost is for multiple units;
so we first divide

58
00:04:08,319 --> 00:04:11,800
the cost by the quantity of the rescue.

59
00:04:11,800 --> 00:04:15,490
Now let's look at the Scalar and String functions.

60
00:04:15,490 --> 00:04:19,360
Scalar functions perform operations on individual
values.

61
00:04:19,360 --> 00:04:23,630
For example, to round up or down
every value in the cost column

62
00:04:23,630 --> 00:04:33,080
to the nearest integer,
select ROUND (COST) from PETRESCUE.

63
00:04:33,080 --> 00:04:36,630
There is a class of scalar functions
called string functions,

64
00:04:36,630 --> 00:04:39,550
that can be used for operations on strings.

65
00:04:39,550 --> 00:04:41,320
That is char and varchar values.

66
00:04:41,320 --> 00:04:47,570
For example, to retrieve the length
of each value in animal column,

67
00:04:47,570 --> 00:04:55,000
select LENGTH (ANIMAL) from PETRESCUE.

68
00:04:55,000 --> 00:04:58,130
Uppercase and lowercase functions can be used
to

69
00:04:58,130 --> 00:05:02,160
return uppercase or lowercase values of strings.

70
00:05:02,160 --> 00:05:05,940
For example, to retrieve animal values in
uppercase:

71
00:05:05,940 --> 00:05:13,730
select UPPERCASE (ANIMAL) from PETRESCUE.

72
00:05:13,730 --> 00:05:16,440
Scalar functions can be used in the where
clause.

73
00:05:16,440 --> 00:05:21,110
For example, to get lowercase values
of the animal column for cat,

74
00:05:21,110 --> 00:05:27,750
select star from
PETRESCUE where LOWERCASE(ANIMAL) equals cat.

75
00:05:27,750 --> 00:05:32,000
This type of statement is useful
for matching values in the where clause,

76
00:05:32,000 --> 00:05:35,560
if you're not sure whether
the values are stored in upper,

77
00:05:35,560 --> 00:05:39,400
lower or mixed case in the table.

78
00:05:39,400 --> 00:05:44,060
You can also have one function
operate on the output of another function.

79
00:05:44,060 --> 00:05:47,460
For example, to get unique cases for the animal
column in

80
00:05:47,460 --> 00:05:56,250
uppercase: select
DISTINCT (UPPERCASE(ANIMAL)) from PETRESCUE.

81
00:05:56,250 --> 00:06:00,560
In this video, we looked at some
built-in SQL aggregate functions, such as

82
00:06:00,560 --> 00:06:05,060
sum, minimum, maximum, and average
We also looked at scalar and

83
00:06:05,060 --> 00:06:09,940
string functions, such as round,
lowercase, and uppercase.

84
00:06:09,940 --> 00:06:11,050
Thank you for watching.

85
00:06:11,050 --> 00:06:16,000
(Music)