SUB-QUERY

SQL> SELECT DEPTNO FROM EMP WHERE ENAME='ALLEN';

DEPTNO
----------
30

SQL> SELECT ENAME FROM EMP WHERE DEPTNO=30;

ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
WARD
7 rows selected.

SQL> SELECT ENAME FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM

EMP WHERE ENAME='ALLEN');
ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
WARD
7 rows selected.

SQL> SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP WHERE

DEPTNO=(SELECT DEPTNO FROM DEP WHERE DNAME='SALES'
);
EMPNO ENAME JOB DEPTNO
---------- ---------- ---------- ----------
7499 ALLEN SALESMAN 30
7521 WARD SALESMAN 30
7654 MARTIN SALESMAN 30
7698 BLAKE MANAGER 30
7844 TURNER SALESMAN 30
7900 JAMES CLERK 30
7521 WARD SALESMAN 30
7 rows selected.
SQL> SELECT EMPNO,ENAME FROM EMP WHERE EMPNO IN(SELECT
MGRNO FROM EMP);
EMPNO ENAME
---------- ----------
7566 JONES
7698 BLAKE
7782 CLARK
7839 KING

SQL> SELECT EMPNO,ENAME,SALARY,DEPTNO FROM EMP WHERE SALARY

IN(SELECT MAX(SALARY) FROM EMP GROUP BY DEPTNO);
EMPNO ENAME SALARY DEPTNO
---------- ---------- ---------- ----------
7698 BLAKE 2850 30
7788 SCOTT 3000 20
7839 KING 5000 10

SQL> SELECT EMPNO,ENAME,SALARY,DEPTNO FROM EMP A WHERE

SALARY>(SELECT AVG(SALARY) FROM EMP WHERE DEPTNO=A.DEPTNO);
EMPNO ENAME SALARY DEPTNO
---------- ---------- ---------- ----------
7499 ALLEN 1600 30
7566 JONES 2800 20
7698 BLAKE 2850 30
7788 SCOTT 3000 20
7839 KING 5000 10
7844 TURNER 1500 30
6 rows selected.
 JOINS
SQL> CREATE TABLE EMPL(EMPNO NUMBER(4),ENAME
VARCHAR2(20),SALARY NUMBER(4),MGRNO NUMBER(4),DEPTNO NU
MBER(2));

SQL> DESC EMPL;

Name Null? Type
-------------------------------- -------- ----------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(20)
SALARY NUMBER(4)
MGRNO NUMBER(4)
DEPTNO NUMBER(2)

SQL>INSERT INTO EMPL VALUES(7369,'SMITH',1000,7902,20);

1 row created.

SQL>INSERT INTO EMPL VALUES(7499,'MARK',1050,7698,30);

1 row created.

SQL> INSERT INTO EMPL VALUES(7565,'WILL',1500,7698,30);

1 row created.

SQL> INSERT INTO EMPL VALUES(7678,'JOHN',1800,7898,20);

1 row created.

SQL> INSERT INTO EMPL VALUES(7578,'TOM',1500,7298,10);

1 row created.

SQL>INSERT INTO EMPL VALUES(7548,'TURNER',1500,7298,10);

1 row created.

SQL> SELECT * FROM EMPL;

EMPNO ENAME SALARY MGRNO DEPTNO
--------- -------------------- ---------- ---------- ----------
7369 SMITH 1000 7902 20
7499 MARK 1050 7698 30
7565 WILL 1500 7698 30
7678 JOHN 1800 7898 20
7578 TOM 1500 7298 10
7548 TURNER 1500 7298 10
6 rows selected.

SQL> CREATE TABLE DEPMT(DNAME VARCHAR2(20),DEPTNO

NUMBER(2),LOC VARCHAR2(20));

Table created.

SQL> DESC DEPMT;

Name Null? Type
----------------------------------------- -------- -----------------------
DNAME VARCHAR2(20)
DEPTNO NUMBER(2)
LOC VARCHAR2(20)

SQL> INSERT INTO DEPMT VALUES('ACCOUNT',10,'NEW YORK');

1 row created.

SQL> INSERT INTO DEPMT VALUES('SALES',20,'CHICAGO');

1 row created.

SQL> INSERT INTO DEPMT VALUES('RESEARCH',30,'ZURICH');

1 row created.

SQL> SELECT * FROM DEPMT;

DNAME DEPTNO LOC
-------------------- ---------- --------------------
ACCOUNT 10 NEW YORK
SALES 20 CHICAGO
RESEARCH 30 ZURICH

SQL> SELECT EMPNO,ENAME,DNAME,LOC FROM EMPL,DEPMT;

EMPNO ENAME DNAME LOC
---------- -------------------- -------------------- --------------------
7369 SMITH ACCOUNT NEW YORK
7499 MARK ACCOUNT NEW YORK
7565 WILL ACCOUNT NEW YORK
7678 JOHN ACCOUNT NEW YORK
7578 TOM ACCOUNT NEW YORK
7548 TURNER ACCOUNT NEW YORK
7369 SMITH SALES CHICAGO
7499 MARK SALES CHICAGO
7565 WILL SALES CHICAGO
7678 JOHN SALES CHICAGO
 7578 TOM SALES CHICAGO

EMPNO ENAME DNAME LOC

---------- -------------------- -------------------- --------------------
7548 TURNER SALES CHICAGO
7369 SMITH RESEARCH ZURICH
7499 MARK RESEARCH ZURICH
7565 WILL RESEARCH ZURICH
7678 JOHN RESEARCH ZURICH
7578 TOM RESEARCH ZURICH
7548 TURNER RESEARCH ZURICH
18 rows selected.

SQL> SELECT EMPNO,ENAME,DEPMT.DEPTNO,DNAME,LOC FROM

EMPL,DEPMT WHERE EMPL.DEPTNO=DEPMT.DEPTNO;

EMPNO ENAME DEPTNO DNAME LOC

---------- -------------------- ---------- -------------------- --------------------
7578 TOM 10 ACCOUNT NEW YORK
7548 TURNER 10 ACCOUNT NEW YORK
7369 SMITH 20 SALES CHICAGO

EMPNO ENAME DEPTNO DNAME LOC

---------- -------------------- ---------- -------------------- --------------------
7678 JOHN 20 SALES CHICAGO
7499 MARK 30 RESEARCH ZURICH
7565 WILL 30 RESEARCH ZURICH
6 rows selected.

SQL> SELECT A.EMPNO,A.ENAME,B.DEPTNO,B.DNAME,B.LOC FROM EMPL

A,DEPMT B WHERE A.DEPTNO=B.DEPTNO;
EMPNO ENAME DEPTNO DNAME LOC
---------- -------------------- ---------- -------------------- --------------------
7578 TOM 10 ACCOUNT NEW YORK
7548 TURNER 10 ACCOUNT NEW YORK
7369 SMITH 20 SALES CHICAGO

EMPNO ENAME DEPTNO DNAME LOC

---------- -------------------- ---------- -------------------- --------------------
7678 JOHN 20 SALES CHICAGO
7499 MARK 30 RESEARCH ZURICH
7565 WILL 30 RESEARCH ZURICH
6 rows selected.
SQL> SELECT A.EMPNO,A.ENAME,B.DEPTNO,B.DNAME,B.LOC FROM EMPL
A,DEPMT B WHERE A.DEPTNO(+)=B.DEPTNO;
EMPNO ENAME DEPTNO DNAME LOC
---------- -------------------- ---------- -------------------- --------------------
7578 TOM 10 ACCOUNT NEW YORK
7548 TURNER 10 ACCOUNT NEW YORK
7369 SMITH 20 SALES CHICAGO

EMPNO ENAME DEPTNO DNAME LOC

---------- -------------------- ---------- -------------------- --------------------
7678 JOHN 20 SALES CHICAGO
7499 MARK 30 RESEARCH ZURICH
7565 WILL 30 RESEARCH ZURICH
6 rows selected.