add

About Me

My photo
Oracle Apps - Techno Functional consultant

Wednesday, October 23

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;



No comments: