[6/17] 오라클 서브쿼리

• 서브쿼리 : sql명령에 포함되어 실행되는 select명령
• 여러번의 sql명령으로 얻을 수 있는 결과를 하나의 sql명령으로 얻기위해 사용하는 기능
• select(main쿼리) 명령에 포함되어 실행되는 select(서브쿼리)명령
• 서브쿼리 명령 실행 후 메인쿼리 명령 실행
• 메인쿼리 명령에서 서브뭐리 명령은 from, whrere, having구문에서 ( )안에 작성
• WHERE구문에서 조건식의 비교값 대신 서브쿼리 명령의 검색값을 사용하여 검색
• 조건식의 비교대상과 같은 자료형의 값이 하나만 검색되도록 서브쿼리를 작성해야 한다.
• 단일행의 단일 검색값으로 비교

--EMP테이블에서 사원이름이 SCOTT인 사원보다 많은 급여를 받는 사원을 검색
--내가 처음에 틀리게 작성한 쿼리문
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL > (SELECT ENAME FROM EMP WHERE ENAME = 'SCOTT');
--맞는 쿼리문 ★서브쿼리에서 SAL을 검색대상으로 두어야 함
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SCOTT');

--EMP테이블에서 사원번호가 7844인 사원과 같은 업무를 하는 사원을 검색
SELECT EMPNO, ENAME, SAL, JOB FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO = 7844) 
				AND EMPNO <> 7844;

--사원번호가 7521인 사원과 같은 업무를 하는 사원 중 사원번호가 7900인 사원?
SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO = 7521)
	AND EMPNO <>7521 AND SAL > (SELECT SAL FROM EMP WHERE EMPNO = 7900);

--EMP테이블에서 SALES 부서에 근무하는 사원?
--부서이름은 DEPT테이블에 저장되어있으므로 조인을 해야한다
SELECT EMPNO, ENAME, JOB, SAL FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO WHERE DNAME = 'SALES';

--테이블 조인대신 서브쿼리를 이용하여 검색가능(더 빠르다)
SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');

--EMP테이블에서 급여를 가장 적게받는 사원?
SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP);

--EMP테이블에서 SALES부서에 근무하는 사원중 급여를 가장 많이 받는 사원?
--오라클은 서브쿼리 내부에 다른 서브쿼리 사용 가능
SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES'));

--EMP테이블에서 부서별 평균 급여 중 가장 많은 평균 급여를 받는 부서의 부서번호, 평균급여 검색
--GROUP BY구문에서 HAVING절을 이용해서 서브쿼리도 쓸 수 있음
SELECT DEPTNO, CEIL(AVG(SAL)) AVG_SAL FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) = (SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO);

 

[다중 행 비교 연산자]

 

IN	서브쿼리의 결과에 존재하는 임의의값과 동일한 조건인 결과를 반환
ALL	서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미함 -즉, 메인쿼리는 서브쿼리의 모든 결과를 만족해야 하므로 서브쿼리 결과의 최대값보다 큰 모든 건이 조건을 만족해야한다.
ANY	서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미함 - 즉, 메인쿼리는 서브쿼리의 모든 결과를 만족해야 하므로 서브쿼리 결과의 최소값보다 큰 모든 건이 조건을 만족해야한다.

 

--EMP테이블에서 부서별 최소 급여를 받는 사원의 사원번호 사원이름 부서번호
--SINGLE LOW에러가 뜬다.
--서브쿼리의 행이 3개(다중 행)가 나오기 때문에 나오는 오류이다.
--다중행인 경우 = 대신 선택연산자인 IN 연산자를 사용하면 된다.
SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);

• 서브쿼리의 검색값이 다중행인 경우 > 또는 < 연산자는 ANY 또는 ALL키워드를 같이 사용하여 검색하면된다.
• 다중행 서브쿼리 ANY 또는 ALL을 사용 대신 MIN, MAX 함수의 단일행 서브쿼리사용이 가능하다

--서브쿼리의 검색값이 다중행인 경우 > 또는 < 연산자는 ANY 또는 ALL키워드를 같이 사용하여 검색하면된다.
--EMP테이블에서 부서번호가 10인 부서에 근무하는 모든 사원보다 급여를 적은 사원?
--모든 사원보다 적은 사원을 고르는 것이기 때문에 ALL을 사용해야한다
--만약 어떤 사원보다~ 라면 ANY사용 -> 어떠한 사원보다 작은 사원을 출력하면된다.( DEPTNO =10인 부서에서 5000이 젤 크므로 5000보다 작은 사원들이 출력됨)
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 10) AND DEPTNO = 10;

--같은 결과이다.
--멀티행
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE SAL < ALL (SELECT SAL FROM EMP WHERE DEPTNO = 10) AND DEPTNO = 10;
--단일행
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE SAL < (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 10) AND DEPTNO <> 10;

 

- (SELECT SAL FROM EMP WHERE DEPTNO = 10)

• ANY : 5000보다 작은 사원들 모두
• ALL : 1300보다 작은 사원들 모두(다중행 서브쿼리의 값과 모두 비교하시오)

--EMP테이블에서 부서번호가 20인 부서에 근무하는 모든 사원보다 급여를 많은 사원?
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE SAL > ANY (SELECT SAL FROM EMP WHERE DEPTNO = 10) AND DEPTNO <> 10;

 

- (SELECT SAL FROM EMP WHERE DEPTNO = 20)

• ANY : 800보다 많은 사원들 모두
• ALL : 3000보다 많은 사원들 모두

 

--EMP테이블에서 사원이름이 ALLEN인 사원과 같은 매니저인 사원 중 같은 업무의 사원의 사원번호 사원이름, 
SELECT EMPNO, ENAME, MGR,JOB, SAL FROM EMP WHERE MGR = (SELECT MGR FROM EMP WHERE ENAME = 'ALLEN') 
                                            AND JOB = (SELECT JOB FROM EMP WHERE ENAME = 'ALLEN') AND ENAME <> 'ALLEN';

 

[멀티컬럼 서브쿼리]

• 서브쿼리의 검색대상이 여러개인 경우이다.
• 즉, 서브쿼리의 결과로 여러개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미
• 비교대상을 ( )안에 , 로나열하면된다.

--멀티컬럼서브쿼리
-- : 서브쿼리의 검색대상이 여러개인 경우
SELECT EMPNO, ENAME, MGR,JOB, SAL 
FROM EMP 
WHERE (MGR,JOB) = (SELECT MGR,JOB FROM EMP WHERE ENAME = 'ALLEN') 
AND ENAME <> 'ALLEN';

 

[집합연산자(SET연산자)]

• 두개의 SELECT명령에 대한 검색결과를 이용하여 집합 결과값을 제공한다
• 합집합(UNION), 교집합(INTERSECT), 차집합(MINUS) 

CREATE TABLE SUPER_HIRO(NAME VARCHAR2(20));

INSERT INTO SUPER_HIRO VALUES('슈퍼맨');
INSERT INTO SUPER_HIRO VALUES('앤트맨');
INSERT INTO SUPER_HIRO VALUES('아이언맨');
INSERT INTO SUPER_HIRO VALUES('베트맨');
INSERT INTO SUPER_HIRO VALUES('스파이더맨');
COMMIT;
SELECT * FROM SUPER_HIRO;
--------------------------------------------------------------------
CREATE TABLE MARVEL_HIRO(NAME VARCHAR2(20), GRADE NUMBER(1));

INSERT INTO MARVEL_HIRO VALUES('아이언맨',3);
INSERT INTO MARVEL_HIRO VALUES('헐크',1);
INSERT INTO MARVEL_HIRO VALUES('스파이더맨',4);
INSERT INTO MARVEL_HIRO VALUES('토르',2);
INSERT INTO MARVEL_HIRO VALUES('앤트맨',5);
COMMIT;
SELECT * FROM MARVEL_HIRO;

 

1) UNION연산자 : 두 개의 SELECT명령으로 검색된 행을 합한 결과를 제공

• 똑같은 값은 하나만 표현됨
• 집합연산자를 사용할 두개의 SELECT 명령의 검색대상은 갯수와 자료형이 반드시 동일해야함
• 두개의 SELECT명령에 대한 검색대상의 갯수 또는 자료형이 다른 경우 ERROR
• ==> 검색대상의 갯수가 다른 경우 임의값을 사용해 주면 된다.
• ==>검색대상의 자료형이 다른 경우 변환함수를 이용해주면 된다.
• UNION ALL연산자를 사용할 경우 중복행을 포함한 결과를 출력한다.

--UNION연산자 : 두 개의 SELECT명령으로 검색된 행을 합한 결과를 제공
SELECT NAME FROM SUPER_HIRO
UNION
SELECT NAME FROM MARVEL_HIRO;
------------------------------
 
SELECT NAME FROM SUPER_HIRO 
UNION ALL 
SELECT NAME FROM MARVEL_HIRO;
 
------------------------------
--갯수가 다른 경우
 
SELECT NAME, 0 FROM SUPER_HIRO
UNION
SELECT NAME, GRADE MARVEL_HIRO
 
----------------------------
--자료형이 다른경우
SELECT NAME FROM SUPER_HIRO
UNION
SELECT TO_CHAR(GRADE, '0') FROM MARVEL_HIRO;
--여기서 0은 숫자 한자리라는 뜻!
 
 

UNION

UNION ALL

2) INTERSECT연산자 : 두 개의 SELECT 명령으로 검색된 중복행의 결과를 제공한다.

2) MINUS연산자 : 첫번째 SELECT명령의 검색 결과에서 두번째 SELECT명령의 검색결과를 제외한 결과행을 제공한다.

 

[DML]

• 데이터 조작어
• 테이블의 행에대한 삽입, 변경, 삭제 기능을 제공하는 SQL명령
• DML명령 실행 후, COMMIT(DML 명령의 적용) 도는 ROLLBACK(DML명령 실행 취소)명령을 실행할 것을 권장

 

1) INSERT : 테이블에 행을 삽입(저장)하는 명령

INSERT INTO [테이블명] VALUES(결과값...);

• 테이블에 삽입될 행의 컬럼값은 테이블 구조에 맞게 차례대로 나열하여 저장되도록 작성
• 테이블의 컬럼순서대로 자료형에 맞는 값을 생략없이 전달하여 저장해야함
• 그렇게 하기위해선 테이블의 구조(속성)을 확인해야한다

DESC DEPT;

INSERT INTO DEPT VALUES('50', '회계부', '서울');
COMMIT;
SELECT * FROM DEPT;

 

추가된 행

• 행에 전달되어 저장될 컬럼값의 갯수가 맞지 않은 경우 에러발생
• 행에 전달되어 저장될 컬럼값의 자료형이 맞지 않거나, 컬럼크기보다 큰 경우 에러가 발생
• 컬럼에 부여된 제약조건 위반하는 값이 전달될 경우 에러가 발생(EX> DEPTNO는 NOT NULL인데 NULL로 값을 주면 에러가 발생)
• PK제약조건 : 기존 행의 컬럼값과 중복된 값이 저장되지 않도록 설정하는 제약조건임(EX>이미 DEPTNO = 50 이 있는데 또 50으로 INSERT하려고 하면 ERROR발생
• 테이블 구조와 제약조건에 맞는 컬럼값을 전달하여 행을 삽입
• FK제약조건 : 컬럼값이 참조 테이블에 저장된 행의 컬럼값이 참조테이블에  저장된 행의 컬럼값일 경우에만 저장되도록 설정하는 제약조건
• 테이블에 행 삽입시 컬럼값을 저장하고 싶지 않는 경우 NULL을 전달하여 저장 : 명시적 NULL사용
• 단, NOT NULL제약조건이 있는 컬럼에는 NULL을 저장할 수 없음
• 선언된 테이블을 컬럼순서대로 컬럼값을 전달하여 행을 삽입할 수도 있다.
• 값을 저장할 때 날짜형 컬럼에는 날짜형 상수 대신 SYSDATE키워드를 사용하여 컬럼값 저장가능

--FK제약조건에 의해 ERROR가 발생
--45번 부서는 존재하지 않으므로 사원정보 저장이 불가능
INSERT INTO EMP VALUES(9000, 'KIM', 'MANAGER', 7298,'00/12/10', 3500, 1000, 45);
INSERT INTO EMP VALUES(9000, 'KIM', 'MANAGER', 7298, '00/12/10', 3500, 1000, 40);
COMMIT;
SELECT * FROM EMP;
 
--테이블에 행 삽입시 컬럼값을 저장하고 싶지 않는 경우 NULL을 전달하여 저장
INSERT INTO DEPT VALUES(70,'영업부',NULL) ;
COMMIT;
SELECT * FROM DEPT;
 
--선언된 테이블을 컬럼순서대로 컬럼값을 전달하여 행을 삽입한다.
INSERT INTO DEPT(LOC, DEPTNO, DNAME) VALUES('수원',80,'자재부');
COMMIT;
SELECT * FROM DEPT;
--이경우, 컬럼값 생략도 가능하다
INSERT INTO DEPT(DEPTNO, DNAME) VALUES(90,'인사부');--묵시적 NULL사용
COMMIT;
--생략된 컬럼에는 컬럼기본값이 자동저장됨
--테이블 생성이나 변경시 컬럼 기본값을 설정 할 수 있으며, 서정하지 않은 경우, 
--컬럼기본값은 자동으로 NULL로 지정됨
 

--SYSDATE를 이용하여 오늘날짜 시간 저장 가능
INSERT INTO EMP VALUES(9001, 'LEE', 'ANALYST' , 9000, SYSDATE, 2000, NULL, 40);
COMMIT;
SELECT * FROM EMP WHERE EMPNO = 9001;

• INSERT명령에서 서브쿼리 사용 가능 : 테이블에서 행을 검색하여 다른 테이블에 행 삽입

INSERT INTO 테이블 명 SELECT 검색대상...FROM 테이블 명 WHERE 조건식

• 행이 삽입될 테이블의 컬럼과 서브쿼리의 검색대상의 구조(컬럼갯수, 자료형, 크기) 가 동일 - 컬럼명 제외
• EX) EMP테이블에서 성과급이 NULL이 아닌 사원을 검색하여 BONUS테이블에 (한번에) 행삽입이 가능하다

INSERT INTO BONUS SELECT ENAME, JOB, SAL, COMM FROM EMP WHERE COMM IS NOT NULL;
COMMIT;
SELECT * FROM BONUS;

 

2) UPDATE: 테이블명에 저장된 행의 컬럼값을 변경하는 명령

UPDATE  테이블 명 SET 컬럼명=변경값, ... WHERE 조건식

• WHERE구문을 생략할 경우 테이블에 저장된 모든 행의 컬럼값을 동일하게 변경한다.
• 조건식에 사용될 컬럼은 PK제약조건이 설정된 컬럼을 사용하는 것을 권장
• 단, PK제약조건이 설정된 컬럼값은 변경하지 않는 것을 권장한다.

SELECT * FROM DEPT WHERE DEPTNO = 50;
UPDATE DEPT SET DNAME = '경리부', LOC = '부천' WHERE DEPTNO = 50;
COMMIT;
SELECT * FROM DEPT;

• 변경값은 컬럼의 자료형, 크기, 제약조건에 맞는 경우에만 변경가능
• 서브쿼리도 사용가능 : 변경값 또는 조건식의 비교값 대신 서브쿼리로 사용가능

UPDATE DEPT SET LOC = (SELECT LOC FROM DEPT WHERE DNAME = '총무부') WHERE DNAME = '영업부';

--보너스 테이블에서 사원이름이 KIM인 사원보다 성과급이 작은 사원의 성과급을 100증가시켜라
UPDATE BONUS SET COMM = COMM + 100 WHERE COMM < (SELECT COMM FROM BONUS WHERE ENAME ='KIM');

 

3) DELETE: 테이블에 저장된 행을 삭제하는 명령

DELETE FROM 테이블 명  WHERE 조건식

• WHERE구문을 생략할 경우 테이블에 저장된 모든 행 삭제
• 조건식에 사용될 컬럼은 PK제약조건이 설정된 컬럼을 사용하는 것을 권장
• FK제약조건에 의해 자식테이블이 있는 부모테이블의 경우 삭제 불가능 하다.

DELETE FROM DEPT WHERE DEPTNO = 90;

--10번에 일하고있는 사원들이 있는데 얘를 삭제하면 FK제약조건에 맞지 않으므로 에러발생
--FK 제약조건에 의해 자식 테이블이 참조하는 부모테이블의 행은 삭제 불가능
DELETE FROM DEPT WHERE DEPTNO = 10;

--자식테이블이 있는 부서 확인
SELECT DISTINCT DEPTNO FROM EMP;

• 조건식의 비교값대신 서브쿼리 사용

DELETE FROM DEPT WHERE LOC = (SELECT LOC FROM DEPT WHERE DNAME = '영업부');

 

3) MERGE : 소스테이블의 행을 이용하여 티켓테이블에 행을 삽입하거나 행의 컬럼값을 변경하는 명령

MERGE INTO 타켓테이블 USING 소스테이블 ON (조건식) WHEN MATCHED THEN UPDATE SET 타겟컬럼명 = 소스컬럼명, ..... WHEN NOT MATCHED THEN INSERT (타겟컬럼명,타겟컬럼명...) VALUES (소스컬럼명, 소스컬럼명...)

 

CREATE TABLE MERGE_DEPT(DEPTNO NUMBER(2) , DNAME VARCHAR2(10), LOC VARCHAR2(11));
DESC MERGE_DEPT;
 
INSERT INTO MERGE_DEPT VALUES(30, '총무부', '서울');
INSERT INTO MERGE_DEPT VALUES(60, '자재부', '인천');
COMMIT;
SELECT * FROM MERGE_DEPT; --타겟테이블
SELECT * FROM DEPT; --소스테이블
 
--DEPT테이블에서 모든 부서정보를 MERGE_DEPT 테이블 삽입 또는 변경
MERGE INTO MERGE_DEPT M USING DEPT D ON (M.DEPTNO = D.DEPTNO)
WHEN MATCHED THEN UPDATE SET M.DNAME = D.DNAME, M.LOC = D.LOC
WHEN NOT MATCHED THEN INSERT (M.DNAME, M.DEPTNO, M.LOC) VALUES(D.DNAME, D.DEPTNO, D.LOC);
 

---

[정리]

• where/having같은 경우 단일행의 단일컬럼으로 쓰는것이 기본이다.
• 조건으로 써야하기 때문
• any all은 max min의 단일 컬럼으로 쓰는 것이 더욱 효율적이다
• 멀티컬럼의 경우 괄호안에 넣어 비교하도록한다(별로 권장하지는 않는다)
• insert를 할 때에는 컬럼의 순서와 제약조건을 유의해야 한다.
• insert into table(컬럼, 컬럼...) values(값,값,,,)할때,
• 컬럼의 default값이 있으므로 컬럼을 표현하지 않으면 자동으로 기본값이 저장된다.
• default값을 create할때 설정해주지 않으면 null이 저장된다.
• group by는 보통 검색대상이 그룹일때(?)사용해야한다. 아무때나 사용하면 안된다 ㅠ