Day25

Day25

2020. 10. 30. 10:18ㆍ교육과정/KOSMO

키워드 : 뷰 / 복합뷰 / 읽기전용 뷰 / 시퀀스 / JDBC / 자바에서 오라클 연결하기

****

1. SQL 복습

SELECT * FROM employees;
SELECT * FROM departments;
-- 1. Zlotkey과 동일한 부서에 속한 모든 사원의 이름과 입사일을 표시하는 질의를 작성하십시오.
--     Zlotey는 제외하십시오.
SELECT department_id FROM employees WHERE last_name='Zlotkey';
SELECT first_name||' '||last_name 이름, hire_date FROM employees
    WHERE department_id = (SELECT department_id FROM employees WHERE last_name='Zlotkey');
-- 2. 급여가 평균 급여보다 많은 모든 사원의 사원 번호와 이름을 표시하는 질의를 작성하고
--     결과를 급여에 대해 오름차순으로 정렬하십시오.
SELECT avg(salary) FROM employees;
SELECT employee_id, first_name||' '||last_name 이름, salary FROM employees
    WHERE salary > (SELECT avg(salary) FROM employees) ORDER BY salary ASC;
-- 3. 이름에 u가 포함된 사원과 같은 부서에서 일하는 모든 사원의 사원 번호와 이름을 표시하는 질의를 작성하십시오.
SELECT department_id FROM employees WHERE first_name like '%u%';
SELECT employee_id, first_name||' '||last_name 이름 FROM employees
    WHERE department_id IN (SELECT department_id FROM employees WHERE first_name like '%u%');

-- 4. 부서 위치 ID가 1700인 모든 사원의 이름, 부서 번호 및 업무 ID를 표시하십시오.
SELECT department_id FROM departments WHERE location_id=1700;
SELECT first_name||' '||last_name 이름, department_id, job_id FROM employees
WHERE department_id = ANY(SELECT department_id FROM departments WHERE location_id=1700);
-- 5. King에게 보고하는 모든 사원의 이름과 급여를 표시하십시오.
SELECT employee_id FROM employees WHERE last_name='King';
SELECT first_name||' '||last_name 이름, salary FROM employees
WHERE manager_id = ANY(SELECT employee_id FROM employees WHERE last_name='King');

SELECT e1.first_name||' '||e1.last_name 이름, e1.salary, e2.first_name||' '||e2.last_name 매니저명
FROM employees e1, employees e2

WHERE e1.manager_id = e2.employee_id
AND e1.manager_id = ANY(SELECT employee_id FROM employees WHERE last_name='King');

-- 6. Executive 부서의 모든 사원에 대한 부서번호, 이름 및 업무 ID를 표시하십시오.
SELECT department_id FROM departments WHERE department_name='Executive';
SELECT department_id, first_name||' '||last_name 이름, job_id FROM employees
    WHERE department_id = (SELECT department_id FROM departments WHERE department_name='Executive');
-- 7. 평균 급여보다 많은 급여를 받고, 이름에 'u'가 포함된 사원과 같은 부서에서 근무하는
--     모든 사원의 사원번호, 이름 및 급여를 표시하시는 질의를 작성하십시오.
SELECT avg(salary) FROM employees;
SELECT department_id FROM employees WHERE first_name like '%u%';
SELECT department_id, first_name||' '||last_name 이름, salary FROM employees
    WHERE salary > (SELECT avg(salary) FROM employees)
    AND department_id = ANY(SELECT department_id FROM employees WHERE first_name like '%u%');

2. 뷰 (VIEW)

: 가상 테이블 (보안이나 복잡한 작업을 간소화 하기 위해 생성)
-- ` 데이터 보안
-- ` 복잡한 쿼리의 사용빈도가 높은 경우
-- 원본테이블은 그대로 두고, 필요한 컬럼만 들어있는 뷰를 만들어 작업할 수 있다.
-- 여러 테이블에 나뉘어져 조인이 필요할 경우, 해당 컬럼들만 추출된 뷰를 만들어 작업할 수 있다.
-- CREATE OR REPLACE VIEW 뷰명 : 테이블 생성할 때 중복되는 이름이 있으면 덮어쓰며 테이블 생성한다.

< View의 장점 >
- View는 데이터베이스의 선택적인 내용을 보여줄 수 있기 때문에 데이터베이스에 대한 액세스를 제한한다

- 조인을 한 것처럼 여러 테이블에 대한 데이터를 View를 통해 볼 수 있다

- 한 개의 View로 여러 테이블에 대한 데이터를 검색한다

- 특정 기준에 따른 사용자 별로 다른 데이터를 액세스 할 수 있다

-- >> 그냥 쿼리 수행하면 scott은 데이터베이스 관리자가 아니라서 권한이 없는 관계로 뷰를 생성하지 못한다.
-- >> CMD에서 관리자 권한으로 접속하기 위해 sqlplus "/as sysdba" 입력 후
-- >> GRANT create view TO scott; 로 필요한 계정에 권한 부여하면 이후부터 뷰 생성 가능

CMD에서 scott계정에 뷰 생성 권한 부여 필수

-- 뷰를 읽기전용으로 만들기
CREATE OR REPLACE VIEW v_emp AS SELECT empno, ename, deptno FROM emp
with read only;

--- 복합뷰 생성
CREATE OR REPLACE VIEW v_emp_info
AS SELECT e.empno emp_no, e.ename e_name, d.dname d_name
FROM emp e, dept d WHERE e.deptno=d.deptno;
SELECT * FROM v_emp_info;
rollback;

-- 복합뷰에 데이터 입력
INSERT INTO v_emp_info (emp_no, e_name) VAlUES (4000, '맹돌이');
-->> 조회해도 맹돌이의 데이터가 나오지 않는다. 왜????????????????????
-- v_emp_info 뷰에서 4000번 사번 삭제
DELETE FROM v_emp_info WHERE emp_no=4000; -- X (삭제 불가?????????????)

-- 부서별로 부서명, 최소급여, 최대급여, 부서의 평균급여를 포함하는 v_dept_sum뷰를 생성하여라.
SELECT avg(sal) FROM emp GROUP BY deptno;
CREATE OR REPLACE VIEW v_dept_sum
    AS SELECT d.dname d_name, e.min(sal) min_sal, e.max(sal) max_sal,
    (SELECT e.avg(sal) FROM emp e GROUP BY e.deptno) avg_sal
    FROM emp e, dept d WHERE e.deptno=d.deptno;

3. 시퀀스 (Sdquence) : 자동증가수
-- ex) 게시판의 글 번호 (중복값이 없어 PK값으로 사용할 수 있다)

CMD에서 HR계정에 시퀀스 권한을 부여해야 사용할 수 있다.

-- 시퀀스 생성
CREATE SEQUENCE seq_test;
-- 시퀀스 조회
SELECT seq_test.currval FROM dual; -- X 값을 한 번도 넣은 적 없을 경우 조회가 되지 않는다.
SELECT seq_test.nextval FROM dual; -- 1씩 증가함, 5번 증가시킨 후 currval 조회하면 반영되어 있다.

INSERT INTO emp (empno, ename) VALUES (seq_test.nextval , '이돌이');
SELECT * FROM emp; -- 이돌이의 사번이 2가 되어 있다.

-- 연습 TEMP 테이블
CREATE TABLE temp (
    no      number,
    name    varchar2(20),
    indate  date,
    CONSTRAINT pk_temp_no PRIMARY KEY (no)
);
SELECT * FROM temp;
CREATE SEQUENCE seq_temp_no -- 테이블명과 컬럼명을 사용해 시퀀스의 이름을 지정해준다.
    start with 10000        -- 시작하는 숫자 위치
    increment by 1000;      -- 숫자 증가 폭
INSERT INTO temp VALUES (seq_temp_no.nextval, '홍돌이', sysdate);
SELECT * FROM temp;         -- 홍돌이의 사번이 11000으로 입력된다.

4. 복습

5. 인덱스
-- PK로 검색하면 메모리에 올라간 인덱스를 사용해서 조회하기 때문에 속도가 빠르나,
-- 그 외의 정보로 검색하면 하드디스트에서 차례대로 조회해야 하기 때문에 상대적으로 속도가 느리다.
SELECT employee_id, last_name, phone_number FROM employees WHERE salary = 3000;
-- 자동추적(F6)(CMD에서 권한 부여 필요)시 Options이 full, cost가 3으로 나온다.

-- salary컬럼으로 자주 조회해서 속도 향상을 위해 메모리에 올려야 할 경우 인덱스를 생성할 수 있다.
CREATE INDEX emp_sal_idx ON employees(salary);
-->> 자동추적 결과 Option이 Range Scan으로, cost는 1로 나온다.

6. 쇼핑몰 연습문제

-- 문제1. 배송중인 주문 내역과 상품 정보 출력
SELECT ono, status FROM ex_order WHERE status='배송중';

SELECT o.orderno, g.gname, g.gdetail, g.price, o.id, o.count, o.status

FROM ex_good g, ex_order o
WHERE g.gno=o.gno AND status='배송중';

-- 문제2. 주문 들어온 상품 내역과 고객 정보 출력
SELECT ono, status FROM ex_order WHERE status='주문';
SELECT o.orderno, o.count, g.gname, g.gdetail, g.price, o.status st, m.id, m.name, m.tel, m.addr
FROM ex_member m, ex_good g, ex_order o
WHERE o.id=m.id AND o.gno=g.gno AND status='주문';

-- 문제3. 주문별로 고객 정보(ID만 출력)와 주문한 상품의 총금액을 출력
SELECT sum(g.price * o.count) total
FROM ex_good g, ex_order o WHERE g.gno=o.gno GROUP BY id, orderno;
SELECT id, orderno, sum(g.price * o.count) total
FROM ex_good g, ex_order o WHERE g.gno=o.gno GROUP BY id, orderno;