00:00:00
salamualikum' in hi everyone we are now
00:00:03
in lesson 3 introduction to functions
00:00:06
but one at the end of this lesson
00:00:09
students should be able to describe
00:00:12
various types of functions available in
00:00:14
SQL the singer of functions such as
00:00:17
character functions in SQL there are
00:00:21
many types of functions they are used to
00:00:23
transform input in one form to output in
00:00:26
another form these functions are used to
00:00:30
manipulate data values functions are
00:00:32
small programs that perform an action on
00:00:35
a value or column and produce something
00:00:38
different as output as an example when
00:00:42
you put money in a dream machine
00:00:44
something happens between the time the
00:00:47
money is deposited and your favorite
00:00:49
thing is dispensed the transaction is
00:00:51
processed internally by the machine your
00:00:53
money is the import and the drink is the
00:00:55
output the machine performs as a
00:00:58
function functions are a very powerful
00:01:02
feature of SQL they can be used to do
00:01:05
the following to perform calculations on
00:01:07
data modify individual data items many
00:01:11
plate output for groups of rows format
00:01:14
dates and numbers for display and
00:01:16
convert column data types
00:01:19
SQL functions sometimes the arguments
00:01:23
and always return a value we have two
00:01:26
different types of functions here the
00:01:28
first one is single row functions and
00:01:30
the second one is multiple row functions
00:01:33
single functions are serve one or more
00:01:36
arguments and will return a single
00:01:38
result per row so if you apply the
00:01:41
single row function to 12 rows you will
00:01:43
get 12 results out of the single
00:01:46
function in summary single functions to
00:01:49
the following manipulate data items
00:01:51
accept arguments and return one value on
00:01:55
each row return written one result purl
00:01:59
row can modify the data type and it can
00:02:02
be nested well for multiple row or group
00:02:06
functions it takes many rows as an input
00:02:09
and return a single value as output the
00:02:12
rows input may be
00:02:13
a whole table or the table Street into
00:02:16
smaller groups the example of multiple
00:02:19
row or group functions include maximum
00:02:21
to find the highest value minimum to
00:02:25
find the lowest value average to find
00:02:28
the average value in a group of rows and
00:02:30
we have a lot of other more we have few
00:02:34
types of singular functions the first
00:02:36
one is correct the function followed by
00:02:39
number function did function conversion
00:02:42
function and general function let's take
00:02:46
a look at it one by one being able to
00:02:49
change the way in which data is
00:02:51
presented is important when dealing with
00:02:54
data from a database most of the time in
00:02:57
SQL we need to change the way data
00:03:00
appears depending on the requirements of
00:03:02
the task we are trying to accomplish in
00:03:04
this lesson you will learn several ways
00:03:07
in which to transform data to fit a
00:03:09
particular situation and to test for the
00:03:13
function we need to use dummy table this
00:03:17
dummy table is represented by dual the
00:03:19
dual table here is used to create select
00:03:22
statement and as it functions not
00:03:24
entirely related to a specific database
00:03:26
table in character functions we have two
00:03:31
different sub functions the first one is
00:03:33
case conversion function which we have
00:03:36
lower output and in cap and the second
00:03:38
one is character manipulation functions
00:03:40
which we have concat substring lang
00:03:43
ensuring left / right bad dream and
00:03:45
replace we will take a look at case
00:03:48
conversion function first in this
00:03:52
example - will will be used to learn
00:03:54
many of the single functions for example
00:03:57
we have a collision here 3 1 9 divided
00:04:00
by 29 + 12 so of course it is hard for
00:04:03
us to calculate this isn't it so we can
00:04:05
use tool table as a dummy table since
00:04:07
this calculation is not coming from any
00:04:10
table so let's try to run this one so
00:04:13
the answer 41 9 divided by 29 + 12 is 23
00:04:18
so actually we will use a lot of dual
00:04:21
table if we just want to try to test for
00:04:24
our functions in our employees table the
00:04:29
format of last name the first letter
00:04:32
here must be capital but for this paper
00:04:36
purse we can change the case to be the
00:04:38
format that we want for example
00:04:40
lowercase let's decorate this example
00:04:42
for example select last name select last
00:04:48
name lower last name from employees so
00:04:56
if we take a look if he has a cute this
00:04:58
SQL the last name is actually in the
00:05:01
first letter must be capital but after
00:05:04
we change into lower last name then the
00:05:06
last name become small letter
00:05:08
everything here is lowercase but the
00:05:10
inside that abyss the data is still in
00:05:13
the same format this is just a display
00:05:14
to change the format into something that
00:05:17
we want to see only KB says lower we can
00:05:20
use a per app per means a firm is all of
00:05:25
the last name now has become uppercase
00:05:29
this one is very useful for the search
00:05:32
sometimes we don't know what is the
00:05:35
format of that characters in our
00:05:36
database so we can search the data that
00:05:40
we have converted to lower or upper case
00:05:42
for example now I don't know I want to
00:05:45
search for a bell but I don't know what
00:05:48
is the format of a bell so I just
00:05:50
decided to search for all of the all of
00:05:53
the uppercase here so what we can do
00:05:54
here we add where Apple Apple of last
00:06:02
name equals to what we want to search
00:06:05
for or to search for all uppercase of a
00:06:10
pair so if we ask you this one we will
00:06:12
get the answer as I said the rate I
00:06:15
select a base is still in there in the
00:06:17
same format the first letter must be
00:06:19
capital but we displayed upper here if I
00:06:21
want to display lower east any problem
00:06:25
now this query is valid because why we
00:06:28
search for uppercase but we want to
00:06:30
display the lowercase there's no problem
00:06:33
it still can be executed okay but what
00:06:36
if I change this one to become these
00:06:38
Arbel can this be executed no it can be
00:06:43
scripted however you will not get the
00:06:45
result why because we output a last name
00:06:47
when you upload a last name it will
00:06:49
become this format a bell but what do
00:06:51
you search for you search for this a
00:06:53
bell remember this a bell and this Oh
00:06:57
Bell is not the same because characters
00:06:59
are cases active you search for
00:07:01
uppercase but your your such such string
00:07:06
here is the lowercase so of course the
00:07:07
orc receiver cannot get what you are
00:07:10
trying to find the third case combustion
00:07:15
function is in it cap in it cap means
00:07:18
initial capital for example here select
00:07:20
last name chopped ID from employees if
00:07:22
we execute this one if we take a look
00:07:25
the job ID everything is in upper case
00:07:28
but what if I want to change to in it
00:07:32
cap chopped ID
00:07:34
[Music]
00:07:36
okay so if we try to execute this one
00:07:38
you will see this is the format at the
00:07:41
base but we changed for the display
00:07:43
purposes to become is a ref here only
00:07:46
the first letter of each word is being
00:07:48
capital that's in it cap initial capital
00:07:53
now let's take a look at character
00:07:56
manipulation functions the first one is
00:07:58
con cats con cats joins two values
00:08:01
together it takes two character string
00:08:04
arguments and joins the second string to
00:08:07
the first it could also be written using
00:08:09
the concatenation of double vertical bar
00:08:14
let's take a look at the example of
00:08:16
concat in our lesson 1 if I want to
00:08:21
color to combine hello n well I will do
00:08:27
this kind of double vertical bar here
00:08:30
select hello world from dual y from dual
00:08:35
because hello and what is not coming
00:08:37
from any table in our HR schema so if we
00:08:39
ask you this one we will get this hello
00:08:42
well actually this concatenation we will
00:08:45
have a function okay we will have a
00:08:47
function of con cats they will have two
00:08:51
arguments the first argument is our
00:08:54
hello and the second argument is work so
00:08:58
if we execute this one we still have the
00:09:01
same result hello world in our first
00:09:05
lesson if you want to combine the first
00:09:07
name and last name of the employees this
00:09:09
is how we do it select first name last
00:09:13
name you look up at the nation of first
00:09:15
name and last name rising above at Aqaba
00:09:17
and this is the reason that we will get
00:09:18
but we also can use the same method by
00:09:21
using concat function member Khan cats
00:09:25
will have two different arguments the
00:09:27
first argument of the first the first
00:09:29
string the second one is the second
00:09:31
string so concat first name last name we
00:09:35
still have the same result but what if I
00:09:38
want to have a learn
00:09:41
space a bell something like this okay
00:09:45
Ellen a bell we have a space in between
00:09:47
so the first part here this is the first
00:09:50
name the second one is the second one is
00:09:53
space the third one is a bell so means
00:09:56
since we only have two different
00:09:57
arguments here so we can add here
00:10:00
another one concat number one number two
00:10:03
so number one here is a space and the
00:10:07
second one is the their last name so
00:10:11
this is a sample echo nested function
00:10:13
okay first name space last name this
00:10:17
like l-learn spaced a bell so if you ask
00:10:19
you this one all of the last name now
00:10:21
has or the full name now has space in
00:10:24
between the first name and last name
00:10:27
next function is substring substring
00:10:30
means you want to extract certain length
00:10:33
of characters from the character string
00:10:35
for example here normally substring will
00:10:39
have three different arguments the first
00:10:40
one is character string the second one
00:10:42
is starting position and third one is
00:10:44
the length so in this example our
00:10:46
character string is hello world we want
00:10:49
to start the push position at 1 and we
00:10:53
will take how many 5 so in Oracle the
00:10:56
index start with 1 so right now if we
00:10:59
take a look at this example hello world
00:11:01
1 5 is a cute what we will get we will
00:11:05
get hello why hello we start at 1 how
00:11:08
many do we need to take 5 1 2 3 4 5 so
00:11:14
that's why we get to resolve hello in
00:11:18
substring also the lang argument here is
00:11:21
optional in if omitted it returns all
00:11:25
characters to the end of the string for
00:11:27
example I want to take hello world 6
00:11:31
what does it mean here it was start at
00:11:33
position 6 and it will take up until the
00:11:37
and if it could be 6 the 6 1 H 1 2 3 4 5
00:11:41
6 so we will start hello and there's no
00:11:45
line being specified here means it will
00:11:47
take up until the end of the string so
00:11:49
the research should be world
00:11:53
get it in previous example what if I
00:11:58
said I want you to find all employees
00:12:01
that starts their last name with a
00:12:03
capital A here so what we can do select
00:12:07
last name from employees where last name
00:12:08
like capital a after it can be any
00:12:12
characters so we use percentage so if we
00:12:15
as a cute this one we will get all of
00:12:18
the employees who started with capital
00:12:20
at the air will be listed here but
00:12:22
actually this query also can be done by
00:12:25
using substring for example here where
00:12:28
last name or we can change where
00:12:31
substring substring of last name what is
00:12:37
the position of a start with one how
00:12:40
many do you want to take one so it must
00:12:43
be close to e so if you take your last
00:12:46
name one one what does it mean here
00:12:49
let's say this is our last name it
00:12:51
started with one so this one you start
00:12:52
with in here how many do you take only
00:12:54
one so the first one must be equal to e
00:12:58
so if ask you this one we still have the
00:13:00
same result
00:13:01
same goes what if I said the last
00:13:05
character of last name must be n so what
00:13:08
we can do negative 1 1 equals 2 and what
00:13:14
does he by negative here we start the
00:13:17
position from the back ok we start a %
00:13:20
position from the back how many do we do
00:13:22
in do we want to take only 1 then it
00:13:25
must be equals to and so if we execute
00:13:27
this one all of the employees that ends
00:13:30
with n will be listed take a look their
00:13:34
last name and Swift and so that's why
00:13:37
they are projected in the table we saw
00:13:41
what if I said the first two characters
00:13:45
must be CH the first two character must
00:13:48
be CH so means we start with one we want
00:13:52
to take two and it must be close to C
00:13:55
Heche so if he has a cutest one so we
00:13:59
have two resources Chen and Chung the
00:14:02
first two character is C
00:14:05
okay what if I said the last three
00:14:07
characters the last three characters
00:14:09
must be ma n the last three so means we
00:14:13
start at negative three
00:14:14
how many do you take three and it must
00:14:18
be quest o ma n so if we execute this
00:14:20
one and this is what we get the glue the
00:14:23
last character is ma and get the last
00:14:28
characters of the string must be ma and
00:14:30
and accessories that we will get power
00:14:36
let's take a look at length length shows
00:14:39
the length of a string as number value
00:14:41
the function takes a character string as
00:14:44
an argument and returns the number of
00:14:46
characters in that character string and
00:14:50
for ensuring it finds the numeric
00:14:53
position of the specified characters in
00:14:56
string search for the first occurrence
00:14:59
of a substring within occur at the
00:15:01
string and returns the position as a
00:15:04
number if the substring is not found the
00:15:07
number 0 is returned let's take a look
00:15:12
at Lang select lang
00:15:14
hello world from the world so we want to
00:15:17
know what is the size of these
00:15:19
characters so if we as a queue this one
00:15:22
will get 10 1 2 3 4 5 6 7 8 9 10 11 the
00:15:28
reason is 10 so what if I say find all
00:15:31
employees that their last name has 8
00:15:34
characters so what we can do is we
00:15:37
select last name length off last name
00:15:45
from this one coming from employees not
00:15:48
the world because lesson is coming from
00:15:49
employees where the length of this last
00:15:52
name
00:15:53
well length of this last name must be
00:15:56
equals to it okay so if we ask you this
00:15:59
one we have all employees that has eight
00:16:02
characters India last name let's take a
00:16:08
look at in string instrument find the
00:16:10
numeric position of the specified
00:16:12
characters for example we select in
00:16:14
string hello world W the reason is six
00:16:18
why take a look W is a position number
00:16:21
one two three four five six it will take
00:16:26
the numeric position of the specified
00:16:28
characters so this method also is very
00:16:31
useful if you want to find all employees
00:16:33
that their third character must be a so
00:16:37
what you want to find all employees that
00:16:39
the third character must be a sailor
00:16:42
last name from employees where in string
00:16:47
in string of last name E
00:16:50
[Music]
00:16:53
must be at that position so if you ask
00:16:57
you this one we will get all employees
00:17:00
that the third letter is a I guess
00:17:06
that's all for now see you again in the
00:17:08
next part
