국비교육 27일차 Oracle : SQL, Sub Query, JOIN
1. SQL(Structured Query Language) 정형화된 질의어
1) SELECT
- DQL문 (Data Query Language) : 데이터 질의어
2) INSERT, UPDATE, DELETE, MERGE
- DML문 (Data Manuplation Language) : 데이터 조작어
3) COMMIT, ROLLBACK
- TCL문 (Transaction Control Language)
4) CREATE, ALTER, DROP, TRUNCATE
- DDL문 (Data Defination Language) : 데이터 정의어
5) GRANT, REVOKE : DCL문 (Data Control Language)
2. Sub Query(서브 쿼리)
1) Sub Query란?
- select문 속에 또 다른 select 문이 포함되어져 있을 때 포함되어진 select문을 Sub Query(서브쿼리) 라고 부른다.
select ...
from ... ==> Main Query(메인쿼리, 외부쿼리)
where ... in(select ...
from ...) ==> Sub Query(서브쿼리, 내부쿼리)- 예시(hr 테이블)
-- employees 테이블에서
-- 기본 급여가 제일 많은 사원과 기본 급여가 제일 적은 사원의 정보를
-- 사원번호, 사원명, 기본급여로 나타내세요.
-- employees 테이블에서 salary의 최대값 ==> select max(salary) from employees ==> 24000
-- employees 테이블에서 salary의 최소값 ==> select min(salary) from employees ==> 2100
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, salary AS 기본급여
from employees
-- where salary = (employees 테이블에서 salary의 최대값) OR (employees 테이블에서 salary의 최소값)
where salary = (select max(salary) from employees) OR
salary = (select min(salary) from employees);
/*
100 Steven King 24000
132 TJ Olson 2100
*/2) ANY, ALL
- Sub Query(서브쿼리)에서 사용되어지는 ANY는 OR와 흡사하고,
- Sub Query(서브쿼리)에서 사용되어지는 ALL은 AND와 흡사하다.
(1) ANY 사용 예시
-- HR 테이블 활용
-- employees 테이블에서 salary가 30번 부서에 근무하는 사원들의 salalry와 동일한 사원들만 추출하세요.
-- 단, 출력시 30번 부서에 근무하는 사원은 제외합니다.
select department_id AS 부서번호
, employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, salary AS 기본급여
from employees
where NVL(department_id, -9999) != 30 AND -- null인 값에 부여해서 누락 없도록.
salary =ANY ( select distinct salary
from employees
where department_id = 30)
order by 1, 4;
-- select 되어져 나온 결과물에서 중복된 행값이 있을 때, 중복을 피하고 한번만 나타내고자 할 때는
-- select 바로 다음에 distinct를 넣어주면 중복된 행을 제거하고 나타낼 수 있다.
(2) ALL 사용 예시
-- HR 테이블 활용
-- 기본급여(salary)가 제일 많은 사원만
-- 사원번호, 사원명, 기본급여를 나타내세요.
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, salary AS 기본급여
from employees
where salary >= ALL(select salary from employees where salary is not null);- ALL은 AND와 유사하여 하나라도 false면 전체가 false가 된다. 급여가 제일 많은 사람이 24000인데, 위의 where 조건문이 성립하려면 모두와 salary의 값이 같거나 큰 24000 만이 출력되게 된다.
- Sub Query에서의 ALL은 null 값이 있으면 계산이 되지 않기 때문에 주의하여야 한다.
3) PairWise(쌍) Sub Query
- 사용 예시
--employees 테이블에서
--부서번호별로 salary가 최대인 사원과
--부서번호 별로 salary가 최소인 사원의 정보를
--부서번호, 사원번호, 사원명, 기본급여를 나타내시오.
--> 다른 부서에 salary가 같은 직원이 있을 수 있으니 salary와 department_id를 한 쌍으로 생각하여야 한다.
select department_id AS 부서번호
, employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, salary AS 기본급여
from employees
where (NVL(department_id, -999), salary) in(select NVL(department_id, -999), max(salary)
from employees
group by department_id )-- 이 결과물이 부서 번호별 최대 값이다.
-- 부서마다 1등과 꼴찌가 필요하기 때문에 특정 행만 가져오도록 where 사용
-- 괄호를 쳐주면 NVL(department_id, -999)(부서아이디)와 salary가 한 쌍(pair)이 된다.
-- 그냥 salary만 보는 것이 아니라, department_id의 salary의 max 값을 구해오는 것.
OR
(NVL(department_id, -999), salary) in(select NVL(department_id, -999), min(salary)
from employees
group by department_id )
order by 1,4; 4) 상관서브쿼리(서브상관쿼리)
- 상관서브쿼리(=서브상관쿼리)라 함은 Main Query(=외부쿼리)에서 사용된 테이블(뷰)에 존재하는 컬럼이 Sub Query(내부쿼리)의 조건절(where, having 절) 사용되어질 때를 상관서브쿼리(=서브상관쿼리) 라고 부른다.
-- HR 테이블 사용
-- employees 테이블에서 기본 급여에 대해 전체 등수를 구하세요.
select E.department_id AS 부서번호
, E.employee_id AS 사원번호
, E.salary AS 기본급여
, ( select count (*) +1
from employees
where salary > E.salary ) AS 전체등수
-- E.salary는 내급여(기본급여)
-- 둘 다 똑같이 salary > salary로 적어주면 내부쿼리의 salary로 겹치기 때문에
-- 여기서의 E.salary은 반드시 외부쿼리의 E.을 넣어주어야 한다. 위의 select 문에서는 E. 생략 가능
from employees E
-- 외부 쿼리의 employess를 별칭을 준다.
order by 3 desc;
-- 외부쿼리의 컬럼(E.salary)이 서브쿼리의 where 조건절(E.salary)에 쓰이고 있는데, 이것을 서브상관쿼리라고 부른다. *상관서브쿼리로 데이터 update, insert, delete 가능
3. JOIN
- JOIN(조인)은 테이블(뷰)과 테이블(뷰)을 합치는 것을 말하는데
행(ROW) 과 행(ROW)을 합치는 것이 아니라, 컬럼(COLUMN) 과 컬럼(COLUMN)을 합치는 것을 말한다. (ex. first_name과 Last_name을 합치면 join)- 위에서 말한 행(ROW) 과 행(ROW)을 합치는 것은 UNION 연산자를 사용하는 것이다.
-
SQL 1992 CODE 방식 : 테이블(뷰) 과 테이블(뷰) 사이에 콤마(,)를 찍어주는 것.
콤마(,)를 찍어주는 것을 제외한 나머지 문법은 데이터베이스 밴더(회사) 제품마다 조금씩 다르다. -
SQL 1999 CODE 방식(ANSI) : 테이블(뷰) 과 테이블(뷰) 사이에 JOIN 이라는 단어를 넣어주는 것. ANSI(표준화) SQL
-- SQL 1992 CODE
select *
from employees, departments; -- 107명이 10번 부서와 하나하나 매핑되고, 그 다음에 다시 107번이 20번 부서와 하나하나 매핑되고 ... 이렇게 2889번까지 반복
-- SQL 1999 CODE 방식(ANSI)
select *
from employees CROSS JOIN departments;- HR 테이블을 활용한 CROSS JOIN 사용 예시
-- 사원번호 사원명 부서번호 기본급여 기본급여평균과의차액을 나타내기
SELECT A.employee_id AS 사원번호
, A.ename AS 사원명
, A.department_id AS 부서번호
, A.salary AS 부서번호
-- , B.avg_salary AS 기본급여평균
, A.salary - B.avg_salary AS 기본급여평균과의차액
-- 여기서는 A. B. 생략 가능
from
(
select employee_id
, first_name || ' ' || last_name as ENAME
, department_id
, salary
from employees
) A
CROSS JOIN -- 107 * 1개이기 때문에 107개 행만 나온다.
(
select TRUNC( avg(salary) ) as AVG_SALARY
from employees
) B
order by 5;
이런 저런 기록들