Oracle Logical Operators

The logical operators are typically used to combine conditional statements. Below is the list of logical operators in Oracle.

Oracle Logical Operators Examples

CREATE TABLE EMP

(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2));

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800);

INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600);

INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250);

INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975);

INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250);

INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850);

INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450);

INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000);

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000);

INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500);

INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100);

INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950);

INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000);

INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300);

COMMIT;

SELECT ENAME FROM EMP WHERE SAL BETWEEN 100 AND 1000

SELECT EMPNO, ENAME, JOB, MGR FROM EMP WHERE ENAME IN ('SMITH','ALLEN','WARD');

SELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB LIKE ('CLE%')

SELECT ENAME, SAL FROM EMP WHERE EXISTS (SELECT * FROM EMP WHERE SAL >3000)

SELECT ENAME, SAL FROM EMP WHERE EXISTS (SELECT * FROM EMP WHERE SAL =3333)

SELECT ENAME, SAL FROM EMP WHERE SAL > ALL (SELECT SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000)

SELECT ENAME, SAL FROM EMP WHERE SAL <ALL (SELECT SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000)

SELECT ENAME, SAL FROM EMP WHERE SAL < ALL (SELECT SAL FROM EMP WHERE SAL BETWEEN 900 AND 3000)

SELECT ENAME, SAL FROM EMP WHERE SAL < ANY (SELECT SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000)

All Chapters