Resctricting Rows

 Using SELECT statement we can retrieve data from a table, but if we need just few rows of a table returned based on one or more conditions we need to use WHERE clause.
Here are some important rules regarding WHERE clause:

• Where clause is optional;
• It always follows the FROM clause;
• We can apply one or more conditions to WHERE Clause.

Here are some exmples of using Where clause:

SQL> select first_name
from employees
where salary>15000;

FIRST_NAME
--------------------
Steven
Neena
Lex

It means salary should be 2000 or 5000 or 7000FIRST_NAME

SQL> select first_name
from employees
where salary=6000;

FIRST_NAME
--------------------
Pat
Bruce

 

SQL> select first_name from employees where last_name='Smith';

FIRST_NAME
--------------------
Lindsey
William

SQL> select first_name from employees where last_name like 'Sm_th'; ----> _ represents exactly one caracter

FIRST_NAME
--------------------
Lindsey
William

SQL> select first_name from employees where last_name like 'Smi%';  ----> % presents  0 or more caracters

FIRST_NAME
--------------------
Lindsey
WilliamWe can restrict rows by more than one condition. To do this we are using operators AND and OR.

• AND Operator - all conditions should be true

FIRST_NAME
--------------------
Lindsey
William

• OR Operator - one of the conditions should be true

SQL> select first_name from employees where last_name like 'Smi%' or salary > 10000;

--------------------
Michael
Shelley
Steven
Neena
Lex
Nancy
Den
John
Karen
Alberto....

BETWEEN Operator - comparison operator. It tests if some column value or expression falls within a scpecified range. For example:

WHERE salary BETWEEN 2000 and 5000;

It means : salary should be >=2000 and <=5000IN Operator - It testw if an item is member of specified set ot values. For example:

WHERE salary IN (2000,5000,7000);

NOT Operator - negates the comparison operator in condition. For example:

WHERE salary NOT IN (2000,5000,7000)



 SQL> select first_name from employees where last_name like 'Smi%' and  salary > 5000;