DATA FILTERING USING WHERE CLAUSE IN ORACLE

You can restrict the rows returned from the query by using the WHERE clause. A WHERE clause contains a condition that must be met, and it directly follows the FROM clause. If the condition is true , the row meeting the condition is returned.

Syntax - select * from tablename where conditions;

Display employee records whose job='CLERK' ?

SQL> select * from emp where job='CLERK';

Display employee records joined after 1981 ?

SQL> select * from emp where hiredate>'31-DEC-1981';

BETWEEN OPERATOR

It is used to retrieve range of values from a column. This operator is also called as between....and operator.

Write a query to display the employees who are getting salaries between 2000 to 5000 from emp table ?

SQL> select * from emp where sal between 2000 and 5000.

It is also same as - select * from emp where sal>=2000 and sal<=5000;

Write a query to display the employees who are getting salaries not between 2000 to 5000 from emp table ?

SQL> select * from emp where sal not between 2000 and 5000;

LIKE OPERATOR

This operator is used to retrieve data based on character pattern specified along with column data. This operator performance is very high compare to all other searching functions in Or acle database. Along with like operator we are using 2 special character. These special characters are also called as "WILDCARD" characters - _ and %

Write a query to display the employees whose ename starts with capital letter 'M' from emp table by using like operator ?

SQL> select * from emp where ename like 'M%';

Write a query to display the employees whose ename whose ename having any letter having capital 'M' from emp table by using like operator ?

SQL> select * from emp where ename like '%M%';

Write a query to display the employees whose ename second letter is 'L' from emp table by using like operator ?

SQL> select * from emp where ename like '_L%';

Write a query to display the employees who are joining in the year 81 from emp table by using like operator ?

SQL> select * from emp where ename like '%81';

Generally whenever table column having wildcard characters and also if we are retrieve data based on wildcard then database servers returns wrong results because here wildcard characters having special meaning. To overcome this problem for escaping special meaning of the wildcard character then ANSI/ISO SQL provided escape function on along with the like operator.

Syntax - select * from tablename where colname like 'characterpattern' escape 'escapecharacters';

Here escape character length must be 1 byte.

Which wildcard meaning you want to change that wildcard character before we must specify escape character with in character pattern then only oracle server anything after escape character, wildcard character meaning does not treated as a special meaning i.e. it escapes special meaning.

select * from emp where ename like'S@_%' escape '@';

@ here after escape character under_score treated as underscore, not treated as wildcard character

Write a query to display the employees whose ename starts with 'S__' from emp table by using like operator ?

SQL> select * from emp where ename like 'S1_1_%' escape '1';

IN OPERATOR

It is used to pick the values one by one from list of values. We can also use IN operator in place of OR operator. When we are trying to retrieve multiple values from a single column. In this case IN operator performance is very high compared to OR operator.

Syntax - select * from tablename where colname in(val1,val2....);

Write a query to display the employees who are belong to the deptno 10,20,30 from emp using IN operator ?

SQL> select * from emp where deptno in(10,20,30);

Write a query to display the employees other than 10,20 dept from emp table ?

SQL> select * from emp where deptno not in(10,20);

IS NULL, IS NOT NULL OPERATOR

This two special operators are used in where clause only. This operators are used to test whether a column having null values or not.

Syntax - select * from tablename where colname is null;

Syntax - select * from tablename where colname is not null;

Write a query to display the employee whose commission is null from emp table ?

SQL> select * from emp where comm is null;

Write a query to display the employee whose commission is not null from emp table ?

SQL> select * from emp where comm is not null;