SAMPLE SQL QUERIES

QUERIES

------------------------------------------------------------------------------------------------------------------------------------------------------------------

1..display all group function on sal?
sql>SELECT MAX(SAL),MIN(SAL),SUM(SAL),AVG(SAL),COUNT(SAL)
FROM EMP;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
2..DISPLAY THOSE EMPLOYEE WHOSE SALARY IS EVEN NO?
SQL>SELECT EMPNO,ENAME,SAL
           FROM EMP
WHERE MOD(SAL,2)=0;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
3..DISPLAY THOSE EMPLOYEES WHOSE SALARY KEEPS DECIMAL DIGIT?
SQL>SELECT EMPNO,ENAME,SAL
          FROM EMP
       WHERE SAL LIKE %.%;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
4..DISPLAY THE 3% OF SAL UPTO TWO DECIMAL DIGIT?
SQL>SELECT EMPNO,ENAME,SAL*.03,ROUND(SAL*.03,2)
          FROM EMP;

------------------------------------------------------------------------------------------------------------------------------------------------------------------
5..DISPLAY THE EMPLOYEE WHOSE NAME HAS CHAR 'A'?
SQL>SELECT EMPNO,ENAME
FROM EMP
WHERE ENAME LIKE 'A%';

------------------------------------------------------------------------------------------------------------------------------------------------------------------
6..DISPLAY THE NAME IN UPPERCASE,JOB IN LOWER CASE?
SQL>  SELECT UPPER(ENAME),LOWER(JOB)
FROM EMP;

------------------------------------------------------------------------------------------------------------------------------------------------------------------
7..DISPLAY THE NAME WITH 5 LEADING MINUS SIGN?
SOL..SELECT ENAME,'-----'||ENAME   FROM EMP;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
          

8..DISPLAY THE EMPLOYEE NAME,JOB,ANNUAL SAL,AND
    TOTAL COMPENSATION FOR ALL EMPLOYEES;
SQL>SELECT ENAME,JOB,(SAL+NVL(COMM,0))*12
FROM EMP

------------------------------------------------------------------------------------------------------------------------------------------------------------------
9..DISPLAY THE DIFFERENT TYPE OF JOB IN EMP?
SQL>SELECT DISTINCT JOB
          FROM EMP;

------------------------------------------------------------------------------------------------------------------------------------------------------------------
10..DISPLAY THE EMPLOYEE WHOSE COMM IS MORE THAN 25% OF SALARY?
SQL>SELECT ENAME,SAL
FROM EMP
WHERE COMM>(SAL*.25);

11..DISPLAY THE NAME,JOB,AND MGR FOR EMPLOYEE 
WHOSE SALESMAN OR MANAGER?
SQL>SELECT EMPNO,ENAME,MGR,JOB
FROM EMP
WHERE JOB = 'SALESMAN' OR JOB='MANAGER';
------------------------------------------------------------------------------------------------------------------------------------------------------------------
12..DISPLAY THE NAME,JOB,SAL WITH THE FOLLOWING CONDITION
  A)     WHERE DEPTNO=20 OR DEPTNO=10 AND JOB=MANAGER;
SQL>SELECT ENAME,JOB,SAL,DEPTNO
FROM EMP
WHERE(( DEPTNO=20 OR DEPTNO=10) AND JOB='MANAGER');

B)WHERE DEPTNO=20 OR(DEPTNO=10 AND JOB='MANAGER')?
SQL>SELECT EMPNO,ENAME,JOB,SAL,DEPTNO
FROM EMP
WHERE DEPTNO=20 OR(DEPTNO=10 AND JOB='MANAGER')
ORDER BY DEPTNO;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
C)WHERE (DEPTNO=20 OR DEPTNO=10)AND JOB='MANAGER'?
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO
FROM EMP
WHERE (DEPTNO=20 OR DEPTNO=10)AND JOB='MANAGER'
ORDER BY DEPTNO;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
13>DISPLAY EMPLOYEES WHOSE EMPNO IS GREATER THAN 
7799 AND LESS THEN 7901?
SQL>SELECT EMPNO,ENAME,JOB,SAL,DEPTNO
FROM EMP
WHERE EMPNO>7799 AND EMPNO<7901
------------------------------------------------------------------------------------------------------------------------------------------------------------------
14>DISPLAY THE NAME WHICH START WITH 'J',
ENDS WIH 'S' AND HAVING THE CHAR  'E' IN THE NAME?
SQL>SELECT EMPNO,ENAME
FROM EMP
WHERE ENAME LIKE 'J%E%S';
------------------------------------------------------------------------------------------------------------------------------------------------------------------
15>DISPLAY THE NAME OF 05 CHARS AND ENDS WITH 'S'?
SQL>SELECT EMPNO,ENAME
FROM EMP
WHERE ENAME LIKE '____S';
------------------------------------------------------------------------------------------------------------------------------------------------------------------
16>DISPLAY DEPT WISE TOTAL SALARY

SQL>select deptno,sum(sal) from emp group by deptno;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
17>DISPLAY AVG(SUM(SAL),SUM(AVG(SAL) AND SEE THE DIFFERENCE>
SQL>SELECT AVG(SUM(SAL)),SUM(AVG(SAL))
,AVG(SUM(SAL))-SUM(AVG(SAL))
FROM EMP GROUP BY EMPNO;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
18>DISPLAY THE DEPT WHICH HAVING TWO 
     OR MORE EMPLOYEE OF SAME JOB?

------------------------------------------------------------------------------------------------------------------------------------------------------------------

19>DISPLAY THE EMPLOYEE WHO JOIN THE COMPANY OTHER THAN 
THE YEAR 81?
SQL>SELECT * FROM EMP
WHERE HIREDATE NOT BETWEEN '01-JAN-81' AND '31-DEC-81';

------------------------------------------------------------------------------------------------------------------------------------------------------------------
20>DISPLAY THE EMPNO,ENAME,JOB USING SUB QUERY IN PLACE OF 
        TABLE----INLINE VIEW?
SQL>DOUBT SHOULD BE CLARFIED

------------------------------------------------------------------------------------------------------------------------------------------------------------------
21>DISPLAY THOSE EMPLOYEES WHOSE GRADE IS 3 
AND JOB OTHER THAN MANAGER?
SQL>/SELECT E.EMPNO,E.ENAME,S.GRADE,E.JOB
FROM EMP E,SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
AND S.GRADE=3 AND
E.JOB NOT IN('MANAGER');
------------------------------------------------------------------------------------------------------------------------------------------------------------------
24>DISPLAY THE EMPLOYEES WHO GET MAX(SAL) IN HIS DEPT?
/
SELECT EMPNO,ENAME,SAL,DEPTNO
FROM EMP
WHERE SAL IN(SELECT MAX(SAL) FROM EMP);
------------------------------------------------------------------------------------------------------------------------------------------------------------------
25>DISPLAY THE EMPNO,ENAME,JOB,DEPTNO,DNAME,LOC
       WITH DEPT WHICH AS NO EMPLOYEES?
SQL>
------------------------------------------------------------------------------------------------------------------------------------------------------------------
26>DISPLAY MANAGERS NAMES 
    AND TOTAL SALARY PAID FOR EMPLOYEE UNDER THEM?
SQL>SELECT B.ENAME,SUM(A.SAL)
FROM EMP A,EMP B
WHERE B.EMPNO=A.MGR
GROUP BY B.ENAME;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
27>DISPLAY THE GRADES AND TOTAL SALARY PAID FOR   EACH GRADE?
SQL> SELECT B.GRADE,SUM(A.SAL)
  2  FROM EMP A,SALGRADE B
  3  WHERE A.SAL BETWEEN LOSAL AND HISAL
  4  GROUP BY B.GRADE;
------------------------------------------------------------------------------------------------------------------------------------------------------------------

28>DISPLAY EACH DEPT NAME AND NO OF EMPLOYEES     WORKING IN IT  ?
SQL> SELECT A.DEPTNO,B.DNAME,COUNT(*)
  2  FROM EMP A,DEPT B
  3  WHERE A.DEPTNO=B.DEPTNO
  4  GROUP BY A.DEPTNO,B.DNAME;

------------------------------------------------------------------------------------------------------------------------------------------------------------------
29>DISPLAY EACH JOB CATAGORIES  AND NO OF EMPLOYEES WORKING IN IT?
SQL> SELECT DISTINCT JOB,COUNT(*)
  2  FROM EMP
  3  GROUP BY JOB;

------------------------------------------------------------------------------------------------------------------------------------------------------------------
30>DISPLAY  MANAGER  WHO ARE MANAGER FOR LEAST NO OF  EMPLOYEES?
SQL> SELECT A.ENAME,COUNT(*)
  2  FROM EMP A,EMP B
  3  WHERE A.EMPNO=B.MGR
  4  GROUP BY A.ENAME;

------------------------------------------------------------------------------------------------------------------------------------------------------------------
31>DISPLAY MANAGER WHO ARE MANGER FOR LEAST NO OF EMPLOYEES
SQL>
  1  SELECT A.ENAME,COUNT(*)
  2  FROM EMP A,EMP B
  3  WHERE A.EMPNO=B.MGR
  4  GROUP BY A.ENAME
  5  HAVING COUNT(*)=(SELECT MIN(COUNT(*))
  6                   FROM EMP
  7*                  GROUP BY ENAME);
------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL>
  1  SELECT A.ENAME,COUNT(*)
  2  FROM EMP A,EMP B
  3  WHERE A.EMPNO=B.MGR
  4  GROUP BY A.ENAME
  5* HAVING COUNT(*)<3;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
32>DISPLAY LEAST 3 SALARY PAID EMPLOYEE
SQL>SELECT  ROWNUM,ENAME,SAL
FROM (SELECT ROWNUM,ENAME,SAL
      FROM EMP
      ORDER BY SAL)
GROUP BY ROWNUM,SAL,ENAME
HAVING ROWNUM<=3;

------------------------------------------------------------------------------------------------------------------------------------------------------------------
33>DISPLAY THE DEPT WHERE MINIMUM NO OF EMPLOYEES WORKING?
SQL>SELECT DEPTNO,COUNT(*)
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*)=(SELECT MIN(COUNT(*))
                                     FROM EMP
                                     GROUP BY DEPTNO);

------------------------------------------------------------------------------------------------------------------------------------------------------------------

34>DISPLAY THE ROW B/W 6 th  AND 10th?
SQL>SELECT ROWNUM,EMPNO,ENAME,SAL,JOB
FROM EMP
WHERE ROWNUM<10
MINUS
SELECT ROWNUM,EMPNO,ENAME,SAL,JOB
FROM EMP
WHERE ROWNUM<6;

------------------------------------------------------------------------------------------------------------------------------------------------------------------

35>DISPLAY 9 ROW?
SQL>SELECT ROWNUM,ENAME
FROM EMP
GROUP BY ROWNUM,ENAME
HAVING ROWNUM=9;

------------------------------------------------------------------------------------------------------------------------------------------------------------------

36>DISPLAY THE 4 th ROW AND THE LAST ROW?
SQL>SELECT ROWNUM,ENAME
   FROM EMP 
   GROUP BY ROWNUM,ENAME
   HAVING ROWNUM=4
   UNION 
   SELECT ROWNUM,ENAME
    FROM EMP 
   GROUP BY ROWNUM,ENAME
   HAVING ROWNUM=(SELECT MAX(ROWNUM)FROM EMP);

------------------------------------------------------------------------------------------------------------------------------------------------------------------

37>DISPLAY THE LAST 10 ROWS?
SQL.SELECT ROWNUM,ENAME
FROM EMP 
MINUS
SELECT ROWNUM,ENAME
FROM EMP 
WHERE ROWNUM<=(SELECT MAX(ROWNUM)-10 FROM EMP);

------------------------------------------------------------------------------------------------------------------------------------------------------------------

38>DISPLAY THE LAST 1st 6 ROWS?
SQL>SELECT ROWNUM,ENAME
       FROM EMP
       GROUP BY ROWNUM,ENAME 
     MINUS
         SELECT ROWNUM,ENAME
         FROM EMP 
        GROUP BY ROWNUM,ENAME
         HAVING ROWNUM>=7;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
39>DISPLAY ALTERNATIVE ROWS?
SQL>SELECT ROWNUM,ENAME
          FROM EMP
          GROUP BY ROWNUM,ENAME 
           HAVING MOD(ROWNUM,2)=0
------------------------------------------------------------------------------------------------------------------------------------------------------------------
40>DISPLAY THE EMPLOYEE WHO GET SAME SALARY?
SQL> SELECT * FROM EMP
WHERE SAL IN(SELECT SAL FROM EMP
              GROUP BY SAL
              HAVING COUNT(SAL)>1)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
41>DISPLAY 'A' IF SAL IS MORE THAN COMM  ELSE 'B'?
SQL>SELECT SAL ,COMM,
DECODE(
GREATEST(SAL,NVL(COMM,0)),SAL,'A',COMM,'B') 
FROM EMP;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
42>DISPLAY THE EMPLOYEE WHO WORK WITH BLAKE AND HAVING GRADE>=3?
SQL>SELECT A.ENAME,G.GRADE
        FROM EMP A,EMP B,SALGRADE G
        WHERE B.EMPNO=A.MGR
       AND B.SAL BETWEEN G.LOSAL AND G.HISAL
      AND B.ENAME LIKE 'BLAKE'
      AND G.GRADE>=3;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
43>DISPLAY THOSE EMPLOYEES WHOSE MANAGER GETTING SALARY>=3000?
SQL>SELECT A.ENAME,A.JOB
FROM EMP A,EMP B
WHERE A.MGR=B.EMPNO
AND B.SAL>=3000;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
44>DISPLAY THE JOB WHICH HAVING MORE EMPLOYEES OF GRADE 3?
SQL>SELECT A.JOB,COUNT(*),G.GRADE
   FROM EMP A,SALGRADE G 
   WHERE A.SAL BETWEEN G.LOSAL AND G.HISAL
  GROUP BY A.JOB,G.GRADE
  HAVING COUNT(*)>(SELECT MAX(COUNT(*)) FROM EMP
                   GROUP BY EMPNO)
  AND G.GRADE=3;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
45> DISPLAY THE GRADE WHICH HAVING MORE EMPLOYEES?
SQL>SELECT COUNT(*),G.GRADE
   FROM EMP A,SALGRADE G 
   WHERE A.SAL BETWEEN G.LOSAL AND G.HISAL
  GROUP BY G.GRADE
  HAVING COUNT(*)>(SELECT MAX(COUNT(*)) FROM EMP
                                      GROUP BY JOB)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
46>PRINT WEBNOLOGY AS WEB-NO-LOGY?
SQL>SELECT 'WEBNOLOGY'
      ,DECODE('WEBNOLOGY'
      ,'WEBNOLOGY'
      ,'WEB_NO_LOGY'
      ) FROM DUAL


------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------
1.DISPLAY THOSE EMPLOYEES WHO GET SAME SALARY?
SQL>SELECT A.ENAME,A.SAL
FROM EMP A
WHERE SAL IN(SELECT B.SAL FROM  EMP B
                            GROUP BY B.SAL
HAVING COUNT(*)>1)
------------------------------------------------------------------------------------------------------------------------------------------------------------------


2>DISPLAY LAST 5 RECORDS?
SQL>SELECT ROWNUM,ENAME
  FROM EMP
  MINUS
  SELECT ROWNUM,ENAME
  FROM EMP
  WHERE ROWNUM<=(SELECT MAX(ROWNUM)-5 FROM EMP)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
3>DISPLAY THE ALTERNATIVE RECORDS?
SQL>SELECT ROWNUM,ENAME
FROM EMP
GROUP BY ROWNUM,ENAME
HAVING MOD(ROWNUM,2)=1
------------------------------------------------------------------------------------------------------------------------------------------------------------------

4>DISPLAY THOSE EMPLOYEES WHO ARE NOT MANAGER?
SQL>SELECT ENAME,JOB
FROM EMP
WHERE JOB NOT IN('MANAGER')
------------------------------------------------------------------------------------------------------------------------------------------------------------------
5>DISPLAY THE THIRD HIGEST PAID EMPLOYEE?
SQL>SELECT ROWNUM,ENAME,SAL
          FROM(SELECT ROWNUM,ENAME,SAL FROM EMP
          ORDER BY SAL DESC)
          GROUP BY ROWNUM,ENAME,SAL
           HAVING ROWNUM=3
------------------------------------------------------------------------------------------------------------------------------------------------------------------
6>DISPLAY 7th RECORD?
SOL>SELECT ROWNUM ,EMPNO,ENAME
FROM(SELECT ROWNUM,EMPNO,ENAME
             FROM EMP
             ORDER BY ROWNUM ASC)
GROUP BY ROWNUM,EMPNO,ENAME
HAVING ROWNUM=7
------------------------------------------------------------------------------------------------------------------------------------------------------------------
7>DISPLAY RECORDS B/W 10th TO 12th?
SQL>SELECT ROWNUM ,EMPNO,ENAME
FROM(SELECT ROWNUM,EMPNO,ENAME
             FROM EMP
             ORDER BY ROWNUM ASC)
GROUP BY ROWNUM,EMPNO,ENAME
HAVING ROWNUM BETWEEN 10 AND 12
------------------------------------------------------------------------------------------------------------------------------------------------------------------
8>DISPLAY LAST RECORD?
SQL>SELECT ROWNUM,EMPNO,ENAME
FROM EMP
GROUP BY ROWNUM,EMPNO,ENAME
HAVING ROWNUM=(SELECT MAX(ROWNUM)FROM EMP)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
10>DISPLAY TH E EMPLOYEE WHO GET MAX SAL IN THEIR            DEPT?
SQL>SELECT ENAME,SAL
FROM EMP
WHERE SAL IN(SELECT MAX(SAL) FROM EMP)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
11>DISPLAY THE EMPLOYEE WHO GWT MORE SALARY THAN AVG SALARY IN THEIR DEPT?
SQL>SELECT ENAME,SAL
FROM EMP
WHERE SAL >(SELECT AVG(SAL) FROM EMP)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
12>DISPLAY THE NAME OF MANAGER FOR EACH EMPLOYEE?
SQL>SELECT A.ENAME,B.ENAME
FROM EMP A, EMP B
WHERE A.MGR=B.EMPNO
------------------------------------------------------------------------------------------------------------------------------------------------------------------
13>DISPLAY THE EMPLOYEE WHO JOIN THE DEPT BEFORE THEIR MANAGER?
SQL>SELECT A.ENAME
FROM EMP A, EMP B
WHERE A.MGR=B.EMPNO
AND A.HIREDATE>B.HIREDATE
------------------------------------------------------------------------------------------------------------------------------------------------------------------
14>DISPLAY THE FIRST FIVE HIGHEST PAID EMPLOYEE?
SQL>SELECT ROWNUM ,SAL
FROM(SELECT SAL
FROM EMP
ORDER BY SAL)
GROUP BY ROWNUM,SAL
HAVING ROWNUM<=5
------------------------------------------------------------------------------------------------------------------------------------------------------------------

15>DISPLAY LAST FIVE LEAST PAID EMPLOYEES?


16>PRINT LIKE
                               *
                           *     *
                       *       *        *
                   *      *    *      *  *
SQL>SELECT LPAD('  ',3,'*')
FROM DUAL
UNION
SELECT LPAD('  ',4,'*')
FROM DUAL
UNION
SELECT LPAD('  ',5,'*')
FROM DUAL
UNION
SELECT LPAD('  ',6,'*')
FROM DUAL
------------------------------------------------------------------------------------------------------------------------------------------------------------------
17>PRINT LIKE
         A
         AS
         ASH
         ASHO
         ASHOK
SQL>SELECT LPAD('  ',3,'A')
FROM DUAL
UNION
SELECT LPAD('  ',4,'AS')
FROM DUAL
UNION
SELECT LPAD('  ',5,'ASH')
FROM DUAL
UNION
SELECT LPAD('  ',6,'ASHO')
FROM DUAL
UNION
SELECT LPAD('  ',7,'ASHOK')
FROM DUAL
------------------------------------------------------------------------------------------------------------------------------------------------------------------
18>DISPLAY THE HIEARCHIES OF EMPLOYEES?
SQL>SELECT LPAD(' ',LEVEL*3,'')||ENAME
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO=MGR
------------------------------------------------------------------------------------------------------------------------------------------------------------------
19>DISPLAY THE MANAGER WHO HAS MORE THAN 
   THREE EMPLOYEES UNDER HIM?
SQL>SELECT A.ENAME,COUNT(*)
        FROM EMP A,EMP B
        WHERE A.EMPNO=B.MGR
        GROUP BY A.ENAME
       HAVING COUNT(*)>3
------------------------------------------------------------------------------------------------------------------------------------------------------------------
20>DISPLAY THE EMPLOYEE WHO HAS SAME JOB IN DEPT?

------------------------------------------------------------------------------------------------------------------------------------------------------------------
21>DISPLAY ALL MANAGER NAME?
SQL>SELECT A.ENAME,A.EMPNO,COUNT(*)
       FROM EMP A,EMP B
       WHERE A.EMPNO=B.MGR
       GROUP BY A.ENAME,A.EMPNO
------------------------------------------------------------------------------------------------------------------------------------------------------------------
22>DISPLAY THE MANAGER NAME UNDER WHOM SALESMAN WORKS?
SQL>SELECT B.ENAME,B.DEPTNO,A.JOB,A.ENAME,A.DEPTNO
         FROM EMP A,EMP B
         WHERE A.MGR=B.EMPNO
         AND A.JOB='SALESMAN'
------------------------------------------------------------------------------------------------------------------------------------------------------------------
23>DISPLAY THE EMPLOYEE WHO WORKS IN 
       THEIR MANAGER DEPT?
SQL>SELECT A.ENAME
           FROM EMP A
           WHERE A.DEPTNO=(SELECT B.DEPTNO FROM EMP B
           WHERE A.MGR=B.EMPNO)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
24>DISPLAY THE EMPLOYEE WHO WORKS WITH FORD 
                                       AND GETTING SAME SALARY?
SQL>SELECT A.ENAME
  FROM EMP A,EMP B
   WHERE B.EMPNO=A.MGR
   AND  B.ENAME='FORD'
   AND A.SAL=B.SAL
------------------------------------------------------------------------------------------------------------------------------------------------------------------
26>DISPLAY THE EMPLOYEE WHO WORKS IN DEPT=20
               AND JOB LIKE SALES DEPT?

SQL>SELECT ENAME,DEPTNO,JOB
          FROM EMP
        WHERE DEPTNO=20
        AND JOB='SALESMAN';
------------------------------------------------------------------------------------------------------------------------------------------------------------------
27>DISPLAY THE MANAGER NAME AND THEIR DEPT NAME?

SQL>SELECT A.ENAME,D.DNAME
         FROM EMP A,EMP B,DEPT D
          WHERE A.MGR=B.EMPNO
         AND A.DEPTNO=B.DEPTNO
        GROUP BY A.ENAME,D.DNAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------
28>DISPLAY THE EMPLOYEE NO EVEN/ODD?
SQLSELECT EMPNO FROM EMP
WHERE MOD(EMPNO,2)=1

SQL>SELECT EMPNO FROM EMP
WHERE MOD(EMPNO,2)=O
------------------------------------------------------------------------------------------------------------------------------------------------------------------
29>DISPLAY THE RECORD NO OF EMP/DEPT TOGETHER AS
SQL>SELECT 'DEPT'TABL ,COUNT(*)RECORDNO  
         FROM DEPT
        UNION
            SELECT 'EMP' TABL ,COUNT(*)RECORDNO  
             FROM EMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
30>DISPLAY THE SALARY OF ALL MANAGER?
SQL>SELECT A.ENAME,A.SAL
    FROM EMP A,EMP B    
       WHERE A.MGR=B.EMPNO
------------------------------------------------------------------------------------------------------------------------------------------------------------------
31>DISPLAY THE EMPLOYEE WHO JOINED FIRST HALF OF THE MONTH?
SQL>SELECT ENAME,JOB,HIREDATE
FROM EMP
WHERE SUBSTR(HIREDATE,1,2)BETWEEN 1 AND 15
------------------------------------------------------------------------------------------------------------------------------------------------------------------

32>DISPLAY THE MIDDLE CHAR OF NAME ,
IF NAMES KEEPS ODD NUMBER OF CHARACTERS?
SQL>SELECT ENAME,SUBSTR(ENAME,ROUND(LENGTH(ENAME)/2),1)
FROM EMP

------------------------------------------------------------------------------------------------------------------------------------------------------------------
33>DISPLAY EMPLOYEES WHOSE NAME AND THEIR MANAGER NAME START 

WITH SAME CHARCTER?
SQL>SELECT A.ENAME,B.ENAME
        FROM EMP A, EMP B
         WHERE A.MGR=B.EMPNO
           AND ASCII(A.ENAME)=ASCII(B.ENAME)
------------------------------------------------------------------------------------------------------------------------------------------------------------------



                      CO-RELATED QUERIES
                     =-=-=-=-=-=-=-=-=-=-
------------------------------------------------------------------------------------------------------------------------------------------------------------------

1>DISPLAY THOSE EMPLOYEES WHOSE DEPT IS SAME AS THEIR MANAGER DEPT?
SQL>SELECT A.ENAME,A.DEPTNO
          FROM EMP A
           WHERE A.DEPTNO=(SELECT B.DEPTNO FROM EMP B
                                              WHERE A.MGR=B.EMPNO)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
2>DISPLAY THOSE EMPLOYEES WHO SAL IS GREATER THEN THE AVG SAL OF DEPT?
SQL.SELECT A.ENAME,A. SAL
      FROM EMP A
      WHERE A.SAL>(SELECT AVG(SAL) FROM EMP B WHERE        A.DEPTNO=B.DEPTNO)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
3>DISPLAY THE EMPLOYEE WHO JOINED BEFORE HIS MANAGER?
SQL>SELECT A.ENAME,A.HIREDATE
        FROM EMP A 
         WHERE A.HIREDATE<(SELECT B.HIREDATE FROM EMP  B
        WHERE A.MGR=B.EMPNO)
------------------------------------------------------------------------------------------------------------------------------------------------------------------

4>DISPLAY THOSE EMPLOYEE WHO 
     JOINED IN SAME DATE AS THEIR MANAGER?
SQL>SELECT A.ENAME,A.HIREDATE
        FROM EMP A
          WHERE A.HIREDATE=(SELECT B.HIREDATE FROM EMP  B
                                                  WHERE A.MGR=B.EMPNO)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
5>DISPLAY  THOSE MANAGER WHOSE SALARY IS 
       GREATER THAN THEIR EMPLOYEE?
SQL>SELECT A.ENAME,A.SAL
         FROM EMP A
            WHERE A.SAL>(SELECT B.SAL FROM EMP  B
            WHERE A.MGR=B.EMPNO)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
6>DISPLAY THOSE EMPLOYEES WHOSE JOB='MANAGER' 
       AND DO NOT HAVE SUBORDINATES
SQL>SELECT B.ENAME,COUNT(A.ENAME)
FROM EMP A,EMP B
WHERE A.MGR=B.EMPNO
GROUP BY B.ENAME
HAVING COUNT(A.ENAME)<=0


------------------------------------------------------------------------------------------------------------------------------------------------------------------
7>DISPLAY THOSE EMPLOYEES WHOSE SALARY IS SAME AS THEIR MANAGER
        SALARY
SQL>SELECT A.ENAME,A.SAL
         FROM EMP A
            WHERE A.SAL=(SELECT B.SAL FROM EMP  B
            WHERE A.MGR=B.EMPNO)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
8>DISPLAY MANAGER WHOSE SALARY IS LESS THAN THEIR EMPLOYEES SALARY
SQL> SELECT A.ENAME,A.SAL
         FROM EMP A
            WHERE  A.EMPNO IN(SELECT B.MGR
                                               FROM EMP B
                                            WHERE A.SAL<B.SAL)

------------------------------------------------------------------------------------------------------------------------------------------------------------------


                                       JOINS QURIES
                                      -=-=-=-=-=-=-=-     
------------------------------------------------------------------------------------------------------------------------------------------------------------------
1>DISPLAY MANAGERS AND THEIR SALARY GRADES?
SQL>SELECT A.ENAME,A.SAL,G.GRADE
           FROM EMP A,SALGRADE G
          WHERE A.SAL BETWEEN G.LOSAL AND G.HISAL
------------------------------------------------------------------------------------------------------------------------------------------------------------------
           AND A.JOB='MANAGER'

2>DISPLAY THE EMPLOYEES NAMES AND 
DEPT NAMES BUT SALARY SHOULD BE 2000 AND MORE?
SQL>SELECT A.ENAME,D.DNAME
            FROM EMP A,DEPT D
           WHERE A.DEPTNO=D.DEPTNO
            AND A.SAL >=2000
------------------------------------------------------------------------------------------------------------------------------------------------------------------
3>DISPLAY THE EMPLOYEES NAMES AND 
DEPT NAMES BUT SALARY IS MORE THAN 2000?
SQL>SELECT A.ENAME,D.DNAME
        FROM EMP A,DEPT D
         WHERE A.DEPTNO=D.DEPTNO
         AND A.SAL >2000
------------------------------------------------------------------------------------------------------------------------------------------------------------------

4>DISPLAY EMPLOYEES NAMES WHOSE GRADE IS 3 AND THEIR JOB IS
    CLERCK OR SALESMAN?
SQL>SELECT A.ENAME,G.GRADE,A.JOB
         FROM EMP A,SALGRADE G
         WHERE A.SAL BETWEEN G.LOSAL AND G.HISAL
         AND G.GRADE=3 /
        AND (A.JOB='CLERCK' OR A.JOB='SALESMAN')

------------------------------------------------------------------------------------------------------------------------------------------------------------------
5>DISPALY THE EMPLOYEES NAME AND THEIR SALARY GRADE
SQL>SELECT A.ENAME,G.GRADE,A.JOB
         FROM EMP A,SALGRADE G
------------------------------------------------------------------------------------------------------------------------------------------------------------------
         WHERE A.SAL BETWEEN G.LOSAL AND G.HISAL

6>DISPLAY THE EMPLOYEES NAME 
       AND THEIR MANAGERS NAME?
SQL>SELECT A.ENAME,B.ENAME
         FROM EMP A, EMP B
------------------------------------------------------------------------------------------------------------------------------------------------------------------
         WHERE A.MGR=B.EMPNO

7>DISPLAY THE EMPLOYEE AND MANAGERS NAMES AND       HIREDATES OF THOSE EMPLOYEES WHO JOINED BEFORE        THEIR MANAGER?
SQL>SELECT A.ENAME,B.ENAME,A.HIREDATE
        FROM EMP A, EMP B
       WHERE A.MGR=B.EMPNO
       AND A.HIREDATE<B.HIREDATE

------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        GROUP BY
                      _-_-_-_-_-_-_
------------------------------------------------------------------------------------------------------------------------------------------------------------------
1>DISPLAY ALL THE DEPARTMENT NAMES AND TOTAL      SALARY PAID FOR EACH DEPT?
SQL>SELECT DEPTNO,SAL,COUNT(*)
           FROM EMP
          GROUP BY DEPTNO,SAL 
------------------------------------------------------------------------------------------------------------------------------------------------------------------
2>DISPLAY JOB AND TOTAL SALARY PAID FOR EACH JOB?
SQL>SELECT A.JOB,SUM(B.SAL)
FROM EMP A,EMP B
WHERE A.EMPNO=B.EMPNO
GROUP BY A.JOB
------------------------------------------------------------------------------------------------------------------------------------------------------------------
3>DISPLAY LOCATIONWISE AND JOB WISE TOTAL SALARY?
SQL>SELECT A.JOB,SUM(B.SAL)
FROM EMP A,EMP B
WHERE A.EMPNO=B.EMPNO
GROUP BY A.JOB
UNION
SELECT D.LOC,SUM(A.SAL)
FROM EMP A,DEPT D
WHERE A.DEPTNO=D.DEPTNO
GROUP BY D.LOC
------------------------------------------------------------------------------------------------------------------------------------------------------------------
4>DISPLAY THE NAMES OF MANAGER WITH TOTAL NO EMPLOYEES WORKING UNDER THEM?
SQL>SELECT B.ENAME,COUNT(A.ENAME)
FROM EMP A,EMP B
WHERE A.MGR=B.EMPNO
GROUP BY B.ENAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------

5>DISPLAY GRADES AND NO. OF EMPLOYEES AVAILABLE FOR EACH GRADE?
SQL>SELECT G.GRADE,COUNT(A.ENAME)
FROM EMP A,SALGRADE G
WHERE A.SAL BETWEEN G.LOSAL AND G.HISAL
GROUP BY G.GRADE
------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    

                           HAVING CLAUSE
                        -_-_-_-_-_-_-_-_-_-_
1.DISPLAY MANAGERS WHO ARE HAVING THREE OR MORE 
   SUBORDINATE
SQL>SELECT B.ENAME,COUNT(A.ENAME)
FROM EMP A,EMP B
WHERE A.MGR=B.EMPNO
GROUP BY B.ENAME
HAVING COUNT(A.ENAME)>3
------------------------------------------------------------------------------------------------------------------------------------------------------------------

2.DISPLAY DEPARTMENT WHICH PAYING MORE THAN 10000
    SALARIES PER MONTH  
SQL>DOUBTS
------------------------------------------------------------------------------------------------------------------------------------------------------------------
3>DISPLAY THOSE GRADES FOR WHICH ATLEAST 2      EMPLOYEES ARE AVAILABLE?
SQL>SELECT G.GRADE,COUNT(A.ENAME)
       FROM EMP A,SALGRADE G
        WHERE A.SAL BETWEEN G.LOSAL AND G.HISAL
      GROUP BY G.GRADE
        HAVING COUNT(A.ENAME)>=2

 -----------------------------------------------------------------------------------------------------------------------------------------------------------------
4>DISPLAY THE DEPARTMENT  WHERE MAXIMUM NO OF     EMPLOYEES WORKING?
SQL>SELECT DEPTNO,COUNT(ENAME)
         FROM EMP
         GROUP BY DEPTNO


------------------------------------------------------------------------------------------------------------------------------------------------------------------

               OTHER QUERIES USING(GROUP BY/HAVING)
      ===================================
1>DISPLAY THE MANAGER NAMES AND TOTAL SALARY PAID      FOR EMPLOYEES UNDER THEM?
      SELECT B.ENAME,SUM(A.SAL)
FROM EMP A,EMP B
WHERE A.MGR=B.EMPNO
GROUP BY B.ENAME 
------------------------------------------------------------------------------------------------------------------------------------------------------------------

2>DISPLAY THE GRADES AND TOTAL SALARY PAID FOR EACH GRADE?
SQL>SELECT G.GRADE,SUM(A.SAL)
FROM EMP A,SALGRADE G
WHERE A.SAL BETWEEN G.LOSAL AND G.HISAL
GROUP BY G.GRADE
------------------------------------------------------------------------------------------------------------------------------------------------------------------

3>DISPLAY EACH DEPT NAME AND NO OF EMPLOYEES    WORKING IN IT?
SQL>SELECT D.DNAME,COUNT(A.ENAME)
FROM EMP A ,DEPT D
WHERE A.DEPTNO=D.DEPTNO
GROUP BY D.DNAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------

4>DISPLAY EACH JOB CATAGORY AND NO OF EMPLOYEE    WORKING FOR IT?
SQL>SELECT A.JOB,COUNT(B.ENAME)
FROM EMP A,EMP B
WHERE A.EMPNO=B.EMPNO
GROUP BY A.JOB
------------------------------------------------------------------------------------------------------------------------------------------------------------------
5>DISPLAY MANAGERS WHO ARE MANAGERS FOR LEAST 
NO OF WMPLOYEES
SQL>SELECT B.ENAME,COUNT(A.EMPNO)
FROM EMP A,EMP B
WHERE B.EMPNO=A.MGR
GROUP BY B.ENAME
HAVING COUNT(A.EMPNO)>=(SELECT MAX(COUNT(A.EMPNO))
                                                     FROM EMP
                                                 GROUP BY B.ENAME )
------------------------------------------------------------------------------------------------------------------------------------------------------------------



7.>  DISPLAY THE DEPT WHERE MIN NO OF EMPLOYEES               WORKING?
SQL>SELECT DEPTNO,COUNT(EMPNO)
FROM EMP
GROUP BY DEPTNO          
HAVING COUNT(EMPNO)>=(SELECT MAX(COUNT(EMPNO)) FROM EMP
GROUP BY DEPTNO)
------------------------------------------------------------------------------------------------------------------------------------------------------------------



                         SET OPERATORS                        -------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------
1>  DISPLAY THOSE WHO ARE EMPLOYEES  AS WELL AS   MANAGERS?
SQL>SELECT ENAME,JOB
FROM EMP
INTERSECT 
SELECT ENAME,JOB
FROM EMP WHERE JOB='MANAGER'
------------------------------------------------------------------------------------------------------------------------------------------------------------------
2>DISPLAY THOSE WHO ARE NOT MANAGER?
SQL>SELECT ENAME 
FROM EMP
MINUS
SELECT ENAME FROM EMP
WHERE JOB NOT IN'MANAGER' 

------------------------------------------------------------------------------------------------------------------------------------------------------------------



3>DISPLAY THOSE DEPT WHICH DONOT HAVE EMPLOYEES?
SQL>SELECT DEPTNO FROM DEPT
MINUS 
SELECT D.DEPTNO
FROM EMP A,DEPT D
WHERE A.DEPTNO=D.DEPTNO
------------------------------------------------------------------------------------------------------------------------------------------------------------------


4>3>DISPLAY THOSE DEPT WHICH  HAVE EMPLOYEES?
SQL>SELECT DEPTNO FROM DEPT
INTERSECT 
SELECT D.DEPTNO
FROM EMP A,DEPT D
WHERE A.DEPTNO=D.DEPTNO
------------------------------------------------------------------------------------------------------------------------------------------------------------------

5>FIND NO. OF ALL RECORDS FROM ALL THE TABLE?
SQL>SELECT 'EMP'EMP,COUNT(*)RECORD
FROM EMP
UNION
SELECT 'DEPT'DEPT,COUNT(*)RECORD
FROM DEPT
UNION
SELECT 'SALGRADE'SALGRADE,COUNT(*)RECORD
FROM SALGRADE
------------------------------------------------------------------------------------------------------------------------------------------------------------------

                          FUNCTION-BASED QUERIES------------------------------------------------------------------------------------------------------------------------------------------------------------------
1>DISPLAY THOSE EMPLOYEES WHO JOINED IN THE YEAR     82 AND 83
SQL>SELECT ENAME,HIREDATE,JOB
         FROM EMP
         WHERE (SUBSTR(HIREDATE,8,2)=82 OR          SUBSTR(HIREDATE,8,2)=83)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
2>DISPLAY THOSE EMPLOYEES WHO JOINED IN FIRST HALF OF ANY MONTH?
SQL>SELECT ENAME,HIREDATE,JOB
FROM EMP
WHERE SUBSTR(HIREDATE,1,2) BETWEEN 1 AND 15
------------------------------------------------------------------------------------------------------------------------------------------------------------------
3>DISPLAY THOSE EMPLOYEES WHOSE NAME AND THIER MANAGER NAME START WITH SAME CHARACTER?
SQL>SELECT B.ENAME,A.ENAME
FROM EMP A,EMP B
WHERE A.MGR=B.EMPNO
AND ASCII(B.ENAME)=ASCII(A.ENAME)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
4>DISPLAY THOSE EMPLOYEES WHOSE NAME AND JOB    COLOUMNS HAVING SAME NUMBER OF CHARACTERS?
SQL>SELECT ENAME,JOB FROM EMP
         WHERE LENGTH(ENAME)=LENGTH(JOB)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
          
                              #USING DATE AND AGGREGATE FUNCTIONS------------------------------------------------------------------------------------------------------------------------------------------------------------------
1>DISPLAY THOSE EMPLOYEES WHO JOINED THE COMPANY 
   20 OR MORE BEFORE?
SQL>SELECT EMPNO,ENAME,JOB,HIREDATE
        FROM EMP
    WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE)/12>=20
------------------------------------------------------------------------------------------------------------------------------------------------------------------

2>DISPLAY THE LAST DAY OF FEB 2001?
SQL>SELECT LAST_DAY('01-FEB-01')
FROM DUAL
------------------------------------------------------------------------------------------------------------------------------------------------------------------

3>DISPLAY THE DATE ON FIRST MONDAY OF JAN 2001:
SQL>SELECT NEXT_DAY('31-DEC-00','MON')
FROM DUAL
------------------------------------------------------------------------------------------------------------------------------------------------------------------

4>DISPLAY THE CURRENT TIME?
SQL>SELECT SYSDATE,TO_CHAR(SYSDATE,'HH:MI:SS') AS TIME
FROM DUAL
                                                                                                                                                                                           ------------------------------------------------------------------------------------------------------------------------------------------------------------------

5>DISPLAY HTE CURRENT YEAR?
SQL>SELECT SYSDATE,TO_CHAR(SYSDATE,'FMYYYY')AS YEAR 
FROM DUAL
------------------------------------------------------------------------------------------------------------------------------------------------------------------

7>DISPLAY THE NET SALARY PAID TO EACH EMPLOYEE?
SQL>SELECT ENAME,SAL+NVL(COMM,0) 
FROM EMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
8>SHOW 10% INCENTIVES FOR EACH EMPLOYEE ONSALARY OR COMM WHICH EVER IS GREATER?
SQL>SELECT ENAME,SAL,COMM,GREATEST(SAL,NVL(COMM,0))*.01
FROM EMP
GROUP BY ENAME,SAL,COMM
------------------------------------------------------------------------------------------------------------------------------------------------------------------
9>SHOW THE CURRENT USER NAME?
SQL>SELECT USER FROM DUAL
------------------------------------------------------------------------------------------------------------------------------------------------------------------
10>DISPLAY THE 3rd HIGHEST PAID EMPLOYEE?
SQL>SELECT ROWNUM,ENAME,SAL
FROM(SELECT DISTINCT SAL,ENAME FROM EMP
                ORDER BY SAL DESC)
GROUP BY ROWNUM,ENAME,SAL
HAVING ROWNUM=3
------------------------------------------------------------------------------------------------------------------------------------------------------------------
11>DISPLAY THE MANAGER UNDER WHICH 5 PERSON ARE WORKING WHO GETTING SALARY>=5000?
SQL>SELECT B.ENAME,COUNT(A.ENAME),B.SAL
FROM EMP A,EMP B
WHERE A.MGR=B.EMPNO
AND B.SAL>=5000
GROUP BY B.ENAME,B.SAL
HAVING COUNT(*)<=5
------------------------------------------------------------------------------------------------------------------------------------------------------------------
12>DISPLAY THOSE EMPLOYEE WHO HAVE JOINED THE COMPANY IN THE MONTH OF JAN?
SQL>SELECT ENAME,HIREDATE
FROM EMP
WHERE SUBSTR(HIREDATE,4,3)='JAN'
------------------------------------------------------------------------------------------------------------------------------------------------------------------
13>DISPLAY THE MANAGERS HAVING MORE THAN 3      EMPLOYEES UNDER THEM?
SQL>SELECT B.ENAME,COUNT(A.ENAME)
FROM EMP A,EMP B
WHERE A.MGR=B.EMPNO
GROUP BY B.ENAME
HAVING COUNT(A.ENAME)>=3
------------------------------------------------------------------------------------------------------------------------------------------------------------------
14>DISPLAY DEPARTMENT LOCATION WITHOT ANY SPACE?
SQL>SELECT REPLACE(LOC,' ','')
FROM DEPT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
15>DISPLAY THOSE EMPLOYEES WHO ARE WORKING UNDER THOSE MANAGER WHO ARE IN SALES DEPARTMENT?
SQL>SELECT A.ENAME,B.ENAME,D.DNAME
FROM EMP A,EMP B,DEPT D
WHERE A.MGR=B.EMPNO
AND B.DEPTNO=D.DEPTNO
AND D.DNAME='SALES'
------------------------------------------------------------------------------------------------------------------------------------------------------------------

                   #SUB QURIES                    =-=-=-=-=-=-=-
------------------------------------------------------------------------------------------------------------------------------------------------------------------
1>DISPLAY THOSE EMPLOYEES WHOSE DEPT IS SALES?
SQL>SELECT A.ENAME,D.DNAME
FROM EMP A,DEPT D
WHERE A.DEPTNO=D.DEPTNO
AND D.DNAME='SALES'
------------------------------------------------------------------------------------------------------------------------------------------------------------------
2>DISPLAY THOSE EMPLOYEE WHOSE GRADE IS 3?
SQL>SELECT A.ENAME,G.GRADE
FROM EMP A,SALGRADE G
WHERE A.SAL BETWEEN G.LOSAL AND G.HISAL
AND G.GRADE=3
------------------------------------------------------------------------------------------------------------------------------------------------------------------
3>DISPLAY THOSE EMPLOYEES WHOSE DEPARTMENT IS SALES OR ACCOUNTING?
SQL>SELECT A.ENAME,D.DNAME
FROM EMP A,DEPT D
WHERE A.DEPTNO=D.DEPTNO
AND (D.DNAME='SALES' OR D.DNAME='ACCOUNTING')
------------------------------------------------------------------------------------------------------------------------------------------------------------------
4>DISPLAY THOSE EMPLOYEES WHO ARE IN BOSTON DEPT
SQL>SELECT  A.ENAME,D.LOC
FROM EMP A,DEPT D
WHERE A.DEPTNO=D.DEPTNO
AND D.LOC='BOSTON'
------------------------------------------------------------------------------------------------------------------------------------------------------------------
5>DISPLAY THOSE EMPLOYEES WHOSE NAME START WITH      'J' AND HIS DEPT ENDS WITH 'S'?
SQL>SELECT A.ENAME,D.DNAME
        FROM EMP A, DEPT D
            WHERE A.DEPTNO=D.DEPTNO
         AND (ENAME LIKE 'J%'    AND
           D.DNAME LIKE '%S')
------------------------------------------------------------------------------------------------------------------------------------------------------------------
6>DISPLAY THOSE EMPLOYEES WHO ARE WORKING WNDER KING?
SQL>SELECT A.ENAME,B.ENAME
FROM EMP A,EMP B
WHERE A.MGR=B.EMPNO
AND B.ENAME='KING'

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
7>DISPLAY THOSE EMPLOYEES WHO ARE WORKING INDER 
     JONES AND CLARK?
SQL>SELECT A.ENAME,B.ENAME
FROM EMP A,EMP B
WHERE A.MGR=B.EMPNO
AND (B.ENAME='JONES' OR B.ENAME='CLARK')
------------------------------------------------------------------------------------------------------------------------------------------------------------------
8>DISPLAY THOSE EMPLOYEES WHOSE MANAGER JOINED IN THE YEAR ie 81
SQL>SELECT A.ENAME,B.ENAME,B.HIREDATE
FROM EMP A,EMP B
WHERE A.MGR=B.EMPNO
AND  SUBSTR(B.HIREDATE,8,2)=81
/

------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hierarchical queries

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

1>DISPLAY ALL EMPLOYEES IN TREE WALK STYLE?
SQL>SELECT LPAD(' ',LEVEL*3,' ')||ENAME
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO=MGR
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
2>DISPLAY THE EMPLOYEE IN HIEARCHY STARTING FROM        JONES?
SQL>SELECT LPAD(' ',LEVEL*3,' ')||ENAME
FROM EMP
START WITH ENAME='JONES'
CONNECT BY PRIOR EMPNO=MGR

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
3>DISPLAY THE TOTAL NO. OF EMPLOYEES WORKING IN COMPLETE HIERARCHY  UNDER THE  JONES?
SQL>

------------------------------------------------------------------------------------------------------------------------------------------------------------------
4>DISPLAY LAST LEVEL EMPLOYEE?
SQL>SELECT LEVEL,ENAME
FROM EMP
WHERE LEVEL=(SELECT MAX(LEVEL) FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO=MGR)
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO=MGR
------------------------------------------------------------------------------------------------------------------------------------------------------------------
5>DISPLAY THOSE EMPLOYEES WHOSE LEVEL HIEARACHY IS      ODD?
SQL>SELECT LEVEL,ENAME
FROM EMP
WHERE MOD(LEVEL,2)=1
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO=MGR
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
6>DISPLAY THE 2nd LEVEL EMPLOYEE
SQL>SELECT LEVEL,ENAME,JOB,SAL
FROM EMP
WHERE LEVEL=2
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO=MGR
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

7>DISPLAY TOTAL SALARY PAID UNDER BLAKE?
SQL>SELECT B.ENAME,SUM(A.SAL)
FROM EMP A, EMP B
WHERE A.MGR=B.EMPNO
GROUP BY B.ENAME
HAVING B.ENAME=(SELECT B.ENAME
                            FROM EMP B
                           WHERE ENAME='BLAKE')
------------------------------------------------------------------------------------------------------------------------------------------------------------------
8>DISPLAY HOW MANY NO OF EMPLOYEES ARE PRESENT AT
LEVEL NO OF 3?
SQL>SELECT LEVEL,  COUNT(*)
FROM EMP
WHERE LEVEL=3
GROUP BY LEVEL
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO=MGR
--------------------------------------------------------------------------------