1z0-071 Oracle Database SQL – BOOLEAN OR LOGICAL OPERATORS

1. BOOLEAN or LOGICAL OPERATORS – CONCEPTS

Up to this point we saw single column conditions. Let us learn some theory before proceeding any further. Let us assume that we have a fiction table called sample underscore Tbl with these data here. If I do select star from sample underscore table where call one equals to b, then we will get these rows that are highlighted in pink in the result. Because these rows match the condition call one equals to B, you see the values be here and so these rows will be fetched here. If I do select star from sample underscore table where call three is greater than R equals to 600, then we will get these rows that are highlighted in pink in the result.

That’s because these six, hundreds and 700 test true for the condition call three greater than or equal to 600, 600 is equal to 600, 700 is greater than 600. If you notice, we’ve been doing one condition in the where class and by the nature of it we are restricting data based on conditions on one column. What if I want to do multiple conditions and make a comparison between them that would allow me to do multiple column conditions in a single sequel II that is possible by boolean operators or logical operators. Oracle supports boolean operators are and and not. Let us take a look at them in the subsequent videos.

2. BOOLEAN OPERATOR – OR

Let me explain the R operator with an example first. Here we can see two conditions. Condition one is call one equals to A. Condition two is call three equals to 700. Let us see which rows will test true for condition one. Select star from sample underscore Tbl where call one equals to a R. Call three equals to 700 for condition one, which is call one equals to A. Row number one, two and six will be fetched. Now let us go to the condition two which is column three equals to 700. Which rows will test true for it? Row number five and six. I’m just using the values in the column Snow, which is serial number, to refer the rows as row number five, R one, R two.

Oracle uses a different logic internally which is called as rowid. Let us keep things simple as of now. So we have two sets of intermittent results. But we still need to get the final result. To get the final result, let us take a look at how an R operator works. Let us take condition one, which is column one equals to A and mark the rows that test true for this condition. For example, row number one will test true for condition one because the value for column one is A and it will test false for condition two because the value of column three is 500 and not 700 like that. Row number two will test true for condition one and false for condition two.

Row number three and four will test false for both the conditions. Row number five will test false for condition one because column one is C here, but it will test true for column three as the value is 700. Row number six will test true for both the conditions. The R operator returns true if any of the conditions are true. It is kind of like we saying in real life, like give me this or that it will be false. Only both the conditions are not met. In other words, both the conditions are false. So the R operator’s result

would be something like this for the first row, since the condition one turns out to be true here, the final result is true. And so this row will appear in the final result.

For the second row, it will be same as the first row. Since condition one is true. This will also appear in the final result. Rows three and four will not appear in the final result as both the conditions are not met, meaning false. Fifth row will appear as condition two has been met. 6th row will also appear since both the conditions have been met. So remember, even if you have many conditions joined by multiple R operators, its final result will be false only if every condition is false. Even if one condition turns out to be true, then it will result into a true for the overall comparison of that row. And the final result will be the rows one, two, five and six.

3. BOOLEAN OPERATOR – AND

In the previous lesson we saw about R operator. Now, what if the operator is an and operator? We know that condition one which is call one equals to A and condition two is call three equals to 700. We also know that the following rows tested true and false according to the conditions. Now let us see how an and operator functions. The and operator returns true only if all the conditions are true. It’s kind of like saying in real life like give me this and also that it will be false if any of the conditions are not met. So the and operator’s result would be something like this for the first row since condition two turns out to be false. Here the final result is false. And so this row will not appear in the final result.

For the second row, it will be the same as first row. Since condition two is false. This also will not appear in the final result. Rows three and four will not appear in the final result as both the conditions are not met. Meaning false. Fifth row will not appear as condition one has not been met. 6th row will appear since both the conditions have been met. So remember, even if you have many conditions joined by multiple and operators its final result will be true only if every condition is true. Even if one condition turns out to be false then it will result into a false for the overall comparison of that row and the final result will be just the row six.

4. BOOLEAN OPERATOR – NOT

Not operator is used alongside of another operator. It just negates the condition following that operator, meaning that the result will have everything other than what that condition which is mostly the condition two would have fetched. For example, a naught operator here will reverse the output of the second condition call one equals to a or not call three equals to 700. If we say it in spoken language we can say I want the rows where call one equals to A-R-I don’t want any rows where col three is 700. When you say that, it implicitly means that I want rows whose values are other than 700.

So for the not condition two, the call three rows with any value other than 700 will test true. The rows with value 700 will test false. So the output will be something like this. If you notice, understanding the implicit meaning is kind of confusing so this not operator is not used generally in this context. Instead, it is common to just use the regular boolean operator, which is R here, and to reverse the comparator in the condition two. For example, use the not equal to comparator instead of equal to comparator. This way it would be easy to understand.

5. BOOLEAN OPERATORS – HANDS ON DEMO

In this SQL, I have two conditions in where class job ID equals to St underscore Clerk R, salary equals to 2500. I’m just ordering it by job ID and salary so that we can easily go through the result. So let’s take a look at the first row. Job ID is PU underscore clock. So the first condition is not met. But yet this row is part of the result. If we check, we’ll find out why. Because the second condition is met. Salary equals to 2500. Same is the case here. Here. And if you look at the fourth one, the first condition is met st underscore clock and the second condition is not met. So it appears on the result. If you look at these rows, both the conditions have been met. Now let me use are not combination. So here it means job ID is in St underscore Clerk R.

I don’t want anything where salary equals to 2500. Meaning that bring me everything where salary is not 2500. About 50 rows came. If I scroll down, it brought everything where the job ID is SD underscore Clark. It also brought rows where the job ID is SD underscore Clerk and the salary is 2500.Even though I said I don’t want anything from 2500. Here the first condition has met. And that’s the reason these rows appear. And if we look at the other rows Shandscore, Clark, they appear because the salary is not 2500. So the second condition is met. I can achieve the same thing using this sequel which is easily readable and understandable. Same 50 rows where job ID is SD underscore Clerk or salary is not 2500.

Let’s go to and operator. Here I brought rows where job ID is SD underscore Clerk and salary is 2500. It’s very simple. You see, both the conditions are met and so they are part of the result. Let me use and not combination. Okay? So here I’m saying the job ID should be St underscore Clack which is there. But I don’t want anything where the salary is 2500 within that set. Because that’s a feature of. And so within St underscore clock it brought me everything where the rows are other than 2500, about 17 rows came in. I can do the same thing in this equal same 17 rows where I’m comparing job IDs T underscore Clerk and salary is not equal to 2005 at.