KEYS:

 

1.      SELECT DISTINCT(A.ENAME) FROM EMP A, EMP B WHERE A.EMPNO = B.MGR;   or  SELECT ENAME FROM EMP WHERE EMPNO IN (SELECT MGR FROM EMP);

2.      SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(EMPNO)>10 AND DEPTNO=10);

3.      SELECT A.ENAME “EMPLOYEE”, B.ENAME “REPORTS TO” FROM EMP A, EMP B WHERE A.MGR=B.EMPNO;

4.      SELECT * FROM EMP WHERE EMPNO IN ( SELECT EMPNO FROM EMP MINUS SELECT MGR FROM EMP);

5.      SELECT * FROM EMP WHERE SAL > ( SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO=20);

6.      SELECT * FROM EMP WHERE SAL > ( SELECT MAX(SAL) FROM EMP GROUP BY JOB HAVING JOB = ‘MANAGER’ );

7.      SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB;

8.      SELECT * FROM EMP WHERE (DEPTNO, HIREDATE) IN (SELECT DEPTNO, MAX(HIREDATE) FROM EMP GROUP BY DEPTNO);

9.      SELECT TO_CHAR(HIREDATE,’YYYY’) “YEAR”, COUNT(EMPNO) “NO. OF EMPLOYEES” FROM EMP GROUP BY TO_CHAR(HIREDATE,’YYYY’) HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO)) FROM EMP GROUP BY TO_CHAR(HIREDATE,’YYYY’));

10.  SELECT DEPTNO, LPAD(SUM(12*(SAL+NVL(COMM,0))),15) “COMPENSATION” FROM EMP GROUP BY DEPTNO HAVING SUM( 12*(SAL+NVL(COMM,0))) = (SELECT MAX(SUM(12*(SAL+NVL(COMM,0)))) FROM EMP GROUP BY DEPTNO);

11.  SELECT ENAME, HIREDATE, LPAD(‘*’,8) “RECENTLY HIRED” FROM EMP WHERE HIREDATE = (SELECT MAX(HIREDATE) FROM EMP) UNION SELECT ENAME NAME, HIREDATE, LPAD(‘ ‘,15) “RECENTLY HIRED” FROM EMP WHERE HIREDATE != (SELECT MAX(HIREDATE) FROM EMP);

12.  SELECT ENAME,SAL FROM EMP E WHERE SAL > (SELECT AVG(SAL) FROM EMP F WHERE E.DEPTNO = F.DEPTNO);

13.  SELECT ENAME, SAL FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT(SAL)) FROM EMP B WHERE A.SAL<=B.SAL);

14.  SELECT * FROM EMP A WHERE A.EMPNO IN (SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING COUNT(EMPNO)>1) AND A.ROWID!=MIN (ROWID));

15.  SELECT ENAME “EMPLOYEE”,TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))||’ YEARS ‘|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN (SYSDATE, HIREDATE),12)))||’ MONTHS ‘ “LENGTH OF SERVICE” FROM EMP;

Advertisements