28일차: Oracle – ANSI Join/서브쿼리/DDL- 2021.07.22

목차

1.     ANSI Outer Join

2.     서브 쿼리

3.     DDL SQL문

 

1. ANSI Outer Join <7장>                            → ★2021_07_22_01 참고

 

- 형식: select * from table1 [left | right | full] outer join table2;

- 두 개의 테이블(DEPT01, DEPT02) 생성

<DEPT01>

create table dept01(deptno number(2), dname varchar2(14));

insert into dept01 values(10, 'ACCOUNTING');

insert into dept01 values(20, 'RESEARCH');

<DEPT02>

create table dept02(deptno number(2), dname varchar2(14));

insert into dept02 values(10, 'ACCOUNTING');

insert into dept02 values(30, 'SALES');

 

1) left outer join : DEPT01 테이블 정보만 출력

select * from dept01 left outer join dept02 using(deptno);

 

2) right outer join : DEPT02 테이블 정보만 출력

select * from dept01 right outer join dept02 using(deptno);

 

3) full outer join : DEPT01, DEPT02 테이블의 모든 정보 출력

select * from dept01 full outer join dept02 using(deptno);

 

2. 서브 쿼리 <8장>                                              → ★2021_07_22_01 참고

 

- 서브 쿼리는 하나의 SELECT 문장의 절 안에 포함된 또 하나의 SELECT 문장

- 서브 쿼리를 포함하고 있는 쿼리문을 메인 쿼리, 포함된 또 하나의 쿼리를 서브 쿼리라 함

 

 

- ex) Q. SCOTT 사원이 소속된 부서명을 출력하는 SQL문 작성

     1) 사원 테이블에서 scott 사원의 부서 번호를 구한다.

     select deptno from emp where ename='SCOTT';          → 20

     2) 부서 테이블에서 20번 부서의 부서명을 구한다.

     select dname from dept where deptno=20;                → RESEARCH

 

→ JOIN으로 구하기

select dname from dept, emp where dept.deptno=emp.deptno and ename='SCOTT';

select dname from dept inner join emp on dept.deptno=emp.deptno where ename='SCOTT';

select dname from dept inner join emp using(deptno) where ename='SCOTT';

select dname from dept natural join emp where ename='SCOTT'

 

→ 서브 쿼리로 구하기

select dname from dept where deptno =                          → 메인 쿼리

    (select deptno from emp where ename='SCOTT');           → 서브 쿼리

 

1) 단일 행 서브 쿼리

- 서브 쿼리의 검색 결과가 1개만 반환되는 쿼리

- 메인 쿼리의 where 조건절에서 비교 연산자만 사용 가능(=, >, >=, <, <=, !=)

- ex) Q1. 사원 테이블에서 가장 최근에 입사한 사원명을 출력하는 SQL문 작성

       select ename from emp where hiredate =

             (select max(hiredate) from emp);

 

       Q2. 사원 테이블에서 최대 급여를 받는 사원명과 최대급여를 출력하는 SQL문 작성

       select ename, sal from emp where sal =

            (select max(sal) from emp);

      *select ename, max(sal) from emp;

       → 오류 발생 : 그룹 함수와 일반 컬럼은 같이 사용할 수 없음

 

       Q3. 직속 상관(MGR)이 KING인 사원의 사원명과 급여를 출력하는 SQL문 작성

       select ename, sal from emp where mgr =

             (select empno from emp where ename='KING');

 

2) 다중 행 서브 쿼리

- 서브 쿼리의 검색 결과가 2개 이상 반환되는 쿼리

- 다중 행 서브 쿼리는 반드시 ‘다중 행 연산자(Multiple Row Operator)’와 함께 사용해야 함

 

① in 연산자

- 서브 쿼리의 검색 결과 중에서 하나라도 일치되면 참이 된다.

- ex) Q. 급여를 3000 이상 받는 사원이 소속된 부서와 그 부서에서 근무하는 사원들의

       정보를 출력하는 SQL문 작성

 

       <각 부서별 최대 급여 금액 구하기>

       select deptno, max(sal) from emp group by deptno;

       → 10번 부서 : 5000  /  20번 부서 : 3000  /  30번 부서 : 2850

 

       select ename, sal, deptno from emp where deptno =

            (select deptno from emp where sal >= 3000);         

       → 오류 발생 : 서브 쿼리의 결과가 2개 이상, 메인 쿼리에 = 연산자를 쓰지 못함

 

       select ename, sal, deptno from emp where deptno in

            (select distinct deptno from emp where sal >= 3000);  → 서브 쿼리 : 10, 20

 

② all 연산자

- 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참이 된다.

- ex) Q. 30번 부서에 소속된 사원 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는

         사원의 이름과 급여를 출력하는 SQL문 작성

 

<30번 부서 소속 사원들 중에서 최대 급여 구하기>

select max(sal) from emp where deptno=30;       → 2850

 

1) 단일 행 서브 쿼리로 구하기

select ename, sal from emp where sal >

    (select max(sal) from emp where deptno=30);  → 서브 쿼리 : 2850

   

2) 다중 행 서브 쿼리로 구하기

select ename, sal from emp where sal >all

    (select sal from emp where deptno=30);        → 다중 행 서브 쿼리 (결과 6개)

 

③ any 연산자

- 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 1개 이상 일치하면 참이 된다.

- ex) Q. 부서 번호가 30번인 사원들의 급여 중 가장 낮은 급여(950)보다 높은 급여를 받는

      사원들의 사원명과 급여를 출력하는 SQL문 작성

 

       <30번 부서 소속 사원들 중에서 가장 낮은 급여 구하기>

       select min(sal) from emp where deptno=30;        → 950

 

       1) 단일 행 서브 쿼리로 구하기

       select ename, sal from emp where sal >

            (select min(sal) emp from emp where deptno=30);    → 서브 쿼리 : 950

 

       2) 다중 행 서브 쿼리로 구하기

       select ename, sal from emp where sal >any

            (select sal from emp where deptno=30);                 → 다중 행 서브 쿼리

 

3. DDL (Data Definition Language) SQL문 <9장>              → ★2021_07_22_02 참고

 

• DDL (Data Definition Language) : 데이터 정의어

- create : 테이블 생성

- alter : 테이블 구조 변경

- rename : 테이블 이름 변경

- truncate : 데이터 삭제

- drop : 테이블 삭제

 

• 오라클의 데이터 타입

① 숫자 데이터

- number(n) : 정수 n자리까지 저장

- number(n1, n2) : n1 - 전체 자릿수

                         n2 - 소수점에 할당된 자릿수

 

② 문자 데이터

- char() : 고정 길이 문자형

            최대 2000 byte까지 저장 가능

- vachar2() : 가변 길이 문자형

                 최대 4000 byte까지 저장 가능

- long : 2GB까지 저장 가능

           long형으로 설정된 컬럼은 검색 기능이 지원되지 않는다.

 

③ 날짜 데이터

- date : 년/월/일

- timestamp : 년/월/일 시:분:초

 

1) CREATE 명령문

- 데이터베이스, 테이블 생성

 

방법 1) 일반적인 방법

create table 테이블명 (컬럼명  데이터타입,

                             컬럼명  데이터타입, ....);

 

- ex)

create table emp01( empno number(4),

                           ename varchar2(20),

                           sal number(7,2) )

 

방법 2) 서브 쿼리로 테이블 생성

- 복사본 테이블 생성됨

- 단, 제약조건은 복사되지 않는다.

 

- ex)

① 테이블 전체 복사

create table emp02 as select * from emp;

② 원하는 컬럼으로 구성된 복사본 테이블 생성

create table emp03 as select empno, ename from emp;

③ 원하는 행으로 구성된 복사본 테이블 생성

create table emp04 as select * from emp where deptno=10;

④ 테이블 구조만 복사

create table emp05 as select * from emp where 1=0;

→ where 조건절에 항상 거짓인 조건을 씀

 

* SQL Developer의 기능을 이용해서 테이블 생성 및 수정도 가능

 

 

 

2) ALTER 명령문

- 테이블 구조 변경(컬럼 추가, 컬럼값 수정, 컬럼 삭제)

 

① 컬럼 추가(add)

- ex) alter table emp01 add (job varchar2(10));

 

② 컬럼값 수정(modify)

i) 수정할 컬럼에 데이터가 없는 경우

컬럼의 '데이터 타입' 변경 가능

컬럼의 '크기' 변경 가능

 

ii) 수정할 컬럼에 데이터가 있는 경우

컬럼의 '데이터 타입' 변경 불가

컬럼의 '크기'를 늘릴 수는 있지만, 현재 데이터의 크기보다 작은 크기로 줄일 수는 없다.

 

- ex) alter table emp01 modify (job varchar2(30));

 

③ 컬럼 삭제(drop)

- 형식 1) drop column 컬럼명

- ex) alter table emp01 drop column job;

• 형식 2) drop(컬럼명)

- ex) alter table emp01 drop(job);      

 

3) RENAME 명령문

- 테이블 이름 변경

- 형식 : rename old_name to new_name;

- ex) Q. EMP01 테이블을 TEST 테이블명으로 변경

       rename emp01 to test;

 

4) TRUNCATE 명령문

- 테이블의 모든 데이터 삭제

- 형식 : truncate table 테이블명;

- ex) truncate table emp02;

 

5) DROP 명령문

- 테이블 삭제

- 형식: drop table 테이블명 purge;          → 깨끗하게 삭제됨

         drop table 테이블명;                   → oracle 10g부터는 임시 테이블로 교체

- ex) drop table test purge;                   → 깨끗하게 삭제 가능

       drop table test;                           → 깨끗하게 지워지지 않고, 임시 테이블로 교체됨

      * 임시 테이블 삭제: purge recyclebin;