Friday, June 23, 2006

Sql Joins #1

SQL> spool
currently spooling to D:\Data\General_purpose_sqls_and_plsql\FunctionsTest\general\23_jun_2006_1.lst
SQL> SELECT e.empno, e.ename, e.deptno, d.dname, d.loc
2 FROM emp e, dept d
3 WHERE e.deptno = d.deptno
4 /

EMPNO ENAME DEPTNO DNAME LOC
--------------- ---------- --------------- -------------- -------------
7782 CLARK 10 ACCOUNTING NEW YORK
7839 KING 10 ACCOUNTING NEW YORK
7934 MILLER 10 ACCOUNTING NEW YORK
7369 SMITH 20 RESEARCH DALLAS
7876 ADAMS 20 RESEARCH DALLAS
7902 FORD 20 RESEARCH DALLAS
7788 SCOTT 20 RESEARCH DALLAS
7566 JONES 20 RESEARCH DALLAS
7499 ALLEN 30 SALES CHICAGO
7698 BLAKE 30 SALES CHICAGO
7654 MARTIN 30 SALES CHICAGO
7900 JAMES 30 SALES CHICAGO
7844 TURNER 30 SALES CHICAGO
7521 WARD 30 SALES CHICAGO

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 SELECT e.empno, e.ename, e.deptno, d.dname, d.loc
2 FROM emp e, dept d
3* WHERE e.deptno(+) = d.deptno
SQL> /

EMPNO ENAME DEPTNO DNAME LOC
--------------- ---------- --------------- -------------- -------------
7782 CLARK 10 ACCOUNTING NEW YORK
7839 KING 10 ACCOUNTING NEW YORK
7934 MILLER 10 ACCOUNTING NEW YORK
7369 SMITH 20 RESEARCH DALLAS
7876 ADAMS 20 RESEARCH DALLAS
7902 FORD 20 RESEARCH DALLAS
7788 SCOTT 20 RESEARCH DALLAS
7566 JONES 20 RESEARCH DALLAS
7499 ALLEN 30 SALES CHICAGO
7698 BLAKE 30 SALES CHICAGO
7654 MARTIN 30 SALES CHICAGO
7900 JAMES 30 SALES CHICAGO
7844 TURNER 30 SALES CHICAGO
7521 WARD 30 SALES CHICAGO
OPERATIONS BOSTON

15 rows selected.

SQL> ed
Wrote file afiedt.buf

1 SELECT e.empno, e.ename, e.deptno, d.dname, d.loc
2 FROM emp e, dept d
3* WHERE e.deptno = d.deptno(+)
SQL> /

EMPNO ENAME DEPTNO DNAME LOC
--------------- ---------- --------------- -------------- -------------
7934 MILLER 10 ACCOUNTING NEW YORK
7839 KING 10 ACCOUNTING NEW YORK
7782 CLARK 10 ACCOUNTING NEW YORK
7902 FORD 20 RESEARCH DALLAS
7876 ADAMS 20 RESEARCH DALLAS
7788 SCOTT 20 RESEARCH DALLAS
7566 JONES 20 RESEARCH DALLAS
7369 SMITH 20 RESEARCH DALLAS
7900 JAMES 30 SALES CHICAGO
7844 TURNER 30 SALES CHICAGO
7698 BLAKE 30 SALES CHICAGO
7654 MARTIN 30 SALES CHICAGO
7521 WARD 30 SALES CHICAGO
7499 ALLEN 30 SALES CHICAGO

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 SELECT e.empno, e.ename, e.deptno, d.dname, d.loc
2 FROM emp e, dept d
3* WHERE e.deptno(+) = d.deptno(+)
SQL> /
WHERE e.deptno(+) = d.deptno(+)
*
ERROR at line 3:
ORA-01468: a predicate may reference only one outer-joined table


SQL> SELECT e.empno, e.ename, e.deptno, d.dname, d.loc
2 FROM
3 emp e full outer join
4 dept d
5 on( e.deptno = d.deptno)
6 /

EMPNO ENAME DEPTNO DNAME LOC
--------------- ---------- --------------- -------------- -------------
7934 MILLER 10 ACCOUNTING NEW YORK
7839 KING 10 ACCOUNTING NEW YORK
7782 CLARK 10 ACCOUNTING NEW YORK
7902 FORD 20 RESEARCH DALLAS
7876 ADAMS 20 RESEARCH DALLAS
7788 SCOTT 20 RESEARCH DALLAS
7566 JONES 20 RESEARCH DALLAS
7369 SMITH 20 RESEARCH DALLAS
7900 JAMES 30 SALES CHICAGO
7844 TURNER 30 SALES CHICAGO
7698 BLAKE 30 SALES CHICAGO
7654 MARTIN 30 SALES CHICAGO
7521 WARD 30 SALES CHICAGO
7499 ALLEN 30 SALES CHICAGO
OPERATIONS BOSTON

15 rows selected.

SQL> SELECT e.empno, e.ename, e.deptno, d.dname, d.loc
2 FROM emp e, dept d
3 WHERE e.deptno(+) = d.deptno;

EMPNO ENAME DEPTNO DNAME LOC
--------------- ---------- --------------- -------------- -------------
7782 CLARK 10 ACCOUNTING NEW YORK
7839 KING 10 ACCOUNTING NEW YORK
7934 MILLER 10 ACCOUNTING NEW YORK
7369 SMITH 20 RESEARCH DALLAS
7876 ADAMS 20 RESEARCH DALLAS
7902 FORD 20 RESEARCH DALLAS
7788 SCOTT 20 RESEARCH DALLAS
7566 JONES 20 RESEARCH DALLAS
7499 ALLEN 30 SALES CHICAGO
7698 BLAKE 30 SALES CHICAGO
7654 MARTIN 30 SALES CHICAGO
7900 JAMES 30 SALES CHICAGO
7844 TURNER 30 SALES CHICAGO
7521 WARD 30 SALES CHICAGO
OPERATIONS BOSTON

15 rows selected.

SQL> select * from emp where deptno=30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------------- ---------- --------- --------------- --------- --------------- --------------- ---------------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30

6 rows selected.

SQL> SELECT a.empno, a.ename, b.empno AS manager#, b.ename AS manager
2 FROM emp a, emp b
3 WHERE a.mgr = b.empno
4 /

EMPNO ENAME MANAGER# MANAGER
--------------- ---------- --------------- ----------
7788 SCOTT 7566 JONES
7902 FORD 7566 JONES
7499 ALLEN 7698 BLAKE
7521 WARD 7698 BLAKE
7900 JAMES 7698 BLAKE
7844 TURNER 7698 BLAKE
7654 MARTIN 7698 BLAKE
7934 MILLER 7782 CLARK
7876 ADAMS 7788 SCOTT
7566 JONES 7839 KING
7782 CLARK 7839 KING
7698 BLAKE 7839 KING
7369 SMITH 7902 FORD

13 rows selected.

SQL> -- outer self join
SQL> -- includes the person with no manager
SQL> SELECT a.empno, a.ename, b.empno AS manager#, b.ename AS manager
2 FROM emp a, emp b
3 WHERE a.mgr = b.empno(+)
4 /

EMPNO ENAME MANAGER# MANAGER
--------------- ---------- --------------- ----------
7902 FORD 7566 JONES
7788 SCOTT 7566 JONES
7900 JAMES 7698 BLAKE
7844 TURNER 7698 BLAKE
7654 MARTIN 7698 BLAKE
7521 WARD 7698 BLAKE
7499 ALLEN 7698 BLAKE
7934 MILLER 7782 CLARK
7876 ADAMS 7788 SCOTT
7782 CLARK 7839 KING
7698 BLAKE 7839 KING
7566 JONES 7839 KING
7369 SMITH 7902 FORD
7839 KING

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 SELECT a.empno, a.ename, b.empno AS manager#, b.ename AS manager
2 FROM emp a, emp b
3* WHERE a.mgr = b.empno(+)
SQL> /

EMPNO ENAME MANAGER# MANAGER
--------------- ---------- --------------- ----------
7902 FORD 7566 JONES
7788 SCOTT 7566 JONES
7900 JAMES 7698 BLAKE
7844 TURNER 7698 BLAKE
7654 MARTIN 7698 BLAKE
7521 WARD 7698 BLAKE
7499 ALLEN 7698 BLAKE
7934 MILLER 7782 CLARK
7876 ADAMS 7788 SCOTT
7782 CLARK 7839 KING
7698 BLAKE 7839 KING
7566 JONES 7839 KING
7369 SMITH 7902 FORD
7839 KING

14 rows selected.

SQL> -- Put together the emps who work in the same dept
SQL> select
2 a.DEPTNO,a.ENAME,b.ENAME
3 from emp a,emp b
4 where
5 a.DEPTNO=b.DEPTNO and
6 a.EMPNO<>b.EMPNO
7 /

DEPTNO ENAME ENAME
--------------- ---------- ----------
20 FORD SMITH
20 ADAMS SMITH
20 SCOTT SMITH
20 JONES SMITH
30 JAMES ALLEN
30 TURNER ALLEN
30 BLAKE ALLEN
30 MARTIN ALLEN
30 WARD ALLEN
30 JAMES WARD
30 TURNER WARD
30 BLAKE WARD
30 MARTIN WARD
30 ALLEN WARD
20 FORD JONES
20 ADAMS JONES
20 SCOTT JONES
20 SMITH JONES
30 JAMES MARTIN
30 TURNER MARTIN
30 BLAKE MARTIN
30 WARD MARTIN
30 ALLEN MARTIN
30 JAMES BLAKE
30 TURNER BLAKE
30 MARTIN BLAKE
30 WARD BLAKE
30 ALLEN BLAKE
10 MILLER CLARK
10 KING CLARK
20 FORD SCOTT
20 ADAMS SCOTT
20 JONES SCOTT
20 SMITH SCOTT
10 MILLER KING
10 CLARK KING
30 JAMES TURNER
30 BLAKE TURNER
30 MARTIN TURNER
30 WARD TURNER
30 ALLEN TURNER
20 FORD ADAMS
20 SCOTT ADAMS
20 JONES ADAMS
20 SMITH ADAMS
30 TURNER JAMES
30 BLAKE JAMES
30 MARTIN JAMES
30 WARD JAMES
30 ALLEN JAMES
20 ADAMS FORD
20 SCOTT FORD
20 JONES FORD
20 SMITH FORD
10 KING MILLER
10 CLARK MILLER

56 rows selected.

SQL> desc sal_grade
ERROR:
ORA-04043: object sal_grade does not exist


SQL> select * from cat;

TABLE_NAME TABLE_TYPE
------------------------------ -----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE

SQL> select * from SALGRADE;

GRADE LOSAL HISAL
--------------- --------------- ---------------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

SQL> select * from SALGRADE;

GRADE LOSAL HISAL
--------------- --------------- ---------------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

SQL> -- gradewise emp listing
SQL> SELECT e.EMPNO,e.Ename,d.DEPTNO,d.DNAME,e.SAL,s.GRADE
2 FROM emp e, dept d, salgrade s
3 WHERE e.deptno = d.deptno AND e.sal BETWEEN s.hisal AND s.losal;

no rows selected

SQL> SELECT e.EMPNO,e.Ename,d.DEPTNO,d.DNAME,e.SAL,s.GRADE
2 FROM emp e, dept d, salgrade s
3 WHERE e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal;

EMPNO ENAME DEPTNO DNAME SAL GRADE
--------------- ---------- --------------- -------------- --------------- ---------------
7369 SMITH 20 RESEARCH 800 1
7900 JAMES 30 SALES 950 1
7876 ADAMS 20 RESEARCH 1100 1
7521 WARD 30 SALES 1250 2
7654 MARTIN 30 SALES 1250 2
7934 MILLER 10 ACCOUNTING 1300 2
7844 TURNER 30 SALES 1500 3
7499 ALLEN 30 SALES 1600 3
7782 CLARK 10 ACCOUNTING 2450 4
7698 BLAKE 30 SALES 2850 4
7566 JONES 20 RESEARCH 2975 4
7788 SCOTT 20 RESEARCH 3000 4
7902 FORD 20 RESEARCH 3000 4
7839 KING 10 ACCOUNTING 5000 5

14 rows selected.

SQL> -- gradewise emp listing
SQL> SELECT e.EMPNO,e.Ename,d.DEPTNO,d.DNAME,e.SAL,s.GRADE
2 FROM emp e, dept d, salgrade s
3 WHERE e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal;

EMPNO ENAME DEPTNO DNAME SAL GRADE
--------------- ---------- --------------- -------------- --------------- ---------------
7369 SMITH 20 RESEARCH 800 1
7900 JAMES 30 SALES 950 1
7876 ADAMS 20 RESEARCH 1100 1
7521 WARD 30 SALES 1250 2
7654 MARTIN 30 SALES 1250 2
7934 MILLER 10 ACCOUNTING 1300 2
7844 TURNER 30 SALES 1500 3
7499 ALLEN 30 SALES 1600 3
7782 CLARK 10 ACCOUNTING 2450 4
7698 BLAKE 30 SALES 2850 4
7566 JONES 20 RESEARCH 2975 4
7788 SCOTT 20 RESEARCH 3000 4
7902 FORD 20 RESEARCH 3000 4
7839 KING 10 ACCOUNTING 5000 5

14 rows selected.

SQL> -- employees hired b4 their mgrs
SQL> SELECT a.empno, a.ename, a.hiredate, m.empno AS manager#, m.ename manager,
2 m.hiredate mgr_dt#
3 FROM emp a, emp m
4 WHERE a.mgr = m.empno AND a.hiredate <>
5 /
WHERE a.mgr = m.empno AND a.hiredate <>
*
ERROR at line 4:
ORA-00936: missing expression


SQL> ed
Wrote file afiedt.buf

1 SELECT a.empno, a.ename, a.hiredate, m.empno AS manager#, m.ename manager,
2 m.hiredate mgr_dt#
3 FROM emp a, emp m
4* WHERE a.mgr = m.empno AND a.hiredate <>
SQL> /

EMPNO ENAME HIREDATE MANAGER# MANAGER MGR_DT#
--------------- ---------- --------- --------------- ---------- ---------
7499 ALLEN 20-FEB-81 7698 BLAKE 01-MAY-81
7521 WARD 22-FEB-81 7698 BLAKE 01-MAY-81
7566 JONES 02-APR-81 7839 KING 17-NOV-81
7782 CLARK 09-JUN-81 7839 KING 17-NOV-81
7698 BLAKE 01-MAY-81 7839 KING 17-NOV-81
7369 SMITH 17-DEC-80 7902 FORD 03-DEC-81

6 rows selected.

SQL> spool off

No comments:

Post a Comment