------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
