Operators

1. Relational Operators ( >,>=, <, <= , < > or != (not equal to) )

2. Logical Operators ( AND, OR, NOT )

3. Special Operators (IN, BETWEEN, LIKE and Is )

Queries using Where Clause( Relational & Logical ) 

1. List The Employees Belonging To The Department 20

• Select * from emp where deptno = 20 ;

1. List The Name And Salary Of The Employees Whose Salary Is More Than 1000

• Select ename, sal from emp where sal > 1000 ;

1. List The Names Of The Clerks Working In The Department 20

• Select ename, job from emp where job = ‘Clerk’ and deptno = 20 ;

1. List The Names Of Analysts And Salesman

• Select ename, job from emp where job = ‘ANALYST’ or job=‘SALESMAN’ ;

1. List the details of the employees who have joined before the end of september 1981.

• Select * from emp where hiredate <= ’30-Sep-1981’ ;

Using Special Operators

IN : The IN operator is used to determine if a given value matches any value in a sub query or in a list

Syntax : [ Not ] In <List of Values>

Examples :

1. List the employees details whose employee id’s are 7369,7499,7788

• select * from emp where empno=7369 or empno=7499 or empno=7788;

Using In Operator

• select * from emp where empno in (7369,7499,7788);

1. List the employees details whose employee id’s are not 7369,7499,7788

• select * from emp where empno!=7369 and empno!=7499 and

empno!=7788;

Using In Operator

select * from emp where empno not in (7369,7499,7788);

Between

The Between operator is used to specify a range of values. It retrieves the values by depending on the condition, on the range of any column

Syntax : [ Not ] Between <Begin Expression And End Expression>

Note : Unlike in ordinary English, “Between” is an inclusive operator ie both the values will be included in the range. The Not Between is an Exclusion operator

Examples :

1. List the employees details whose salary is >=3000 and <=5000

• select * from emp where sal>=3000 and sal<=5000;

Using Between Operator

• select * from emp where sal between 3000 and 5000;

1. List the employees details whose salary is not between 3000 and 5000

• select * from emp where not sal>=3000 and sal<=5000;

Using Between Operator

• select * from emp where sal not between 3000 and 5000;

Like Operator

The Like Operators is only used with Char and Varchar2 data types to match a pattern of characters

It determines whether or not a given character string matches the specified pattern.

A pattern can include regular characters and wild card characters. During pattern matching regular characters must exactly match the characters specified in the character string.

Oracle supports two types of wild cards, they are

1. _ ( underscore ) : Represents a Single Character
2. % ( Percentage ) : Represents Multiple Characters

syntax : [ Not ] Like < Pattern >

Note : This Like Operator can only be used with Varchar2 and Char data types only.

Examples :

1. List the details of the employees whose names starting letter is ‘S’

• select * from emp where ename like ‘S%’;

1. List the details of the employees whose names Ends with letter ‘N’

• select * from emp where ename like '%N';

1. List the details of the employees whose names Second Character is letter ‘A’

• select * from emp where ename like '_A%';

1. List the details of the employees whose names Exact Length is 5 characters only

• select * from emp where ename like '_ _ _ _ _';

The special operator “is” is used with the key word “NULL” to locate Null Values

Is Operator

This operator is used to find or locate the Null values in the given column or expression

Examples :

1. List the details of the employees who are not Eligible for Commission

• select * from emp where comm is NULL;

1. List the details of the employees who are Eligible for Commission

• select * from emp where comm is not NULL;

1. List the details of the Manager who are Eligible for Commission

• select * from emp where job='MANAGER' and comm is null