Hi. In this lesson, we talk about functions. Oracle provides a set of predefined functions. First of all, a function is a set of statements. It is meant to perform a single operation. Some functions take parameters, some don't, but they always return a single value depending on how many roles a function is working on, how many values it can take, the functions are divided into single row functions, multi row functions, we understand single row functions for now, later we talk about multi row functions.

These functions are specific to Oracle. So if you are using some other DBMS like my SQL, you have different set of functions. So this is is an area where things change from database to database, I mean from one product to another product. So let's talk about single row functions first. single row functions are divided into five categories based on what type of data they operate on. If the function deals with the numbers, it's called a number function or a numeric function, if it works with the date date function, if it works with strings, also known as characters, character function, if it is use it to convert one type of data to another, we call it as conversion function.

And finally, if it is working with the different data types, we call it as miscellaneous function or general function. Let's first focus on numeric functions or number functions. These are Some of the functions that are more widely used in the context of number functions, abs is going to return the absolute value that is the positive value for the given value. seal is going to give you the smallest integer larger than or equal to the given value. So if I gave something like 10.1 C is going to give you 11. It always gives the value that is higher than or equal to the given value.

Simply remember it as anything more than 10 will become level. The opposite to that is a floor. If you give 10.9 floor we'll make it 10. So the largest intakes are smaller than or equal to the value. We have to use them depending on the requirement. So we'll see some examples later, mod returns the remainder of the division.

So if you say, mod 10 comma three, you get one because that's the remainder of the division between 10 and three. Power raises the given base two given power. So if you give two comma, eight, you get 256. That's to raise it to eight, round and trunk are two round are truncate the given value to the given precision. We'll see some examples. I explained more about them.

When we look at examples. Square root is just returning square root of the number sign returns one, or minus one or zero. It depends on the given value. All positive values will return one, negative values minus one and zero returns zero. Now, these are some examples. To start with ABS f minus 10 is going to give you 10 because that's the absolute value for the given value.

C are for 12.1 is going to be 13. It'll always take you to the next number when it is more than 12 12.1 or 12.9. Anything about fall is going to be 13. But then 12.6 in case of floor is going to be just 12. Depending on your requirement, you have to consider either that or this. There are cases where anything more than two should be considered as three, like the number of days, okay, you stay in a hotel, anything more than two days should be counted as three days and anything more than 10 up leaven should be considered as 10.

That's the case if you're calculating age of a person. So anything like 10.9 or 10.5 should be considered just as 10. Now around is going to round the number to two decimal places. Well first what is this duel? To understand what is this tool? Let us just go and have a look at some commands in SQL Developer.

Now, I want to know what is the result of ABS of minus 10. But how do I do that? Because if I just use select ABS of minus 10 and try to run that, it is saying there is no from keyboard because you didn't give from after select and I need to give from but then what table do I give? If I give a table like a jobs where we have n number of rows It does show the result, but the result is shown for every row of this table. If the table has 20 rows, the result is displayed for 20 times, that's where your jewel table comes to your rescue, it becomes very handy because it has only one row. And it's already there, we don't have to create it.

The old table is already present, so we just need to straightaway use this. And as it contains only one row, the result is always displayed Just for once, so it's the best way to test your commands. For example, I want to round a number like 123 dots, okay, 1234 dot 567 comma two. I want to know what will be the result if this is the input. And if this is to be rounded to two decimal places, go ahead and that's what you get. We want to round the number to two decimal places that is five, six, but what we are losing is greater than or equal to five.

So it's going to add one to this number, so it becomes five, seven, and that's what you see there. So if you are rounding to two decimal places, and if the digit that you are truncating is greater than or equal to five, one will be added to the last digit. So it's going to be five, seven. But if I say it's going to be 562, you're not going to see any addition to that. It's going to be five, six. This is about round.

But if you say 568, and if you say you are going with the trunk, which is just truncating the number without bothering about rounding it, so it will simply ignore Anything beyond two decimal places, it's not going to consider whatever is present after that. That's how trunk works. So coming back, this is where you see you have round and the trunk. But another interesting possibility with round, you can round a number to negative precision. This is where we move to the left of the decimal point. If you give zero it means you're rounding the number to zero decimal places.

So it's going to be 1356 in case if you are giving zero, but rounding it to negative precision, so you're moving to the left of the decimal point and you are going to round these two digits. So it goes like this. You just need to set these to two zeros. And then if this is good greater than or equal to five, we will be adding one to that. So this becomes four, because this is greater than or equal to five. So rounding 1355 dot something to minus two places is going to be 1400.

And it's simply put, you're rounding the number two hundreds. Now let's go and try this. And I'm going to use round again. Of course, even a trunk, the same principle applies. If I say minus two, then what's going to be the result? While you can pause the video here, and then try to answer this and then proceed and see what is the result.

The result is going to be 1200. As we discussed, it's going to set these two to zeros, but nothing is added to because it's just that he for any Think greater than or equal identify would mean one will be added to the previous visit. So that's the way your trunk is working, round is working both support negative precision. So, these are some of the examples and you can use them with your data like employee salary. For example, I want to round employee salary to thousands, then here is the way to do it. I say first name, I say first name comma, round of salary comma minus three, and this is the alias for that.

And I need to change this to employees because salary is coming from employees go ahead and run and you can see the values are rounded. But to be sure that they are really rounded, you need to display the actual salary as well. Then you get to see whether it is really rounded. In some of these cases, you can see it is 4200. Now it became 4000. And like this, it's possible that you round the values coming from a column.

Of course, if you want, you can even do something like this, I want to give something like some 25% of commission on the salary and that can be rounded to hundreds. So I can put it like this, this is not salary, this is commission, go ahead and try and you're going to see how it is going to work. And in such cases, you can see this is the commission where we are talking about rounded values. So that's the way it goes. And those are some of the examples. Next, let's talk about the dates, dates, play.

Very important role because database contains lot of dates, numbers, dates, strings, three very important data types are racquel is going to store a date plus time in data type called date. So though you're saying it's only a date, don't forget, it always contains time. So it stores century, year, month, day, hours, minutes and seconds. And the date format is DD mo n RR. Well, you might not see this format in SQL Developer, because SQL Developer uses my system format. But if you go to SQL Plus, you get system date like this, for example, today's 31st August 2019.

So it's going to put it like this. If you're talking about SQL plus But in SQL Developer, it's coming in the format in which I configured my system. And these are the valid ranges. So it goes from first January for 712 bc to 31st December four nines ad. That is the range of dates supported by Oracle. So, what are the other details, we have what is known as date at the metric, which is allowing you to add a number to date, subtract a number from date, and by the way, this date represents system date.

I want to know what will be the date by adding 10 days to system date. That's exactly what you can see from this expression. Similarly, if you want to go back by 10 days, this is the way you do it. The third possibility is subtracting a date from another date. So you can subtract a date like hire a date of employ from this date. And then you can also add not just the days but also a fraction which indicates hours.

So I'm trying to add point five. That means I'm talking about the 12 hours otherwise called as off of a day. Because 24 is 112 is going to be point five. Of course you can add anything you like. You can say you want to add just six hours to system date. Don't forget it is date place time.

So this represents a right now what is my system date and what is my system time to that I want to add six hours, that could mean I go into next day also in case if the current time is something like 20 hours or even 19 hours because it will take it to beyond 24 hours. So these are all called date at the Matic. So you can add a number, subtract a number, subtracted date from another date and add a fraction which indicates a part of the day not the whole day. This is interesting and these are the functions. So you can use functions like add months because you can add days but not months. To add months you need to use this function.

Similarly, you can find out the number of days between two dates using date or dometic. But if you want to know the number of months you have to go with months between last day returns the last day in the month In which the date is falling. So if the date is today for example, it's already 31st of August, the last day of the month, but if it is second of September, then it will become 30th of September. Next day is going to give you the date for the coming day since the date while it means if I'm giving today which is 31st August, which is Saturday, and I want to know when is the next Friday, and it'll give me the day for coming Friday from this date. As today is Saturday, it will find out what is the date for coming Friday, and it'll give me the date for that.

And we also can round a date. Rounding a date by default is rounding a two day by rounding the time portion by ignoring the time portion, and rounding the date based on the time and trunk also does the same but does not consider the time part of it, it will just directly give you the date. Now, they play a very important role, and especially trunk it can be used very widely in the context of comparing the dates because when you compare the dates, it's not just the date, you're also comparing the time but to ignore the time and compare only by dates we need to use trunk. So those are the functions and here are some examples. Well to start with the SIS date is going to give you a system date. Actually it contains both the date and time but you will see only the date because the default format is only displaying the date not the time.

But later in the course, you will see how to extract the time from the date data type add months is adding three months and also you can go back by two months and months between a saying find out the number of months between sets date and the first to January 2017. And then this is going to give you how many months are there plus a little fraction and the fraction indicates how much of the next month is already over. That means the the gap in terms of months and also the percentage of next month we will see some examples and trunk is going to truncate whatever that is coming from this if it is something like let us say some 24 dot 158 Going to remove that it'll give you only the exact number of months ignoring that decimal portion. Last day will give you the last day in which this month is falling since date is falling in the month of August, so you get the last day of August.

Similarly says date, and then son, this is about getting the coming Sunday's date. And then around is going to round this date using month. So it is rounding it to month. And we'll see some examples. Let's get to our SQL Developer. And let's try so I say says date First of all, from June.

Let's see what it is giving and as they told you, it's 31st August 2019. So that's what you get and you add 10 to it and you will be going to 10th September 2019, but if you want to add months, he can do it in this way. So I would like to add for example, three months today's and I would like to know what's going to be the date after three months. It says it's going to be 30th of November but you see something interesting then today's 31st August, but we don't have 31st November. So it's automatically adjusting it to the last day of November which happens to be the 30th of November. And then you can also say months between and you need to find out how many months are there between says date, I can give something like this and I'm talking about first to January 2019.

And it is going to find out the number of months between these two. Well, they is a string, but Oracle can convert this to a date provided you give it in the format or racquel understands. So it's saying seven months and 98% is of the next month. So we are on the last day of eight month. So it's saying you are done with the 98% but not hundred percent. So exactly only seven months are completed, because I'm on 31st of August, a few hours are still left.

So it is saying you are having 98% of eighth month completed but not completed as a whole. So seven point 98 should be read as seven months and 98% of next month. Well if you're not interested, you can get rid of it. But if you want to consider anything more than seven to Be aid, maybe you're talking about rent for your house, anything more than seven months should be considered as aid, then comes your C. And this is how we can use C. So the 7.98 will be given to C, and it's going to make it eight, but it'll make it eight even if it is 7.1. That's the difference between seal and round. round we'll make it eight only if it is 7.5 or more, but seal will make it always eight.

If it crosses seven, seven point anything, except zero is going to be eight. That's the way seal works. So those are the functions, then this is pretty important one. I'm trying to find out employees who joined today. But the problem with this if I give it normally, let's say I do Try this. Actually there is no one who joined today but if you even try to get something like this, select something like EAS, okay from a duel where CES date, okay is equal to today Today is 31st August 2019.

So you might think it is going to succeed and you get the result s but I'm not getting anything but don't be deceived. This is only the heading okay? I'm not getting any result because CES date is certainly 31st August. But the problem here is, it's not just the date we have date plus time. The time right now is something like 11 o'clock in the morning, but here we are giving only the date the time is not mentioned. So Oracle will take the time as zero hours.

Zero minutes, zero seconds. That means the dates are same, but the time portions are not matching. That is a very important point to remember, the dates are same, but the time portions don't match, because here it is 11 o'clock. But there in the absence of any explicit time, it will consider that as beginning of the day. That means zero hours, zero minutes, zero seconds. So, this is the problem, but how do I solve it?

Well, simple trunk, get rid of the time, just take only the date. This is going to set the time portion to all zeros. When time portion is set to all zeros, you get the result. And there you are. So what's happening, the date is going to be still 31st August 2019. But even because of trunk that time is set to all zeros.

And now this is same as what we are giving there. So 31st August 2019 and 31st August 2019, all zeros there and all zeros here. So these two are now saying, so remember this, if you want to compare only the dates without considering the time, you should use a trunk, sometimes you need to give trunk even on the right side expression. If that is the case, then go ahead and do that. So, when you go ahead and try this, this is what you get. Well, that's okay.

We'll discuss about this issue later. Well simply put, Oracle is thinking this is a number, not a date. So that's the reason it is saying I'm sorry, this is not date and see the message, expected a date but got a number. It's thinking, this is a number, not a date, we will fix this problem in another lesson where we talk about how to convert one type of value to another type. So those are the examples. And the last one is new time, which is trying to give you the time in a different time zone.

So from ESD, in which the CES date is, I want to convert that to GMT. But to appreciate what it exactly does, you should be able to extract the time portion from the date. And that is, again, something we'll discuss in future lesson. For now. It does the job but you just can't see what is the real effect of this job. So that's about data Functions number functions dual table.

So we understood a function is a set of instructions meant to perform a single task. We have two types of functions single row multi row, single row functions, take one row and perform the operation. Multi row functions take a bunch of rows, we haven't seen them, we'll see them later. Dual table is made available to every user. It contains one column one row, so we can use it in our queries where the expressions result is to be displayed Just for once. We use that for our testing purpose.

Number functions perform operations on numbers like rounding, getting the square root so on date data type contains both not just the date but also the time this is crucial date Always containing date plus time, all calculations, all comparisons involve both date and time. So you have to be aware of that date at the medic allows you to add a number, subtract a number from a date. And also you can subtract a date from another date. And that will equal the number of days between those two. But even there, you get fraction that indicates the amount of next day that is completed. So 3.5 is like three days and 12 hours because point five minutes off of the next day, which is equivalent to 12 hours and date functions like add months and months between last day next day.

They're all useful. We can use them in good number of cases. And before we wrap it Let us see how we can use next day. For example, I want to know when the employ got is first weekend, we assume we can start with the Saturday. So we say the first name of the employ the hire date, and I say next day for hire date comma Saturday. So this is like the first weekend for the employer.

Let us see what we get from that. Go ahead run. And this is what you get. The employee joined on 17th of June. His first weekend was 21st. June, another employee joined on 21st.

September, he got the first weekend on 44th and so on. And what if you want to then know when he got his first salary? Well, his first salary would be given on first of every month, so I can do it in this way, I want to find out the last day of hire date. plus one is going to be the salary day. This is the first salary day you can give more descriptive name like this first salary day. Well, last day of hire date.

So if I joined in the month of August, the last day of that is going to be 31st August and you are adding one that makes it to first to September, that's going to be the day on which the employ would get his first salary. So go ahead run. You can see here the employee joined in the month of June he gets salary on first July and so on this employee joined in the month of February a good salary in the month of March that too on first, but what if you say no we pay salaries not on first but On fifth issue, just add five, you get the same stuff. So those are some of the applications and you can try a lot more. Depending on your requirement. You have to choose which functions to use.

And you can always nest them, you can use them in combination. So that's about numeric functions, date functions, date, a dometic, all that stuff. So just go back to your system practice, and make sure you understand how those functions work. And we'll resume in the next lesson.