4월 4일 (1) DB - Subquery

프로젝트 발표

 

신규 입고의 경우 시간이 지나면 변경되기 때문에

프로그램으로 어떻게 연결되는지를 생각해서 데이터베이스에 확정된 코드로 넣어 변경하기보다는

따로 코드를 작성하여 넣을 수 있도록 바꿔 주는 것도 생각해 볼 수 있다.

 

ERD 관계 수정 - 개체끼리 연결된 부분

 

---

서브쿼리(Subquery)

하위질의문, 하나의 쿼리문이 여러 쿼리문으로 구성되는 형태

이 때 내부에 포함되는 쿼리문을 서브쿼리라고 한다.

 

형식 

SELECT 컬럼 목록

FROM 테이블

WHERE 컬럼 연산자(SELECT 컬럼 FROM 테이블 WHERE 조건);

 

-> () 안의 쿼리문을 서브쿼리라고 한다. INNER QUERY라고도 한다. 

특수한 경우 SELECT와 FROM 절에 QUERY문이 위치하기로 한다.

 

종류 (WHERE 절에 서브쿼리가 위치함)

1) 단일행 서브쿼리 : 서브쿼리의 실행결과가 하나의 행임, 결과 컬럼도 하나임, = 연산자로 처리, 가장 많이 사용

2) 다중행 서브쿼리 : 서브쿼리의 실행결과가 여러 행임, 서브쿼리의 결과 컬럼은 하나,

    IN(등가조건), ANY(비등가조건) 연산자로 처리가능

   

    다중행 서브퀄의 결과를 비등가 조건으로 처리할 수 있는 연산자

     ANY 연산자 : 서브 쿼리의 여러 결과 중 어느 하나라도 만족하면 결과로 가져오는 연산자

     ALL 연산자 : 서브 쿼리의 결과에 모두 만족하는 결과만 가져오는 연산자

 

3) 다중컬럼 서브쿼리 : 서브쿼리의 실행 결과가 여러 행, 여러 컬럼임, 두 번째로 많이 사용됨

 

작성 요령 

1) 문제를 먼저 분할한다. 예_ 직책JOB을 구하고, 사원정보 구하기

2) 분할된 문제를 해결할 쿼리문을 각각 작성한다.

3) 쿼리문을 결합하여 최종 쿼리문 완성

 

예

-- 예_단일행 서브쿼리
-- 사원번호가 7698번인 사원과 같은 직책(JOB)을 가진 사원 정보
-- 1) 사원번호가 7698번인 사원의 직책 구하기  
SELECT job FROM emp WHERE empno = 7698;

-- 2) 'MANAGER' 직책을 가진 사원들을 조회
SELECT * FROM emp WHERE job = 'MANAGER';

-- 3) 두 쿼리문을 결합한다.
SELECT * FROM emp WHERE job = (SELECT job FROM emp WHERE empno = 7698);
 
SELECT empno FROM emp;

-- 사원번호 7566번과 MGR(부서장)이 같은 사원의 정보를 조회하시오.
-- 1) 사원번호가 7566번호의 MGR을 조회
SELECT MGR FROM emp WHERE empno = 7566;

-- 2) MGR이 같은 사원들을 조회
SELECT * FROM emp WHERE MGR = 7839;

-- 3) 두 쿼리문을 결합한다.
SELECT * FROM emp WHERE MGR = (SELECT MGR FROM emp WHERE empno = 7566);

-- 예_다중행 서브쿼리
-- 부서별로 가장 많은 급여와 같은 급여를 받는 사원 정보를 조회
-- 1) 부서별 가장 많은 급여 조회
SELECT max(sal) FROM emp GROUP BY deptno;

-- 2) 각 급여에 해당하는 사원 조회
SELECT * FROM emp WHERE sal = 5500 OR sal = 3300 OR sal = 3135;
-- >
SELECT * FROM emp WHERE sal IN (5500, 3300, 3135);

-- 3) 두 쿼리문의 결함
SELECT * FROM emp WHERE sal IN (SELECT max(sal) FROM emp GROUP BY deptno);


-- JOB이 'SALESMAN' 인 사원보다 많은 급여를 받는 사원 정보 조회
-- 1)  JOB이 'SALESMAN' 인 급여조회
SELECT sal FROM emp WHERE job = 'SALESMAN';

-- 2) ANY 연산자 
SELECT * FROM emp WHERE sal >  ANY(SELECT sal FROM emp WHERE job = 'SALESMAN');
-- > ALL 연산자 모든 'SALESMAN'보다 급여가 많은 사원 정보 조회 
-- 최대치보다 큰 즉, 1700 보다 큰 값을 가진
SELECT * FROM emp WHERE sal >  ALL (SELECT sal FROM emp WHERE job = 'SALESMAN');

-- 예_다중 컬럼 서브쿼리
-- 부서번호가 30번이고, 커미션을 받는 사원과 같은 급여나 부서인 사원의 정보를 조회
SELECT sal, deptno FROM emp WHERE deptno = 30 AND comm IS NOT NULL;

SELECT * FROM emp
WHERE (sal, deptno) IN (SELECT sal, deptno FROM emp WHERE deptno = 30 AND comm IS NOT NULL);

-- 여러 테이블을 같이 조회할 때 서브쿼리를 많이 사용
-- 사원번호가 7654번인 사원의 부서명과 근무위치를 조회
SELECT * FROM emp;
SELECT * FROM dept;

-- 1) 7654번 사원의 부서번호 조회 
SELECT deptno FROM emp WHERE empno = 7654;

-- 2) 부서번호로 부서 테이블에서 부서명과 위치를 조회
SELECT dname, loc FROM dept WHERE deptno = 30;

-- 3) 두 쿼리 결합
SELECT dname, loc FROM dept WHERE deptno = (SELECT deptno FROM emp WHERE empno = 7654);

 

연습문제