Basic SQL queries in Oracle

--------------- Where Clause ----------------

SELECT ename,sal,job FROM emp WHERE job = 'MANAGER';

ptno SELECT ename, hiredate,deptno,sal FROM emp WHERE de= 10;

SELECT empno,ename,sal FROM emp WHERE sal >= 3000;

SELECT ename||'joined on '||hiredate FROM emp WHERE hiredate = '17-NOV-81';

SELECT ename||' Works in department '||deptno FROM emp WHERE deptno <> 20;

SELECT ename,sal,deptno,job FROM emp WHERE job <> 'CLERK';

SELECT ename,sal,sal*12 FROM emp WHERE sal*12 = 60000;

---------------- logical Operators ----------

---------------- And Operator ---------------

SELECT ename, sal, deptno, job FROM emp WHERE deptno = 20 AND job = 'MANAGER';

SELECT empno, ename, job, sal FROM emp WHERE sal >= 1100 AND job = 'CLERK';

SELECT empno, ename, job, sal FROM emp WHERE deptno = 20 AND job = 'CLERK';

SELECT empno, ename, job, sal FROM emp WHERE sal >= 1500 AND SAL > 5000;

SELECT empno, ename, job, sal FROM emp WHERE (sal >= 1500 AND SAL <= 5000) AND job = 'MANAGER';

---------------- Or Operator ---------------

SELECT ename, sal, deptno, job FROM emp WHERE deptno = 20 OR job = 'MANAGER';

SELECT empno, ename, job, sal FROM emp WHERE sal >= 1100 OR job = 'CLERK';

SELECT empno, ename, job, sal FROM emp WHERE deptno = 20 OR job = 'MANAGER';

SELECT empno, ename, job, sal FROM emp WHERE sal >= 1500 OR SAL >= 5000;

SELECT empno, ename, job, sal FROM emp WHERE (sal >= 2500 OR SAL <= 5000) OR job = 'MANAGER';

------------- Not Operator --------------

SELECT ename, sal, deptno, job FROM emp WHERE NOT job = 'MANAGER';

SELECT empno, ename, job, sal FROM emp WHERE NOT sal > 5000;

SELECT ename,sal,job FROM emp WHERE NOT sal<5000;

SELECT ename,sal,hiredate FROM emp WHERE NOT hiredate='20-FEB-81';

SELECT ename,sal,job,deptno FROM emp WHERE NOT job='SALESMAN' AND deptno =30;

------------------AND and OR Operators-----------------------

SELECT empno,ename,job,sal FROM emp WHERE (sal>1100 OR job='CLERK') AND deptno=20;

SELECT empno,ename,job,sal FROM emp WHERE (deptno=10 AND job='MANAGER') OR sal>=3000;

SELECT empno,ename,job,sal FROM emp WHERE (deptno=10 AND job='MANAGER') OR (deptno=20 and sal>=3000);

-------Somethings to Note----------------------

SELECT ename,sal,job FROM emp WHERE job>'MANAGER';

SELECT ename,sal,job FROM emp WHERE job<'MANAGER';

SELECT ename,sal,hiredate FROM emp WHERE hiredate>'20-FEB-1981';

SELECT ename,sal,hiredate FROM emp WHERE hiredate<'20-FEB-1981';

SELECT ename,sal,hiredate FROM emp WHERE hiredate<>'20-FEB-1981';

SELECT ename,sal,comm FROM emp WHERE comm IS NULL;

SELECT ename,sal,comm FROM emp WHERE comm IS  NOT NULL;

SELECT ename,sal,job FROM emp WHERE NOT job>'MANAGER';

SELECT ename,sal,hiredate FROM emp WHERE NOT hiredate='17-DEC-1980';

SELECT ename,sal,hiredate FROM emp WHERE NOT hiredate>'17-DEC-1980';

SELECT ename,sal,hiredate FROM emp WHERE NOT hiredate>'17-DECEMBER-1980';

---Rules of Operator Precedence--------------------------

SELECT ename,sal,job,deptno FROM emp WHERE deptno=10 OR deptno=20 AND job='SALESMAN' and sal>2500 AND sal<1500;

SELECT ename,sal,job,deptno FROM emp WHERE deptno =10 OR (deptno=20 AND job='SALESMAN') AND (sal>2500 OR sal<1500);

----------BETWEEN............AND.........Operator---------------------

SELECT ename,sal,job FROM emp WHERE sal BETWEEN 1000 AND 1500;

SELECT ename,sal,job FROM emp WHERE sal NOT BETWEEN 1000 AND 1500;

SELECT ename,sal,job FROM emp WHERE job BETWEEN 'MANAGER' AND 'SALESMAN';

SELECT ename,sal,job FROM emp WHERE job NOT BETWEEN 'MANAGER' AND 'SALESMAN';

SELECT ename,sal,job,hiredate FROM emp WHERE hiredate BETWEEN '17-FEB-1981' AND '20-JUN-1983';

SELECT ename,sal,job,hiredate FROM emp WHERE hiredate NOT BETWEEN '17-FEB-1981' AND '20-JUN-1983';

-------IN Operator---------------------

SELECT ename,sal,job FROM emp WHERE ename IN('FORD','ALLEN');

SELECT ename,sal,job FROM emp WHERE ename NOT IN('FORD','ALLEN');

SELECT ename,sal,deptno FROM emp WHERE deptno IN (10,30);

SELECT ename,sal,deptno FROM emp WHERE deptno NOT IN (10,30);

SELECT ename,sal,hiredate FROM emp WHERE hiredate IN('20-FEB-1981','09-JUN-1981');

SELECT ename,sal,hiredate FROM emp WHERE hiredate NOT IN('20-FEB-1981','09-JUN-1981');

--------------IS NULL Operator---------------------

SELECT ename,deptno,comm FROM emp WHERE comm IS NULL;

SELECT ename,deptno,comm FROM emp WHERE comm IS NOT NULL;

SELECT ename,deptno,comm,MGR FROM emp WHERE MGR IS NOT NULL;

--------------LIKE Operator-----------------------------

SELECT ename,job FROM emp WHERE ename LIKE'S%';

SELECT ename,job FROM emp WHERE ename NOT LIKE'S%';

SELECT ename,job FROM emp WHERE ename LIKE'_A%';

SELECT ename,job FROM emp WHERE ename NOT LIKE'_A%';

SELECT ename,sal FROM emp WHERE ename ='SM%';

SELECT ename,sal FROM emp WHERE 'SM%' LIKE ename;

SELECT ename,hiredate FROM emp WHERE hiredate LIKE '%-FEB-1981';

SELECT ename,hiredate FROM emp WHERE hiredate LIKE '03-%-1981';

SELECT * FROM emp WHERE dname LIKE '%\_%'ESCAPE'\';

--------ORDER BY Information-------------------------

SELECT ename,job,deptno,hiredate FROM emp ORDER BY hiredate;

SELECT ename,job,deptno,hiredate FROM emp ORDER BY hiredate DESC;

SELECT ename,job,sal FROM emp WHERE job='MANAGER' ORDER BY sal;

SELECT ename,job,sal FROM emp WHERE sal>=2500 ORDER BY job,ename DESC;

SELECT empno,ename,sal,sal*12 Annsal FROM emp ORDER BY Annsal;

SELECT empno,ename,sal FROM emp ORDER BY deptno,sal,hiredate;

SELECT empno,ename,sal FROM emp WHERE sal>=2000 ORDER BY hiredate,sal DESC;