Logical operators

Logical Operators : NOT / AND / OR

=================

SELECT * FROM EMP WHERE DEPTNO = 20 AND SAL <1400;

SELECT * FROM EMP WHERE DEPTNO = 20 AND DEPTNO=10;

SELECT * FROM EMP WHERE DEPTNO = 20 AND DEPTNO!=10;

SELECT * FROM EMP WHERE DEPTNO = 30 AND COMM IS NOT NULL;

SELECT * FROM EMP WHERE SAL>3000 OR COMM IS NOT NULL;

SELECT * FROM EMP WHERE DEPTNO = 10 OR DEPTNO!=30;

SELECT * FROM EMP WHERE SAL=1250 OR COMM IS NOT NULL OR DEPTNO = 10;

SELECT * FROM emp WHERE deptno=10 OR deptno!=10;

select * from emp where job = 'manager';

select * from emp where job = 'MANAGER';

select * from emp where job = 'MANAGER' OR JOB='SALESMAN';

SELECT * FROM EMP WHERE JOB IN ('MANAGER','SALESMAN');

SELECT * FROM emp WHERE job NOT IN('MANAGER','SALESMAN');

SELECT * FROM EMP WHERE JOB =ANY('MANAGER','SALESMAN');

Note : =ANY is same as IN operator.

SELECT * FROM EMP WHERE SAL >ANY(1250,2450,3000);

Note: >ANY means greater than the minimum.

SELECT * FROM EMP WHERE SAL < ANY(1250,2450,3000);

Note : <ANY means less than the maximum value

SELECT * FROM EMP WHERE SAL > ALL(1250,2450, 3000);

Note: >ALL means greater than maximum value

SELECT * FROM emp WHERE sal < ALL(1250, 2450, 3000);

Note: <ALL means less than minimum value

SELECT * FROM emp WHERE sal =ALL(1250, 2450, 3000); -- not possible

SELECT * FROM emp;

SELECT * FROM EMP WHERE JOB NOT IN ('CLERK','SALESMAN','ANALYST');

*Evaluate Non-null expression first

AND - TRUTH TABLE

------------------

first_condtion  operator  second_condition  =  result

TRUE            AND           TRUE           = TRUE   - rows will be displayed

TRUE            AND           FALSE          = FALSE   - rows will not be displayed

FALSE          AND            FALSE         = FALSE

TRUE           AND           NULL           = NULL

FALSE          AND           NULL          = FALSE

NULL           AND           TRUE          = NULL

NULL           AND            FALSE         = FALSE

OR - Truth Table:

first_condtion  operator  second_condition  =  result

TRUE             OR       TRUE               = TRUE

TRUE             OR      FALSE               = TRUE

FALSE            OR      TRUE                = TRUE

FALSE            OR      FALSE                = FALSE

TRUE              OR       NULL               = TRUE

FALSE             OR       NULL                = NULL

Between Operator

================

it is useful to compare the column value in the range.

SELECT ENAME, sal FROM EMP WHERE SAL BETWEEN 2450 AND 3000;

SELECT ENAME, sal FROM EMP WHERE SAL BETWEEN 5000 AND 3000;

SELECT ENAME, sal FROM EMP WHERE SAL NOT BETWEEN 2450 AND 3000;

SELECT empno, ename, hiredate, sal from emp

where ENAME BETWEEN 'A' AND 'K';

SELECT empno, ename, hiredate, sal from emp

where HIREDATE BETWEEN '30-JUN-1980' AND '30-JUN-1981';

LIKE Operator

=============

SELECT * FROM emp WHERE ename='S';

SELECT * FROM emp WHERE ename LIKE 'S%';

-- DT.26-FEB-2014 --

SELECT * FROM emp WHERE ename LIKE '__O%';

Searching the data in table using like operator is known as wildcard search or

character pattern search.

With like operator we can use '_' and '%' for searching.

To match pattern

%(PERCENTILE) -> Represents zero or more characters

_ (UNDERSCORE)-> Represents one (any) character;

SELECT * FROM emp;

SELECT * FROM EMP WHERE ENAME LIKE '%S';

SELECT * FROM EMP WHERE ENAME LIKE '_A%';

SELECT * FROM EMP WHERE ENAME LIKE '%M%';

SELECT * FROM EMP WHERE JOB LIKE 'S%';

SELECT * FROM EMP WHERE JOB = 'S%';

SELECT * FROM emp WHERE ename LIKE '__O%';

SELECT * FROM emp WHERE hiredate LIKE '%82';

SELECT * FROM emp WHERE hiredate LIKE '%JAN%';

--Note: In WHERE clause we shouldn't use column alias

SELECT EMPNO, ENAME, SAL * 12 ANNUAL_SALARY FROM EMP

WHERE ANNUAL_SALARY > 10000; -- NOT ALLOW

SELECT EMPNO, ENAME, SAL * 12 ANNUAL_SALARY FROM EMP

WHERE SAL * 12 > 10000;

NOTE: IN WHERE CLAUSE WE CANNOT USE COLUMN ALIAS, REASON IS WHERE CLAUSE WILL EXECUTE

BEFORE FETCHING THE DATA AND COLUMN ALIAS WILL BE GIVEN AFTER FETCHING THE DATA.

CREATE TABLE WILDCHAR_TEST(TEXT VARCHAR2(20));

INSERT INTO WILDCHAR_TEST VALUES('AB%CD');

INSERT INTO WILDCHAR_TEST VALUES('AB_CD');

INSERT INTO WILDCHAR_TEST VALUES('ABCD');

INSERT INTO WILDCHAR_TEST VALUES('AXYZ');

SELECT * FROM WILDCHAR_TEST;

SELECT * FROM WILDCHAR_TEST WHERE TEXT LIKE '__%__'; -- INVALID

SELECT TEXT FROM WILDCHAR_TEST WHERE TEXT LIKE '%%%'; -- INVALID

SELECT TEXT FROM WILDCHAR_TEST WHERE TEXT LIKE '__'%'__'; -- INVALID

SELECT TEXT FROM WILDCHAR_TEST WHERE TEXT LIKE '%/%%' ESCAPE '/';