Oracle Database multi-Table query instance and Oracle database instance

Oracle Database multi-Table query instance and Oracle database instance
Flute product: the number of columns is added, and the number of rows is multiplied.

SQL> -- equivalent connection SQL> -- Query employee information: employee ID name monthly salary Department name SQL> set linesize 80SQL> desc dept name is blank? Type ----------------------------------------- -------- ---------------------------- deptno not null number (2) DNAME VARCHAR2 (14) LOC VARCHAR2 (13) SQL> select e. empno, e. ename, e. sal, d. dname 2 from emp e, dept d 3 where e. deptno = d. deptno; empno ename sal dname ---------- -------------- 7369 SMITH 800 RESEARCH 7499 ALLEN 1600 SALES 7521 WARD 1250 SALES 7566 JONES 2975 RESEARCH 7654 MARTIN 1250 SALES 7698 BLAKE 2850 SALES 7782 CLARK 2450 ACCOUNTING 7788 SCOTT 3000 RESEARCH 7839 KING 5000 ACCOUNTING 7844 TURNER 1500 SALES 7876 ADAMS 1100 research empno ename sal dname ---------- -------------- 7900 JAMES 950 SALES 7902 FORD 3000 RESEARCH 7934 MILLER 1300 ACCOUNTING has selected 14 rows. SQL> -- Non-equivalent connection SQL> -- Query employee information: employee ID name monthly salary level SQL> select * from tab; tname tabtype clusterid ---------------------------- ------- ---------- dept table emp table bonus table salgrade table SQL> select * from salgrade; grade losal hisal ---------- 1 700 1200 2 1201 1400 3 1401 4 2000 5 2001 3000 SQL> select e. empno, e. ename, e. sal, s. grade 2 from emp e, salgrade s 3 wher E. sal between s. losal and s. hisal; empno ename sal grade ---------- 7369 SMITH 800 1 7900 JAMES 950 1 7876 ADAMS 1100 1 7521 WARD 1250 2 7654 MARTIN 1250 2 7934 MILLER 1300 2 7844 TURNER 1500 3 7499 ALLEN 1600 3 7782 CLARK 2450 4 7698 BLAKE 2850 4 7566 JONES 2975 4 empno ename sal grade ---------- 7788 SCOTT 3000 4 7902 FORD 3000 4 7839 KING 5000 5 select 14 rows. SQL> host clsSQL> -- External Connection: SQL> -- collect employee information by Department: Number of employees by department ID Department name SQL> select d. deptno department no., d. dname Department name, count (e. empno) number of people 2 from emp e, dept d 3 where e. deptno = d. deptno 4 group by d. deptno, d. dname; Department No. Department name count ---------- -------------- ---------- 10 ACCOUNTING 3 20 RESEARCH 5 30 SALES 6 SQL> select COUNT (*) from emp; count (*) ---------- 14 SQL> select * from dept; DEPTNO DNAME LOC ------------------------------ ------- 10 accounting new york 20 research dallas 30 sales chicago 40 operations boston SQL> select * from emp where deptno = 40; unselected SQL>/* SQL> you want to include some invalid records (Department 40) in the final result ---> external connection SQL> left outer connection: when where e. deptno = d. when deptno is not true, the table on the left of the equal sign is included in the final result. SQL> Syntax: where e. deptno = d. deptno (+) SQL> right outer join: When where e. deptno = d. when deptno is not true, the table on the right of the equal sign is included in the final result. SQL> Syntax: where e. deptno (+) = d. deptnoSQL> */SQL> select d. deptno, D. dname Department name, count (e. empno) number of people 2 from emp e, dept d 3 where e. deptno (+) = d. deptno 4 group by d. deptno, d. dname; Department No. Department name count ---------- -------------- ---------- 10 ACCOUNTING 3 40 OPERATIONS 0 20 RESEARCH 5 30 SALES 6 SQL> host clsSQL> -- self-connection SQL> -- Query employee information: employee name boss name SQL> set linesize 200SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------------------------------------------- ---------- 7369 smith clerk 7902-17-12 months-80 800 20 7499 allen salesman 7698 20-2 months-81 1600 300 30 7521 ward salesman 7698 22-2 months-81 1250 500 30 7566 jones manager 7839 04-4 months-81 2975 20 7654 martin salesman 7698 28-9 month-81 1250 1400 30 7698 blake manager 7839 01-5 months-81 2850 30 7782 clark manager 7839 09-6 months-81 2450 10 7788 SCOTT ANALYST 7566 month-87 3000 20 7839 KI Ng president 17-11 month-81 5000 10 7844 turner salesman 7698 08-9 month-81 1500 0 30 7876 adams clerk 7788 month-87 1100 20 empno ename job mgr hiredate sal comm deptno ----------------------------- ---------- -------------- ---------- 7900 james clerk 7698 03-12 month-81 950 30 7902 ford analyst 7566 03-12 month-81 3000 20 7934 miller clerk 7782 23-1 month-82 1300 10 already selected 14 rows. SQL> -- Auto join: treats the same table as multiple tables using the table alias. SQL> select e. ename employee name, B. ename owner name 2 from emp e, emp B 3 where e. mgr = B. empno; employee name boss name ---------- ford jones scott jones james blke TURNER blke MARTIN blke WARD blke ALLEN blke miller clark adams scott clark king blke KING employee name boss name ---------- jones king smith ford selected 13 rows. SQL> select count (*) 2 from emp e, emp B; COUNT (*) ---------- 196 SQL> -- Auto join is not suitable for large table SQL> -- hierarchical query SQL> select level, empno, ename, mgr 2 from emp 3 connect by prior empno = mgr 4 start with mgr is null 5 order by 1; level empno ename mgr ---------- 1 7839 KING 2 7566 JONES 7839 2 7698 BLAKE 7839 2 7782 CLARK 7839 3 7902 FORD 7566 3 7521 WARD 7698 3 7900 JAMES 7698 3 7934 MILL ER 7782 3 7499 ALLEN 7698 3 7788 SCOTT 7566 3 7654 MARTIN 7698 level empno ename mgr ------------ ---------- 3 7844 TURNER 7698 4 7876 ADAMS 7788 4 7369 SMITH 7902 has selected 14 rows. SQL> spool off

Hierarchical Query