1z0-071 Oracle Database SQL – CONDITIONAL FUNCTIONS

1. CONDITIONAL FUNCTIONS – NVL, NVL2 and NULLIF

Circle provides functions which act only if certain conditions are met. Let us take a look at this SQL. This is a simple sequel. We see some null values in the state province column. Instead of displaying as null, I can display it as another string using NVL function NVL state province not applicable. Replaces null values with not applicable string. That’s the condition. If there are null values, then display them as not applicable. Otherwise display the original non null value. What if I want to change those original non null values to? For that I can use NVL two. This enhances NVL a little bit. It can display the non null values as this second parameter and null values as this third parameter.

This is the first parameter. So here the null values are displayed as not applicable and non null values are displayed as applicable. And then we have this nullif function. This takes in two parameters parameter number one, parameter number two separated by a comma and if they are same, then it returns null values. Meaning that if this is equal to this, then it returns null values. Otherwise it will return the first value. So here if there are any rows with max salary, which is twice the minimum salary, then it will return null. For example, here the max salary is 30,000. Here the minimum salary is 15,000, which is the max salary is twice the minimum salary. And so we get null values here.

2. CONDITIONAL FUNCTIONS – DECODE and CASE

This is kind of like if then else function here the first parameter gets processed. If it is same as the second parameter then it gets replaced with this third parameter and so on. In this example, if job ID is ad underscore Pros then it will be replaced by president or if it is it underscore Prague then it will be replaced by software developer sarip will be replaced by sales associate everything else will be replaced with not applicable. You can see it here ad press has been replaced with President.

It Prog as software developer everything else as not applicable or I can leave everything else to its original value. Like this SQL look at the difference. Instead of specifying the everything else value, I’m just specifying the column name itself in its place. Okay, so this does the change where it’s applicable and leaves the rest case function. The case function also provides if the nails functionality with more options. Let us just say that I want to make those who were hired after Janfa.