1z0-071 Oracle Database SQL – FUNCTIONS

1. FUNCTIONS – INTRODUCTION

Look at this following sequels select Upper. This is a test from Dual has converted the string to an all uppercase string. Select Sqrt 36 from Dual has given us the square root of 36, which is six. So what is this upper and square root? These are functions provided by Oracle. Functions are nothing but programs supplied by Oracle that does a certain operation on the input and gives an output. Now, do I need to know the programming code behind upper R square root not needed. All I need to know is that there is a function like that and I can use it if I need. Oracle provides lots of such functions.

Functions have been classified into two single row functions and group functions. Single row functions operate on one row per execution and provide a result for each of those rows. For example, let’s assume I do select Upper City from test one. This upper function will operate on each row and get the result for each of them, so the output will be like this. Or I can do like this too. Select Upper City round budget from test one. Here the upper function operates on each row and gets the result for each of them as well as the round function operates on each row of budget column and gets the result for each of them.

So the output will be like this group functions operate on multiple rows per execution. For example, if I do select some population from test one, it will add all the rows of that column and bring the following output here. You may also notice that the sum function did one execution covering all the rows. It is possible to have group functions to split the overall result into subgroups and do calculations on each subgroups. For example, we can find the total budget of each team using this SQL with a group by class. We will cover this later.

2. NUMERICAL SINGLE ROW FUNCTIONS

Let’s take a look at some of the numeric based single row functions. Function ABS gives us the absolute value of the numerical that we pass. If it’s a negative number, then it still gives just the value. And if it’s a positive number, again it just gives the value function. Round helps us in rounding the numbers. If we pass just integer without any decimal, it doesn’t do anything. If we pass a decimal number then it rounds to the nondisimal value which is 226. Here we can also control where the rounding should occur by using one more parameter after comma. So here two means round at the second digit from the right of the decimal point. So one, two, three is the second digit. So since four is lesser than five, this stays as three.

So 225 dot 63 and a negative number means go to the opposite direction from the decimal point. So from the decimal point go to the first digit which is five. OK? Since five is equal to five, so this becomes three. So 230 seal is a function which always goes to the higher value. It’s kind of like round but it always go to the higher value. Whether it’s zero one or zero nine both get bumped to 226. Floor is the opposite of seal. It will always go to the lower value which is whether it’s point one or . 9, it will go back to 225. For example, if I want to find four square then I can use this power function. So power function the number and the power which we need. Okay, so this will be four square is 16 and phi twelve square is this number. Square root function gives the square root of the number.Square root of 16 is four.

Square root of this number is 512. We can use the modulus function to find a reminder. Modulus function can also be used to find even number or odd number anything with modulus of two. If there is a reminder then it’s an odd number. So here the remainder would be two right after dividing it by ten. So two comes here. Here the remainder would be nine. So nine comes here. We can use this reminder function which is kind of equivalent to modulus but it works in a different way. Okay, so here the remainder is two. So it shows it as two. And here the remainder is five. It shows it as five. Anything above five which is above the half of ten will be counted from backwards.

For example, a remainder of six will be minus four of ten. A remainder of nine will be minus one of ten. Sine function gives the sine of the number. If it’s negative, it gives minus one. If it’s zero, it just says zero. If it’s a positive number, it gives plus one. Trunk function just truncates to the precision that we want at two means truncate here. So this digit is gone. Okay, and minus two means here. Okay? So this becomes zero. This also becomes zero. It becomes 200. Of course, these all can be used on a numerical based column. For example, I’m selecting salary and then I am applying round function on salary. Column gives me this output. I’m rounding using the third digit to the left of the decimal. If you notice here, 4800 becomes 5000.

3. CHARACTER BASED SINGLE ROW FUNCTIONS

Character based single row functions. Every character has a numerical value within the databases character set. That numerical value can be converted into a character using C HR function. The function concat is equivalent to the double full pipe operator that we saw earlier. Select star from employees here. The last name column was concatenated with the was higher down phrase using the inner Concate function. Then it is again concatenated with higher date. With the outer concatenation function. It basically takes two strings whether they are a regular string or from columns of a table and concatenates them here.

We can convert the case of the strings using these functions regular first name upper first name converts everything into upper case. The regular last name lawyer last name converts everything into lowercase. The regular email init cap converts just the first character into capital. The function LPAD can convert a string to a fixed length by adding a pattern of characters in the left. For example, this sequel can expand the values of first name column to a total of 20 characters as specified here by adding hash characters in the left. Rpad does a similar thing but adds the hash characters in the right. Replace is for search and replace strings.

I can replace the character five with s using this SQL l trim scans the string here from left and looks for values specified here. Currently it is C. If needed, I can have multiple values here, but for simplicity I am showing with only one value. If it finds the same value here in the left, then it will start removing them until it hit a value which is not here. So all the CS are removed until d came. R trim does the same thing, but from the right side. All the e’s are removed until decay. substring returns a subset of the string. In this example, this is the string three here means to start from the third letter which is C. Seven here tells from go for seven more letters the answer is cde of G-H-I where I e is the 7th letter from.

4. CHARACTER FUNCTIONS WITH NUMERICAL RESULTS

As key function bring the numerical values for the characters within the character set. A is represented internally as 65 and B as 66. In string function brings the position of the occurrence of the search string which is CD here from the main string which is this one. CD starts at the third position. Reg underscore count is an improvement on in string. It lets us know how many times the search string appears in the main string. CD appears twice here and so the result two length lets us know the length of the string in terms of number of characters length b does the same but displays the results in terms of bytes. We can use length functions to get the length of the values of a column.

5. DATETIME FUNCTIONS INTRODUCTION

Now, let me give you a brief introduction to how date and time work in Oracle database. Let me run this sequel and explain. What I’m doing. Here is I’m selecting Systate and I’m selecting current date and session time zone from Dual. And I’m using a function called twoCAR to convert this state into this format which is date, month, year hovers in 24 hours format minutes seconds. I’m doing the same for Current Date column two. Okay, so it brings me sys date. Currently it’s 1st May 2016 1957. However, in 24 hours format and Current Date, this column is also the same and my sessions time zone is America, New York. Let me explain this one by one. You all know that the database gets installed in a computer. The clock of that computer is the database’s time.

It can be read by Sys data. Now, a client can connect to the database from anywhere and we may want the date and time to appear in their local time format. For example, now I am in the Eastern time zone and the database which is installed in this very PC is also in the same time zone. So if I select SIS state, it will show the current time in Est which is Eastern time zone and that is fine. Now, what if I have colleagues who are in Australia now and have connected to this database? If they want to see the date and time in their local format, what can they do for that? They can specify their time zone at their session level. Session is just a connection and is valid until that connection is alive.

Remember, we are connected as HR user. So they do this, they can set up their local session to reflect their local time zone. Alter session set time zone equals to Australia. Slash west will set their local session to their time zone. Now, instead of SIS Date, if they read Current Date, it will bring the same date and time in local time zone format. Let me run this equal again. Remember, this was the previous result where my database time zone as well as my local session time zone were in Eastern. Now I changed it to Australia and I’m going to run it. Look at this current Date column. Okay, it’s 1957, right? Let’s see how it changes. And also it’s May 1, right? Let’s see how it changes. SIS Date always brings the date in the databases time zone which is Eastern.

So it’s 08:00 evening May 1, whereas Current Date brings the value in the sessions time zone. And we know the sessions time zone is Australia West and currently what time is it there? It’s 801 in the morning and second May. Okay, so the current time here is in the Eastern time zone is 08:00 P. m. Which is 20 in 24 hours format, may 1 in Australia, may 2 08:00 A. m. . Okay, so that’s how you read the same date value in your local format. Now, what are the local formats that are supported that can be found by this query? Select distinct time zone name tz name from v dollar time zone underscore names that will give you the time zone formats that are supported by an Oracle database. Okay.

6. DATE AND TIME FUNCTIONS – MORE FUNCTIONS

Let me set the time zone back to Eastern, which is America New York to curve function. Use this function to display the date values in character formats as specified by this string. DD represents day, month represents uppercase three letter abbreviation for months. You can see May in uppercase here y y represents four character year format. HH 24 represents hover in 24 hover format which is zero to 23. EMI represents minutes, and yes yes represents seconds. There are other formats as well. Following are some of those examples. Look at this format I’m using slash here I get the slash m yum DD month 50 one. Look at the difference between mvoy n and m yum.

Okay. And HH has become 02:00 p. m. , whereas here it’s 14. Now let me set nls date format to be in this format. This will make certain date values to be displayed in this format. Add months function. This takes two inputs. One is the date value and the next is a numerical value. Add Months since date, one will add a month to the current date. Current month is May, and if we add one, we get June. If we use negative number here, it will subtract May. Minus one is April. Extract Function we can extract the fields individually from a date value. Extract year will just give us the year. Month will just give us the month.

They will just give us the day. Using last day function, we can find the last day of the month. May 31 is the last day of this month. Current timestamp will give date in timestamp format with time zone.Local timestamp will give the date in timestamp format without the time zone. America New York is a time zone here, and we do not see time zone here. Months Between Function this function will give the number of months between two dates. In this sequel, we are trying to get the months between Higher Date column and Sys date. Today is May 1, 2016.

If the left value is older than the right value, then it will give negative values in the result. See, the high update is older than systate. Now let us change the sequel. Now I have put the systate on the left. The result is positive numbers here. But why the results are on decimal points? It’s because the days are different. 17th here first here so I can round or truncate both the dates by month. I’m rounding it here. I am truncating it here. If I do this, the day field will always be one. Then I can do the comparison. Now the SQL will give whole numbers. Using new time function, we can see the current time in a different zone.

I know my sys date is in est and if I want to see it in PSD, I will use this sequel. Eastern Time. 03:00 Pacific Time. It’s 12:00. Using the next day function, we can find when a particular day will come next. In this example, next Thursday from today which is SIS date is on 5th May we can also extract the Greenwich meantime, using this SQL, we can convert sysdate into timestamp format and timestamp with timezone format. We can also use cast function to convert a type of value. Sys state can be converted into timestamp in this SQL. There are lots of such functions and I just covered the functions that would be useful to you.

7. DATE AND TIME FUNCTIONS – TO_DATE

Often, you may need to insert date values into a date column. How do we create a date value? We can do it by two date function. First, we need to type the character string which needs to be converted into a date value. Then we need to indicate its format in this SQL. I am using this character string as enclosed within these single quotes followed by its format here. Zero phi is Mm as in month. Slash here and so slash here too. Zero one is DD as in day slash and Slash 2016 is in here. Space here and space here. Five is HH inhover semicolon here and semicolon here too.

Zero zero is Mi as in minutes. Let me run the sequel. In this sequel, I have changed the format. Format is just a way of inputting the value. Once it gets stored as a date value, then we can read it in any way that we want. Important thing is to store it as a date value. That is all. And we can do regular date operations on it. I’m adding three months to it. I am checking last day of that month. These are possible only after a successful conversion of this to a date value. Thank you for watching.

8. COMPARISON FUNCTIONS – GREATEST AND LEAST

Oracle provides two functions for general comparisons. The function greatest will provide the highest value. In the set of values that we provide, the highest value is 345. It can do numerical comparison as well as characterbased comparison. So this is a characterbased comparison. The highest value is Car, which starts with the letter C. The function least does the opposite. It returns the lowest value of the set. 25 is the lowest value. Airplane is the lowest value. So it can also do numerical comparison as well as character based comparison. Thank you for watching.