8/10 서브쿼리, DML(INSERT, UPDATE, DELETE, MERGE), DELETE와 DROP과 TRUNCATE 비교

[서브쿼리의 개념]

서브쿼리가 먼저 실행되고 메인쿼리가 실행된다.

--SUB Query(중요, 별 3개)--
--하나의 SQL 명령문의 결과를 다른 SQL 명령문에 전달하기 위해 
--두 개 이상의 SQL 명령문을 하나의 SQL명령문으로 연결하여 처리하는 방법
--1-1. 목표 : 교수 테이블에서 ‘전은지’ 교수와 직급이 동일한 모든 교수의 이름 검색(서브쿼리 이용하지 않음)
--1) 교수 테이블에서 ‘전은지’ 교수의 직급 검색 SQL 명령문 실행
SELECT position FROM professor WHERE name='전은지';
--2) 교수 테이블의 직급 칼럼에서 1 에서 얻은 결과 값과 동일한 직급을 가진 교수 검색 명령문 실행
SELECT name, position FROM professor WHERE position='전임강사';
--1-2. 목표 : 교수 테이블에서 ‘전은지’ 교수와 직급이 동일한 모든 교수의 이름 검색(서브쿼리 이용)
SELECT name, position FROM professor
WHERE position = (SELECT position FROM professor WHERE name = '전은지');

 

--종류
--1. 단일행 서브쿼리
--서브쿼리에서 단 하나의 행만을 검색하여 메인쿼리에 반환하는 질의문
--메인쿼리의 WHERE 절에서 서브쿼리의 결과와 비교할 경우에는 반드시 단일행 비교 연산자 중 하나만 사용해야함
--단일행은 서브쿼리(괄호 안)의 결과값이 하나만 나오는 것이 단일행이다.
--문1) 사용자 아이디가 ‘jun123’인 학생과 같은 학년인 학생의 학번, 이름, 학년을 출력하여라
SELECT studno, name, grade FROM student
WHERE grade = (SELECT grade FROM student WHERE userid = 'jun123');
--문2)101번 학과 학생들의 평균 몸무게보다 몸무게가 적은 학생의 이름, 학과번호, 몸무게를 출력(학과 별 오름차순 출력)
SELECT name, deptno, weight
FROM student 
WHERE weight < (SELECT AVG(WEIGHT) FROM student WHERE deptno = 101)
ORDER BY deptno;
--문3) 20101번 학생과 학년이 같고, 키는 20101번 학생보다 큰 학생의 이름, 학년, 키를 출력하여라
SELECT name, grade, height
FROM student
WHERE grade = (
    SELECT grade
    FROM student 
    WHERE studno = '20101')
and height > (
    SELECT height
    FROM student 
    WHERE studno ='20101');
--문4) 101번 학과 학생들의 평균 몸무게보다 몸무게가 적은 학생의 이름, 학과번호, 몸무게를 출력하여라. (학과별 출력)
SELECT name, deptno, weight FROM student
WHERE weight < 
( SELECT AVG(weight) FROM student WHERE deptno = '101')
ORDER BY deptno;

 

 

--2. 다중행 서브쿼리
--서브쿼리에서 반환되는 결과 행이 하나 이상일 때 사용하는 서브쿼리
--메인쿼리의 WHERE 절에서 서브쿼리의 결과와 비교할 경우에는 다중 행 비교 연산자를 사용하여 비교
--다중 행 비교 연산자 : IN, ANY, SOME, ALL, EXISTS
--1) IN : 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 하나라도 일치하면(즉 or이면) 참, ‘=‘비교만 가능
--2) ANY, SOME : 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 하나라도 일치하면(즉 or이면) 참
--3) ALL : 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 모든값이 일치하면(즉 and이면) 참
--4) EXISTS : 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 만족하는 값이 하나라도 존재하면 참. (중요 : 많이 쓰이므로 알아두기)

--1) IN 연산자를 이용한 다중 행 서브쿼리
-- 괄호 안에 있는 값이 하나 이상이면 다중행 서브쿼리를 통해 받아야 한다.
-- 오류 발생 : "single-row subquery returns more than one row"
SELECT name, grade, deptno
FROM student
WHERE deptno = (
    SELECT deptno
    FROM department
    WHERE college = 100);
-- 오류 해결 : 다중행 쿼리를 사용
SELECT name, grade, deptno
FROM student
WHERE deptno IN (
    -- 101, 102
    SELECT deptno
    FROM department
    WHERE college = 100);
    
--2) ANY 연산자를 이용한 다중 행 서브쿼리
--문1) 모든 학생 중에서 4학년 학생 중에서 키가 제일 작은 학생보다 키가 큰 학생의 학번, 이름, 키를 출력하여라
SELECT studno, name, height
FROM student
WHERE height > ANY (
    -- 175,176,177
    -- 이 값 중 어떤것 보다도 커야 하므로(or 이므로) 175보다 크면 된다.
    SELECT height
    FROM student
    WHERE grade = '4');

--3) ALL 연산자를 이용한 다중 행 서브쿼리
--문2) 모든 학생 중에서 4학년 학생 중에서 키가 제일 큰 학생보다 키가 큰 학생의 학번, 이름, 키를 출력하여라
SELECT studno, name, height
FROM student
WHERE height  > ALL (
    -- 175,176,177
    -- 이 값 모두보다 커야 하므로(and 이므로) 177보다 커야 한다.
    SELECT height
    FROM student
    WHERE grade = '4');
    
--4) EXISTS 연산자를 이용한 다중 행 서브쿼리
--괄호 안의 값과 인과관계가 전혀 없다. 괄호 안을 만족하는 데이터가 하나라도 존재하면 실행하는 것임. 
--그래서 괄호 안의 select문은 중요하지 않고 
--괄호 안에 해당하는 데이터의 존재만 중요한 것이다.
--그래서 성능 최적화에 아주 좋다.
SELECT profno, name, sal, comm, position
FROM professor
WHERE EXISTS (
    SELECT position
    FROM professor
    WHERE comm IS NOT NULL
    );
SELECT profno, name, sal,comm, position
FROM professor
WHERE EXISTS  (
               SELECT sal
               FROM professor
               WHERE position = '교수'
               );

SELECT profno, name, sal, comm, position
FROM professor
WHERE EXISTS (
               SELECT * FROM EMP WHERE empno = 1000
               );
--문1) 보직수당을 받는 교수가 한 명이라도 있으면 
--모든 교수의 교수 번호, 이름, 보직수당 그리고 급여와 보직수당의 합을 출력
SELECT  profno, name, comm, NVL(comm, 0)+sal
FROM professor
WHERE EXISTS (
    SELECT comm
    FROM professor
    WHERE COMM IS NOT NULL);
--문2) 학생 중에서 ‘goodstudent’이라는 사용자 아이디가 없으면 1을 출력하여라
SELECT 1 USERID_EX
FROM dual
WHERE NOT EXISTS (
SELECT userid
FROM student
WHERE userid = 'goodstudent');

 

--3. 다중 컬럼 서브쿼리
--서브쿼리에서 여러 개의 칼럼 값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리
--메인쿼리의 조건절에서도 서브쿼리의 칼럼 수만큼 지정
--종류
--1) PAIRWISE : 괄호 안에서 칼럼을 쌍으로 묶어서 괄호 밖에서 동시에 비교하는 방식
--2) UNPAIRWISE : 괄호 안에서 칼럼별로 나누어서 비교한 후 괄호 밖에서 AND 연산을 하는 방식

--1) PAIRWISE 다중 칼럼 서브쿼리
--문1) PAIRWISE 비교 방법에 의해 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력하여라
SELECT name, grade, weight
FROM student
WHERE (grade, weight) IN (SELECT grade, MIN(weight)
                          FROM student
                          GROUP BY grade);
                          
--2) UNPAIRWISE 
--UNPAIRWISE 비교 방법에 의해 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력
SELECT name, grade, weight
FROM student
--group by로 들어간 게 in이라서 1 or 2 or 3 or 4가 됨.
WHERE grade IN(SELECT grade
               FROM student
               GROUP BY grade)
--괄호 안은 52, 70, 72, 42(전부 or)
AND weight IN (SELECT MIN(weight)
               FROM student
               GROUP BY grade);
--최종적으로는 and로 엮음.
--그래서 잘못 섞였기 때문에 UNPAIRWISE가 아닌 PAIRWISE를 사용하여야 한다.
--이렇게 의도하지 않은 결과가 나올 수 있기 때문에 잘 써야 한다.

 

--4. 상호연관 서브쿼리 ***(중요 : 별 3개)
-- 메인쿼리절과 서브쿼리간에 검색 결과를 교환하는 서브쿼리
--문1) 각 학과 학생의 평균 키보다 키가 큰 학생의 이름, 학과 번호, 키를 출력하여라
--실행 순서가 주고받기 때문에 상호연관 서브쿼리이다.
SELECT deptno, name, grade, height  --실행순서(1), (3)
FROM student s1
WHERE height > (SELECT AVG(height)
                FROM student s2
                --WHERE s2.deptno = 101     --괄호 안 쿼리문 테스트할 때 주석문 지우고 사용.
                WHERE s2.deptno = s1.deptno  --(2)
                )
ORDER BY deptno;

 

[과제]

 -------------  HW  -----------------------
--  1. Blake와 같은 부서에 있는 모든 사원에 대해서 사원 이름과 입사일을 디스플레이하라
SELECT e.ename, e.hiredate, deptno
FROM emp e
WHERE e.deptno = (
    SELECT e.deptno
    FROM emp e
    WHERE INITCAP(e.ename) = 'Blake');

--  2. 평균 급여 이상을 받는 모든 사원에 대해서 사원 번호와 이름을 디스플레이하는 질의문을 생성. 
--     단 출력은 급여 내림차순 정렬하라
SELECT empno, ename, sal
FROM emp
WHERE sal > (
    SELECT AVG(sal)
    FROM emp)
ORDER BY sal DESC;

--  3. 보너스를 받는 어떤 사원의 부서 번호와 
--      급여에 일치하는 사원의 이름, 부서 번호 그리고 급여를 디스플레이하라.
SELECT ename, deptno, sal
FROM emp
WHERE comm IS NOT NULL;

--정답
SELECT ename, deptno, sal
FROM emp
WHERE (deptno, sal) IN
    (SELECT deptno, sal
    FROM emp
    WHERE comm IS NOT NULL);

 

--데이터 조작어 (DML:Data Manpulation Language) (중요 : 별 2개)
--테이블에 새로운 데이터를 입력하거나 기존 데이터를 수정 또는 삭제하기 위한 명령어
--종류
--INSERT : 새로운 데이터 입력 명령어
--UPDATE : 기존 데이터 수정 명령어
--DELETE : 기존 데이터 삭제 명령어
--MERGE : 두개의 테이블을 하나의 테이블로 병합하는 명령어

--1) INSERT
--컬럼명을 지정 안하면 순서대로 들어감.
--컬럼에 데이터 전체 넣을 거면 컬럼명 지정 안해도 괜찮다. 대신 순서 지키기
--데이터 전체를 넣을 것이 아니라면 컬럼명을 지정해주는 것이 좋다.
INSERT INTO dept VALUES(71, '인사', '이대');
--컬럼 수에 맞지 않게 넣으면 오류남.
INSERT INTO dept VALUES(71, '인사');
--컬럼명을 지정하고 넣으면 데이터가 순서대로 들어감.
INSERT INTO dept (deptno, dname, loc) VALUES (72, '회계팀', '충정로');
--이렇게 컬럼명의 순서를 바꿔도 지정한 컬럼명 순서대로 데이터가 들어감.
INSERT INTO dept (deptno, loc, dname) VALUES (75, '회계팀', '충정로');
--컬럼명을 지정하고 컬럼수를 맞지 않게 넣으면 데이터를 넣지 않은 부분은 null이 됨.
INSERT INTO dept (deptno, loc) VALUES (73, '홍대');
--deptno가 primary key인데, primary key의 유일성을 위반하기 때문에 데이터가 들어가지 않음.
INSERT INTO dept (deptno, loc, dname) VALUES (75, '자재팀', '신대방');
--제약조건에서 not null로 설정한 항목에 null값을 넣으면 오류가 남.
INSERT INTO dept(deptno, loc) VALUES (77, '당산');

INSERT INTO professor (profno, name, position, hierdate, deptno) VALUES (9910, '백미선', '전임강사', '22/08/10', 101);
INSERT INTO professor (profno, name, position, hierdate, deptno) VALUES (9920, '최윤식', '조교수', '06/01/01', 102);
--하단 방법으로 해도 됨.
--INSERT INTO professor (profno, name, position, hierdate, deptno) VALUES (9910, '백미선', '전임강사', SYSDATE), 101);
--INSERT INTO professor (profno, name, position, hierdate, deptno) VALUES (9920, '최윤식', '조교수', TO_DATE('2006/01/01', 'YYYY/MM/DD', 102);

CREATE TABLE JOB3
--기본키에 별명을 지어주지 않으면 시스템이 임의로 짓기 때문에 관리하기 힘들다.
(jobno VARCHAR2(2) PRIMARY KEY,
 jobname VARCHAR2(20)
 );
 
INSERT INTO JOB3                  VALUES (10,'학교');
INSERT INTO JOB3(jobno,jobname)   VALUES (11,'공무원');
INSERT INTO JOB3(jobno,jobname)   VALUES (12,'공기업');
INSERT INTO JOB3                  VALUES (13,'대기업');
INSERT INTO JOB3                  VALUES (14,'중소기업');

CREATE TABLE Religion   
--이렇게 기본키에 별명을 지어주어야 제약조건의 이름을 잘 기억할 수 있기 때문에 좋다.
(   religion_no NUMBER(2) CONSTRAINT PK_ReligionNo3 PRIMARY KEY,
	 religion_name VARCHAR2(20)
) ;

INSERT INTO Religion(religion_no, religion_name)   VALUES (10, '기독교');
INSERT INTO Religion(religion_no, religion_name)  VALUES (20, '카톨릭교');
INSERT INTO Religion   VALUES (30, '불교');
INSERT INTO Religion   VALUES (40, '무교');

-- COMMIT은 지금까지 로그버퍼에 저장한 것을 물리적인 테이블에 진짜로 넣은 것
COMMIT;
-- ROLLBACK은 COMMIT을 취소해버리는 것.
ROLLBACK;

 

 

--- 다중 행 입력---
--1. 생성된 TBL이용 신규 TBL 생성
--모든 조건을 복사해서 신규 테이블을 만들어준다.
--이렇게 만들면 기본키는 만들어지지 않는다. 스키마와 데이터는 그대로 복사해서 만들어진다. 
CREATE table dept_second
AS SELECT * FROM dept;

-- 2. TBL 가공 생성
-- 원하는 레이어만 골라서 만들 수 있다.
CREATE TABLE emp20
AS SELECT empno, ename, sal*12 annsal
FROM emp
WHERE deptno = 20;

-- 3. TBL 구조만
-- 스키마만 있고 데이터는 없는 테이블을 만들 수 있다.
CREATE TABLE dept30
AS SELECT deptno, dname
FROM dept
WHERE 0=1;

-- 4. Column 추가
-- birth를 DATE라는 데이터 타입으로 컬럼을 추가(ADD)한다.
ALTER TABLE dept30 ADD(birth DATE);
-- 데이터를 넣는다.
INSERT INTO dept30 VALUES(10,'중앙학교', sysdate);

--5 Column 변경
--modify : 컬럼 변경하기
--원래는 dname의 데이터타입이 varchar2(14)인데 9바이트로 변경하는 것이다.
--하지만 dname에 저장돼있는 데이터 중 9바이트를 넘는 12바이트인 '중앙학교'가 있어서 컬럼의 길이를 변경할 수 없다는 오류가 발생한다
-- : "cannot decrease column length because some value is too big"
ALTER TABLE dept30
Modify dname varchar2(9);

-- 12바이트에서 20바이트로 변경. 변경이 잘 된다.
ALTER TABLE dept30
Modify dname varchar2(20);

--6 Column 삭제
ALTER TABLE dept30
Drop Column dname;

--7. TBL 명 변경
--테이블 이름을 dept30에서 dept35으로 변경한다.
RENAME dept30 TO dept35;

--8. TBL 제거
DROP Table dept35;

--9. Truncate 자르기
TRUNCATE table dept_second;

 

DELETE 와 TRUNCATE 의 비교 (중요 : 별 3개, 면접 단골 문제, 반드시 외우기)

- DELETE, TRUNCATE, DROP 셋 다 데이터를 삭제한다는 공통점이 있다.

- DELETE와 TRUNCATE는 테이블의 데이터만 삭제하는 반면, DROP은 테이블의 정의까지 삭제한다.

- DELETE는 DML이고 TRUNCATE와 DROP는 DDL이다. DML은 COMMIT을 하기 전 버퍼에 저장되기 때문에 작업 속도가 느리고 저장공간을 그대로 유지하지만 DDL은 자동 COMMIT이 되기 때문에 작업 속도가 빠르고 저장공간을 차지하지 않는다.

 

 

--테이블 만들기. 대소문자 구별하지 않기 때문에 테이블 이름에 언더바를 넣는 것임.
CREATE TABLE height_info
( studNo       number(5),   
	NAME        VARCHAR2(20),
  height      number(5,2)
) ;
CREATE TABLE weight_info
( studNo       number(5),   
	NAME        VARCHAR2(20),
  weight      number(5,2)
) ;

-- INSERT ALL(unconditional INSERT ALL) 명령문
-- 서브쿼리의 결과 집합을 조건없이 여러 테이블에 동시에 입력
-- 서브쿼리의 컬럼 이름과 데이터가 입력되는 테이블의 칼럼이 반드시 동일해야 함

INSERT ALL
INTO  height_info VALUES(studNo , name ,height)
INTO  weight_info VALUES(studNo , name ,weight)
SELECT studno , name , height , weight
FROM   student
WHERE  grade >= '2';

DELETE height_info;
DELETE weight_info;

-- 학생 테이블에서 2학년 이상의 학생을 검색하여 
-- height_info 테이블에는 키가 170보다 큰 학생의 학번, 이름, 키를 입력
-- weight_info 테이블에는 몸무게가 70보다 큰 학생의 학번, 이름, 몸무게를 
-- 각각 입력하여라

-- INSERT ALL 
-- [WHEN 조건절1 THEN
-- INTO [table1] VLAUES[(column1, column2,…)]
-- [WHEN 조건절2 THEN
-- INTO [table2] VLAUES[(column1, column2,…)]
-- [ELSE
-- INTO [table3] VLAUES[(column1, column2,…)]
-- subquery;

INSERT ALL
WHEN  height > 170 Then
      INTO  height_info VALUES(studNo , name ,height)
WHEN  weight > 70 Then
      INTO  weight_info VALUES(studNo , name ,weight)
SELECT studno , name , height , weight
FROM   student
WHERE  grade >= '2';

-- 데이터 수정 개요
-- UPDATE 명령문은 테이블에 저장된 데이터 수정을 위한 조작어
-- WHERE 절을 생략하면 테이블의 모든 행을 수정
--- Update 
-- 문1) 교수 번호가 9903인 교수의 현재 직급을 ‘부교수’로 수정하여라
UPDATE professor 
SET position = '부교수'
WHERE profno = 9903;

--  문2) 서브쿼리를 이용하여 학번이 10201인 학생의 학년과 학과 번호를
--        10103 학번 학생의 학년과 학과 번호와 동일하게 수정하여라
UPDATE student SET (grade, deptno) = (
    SELECT grade, deptno
    FROM student 
    WHERE studno = '10103')
WHERE studno = '10201';

-- 데이터 삭제 개요
-- DELETE 명령문은 테이블에 저장된 데이터 삭제를 위한 조작어
-- WHERE 절을 생략하면 테이블의 모든 행 삭제

-- 문1) 학생 테이블에서 학번이 20103인 학생의 데이터를 삭제
--삭제할 때는 기본키를 삭제하는 것을 권장한다.
DELETE 
FROM  student
WHERE studno = 20103;

--  문2) 학생 테이블에서 컴퓨터공학과에 소속된 학생을 모두 삭제하여라. HomeWork --> Rollback
delete FROM student
WHERE DEPTNO = (
    SELECT deptno
    FROM department
    WHERE dname = '컴퓨터공학과');