Database

서브쿼리

서브쿼리의 종류

• 스칼라 서브쿼리
  • 하나의 행에서 하나의 값만 반환하는 서브쿼리다.
    • 다중행 X, 다중열 X, 단일행-단일열 O
  • 스칼라 서브쿼리는 select절, insert문의 values에서 사용할 수 있다.
  • 스칼라 서브쿼리는 decode 및, case의 조건 및 표현식에서 사용할 수 있다.

    select col1, col2, (select col3 from table2 where 조건식)
    from table1;

    insert into table1 
    (col1, col2, col3, ...)
    values
    (값, 값, (select col4 from table2 where 조건식));

-- 스칼라 서브쿼리
-- 서브쿼리의 실행결과가 단일행-단일컬럼인 경우(오직 갑싱 하나만 조회되는 서브쿼리)
-- 사원아이디, 이름, 급여, 사원전체평균급여를 조회하기
SELECT O.employee_id, O.first_name, O.salary,
        (SELECT TRUNC(AVG(I.salary)) FROM employees I) avg_salary
FROM employees O;

-- 스칼라 서브쿼리를 update문에서 사용하기
-- 사원들의 급여를 전체사원의 평균급여의 10%만큼 인상시키기
UPDATE employees
SET
    salary = salary + (SELECT TRUNC(AVG(salary)*0.1) FROM employees);
    
-- 스칼라 서브쿼리르 insert문에서 사용하기
-- 새로운 사원정보를 추가하기
INSERT INTO employees
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES
(employees_seq.nextval, 'Gildong', 'Hong', 'GILDONG', '010.1111.2222', sysdate, 'IT_PROG',(SELECT min_salary FROM jobs WHERE job_id = 'IT_PROG') ,null, 103, 60);

데이터 정의어(DDL)

• 오라클 데이터베이스 객체 생성, 변경, 삭제에 사용되는 명령어
• CREATE, ALTER, DROP, TRUNCATE

뷰

• 테이블 혹은 다른 뷰를 기반으로 하는 가상의 테이블(논리적인 테이블)이다.
• 특징
  • 물리적인 저장공간을 가지지 않는다.
  • INSERT, UPDATE, DETELE 작업의 수행이 불편하다.
• 목적
  • 복잡한 SQL문을 간단하게 작성하기 위해서 사용
  • 데이터의 엑세스를 제한하기 위해서 사용(데이터에 대한 보안성 강화)
  • 동일한 데이터로부터 다양한 결과를 얻기 위해서 사용
• 종류
  • 단순 뷰
    • 한 테이블에서만 데이터를 가져온다.
    • 함수 또는 데이터 그룹을 사용하지 않았음
    • DML(INSERT/UPDATE/DELETE가 가능은 하다)
  • 복합 뷰
    • 여러 테이블에 데이터를 가져온다.
    • 함수 또는 데이터 그룹을 포함하고 있음
    • DML 거의 불가능하다.
• 뷰 생성

  CREATE [OR REPLACE] VIEW 뷰이름
  AS 서브쿼리
  WITH READ ONLY
  
  -- 서브쿼리는 가상의 테이블이 포함할 데이터를 조회하는 SELECT문

• 뷰 수정

  CREATE OR REPLACE VIEW 뷰이름
  AS 서브쿼리
  
  -- 뷰이름을 수정하려는 기존 뷰이름과 동일하게 지정하면 된다.

• 뷰 삭제

  DROP VIEW 뷰이름

인라인 뷰

• SELECT 문의 FROM절에 서브쿼리를 정의하고, 이 서브쿼리로 조회된 결과를 가상의 테이블로 취급하고, 별칭을 부여한 것
• 데이터베이스 객체가 아니다.
• 형식

  SELECT A.column, A.column, A.column
  FROM (SELECT column, column, column, ...
        FROM table1
        WHERE 조건식) A
   WHERE 조건식

-- 뷰(가상의 테이블)
-- 부서 상세정보를 제공하는 가상의 테이블 생성하기
-- 부서 상세정보 - 부서아이디, 부서명, 관리자 아이디, 관리자 이름, 부서에 소속된 사원수, 부서의 소재지, 주소

CREATE OR REPLACE VIEW departments_detail_view
AS 
SELECT D.department_id, D.department_name, D.manager_id, E.first_name, E.last_name, L.city, l.street_address,
        (SELECT COUNT(*) FROM employees I WHERE I.department_id = D.department_id) emp_cnt
FROM departments D, employees E, locations L
WHERE D.manager_id = E.employee_id(+)
AND D.location_id = L.location_id;

-- VIew 사용 후
SELECT department_id, department_name, manager_id, first_name, last_name, city, street_address, emp_cnt
FROM departments_detail_view
WHERE department_id = 60;

-- 사원의 상세정보를 제공하는 뷰 생성하기
-- 사원아이디, 이름 ,이메일, 전화번호, 입사일, 근무개월수, 근무년수, 급여, 커미션, 급여등급,
-- 연봉, 상사의 아이디, 상사이름, 직종아이디, 직종제목, 부서아이디, 부서명, 부서관리자이름
CREATE OR REPLACE VIEW employees_detail_view
AS
SELECT E.employee_id emp_id, E.first_name || ',' || E.last_name as full_name, E.email, E.phone_number,
       E.hire_date, TRUNC(MONTHS_BETWEEN(SYSDATE, E.hire_date)) work_monhs,
       TRUNC(MONTHS_BETWEEN(SYSDATE, E.hire_date) / 12) working_year, E.salary, E.commission_pct, G.grade,
       E.salary*12+E.salary*12*nvl(E.commission_pct, 0) annual_salary, M.employee_id as manager_id,
       M.first_name || ',' || M.last_name as manager_name, E.job_id, J.job_title, D.department_id, D.department_name,
       DM.employee_id dept_manager_id,
       DM.first_name || ',' || DM.last_name as department_manger_name
       
FROM employees E, salary_grade G, employees M, jobs J, departments D, employees DM
WHERE E.salary >= G.min_salary and E.salary <= G.max_salary
AND E.manager_id = M.employee_id(+)
AND E.job_id = J.job_id
AND E.department_id = D.department_id
AND D.manager_id = DM.employee_id(+) with read only;

-- 200사원의 아이디, 이름, 급여, 급여등급, 연봉, 직종아이디, 소속부서명 조회
SELECT emp_id, full_name, salary, grade, annual_salary, job_id, department_name
FROM EMPLOYEES_DETAIL_VIEW
WHERE emp_id = 200;

-- 급여등급별 사원수를 조회하기
SELECT grade, count(*)
FROM EMPLOYEES_DETAIL_VIEW
GROUP BY grade
ORDER BY grade;

-- 입사일 기준 근무년수가 15년 이상인 사원의 아이디, 이름, 입사일, 근무년수를 조회하기
SELECT emp_id, full_name, hire_date, working_year
FROM employees_detail_view
WHERE working_year >= 15
ORDER BY working_year DESC;


-- 연봉이 10만불 이상인 사원의 아이디, 이름, 급여, 급여등급, 연봉, 직종을 조회하기
SELECT emp_id, full_name, salary, grade, annual_salary, job_id
FROM EMPLOYEES_DETAIL_VIEW
WHERE annual_salary >= 200000;

-- 인라인 뷰
-- 직종별 사원수를 조회하기
-- 직종아이디, 직종제목, 직종최소급여, 직종최대급여, 사원수
-- select job_id, count(*)
-- from employees group job_id
SELECT A.job_id, B.job_title, B.min_salary, B.max_salary, A.cnt
FROM (SELECT job_id, count(*) cnt
	  FROM EMPLOYEES
	  GROUP BY job_id) A, jobs B
WHERE A.job_id = B.job_id;

-- Top-N 분석
-- 지정된 컬럼의 값을 기준으로 행을 정렬하고, 

-- 연봉에 대한 내림차순으로 정렬했을 때 상위 3명의 아이디, 이름, 연봉을 조회하기
SELECT  rownum, emp_id, full_name, annual_salary
FROM   (SELECT emp_id, full_name, annual_salary
        FROM employees_detail_view
        ORDER BY annual_salary desc)
WHERE rownum <=10;

-- 직종별 인원수를 조회했을 때, 인원수가 가장 많은 직종 3개를 조회하기
SELECT rownum, job_id, cnt
FROM (SELECT job_id, COUNT(*) cnt
      FROM employees
      GROUP BY job_id
      ORDER BY cnt desc)
WHERE rownum <= 3;

-- 직종별 인원수를 조회했을 때, 인원수를 기준으로 내림차순 정렬하고, 6번째~10번째에 해당하는
-- 행을 조회하기
SELECT ranking, job_id, cnt
FROM (SELECT rownum ranking, job_id, cnt
      FROM (SELECT job_id, COUNT(*) cnt
            FROM employees
            GROUP BY job_id
            ORDER BY cnt desc))
WHERE ranking >= 6  and ranking <= 10;