SQL operator: – SQL operators are used to perform some function that would not have been possible with only SQL query. The SQL operators are value operator, logical operator etc.

Value operator: – Value operator used with arithmetic sign. There are four value operator such as +, -, *, /. There four operators are used with select statement.

Plus (+) operator: – This operator adds value or column in selected field. An example of plus operator is –

                Select (salary+100) from employee;

In case of above example, value 100 added in salary column only when it is listed.

Logical operator: – The logical operators are used for conditional testing. There are number of logical operator used with select statement for conditional testing. They are =, <>, >=, <=, >, <. These operators are used for conditional testing.

An example of not (<>) operator: –

                Select ename from employee where city<>‘Nagpur’;

In case of above example, ename column of table employee are listed only when its city column not equal to ‘Nagpur’.

Between operator: – The between operator is used for testing one value which is between the two values. An example –

                Select ename from company where salary between 20000 and 40000;

In case of above example, value of ename column listed from table company which salary is greater than or equal to 20000 and is less than or equal to 40000.

In operator: – The in operator is used for testing the membership of element in set. For example –

                Select ename from employee where city in(‘Nagpur’,’Mumbai’);

In the above example, values of ename column is listed from table employee which city column is either Nagpur or Mumbai.

Not In operator: – The Not in operator is reverse of in operator. The not in operator test the membership of element which is not in set. For example –

                Select ename from employee where city not in(‘Nagpur’,’Mumbai’);

In case of above example, values of in name column listed from table employee which city column is not match from value Nagpur or Mumbai.

Any Operator: – The any operator compares a value to each value in a set and return true if any value is compared according to compared to condition. An example –

                Select ename from company where cname=’ACC’ and salary>any(select salary from employee where cname=’Tata’);

In case of above example, values of ename column listed from table company which cname=’ACC’ having salary greater than any other employee of table employee which cname=’Tata’.

All operator: – The all operator is used to compares a value to every value in set and returns true if condition is satisfied for every value. An example –

                Select ename from company where cname=’Telco’ and salary > all(select salary from employee where cname=’Tata’);

In case of above example, values of ename column are listed from table company which cname column=’Telco’ having salary greater than every employee of table employee which cname=’Tata’.

Exists operator: – The exists operator used for testing whether a sub query returns at list one row. It is true if it returns one row, otherwise it is false. An example –

                Select ename from employee where city=’Nagpur’ and exists(Select company.ename from company where employee.ename=company.ename and company.ename=’Tata’);

In case of above example, value of ename column listed from table employee having city=’Nagpur’ and company name=’Telco’.

Not exists operator: – The not exists operator is used for testing whether a subquery returns null. It returns true if no row exists otherwise its returns false. The not exists is opposite of exists operator. An example –

                Select ename from employee where city=’Nagpur’ and Not exists(select company.ename from employee,company where employee.ename=company.ename and company.ename=’Telco’);

In case of above example, value of ename column listed from table employee which city is Nagpur and not match with table company.

Like operator: – The like operator is used for pattern matching. An example –

                Select ename from employee where city like ‘M%’;

The above statement will display listing of ename column value of these employee which city name’s first letter is M.

Not like operator: – The not like operator is used for pattern un-matching. The not like operator is reverse of like operator. An example –

                Select ename from employee where city not like ‘M%’;

The above statement will display listing of ename column from table employee where city names is not match with letter M.

Is null operator: – The is null operator is used for testing whether the value in column is NULL. If value of column is null or not specified, it will be listed otherwise not. For example –

                Select ename from company where salary is null;

In case of above example, values of ename column listed from table company only when its salary column is empty or not specified.

Is not null operator: – Is not null operator is just reverse of is null operator. It displays listing of only those columns which is not null. An example –

                Select ename from company where salary is not null;

In case of above example, values of ename column listed from table company which salary column is not null.

And operator: – The and operator is used to combines two or more logical condition. And operator return true only when all the condition is logically true. An example –

                Select ename from company where salary > 1000 and cname=‘Telco’;

In case of above example, values of ename column listed from table company only when its salary greater than 1000 and cname=’Telco’.

Or operator: – The or operator combines two logical condition. It returns true only when any one out of two condition are true. For example –

                Select ename from company where salary > 2000 or cname=’Telco’;

In case of above example, values of ename column are listed from table company only if its salary is greater than 1000 or its cname=’Telco’

Not operator: – The not operator is used to reverse a result of logical expression. If condition is not true, it will be listed and if condition is false, it will not have listed. For example –

                Select ename from company where not(cname=‘Telco’);

In case of above example, values of ename column are listed from table company only when cname column is not equal to Telco.

Advertisements