PREDICATES OR SPECIAL OPERATORS
DEPT TABLE
EMP TABLE
Every operator that is used within the conditions in " where clause" is called as predicates.
Tsql provides special predicated to simplify the conditions in where clause which are collectively called as “predicates”
a. Between
b. Not between
c. In
d. Not in
e. Is null
f. Is not null
g. Like
h. Not like
Between:
Is used to verify wheather or not the given value is within the specified range.
Find the employee whose salary is in the range of 2000 and 4000
select * from emp where sal between 2000 and 4000
To display the employees who joined in the company in the year 1982
select * from emp where hiredate between '1/1/1982' and '12/31/1982'
Find the employees whose salary is not in the range of 2000 and 4000
select * from emp where sal not between 2000 and 4000
IN OPERATOR
This operator is used to verify whether or not the given value is within a specified list of values
Examples
Find the employees whose salary is either 800 or 950 or 1600
Select * from emp where sal in (800,950,1600)
Find the employees whose job is either clerk or manager
Select * from emp where job not in('clerk','manager')
Display employee list who are not working for 10 or 20 the department.
Select * from emp where deptno not in(10,20)
Find the employees whose salary is not either 800 or 950 or 1600
Select * from emp where sal not in(800,950,1600)
Display employee records earning between 2000 and 5000 and earning multiple of 50 and working as clerk or manager & not deptno in 10 and 20
select * from emp where sal between 2000 and 5000
and sal%50=0
and job in ('clerk','manager')
and deptno not in(10,20)
==================================================
LIKE
It is used to search for a particular pattern within a column
While working with like predicates you have to use two wild card characters
1 %--> it is used for representing multiple characters.
2 _underscore: it is used for representing single character.
Find the employees whose name starts with alphabets s
select * from emp where ename like 's%'
Name ends with s
select * from emp where ename like '%s'
Name starts with s and ends with s
select * from emp where ename like 's%s'
Find the employees whose name contains alphabet a
select * from emp where ename like '%a%'
Find the employees whose name contains alphabet a minimum twice in the name
select * from emp where ename like '%a%a%'
Find the employees whose name contains alphabet L as second letter in the name
select * from emp where ename like '_l%'
Display employee records name starts between a and p
select * from emp where ename like'[a-p]%'
where name doesnot start with a and p
select * from emp where ename not like'[a-p]%'
select * from emp where ename like '[^a-p]%'
Display employee records name starts with a or b or s or t
select * from emp where ename like '[abst]%' Find the employees whose name contains the alphabet I or R OR N as 3 letter in the name
select * from emp where ename like '__[i,r,n]%'
Find the employees whose name contains any alphabet in the range of a to k as 2 letter in the name
select * from emp where ename like '_[a-k]%'
Find the employees whose name does not contain alphabet A as second letter in the name.
select * from emp where ename like '_[^a]%'
Find the employees whose name does not start with alphabet s
select * from emp where ename not like 'S%'
Find the employee whose name contain alphabet L as second letter in the name but not alphabet e as last but one character in the name
select * from emp where ename like '_l%[^e]_'
Isnull
Is used to search for null in a column
Find the employees who are not drawing commissions
select * from emp where COMM is null
Find the employee who have no manager
select * from emp where mgr is null
Find the employees who are drawing commission
select * from emp where comm is not null
No comments:
Post a Comment
Thank you for visiting my blog