[20110711] Oracle Subquery & 복수행 & DML

-- FORD 와 동일한 직업을 가진 사람은 누구 인가?

-- 단일행 Subquery

sql > SELECT *

         FROM EMP

         WHERE job = (SELECT job FROM EMP

                     WHERE ename = 'FORD')

서브쿼리 EMP 테이블의 ename 이 'FORD' 라는 정보가 있는 JOB을 가져와 EMP 테이블의 해당 JOB을 출력한다.

-- 주의사항

-- error msg : single-row subquery returns more than one row

-- 1. 여러행의 결과는 처리할 수 없다.

sql > SELECT sal

         FROM EMP

         where sal = (SELECT sal FROM EMP WHERE deptno = 10);

DEPTNO 에 관한 SAL의 데이터값은 여러행이기 때문에 위 쿼리문은 오류가 난다.

 

-- error msg : too many values

-- 2. 서브쿼리의 행과 메인쿼리의 행의 갯수나 행이 다르면 안된다.

--    ( 서브쿼리 행과 메인쿼리 행은 같아야 한다. )

sql > SELECT ename, sal, mgr

         FROM EMP

         where job = (SELECT job, dempno, comm FROM EMP WHERE empno = 7900);

서브쿼리의 행과 메인쿼리의 행이 다르므로 쿼리문 실행이 되지 않는다.

 

-- 3. 서브쿼리에서는 복수행 함수를 사용할 수 없다.

sql > SELECT ename, sal, mgr

         FROM EMP

         where sal > (SELECT sal FROM EMP WHERE AVG(sal) < 10);

복수행 함수는 서브쿼리에서 복수행 함수를 사용 할 수 없다. 

 

-- 4. 서브쿼리는 괄호로 묶는다.

-- 5. 서브쿼리는 ORDER BY 에서는 사용할 수 없다. 

-- 복수행 서브쿼리 --> IN, ANY(SOME), ALL, EXISTS

-- 부서에서 제일 작은 봉급을 받는 사원의 이름, 봉급, 부서번호를 출력하라.

-- IN 은 똑같아야 한다.

sql > SELECT ename, sal, deptno

         FROM EMP

         WHERE sal IN (SELECT MIN(sal) FROM EMP GROUP BY deptno);

-- where sal in (800, 950, 1300); <----- 위와 같은 조건

EMP 테이블의 DEPTNO 그룹의 최소SAL 을 구하여 메인쿼리의 ENAME, SAL, DEPTNO를 구한다.

 

-- 급여가 CLERK 보다 적으면서, 직무가 CLERK 가 아닌 사원의

-- 번호,이름, 직무 를 출력하라.

-- ANY는 ~ 중에 하나이면 된다.

sql > SELECT empno, ename, job

         FROM EMP

         WHERE sal < ANY (SELECT sal FROM EMP WHERE job = 'CLERK') AND job <> 'CLERK';

서브쿼리 JOB이 CLERK인 SAL을 구하고 JOB이 CLERK이 아닌 메인쿼리 EMPNO, ENAME, JOB을 구한다.

 

-- 급여가 모든 급여의 평균 급여보다 많은 사원번호, 사원이름, 직무, 급여를 출력하라.

sql > SELECT empno, ename, job, sal

        FROM EMP

        WHERE sal > ALL(SELECT AVG(sal) FROM EMP GROUP BY deptno);

서브쿼리 DEPTNO 를 그룹하여 평균 급여를 구하고 평균급여보다 큰 급여 EMPNO, ENAME, JOB, SAL을 구한다.

 

-- 10번부서의 이름과 지역을 출력 하라.

-- EXISTS 는 참인지 거짓인지를 구분한다.

sql > SELECT dname, LOC

         FROM DEPT

         WHERE EXISTS(SELECT * FROM DEPT WHERE deptno = 10);

서브쿼리 DEPTNO 가 10인 것을 메인쿼리 DEPT 테이블 DNAME, LOC 를 출력한다.
EXISTS 는 EXISTS 행 안에 있는 것만 찾기 때문에 IN 보다 검색 속도가 빠르다. 

 

-- 사원번호 7396, 7499번과 같은 매니저와 부서번호를 갖는

-- 모든 사원의 번호, 매니저 번호, 부서 번호를 출력 하라.

-- 단, 7368, 7499는 제외한다.

sql > SELECT empno, mgr, deptno

        FROM EMP

        WHERE (mgr, deptno) IN (SELECT mgr, deptno FROM EMP WHERE empno IN(7396, 7499)) 
        AND empno NOT IN (7368, 7499);

서브쿼리 EMPNO가 7396, 7499 인  MGR, DEPTNO 를 가져오며 메인쿼리 MGR, DEPTNO를 참고하여 EMPNO, MGR, DEPTNO 를 출력한다. 단 7368, 7499 는 제외한다.

 

-- DML INSERT

-- Dept Table 에 추가하자.

sql > INSERT INTO DEPT(deptno, dname, loc)

        VALUES (50, 'DEVELOPMENT', 'SEOUL');

DEPT 테이블에 DEPTNO에 50 , DNAME에 DEVELOPMENT, LOC에 SEOUL 를 삽입한다.

 

-- 컬럼을 생략할 수 있다.

sql > INSERT INTO DEPT

        VALUES (60, 'MARKETTING', 'PUSAN');

DEPT 테이블에 첫번째 컬럼 부터 차례대로 60, MARKETTING, PUSAN 을 삽입한다.
컬럼을 생략 가능 하다. 

 

-- 순서가 달라도 된다 하지만. 컬럼 순서는 일치해야한다.

sql > INSERT INTO DEPT(loc, deptno, dname)

        VALUES ('BUDANG', 70, 'JUNSAN');

DEPT 테이블에 LOC에 BUDANG, DEPTNO에 70, DNAME에 JUNSAN 을 삽입한다.
컬럼의 배치는 달라도 되지만 VALUES 와는 일치 시켜야 한다. 

 

-- 주의할 점

-- 1. 반드시 한번에 한행만 입력된다.

sql > INSERT INTO DEPT

        VALUES (80, '영업', '수원', 90, '총무', '인천');

DEPT 에 값은 한행만 입력이 된다. 한번의 INSERT로 여러 행을 삽입할 수 없다.

 

 -- 2. 데이타 타입이 일치해야 한다.

 -- 3. 데이타의 크기가 크지 않아야 한다.

sql > DESC DEPT;

sql > INSERT INTO DEPT

         VALUES (100, '영업', '수원');

DEPT 테이블에 삽입하는 VALUES 가 해당 컬럼의 데이타 타입과 일치해야 하며 범위를 초과해서는 안된다.

 

 -- 4. NULL 값에 주의하자.

sql > INSERT INTO DEPT

        VALUES(100,'영업', '');

sql > INSERT INTO DEPT

        VALUES (80, '영업', '');

sql > INSERT INTO DEPT

        VALUES(90,'총무', NULL);

 
sql > SELECT * FROM DEPT;

 -- INSERT INTO DEPT

 -- VALUES(95, 자재, NULL);

 
sql > SELECT * FROM EMP

 sql > INSERT INTO EMP(empno, ename, hiredate)

         VALUES (9999, '조성모', sysdate);

 -- 외래키의 제약 조건이 있기 때문에 INSERT 불가하다.

 -- INSERT 할때는 제약조건이 있지 않나 확인을 중요시하자.

sql > INSERT INTO emp(empno, ename, deptno)

        VALUES (8888, '이미자', 99);

sql > INSERT INTO emp(empno, ename, deptno)

        VALUES (8888, '이미자', 50);

 -- DML DELETE

 -- 이미자 가 속해 있는 50번 부서를 없애자

 -- 참조하는 테이블 정보를 먼저 지우자

sql > DELETE FROM EMP

        WHERE empno = 8888;

 -- 참조 당하는 테이블 정보를 지우자

sql > DELETE FROM DEPT

        WHERE deptno = 50;

 sql > DELETE FROM DEPT

         WHERE deptno BETWEEN 60 AND 90;

-- DML UPDATE

sql > UPDATE dept

        SET loc = 'SEOUL'

        WHERE deptno = 20;

DEPT 테이블 DEPTNO 가 20인 LOC를 SEOUL로 변경하자.

 

sql > UPDATE dept

        SET dname = 'RESEARCH', loc = 'DALLAS'

        WHERE deptno = 20;

DEPT 테이블의 DEPTNO 가 20인 컬럼에서 DNAME 를 RESEARCH로 LOC를 DALLAS 로 변경하자.

 

sql > UPDATE EMP

        SET sal = sal * 1.1

        WHERE deptno = 30;

EMP 테이블 DEPTNO 가 30인 컬럼 중에 SAL에 10% 계산하여 나온값으로 변경 하자.

 

sql > UPDATE EMP

        SET comm = (SELECT FLOOR(AVG(NVL(comm, 0))) FROM emp)

        WHERE ename = 'JAMES';

EMP 테이블 comm의 평균을 구하여 ENAME이 JAMES인 comm을 변경하자.

 

sql > UPDATE EMP

        SET hiredate = SYSDATE

WHERE TO_CHAR(hiredate, 'YYYY') IN (1981, 1982);

EMP 테이블 1981 ~ 1982 인 컬럼중에 HIREDATE 를 SYSDATE(현재날짜) 로 변경 하자.