IMPORTANT SQL QUERIES

*select * from emp order by deptno asc,job desc;

*select unique job from emp order by job desc;

*select distinct job from emp order by job desc;

*select * FROM emp where job='MANAGER';

*SELECT * FROM EMP WHERE HIREDATE < TO_DATE('01-01-1981','DD-MM-YYYY');

*select * from emp where TO_CHAR(HIREDATE,'YYYY')<'1981';

*SELECT * FROM EMP WHERE HIREDATE <('01-JAN-1981');

*SELECT EMPNO,ENAME,SAL,SAL/30 DAILY_SALARY,SAL*12 ANNUAL_SALARY FROM EMP ORDER BY ANNUAL_SALARY ASC;

*SELECT EMPNO,ENAME,JOB,HIREDATE,SYSDATE,(SYSDATE-HIREDATE) EXPERIENCE_DAYS FROM EMP WHERE JOB = 'MANAGER';

*SELECT EMPNO,ENAME,JOB,HIREDATE,SYSDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE) EXPERIENCE_MONTHS FROM EMP WHERE JOB = 'MANAGER';

*SELECT EMPNO,ENAME,SAL,MONTHS_BETWEEN(SYSDATE,HIREDATE) EXPERIENCE_OF_EMPLOYEES FROM EMP WHERE MGR = 7839;

*SELECT * FROM EMP;

*SELECT * FROM EMP WHERE COMM<SAL;

*SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')>'1981' ORDER BY JOB ASC;

*SELECT EMPNO,ENAME,JOB,MONTHS_BETWEEN(SYSDATE,HIREDATE) EXPERIENCE,SAL/30 DAILY_SALARY FROM EMP WHERE (SAL/30)>100;

*SELECT * FROM EMP WHERE JOB IN(SELECT JOB FROM EMP WHERE ENAME='SMITH' OR ENAME='ALLEN');

*SELECT * FROM EMP WHERE JOB IN(SELECT JOB FROM EMP WHERE ENAME IN('SMITH','ALLEN'));

*SELECT JOB FROM EMP WHERE DEPTNO=10 AND JOB NOT IN(SELECT JOB FROM EMP WHERE DEPTNO=20);

*SELECT * FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP);

*SELECT MAX(SAL) FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND D.DNAME='SALES';

*SELECT * FROM DEPT;

*SELECT * FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND D.DNAME='SALES');

*SELECT * FROM EMP E WHERE HIREDATE < (SELECT MAX(HIREDATE) FROM EMP WHERE MGR IN (SELECT EMPNO FROM EMP WHERE ENAME ='KING'))
AND MGR = (SELECT EMPNO FROM EMP WHERE ENAME ='KING');

*select * from emp where hiredate < (select max(hiredate) from emp where mgr
in
(select empno from emp where ename = 'KING')) ;

*SELECT * FROM EMP WHERE HIREDATE IN ( SELECT MIN(HIREDATE) FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')='1981');

*SELECT MIN(HIREDATE) FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')='1981';

*SELECT * FROM EMP WHERE JOB IN(SELECT JOB FROM EMP WHERE HIREDATE IN(SELECT MIN(HIREDATE) FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')='1981'));

*select * from emp;

*select avg(sal) from emp where job ='CLERK';

*SELECT * FROM EMP WHERE DEPTNO=20 AND SAL>(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=10);

*SELECT * FROM DEPT;

*SELECT DEPTNO,JOB,COUNT(*) FROM EMP GROUP BY DEPTNO,JOB;

*List the manage rno and the number of employees working for those mgrs in the
ascending Mgrno

*SELECT E.MGR ,COUNT(*) FROM EMP E,EMP M
WHERE E.MGR = M.EMPNO
GROUP BY E.MGR
ORDER BY E.MGR ASC;

*select * from emp;

*SELECT * FROM DEPT;

*select w.mgr ,count(*) from emp w,emp m
 where w.mgr = m.empno
group by w.mgr
order by w.mgr asc;

*select deptno,count(*) from emp group by deptno having count(*)>=2;

*select * from dept where deptno in
 (select deptno from emp group by deptno
 having count(*) in
  (select max(count(*)) from emp group by deptno) );

 *select d.deptno,d.dname,d.loc,count(*) from emp e ,dept d
 where e.deptno = d.deptno group by d.deptno,d.dname,d.loc
 having count(*) = (select max(count(*) ) from emp group by deptno);

 *select * from emp where mgr IN
 (select empno from emp where ename = 'JONES');

* SELECT * FROM EMP WHERE (SAL*(120/100))>3000;

*SELECT * FROM EMP WHERE (1.2*SAL) > 3000 ;

*SELECT E.*,D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO;

*select * from emp where deptno not in
 (select D.DEPTNO from emp E,DEPT D where E.DEPTNO=D.DEPTNO AND DNAME = 'SALES');

* SELECT ENAME,DEPTNO,SAL,COMM FROM EMP WHERE SAL BETWEEN 2000 AND 5000 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC='CHICAGO');

*SELECT * FROM EMP W,EMP M WHERE W.MGR=M.EMPNO AND W.SAL>M.SAL;

* SELECT E.ENAME,E.JOB,D.DNAME,D.LOC FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND JOB='MANAGER';

* select w.empno,w.ename,w.job,w.mgr,w.hiredate,w.sal,w.deptno,m.ename
from emp w ,emp m
where w.mgr = m.empno and m.ename = 'JONES';

*SELECT DNAME,ENAME,JOB,SAL FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO ORDER BY D.DNAME;

*SELECT ENAME,JOB,SAL,DNAME FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND JOB<>'CLERK' ORDER BY SAL DESC;

*SELECT * FROM EMP WHERE MGR IS NULL;

* select e.ename,e.job from emp e where mgr is null;

* List the names of the emps who are getting the highest sal dept wise?

 *SELECT ENAME,DEPTNO FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

*select * from emp where sal =(select (max(sal)+min(sal))/2 from emp);

*select * from emp;

*List the no. of emps in each department where the no. is more than 4?

*select deptno,count(*) from emp group by deptno having count(*) > 4;

*List the names of depts. Where atleast 3 are working in that department?

*select dname,count(*) from dept d, emp e where e.deptno=d.deptno group by dname having count(*) >= 3;

 *select * from emp m where m.empno in (select mgr from emp)
 and m.sal > (select avg(e.sal) from emp e where e.mgr = m.empno );