Restricting & Sorting Data

In this section we will see, how we can restrict the data in our select statement using:
1. The WHERE clause
2. Comparison operators
3. Boolean operators
4. Precedence rules

The WHERE clause:
The where clause can be used to restrict the result sets. Here are the few basic examples using where clause:

Example 1: Using numeric literals in where clause.
SQL> SELECT * FROM regions WHERE region_id=1;
REGION_ID   REGION_NAME
-------------------------------------
                   1  Europe

Example 2:Using string literals in the where clause.
SQL>SELECT * FROM regions WHERE region_name='Europe';
REGION_ID   REGION_NAME
-------------------------------------
                   1  Europe

Example 3: Using comparison operator in the where clause.
SQL>SELECT first_name||' '||last_name FROM employees WHERE SALARY>10000;
----------------------------------------------------
Steven King
Neena Kochhar

Example 4: Using date literals in the where clause.
SQL>SELECT employee_id from job_history WHERE start_date='01-JAN-99';
EMPLOYEE_ID
------------------
                   122
                   176

Example 5: Using date literals in the where clause.
SQL>SELECT employee_id from job_history WHERE start_date + 30 = '31-JAN-99';
EMPLOYEE_ID
------------------
                   122
                   176
Note:
1. When two dates are subtracted,  it gives result as number of days-1 between the two dates.
2. When a number(n) is added to a DATE type, it gives result as DATE having value n more. The example 5 shows the same.

Comparison Operator in where clause:
The comparison operator can be classified as equality and inequality operator. The examples of the equality operator has already been seen above. Lets get familiar with the inequality operators.

Operator Symbol	Description
<	Less than
>	Greater than
<=	Less than equal to
>=	Greater than equal to
<>	Not equal to
!=	Not equal to

Examples:
SQL>SELECT first_name||' '||last_name FROM employees WHERE SALARY > 10000;
SQL>SELECT first_name||' '||last_name FROM employees WHERE SALARY < 10000;
SQL>SELECT first_name||' '||last_name FROM employees WHERE SALARY >= 10000;
SQL>SELECT first_name||' '||last_name FROM employees WHERE SALARY <= 10000;
SQL>SELECT first_name||' '||last_name FROM employees WHERE SALARY <> 10000;
SQL>SELECT first_name||' '||last_name FROM employees WHERE SALARY != 10000;

BETWEEN Operator:
The between operator tests whether the column/expression value falls within the boundary values specified.

Example 1: To get the list of employees whose salary is between 1000 and 10000.
SELECT first_name||' '||last_name FROM employees WHERE SALARY BETWEEN 1000 AND 10000;

Example 2: To get the list of employees whose hire date is ranging between '01-JAN-2001' and '01-DEC-2013'
SELECT first_name, hire_date FROM employees WHERE hire_date BETWEEN '01-JAN-2001' AND '01-DEC-2013';

IN Operator:
The IN operator tests whether an item is a member of a set of literal values. The set
is specified by a comma separating the literals and enclosing them in round brackets.

SELECT last_name FROM employees WHERE salary IN (1000,2000,3000);
SELECT last_name FROM employees WHERE last_name IN ('Ranjan','Kumar','Miller');
SELECT last_name FROM employees WHERE hire_date IN ('01-JAN-2001','01-DEC-2013');

LIKE Operator:
The two wildcard characters used with the LIKE operator is '%' & '_'.
The % symbol is used to specify 0 or more wildcard characters.
The _ symbol is used to specify only one wildcard character.

Lets review some examples to have the better understanding of LIKE operator:
Example 1: To list all the employees whose first name starts with 'R'.
SELECT first_name FROM employees WHERE first_name like 'R%';

Example 2: To list all the employees whose first name ends with 'R'.
SELECT first_name FROM employees WHERE first_name like '%R';

Example 3: To list all the employees whose first name starts has characters 'av' in between.
SELECT first_name FROM employees WHERE first_name like '%av%';

If the like statement is not having any wildcard character, it behaves as an equal operator. For example the below sql statements gives the same result:
SELECT first_name FROM employees WHERE last_name like 'ravi';
SELECT first_name FROM employees WHERE last_name = 'ravi';

ESCAPE identifier :

There could be situations where you have to specify '_' symbol under the comparison literals in where clause. however, we have seen above that the underscore is a wildcard character. So in order to achieve this, we user ESCAPE characters. The symbol (\) is used as the escape character.

Example: If we want to search job ids statring with 'SA_', we use the escapae character(\) before the underscore symbol.

SELECT job_id FROM jobs WHERE job_id like 'SA\_%' ESCAPE '\';

The ESCAPE identifier treats any character found after the backslash character as a regular nonspecial symbol with no wildcard meaning.

The $ symbol is also used as the ESCAPE identifier . For example:

SELECT job_id FROM jobs WHERE job_id like 'SA$_%' ESCAPE '$';

IS NULL operator:

The IS NULL operator is used to SELECT the rows WHERE a specific column value is NULL. For example:

SELECT first_name FROM employees WHERE commission_pct IS NULL;

Boolean Operators

The boolean operators enables programmer to have mulitple conditions under a single WHERE clause. Lets explore these boolean operators.

The AND Operator

The AND operator is used when both the conditions under the WHERE clause has to be true. 

Example: We want to see the list of employees whose first_name starts with 'R% and commission_pct is greater than 0.02.

SELECT first_name, commission_pct FROM employees WHERE first_name like 'R%' AND commission_pct > 0.2;

The OR Operator

The OR operator separates multiple conditions, at least one of which must be satisfied by the row selected to display the results set.

Example: We want to see the list of employees whose first name starts with 'R%' or commission_pct is greater than 0.02.

SELECT first_name, commission_pct FROM employees WHERE first_name like 'R%' OR commission_pct > 0.2;

The NOT Operator

The NOT operator negates the comparison operator in a condition.

Example: We want to see the list of employees whose first name starts with 'R%' or commission_pct not equal to 0.02.

SELECT first_name, commission_pct FROM employees WHERE first_name like 'R%' and NOT(commission_pct = 0.2);

Operators Precedence Rule Table:

Precedence Level	Operator Symbol	Operation
1	()	Parentheses or brackets
2	/,*	Division and multiplication
3	+,-	Addition and subtraction
4	||	Concatenation
5	=,<,>,<-,>=	Equality and inequality comparison
6	[NOT] LIKE, IS [NOT] NULL, [NOT] IN	Pattern, null, and set comparison
7	[NOT] BETWEEN	Range comparison
8	!=,<>	Not equal to
9	NOT	NOT logical condition
10	AND	AND logical condition
11	OR	OR logical condition

Note: The operators having same precedence are evaluated from left to right.

The ORDER BY clause:
The ORDER BY clause is used to sort the data according to the user preferences.
SYNTAX:
SELECT *|{[DISTINCT] column|expression [alias],…}
FROM table
[WHERE condition(s)]
[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];
Example:
SELECT employee_id FROM employees WHERE hire_date > '01-JAN-07' ORDER BY hire_date ASC;

By default the ORDER BY clause will order the data in ascending order if no preferences are specified.
SELECT employee_id FROM employees WHERE hire_date > '01-JAN-07' ORDER BY hire_date;
In the above example the default order of result will be ascending order of the hire_date.

Without the ORDER BY clause the oracle uses 'undefined' sort order.

If the column used for sorting, has some NULL values, we can use optional NULLS LAST or NULLS FIRST with ORDER BY clause to show the NULL values at last or first respectively.

SELECT last_name,commission_pct FROM employees WHERE JOB_ID IN ('SA_MAN','MK_MAN') ORDER BY commission_pct DESC NULLS LAST.

We can do multi level sort or Composite sort using the ORDER BY clause.
SELECT last_name,commission_pct FROM employees WHERE JOB_ID IN ('SA_MAN','MK_MAN') ORDER BY commission_pct, last_name;

Positional Sorting:
A shorter way to specify the sort column or expression is instead of specifying the column name, the position of the column as it occurs in the SELECT list is appended to the ORDER BY clause.
Example:
SELECT last_name,commission_pct FROM employees WHERE JOB_ID IN ('SA_MAN','MK_MAN') ORDER BY 2;

The ORDER BY clause specifies the numeric literal two. This is equivalent to:

SELECT last_name,commission_pct FROM employees WHERE JOB_ID IN ('SA_MAN','MK_MAN') ORDER BY commission_pct;

Ampersand Substitution:
Single Ampersand Substitution
The ampersand character (&) is used to designate a substitution variable in a statement and precedes the variable name with no spaces between them. Upon execution of statement,Oracle processes the statement, notices a substitution variable, and attempts to resolve this variable’s value in one of two ways:
1. First, it checks whether the variable is defined in the user session using 'DEFINE'.
2. If the variable is not defined, the user process prompts for a value that will be substituted in place of the variable. Once a value is submitted, the statement is complete and is executed by the Oracle server.

The ampersand substitution variable is resolved at execution time and is sometimes known as runtime binding or runtime substitution.
Example:
select employee_id, last_name, phone_number from employees where last_name = &LASTNAME;

When the above query is executed, SQL prompts user to enter the value of variable &LASTNAME. If the user enters the value as 'Ranjan', the above query becomes equivalent to the below one:
select employee_id, last_name, phone_number from employees where last_name = 'Ranjan';

Double Ampersand Substitution
This is used when you want to reference the substitution variable multiple times.
For example: If we want to search an employee by first and last name, we use following querry:
select first_name, last_name from employees where last_name like '%&VAR_NAME%' and first_name like '%&VAR_NAME%';

The above example uses single ampresend substitution but has to be used twice, instead we can use the double ampresend substitution as follows:

select first_name, last_name from employees where last_name like '%&&VAR_NAME%' and first_name like '%&VAR_NAME%';
.............................................UNDER CONSTRUCTION......................................................