SUBQUERY / DDL/ DML

위치에 따라

Subquery : WHERE절
subquery의 결과를 조건에 활용하는 방식으로 사용. 

WHERE 필드명 IN (SUBQUERY)

 

InlineVies : FROM절

ScalarQuery : SELECT

 

실행결과 행수에 따라

단일행 서브쿼리 : 메인쿼리와 일반비교연산 ( =, >, <, >=, <=, <> )

여러행 서브쿼리 : 메인쿼리와 특수비교연산 (IN, ANY, ALL)

 

사원의 최대 급여를 출력하시오

SELECT MAX(salary)

FROM employees;

 

 

사원의 최대 급여자의 사번, 이름, 급여를 출력하시오

1) 사원의 최대 급여를 계산한다

2) 1)과 같은 급여갖는 사원 검색, 출력

SELECT employee_id, first_name, salary

FROM employees

WHERE salary = (SELECT MAX(salary))

                             FROM employees

                            );

 

 

성(last_name)이 'Davies'인 사원과 같은 부서에 근무하는 사원들의 사번, 성, 이름을 출력하시오 --44건

1)  성(last_name)이 'Davies'의 부서번호를 검색한다

2) 1)과 같은 부서번호를 갖는 사원 검색, 출력

--Davies는 제외하고 출력한다. 

SELECT employee_id, last_name, first_name

FROM employees

WHERE department_id = (SELECT department_id

                                           FROM employees

                                           WHERE last_name = 'Davies')

AND last_name <> 'Davies' ;

 

 

여러행 서브쿼리

부서별 최대 급여를출력하시오 --12건

SELECT department_id, MAX(salary)

FROM employees

GROUP BY department_id;

 

 

부서별 최대급여자의 사번, 이름, 급여를 출력하시오

1) 부서별 최대급여 계산

2) 1)과 같은 급여를 갖는 사원 검색, 출력

SELECT employee_id, first_name, salary

FROM employees

WHERE salary IN (SELECT MAX(salary)

                           FROM employees

                           GROUP BY department_id

                           ); --24건(x)

 

 

SELECT employee_id, first_name, salary

FROM employees

WHERE (department_id, salary) IN (SELECT department_id, MAX(salary)

                                                          FROM employees

                                                          GROUP BY department_id

                                                          ); --11건(부서없는 사원은 제)

 

 

=>WHERE (department_id, salary) IN (SELECT department_id, MAX(salary)

    쌍을 맞춰줘야함.

 

 

SELECT department_id, employee_id, first_name, salary

FROM employees

WHERE (NVL(department_id, 0), salary) IN (SELECT NVL(department_id, 0), MAX(salary)

                                                                      FROM employees

                                                                      GROUP BY department_id

                                                                       ); -- 12건(부서없는 사원도 출력)

 

 

 

부서별 최대 급여자를 제외한 사원의 부서번호, 사번, 이름, 급여를 출력하시오

1) 부서별 최대급여자의 부서번호, 최대급여 계산

2) 1)과 다른 부서번호, 급여를 갖는 사원을 검색, 출력한다

 

 

= ANY는 IN과 같음, >ANY : 최소값보다 크다, <ANY : 최대값보다 작다

= ALL : 결과없음 , >ALL : 최대값 보다 크다 , <ALL : 최소값보다 작다

SELECT department_id, employee_id, first_name, salary

FROM employees

WHERE (NVL(department_id, 0), salary) =ANY (SELECT NVL(department_id, 0), MAX(salary)

                                                                      FROM employees

                                                                      GROUP BY department_id

                                                                       ); -- 12건(부서없는 사원도 출력)

 

 

InlineView

행번호(rownum)는 1부터 시작한다, 조건에 만족하는 행을 만나면 rownum이 1씩 증가한다

 

SELECT 처리순서 : FROM -> WHERE-> GROUP BY-> HAVING -> SELECT -> ORDER BY

 

사원의 행번호, 사번, 급여를 출력하시오

SELECT rownum, employee_id, salary

FROM employees;

 

입사일자가 '07/01/01' 이후 입사한 사원의 행번호, 사번, 입사일자, 급여를 출력하시오

SELECT rownum, employee_id, hire_date, salary

FROM employees

WHERE hire_date >= '07/01/01' ;

 

입사일자가 '07/01/01' 이후 입사한 사원의 행번호, 사번, 입사일자, 급여를 출력하시오

적은 급여부터자 부터 출력한다

SELECT rownum, employee_id, hire_date, salary

FROM employees

WHERE hire_date >= '07/01/01' 

ORDER BY salary;

 

=> SELECT rownum 처리 후 ORDER BY salary 순서로 처리되었기 때문에 ROWNUM순차는 뒤섞이게 됨.

 

SELECT rownum, employee_id, hire_date, salary

FROM (

            SELECT rownum, employee_id, hire_date, salary

            FROM employees

            WHERE hire_date >= '07/01/01' 

            ORDER BY salary

);

 

 

 

입사일자가 '07/01/01' 이후 입사한 사원의 행번호, 사번, 입사일자, 급여를 출력하시오

적은 급여부터자 부터 출력한다

SELECT rownum, employee_id, hire_date, salary

FROM (

            SELECT rownum, employee_id, hire_date, salary

            FROM employees

            WHERE hire_date >= '07/01/01' 

            ORDER BY salary

)

WHERE rownum < = 5 ; 

 

 

입사일자가 '07/01/01' 이후 입사한 사원의 행번호, 사번, 입사일자, 급여를 출력하시오

적은급여자부터 6행부터 10행만 출력한다

SELECT rownum, employee_id, hire_date, salary

FROM (

            SELECT rownum, employee_id, hire_date, salary

            FROM employees

            WHERE hire_date >= '07/01/01' 

            ORDER BY salary

)

WHERE rownum BETWEEN 6 AND 10; (X)

=> WHERE rownum BETWEEN 6 AND 10; -- WHERE절 초기값은 1인데 6AND10 조건에 맞지 않아

결과 값이 나오지 않음

 

 

SELECT * -- ( = rn, employee_id 사번, )

FROM (SELECT rownum rn, a.*

            FROM (

                        SELECT employee_id, hire_date, salary

                        FROM employees

                        WHERE hire_date >= '07/01/01' 

                        ORDER BY salary

             ) a

)

WHERE rn BETWEEN 6 AND 10; 

 

5. Scalar Subquery

사원의 사번, 이름, 부서번호, 부서명을 출력하시오

SELECT employee_id, first_name, d.department_id, department_name

FROM employee e JOIN departments d ON (e.department_id = d.department_id) ;

 

SELECT employee_id, first_name, department_id,

              (SELECT department_name

               FROM departments

               WHERE department_id = employees.department_id)

FROM employees ; 

 

사원의 부서별 부서번호, 부서명, 총급여를 출력하시오. 부서 없는 사원도 출력한다

SELECT e.department_id, department_name, SUM(salary)

FROM employees e LEFT JOIN departments d ON (e.department_id = d.department_id)

GROUP BY e.department_id, department_name;

 

SELECT department_id, 

              (SELECT department_name

               FROM departments

               WHERE department_id = employees.department_id),

               SUM(salary)

FROM employees

GROUP BY department_id;

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

'Sales' 부서번호: 80번

SELECT * FROM departments WHERE department_name = 'Sales';

Sales 평균급여: 8955.8823~
SELECT AVG(salary) FROM employees WHERE department_id = 80;

 

sales평균급여보다 많은 급여자

SELECT employee_id, department_id, salary

FROM employees

WHERE department_id =80 AND salary > 8955.8823~

 

sales평균급여보다 많은  sales부서의 급여자

 

Sales 부서의 평균급여를 출력하시오

SELECT AVG(salary)
FROM employees

WHERE department_id =

(SELECT department_id FROM departments WHERE department_name = 'Sales');

 

 

SELECT e.department_id, department_name, AVG(salary) FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE department_name = 'Sales' 

 

 

Sales 부서의 평균급여보다 많은 급여를 받는 사원의 부서번호, 급여를 출력하시오

SELECT employee_id, department_id, salary

FROM employees

WHERE salary > (SELECT AVG (salary)

                           FROM employees

                           WHERE department_id = (SELECT department_id

                                                                     FROM departments

                                                                      WHERE department_name = 'Sales')

                             );

 

Sales 부서의 평균급여보다 많은 급여를 받는 'Sales'부서사원의 부서번호, 급여를 출력하시오 -- subquery편리

SELECT employee_id, department_id, salary

FROM employees

WHERE department_id =  (SELECT department_id

                                                                      FROM departments

                                                                      WHERE department_name = 'Sales')

AND         salary > (SELECT AVG (salary)

                               FROM employees

                               WHERE department_id = (SELECT department_id

                                                                          FROM departments

                                                                          WHERE department_name = 'Sales')

                             );

 

 

 

상호 연관 서브쿼리로 해결

SELECT employee_id, department_id, salary

FROM employees e

WHERE department_id =  (SELECT department_id

                                                                      FROM departments

                                                                      WHERE department_name = 'Sales')

AND         salary > (SELECT AVG (salary)

                               FROM employees

                               WHERE department_id = e.department_id) ;

 

DDL (데이터 정의어) - 객체를 생성: CREATE , 구조변경:ALTER, 제거:DROP
CREATE 객체종류
CREATE TABLE
ex) CREATE TABLE, CREATE VIEW, CREATE SEQUENCE

1. 테이블 생성
CREATE TABLE product(
 prod_no VARCHAR2(5), 
 prod_name VARCHAR2(20)  
); 

테이블구조확인
DESC product

2. 테이블구조 변경
ALTER TABLE product 
ADD prod_price NUMBER(1)     -- 컬럼추가

2) 컬럼 이름 변경

ALTER TABLE product
ADD a NUMBER(1);

 

3) 컬럼의 자료형 또는 자릿수 변경
ALTER TABLE product
MODIFY prod_price NUMBER(6);

 

ALTER TABLE product
MODIFY prod_price DEFAULT 0;

 

4) 컬럼 삭제

ALTER TABLE product

DROP COLUMN abc;

 

5) 제약조건 추가

(테이블레벨로 추가)

ALTER TABLE product

ADD CONSTRAINTS prod_no_pk PRIMARY KEY(prod_no);

 

(컬럼 레벨로 추가)

ALTER TABLE product
MODIFY prod_name CONSTRAINTS prod_name_nn NOT NULL;

 

3. 테이블 제거

DROP TABLE product;

 

 

무결성 제약조건

설정방법: 테이블레벨로 설정, 컬럼레벨로 설정하는 방법이 있다

그 중 NOT NULL제약 조건은 컬럼 레벨로만 설정 할 수 있다

(1)테이블 레벨로 설정

    CREATE TABLE t_a(c_a number (5) default 0, 

                                      c_b varchar2(10),

                                      constraint c_a_pk PRIMARY KEY(c_a),

                                      constraint c_b_ck CHECK (c_b IN ('남', '여'))

     )

 

(2) 컬럼레벨로 설정

CREATE TABLE t_a(c_a number(5) default 0 constraint c_a_pk PRIMARY KEY,

c_b varchar2(10) constraint c_b_ck CHECK (c_b IN ('남', '여')) 

NOT NULL

 

1. NOT NULL : 컬럼레벨로만 설정 가능

2. UNIQUE

3. PRIMARY KEY : NOT NULL + UNIQUE

4. CHECK

5. FOREIGN KEY

---

DML(데이터 조작어) - 추가 INSERT, 수정 UPDATE, 삭제DELETE

 

SELECT * FROM product;

1. 데이터추가

INSERT INTO product(prod_no, prod_name) VALUES ('C0001', '아메리카노' );

INSERT INTO product(prod_no, prod_name) VALUES ('C0002', '라떼' );

INSERT INTO product(prod_no, prod_name) VALUES ('C0003', '핫초코' );

 

ALTER TABLE product
MODIFY prod_name VARCHAR2(100);

 

INSERT INTO product(prod_no, prod_name) VALUES ('C0004', '아이스딸기아사이' );

 

INSERT INTO product VALUES ('F0001', '치즈케이크', 1000);

INSERT INTO product VALUES ('F0002', '치즈베이글', NULL);

INSERT INTO product VALUES ('F0003', '치킨샌드위치', ' ');

INSERT INTO product VALUES ('F0004', '밀박스'); --상품가격 입력필수

 

INSERT INTO product  VALUES (C0001', '다른상품', 0); --상품번호중복 - PK제약조건에 위배

INSERT INTO product  VALUES (' ', '번호가없는상품1', 0); --상품번호NULL - PK제약조건에 위배

INSERT INTO product  VALUES (' ', '번호가없는상품2', 0);

 

DELETE product WHERE prod_name = '다른상품';

DELETE product WHERE prod_no IS NULL;

 

INSERT INTO product(prod_no, prod_name) VALUES ('D0001', '라임블렌디드'); --OK

INSERT INTO product(prod_no, prod_name) VALUES ('D0002', NULL); --OK

DELETE product WHERE prod_name IS NULL;

 

 

 

UPDATE product SET prod_name= prod_name || '음료' , = prod_price=3000 WHERE prod_no LIKE 'D%' ;