SQL DML와 서브쿼리

1. DML

테이블에 원하는 데이터를 입력/수정/삭제

테이블에 들어 있는 데이터에 변형을 가하는 명령어

 

데이터 조작어 INSERT UPDATE DELETE ....

 

1.2 INSERT 

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

INSERT INTO 테이블(컬럼 리스트)
VALUES (컬럼 리스트에 입력 할 값들);

 

- 회원 테이블 추가

CREATE TABLE 네이버회원(
회원ID VARCHAR2(15),
이름 VARCHAR2 (12) NOT NULL,
비밀번호 VARCHAR2(16),
생년월일 DATE,
성별 VARCHAR2(3),
-- 제약조건 추가
CONSTRAINT 회원_ID_PK PRIMARY KEY(회원ID),
CONSTRAINT 회원_성별_CK CHECK(성별 in ('남', '여'))
);

 

- 회원 INSERT 추가

-- 회원 추가 INSERT
INSERT INTO 네이버회원 VALUES('simsim', '심심', '1234', '2023-05-15', '남');

 

1.2.1 특정 칼럼에만 값 넣어보기

 

- 계약 조건 오류

-- 특정 컬럼에만 값 넣어보기
INSERT INTO 네이버회원(회원ID, 비밀번호) VALUES ('aabbcc', '5678');

오류 보고 -
ORA-01400: cannot insert NULL into ("HR"."네이버회원"."이름")

이름 컬럼에는 NOT NULL 계약조건이 걸려있어서, NULL값이 들어갔기 때문에 오류 발생

 

- 정상 등록

INSERT INTO 네이버회원(회원ID, 비밀번호, 이름) VALUES ('aabbcc', '5678', 'ABC');

SELECT * FROM 네이버회원;

행이 추가 되며, 조회시 빈값은 NULL로 등록 되어있습니다.

 

- 칼럼리스트 초과 오류

INSERT INTO 네이버회원(회원ID, 비밀번호, 이름) VALUES ('123', '123456', '일이삼', '2023-05-15');

오류 보고 -
SQL 오류: ORA-00913: too many values
00913. 00000 -  "too many values"

칼럼리스트는 3개, 값은 4개라서 오류 발생

 

- 잘못된 타입 오류

INSERT INTO 네이버회원 VALUES('123', '일이삼', '12345', 2023-05-15, '남');

오류 보고 -
SQL 오류: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

생년월일 자리에는 DATE타입이 들어가야 되는데 숫자가 들어가서 오류 발생

 

- PK 중복 오류

INSERT INTO 네이버회원 VALUES('simsim', '심심', '1234', '2023-05-15', '남');

오류 보고 -
ORA-00001: unique constraint (HR.회원_ID_PK) violated

이미 들어가 있는 정보라서 중복되어서 PK에 위배되어 오류 발생

 

- CKECK 제약 조건 오류

INSERT INTO 네이버회원 VALUES('1234', '1234', '1234', '2023-05-15', '냠');

오류 보고 -
ORA-02290: check constraint (HR.회원_성별_CK) violated

CKECK 제약 조건에 위배되서 오류 발생

 

- 칼럼 크기 초과 오류

INSERT INTO 네이버회원 VALUES('1234', '1234 숫자 이름 입니다.', '1234', '2023-05-15', '남');

SQL 오류: ORA-12899: value too large for column "HR"."네이버회원"."이름" (actual: 29, maximum: 12)
12899. 00000 -  "value too large for column %s (actual: %s, maximum: %s)

한글이 3바이트, 영어 2바이트, 실제 19바이트, 이름 컬럼에 최대 크기 12바이트라 오류 발생

 

1.2.2 칼럼 크기 조절 방법

ALTER TABLE 네이버회원 MODIFY 이름 VARCHAR2(20);

 

1.2.3 실습

CREATE TABLE 부서(
부서ID NUMBER(4) not null,
부서명 VARCHAR2(30) not null,
매니저ID NUMBER(6),
지역ID NUMBER(4)
);

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값을 못 넣는 곳만 넣기
-- 입사일은 SYSDATE 함수를 이용하며, 부서는 원하는 곳에 넣기

SELECT * FROM 직원; -- not NULL 조건 확인 
INSERT INTO 직원(직원ID,이름,성,이메일,입사일,직업ID,부서ID)
VALUES(001, 'bbbccc','AAA','AABB@CC',SYSDATE,'PROGRAMMER','1');

 

1.2 UPDATE

- UPDATE : 테이블의 데이터를 변경

UPDATE 테이블
SET 변경할 칼럼 = 변경할 값;

 

직원 테이블에 추가

INSERT INTO 직원 VALUES (2, 'BB', 'B', 'BB@gmail.com', '010-0000-0000', sysdate, 'JAVA', 10000, NULL, NULL, 5);
INSERT INTO 직원 VALUES (3, 'CC', 'C', 'CC@gmail.com', '010-1111-1111', sysdate, 'JAVA', 10000, NULL, NULL, 2);
INSERT INTO 직원 VALUES (4, 'DD', 'D', 'DD@gmail.com', '010-2222-2222', sysdate, 'DB', 10000, NULL, NULL, 1);

 

UPDATE 직원
SET 직업ID = 'JAVA'
WHERE 직원ID = 4;

 

조건을 걸지 않으면 테이블 전체의 값이 변경!
조건을 꼭걸기

 

- 직원 1의 급여, 핸드폰, 매니져 ID 추가

UPDATE 직원
SET 급여 = 200000
WHERE 직원ID = 1;

UPDATE 직원
SET 핸드폰 = '010-1234-5678'
WHERE 직원ID = 1;

UPDATE 직원
SET 매니저id = 4
WHERE 직원ID = 1;

 

1.3 DELETE

 DELETE : 테이블의 테이터 삭제

DELETE FORM 테이블;

DELETE FROM 직원
WHERE 직원ID = 1;

 

2. 서브쿼리

서브쿼리 : SQL문장 내에 SQL문장이 들어가는 것

 

이름이 Shelli 인 직원 보다 급여가 낮은 직원들을 출력

SELECT salary
FROM employees
WHERE first_name = 'Shelli';

SELECT first_name, salary
FROM employees
WHERE salary < 2900
ORDER BY salary DESC;

- 서브쿼리를 사용해서 SQL 문장 1개로 만들어보기
1번 서브쿼리(Shelli의 급여), 2번 메인쿼리(Shelli의 급여보다 낮은 직원들)

SELECT first_name, salary
FROM employees
WHERE salary < (SELECT salary
                FROM employees
                WHERE first_name = 'Shelli')
ORDER BY salary DESC;

2.1 단일행 서브쿼리

단일행 서브쿼리 : 결과가 하나의 행으로 출력되는 서브쿼리, 단일행 연산자와 비교해서 사용

낸시보다 빨리 입사한 사원의 이름과 입사일을 조회

--Nancy 보다 빨리 입사한(서브) 사원의 이름과 입사일을 조회(메인)
SELECT first_name, hire_date
FROM employees
WHERE hire_date < (SELECT hire_date
                   FROM employees
                   WHERE first_name = 'Nancy');

- 평균 급여보다 높은 급여를 받는 직원 조회

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

SELECT first_name, salary
FROM employees
WHERE salary > (SELECT avg(salary)
                FROM employees)      
ORDER BY salary;

 

2.2 다중행 서브쿼리

다중행 서브쿼리 : 결과가 여러 행으로 나오는 서브쿼리
다중행 연산자 : IN, ANY, SOME, ALL, EXISTS

 

 

- IN : 메인쿼리의 데이터가 서브쿼리의 결과 중 하나라도 일치하면 true

-- 부서별 최고 급여와 같은 급여를 받은 직원을 출력

SELECT first_name, salary
FROM employees
WHERE salary in (SELECT max(salary)
                 FROM employees
                 GROUP BY department_id)
ORDER BY salary;

 

 

- ANYE, SOME : 메인쿼리의 조건식을 만족하는 서브쿼리의 결과가 하나 이상이면 TRUE 반환

SELECT first_name, salary
FROM employees
WHERE salary = ANY (SELECT max(salary)
                 FROM employees
                 GROUP BY department_id)
ORDER BY salary; 

---------------------------------------------------

SELECT first_name, salary
FROM employees
WHERE salary = SOME (SELECT max(salary)
                 FROM employees
                 GROUP BY department_id)
ORDER BY salary;

 

 

- ALL : 메인쿼리의 조건식을 만족하는 서브쿼리의 결과가 전부 TRUE여야 WHERE절에 TRUE로 반환

SELECT first_name, salary
FROM employees
WHERE salary = All (SELECT max(salary)
                 FROM employees
                 GROUP BY department_id)
ORDER BY salary;

SELECT first_name, salary
FROM employees
WHERE salary >= All (SELECT max(salary)
                 FROM employees
                 GROUP BY department_id)
ORDER BY salary;

 

2.3 다중열 서브쿼리

다중열 서브쿼리 : 컬럼 여러개로 나오는 서브쿼리, 비교 대상과 1:1 대응 해야한다.

각 부서별 최고 연봉을 받는 직원을 출력

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;

 

- FROM절

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

WHERE 직원테이블의 부서ID = 인라인 뷰의 부서ID
AND 직원테이블의 급여 = 인라인 뷰의 부서별 최고 급여

- 부서별 최고 연봉을 받는 직원 조회

SELECT E.first_name, D.department_id, D."부서별 최고 급여"
FROM employees E,
               (SELECT department_id
                     , MAX(salary) AS "부서별 최고 급여"
                FROM employees
                GROUP BY department_id) D

WHERE E.department_id = D.department_id
    and E.salary = D."부서별 최고 급여"

ORDER BY D.department_id;

 

- 스칼라 서브쿼리

스칼라 서브쿼리 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;