Subscribe:

Sunday, October 2, 2011

Logical operators


Logical operators in SQL


And
Used to define conditions that must all be true for a row to be retrieved.
OR
Used to define two conditions of which only one must be true for a row to be retrieved.
NOT
You can use the NOT operator to invert the result of single condition in the where clause.



Examples.
AND OPERATOR
Suppose you want to display the employee number and name and salary of all employees
Who earn equal and more than 800$ and who are in department 20. Then you will write the next statement
Select empno,ename,deptno,sal
 From emp
 Where sal >=800
 And deptno=20;

 EMPNO ENAME          DEPTNO        SAL
------ ---------- ---------- ----------
  7369 SMITH              20                 800
  7566 JONES              20                 2975
  7788 SCOTT              20                 3000
  7876 ADAMS           20                  1100
  7902 FORD               20                  3000
OR Operator
Assume that  want to retrieve information about employees who earn a salary greater than or equal to $1250 or who have the job title SALESMAN
Then you can combine these conditions in a where clause by using the or operator.
SELECT ENAME,JOB,SAL
FROM EMP
WHERE SAL >= 1250
OR JOB =’ SALESMAN’;
ENAME      JOB                    SAL
---------- --------- ----------
ALLEN          SALESMAN          1600
WARD          SALESMAN           1250
JONES          MANAGER            2975
MARTIN       SALESMAN          1250
BLAKE          MANAGER           2850
CLARK         MANAGER            2450
SCOTT         ANALYST             3000
KING            PRESIDENT           5000
TURNER      SALESMAN           1500
FORD          ANALYST              3000
MILLER        CLERK                  1300

Notice that you can use the OR logical operator in combination with any comparison operator to specify conditions in a where clause.
NOT operator
You can use the NOT logical operator in combination with the comparison operators IN, BETWWEN, LIKE, AND IS NULL.
To retrieve a list of employees who are not salesman, president, clerk
You can use the not operator IN combination with the in operator.
 SQL> SELECT EMPNO,ENAME,JOB,SAL
   FROM EMP
    WHERE JOB NOT IN ('SALESMAN', 'PRESIDENT', 'CLERK');

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
    7566 JONES     MANAGER           2975
    7698 BLAKE    MANAGER           2850
    7782 CLARK   MANAGER           2450
    7788 SCOTT   ANALYST            3000
    7902 FORD     ANALYST            3000
You place NOT directly before the IN,BETWEEN,and LIKE operators in a sql statement.
However with the IS NULL operator ,you place the not keyword in the middle to produce the operator IS NOT NULL.
EXAMPL
SELECT ENAME,SAL,JOB
FROM EMP
WHERE  JOB NOT LIKE 'SALESMAN';
ENAME             SAL JOB
---------- ---------- ---------
SMITH             800 CLERK
JONES            2975 MANAGER
BLAKE            2850 MANAGER
CLARK            2450 MANAGER
SCOTT            3000 ANALYST
KING             5000 PRESIDENT
ADAMS            1100 CLERK
JAMES             950 CLERK
FORD             3000 ANALYST
MILLER           1300 CLERK
EXAMPL: for NOT operator with NULL VALUES.
SELECT ENAME,MGR
FROM EMP
WHERE MGR IS NOT NULL;
ENAME             MGR
---------- ----------
SMITH            7902
ALLEN            7698
WARD             7698
JONES            7839
MARTIN           7698
BLAKE             7839
CLARK             7839
SCOTT              7566
TURNER           7698
ADAMS            7788
JAMES              7698

ENAME             MGR
---------- ----------
FORD               7566
MILLER           7782

13 rows selected.

No comments:

Post a Comment