SPECIAL OPERATORS IN SQL SERVER
IN
NOT IN
BETWEEN
NOTBETWEEN
LIKE
NOT LIKE
IS NULL
1. IN operator:
It is used for comparing multiple values.
SELECT * FROM EMP WHERE ENAME IN (‘RAM’,’RAJ’)
SELECT * FROM EMP WHERE EMPNO IN (11, 22, 33)
2. NOT IN Operator: It is just opposite to in. it
display all the values in table, other than values specified in list
SELECT
* FROM EMP WHERE JOB NOT IN (‘RAM’,’RAJ’)
Other than ‘RAM’, ‘RAJ’ details will be displayed
3. BETWEEN Operator: it will display the values
specified in the range including the given range. It is also known as INCLUSIVE
OPERATOR.
SELECT *
FROM EMP WHERE SAL BETWEEN 2000 AND 4000
It will display all the employees details who are
getting salary more than or equal to 2000 and less than or equal to 4000.
It includes the range specified, so it is called
inclusive operator.
4. NOT BETWEEN
Operator: It is opposite to ‘between’ operator it will display the values other
than range specified.
SELECT * FROM
EMP WHERE SAL NOT BETWEEN 2000 AND 4000.
Note: NOT BETWEEN operator will not include the range
specified, so it is called exclusive operator.
5. LIKE Operator: It is used to search for pattern in
characters. it associated with two characters
_ (UNDERSCORE): For single character
%(PERCENTILE):
For Multiple characters
[] – Any single character within the specified range.
[^] – Any single character not within the range [^a_f]
[^abcdef]
E.g.: WAQ TO DISPLAY THE NAMES BEGIN WITH ‘S’
SELECT ENAME FROM
EMP WHERE ENAME LIKE ‘S%’
ENAME
SMITH
SCOTT
E.g.: WAQ TO DISPLAY NAMES END WITH ‘S’
SELECT ENAME FROM
EMP WHERE ENAME LIKE ‘%S’
ENAME
JAMES
JONES
E.g.: WAQ TO DISPLAY NAMES CONTAINING ‘A’ AS SECOND
CHARACTER
SELECT ENAME LIKE
‘_A%’
E.g.: WAQ TO DISPLAY NAME CONTAINING 5 CHARACTERS
SELECT ENAME FROM
EMP WHERE ENAME LIKE ’-----‘
OR
SELECT ENAME FROM EMP WHERE LEN (ENAME)=5
6. NOT LIKE Operator: Opposite to like operator.
7. IS NULL Operator: It is used for comparing NULL
values
E.g.: WAQ TO DISPLAY ALL EMPLOYEES WHO ARE WORKING IN
NULL DEPARTMENT
SELECT * FROM EMP WHERE DEPTNO. IS NULL
8. IS NOT NULL: Opposite to IS NULL operator
E.g.: WAQ TO DISPLAY ALL EMPLOYEES WHO ARE NOT WORKING
UNDER NULL DEPARTMENT
SELECT * FROM EMP WHERE DEPTNO. IS NOT NULL
SUB-QUERIES
A Sub Query is also a query, which is defined
under a main query .It always sends values to its nearest main query. Server
first sub query first based on the sub query value main query will get
executed.
If a sub query send
single value to its nearest main query then that sub query is called
Single-Valued-Sub query
If a sub query sends
multiple values to its nearest main query then that sub query is called
Multi-Valued-Sub query
Note: If a sub query
sends multiple values to its nearest main query then we have to use IN operator
between Main query and Sub query.
EMPNO ENAME SAL DEPTNO
----------- --------------------
--------------------- -----------
11 RAM 15000.0000 10
22 RAJ 20000.0000 20
33 ABI 10000.0000 10
44 DP 5000.0000 20
55 NAAG 5000.0000 10
Syntax:
SELECT * FROM TABLENAME
WHERE COLUMNNAME =
(SELECT STATEMENT WHERE CONDITION)
E.g.: WAQ To display
employee details, who are working under RAM dept.
SELECT * FROM EMP
WHERE DEPTNO= (SELECT
DEPTNO FROM EMP WHERE ENAME=’RAM’)
E.g.: WAQ To display
employee details, whose salary is greater than highest salary of 10th
department
SELECT * FROM EMP
WHERE SAL> (SELECT
MAX (SAL) FROM EMP WHERE DEPTNO=10)
E.g.: WAQ To display
employee details, whose salary is greater than average salary of RAM department
SELECT * FROM EMP
WHERE SAL> (SELECT
AVG (SAL) FROM EMP WHERE DEPTNO=
SELECT DEPTNO FROM EMP WHERE ENAME=’RAM’)
No comments:
Post a Comment