[DB]8.DML&서브쿼리

데이터베이스 08_ DML, 서브쿼리

 

[DML]

1. DML: 데이터 조작어, 테이블에 원하는 데이터를 입력/수정/삭제, 테이블에 있는 데이터를

변형시키는 명령어

 

2. INSERT: 테이블에 새로운 데이터를 행(튜플) 단위로 입력

 

● INSERT INTO, VALUES는 컬럼 리스트 순서, 데이터 타입을 맞춰서 입력해야 함

● 컬럼리스트를 생략하면 테이블 전체에 테이블 변형, VALUES에 입력할 값은 INSERT

테이블에 있는 컬럼 개수와 동일해야 함

 

[실습1]

--실습1. 
--부서명에 값 넣기
INSERT INTO 부서(부서ID, 부서명) VALUES(1, '연구개발팀');
INSERT INTO 부서(부서ID, 부서명) VALUES(2, '교육운영부');
INSERT INTO 부서(부서ID, 부서명) VALUES(3, '기획팀');
INSERT INTO 부서(부서ID, 부서명) VALUES(4, '홍보팀');
INSERT INTO 부서(부서ID, 부서명) VALUES(5, '외부강사');

--직원 테이블에 값 넣기
--NULL이 아닌 컬럼은 본인의 정보 입력, NULL이 가능한 컬럼은 NULL
--입사일은 날짜 함수를 사용해 오늘날짜로 입력
--직업 ID는 PLOGRAMMER로 통일
--부서ID는 원하는 부서 입력

INSERT INTO 직원(직원ID, 이름, 성, 이메일, 입사일, 직업ID, 부서ID)
VALUES(1, '철수', '김', 'chsue@gmail.com', SYSDATE, 'PROGRAMMER', 1);

 

3. UPDATE: 테이블에 저장된 데이터 변경

[실습2]

UPDATE 직원
SET 핸드폰번호 = '010-1234-5678'
    급여 =20000
    매니저ID=4
WHERE 직원ID =1;

 

4. DELETE: 테이블의 데이터 삭제

→조건이 없으면 테이블의 모든 데이터 삭제됨

 

[서브쿼리] 246p.

1. 서브쿼리: SQL문 내부에서 사용하는 SQL문(주로 SELECT)

     -서브쿼리에는 ;를 적지 않는다.

     -같은 테이블 내에서 여러 정보를 조회하고자 할 때 사용

[실습1]

--이름이 Shelli인 직원보다 급여가 낮은 직원들을 출력
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY< ( SELECT salary
                FROM EMPLOYEES
                WHERE FIRST_NAME = 'Shelli'
)
ORDER BY SALARY DESC;

 

1)단일행 서브쿼리: 결과가 단 하나의 행으로 나오는 서브쿼리

      →단일행연산자를 사용해서 비교

[실습2]

--실습2.
--이름이 Nancy인 사원보다 빨리 입사한(서브) 사원의 이름과 입사일 조회(메인)

SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE <= (SELECT HIRE_DATE FROM EMPLOYEES
                WHERE FIRST_NAME='Nancy')
ORDER BY HIRE_DATE DESC;

 

[실습3]

--실습3.
--평균급여(서브)보다 높은 급여를 받는 직원 조회(메인)

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY>(SELECT AVG(SALARY) FROM EMPLOYEES)
ORDER BY SALARY DESC;

 

2)다중행 서브쿼리: 결과가 여러 개의 행으로 나오는 서브쿼리

    →다중행 연산자를 사용해서 비교

  ● IN(=, OR 연산자): 메인쿼리의 데이터가 서브쿼리의 결과 중 하나라도 일치하면 TRUE

--부서별 최고급여와 같은 급여를 받는 직원 출력
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY IN(SELECT MAX(SALARY) 
                FROM EMPLOYEES 
                GROUP BY DEPARTMENT_ID)
ORDER BY SALARY;

→급여 중 부서별 최고급여와 하나라도 일치하면 WHERE 절이 TRUE

 

  ● ANY/SOME: 메인쿼리의 결과값과 서브쿼리가 반환한 결과값이 하나라도 TRUE이면 WHERE절을 TRUE로 반환

         →등가연산자(=)를 같이 쓰면 IN과 같은 결과 출력

→하나가 TRUE이므로 모든 값이 출력

 

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY = ANY(SELECT MAX(SALARY) 
                FROM EMPLOYEES 
                GROUP BY DEPARTMENT_ID)
ORDER BY SALARY;

 

  ● ALL: 메인쿼리의 결과값과 서브쿼리가 반환한 결과값이 전부 TRUE이면 WHERE절을 TRUE로 반환

      =일 때

          >하나만 TRUE이므로 출력되지 않음

     

  >=일 때

          >모두 TRUE이므로 출력

 

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY = ALL(SELECT MAX(SALARY) 
                FROM EMPLOYEES 
                GROUP BY DEPARTMENT_ID)
ORDER BY SALARY;

 

3)다중열 서브쿼리: 서브쿼리의 결과가 여러 컬럼으로 나오는 서브쿼리

   ● 조건에 여러 컬럼을 입력하여 비교한다. 조건절과 비교대상이 1:1 대응이 되어야함

        (메인쿼리의 컬럼, 서브쿼리의 컬럼 순서와 개수가 같아야 함)

   ● 주로 IN연산자와 같이 사용

SELECT FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE (DEPARTMENT_ID, SALARY) IN (SELECT DEPARTMENT_ID, MAX(SALARY) 
                                  FROM EMPLOYEES
                                  GROUP BY DEPARTMENT_ID)
 ORDER BY DEPARTMENT_ID;

 

4)FROM절 서브쿼리(인라인뷰): FROM절에서 가상테이블처럼 사용하는 서브쿼리

 --각 부서별 최고연봉(서브쿼리)을 받는 직원(INNER JOIN) 출력
 --JOIN조건: 1. 부서명이 같음 2. 부서의 최고연봉이 직원의 급여와 같음
 
 SELECT FIRST_NAME, DMAX.DEPARTMENT_ID, DMAX."부서별 최고급여"
 FROM EMPLOYEES E, 
 (SELECT DEPARTMENT_ID, MAX(SALARY) AS "부서별 최고급여"
  FROM EMPLOYEES
  GROUP BY DEPARTMENT_ID) DMAX
 WHERE E.DEPARTMENT_ID = DMAX.DEPARTMENT_ID
 AND E.SALARY = DMAX."부서별 최고급여"
 ORDER BY DEPARMENT_ID;

5)SELECT절 서브쿼리(스칼라 서브쿼리): SELECT절에 사용하는 서브쿼리

   (서브쿼리를 반복해야 비교해야 하므로 성능이 떨어져 사용 빈도수가 낮다.)

       ● SELECT 절에 사용되는 스칼라 서브쿼리의 결과는 1개로 나와야 한다.

       ● JOIN 없이 JOIN처럼 기능을 사용하고자 할 때 사용

 

[실습]

 --직원의 부서이름 출력
 SELECT FIRST_NAME, SALARY, DEPARTMENT_ID,
                (SELECT DEPARTMENT_NAME
                FROM DEPARTMENTS
                WHERE DEPARTMENT_ID = E.DEPARTMENT_ID) AS 부서이름
FROM EMPLOYEES E 
ORDER BY DEPARTMENT_ID;