1. set 연산자를 이해하고 기술할 수 있다.
2. subquery를 기술할 수 있다.
=====================================
SET연산자는 두 개 이상의 질의 결과를 하나의 결과로 통합하며 SET 연산자를 포함하는 질의를
복합 질의라고 한다. ----> (여기서 질의는 쿼리를 말함)종류는 다음과 같다.
- UNION : 두 질의 중 어느 것 하나에 의해서라도 선택된 모든 구분 (DISTINCT)행을 결과로 한다.
중복값 한번만 , 첫번째 컬럼을 기준으로 ASC 정렬한다.
- UNION ALL : 중복 행을 포함하여 두 질의 중 어느 것 하나에 의해서 선택된 모든 행을 결과로 한다.
첫번째 선택 문장에서 두번째 선택 문장과 일치하는 최소한 하나의 행을 리턴한다.
- INTERSECT : 두 질의에 공통으로 선택된 모든 구분 (DISTINCT) 행을 결과로 한다.
첫번째 선택 문장에서 두번째 선택 문장과 일치하는 최소한 하나의 행을 리턴한다.
-MINUS : 첫째 SELECT문에 선택되고, 둘째 SELECT문에서 선택되지 않은 모든 구분(DISTINCT)행을 결과로 한다.
(두번째 선택 문장에서는 선택되지 않는 첫번째 선택 문장에 의해 선택된 모든 유일한 행이다.)
모든 SET 연산자는 같은 우선순위를 가진다. SQL문장이 여러 개의 SET연산자를 포함한다면 데이터베이스는 명시적으로 다른 순서를 명시하는 괄호가 없을 경우 왼쪽 위에서 오른쪽 아래로 실행한다.
DB는 명시적으로 다른 순서를 명시하는 괄호가 없을 경우 왼쪽 (위)에서 오른쪽 (아래)로 실행을 한다.
-사용하는 질의의 평가 순서를 명시적으로 표현하기 위해서는 괄호를 사용한다.
[형식]
select from where group by having set 연산자select from where group by having order by..
============================================================================
- UNION : 컬럼의 개수가 두 개의 select 문장에 일치해야한다.
컬럼 이름은 같지 않아도 된다. 중복값은 하나만 리턴된다.
중복값은 하나만 리턴된다.
Where절에서 UNION을 사용하는 질의는 SELECT목록에 있는 것과 똑같은 수와
데이터 타입의 열을 가져야한다.
Q1) 데이터 생성을 해보자.
CREATE TABLE EMP_HISTORY(NAME , TITLE , DEPTID)
AS
SELECT ENAME , JOB , DEPTNO
FROM EMP;
DESC EMP_HISTORY;
Q2) EMP 테이블과 EMP_HISTORY 테이블의 내용을 UNION연산을 구현하자.
SELECT ENAME , JOB , DEPTNO
FROM EMP
UNION
SELECT NAME ,TITLE , DEPTID
FROM EMP_HISTORY;
SELECT ENAME , JOB , DEPTNO
FROM EMP
UNION
SELECT ENAME , JOB , DEPTNO
FROM TEST_EMP;
Q3) EMP 테이블과 EMP_HISTORY 테이블의 내용을 UNION ALL 연산으로 구현하자.
SELECT ENAME , JOB , DEPTNO
FROM EMP
UNION ALL
SELECT NAME ,TITLE , DEPTID
FROM EMP_HISTORY;
Q4) TEST_EMP , EMP 이용해서 INTERSECT 해보자.
SELECT ENAME , JOB , DEPTNO
FROM EMP
INTERSECT
SELECT NAME , JOB , DEPTNO
FROM TEST_EMP;
Q5) EMP 테이블과 EMP_HISTORY 테이블의 내용을 MINUS 연산으로 구현하자.
SELECT ENAME , JOB , DEPTNO
FROM EMP
MINUS
SELECT NAME ,TITLE , DEPTID
FROM EMP_HISTORY;
SELECT ENAME , JOB , DEPTNO
FROM TEST_EMP
MINUS
SELECT ENAME , JOB , DEPTNO
FROM EMP;
Q6) EMP , DEPT에서 부서번호 , 부서이름 , 입사일을 UNION 연산자를 이용해서 출력해보자.
SELECT DEPTNO , TO_CHAR (NULL) , HIREDATE
FROM EMP
UNION
SELECT DEPTNO , DNAME , TO_DATE (NULL)
FROM DEPT
ORDER BY 1;
============================================================================2. 서브쿼리를 기술할 수 있다.
- 정의 , 유형
- Single Row Subquery , Multi Row Subquery
- Scalar Subquery
- Correlated Subquery
- WITH 절과 Subquery
Scalar --> 프로그램 자료형을 의미한다. 단일값을 의미한다. 정수 , 문자 , 문자열 등
Q1 'JONES'보다 더 많은 월급을 받는 사원이 이름과 월급을 출력해보자.
SELECT ENAME , SAL FROM EMPWHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'JONES');
[형식] SELECT -----------> 주쿼리 FROM WHERE ( SELECT FROM WHERE) ---------------> 서브쿼리
1. 서브쿼리는 일반적으로 기본 질의 실행전에 한번 실행된다.
2. 서브쿼리의 결과는 기본질의 (외부질의 , 주쿼리: MAIN QUERY)에 사용된다.
3. 서브쿼리는 ( ) 묶여 있다.
4. 비교 조건의 오른쪽에 서브쿼리를 명시한다.
5. 일반적인 서브쿼리(Top-N분석 제외) order by절을 사용하지 않는다.
6. 단일행 연산자(> , < , >= , <= , == , !=) , 복수행 연산자 (in , any , all)을 사용한다.
7. 서브쿼리 사용위치는 SELECT , FROM , WHERE , GROUP BY , HAVING , ORDER BY , UPDATE , INSERT , DELETE문
============================================================================
Q2) 7566 사원보다 더 많은 월급을 받는 사원의 이름과 월급을 출력해보자.
select ename , sal
from emp
where sal = (select sal
from emp
where empno = 7566);
Q3) 급여의 평균보다 적은 사원의 사원번호 , 이름 , 직업 , 급여 , 부서번호를 출력하자.
SELECT EMPNO , ENAME , JOB , SAL , DEPTNO
FROM EMP
WHERE SAL < (SELECT AVG(SAL) FROM EMP);
Q4) 사원의 번호가 7521인 사원의 직업과 같고 급여가 7934 사원보다 많은 사원의 사원번호, 이름, 직업 , 입사일자 급여를 조회하자
SELECT EMPNO , ENAME , JOB , HIREDATE , SAL
FROM EMP
WHERE SAL >(SELECT SAL
FROM EMP
WHERE EMPNO = 7934)
AND
JOB = (SELECT JOB
FROM EMP
WHERE EMPNO = 7521);
Q5) 사원테이블에서 사원의 급여가 20번 부서의 최소 급여보다 많은 부사와 최소급여를 리천해보자.
SELECT DEPTNO , MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL) > (SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO = 20);
Q6) 사원테이블에서 직업중에서 가장 작은 평균 급여를 받는 직업을 조회하자.
SELECT JOB , AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) = (SELECT MIN(AVG(SAL))
FROM EMP
WHERE JOB = 'SALESMAN');
Q7) 직업이 SALESMAN인 이상의 사원보다 급여를 많이 받는 사원의 이름, 급여 , 직업을 출력
SELECT ENAME, SAL , JOB
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');
ORA-01427: 싱글로우 서브쿼리 리턴 모어 댄 원 로우
[다중 행 서브쿼리]
IN: 목록에 있는 임의의 값과 동일하다. DATA OR , DATA OR
ANY: 값을 반환하는 각각의 값과 개별 비교를 한다.
<ANY = 최대값보다 작음
>ANY = 최소값보다 크다
=ANY = IN연산자와 동일하다.
ALL: 서브쿼리에 의해 반환되는 모든 값과 비교한다.
< ALL = 최소값보다 작음
> ALL = 최대값보다 클 때
NOT: IN , ANY , ALL 연산자와 함께 사용된다.
Q8) 직업이 SALESMAN인 모든 사원보다 많이 받는 사원의 이름 봉급 직업 입사일 부서번호를 출력
SELECT ENAME, SAL , JOB , HIREDATE , DEPTNO
FROM EMP
WHERE JOB != 'SALESMAN'
AND
SAL >ALL (SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');
============================================================================ - MULTI COLUMN SUBQUERY (다중 열 서브쿼리): 서브쿼리의 결과값이 두 개 이상의 컬럼을 반환하는 서브쿼리로 혼합 WHERE을 작성한다. (중복된 WHERE조건을 하나의 WHERE 절로 결합시킨다.)
- NON - PAIRWISE 비교
Q9) FORD , BLAKE와 관리자 및 부서가 같은 사원의 정보를 출력
SELECT ENAME , MGR ,DEPTNO
FROM EMP
WHERE MGR IN (SELECT MGR FROM EMP WHERE ENAME IN('FORD','BLAKE'))
AND DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME IN('FORD' , 'BLAKE'))
AND ENAME NOT IN ('FORD','BLAKE')
- PAIRWISE 비교
Q9) FORD , BLAKE와 관리자 및 부서가 같은 사원의 정보를 출력
SELECT ENAME, MGR, DEPTNO FROM EMP WHERE (MGR, DEPTNO) IN (SELECT A.MGR, B.DEPTNO FROM EMP A, EMP B WHERE A.ENAME IN ('FORD', 'BLAKE')) AND ENAME NOT IN ('FORD','BLAKE') AND DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME IN('FORD', 'BLAKE'))
============================================================================
SELECT ENAME , MGR , DEPTNO
FROM EMP
WHERE(MGR,DEPTNO) IN ((7839,30),(7566,20));
============================================================================
Q11) CORRELATED SUBQUERY
SELECT
FROM TABLE MAIN
WHERE COLUMN1 OPERATOR ( SELECT
FROM TABLE2
WHERE EXPR1 = MAIN.EXPR2);
- 메인 쿼리의 한 ROW에 대해서 서브쿼리가 한번씩 실행된다.
- 테이블에서 행을 먼저 읽어서 각 행의 값을 관련된 데이터와 비교한다.
- 기본 질의에서 각 후보행에 대해 서브쿼리가 다른 결과를 반환해야 하는 경우 사용된다.
- 서브쿼리에서 메인 쿼리의 컬럼명을 사용할 수 있으나, 메인쿼리에서는 서브쿼리 컬럼명을 사용할 수 없다.
실행
- 후보행을 메인쿼리에서 가져온다 -> 후보행의 값을 사용해서 서브쿼리를 실행한다 -> 서브쿼리의 결과 값을 사용하여 후보행의 조건을 확인한다. -> 후보행이 남지 않을때까지 반복한다.
Q11) 사원이 속한 부서의 평균 급여보다 많은 급여를 받는 사원의 이름 급여 부서번호 입사일 직업을 출력하자.
(서브쿼리에서 메인 쿼리의 컬럼명을 사용할수 있다.)
SELECT ENAME ,SAL,DEPTNO,HIREDATE,JOB
FROM EMP E
WHERE SAL = (SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = E.DEPTNO);
============================================================================
Q12) FROM절에 서브쿼리 = INLINE VIEW
SELECT
FROM (SELECT....)
WHERE 조건식;
사원이 속한 부서의 평균 급여보다 많은 급여를 받는 사원의 이름 급여 부서번호 입사일 직업을 출력하자.
SELECT E,ENAME,E.SAL,E.DEPTNO,E.HIREDATE,D.AVGSAL
FROM EMP EMP E, (SELECT DEPTNO, AVG(SAL) AVGSAL
FROM EMP E
GROUP BY DEPTNO) D
WHERE E.DEPTNO = D.DEPTNO
AND E,SAL >D.AVGSAL;
============================================================================스칼라 서브쿼리
- 하나의 행에서 하나의 열 값만 반환하는 서브쿼리를 스칼라 서브쿼리라고 한다.
- SELECT, INSERT문의 VALUES목록,DECODE 및 CASE의 조건 및 표현식 부분, GROUP BY를 제외한 SELECT의 모든 절 ,UPDATE문의 SET절 및 WHERE절에서 연산자 목록
- 서브쿼리가 0개 행을 반환하면 스칼라 서브쿼리의 값은 NULL이다.
- 서브쿼리가 2개 이상일 때는 행을 반환하는 오류가 발생한다.