1z0-071 Oracle Database SQL – COMPARISON OPERATORS

1. INEQUALITY OPERATORS – GREATER THAN & LESS THAN

We saw equality operations done by the equals to sign. Now let us explore Inequality Operators, which actually gets us results based on a range of matching values. This is less than sign and this brings rows that are lesser than the condition select First Name Last Name Salary Commission Percentage from employees where commission percentage is less than 00:25. This is greater than symbol. We can use this to get rows that are greater than the condition. The same query with greater than symbol will bring us a result where the commission percentage is greater than 00:25.

Also note that the column in the Where class doesn’t have to appear in the select class. This Commission percentage column doesn’t have to appear in the select class. Let me run this sequel which is Select First Name Last Name Salary from employees where commission percentage is greater than 00:25. In that statement, we are not selecting the commission percentage column, but still can filter the results by it. And it just brought us the rows that match the where condition from the columns specified in the select class.

2. COMPOSITE INEQUALITY OPERATORS – GREATER/LESS THAN OR EQUAL TO

Oracle allows composite inequality operators which comprise of more than one symbol. The following SQL achieves less than or equal to function by using less than equal to sine, and it brings rows that are less than 00:25 as well as equal to 00:25. You see the values that are greater than 00:25 as well as equal to 00:25 as well as the column in the where class doesn’t have to be in the select class. Okay?

3. COMPOSITE INEQUALITY OPERATORS -NOT EQUAL TO

We can also achieve a not equal to function by using a combination of less than and greater than symbols. What this means is it will bring the rows that are not equal equal to the value passed in the condition. For example, this following SQL achieves not equal to function by using this less than greater than symbols together and will bring rows that are not equal to 00:25. Here you will not see any rows that has 00:25 as its value in Commission percentage column. The same function can also be achieved by these two signs exclamation followed by equals to sign, which is equivalent to not equal to. This equal brings the same set of results where the commission percentage is not equal to 0. 25. Okay.

4. INEQUALITY OPERATORS ON DATE VALUES

These operators work on date values too. The following query will bring rose whose date values are earlier than May 6, 2007. I’m using lesser than symbol here. Lesser means earlier. Select first name, last name, salary, higher date from employees where higher date is lesser than 6 May 2007. And this will bring me the results with the rows that has dates that are earlier to 6th May 2007. OK 2003 2005 january 2006 and the following query will bring rose whose date values are after 6th May 2007. For that, I’m using greater than symbol. Greater means after okay, we see the results from 2007 2008. So that’s how the inequality operators work on date values.

5. RANGE COMPARISON – BETWEEN OPERATOR

Earlier, we selected rows that are greater than or lesser than a particular value. What if we need to select rows that are in between two values? For that we can use the between operator. This query select first Name last Name salary commission percentage from employees where salary between 5000 810,000 will bring rows where salary is greater than or equal to 5800 but lesser than r equal to 10,000. Let me run it. You would see the values are between 5000 810,000. Let me sort it. I’m running the same query with order by salary class. Here you would notice that the result contains rows where the salary values are between 5000 810,000. You will also notice that it includes 5000 810,002.

6. SET COMPARISON – IN OPERATOR

We can also select rows that satisfy a particular set of values that we pass into the SQL. For that, we would need to use the in operator this sequel select first Name Last Name Salary commission percentage from employees where salary in 5800, 8000, 210,000 will bring rows where the salary contains values 5800 or 8200 or 10,000 let me run it so the result has rows where the salary is 8200 or 5800 or 10,000. Of course, we can also do the same for character comparisons too.

Remember to use the single quotes. This statement select first Name Last Name salary commission percentage from employees where first name in james within single quotes wally within single quotes Sarah within single quotes will bring Rose where the first name contains the values that we just mentioned. That’s the use of this in operator which is a set comparison operator.