728x90
SQL> select ename, emp.deptno, dept.deptno, dname from emp, dept where emp.deptno=dept.deptno;
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
SMITH 20 20 RESEARCH
ALLEN 30 30 SALES
WARD 30 30 SALES
JONES 20 20 RESEARCH
MARTIN 30 30 SALES
BLAKE 30 30 SALES
CLARK 10 10 ACCOUNTING
SCOTT 20 20 RESEARCH
KING 10 10 ACCOUNTING
TURNER 30 30 SALES
ADAMS 20 20 RESEARCH
JAMES 30 30 SALES
FORD 20 20 RESEARCH
MILLER 10 10 ACCOUNTING
14 개의 행이 선택되었습니다.
SQL> select ename, dname from emp join dept 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
14 개의 행이 선택되었습니다.
SQL> select ename, emp.deptno, dept.deptno, dname from emp, dept where emp.deptno=dept.deptno;
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
SMITH 20 20 RESEARCH
ALLEN 30 30 SALES
WARD 30 30 SALES
JONES 20 20 RESEARCH
MARTIN 30 30 SALES
BLAKE 30 30 SALES
CLARK 10 10 ACCOUNTING
SCOTT 20 20 RESEARCH
KING 10 10 ACCOUNTING
TURNER 30 30 SALES
ADAMS 20 20 RESEARCH
JAMES 30 30 SALES
FORD 20 20 RESEARCH
MILLER 10 10 ACCOUNTING
14 개의 행이 선택되었습니다.
SQL> select ename, dname from emp join dept 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
14 개의 행이 선택되었습니다.
SQL> select ename 사원명, dname from emp 사원, dept 부서
2 where 사원.deptno=부서.deptno;
사원명 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
14 개의 행이 선택되었습니다.
SQL> select ename, sal, grade from emp, salgrade
2 sal between losal and hisal;
sal between losal and hisal
*
2행에 오류:
ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다
SQL> select ename, sal, grade from emp, salgrade
2 where sal between losal and hisal;
ENAME SAL GRADE
---------- ---------- ----------
SMITH 800 1
JAMES 950 1
ADAMS 1100 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 3
ALLEN 1600 3
CLARK 2450 4
BLAKE 2850 4
JONES 2975 4
SCOTT 3000 4
FORD 3000 4
KING 5000 5
14 개의 행이 선택되었습니다.
SQL> spool off
SQL> --잘못된 답
SQL> select dept.dname, emp.ename, hiredate from emp, dept where job = 'SALESMAN';
DNAME ENAME HIREDATE
-------------- ---------- --------
ACCOUNTING ALLEN 81/02/20
RESEARCH ALLEN 81/02/20
SALES ALLEN 81/02/20
OPERATIONS ALLEN 81/02/20
ACCOUNTING WARD 81/02/22
RESEARCH WARD 81/02/22
SALES WARD 81/02/22
OPERATIONS WARD 81/02/22
ACCOUNTING MARTIN 81/09/28
RESEARCH MARTIN 81/09/28
SALES MARTIN 81/09/28
OPERATIONS MARTIN 81/09/28
ACCOUNTING TURNER 81/09/08
RESEARCH TURNER 81/09/08
SALES TURNER 81/09/08
OPERATIONS TURNER 81/09/08
16 개의 행이 선택되었습니다.
SQL> --올바른 답
SQL> select ename, dname, hiredate
2 from emp inner join dept on
3 emp.deptno = dept.deptno
4 where job = 'SALESMAN';
ENAME DNAME HIREDATE
---------- -------------- --------
TURNER SALES 81/09/08
MARTIN SALES 81/09/28
WARD SALES 81/02/22
ALLEN SALES 81/02/20
SQL> select ename, dname, hiredate
2 from emp inner join dept on
3 emp.deptno = dept.deptno
4 where loc = 'NEW YORK';
ENAME DNAME HIREDATE
---------- -------------- --------
CLARK ACCOUNTING 81/06/09
KING ACCOUNTING 81/11/17
MILLER ACCOUNTING 82/01/23
SQL> --서브쿼리
SQL>
SQL> desc emp
이름 널? 유형
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select ename from emp where ename = (select ename from dept where loc = 'NEW YORK');
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 개의 행이 선택되었습니다.
SQL> select ename from emp where deptno = (select deptno from dept where loc = 'NEW YORK');
ENAME
----------
CLARK
KING
MILLER
SQL> select ename from emp where deptno = (select deptno from dept where loc = 'NEW YORK');
ENAME
----------
CLARK
KING
MILLER
SQL> select ename from emp where exists (select deptno from dept where loc = 'NEW YORK');
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 개의 행이 선택되었습니다.
SQL> select ename from emp where deptno = (select deptno from dept where loc = 'NEW YORK');
ENAME
----------
CLARK
KING
MILLER
SQL> select dname, loc from dept d
2 where exists (select * from emp where deptno=d.deptno);
DNAME LOC
-------------- -------------
RESEARCH DALLAS
SALES CHICAGO
ACCOUNTING NEW YORK
SQL> select dname, loc from dept 부서
2 where exists (select * from emp where deptno=부서.deptno);
DNAME LOC
-------------- -------------
RESEARCH DALLAS
SALES CHICAGO
ACCOUNTING NEW YORK
SQL> spool off
728x90
'행복한 프로그래밍' 카테고리의 다른 글
| select(4) (0) | 2018.05.24 |
|---|---|
| select(3) (0) | 2018.05.24 |
| select (1) (0) | 2018.05.24 |
| DataBase select 가장 기초되는 구문들 (scott의 경우) (0) | 2018.03.28 |
| id passport (0) | 2018.02.26 |