SQL 10일차

20230327 SQL

<차례>
그룹화 연산자
rollup
cube
grouping sets

계층 검색
START WITH
CONNECT BY PRIOR
ORDER SIBLINGS BY

DDL(Data Definition Language)
유저관리(권한)
유저생성

DCL(Data Control Language)
권한 부여
시스템 권한 부여
시스텐 권한 회수
객체 권한 부여
객체 권한 회수
유저 정보 수정
===== =====

union all(중복 포함; 중복성 없는 집합 합칠 때) > union
exists > intersect
not exists > minus

UNION ALL, UNION, INTERSECT, MINUS

SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id(+); -> 이거 안 됨!!

1)
SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;
2)
SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);
1)+2)
SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
UNION
SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

=> 문제점 : 동일 큰 테이블 두 번 엑세스함, 중복제거 위해 sorting 함(부하)

▽▽▽ (둘 다 모든 컬럼 보고싶다면?)

SELECT e.employee_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

=> 이게 최선이긴 하지만 ANSI 표준 못 쓰게 하는 기업 있음

[문제83] UNION -> UNION ALL을 이용해서 결과 집합을 출력해주세요.
SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
UNION
SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;
=> UNION 위 첫 번째 쿼리문에서 뽑은 정보는 두고, 추가로 ‘소속 사원이 없는 부서 정보’를 뽑자!

(풀이 과정)
SELECT department_id
FROM departments d
WHERE NOT EXISTS(SELECT ‘x’
FROM employees
WHERE department_id = d.department_id => 이거 절대 아님

(참고)
SELECT *
FROM departments
WHERE department_id IN (SELECT department_id
FROM employees);

▽▽▽(IN 절 보다는 EXISTS 절을 사용하는 것을 지향; NOT EXISTS도 마찬가지)

SELECT *
FROM departments d
WHERE EXISTS(SELECT 'x'
FROM employees
WHERE department_id = d.department_id);

SELECT *
FROM departments d
WHERE NOT EXISTS(SELECT 'x'
FROM employees
WHERE department_id = d.department_id);

(최종 답안)
SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
UNION ALL
SELECT NULL, department_name
FROM departments d
WHERE NOT EXISTS(SELECT 'x'
FROM employees
WHERE department_id = d.department_id);

=> 두 번째 쿼리문에서 사원 아이디 정보 자리에 ‘NULL’ 넣기,
큰 테이블에 두 번 ACCESS 하는 것은 STILL 문제점

[문제84]
1) department_id, job_id, manager_id 기준으로 총액급여 출력
2) department_id, job_id 기준으로 총액급여 출력
3) department_id 기준으로 총액급여 출력
4) 전체 총액 급여를 출력
1), 2), 3), 4)를 한꺼번에 출력해주세요.

=> grouping이 다르므로 중복 없음, union all 사용해도 무방

SELECT department_id, job_id, manager_id, sum(salary)
FROM employees
GROUP BY department_id, job_id, manager_id
union all
SELECT department_id, job_id, null, sum(salary)
FROM employees
GROUP BY department_id, job_id
union all
SELECT department_id, null, null, sum(salary)
FROM employees
GROUP BY department_id
union all
SELECT null, null, null, sum(salary)
FROM employees;

=> ‘null’ 사용; 컬럼 개수, 타입 일치하도록 채워넣기
문제점 : employees 테이블 네 번 access 함(rollup 문으로 해결)

[면접 때 물어봄]
OLTP(ONLINE TRANSACTION PROCESSING) - 실시간 금융 처리 등
OLAP((ONLINE ANALYTICAL PROCESSING) - pivot, grouping 집합 연산자, inline view 등
DW(DATA WAREHOUSE) - pivot, grouping 집합 연산자, inline view 등

■ ROLLUP(8i)

• GROUP BY 절에 지정된 열 리스트를 오른쪽에서 왼쪽 방향으로 이동하면서 그룹화를 만드는 연산자

SELECT a,b,c,sum(sal)
FROM test
GROUP BY a,b,c;

-> sum(sal) = (a,b,c)

SELECT a,b,c,sum(sal)
FROM test
GROUP BY ROLLUP(a,b,c);

-> sum(sal) = (a,b,c)
sum(sal) = (a,b)
sum(sal) = (a)
sum(sal) = ()

SELECT department_id, job_id, manager_id, sum(salary)
FROM employees
GROUP BY ROLLUP(department_id, job_id, manager_id)

-> sum(salary) = (department_id, job_id, manager_id)
sum(salary) = (department_id, job_id)
sum(salary) = (department_id)
sum(salary) = ()

■ CUBE(9i)

• ROLLUP 연산자를 포함하고 모든 그룹화를 할 수 있도록 만드는 연산자

SELECT a,b,c,sum(sal)
FROM test
GROUP BY CUBE(a,b,c);

-> sum(sal) = (a,b,c)
sum(sal) = (a,c)
sum(sal) = (a,b)
sum(sal) = (b,c)
sum(sal) = (a)
sum(sal) = (b)
sum(sal) = (c)
sum(sal) = ()

SELECT department_id, job_id, manager_id, sum(salary)
FROM employees
GROUP BY CUBE(department_id, job_id, manager_id)

-> sum(salary) = (department_id, job_id, manager_id)
sum(salary) = (department_id, manager_id)
sum(salary) = (department_id, job_id)
sum(salary) = (job_id, manager_id)
sum(salary) = (department_id)
sum(salary) = (job_id)
sum(salary) = (manager_id)
sum(salary) = ()

(예시) 이 두 가지 그룹만 출력하고 싶음
sum(sal) = (a,b)
sum(sal) = (a,c)

SELECT a,b,NULL,sum(sal)
FROM test
GROUP BY a,b
UNION ALL
SELECT a,NULL,c,sum(sal)
FROM test
GROUP BY a,c

▽▽▽ 개선!

■ GROUPING SETS(9iR2)

• 내가 원하는 그룹을 만드는 연산자

SELECT a,b,c,sum(sal)
FROM test
GROUP BY GROUPING SETS((a,b),(a,c));
sum(sal) = (a,b)
sum(sal) = (a,c)

SELECT a,b,c,sum(sal)
FROM test
GROUP BY GROUPING SETS((a,b),(a,c),());

sum(sal) = (a,b)
sum(sal) = (a,c)
sum(sal) = ()

SELECT department_id, job_id, manager_id, sum(salary)
FROM employees
GROUP BY GROUPING SETS((department_id, job_id), (department_id, manager_id),());

->
sum(salary) = (department_id, manager_id)
sum(salary) = (department_id, job_id)
sum(salary) = ()

[문제85] 연도 분기별 급여총액을 구하세요. 행의 합과 열의 합도 구하세요.
연도 1분기 2분기 ... 합

---

2001
2002
...
합

[이거 손코딩 물어봄]
=> CUBE 사용

(풀이 과정)
SELECT *
FROM
(SELECT to_char(hire_date, 'yyyy') 연도, to_char(hire_date,'q') 분기, sum(salary) 총액
FROM employees
GROUP BY to_char(hire_date, 'yyyy'), to_char(hire_date,'q'),
CUBE(to_char(hire_date, 'yyyy'),to_char(hire_date,'q')))
PIVOT (max(총액) FOR 분기 IN (1,2,3,4)); => 이거 아님

SELECT to_char(hire_date, 'yyyy'), to_char(hire_date,'q'), sum(salary)
FROM employees
GROUP BY CUBE(to_char(hire_date, 'yyyy'), to_char(hire_date,'q'));

SELECT to_char(hire_date, 'yyyy') year, to_char(hire_date,'q') quarter, sum(salary)
FROM employees
GROUP BY CUBE(to_char(hire_date, 'yyyy'), to_char(hire_date,'q'));

=> null 입력값을 어떻게 처리할 것인지? 고민해봐야 함

SELECT year, nvl(quarter,0) quarter, sumsal
FROM (SELECT to_char(hire_date, 'yyyy') year, to_char(hire_date,'q') quarter, sum(salary) sumsal
FROM employees
GROUP BY CUBE(to_char(hire_date, 'yyyy'), to_char(hire_date,'q')));

SELECT *
FROM (SELECT year, nvl(quarter,0) quarter, sumsal
FROM (SELECT to_char(hire_date, 'yyyy') year, to_char(hire_date,'q') quarter, sum(salary) sumsal
FROM employees
GROUP BY CUBE(to_char(hire_date, 'yyyy'), to_char(hire_date,'q'))))
PIVOT (max(sumsal) FOR quarter IN (1,2,3,4,0));

(최종 답안)
1) pivot 사용(이것을 선호)
SELECT *
FROM (SELECT year, nvl(quarter,0) quarter, sumsal
FROM (SELECT to_char(hire_date, 'yyyy') year, to_char(hire_date,'q') quarter, sum(salary) sumsal
FROM employees
GROUP BY CUBE(to_char(hire_date, 'yyyy'), to_char(hire_date,'q'))))
PIVOT (max(sumsal) FOR quarter IN (1 “1분기”,2 “2분기”,3 “3분기”,4 “4분기”,0 “합”))
ORDER BY 1;

2) pivot 사용X(결과 왜 안나오지)
SELECT
year 년도,
max(decode(quarter,1,sumsal)) 01,
max(decode(quarter,2,sumsal)) 02,
max(decode(quarter,3,sumsal)) 03,
max(decode(quarter,4,sumsal)) 04,
max(decode(quarter,NULL,sumsal)) 합
FROM (SELECT to_char(hire_date, 'yyyy') year, to_char(hire_date,'q') quarter, sum(salary) sumsal
FROM employees
GROUP BY CUBE(to_char(hire_date, 'yyyy'), to_char(hire_date,'q')))
GROUP BY year
ORDER BY 1;

■ 계층검색(hierarchical query)

• START WITH, CONNECT BY PRIOR

SELECT *
FROM employees
START WITH employee_id = 100 => 시작점
CONNECT BY PRIOR employee_id = manager_id; => 연결고리 조건

(TOP-DOWN 방식)
SELECT *
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id; => 101번 조직도만 보는 것임
(그 다음엔 누구를 출력하냐면 전 단계 사원 번호를 관리자 번호로 가진 사람 출력해)

(BOTTOM-UP 방식)
SELECT *
FROM employees
START WITH employee_id = 101
CONNECT BY employee_id = PRIOR manager_id; => 위 쿼리문과 결과 다름(2개 나옴)

SELECT *
FROM employees
START WITH last_name = 'King' => 두 명임(한 행으로 시작 권장)
CONNECT BY PRIOR employee_id = manager_id;

SELECT *
FROM employees
START WITH last_name = 'King'
CONNECT BY employee_id = PRIOR manager_id;

SELECT level, employee_id, last_name, manager_id
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

=> level: 계층구조 볼 수 있는 가상 컬럼

❍ 테이블 들여쓰기(LPAD)

• lpad 이용
  SELECT level, lpad(' ',level*2-2,' ')||last_name
  FROM employees
  START WITH employee_id = 100
  CONNECT BY PRIOR employee_id = manager_id;

❍ 계층 정렬 수행(ORDER SIBLINGS BY)

• 계층검색에서 정렬 수행을 할때는 SIBLINGS 키워드 반드시 입력
• ORDER SIBLINGS BY 에서는 컬럼의 위치표기법, 열 별칭을 사용할 수 없음

SELECT level, lpad(' ',level*2-2,' ')||last_name
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

❍ 대상 제외하기
1) where 절 사용 : 101번만 제거
SELECT level, lpad(' ',level*2-2,' ')||last_name
FROM employees
WHERE employee_id != 101
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

2) connect by prior, AND : 101번 포함 조직 전부 제거(분기 제거; 가지치기)
SELECT level, lpad(' ',level*2-2,' ')||last_name
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
AND employee_id != 101
ORDER SIBLINGS BY last_name;

[회사 시험문제][문제86] SELECT문을 이용하여 1 ~ 100까지 출력해주세요.
(내가 쓴 답 -> 이거 아님)
SELECT rownum
FROM employees
WHERE rownum between 1 and 100;

(풀이 과정)
SELECT level
FROM dual;
=> 오류: connect by 절이 필요함(start with은 필수X connect by는 필수O)

(최종 답안)
SELECT level
FROM dual
CONNECT BY level <=100;

[문제87] SELECT문을 이용하여 구구단 2단을 출력해주세요.

SELECT '2 * '||level||' = '||2*level "2단"
FROM dual
CONNECT BY level <= 9;

[문제88] SELECT문을 이용하여 구구단 2단 ~ 9단 출력해주세요.
(inline view 이용, cartesian 법 유도)
SELECT dan ||' * ' || num || ' = ' || dan*num 구구단
FROM (SELECT level + 1 dan
FROM dual
CONNECT BY level <=8),
(SELECT level num
FROM dual
CONNECT BY level <=9);

2주간 우리가 배운 것: DQL(Data Query Language) -> 3/14 수업자료 참고

• SELECT문 : 데이터베이스에 있는 데이터를 조회하는 SQL문

■ DDL(Data Definition Language)

• CREATE
• ALTER
• DROP
• RENAME
• TRUNCATE
• COMMENT

❍ 유저관리
■ 권한(privilege) : 특정한 SQL문을 수행할 수 있는 권리

• 시스템 권한 : 데이터베이스에 영향을 줄 수 있는 권한

• 객체 권한 : 객체(테이블)를 사용할 수 있는 권한

• ROLE(롤) : 유저에게 부여할 수 있는 권한을 모아 놓은 객체, 관리에 대한 편리성

• 유저 확인(우리의 경우 “USER이(가) “HR”입니다.“
  SHOW USER

• 내가 dba로부터 ‘’‘직접’‘’ 받은 시스템 권한을 확인하는 방법
  SELECT * FROM user_sys_privs;
  ▽▽▽(결과창)
  CREATE VIEW
  UNLIMITED TABLESPACE
  CREATE DATABASE LINK
  CREATE SEQUENCE
  CREATE SESSION => 가장 중요한 권한, 로그인(열쇠 같음!)
  ALTER SESSION
  CREATE SYNONYM

• 내가 ‘’‘직접’‘’ 받은 객체 권한을 확인하는 방법
  SELECT * FROM user_tab_privs;
  ▽▽▽(결과창)
  GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
  HR SYS DBMS_STATS SYS EXECUTE NO NO

• 내가 받은 롤에 대한 정보 확인(권한 관리 편리성)
  SELECT * FROM session_roles;
  ▽▽▽(결과창)
  ROLE
  CONNECT
  RESOURCE

• 내가 받은 롤 안의 시스템 권한 확인
  SELECT * FROM role_sys_privs;
  ▽▽▽(결과창)
  ROLE PRIVILEGE ADMIN_OPTION
  RESOURCE CREATE SEQUENCE NO
  RESOURCE CREATE TRIGGER NO
  RESOURCE CREATE CLUSTER NO
  RESOURCE CREATE PROCEDURE NO
  RESOURCE CREATE TYPE NO
  CONNECT CREATE SESSION NO
  RESOURCE CREATE OPERATOR NO
  RESOURCE CREATE TABLE NO
  RESOURCE CREATE INDEXTYPE NO

• 내가 받은 롤 안의 객체 권한 확인(결과 없음!)
  SELECT * FROM role_tab_privs;

❍ 유저 생성(sys 계정에서 수행)
dba > SHOW USER > “SYS”입니다.

• 땅 확인(데이터스페이스 확인)
  SELECT * FROM dba_data_files; => hr에서는 권한 없어서 못 봄

• 메모리에서 sort작업 다 할 수 없을시 일시 저장하는 디스크 장소
  SELECT * FROM dba_temp_files;
  1) order by
  2) distinct 중복 제거
  3) union
  4) join
  5) 9i 이전 버전에서는 group by도(9r 이후 hash group by)

• 유저 생성
  CREATE USER insa
  IDENTIFIED BY oracle
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 10M ON USERS;

=> SELECT * FROM dba_users;에서 생성되었는지 확인

• 사용할 수 있는 테이블 확인 방법
  SELECT * FROM user_ts_quotas; => hr에서 확인시
  ▽▽▽(결과창)
  MAX_BYTES
  USERS 1638400 -1 200 -1 NO
  => MAX_BYTES “-1“ 무한으로 사용 가능 의미

SELECT * FROM dba_ts_quotas; => dba에서 확인시

■ 권한부여
■ DCL(Data Control Language)

• GRANT
• REVOKE

❍ 시스템 권한 부여

• GRANT CREATE SESSION TO INSA;
• SELECT * FROM dba_sys_privs WHERE GRANTEE = 'INSA';
  ▽▽▽(결과창)
  GRANTEE PRIVILEGE ADMIN_OPTION
  INSA CREATE SESSION NO

=> insa 데이터베이스 접속 (초록 플러스 버튼)
SELECT*FROM user_sys_privs;
SELECT*FROM session_privs;
SELECT*FROM user_tab_privs;
SELECT*FROM user_ts_quotas;
=> 스스로 확인할 수 있어야 함

❍ 시스템 권한 회수(sys 계정 수행)
REVOKE CREATE SESSION FROM INSA; => dba에서 해야 함

❍ 객체 권한 부여(객체의 소유자 또는 SYS가 부여할 수 있음)

=> insa 화면
SELECT FROM hr.employees; => 소유자, sys 모두 함
(sys에서 해도 owner: hr 나옴)
=> hr 화면
GRANT SELECT ON hr.employees TO insa; => insa가 확인할 수 있도록 권한 부여
SELECT FROM user_tab_privs; => 받은거, 부여한거 모두 확인 가능

=> 이제 insa 화면에서도 조회됨
❍ 객체 권한 회수(객체 소유자 또는 sys가 부여할 수 있음)
REVOKE SELECT ON hr.employees FROM insa; => hr, dba에서 함

■ 유저 정보 수정( sys 계정에서 수행)
ALTER USER insa
IDENTIFIED BY insa -- 패스워드 수정 => 동일할시 보안상 문제로 보통 안 됨
DEFAULT TABLESPACE users — 디폴트 테이블스페이스 수정
TEMPORARY TABLESPACE temp — 임시 테이블스페이스 수정
QUOTA UNLIMITED ON users; -- 테이블 스페이스 사용할 수 있는 양 수정
ACCOUNT LOCK; -- 계정 잠금
ACCOUNT UNLOCK —- 계정 잠금 해지

< ACCOUNT LOCK 할 시에 >
=>insa 화면에서
select*from user_users; => account_status locked로 나옴
=> RUN SQL COMMAND LINE 까만화면에서
SQL> conn insa/oracle
ERROR:
ORA-28000: the account is locked