Sql Operators

Sql 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 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 :

 Using In Operator

• select * from emp where empno 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 :

 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%’;

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;