Database
오라클의 sequence
- 일련번호을 발행하는 오라클의 데이터베이스 객체다.
- 시퀀스 객체 생성
create sequence 시퀀스명;
create sequence category_seq;
create sequence product_seq;- 시퀀스명은 _seq로 끝나도록 짓는것이 관례다.
-
일련번호 발행
- 시퀀스명.nextval
- 새로운 일련번호를 제공한다.
- 시퀀스명.currval
- 직전에 발행된 일련번호를 다시 제공한다.
- 시퀀스명.nextval
-
시퀀스 사용하기
- insert문에서 시퀀스를 사용해서 새로운 번호가 저장되게 하기
insert into tb_categories(cate_no, cate_name, cate_parent_no) values(cate_seq.nextval, ?, ?); insert into tb_products(prod_no, prod_name, prod_maker, prod_price, prod_discount_rate) values(product_seq.nextval, ?, ?, ?, ?);- select문에서 시퀀스를 사용해서 새로운 번호 발행하기
select cate_seq.nextval from dual; select product_seq.nextval from dual;
서브쿼리(sub query)
- 메인 쿼리 내부에 정의된 쿼리
- 다른 select 문의 내부에 정의된 select문을 서브쿼리라고 한다.
- 서브쿼리를 포함시킬 수 있는 곳
- where 절
- having 절
- from 절 (인라인뷰라고도 부른다)
- 서브쿼리 형식
select column, column
from table
where column 연산자 (select column
from table)- 서브쿼리의 특징
- 서브쿼리는 한번만 실행된다.
- 서브쿼리는 메인쿼리보다 먼저 실행된다.
- 서브쿼리의 실행결과는 메인쿼리의 조건식에서 사용된다.
- 조건식에서 비교값으로 사용되는 값이 쿼리의 실행결과로만 획득할 수 있을 때, 그 비교값을 조회하는 쿼리가 서브쿼리다.
- 서브쿼리 사용시 주의점
- 서브쿼리는 반드시 괄호로 묶어야 한다.
- 조건식의 오른쪽에 서브쿼리를 위치시키면 가독성이 높아진다.
- 서브쿼리의 실행결과가 단일행인지, 다중행인지에 따라 적절한 연산자를 사용해야 한다.
-- 전체 사원의 평균급여보다 급여를 적게 받는 사원들의 사원아이디, 이름, 급여를 조회하기
-- 조건식에서 사원의 급여와 비교되는 전체사원의 평균급여는 select문의 실행결과로 획득되는 값이다.
-- 따라서, where절에 사원들의 전체 평급을 조회하는 select문(서브쿼리)이 필요하다.
select employee_id, first_name, salary
from employees
where salary < (select avg(salary)
from employees)서브쿼리의 종류
- 단일행 서브쿼리
- 서브쿼리의 실행결과로 한 행만 반환된다.
- 단일행 비교 연산자
- =, >, >=, <, <=, <>
- 다중행 서브쿼리
- 서브쿼리의 실행결과로 여러 행이 반환된다.
- 다중행 비교 연산자
- in, any, all
| 단일행 서브쿼리 | 다중행 서브쿼리 |
|---|---|
| = | in |
| <> | not in |
| > | >any, >all |
| < | <any <all |
- 다중열 서브쿼리
- 두 개 이상의 컬럼값이 조회조건으로 반환되는 서브쿼리다.
- 다중열 서브쿼리 형식
select column, column, .... from table1 where (column1, column2) in (select column1, column2 from table2) - having절에서 서브쿼리 사용하기
- group by 절을 사용해서 그룹화하고 그룹함수를 실행한 결과를 필터링하는 having 절에도 서브쿼리를 사용할 수 있다.
- 사용예
select column, 그룹함수 from table1 group by column having 그룹함수 연산자 (select column from table) - 상호연관 서브쿼리
- 서브쿼리가 메인쿼리문에서 사용(참조)되는 컬럼을 참조할 때 상호연관 서브쿼리가 된다.
- 상호연관 서브쿼리는 메인쿼리문에서 처리되는 각 행에 대해서 한번씩 실행된다.
- 형식
select outer.column1, outer.column2, ... from table1 outer where column 연산자 (select column1 from table2 where column = outer.column3) -- 서브쿼리에서 메인쿼리에서 사용되는 테이블의 컬럼을 참조한다. -- 서브쿼리에서는 메인쿼리의 테이블의 별칭을 사용해서 메인쿼리에서 사용되는 테이블의 컬럼을 참조한다.- 예제
- 교수테이블에서 해당 직급의 평균급여보다 급여를 많이 받는 교수의 정보를 조회하기
select A.profno, A.name, A.position, A.pay from professor A where A.pay > (select avg(B.pay) from professor B where B.position = A.position);- 교수테이블에서 교수정보를 조회하기, 교수번호, 교수이름, 직급, 해당직급의 평균급여 정보를 조회하기
select A.profno, A.name, A.position, A.pay, (select avg(B.pay) from professor B where B.position = A.position) position_avg_pay from professor;- 교수테이블에서 교수정보를 조회하기. 교수번호, 교수이름, 직급, 해당교수가 지도하는 학생숫자를 조회하기
select A.profno, A.name, A.position, (select count(*) from student B where B.profno = A.profno) student_cnt from professor;
-- 서브쿼리
------------------------- 단일행 서브 쿼리 -------------------------------------------------
-- 전체사원의 평균급여보다 급여를 적게 받는 사원들의 아이디, 이름, 급여를 조회하기
select employee_id, first_name, salary
from EMPLOYEES
where salary < (select avg(salary)
from employees);
-- 100번 사원과 같은 부서에서 근무하는 사원들의 사원아이디, 이름을 조회하기
-- 1. 100번 사원이 소속되어 있는 부서아이디
select department_id
from employees
where employee_id = 100;
-- 2. 90번 부서에서 근무하고 있는 사원들의 사원아이디, 이름 조회하기
select employee_id, first_name
from employees
where department_id = 90;
-- 3. 100번 사원과 같은 부서에서 근무하는 사원들의 사원아이디, 이름을 조회하기
select employee_id, first_name
from employees
where department_id = (select department_id
from employees
where employee_id = 100);
-------------------------- 다중행 서브쿼리 --------------------------------------------------
-- 부서별 평균급여를 조회했을 때 평균급여가 5000달러 이하 부서에 근무중인 사원들 조회하기
-- 1. 부서별 평균급여를 조회했을 때 평균급여가 5000달러 이하인 부서
select department_id -- 10, 30, 50부서가 평균급여가 5000 달러 이하인 부서
from employees
where department_id is not null
group by department_id
having avg(salary) <= 5000;
-- 2. 5000달러 이하 부서에 근무중인 사원들 조회하기
select employee_id, first_name, department_id
from employees
where department_id in (10, 30, 50);
-- 3. 부서별 평균급여를 조회했을 때 평균급여가 5000달러 이하 부서에 근무중인 사원들 조회하기
select employee_id, first_name, department_id
from employees
where department_id in (select department_id
from employees
where department_id is not null
group by department_id
having avg(salary) <= 5000);
-- 직종최소급여가 10000달러 이상인 직종에 종사중인 사원들이 아이디, 이름, 직종아이디를 조회하기
-- 1. 직종최소급여가 10000달러 이상인 직종
select job_id -- AD_PRES, AD_VP, SA_MAN
from jobs
where min_salary >= 10000;
--2. 직종최소급여가 10000달러 이상인 직종에 종사중인 사원
select employee_id, first_name, job_id, salary
from employees
where job_id in ('AD_PRES', 'AD_VP', 'SA_MAN');
-- 3. 직종최소급여가 10000달러 이상인 직종에 종사중인 사원들이 아이디, 이름, 직종아이디를 조회하기
select employee_id, first_name, job_id, salary
from employees
where job_id in (select job_id
from jobs
where min_salary >= 10000);
-- 서브쿼리로 해결되는 문제를 조인으로 해결하기
select E.employee_id, E.first_name, E.job_id, E.salary
from jobs J, employees E
where J.min_salary >= 10000
and J.job_id = E.job_id;
-- 50번 부서에 소속된 사원중에서 60번 부서에 소속된 직원의 급여보다 급여를 많이 받는
-- 사원의 아이디, 이름, 급여를 조회하기
-- 1. 60번부서에 소속된 사원들의 급여 조회
select salary -- 9000, 6000, 4800, 4200
from employees
where department_id = 60;
-- 2. 60번 부서에 소속된 직원의 모든 급여보다 급여를 많이 받는 50번 부서의 사원 조회
select employee_id, first_name, salary
from employees
where department_id = 50
and salary >all (9000, 6000, 4800, 4200); -- and salary > 9000
-- 2. 60번 부서에 소속된 직원의 급여 중 아무꺼나 하나보다 급여를 많이 받는 50번 부서의 사원 조회
select employee_id, first_name, salary
from employees
where department_id = 50
and salary >any (9000, 6000, 4800, 4200); -- and alary > 4200
-- 3. 50번 부서에 소속된 사원중에서 60번 부서에 소속된 직원의 급여보다 급여를 많이 받는
-- 사원의 아이디, 이름, 급여를 조회하기
select employee_id, first_name, salary
from employees
where department_id = 50
and salary >all (select salary
from employees
where department_id = 60);
-- ">all"은 "> max()"로 대체할 수 있다.
select employee_id, first_name, salary
from employees
where department_id = 50
and salary > (select max(salary)
from employees
where department_id = 60);
------------------------------- 다중열 서브쿼리 -------------------------------------------
-- 부서별 최고 급여를 받는 사원의 아이디, 이름, 급여, 부서아이디를 조회하기
-- 1. 부서별 최고급여를 조회하기
select department_id, max(salary)
from employees
where department_id is not null
group by department_id;
-- 2. 부서별 최고 급여를 받는 사원을 조회하기
select employee_id, first_name, salary, department_id
from employees
where (department_id, salary) in (select department_id, max(salary)
from employees
where department_id is not null
group by department_id);
-------------------------- having절 서브쿼리 ------------------------------------------------
-- 부서별 사원수를 조회했을 때 가장 인원수가 많은 부서의 아이디와 인원수를 조회하기
-- 1. 부서별 사원수를 조회하기
select department_id, count(*)
from employees
where department_id is not null
group by department_id;
-- 2. 가장 많은 인원수 조회하기
select max(count(*)) -- 45
from employees
where department_id is not null
group by department_id;
-- 3. 가장 많은 인원수를 보유하고 있는 부서
select department_id, count(*)
from employees
where department_id is not null
group by department_id
having count(*) = 45;
-- 4. 부서별 사원수를 조회했을 때 가장 인원수가 많은 부서의 아이디와 인원수를 조회하기
select department_id, count(*)
from employees
where department_id is not null
group by department_id
having count(*) = (select max(count(*))
from employees
where department_id is not null
group by department_id);
-- 단일행 서브쿼리 연습
-- Neena와 같은 해에 입사한 사원들의 아이디, 이름, 입사일 조회하기
select employee_id, first_name, hire_date
from employees
where to_char(hire_date, 'yyyy') = (select to_char(hire_date, 'yyyy')
from employees
where first_name = 'Neena');
-- Neena와 같은 해에 입사한 사원중에서 전체 사원의 평균급여보다 급여를 많이 받는
-- 사원의 아이디, 이름, 입사일, 급여를 조회하기
select employee_id, first_name, hire_date, salary
from employees
where to_char(hire_date, 'yyyy') = (select to_char(hire_date, 'yyyy')
from employees
where first_name = 'Neena')
and salary > (select avg(salary)
from employees);
-- Neena의 상사가 소속되어 있는 부서와 같은 부서에서 근무하는 사원의 아이디,
-- 이름, 직종, 부서아이디를 조회하기
select employee_id, first_name, job_id, department_id
from employees
where department_id = (select department_id -- 니나의 상사가 소속된 부서의 아이디
from employees
where employee_id = (select manager_id -- 니나의 상사
from employees
where first_name = 'Neena'));
-- 60번 부서에 소속된 사원들의 급여를 인상시키기
-- (인상액은 전체 사원 평균급여의 10%, 소수점이하는 반올림한다.)
update employees
set
salary = salary + (select round(avg(salary)*0.1)
from employees)
where department_id = 60;
-- 다중행 서브쿼리 연습하기
-- 100 상사에게 보고하는 직원이 자신의 상사인 사원들의 아이디, 이름, 상사의 아이디를 조회하기
select employee_id, first_name, manager_id
from employees
where manager_id in (select employee_id
from employees
where manager_id = 100);
-- 직종이 변경된 적이 있는 직원의 아이디, 이름, 소속부서아이디를 조회하기
SELECT employee_id, first_name, department_id
FROM employees
WHERE employee_id in (SELECT distinct employee_id
from job_history);
-- 'Seattle'에서 일하고있는 사원들의 사원 아이디, 이름, 부서아이디를 조회
SELECT employee_id , first_name, department_id
FROM employees
WHERE department_id in (SELECT department_id
FROM departments
WHERE location_id = (SELECT location_id
FROM locations
WHERE city = 'Seattle'));
-- 'Steven'의 급여보다 많은 급여를 받는 사원들의 아이디, 이름 ,급여를 조회하기
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE first_name = 'Steven');
