728x90
SQL> select * from dept where deptno = 20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
SQL> select * from emp where deptno = (select deptno from dept where dname = 'RESEARCH') ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 80/12/17 800
20
7566 JONES MANAGER 7839 81/04/02 2975
20
7788 SCOTT ANALYST 7566 87/04/19 3000
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7876 ADAMS CLERK 7788 87/05/23 1100
20
7902 FORD ANALYST 7566 81/12/03 3000
20
SQL> select ename, sal from emp where deptno = (select deptno from dept where dname = 'RESEARCH') ;
ENAME SAL
---------- ----------
SMITH 800
JONES 2975
SCOTT 3000
ADAMS 1100
FORD 3000
SQL> select ename, job from emp where to_char(hiredate, 'yyyy') =
2 (select to_char(hiredate,'yyyy') from emp where ename = 'SCOTT');
ENAME JOB
---------- ---------
SCOTT ANALYST
ADAMS CLERK
SQL> select ename, job from emp where to_char(hiredate, 'yyyy') =
2 (select to_char(hiredate,'yyyy') from emp where ename = 'SCOTT');
ENAME JOB
---------- ---------
SCOTT ANALYST
ADAMS CLERK
SQL> select ename, job from emp where to_char(hiredate, 'yyyy') =
2 (select to_char(hiredate,'yyyy') from emp where ename = 'SCOTT');
ENAME JOB
---------- ---------
SCOTT ANALYST
ADAMS CLERK
SQL> select ename, job from emp where to_char(hiredate, 'yyyy') =
2 (select to_char(hiredate,'yyyy') from emp where ename = 'SCOTT');
ENAME JOB
---------- ---------
SCOTT ANALYST
ADAMS CLERK
SQL> select ename, sal
2 from emp
3 where deptno in (10, 20);
ENAME SAL
---------- ----------
SMITH 800
JONES 2975
CLARK 2450
SCOTT 3000
KING 5000
ADAMS 1100
FORD 3000
MILLER 1300
8 개의 행이 선택되었습니다.
SQL> select ename, sal from emp where deptno in (select deptno from dept where lower(dname) = 'research' or lower(dname) = 'accounting');
ENAME SAL
---------- ----------
SMITH 800
JONES 2975
CLARK 2450
SCOTT 3000
KING 5000
ADAMS 1100
FORD 3000
MILLER 1300
8 개의 행이 선택되었습니다.
SQL> select ename from emp where sal > (select max(sal) from emp where job = 'MANAGER');
ENAME
----------
SCOTT
KING
FORD
SQL> select ename from emp where sal > (select min(sal) from emp where job = 'MANAGER');
ENAME
----------
JONES
BLAKE
SCOTT
KING
FORD
SQL> select ename from emp where sal > any(select sal from emp where job = 'MANAGER');
ENAME
----------
KING
FORD
SCOTT
JONES
BLAKE
SQL> select ename from emp where sal > any(select sal from emp where job = 'MANAGER')order by ename;
ENAME
----------
BLAKE
FORD
JONES
KING
SCOTT
SQL> select ename from emp where sal > (select min(sal) from emp where job = 'MANAGER')order by ename;
ENAME
----------
BLAKE
FORD
JONES
KING
SCOTT
SQL> spool off
728x90
'행복한 프로그래밍' 카테고리의 다른 글
| select(5) (0) | 2018.05.24 |
|---|---|
| select(4) (0) | 2018.05.24 |
| select(2) (0) | 2018.05.24 |
| select (1) (0) | 2018.05.24 |
| DataBase select 가장 기초되는 구문들 (scott의 경우) (0) | 2018.03.28 |