DB 03/10

SUBQUERY : SELECT 문장 안에 SELECT문장이 중첩되어 있는 형태

여러번의 DB접속이 필요한 상황을 한번으로 줄여 속도증가에 도움을 줄수 있다.

-- 서브쿼리를 사용할 수 있는 영역
-- 1) where, having절
-- 2) select나 delete의 from절
-- 3) update의 set절
-- 4) insert의 into절

 

--예) 사원테이블에서 이름이 'MIchael'이고, 직종이 MK_MAN인 사원의 급여보다 초과해서 받는
       사원들의 정보를 사번,이름,직종,급여 순으로 출력

 

subquery를 사용하지 않을때

--문제접근1) 이름이 마이클이고 직종이 MK_MAN인 사원의 급여 
SELECT salary
FROM employees
WHERE first_name = 'Michael' AND job_id = 'MK_MAN';  --결과 : 13000


--문제접근2) 접근1을 통해 얻은 급여값 13000을 초과하는 직원들을 조회
SELECT employee_id, first_name, job_id, salary
FROM employees
WHERE salary > 13000;   -- 최종 결과

subquery를 사용하였을때

-- 위의 두가지 select문을 subquery를 통해 하나로 통합

SELECT employee_id, first_name, job_id, salary
FROM employees
WHERE salary > ( SELECT salary
	         FROM employees
                 WHERE first_name = 'Michael' AND job_id = 'MK_MAN' );

--문제1) 사원테이블에서 사번이 111번인 사원의 직종과 같고,
사번이 159번인 사원의 급여를 초과한 사원들의 정보를 
사번,이름,직종,급여 순으로 출력 

SELECT employee_id, first_name, job_id, salary
from employees
where  -- 서브쿼리가 2개 필요함
job_id = ( select job_id from employees where employee_id = 111)
and
salary > ( select salary from employees where employee_id = 159);

--문제2) 사번이 150인 사원의 급여와 같은 급여를 받는 사원들의 사번,이름,급여를 출력

SELECT employee_id, first_name, salary
FROM employees
WHERE salary = (SELECT salary FROM employees WHERE employee_id = 150);

--문제3) 월급이 평균 월급 이상인 사원들의 이름과 급여를 출력

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

--문제4) Bruece와 같은 부서에서 근무하고 있는 사원들의 이름과 , 부서번호를 출력

SELECT first_name, department_id
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE first_name = 'Bruce')
ORDER BY first_name;

--문제5) 137번 사원의 급여이상이며, 149번 사원의 급여 이하인 사원들의 이름과 급여를 조회

SELECT first_name, salary
FROM employees
WHERE salary between ( SELECT salary FROM  employess WHERE employee_id = 137) -- 이상,이하이므로 between연산자 사용
AND
(SELECT salary FROM  employess WHERE employee_id = 149);

--문제6)  직종과 직종별 평균 급여를 조회하되, 평균급여가 Bruce 사원의 급여를 초과하는 경우만 조회

SELECT job_id, avg(salary)  -- 일반컬럼과 그룹함수가 같이 쓰이므로 GROUP BY 사용
FROM employees
GROUP BY job_id      -- 평균급여(AVG(SALARY),그룹함수)를 조건식으로 쓰므로 WHERE이 아닌 HAVING
HAVING avg(salary) > (select salary FROM employees WHERE first_name = 'Bruce') -- 그룹함수의 조건은 where사용 불가, having 사용
ORDER BY job_id

--문제7) 사원 테이블에서 급여가 가장 적은 사원의 사번, 이름, 급여를 출력

SELECT employee_id, first_name, salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees); -- 가장 적은 급여를 구해야함

 

문제 8) 
사번이 158번인 사원과 직종이 같고, 급여가 164번 사원 초과인 사람들의 사번, 이름 ,직종, 급여를 출력

SELECT employee_id, first_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 158)
AND
salary > (SELECT salary FROM employees WHERE employee_id = 164)
ORDER BY employee_id;

--문9)
사원테이블에서 직종이 SA_REP인 사원의 최소 급여보다 적으면서(미만)
직종이 SH_CLERK이 아닌 사원들의 이름과,직종, 급여를 출력

SELECT first_name, job_id, salary
FROM employees
WHERE salary < (SELECT MIN(salary) FROM employees WHERE job_id = 'SA_REP')
AND
job_id != 'SH_CLERK';

--문10)
사원테이블에서 100번 부서의 최소급여를 초과하는 다른 모든 부서의 번호와, 최소 급여를 출력

SELECT department_id, MIN(salary)
FROM employees;
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 100);

--문11)
사원 테이블에서 last_name중에 'Bat'라는 단어를 포함하고 있는 사원과 같은 부서에서 근무하는 사원의
사번, 부서번호, 이름을 출력

SELECT  employee_id, department_id, first_name
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE last_name LIKE '%B%');

TRANSACTION : DB의 상태를 변화시키기 위해 수행하는 작업의 단위
(INSERT, DELETE, UPDATE 구문에만 허용)

1. ROLLBACK 키워드 (commit; 을 실행하기 전의 실수일때)

-실수로 잘못 작성된 쿼리문이 있거나 전달이 정상적으로 전달되지 않은 쿼리 문이 있는 경우
 commit; 단계를 거치지 않았을때  rollback;을 통해 직전단계로 돌아갈수 있다.

 

예제)

CREATE table EMPL  -- employees테이블의 전체내용을 복사하여 EMPL 테이블 생성
AS select * from employees;

--전체 테이블 수 출력
SELECT COUNT(*) from employees;

--사번이 100번인 사원을 삭제 , -- commit;은 진행하지 않음
DELETE 
FROM empl
WHERE employee_id = 100;

--하지만 위의 삭제코드에서 commit을 하지 않아도
내부적으로는 삭제 쿼리문이 실행중

--EMPL테이블의 데이터를 수정

UPDATE empl SET first_name = 'A'; -- 모든사원의 이름을 A라고 실수로 수정 

rollback; -- 100번인 사원과 A로 이름이 바뀐 실수 모두 전으로 롤백(commit;이 진행되지 않았기 때문에)

--만약 위의 코드처럼 실수로 잘못작성된 쿼리문이 있거나 전달이 정상적으로 전달되지않은 쿼리문이 잇는 경우 rollback; 키워드를 통해 commit; 직전단계까지 돌아 갈수 있다,

2. TIMESTAMP 키워드 (commit; 을 실행한 후의 실수일때)

UPDATE empl SET first_name = 'B';  --전체의 이름을 'B'로 수정했을때의 실수
commit;  -- commit과정까지 실행하여 데이터가 업데이트 완료 되었음.

--위의 코드처럼 commit 이 진행된(코드실수로 데이터가 바뀐) 이후에 이전단계로 rollback을 원한다면?

1) EMPL -- 실수한 empl의 테이블의 데이터를 삭제 한다.
DELETE FROM empl;  -- 테이블 empl의 모든 데이터 삭제

2) rollback을 원하는 시간대로 돌아가서 테이블을 재 조회
INSERT INTO empl  -- empl 테이블에 데이터 추가 
SELECT * FROM empl
as of timestamp(systimestamp-interval '10' minute)  -- 실제로 데이터가 추가 된게 아니라 데이터를 조회만 하고 있음
commit; -- 10분전의 empl데이터를 가져와 다시 새로운 empl의 테이블에 내용을 추가한다. 


as of timestamp(
	systimestamp-interval '원하는 시간' 단위);
-- '1' minute : 1분전
-- '2' hour :  2시간전
-- '3' day : 3일전

SEQUENCE : 중복되지 않는 연속적인 번호를 만들어주는 키워드(시퀀스)(primary key)

-- 형식
   create sequence 시퀀스명  -- 이거하나만 알고 넘어가도 됨 *** 중요
   start with 시작값( 기본값 1 )
   increment by 증가값( 기본값 1 )
   maxvalue 최대값 
   minvalue 최소값;
   cache / nocache (시퀀스의 속도를 위한 캐시사용 여부);

 

EX)

① goods테이블 생성
create table goods(
no NUMBER(3) primary key, --기본키가 되면서 절대로 중복된 값이 들어갈 수 없다
name varchar2(200) unique -- 기본키가 존재하지만 중복된 값이 들어갈수 없게 unique 속성 지정
);

 

② --굿즈 테이블에서 사용할 시퀀스 지정 
create sequence goods_no_seq;      -- goods테이블의 no 컬럼(primary key) 시퀀스

 

③ --데이터 추가
insert into GOODS values(goods_no_seq.nextVal,'라이언');   --   no = 1
insert into GOODS values(goods_no_seq.nextVal,'춘식이');   --  no = 2
inser into GOODS values(3,'춘식이2');      --  no = 3 -- 시퀀스를 사용할때는 강제로 3 값을주고 추가하면 절대 안된다. 대신 빈 공간에는 가능
insert into GOODS values(goods_no_seq.nextVal,'무지');      -- no = 3 시퀀스는 현재 2까지 증가되어 있는 상태고 3의 값이 와야하는데 위 코드에서 강제로 3을 값을 주어 값이 중복되기 때문에 오류
commit;

 

시퀀스를 사용할때  no 빈공간이 생기는 경우
시퀀스를 사용하여 쿼리문을 넣을때 '값이 중복되는 오류'(데이터 측면의 오류)가 나서 데이터가 들어가지 않아도 시퀀스는 증가한다.
하지만 쿼리문의 오타로인한 문법적인 오류는 시퀀스가 증가하지 않는다.
즉 insert into GOODS values(goods_no_seq.nextVal,'무지');    에서 시퀀스의 값이 3으로 증가하여 쿼리문을 작성하고 오류가 나면 값이 들어가지 않지만 시퀀스는 4로 증가하여 수정하고 쿼리문을 넣으면 no은 4로 들어가게된다.
이때 3은 빈공간이므로 강제적으로 시퀀스가 아닌 no=3을 넣는경우엔 오류가 나지 않는다.

 

캐시사용 여부의 차이

nocache를 사용한 시퀀스는 미리 데이터가 들어갈 공간을 마련해두지 않는다.

--------------------
no        name
1  라이언
2 춘식이
3 무지
4 콘
5 어피치

cache를 사용한 시퀀스는 미리 데이터가 들어갈 공간을 20개를 마련해 둔다. 공간할당에 유리함.
-------------------
no name
1 라이언
2 춘식이
↕
↕
↕
20