728x90
SQL> select ename, mgr from emp;
ENAME MGR
---------- ----------
SMITH 7902
ALLEN 7698
WARD 7698
JONES 7839
MARTIN 7698
BLAKE 7839
CLARK 7839
SCOTT 7566
KING
TURNER 7698
ADAMS 7788
JAMES 7698
FORD 7566
MILLER 7782
14 개의 행이 선택되었습니다.
SQL> select dname, count(*), avg(sal)
2 from emp, dept
3 where emp.deptno=dept.deptno
4 group by dname
5 order by dname;
DNAME COUNT(*) AVG(SAL)
-------------- ---------- ----------
ACCOUNTING 3 2916.66667
RESEARCH 5 2175
SALES 6 1566.66667
SQL> select mgr, empno, ename from emp;
MGR EMPNO ENAME
---------- ---------- ----------
7902 7369 SMITH
7698 7499 ALLEN
7698 7521 WARD
7839 7566 JONES
7698 7654 MARTIN
7839 7698 BLAKE
7839 7782 CLARK
7566 7788 SCOTT
7839 KING
7698 7844 TURNER
7788 7876 ADAMS
7698 7900 JAMES
7566 7902 FORD
7782 7934 MILLER
14 개의 행이 선택되었습니다.
SQL> select ename, dname, sal, job
2 from emp join dept on emp.deptno = dept.deptno
3 where lower(loc) in ('new york', 'dallas');
ENAME DNAME SAL JOB
---------- -------------- ---------- ---------
SMITH RESEARCH 800 CLERK
JONES RESEARCH 2975 MANAGER
CLARK ACCOUNTING 2450 MANAGER
SCOTT RESEARCH 3000 ANALYST
KING ACCOUNTING 5000 PRESIDENT
ADAMS RESEARCH 1100 CLERK
FORD RESEARCH 3000 ANALYST
MILLER ACCOUNTING 1300 CLERK
8 개의 행이 선택되었습니다.
SQL> select e.ename, m.ename, dname
2 from emp e join dept on e.deptno=dept.deptno
3 join emp m on e.mgr=e.empno;
선택된 레코드가 없습니다.
SQL> select e.ename, e.ename, dname
2 from emp e join dept on e.deptno=dept.deptno
3 join emp m on e.mgr=m.empno;
ENAME ENAME DNAME
---------- ---------- --------------
SMITH SMITH RESEARCH
ALLEN ALLEN SALES
WARD WARD SALES
JONES JONES RESEARCH
MARTIN MARTIN SALES
BLAKE BLAKE SALES
CLARK CLARK ACCOUNTING
SCOTT SCOTT RESEARCH
TURNER TURNER SALES
ADAMS ADAMS RESEARCH
JAMES JAMES SALES
FORD FORD RESEARCH
MILLER MILLER ACCOUNTING
13 개의 행이 선택되었습니다.
SQL> select e.ename, e.ename, dname
2 from emp e join dept on e.deptno=dept.deptno
3 join emp m on e.mgr=m.empno;
ENAME ENAME DNAME
---------- ---------- --------------
SMITH SMITH RESEARCH
ALLEN ALLEN SALES
WARD WARD SALES
JONES JONES RESEARCH
MARTIN MARTIN SALES
BLAKE BLAKE SALES
CLARK CLARK ACCOUNTING
SCOTT SCOTT RESEARCH
TURNER TURNER SALES
ADAMS ADAMS RESEARCH
JAMES JAMES SALES
FORD FORD RESEARCH
MILLER MILLER ACCOUNTING
13 개의 행이 선택되었습니다.
SQL> select e.ename, e.ename, dname
2 from emp e left outer join emp m on e.mgr=m.empno
3 join dept on e.deptno=dept.deptno;
ENAME ENAME DNAME
---------- ---------- --------------
SMITH SMITH RESEARCH
ALLEN ALLEN SALES
WARD WARD SALES
JONES JONES RESEARCH
MARTIN MARTIN SALES
BLAKE BLAKE SALES
CLARK CLARK ACCOUNTING
SCOTT SCOTT RESEARCH
KING KING ACCOUNTING
TURNER TURNER SALES
ADAMS ADAMS RESEARCH
JAMES JAMES SALES
FORD FORD RESEARCH
MILLER MILLER ACCOUNTING
14 개의 행이 선택되었습니다.
SQL> select mgr, empno, ename from emp;
MGR EMPNO ENAME
---------- ---------- ----------
7902 7369 SMITH
7698 7499 ALLEN
7698 7521 WARD
7839 7566 JONES
7698 7654 MARTIN
7839 7698 BLAKE
7839 7782 CLARK
7566 7788 SCOTT
7839 KING
7698 7844 TURNER
7788 7876 ADAMS
7698 7900 JAMES
7566 7902 FORD
7782 7934 MILLER
14 개의 행이 선택되었습니다.
SQL> select ename, dname, sal, job
2 from emp join dept on emp.deptno = dept.deptno
3 where lower(loc) in ('new york', 'dallas');
ENAME DNAME SAL JOB
---------- -------------- ---------- ---------
SMITH RESEARCH 800 CLERK
JONES RESEARCH 2975 MANAGER
CLARK ACCOUNTING 2450 MANAGER
SCOTT RESEARCH 3000 ANALYST
KING ACCOUNTING 5000 PRESIDENT
ADAMS RESEARCH 1100 CLERK
FORD RESEARCH 3000 ANALYST
MILLER ACCOUNTING 1300 CLERK
8 개의 행이 선택되었습니다.
SQL> select ename, dname
2 from emp right outer join dept
3 on emp.deptno = dept. deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
OPERATIONS
15 개의 행이 선택되었습니다.
SQL> select ename, dname
2 from emp, dept
3 where emp.deptno(+) = dept.deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
OPERATIONS
15 개의 행이 선택되었습니다.
SQL> spool off
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select job, count(*) from emp group by job;
JOB COUNT(*)
--------- ----------
CLERK 4
SALESMAN 4
PRESIDENT 1
MANAGER 3
ANALYST 2
SQL> select mgr, count(*) from emp
2 group by mgr
3 order by mgr
4 ;
MGR COUNT(*)
---------- ----------
7566 2
7698 5
7782 1
7788 1
7839 3
7902 1
1
7 개의 행이 선택되었습니다.
SQL> select m.ename 관리자명, e.ename 사원명
2 from emp e, emp m
3 where e.mgr=m.empno;
관리자명 사원명
---------- ----------
JONES FORD
JONES SCOTT
BLAKE JAMES
BLAKE TURNER
BLAKE MARTIN
BLAKE WARD
BLAKE ALLEN
CLARK MILLER
SCOTT ADAMS
KING CLARK
KING BLAKE
KING JONES
FORD SMITH
13 개의 행이 선택되었습니다.
SQL> select m.ename 관리자명, e.ename 사원명
2 from emp e, emp m
3 where e.mgr=m.empno order by 관리자명;
관리자명 사원명
---------- ----------
BLAKE ALLEN
BLAKE WARD
BLAKE MARTIN
BLAKE TURNER
BLAKE JAMES
CLARK MILLER
FORD SMITH
JONES SCOTT
JONES FORD
KING JONES
KING BLAKE
KING CLARK
SCOTT ADAMS
13 개의 행이 선택되었습니다.
SQL> select m.ename 관리자명, count(*) 사원수
2 from emp e, emp m
3 where e.mgr=m.empno group by m.ename order by 관리자명;
관리자명 사원수
---------- ----------
BLAKE 5
CLARK 1
FORD 1
JONES 2
KING 3
SCOTT 1
6 개의 행이 선택되었습니다.
SQL>
SQL> spool off
728x90
'행복한 프로그래밍' 카테고리의 다른 글
| select(3) (0) | 2018.05.24 |
|---|---|
| select(2) (0) | 2018.05.24 |
| DataBase select 가장 기초되는 구문들 (scott의 경우) (0) | 2018.03.28 |
| id passport (0) | 2018.02.26 |
| mozart family (0) | 2018.02.26 |