SUBQUERY
- SUBQUERY의 사용방법과 SUBQUERY에 의해
검색된 데이터 정렬에 대해 알아보자!
-SUBQUERY를 사용할 수 있는 절
. WHERE, HAVING, UPDATE
. INSERT구문의 INTO
. UPDATE구문의 SET
. SELECT나 DELETE의 FROM절
-단일 행 SUBQUERY
단일 행 SUBQUERY는 내부 SELECT문장으로 부터
하나의 행을 검색하는 질의이다.
예문) emp테이블에서 SCOTT의 급여보다 많은
사원의 정보를 사번, 이름, 담당업무, 급여를 출력해 보자
우리가 SCOTT이라는 사람의 급여를 알지 못하므로
두번의 SQL문이 필요하다.
SELECT sal FROM emp WHERE name='SCOTT';
위의 결과를 가지고 다시 조건에 사용 되는
SQL문이 있어야 한다.
SELECT empno,ename,job,sal
FROM emp
WHERE sal > 3000;
위의 경우를 SUBQUERY로 구현을 한다면 다음과 같다.
SQL> SELECT empno, ename, job, sal
2 FROM emp
3 WHERE sal > (SELECT sal
4 FROM emp
5 WHERE ename = 'SCOTT');
문제) emp테이블에서 사번이 7521인 사람의 업무와
같고 급여가 7934보다 많은 사원의 정보를
사번, 이름, 업무, 입사일, 급여 순으로 출력해 보자!
SQL> SELECT empno, ename, job, hiredate, sal
2 FROM emp
3 WHERE job = (SELECT job
4 FROM emp
5 WHERE empno = 7521)
6 AND
7 sal > (SELECT sal
8 FROM emp
9 WHERE empno = 7934);
:: SUBQUERY에서 그룹함수 사용
예문) emp테이블에서 급여의 평균보다 적은 사원의
정보를 사번, 이름, 업무, 급여 순으로 출력해 보자!
SQL> SELECT empno, ename, job, sal
2 FROM emp
3 WHERE sal < (SELECT AVG(sal)
4 FROM emp);
:: SUBQUERY에서 HAVING절 사용
예문) emp테이블에서 20번 부서의 최소 급여보다
많은 모든 부서를 출력해보자!
SQL> SELECT deptno, MIN(sal)
2 FROM emp
3 GROUP BY deptno
4 HAVING MIN(sal) > (SELECT MIN(sal)
5 FROM emp
6 WHERE deptno = 20);
문제) emp테이블에서 업무별로 가장 적은 급여의 평균을
출력해 보자! (업무명, 급여의 평균값 순으로 출력)
SQL> SELECT JOB,AVG(sal)
2 FROM emp
3 GROUP BY job
4 HAVING AVG(sal) = (SELECT MIN(AVG(sal))
5 FROM emp
6 GROUP BY job);
:: 다중 행 SUBQUERY연산자
- IN연산자
- ANY연산자
- ALL연산자
- EXISTS연산자
1) IN 연산자
2개 이상의 행을 결과로 받는 SUBQUERY에 대하여
비교 연산자(=,>=...)를 기술하면 ERROR가 발생한다.
이런 경우 SUBQUERY에서 반환되는 모든 행의 값들을
비교하여 QUERY를 수행하는 연산자가 IN이다.
예문) emp테이블에서 업무별로 최소 급여를 받는
사원의 사번, 이름, 업무, 급여 순으로 출력해 보자!
SQL> SELECT empno, ename, job, sal
2 FROM emp
3 WHERE sal = (SELECT MIN(sal)
4 FROM emp
5 GROUP BY job);
위는 서브쿼리에서 넘겨지는 결과가 하나의 결과가 아니라
여러개의 결과 이므로 =이라는 연산을 수행하지 못한다.
SQL> SELECT empno, ename, job, sal
2 FROM emp
3 WHERE sal IN (SELECT MIN(sal)
4 FROM emp
5 GROUP BY job);
2) ANY 연산자
2개 이상의 결과를 반화하는 SUBQUERY에 대하여
어떻게 사용하는가를 지정해 두어야 한다.
비교 연산자(=,>=...)와 SUBQUERY 사이에
ANY연산자를 기술하여 반환된 결과 값 모두를 비교한다.
예문) emp테이블에서 30번 부서의 최소 급여를 받는
사원보다 급여를 더 많이 받는 사원의 정보를
사번,이름,업무, 입사일, 급여, 부서번호를 출력 해보자
(단, 30번 부서는 제외)
SQL> SELECT empno,ename,job,hiredate,sal,deptno
2 FROM emp
3 WHERE deptno != 30 AND
4 sal > ANY (SELECT MIN(sal)
5 FROM emp
6 WHERE deptno = 30);
테이블 생성
CREATE TABLE 문장을 실행하여 테이블을 생성한다.
이 문장은 DDL문장으로 오라클 DataBase 구조를 생성,
수정, 삭제 하는데 사용되는 SQL문장이다. 이러한 문장은
DataBase에 즉각 영향을 미치며 DataBase 사전(DATA
DICTIONARY)에 정보를 기록 한다.
문법:
CREATE TABLE [테이블명]
(컬럼명 자료형,...);
이름 규칙
1) 문자로 시작
2) 문자의 길이는 1~30 이내로 사용
3) 오직 A~Z, 0~9, _, $, #만을 사용 가능하다.
4) 동일한 사용자가 소유한 객체 이름은 중복될 수 없다.
5) 예약어를 사용 불가
자료형
1) VARCHAR2(n) :
가변 길이 문자 데이터 (1~4000byte)
2) CHAR(n) :
고정 길이 문자 데이터(1_2000byte)
3) NUMBER(p.s)
전체 p자리 중 소수점 이하 s자리(p: 1~38, s: -84~127)
4) DATE
7byte를 차지하는 세기,년,월,일,시,분,초를 기억하는
날짜 데이터
5) LONG
가변 길이 문자 데이터(1~2Gbyte)
6) CLOB
단일 바이트 가변 길이 문자 데이터(1~4Gbyte)
7) BLOB
가변 길이 이진 데이터(1~4Gbyte)
8) BFILE
가변 길이 외부 파일에 저장된 이진 데이터(1~4Gbyte)
9) RAW(n)
n byte의 원시 이진 데이터(1~2000)
10) LONG RAW
가변 길이 원시 이진 데이터(1~2Gbyte)
예문)
SQL> CREATE TABLE test_T(
2 name VARCHAR2(10),
3 age NUMBER(3),
4 phone VARCHAR2(15),
5 idNum CHAR(14)
6 addr1 VARCHAR2(10)
:: 제약 조건
오라클 서버는 부적절한 자료가 입력되는 것을
방지하기 위하여 constraint를 사용한다.
- 제약 조건은 테이블 LEVEL에서 규칙을 적용한다.
- 제약 조건은 종속성이 존재할 경우
테이블 삭제를 방지 한다.
- 테이블에서 행이 삽입, 갱신, 삭제 될 때마다
테이블에서 규칙을 적용한다.
:: 제약 조건 정의 방법
1) 컬럼 레벨
열(Column)별로 제약 조건을 정의한다.
- 무결성 제약 조건 5가지를 모두 적용 가능 하다.
1. PRIMARY KEY(PK) :
유일하게 테이블의 각 행을 구별한다.(NOT NULL
과 UNIQUE-중복 불가능 조건을 만족 한다.)
2. FOREIGN KEY(FK) :
열과 참조된 열 사이의 외래키 관계를 적용한다.
3. UNIQUE KEY(UK) :
테이블의 모든 행을 유일하게 하는 값을 가진 열
(NULL을 허용)
4. NOT NULL(NN) :
열은 NULL값을 포함할 수 없다.
5. CHECK(CK) :
참(TRUE)이어야 하는 조건을 지정함
(업무 규칙 설정 시)
- NOT NULL제약 조건은 컬럼 LEVEL에서만 가능하다
특정한 열(Column)에 대입되는 값이 절대로
NULL값을 허용해서는 안될때 사용한다.
idNum CHAR(14) CONSTRAINT NOT NULL
2) 테이블 레벨
테이블의 컬럼 정의와는 개별적으로 정의한다.
하나 이상의 열을 참조할 경우에 사용
NOT NULL을 제외한 나머지 제약 조건만 정의 가능하다
:: 테이블 생성 연습 SQL문
SQL> CREATE TABLE test_T(
2 id VARCHAR2(10) CONSTRAINT test_T_pk PRIMARY KEY,
3 name VARCHAR2(10),
4 pwd VARCHAR2(10));
SQL> CREATE TABLE test2_T(
2 hiredate date,
3 license VARCHAR2(20),
4 id VARCHAR2(10) CONSTRAINT test2_t_fk
REFERENCES test_T (id));
위의 내용을 달리 한다면
SQL> CREATE TABLE test2_T(
2 hiredate date,
3 license VARCHAR2(20),
4 id VARCHAR2(10),
5 CONSTRAINT test2_fk FOREIGN KEY (id)
REFERENCES test_T (id));
주의)
FOREIGN KEY값은 MASTER TABLE에서 존재하는
값과 일치해야 하거나 NULL이 되어야 한다.
예문) 다음 테이블 생성 코드를 보고 설명해 보자!
SQL> CREATE TABLE post(
2 post1 CHAR(3),
3 post2 CHAR(3),
4 addr VARCHAR2(60) CONSTRAINT
post_addr_nn NOT NULL,
5 CONSTRAINT post_post12_pk
PRIMARY KEY (post1, post2));
SQL> insert into post values('100','121','서울');
SQL> CREATE TABLE member_T(
2 id VARCHAR2(10) CONSTRAINT
member_T_pk PRIMARY KEY,
3 name VARCHAR2(10) CONSTRAINT
member_T_name_nn NOT NULL,
4 sex CHAR(1) CONSTRAINT
member_T_sex_ck
CHECK (sex IN ('1','2','3','4')),
5 jumin1 CHAR(6),
6 jumin2 CHAR(7),
7 tel VARCHAR2(15),
8 post1 CHAR(3),
9 post2 CHAR(3),
10 addr VARCHAR2(60),
11 CONSTRAINT member_jumin12_uk
UNIQUE (jumin1, jumin2),
12 CONSTRAINT member_post12_fk
FOREIGN KEY (post1, post2)
REFERENCES post (post1, post2));
위와 같이 제약 조건을 걸고 다음과 같이 데이터를
넣게 되면 오류가 발생 한다. 이유는?
SQL> INSERT INTO MEMBER_T VALUES(
'abc','마루치','1','123456','1234567',
'011','100','200','서울');
:: 테이블 생성시 유익한 Tip
emp테이블에서 사번, 이름, 업무, 입사일, 부서번호만
포함하여 emp_temp라는 테이블을 생성해보자
자료들은 포함하지 않고 구조만 생성하자!!
SQL> CREATE TABLE emp_temp
2 AS SELECT empno,ename,job,hiredate,deptno
3 FROM emp
4 WHERE 1 = 2;
확인
SQL> SELECT * FROM emp_temp;
SQL> desc emp_temp;
테이블 수정
:: 새로운 열(Column) 추가
SQL> ALTER TABLE emp_temp
2 ADD (etc VARCHAR2(20));
:: 열(Column) 수정
SQL> ALTER TABLE emp_temp
2 MODIFY (etc CHAR(30));
:: 열(Column)에 제약 조건 추가
SQL> ALTER TABLE emp_temp
2 ADD CONSTRAINT emp_temp_etc_nn
NOT NULL (etc);
주의)
NOT NULL제약 조건은 컬럼 정의 시에만 가능하다.
그러므로 위의 제약 조건 추가 문은 오류가 발생한다.
SQL> ALTER TABLE emp_temp
2 ADD CONSTRAINT emp_temp_etc_uk
UNIQUE (etc);
:: 열(Column)에 제약 조건 삭제
SQL> ALTER TABLE emp_temp
2 DROP CONSTRAINT emp_temp_etc_uk;
:: 사용되는 계정에 따른 제약 조건을 확인하기 위해
다음과 같은 DATA DICTIONARY를 통해 확인한다.
SQL> SELECT constraint_name,table_name,status
2 FROM user_constraints;
:: 테이블 삭제
SQL> DROP TABLE emp_temp;
아래는 테이블 삭제가 아니라 테이블에 있는 데이터 모두를
삭제하는 문장이다.
SQL> DELETE FROM emp_temp;
[출처] 서브쿼리, 테이블 생성|작성자 자바강지
