1z0-071 Oracle Database SQL – Restricting and Sorting Data

1. Use the ORDER BY clause to sort SQL query results

We can sort the results using order by class. For example, this SQL will bring the result as it is from the table. Now, let us sort it by first name. Select first Name last Name salary commission percentage from employees order by first name. This will sort the results in the ascending order of the column. First name ascending order is the default.

This is equivalent to specifying the keyword ASC in the end. Okay, the keyword DESC will sort the results in descending order. And this is applicable to numerical values too. For example, I’m going to do an order by using Salary column. Okay, I’m going to do a descending order by.

2. Sorting NULL Values and Expressions

If the column in the order by class contains null values, then they would appear in the last by default. Select. First name, last name. Salary commission percentage from employees. Order by commission percentage. If we scroll down, we see all the null values in the bottom which is last and that’s equivalent to specifying the keywords nulls last.

In the sequel, we can change this behavior by specifying the nulls first class. Now the null values appear on the top. We can also order by expressions. Select first name, last name. Salary times ten as new salary. Commission percentage from employees ordered by new salary. The result is ordered by New Salary column, which is actually an alias for the express salary times ten.

3. Positional and Composite Sorting

Circle allows us to do sorting based on the position of the column in the select class. For example, out of the four columns that we are selecting, first name is in the first position, last name is in the second position, new Salary is in third, and Commission percentage is in fourth. So if I want to sort by new salary, I can simply mention its position here. Select First Name last Name salary as New Salary commission percentage from employees, order by three sorted using New Salary if I want to sort by using Commission percentage, I can use position four.

Oracle also allows us to do composite sorting, which is sorting by multiple columns. Results will be sorted by the first column name in the order by class and then by subsequent column names. Let me run this select first Name last Name Salary commission Percentage from Employees order by Salary comma first Name here the results are sorted by salary first. Then for those who has the same salary, it is again sorted by first name. We can combine positional sorting with composite sorting too.

4. Limiting the Rows with a WHERE clause

Where class to limit the result to what we want. For example, select first name, Last name, salary, commission, percentage from employees where salary equals to 2500 will bring us rows where the salary is equal to 2500. This is a numerical comparison. Select first name, Last Name, salary, commission, percentage from employees where first name equals to James. Within quotes will bring us rows where first name is James. This is a character comparison and it needs single quotes to enclose the value. This is also case sensitive. The following query is not same as the previous query.

Note the case difference in James. Okay. And what if I use quotes in a numerical comparison within quotes 2500 nothing will happen. The value will be converted into number implicitly. What if I do not use quotes in the character comparison? It will fail with invalid identifier error. What if I use a number in character comparison? It will fail with invalid number error. And what if I use character in numerical comparison? It will fail with invalid number error. And without codes you will get invalid identifier error.

5. Column based and Date based Conditions in WHERE clause

We can compare two columns in a table, though it is highly unlikely to have redundant sets of data within the same table. However, let me show you these examples, even though there may not be any data matching these conditions select Job ID, job Title, minimum Salary, maximum Salary from Jobs, where minimum salary equals to maximum salary. When we do a comparison between columns, you would notice that we are not using single quotes or any other quote operators. Select star from employees where employee ID equals to department ID.

They do not bring any data because there isn’t a row where the Employee ID matches Department ID. We can also use databased conditions. Select first name, last name higher date from employees where Higher Date equals to 15. March 6 brings me this result. Here. Both these sequels are same. There is no literal character comparison. If you see literally these are different.

This is six and this one is 2006. As long as this is in a valid date format, which is DD M-O-N as in month, Y Y as in two-digit year R-Y-Y as in four-digit year, oracle converts it to date format and compares it with the data in the table. If we use only two digits in the year, oracle automatically converts it to a four-digit year format using the condition, which is if this value is between zero to 49, it assumes as it represents this century, and if it is between 50 to 99, then it converts it to the previous century. For example, if this is 95, then it will be assumed as 1995, and if this is twelve, then it will be assumed as 2012.