Operators
Arithmetic operators ( +, – * / )
Examples:
Select roll,name,m1,m2,m3,(m1+m2+m3),(m1+m2+m3)/3 from student;
Update emp set sal=sal+.10*sal;
Logical operators ( And , Or , not )
And
or
Display details of student whose roll number is either 101 or 105.
Select * from std where roll=101 or roll=105;
Display details of all the students except ram and shyam.
Select * from std where not(name=’ram’ or name=’shyam’);
Not
Display details of all the student except whose roll number is either 101 or 105.
Select * from std where not(roll=101 or roll=105);
Display details of all the students except ram and shyam.
Select * from std where not(name=’ram’ or name=’shyam’);
Select * from emp where not(deptname=’production’ or deptname=’sales’);
Range Searching
Between and operator
Between and operator helps us to fetch data which falls within a specified range.
Points
(i). the lower limit is given first and upper limit is given later
(ii). the upper and lower limit are linked with the keyword “and”.
(iii). Limits are inclusive
(iv). Can work for both numeric and varchar2 (char ) data.
(v). both the limits should be of same types. (either both should be numeric or both should be varchar2).
Select * from std where age>=10 and age<=20;
Select * from std where age between 10 and 20;
Select name from emp where sal between 10000 and 99999;
Select * from std where name between ‘amit’ and ‘sumit’;
Not Between and Operator
Logically opposite of between and operator
Select * from std where age not between 10 and 20;
Select * from std where name not between ‘amit’ and ‘sumit’;
Pattern Matching
Like operator
Like operator helps us to compare two strings which are not exactly same.
Two wild characters are used.
% : any number of characters and any characters.
_ : any one character.
Display details of all the students whose name starts with “A”.
Select * from std where name like ‘A%’ or name like ‘a%’;
Display details of all the student whose name starts with either a or b and who can vote.
Select * from student where (name like ‘A%’ or name like ‘B%’) and age >=18;
Display details of all the student whose name is of 4 characters ending with ‘t’.
Select * from std where name like ‘_ _ _t’;
Display details of all the employees whose name starts with either a or b and whose salary is in 5 digits.
Select * from emp where sal between 10000 and 99999 and (name like ‘A%’ or name like ‘B%’);
Not Like operator
Logically opposite of Like operator
Display details of all the students whose name does not start from ‘a’.
Select * from std where name not like ‘A%’;
IN operator
Equal to (=) operator helps us to compare one value with another value. But if want to compare a value with list of values then IN operator is used.
Display all the details of student whose roll no is either 100 or 102 or 103.
Select * from std where roll=100 or roll=102 or roll=103;
Select * from std where roll in(100,102,103);
Display details of amit, sumit and kapil.
Select * from std where name=’amit’ or name=’sumit’ or name=’kapil’;
Select * from std where name in (‘amit’,’sumit’,’kapil’);
NOT IN operator
Logically opposite of IN operator.
Display all the details of student except whose roll no is either 100 or 102 or 103.
Select * from std where not(roll=100 or roll=102 or roll=103);
Select * from std where roll not in(100,102,103);
Display details of all the students except amit, sumit and kapil.
Select * from std where not(name=’amit’ or name=’sumit’ or name=’kapil’);
Select * from std where name not in (‘amit’,’sumit’,’kapil’);
IS NULL operator
Helps us to check whether the value of the column is null or not.
Select * from std where age is null;
IS NOT NULL operator
Logically opposite of IS NULL operator.
Select * from std where age is not null;
