SQL> ------------------JOINS---------------------------
SQL> ---IF WE WANT TO GET THE DATA FROM MORE THN ONE TABLE WE USE JOINS
SQL> ---WHEN WE USE JOIN WE MUST PUT JOIN CONDITION
SQL> ---MINIMUM NUMBER OF JOIN CONDITION : N-1 (N NUMBER OF TABLE)
SQL> ---IF WE R NOT USING JOIN CONDITION WE WILL GET CARTESIAN PRODUCT
SQL> ---CARTESIAN PRODUCT : TABLE 1 -5 | TABLE 2-4 --5X4=20
SQL> ---TYPES OF JOIN : 1. ORACLE STND JOIN 2. SQL 1999 STND JOIN
SQL> ---ORACLE STND : 1. EQUI JOIN 2. NON EQUI 3. OUTER 4 SELF
SQL> ---SQL 1999 STND : 1. INNER JOIN 2. LEFT OUTER 3 RIGHT OUTER 4. FULL OUTER
SQL> ---JOIN 5. CROSS JOIN
SQL> --------------ORACLE STND JOIN --------------
SQL> ---1. EQUI JOIN : WHEN TABLE HAVING COMMON COLUMN
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 40
15 rows selected.
SQL> CL SCR
SQL> SELECT EMPNO,ENAME,SAL,DEPT FROM EMP;
SELECT EMPNO,ENAME,SAL,DEPT FROM EMP
*
ERROR at line 1:
ORA-00904: "DEPT": invalid identifier
SQL> SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 800 20
7499 ALLEN 1600 30
7521 WARD 1250 30
7566 JONES 2975 20
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7782 CLARK 2450 10
7788 SCOTT 3000 20
7839 KING 5000 10
7844 TURNER 1500 30
7876 ADAMS 1100 20
7900 JAMES 950 30
7902 FORD 3000 20
7934 MILLER 1300 40
111 AAA_BBB
15 rows selected.
SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> CL SCR
SQL> ---EQUI
SQL> SELECT EMP.ENAME,EMP.SAL,EMP.DEPTNO,DEPT.DNAME,DEPT.LOC FROM EMP,DEPT
2 WHERE EMP.DEPTNO=DEPT.DEPTNO;
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
SMITH 800 20 RESEARCH DALLAS
ALLEN 1600 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
JONES 2975 20 RESEARCH DALLAS
MARTIN 1250 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
CLARK 2450 10 ACCOUNTING NEW YORK
SCOTT 3000 20 RESEARCH DALLAS
KING 5000 10 ACCOUNTING NEW YORK
TURNER 1500 30 SALES CHICAGO
ADAMS 1100 20 RESEARCH DALLAS
JAMES 950 30 SALES CHICAGO
FORD 3000 20 RESEARCH DALLAS
MILLER 1300 40 OPERATIONS BOSTON
14 rows selected.
SQL> SELECT E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO;
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
SMITH 800 20 RESEARCH DALLAS
ALLEN 1600 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
JONES 2975 20 RESEARCH DALLAS
MARTIN 1250 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
CLARK 2450 10 ACCOUNTING NEW YORK
SCOTT 3000 20 RESEARCH DALLAS
KING 5000 10 ACCOUNTING NEW YORK
TURNER 1500 30 SALES CHICAGO
ADAMS 1100 20 RESEARCH DALLAS
JAMES 950 30 SALES CHICAGO
FORD 3000 20 RESEARCH DALLAS
MILLER 1300 40 OPERATIONS BOSTON
14 rows selected.
SQL> CL SCR
SQL> SELECT ENAME,SAL,E.DEPTNO,DNAME,LOC FROM EMP E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO;
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
SMITH 800 20 RESEARCH DALLAS
ALLEN 1600 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
JONES 2975 20 RESEARCH DALLAS
MARTIN 1250 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
CLARK 2450 10 ACCOUNTING NEW YORK
SCOTT 3000 20 RESEARCH DALLAS
KING 5000 10 ACCOUNTING NEW YORK
TURNER 1500 30 SALES CHICAGO
ADAMS 1100 20 RESEARCH DALLAS
JAMES 950 30 SALES CHICAGO
FORD 3000 20 RESEARCH DALLAS
MILLER 1300 40 OPERATIONS BOSTON
14 rows selected.
SQL> CL SCR
SQL> UPDATE EMP SET DEPTNO=20 WHERE DEPTNO=40;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> CL SCR
SQL> SELECT ENAME,SAL,E.DEPTNO,DNAME,LOC FROM EMP E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO;
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
SMITH 800 20 RESEARCH DALLAS
ALLEN 1600 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
JONES 2975 20 RESEARCH DALLAS
MARTIN 1250 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
CLARK 2450 10 ACCOUNTING NEW YORK
SCOTT 3000 20 RESEARCH DALLAS
KING 5000 10 ACCOUNTING NEW YORK
TURNER 1500 30 SALES CHICAGO
ADAMS 1100 20 RESEARCH DALLAS
JAMES 950 30 SALES CHICAGO
FORD 3000 20 RESEARCH DALLAS
MILLER 1300 20 RESEARCH DALLAS
14 rows selected.
SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> ---OUTER JOIN : EQUI JOIN WITH + OPERATOR
SQL> SELECT ENAME,SAL,E.DEPTNO,DNAME,LOC FROM EMP E,DEPT D
2 WHERE E.DEPTNO(+)=D.DEPTNO;
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
SMITH 800 20 RESEARCH DALLAS
ALLEN 1600 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
JONES 2975 20 RESEARCH DALLAS
MARTIN 1250 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
CLARK 2450 10 ACCOUNTING NEW YORK
SCOTT 3000 20 RESEARCH DALLAS
KING 5000 10 ACCOUNTING NEW YORK
TURNER 1500 30 SALES CHICAGO
ADAMS 1100 20 RESEARCH DALLAS
JAMES 950 30 SALES CHICAGO
FORD 3000 20 RESEARCH DALLAS
MILLER 1300 20 RESEARCH DALLAS
OPERATIONS BOSTON
15 rows selected.
SQL> SELECT ENAME,SAL,D.DEPTNO,DNAME,LOC FROM EMP E,DEPT D
2 WHERE E.DEPTNO(+)=D.DEPTNO;
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
SMITH 800 20 RESEARCH DALLAS
ALLEN 1600 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
JONES 2975 20 RESEARCH DALLAS
MARTIN 1250 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
CLARK 2450 10 ACCOUNTING NEW YORK
SCOTT 3000 20 RESEARCH DALLAS
KING 5000 10 ACCOUNTING NEW YORK
TURNER 1500 30 SALES CHICAGO
ADAMS 1100 20 RESEARCH DALLAS
JAMES 950 30 SALES CHICAGO
FORD 3000 20 RESEARCH DALLAS
MILLER 1300 20 RESEARCH DALLAS
40 OPERATIONS BOSTON
15 rows selected.
SQL> SELECT ENAME,SAL,E.DEPTNO,DNAME,LOC FROM EMP E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO(+);
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
KING 5000 10 ACCOUNTING NEW YORK
CLARK 2450 10 ACCOUNTING NEW YORK
MILLER 1300 20 RESEARCH DALLAS
FORD 3000 20 RESEARCH DALLAS
ADAMS 1100 20 RESEARCH DALLAS
SCOTT 3000 20 RESEARCH DALLAS
JONES 2975 20 RESEARCH DALLAS
SMITH 800 20 RESEARCH DALLAS
JAMES 950 30 SALES CHICAGO
TURNER 1500 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
MARTIN 1250 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
ALLEN 1600 30 SALES CHICAGO
AAA_BBB
15 rows selected.
SQL> CL SCR
SQL> ---NON EQUI JOIN
SQL> ---WHEN NO ANY COMMON COLUMN
SQL> CREATE TABLE SALGRADE
2 (GRADE NUMBER(2),
3 LOSAL NUMBER(5),
4 HISAL NUMBER(5))
5 /
Table created.
SQL> INSERT INTO SALGRADE VALUES(1,0,1000);
1 row created.
SQL> INSERT INTO SALGRADE VALUES(2,1001,3000);
1 row created.
SQL> INSERT INTO SALGRADE VALUES(3,3001,5000);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 20
111 AAA_BBB
15 rows selected.
SQL> CL SCR
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 20
111 AAA_BBB
15 rows selected.
SQL> SELECT * FROM SALGRADE;
GRADE LOSAL HISAL
---------- ---------- ----------
1 0 1000
2 1001 3000
3 3001 5000
SQL> ----------NON EQUI JOIN
SQL> SELECT E.ENAME,E.SAL,S.GRADE FROM EMP E,SALGRADE S
2 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
ENAME SAL GRADE
---------- ---------- ----------
SMITH 800 1
JAMES 950 1
ALLEN 1600 2
WARD 1250 2
JONES 2975 2
MARTIN 1250 2
BLAKE 2850 2
CLARK 2450 2
SCOTT 3000 2
TURNER 1500 2
ADAMS 1100 2
FORD 3000 2
MILLER 1300 2
KING 5000 3
14 rows selected.
SQL> CL SCER
SP2-0158: unknown CLEAR option "SCER"
SQL> CL SCR
SQL> ----SELF JOIN : JOIN ON SINGLE TABLE
SQL> SELECT * FORM EMP;
SELECT * FORM EMP
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 20
111 AAA_BBB
15 rows selected.
SQL> SELECT E.ENAME,F.ENAME AS MANAGER FROM EMP E,EMP F
2 WHERE E.MGR=F.EMPNO;
ENAME MANAGER
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
13 rows selected.
SQL> CL SCR
SQL> SELECT ENAME,SAL,E.DEPTNO,DNAME,LOC FROM EMP E,DEPT D
2 ;
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
SMITH 800 20 ACCOUNTING NEW YORK
ALLEN 1600 30 ACCOUNTING NEW YORK
WARD 1250 30 ACCOUNTING NEW YORK
JONES 2975 20 ACCOUNTING NEW YORK
MARTIN 1250 30 ACCOUNTING NEW YORK
BLAKE 2850 30 ACCOUNTING NEW YORK
CLARK 2450 10 ACCOUNTING NEW YORK
SCOTT 3000 20 ACCOUNTING NEW YORK
KING 5000 10 ACCOUNTING NEW YORK
TURNER 1500 30 ACCOUNTING NEW YORK
ADAMS 1100 20 ACCOUNTING NEW YORK
JAMES 950 30 ACCOUNTING NEW YORK
FORD 3000 20 ACCOUNTING NEW YORK
MILLER 1300 20 ACCOUNTING NEW YORK
AAA_BBB ACCOUNTING NEW YORK
SMITH 800 20 RESEARCH DALLAS
ALLEN 1600 30 RESEARCH DALLAS
WARD 1250 30 RESEARCH DALLAS
JONES 2975 20 RESEARCH DALLAS
MARTIN 1250 30 RESEARCH DALLAS
BLAKE 2850 30 RESEARCH DALLAS
CLARK 2450 10 RESEARCH DALLAS
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
SCOTT 3000 20 RESEARCH DALLAS
KING 5000 10 RESEARCH DALLAS
TURNER 1500 30 RESEARCH DALLAS
ADAMS 1100 20 RESEARCH DALLAS
JAMES 950 30 RESEARCH DALLAS
FORD 3000 20 RESEARCH DALLAS
MILLER 1300 20 RESEARCH DALLAS
AAA_BBB RESEARCH DALLAS
SMITH 800 20 SALES CHICAGO
ALLEN 1600 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
JONES 2975 20 SALES CHICAGO
MARTIN 1250 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
CLARK 2450 10 SALES CHICAGO
SCOTT 3000 20 SALES CHICAGO
KING 5000 10 SALES CHICAGO
TURNER 1500 30 SALES CHICAGO
ADAMS 1100 20 SALES CHICAGO
JAMES 950 30 SALES CHICAGO
FORD 3000 20 SALES CHICAGO
MILLER 1300 20 SALES CHICAGO
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
AAA_BBB SALES CHICAGO
SMITH 800 20 OPERATIONS BOSTON
ALLEN 1600 30 OPERATIONS BOSTON
WARD 1250 30 OPERATIONS BOSTON
JONES 2975 20 OPERATIONS BOSTON
MARTIN 1250 30 OPERATIONS BOSTON
BLAKE 2850 30 OPERATIONS BOSTON
CLARK 2450 10 OPERATIONS BOSTON
SCOTT 3000 20 OPERATIONS BOSTON
KING 5000 10 OPERATIONS BOSTON
TURNER 1500 30 OPERATIONS BOSTON
ADAMS 1100 20 OPERATIONS BOSTON
JAMES 950 30 OPERATIONS BOSTON
FORD 3000 20 OPERATIONS BOSTON
MILLER 1300 20 OPERATIONS BOSTON
AAA_BBB OPERATIONS BOSTON
60 rows selected.
SQL> CL SCR
SQL> ---SQL 1999 STND JOIN
SQL> ---INNER JOIN ....
SQL> SELECT E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E INNER JOIN DEPT D
2 ON E.DEPTNO=D.DEPTNO;
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
SMITH 800 20 RESEARCH DALLAS
ALLEN 1600 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
JONES 2975 20 RESEARCH DALLAS
MARTIN 1250 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
CLARK 2450 10 ACCOUNTING NEW YORK
SCOTT 3000 20 RESEARCH DALLAS
KING 5000 10 ACCOUNTING NEW YORK
TURNER 1500 30 SALES CHICAGO
ADAMS 1100 20 RESEARCH DALLAS
JAMES 950 30 SALES CHICAGO
FORD 3000 20 RESEARCH DALLAS
MILLER 1300 20 RESEARCH DALLAS
14 rows selected.
SQL> SELECT E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E LEFT OUTER JOIN DEPT D
2 ON E.DEPTNO=D.DEPTNO;
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
KING 5000 10 ACCOUNTING NEW YORK
CLARK 2450 10 ACCOUNTING NEW YORK
MILLER 1300 20 RESEARCH DALLAS
FORD 3000 20 RESEARCH DALLAS
ADAMS 1100 20 RESEARCH DALLAS
SCOTT 3000 20 RESEARCH DALLAS
JONES 2975 20 RESEARCH DALLAS
SMITH 800 20 RESEARCH DALLAS
JAMES 950 30 SALES CHICAGO
TURNER 1500 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
MARTIN 1250 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
ALLEN 1600 30 SALES CHICAGO
AAA_BBB
15 rows selected.
SQL> SELECT E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E RIGHT OUTER JOIN DEPT D
2 ON E.DEPTNO=D.DEPTNO;
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
SMITH 800 20 RESEARCH DALLAS
ALLEN 1600 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
JONES 2975 20 RESEARCH DALLAS
MARTIN 1250 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
CLARK 2450 10 ACCOUNTING NEW YORK
SCOTT 3000 20 RESEARCH DALLAS
KING 5000 10 ACCOUNTING NEW YORK
TURNER 1500 30 SALES CHICAGO
ADAMS 1100 20 RESEARCH DALLAS
JAMES 950 30 SALES CHICAGO
FORD 3000 20 RESEARCH DALLAS
MILLER 1300 20 RESEARCH DALLAS
OPERATIONS BOSTON
15 rows selected.
SQL> SELECT E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E FULL OUTER JOIN DEPT D
2 ON E.DEPTNO=D.DEPTNO;
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
KING 5000 10 ACCOUNTING NEW YORK
CLARK 2450 10 ACCOUNTING NEW YORK
MILLER 1300 20 RESEARCH DALLAS
FORD 3000 20 RESEARCH DALLAS
ADAMS 1100 20 RESEARCH DALLAS
SCOTT 3000 20 RESEARCH DALLAS
JONES 2975 20 RESEARCH DALLAS
SMITH 800 20 RESEARCH DALLAS
JAMES 950 30 SALES CHICAGO
TURNER 1500 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
MARTIN 1250 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
ALLEN 1600 30 SALES CHICAGO
AAA_BBB
OPERATIONS BOSTON
16 rows selected.
SQL> CL SCR
SQL> SELECT E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E CROSS JOIN DEPT D ;
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
SMITH 800 20 ACCOUNTING NEW YORK
ALLEN 1600 30 ACCOUNTING NEW YORK
WARD 1250 30 ACCOUNTING NEW YORK
JONES 2975 20 ACCOUNTING NEW YORK
MARTIN 1250 30 ACCOUNTING NEW YORK
BLAKE 2850 30 ACCOUNTING NEW YORK
CLARK 2450 10 ACCOUNTING NEW YORK
SCOTT 3000 20 ACCOUNTING NEW YORK
KING 5000 10 ACCOUNTING NEW YORK
TURNER 1500 30 ACCOUNTING NEW YORK
ADAMS 1100 20 ACCOUNTING NEW YORK
JAMES 950 30 ACCOUNTING NEW YORK
FORD 3000 20 ACCOUNTING NEW YORK
MILLER 1300 20 ACCOUNTING NEW YORK
AAA_BBB ACCOUNTING NEW YORK
SMITH 800 20 RESEARCH DALLAS
ALLEN 1600 30 RESEARCH DALLAS
WARD 1250 30 RESEARCH DALLAS
JONES 2975 20 RESEARCH DALLAS
MARTIN 1250 30 RESEARCH DALLAS
BLAKE 2850 30 RESEARCH DALLAS
CLARK 2450 10 RESEARCH DALLAS
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
SCOTT 3000 20 RESEARCH DALLAS
KING 5000 10 RESEARCH DALLAS
TURNER 1500 30 RESEARCH DALLAS
ADAMS 1100 20 RESEARCH DALLAS
JAMES 950 30 RESEARCH DALLAS
FORD 3000 20 RESEARCH DALLAS
MILLER 1300 20 RESEARCH DALLAS
AAA_BBB RESEARCH DALLAS
SMITH 800 20 SALES CHICAGO
ALLEN 1600 30 SALES CHICAGO
WARD 1250 30 SALES CHICAGO
JONES 2975 20 SALES CHICAGO
MARTIN 1250 30 SALES CHICAGO
BLAKE 2850 30 SALES CHICAGO
CLARK 2450 10 SALES CHICAGO
SCOTT 3000 20 SALES CHICAGO
KING 5000 10 SALES CHICAGO
TURNER 1500 30 SALES CHICAGO
ADAMS 1100 20 SALES CHICAGO
JAMES 950 30 SALES CHICAGO
FORD 3000 20 SALES CHICAGO
MILLER 1300 20 SALES CHICAGO
ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
AAA_BBB SALES CHICAGO
SMITH 800 20 OPERATIONS BOSTON
ALLEN 1600 30 OPERATIONS BOSTON
WARD 1250 30 OPERATIONS BOSTON
JONES 2975 20 OPERATIONS BOSTON
MARTIN 1250 30 OPERATIONS BOSTON
BLAKE 2850 30 OPERATIONS BOSTON
CLARK 2450 10 OPERATIONS BOSTON
SCOTT 3000 20 OPERATIONS BOSTON
KING 5000 10 OPERATIONS BOSTON
TURNER 1500 30 OPERATIONS BOSTON
ADAMS 1100 20 OPERATIONS BOSTON
JAMES 950 30 OPERATIONS BOSTON
FORD 3000 20 OPERATIONS BOSTON
MILLER 1300 20 OPERATIONS BOSTON
AAA_BBB OPERATIONS BOSTON
60 rows selected.
SQL> CL SCR
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 20
111 AAA_BBB
15 rows selected.
SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> SELECT * FRO SALGRADE;
SELECT * FRO SALGRADE
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> SELECT * FROM SALGRADE;
GRADE LOSAL HISAL
---------- ---------- ----------
1 0 1000
2 1001 3000
3 3001 5000
SQL>
SQL> CL SCR
SQL> SELECT E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC,S.GRADE FROM EMP E,DEPT D,
2 SALGRADE S WHERE E.DEPTNO=D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;
ENAME SAL DEPTNO DNAME LOC GRADE
---------- ---------- ---------- -------------- ------------- ----------
SMITH 800 20 RESEARCH DALLAS 1
JAMES 950 30 SALES CHICAGO 1
ALLEN 1600 30 SALES CHICAGO 2
WARD 1250 30 SALES CHICAGO 2
JONES 2975 20 RESEARCH DALLAS 2
MARTIN 1250 30 SALES CHICAGO 2
BLAKE 2850 30 SALES CHICAGO 2
CLARK 2450 10 ACCOUNTING NEW YORK 2
SCOTT 3000 20 RESEARCH DALLAS 2
TURNER 1500 30 SALES CHICAGO 2
ADAMS 1100 20 RESEARCH DALLAS 2
FORD 3000 20 RESEARCH DALLAS 2
MILLER 1300 20 RESEARCH DALLAS 2
KING 5000 10 ACCOUNTING NEW YORK 3
14 rows selected.
SQL> CL SCR
SQL> -------------SUBQUERY------------------
SQL> ---QUERY WITH A QUERY
SQL> ---NESTED QUERY
SQL> ---INNER QUERY WILL EXECUTE FIRST ... ON THE BASIS OF THAT THE OUTER
SQL> ---QUERY WILL GET EXECUTE
SQL> CL SCR
SQL> ---Q: DISPLAY 2ND HIGHEST SALARY
SQL> ---WITHOUT SUBQUERY
SQL> SELECT MAX(SAL) FROM EMP;
MAX(SAL)
----------
5000
SQL> SELECT MAX(SAL) FROM EMP WHERE SAL < 5000;
MAX(SAL)
----------
3000
SQL> ----------
SQL> SELECT MAX(SAL) FROM EMP WHERE SAL < (
2 SELECT MAX(SAL) FROM EMP)
3 /
MAX(SAL)
----------
3000
SQL> CL SCR
SQL> ----DISPLAY 3RD HIGHEST SALARY
SQL> ----
SQL> SELECT MAX(SAL) FROM EMP;
MAX(SAL)
----------
5000
SQL> SELECT MAX(SAL) FROM EMP WHERE SAL < 5000;
MAX(SAL)
----------
3000
SQL> SELECT MAX(SAL) FROM EMP WHERE SAL < 3000;
MAX(SAL)
----------
2975
SQL> ------------
SQL> SELECT MAX(SAL) FROM EMP WHERE SAL < (
2 SELECT MAX(SAL) FROM EMP WHERE SAL < (
3 SELECT MAX(SAL) FROM EMP))
4 /
MAX(SAL)
----------
2975
SQL> CL SCR
SQL> ----DISPLAY NAME OF EMPLOYEE HAVING HIGHEST SALARY
SQL> SELECT MAX(SAL) FROM EMP;
MAX(SAL)
----------
5000
SQL> SELECT ENAME FROM EMP WHERE SAL=5000;
ENAME
----------
KING
SQL> ---------
SQL> SELECT ENAME FROM EMP WHERE SAL=(
2 SELECT MAX(SAL) FROM EMP)
3 /
ENAME
----------
KING
SQL> CL SCR
SQL> ---DISPLAY NAME OF EMPLOYEE WHOSE DEPT IS SAME AS BLAKE DEPT
SQL> SELECT DEPTNO FROM EMP WHERE ENAME='BLAKE';
DEPTNO
----------
30
SQL> SELECT ENAME FROM EMP WHERE DEPTNO=30;
ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
6 rows selected.
SQL> -------------
SQL> SELECT ENAME FROM EMP WHERE DEPTNO=(
2 SELECT DEPTNO FROM EMP WHERE ENAME='BLAKE');
ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
6 rows selected.
SQL> CL SCR
SQL> ----DISPLAY NAME OF EMPLOYEE WHOSE SALARY IS MORE THAN BLAKE SALARY
SQL> SELECT SAL FROM EMP WHERE ENAME='BLAKE';
SAL
----------
2850
SQL> SELECT ENAME FROM EMP WHERE SAL > 2850;
ENAME
----------
JONES
SCOTT
KING
FORD
SQL> SELECT ENAME FROM EMP WHERE SAL > (
2 SELECT SAL FROM EMP WHERE ENAME='BLAKE')
3 /
ENAME
----------
JONES
SCOTT
KING
FORD
SQL> CL CR
SP2-0158: unknown CLEAR option "CR"
SQL> CL SCR
SQL> ---DISPLAY NUMBER OF EMPLOYEE WHOSE SALARY IS MORE THN THE MAX SALARY OF
SQL> ---DEPT 10
SQL> ------------------------------------------------------------------
SQL> SELECT MAX(SAL) FROM EMP WHERE DEPTNO=10;
MAX(SAL)
----------
5000
SQL> SELECT COUNT(*) FROM EMP WHERE SAL > 5000;
COUNT(*)
----------
0
SQL> SELECT MAX(SAL) FROM EMP WHERE DEPTNO=20;
MAX(SAL)
----------
3000
SQL> SELECT COUNT(*) FROM EMP WHERE SAL > 3000;
COUNT(*)
----------
1
SQL> --------
SQL> SELECT COUNT(*) FROM EMP WHERE SAL > (
2 SELECT MAX(SAL) FROM EMP WHERE DEPTNO=20)
3 /
COUNT(*)
----------
1
SQL> CL SCR
SQL> ---------DISPLAY NAME OF EMPLOYEE WHOSE JOB IS SAME AS BLAKE JOB
SQL> SELET JOB FROM EMP WHERE ENAME='BLAKE';
SP2-0734: unknown command beginning "SELET JOB ..." - rest of line ignored.
SQL> SELECT JOB FROM EMP WHERE ENAME='BLAKE';
JOB
---------
MANAGER
SQL> SELECT ENAME FROM EMP WHERE JOB='MANAGER';
ENAME
----------
JONES
BLAKE
CLARK
SQL> -------------------------
SQL> SELECT ENAME FROM EMP WHERE JOB=(
2 SELECT JOB FROM EMP WHERE ENAME='BLAKE')
3 /
ENAME
----------
JONES
BLAKE
CLARK
SQL> CL SCR
SQL> ----DISPLAY NAME OF MANAGERS
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 20
111 AAA_BBB
15 rows selected.
SQL> SELECT ENAME FROM EMP WHERE EMPNO IN
2 (SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL);
ENAME
----------
FORD
BLAKE
KING
JONES
SCOTT
CLARK
6 rows selected.
SQL> CL SCR
SQL> ---DISPLAY NAME OF WROKERS
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 20
111 AAA_BBB
15 rows selected.
SQL> SELECT ENAME FROM EMP WHERE EMPNO NOT IN
2 (SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL);
ENAME
----------
AAA_BBB
TURNER
WARD
MARTIN
ALLEN
MILLER
SMITH
ADAMS
JAMES
9 rows selected.
SQL> CL SCR
SQL> ---DISPLAY NUMBER OF WORKERS UNDER EACH MANAGERS
SQL> -----------
SQL> SELECT E.ENAME,F.ENAME AS MANAGER FROM EMP E,EMP F
2 WHERE E.MGR=F.EMPNO;
ENAME MANAGER
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
13 rows selected.
SQL> SELECT F.ENAME AS MANAGER FROM EMP E,EMP F
2 WHERE E.MGR=F.EMPNO;
MANAGER
----------
JONES
JONES
BLAKE
BLAKE
BLAKE
BLAKE
BLAKE
CLARK
SCOTT
KING
KING
KING
FORD
13 rows selected.
SQL> SELECT F.ENAME AS MANAGER,COUNT(*) FROM EMP E,EMP F
2 WHERE E.MGR=F.EMPNO
3 GROUP BY F.ENAME;
MANAGER COUNT(*)
---------- ----------
JONES 2
FORD 1
CLARK 1
SCOTT 1
BLAKE 5
KING 3
6 rows selected.
SQL>
SQL> ---------------------------
SQL> CL SCR
SQL> ---ROWID | ROWNUM
SQL> ---PSEUDOCOLUMN
SQL> SELECT ROWID FORM EMP;
SELECT ROWID FORM EMP
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> SELECT ROWID FROM EMP;
ROWID
------------------
AAADaaAABAAAKdqAAA
AAADaaAABAAAKdqAAB
AAADaaAABAAAKdqAAC
AAADaaAABAAAKdqAAD
AAADaaAABAAAKdqAAE
AAADaaAABAAAKdqAAF
AAADaaAABAAAKdqAAG
AAADaaAABAAAKdqAAH
AAADaaAABAAAKdqAAI
AAADaaAABAAAKdqAAJ
AAADaaAABAAAKdqAAK
AAADaaAABAAAKdqAAL
AAADaaAABAAAKdqAAM
AAADaaAABAAAKdqAAN
AAADaaAABAAAKdqAAO
15 rows selected.
SQL> SELECT ROWNUM FROM EMP;
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
15 rows selected.
SQL> SELECT ROWNUM FROM DEPT;
ROWNUM
----------
1
2
3
4
SQL> CL SCR
SQL> SELECT ROWNUM FROM DEPT;
ROWNUM
----------
1
2
3
4
SQL> SELECT ROWID FROM EMP;
ROWID
------------------
AAADaaAABAAAKdqAAA
AAADaaAABAAAKdqAAB
AAADaaAABAAAKdqAAC
AAADaaAABAAAKdqAAD
AAADaaAABAAAKdqAAE
AAADaaAABAAAKdqAAF
AAADaaAABAAAKdqAAG
AAADaaAABAAAKdqAAH
AAADaaAABAAAKdqAAI
AAADaaAABAAAKdqAAJ
AAADaaAABAAAKdqAAK
AAADaaAABAAAKdqAAL
AAADaaAABAAAKdqAAM
AAADaaAABAAAKdqAAN
AAADaaAABAAAKdqAAO
15 rows selected.
SQL> CL SCR
SQL> SELECT * FROM EMP WHERE ROWNUM=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
SQL> SELECT * FROM EMP WHERE ROWNUM=2;
no rows selected
SQL> SELECT * FROM EMP WHERE ROWNUM<6;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
SQL> SELECT * FROM EMP WHERE ROWNUM>;
SELECT * FROM EMP WHERE ROWNUM>
*
ERROR at line 1:
ORA-00936: missing expression
SQL> SELECT * FROM EMP WHERE ROWNUM>6;
no rows selected
SQL> CL SCR
SQL> -----DISPLAY TOP 5 SALARY
SQL> SELECT SAL FROM EMP WHERE ROWNUM<6;
SAL
----------
800
1600
1250
2975
1250
SQL> SELECT SAL FROM EMP ORDER BY NVL(SAL,0);
SAL
----------
800
950
1100
1250
1250
1300
1500
1600
2450
2850
2975
3000
3000
5000
15 rows selected.
SQL> SELECT SAL FROM EMP ORDER BY NVL(SAL,0) DESC;
SAL
----------
5000
3000
3000
2975
2850
2450
1600
1500
1300
1250
1250
1100
950
800
15 rows selected.
SQL> SELECT DISTINCT SAL FROM EMP ORDER BY NVL(SAL,0) DESC;
SAL
----------
5000
3000
2975
2850
2450
1600
1500
1300
1250
1100
950
800
13 rows selected.
SQL> SELECT SAL FROM
2 (
3 SELECT DISTINCT SAL FROM EMP ORDER BY NVL(SAL,0) DESC
4 )
5 WHERE ROWNUM <6;
SAL
----------
5000
3000
2975
2850
2450
SQL> ------5TH HIGHEST SALARY
SQL> SELECT MIN(SAL) FROM
2 (
3 SELECT DISTINCT SAL FROM EMP ORDER BY NVL(SAL,0) DESC
4 )
5 WHERE ROWNUM <6;
MIN(SAL)
----------
2450
SQL> CL SCR
SQL> CREATE TABLE TEST
2 (ROLL NUMER(2),
3 NAME VARCHAR2(20)0
4 .
SQL> CREATE TABLE TEST
2 (ROLL NUMER(2),
3 NAME VARCHAR2(20))
4 /
(ROLL NUMER(2),
*
ERROR at line 2:
ORA-00907: missing right parenthesis
SQL> CREATE TABLE TEST
2 (ROLL NUMER(2),
3 NAME VARCHAR2(20))
4 /
(ROLL NUMER(2),
*
ERROR at line 2:
ORA-00907: missing right parenthesis
SQL> CREATE TABLE TEST
2 (ROLL NUMBER(2),
3 NAME VARCHAR2(20))
4 /
Table created.
SQL> INSERT INTO TEST VALUES(1,'AAA');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> INSERT INTO TEST VALUES(2,'BBBB');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> INSERT INTO TEST VALUES(3,'CCC');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> CL SCR
SQL> SELECT * FROM TEST
2 ;
ROLL NAME
---------- --------------------
1 AAA
1 AAA
1 AAA
1 AAA
2 BBBB
2 BBBB
2 BBBB
2 BBBB
2 BBBB
2 BBBB
2 BBBB
3 CCC
3 CCC
3 CCC
3 CCC
15 rows selected.
SQL> SELECT ROWID,ROLL,NAME FROM TEST
2 /
ROWID ROLL NAME
------------------ ---------- --------------------
AAADajAABAAAKeCAAA 1 AAA
AAADajAABAAAKeCAAB 1 AAA
AAADajAABAAAKeCAAC 1 AAA
AAADajAABAAAKeCAAD 1 AAA
AAADajAABAAAKeCAAE 2 BBBB
AAADajAABAAAKeCAAF 2 BBBB
AAADajAABAAAKeCAAG 2 BBBB
AAADajAABAAAKeCAAH 2 BBBB
AAADajAABAAAKeCAAI 2 BBBB
AAADajAABAAAKeCAAJ 2 BBBB
AAADajAABAAAKeCAAK 2 BBBB
AAADajAABAAAKeCAAL 3 CCC
AAADajAABAAAKeCAAM 3 CCC
AAADajAABAAAKeCAAN 3 CCC
AAADajAABAAAKeCAAO 3 CCC
15 rows selected.
SQL> SELECT ROLL,MAX(ROWID) FROM TEST GROUP BY ROLL;
ROLL MAX(ROWID)
---------- ------------------
1 AAADajAABAAAKeCAAD
2 AAADajAABAAAKeCAAK
3 AAADajAABAAAKeCAAO
SQL> DELETE FROM TEST WHERE
2 ROWID NOT IN
3 (
4 SELECT MAX(ROWID) FROM TEST GROUP BY ROLL
5 )
6 /
12 rows deleted.
SQL> SELECT * FROM TEST;
ROLL NAME
---------- --------------------
1 AAA
2 BBBB
3 CCC
SQL> CL SCR
SQL> ------------DISPLAY NAME OF MANAGERS HAVING HIGHEST NUMBERS OF WORKERS
SQL> -------------------
SQL> SELECT E.ENAME,F.ENAME AS MANAGER FROM EMP E,EMP F
2 WHERE E.MGR=F.EMPNO;
ENAME MANAGER
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
13 rows selected.
SQL> SELECT F.ENAME AS MANAGER FROM EMP E,EMP F
2 WHERE E.MGR=F.EMPNO;
MANAGER
----------
JONES
JONES
BLAKE
BLAKE
BLAKE
BLAKE
BLAKE
CLARK
SCOTT
KING
KING
KING
FORD
13 rows selected.
SQL> SELECT F.ENAME AS MANAGER,COUNT(*) FROM EMP E,EMP F
2 WHERE E.MGR=F.EMPNO
3 GROUP BY F.ENAME;
MANAGER COUNT(*)
---------- ----------
JONES 2
FORD 1
CLARK 1
SCOTT 1
BLAKE 5
KING 3
6 rows selected.
SQL> SELECT F.ENAME AS MANAGER,COUNT(*) AS NOW FROM EMP E,EMP F
2 WHERE E.MGR=F.EMPNO
3 GROUP BY F.ENAME
4 ORDER BY NOW;
MANAGER NOW
---------- ----------
CLARK 1
SCOTT 1
FORD 1
JONES 2
KING 3
BLAKE 5
6 rows selected.
SQL> SELECT F.ENAME AS MANAGER,COUNT(*) AS NOW FROM EMP E,EMP F
2 WHERE E.MGR=F.EMPNO
3 GROUP BY F.ENAME
4 ORDER BY NOW DESC;
MANAGER NOW
---------- ----------
BLAKE 5
KING 3
JONES 2
SCOTT 1
FORD 1
CLARK 1
6 rows selected.
SQL> SELECT MANAGER FROM
2 (
3 SELECT F.ENAME AS MANAGER,COUNT(*) AS NOW FROM EMP E,EMP F
4 WHERE E.MGR=F.EMPNO
5 GROUP BY F.ENAME
6 ORDER BY NOW DESC
7 )
8 WHERE ROWNUM=1;
MANAGER
----------
BLAKE
No comments:
Post a Comment