Afrikaans
Akan
Albanian
Amharic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
French
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
In this lesson, we're going to be looking at functions.
And functions are something that are a very powerful way
to do data processing.
Specifically, we'll be looking at single row functions.
Single row functions are designed
to do a certain operation on every row
in a particular column of a table.
So let's get connected.
And we'll look at our table list.
And let's do a simple query.
Select ename from the emp table and execute.
So here, we see the ename column with employee's last names
that are stored in the database.
But let's say that they're in uppercase,
and we want to display them in a different way.
We want to do some operation.
We can use a function to do that.
So the function is going to change that data as we see it
in whatever way we specify.
So we'll use a function, for instance,
to show the lower case values of the ename column.
So notice what we're seeing here.
This is, in essence, simple data transformation.
So we have the values stored in uppercase in the database.
But we're wanting to see them in lowercase.
And that's what a function does.
So in this case, our function is called lower.
We always use parentheses.
And then we surrounded it with a column name.
So go to each value of the ename column, Smith,
and change it to lowercase, Allen,
and change it to lowercase.
So a single row function goes and operates
on each individual row.
And there are a number of different string type
functions like this.
So we're going to look at a couple of them.
There are some that we won't take
a look at because they're less common or a little more
obscure.
The idea here is to get how a function works
and what it can do for your data processing.
So let's change to a different function called a initcap
and execute.
So here, we're using mixed case.
So are initcap function projects this data
and transforms it into mixed case.
So we might like to see the data be in mixed case instead
of all capitals if we're running a report generating
labels or something like that.
So the initcap function will do that operation on each row.
That's what a single row function does.
Let's take a look at the length function.
So here, our function is length.
And we're using the ename column, execute.
And I'll add ename in here just to make this more visually
obvious.
So what length is going to do, it's going to look at a string,
and then present the number of characters
in each one of the values.
So for instance, Smith has a length of five,
Ward has a length of four, Martin a length of six,
and so on and so forth.
So the length function is usually used in conjunction
with other functions that we'll look at
to give us that information and maybe pass that information
on to another function.
Let's look at the substring string manipulation
function, ename.
And again for clarity, I'll show the before and after.
So the substring is going to take and look
at a string value, such as any of these string
values, these character values and in the ename column.
And then it's going to return a portion of that string.
So here, we're seeing the first three characters in each name.
And that's because we've used the substring function,
but we're passing it three values in this case instead
of just the column.
So we give it the column.
The next value that we pass is the starting character, so
the first character.
And the third value that we're passing into substring
is the total number of characters to be returned.
So start at the first character and return the first three
characters.
We can change this.
Start at the second character and return four characters,
for instance.
And notice that it starts at the second
and returns four characters.
In the case of Ward, it starts at the second character
and doesn't have four to present.
And so it does the three that it can.
And that's just because Ward is equal to four characters,
and we're starting at the second.
Let's take a look at the to_char function.
This is a really powerful function.
It's a little more complex.
To-char is going to be used to take a different data type
and present it as a character.
And execute.
All right, so what are we looking at here?
Well, let's start with the hire date column.
All right, so hire data is stored
in the database as a two digit day, three digit month, and two
digit year.
But what if we want more information than that?
How do we extract time information,
change the formatting of the data?
What if we wanted it to say December 17th, 1980, instead
of 17, December?
That's what the to_char function is used to do.
It's often used to manipulate dates, probably most often,
to a format that we want to display.
So what we've got here is what's called a format mask.
So what does all this mean?
Well, we start out is a format mask
is really giving us a template for how
we want to display the data.
So the DD part of the format mask means two digit day.
MON means three digit month abbreviation.
YYYY means four digit year.
Notice that differentiation.
HH24 means hours on the 24 hour clock.
MI is minutes.
SS is seconds from the emp table.
And so it's taking this data and presenting it
in a different way.
So let's change the format mask to be something
that's even more visible.
All right, so now we're not extracting
time information, but just the date information itself.
And we're showing it in a different format.
So now instead of MON in capitals,
we use mixed case Month, and that's
going to actually show the month value itself.
Let's turn to a different type of function.
And we're looking at the round function.
So at this point, we don't see a lot
of difference between these values because of the way
that we're rounding them.
We're really passing in a default value for the rounding
location, which is just to the nearest one's value.
Let's try something like this.
So notice here that we have a SAL value of 1250.
But in the rounded column, we have 1300.
So 1250 has been rounded up to 1300.
The round function is going to take a value, such as the SAL
column, and round it to the place that we specify.
So here, we're specifying to the negative two place.
So it's going to go back two from the decimal point
and round to that spot.
So a 1250 is going to round to 1300.
For instance, 2975 is going to round
to 3000, so on and so forth.
So the round function is used to round values.
Now normally what you would round are decimal values.
In that case, we would use non-negative numbers
to round to the second decimal point
to the right of the decimal, for instance.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.