251028 TIL

2025. 10. 28. 18:59ㆍCourses/아이티윌 오라클 DBA 과정

문제

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

SELECT department_id, job_id, manager_id, sum(salary)
FROM hr.employees
GROUP BY department_id, job_id, manager_id
UNION ALL
SELECT department_id, job_id, NULL, sum(salary)
FROM hr.employees
GROUP BY department_id, job_id
UNION ALL
SELECT department_id, NULL, NULL, sum(salary)
FROM hr.employees
GROUP BY department_id
UNION ALL
SELECT NULL, NULL, NULL, sum(salary)
FROM hr.employees
ORDER BY 1, 2, 3;

그룹함수

ROLLUP(8i)

GROUP BY 절에 지정된 열 리스트를 오른쪽에서 왼쪽 방향으로 이동하면서 그룹을 만드는 연산자
소그룹간의 소계 및 총계를 구하는 연산자

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

(department_id, job_id, manager_id)

(department_id, job_id)

(department_id)

()

→ 문제 42번과 같은 결과

괄호로 묶으면 하나의 집합 컬럼으로 간주

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

((department_id, job_id), manager_id)

((department_id, job_id))

()

CUBE(8i)

ROLLUP 연산자를 포함하고 모든 그룹화를 할 수 있는 연산자
모든 조합 생성

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

(department_id, job_id, manager_id)

(department_id, job_id)

(department_id, manager_id)

(job_id, manager_id)

(department_id)

(job_id)

(manager_id)

()

괄호로 묶으면 하나의 집합 컬럼으로 간주

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

(department_id, (job_id, manager_id))

(department_id)

(job_id, manager_id)

()

GROUPING SETS(9i)

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

SELECT department_id, job_id, manager_id, sum(salary)
FROM hr.employees
GROUP BY grouping sets((department_id, job_id), (department_id, manager_id), ());

(department_id, job_id)

(department_id, manager_id)

()

GROUPING

SELECT department_id, job_id, manager_id, sum(salary), grouping(department_id), grouping(job_id), grouping(manager_id)
FROM hr.employees
GROUP BY grouping sets((department_id, job_id), (department_id, manager_id), ());

0: 해당 컬럼이 실제 그룹화에 사용된 행 (즉, 일반 그룹 행)
1: 해당 컬럼이 집계에 포함되지 않은 행 (Subtotal, Grand Total)

문제

--[문제43] 년도,분기별 급여의 총액을 구하세요.
/*
YEAR             Q1         Q2         Q3         Q4         합
-------- ---------- ---------- ---------- ---------- ----------
2001          17000                                       17000
2002                     36808      21008      11000      68816
2003                     35000       8000       3500      46500
2004          40700      14300      17000      14000      86000
2005          86900      16800      60800      33400     197900
2006          69400      20400      14200      17100     121100
2007          36600      20200       2500      35600      94900
2008          46900      12300                            59200
             297500     155808     123508     114600     691416
*/

SELECT 
    year,
    max(decode(quarter, '1', sum_sal)) Q1,
    max(decode(quarter, '2', sum_sal)) Q2,
    max(decode(quarter, '3', sum_sal)) Q3,
    max(decode(quarter, '4', sum_sal)) Q4,
    max(decode(quarter, NULL, sum_sal)) 합
FROM (SELECT to_char(hire_date, 'yyyy') year, (to_char(hire_date, 'q')) quarter, sum(salary) sum_sal
        FROM hr.employees
        GROUP BY CUBE(to_char(hire_date, 'yyyy'), to_char(hire_date, 'q')))
GROUP BY year        
ORDER BY 1;

SELECT *
FROM (SELECT to_char(hire_date, 'yyyy') year, nvl(to_char(hire_date, 'q'), '합') quarter, sum(salary) sum_sal
        FROM hr.employees
        GROUP BY CUBE(to_char(hire_date, 'yyyy'), to_char(hire_date, 'q')))
PIVOT (max(sum_sal) for quarter in ('1' "Q1", '2' "Q2", '3' "Q3", '4' "Q4", '합' "합"))
ORDER BY 1;

계층검색(Hierarchical query)

테이블에 있는 행 간의 일반 계층 관계를 기반으로 데이터를 검색할 수 있음
트리 검색

TOP DOWN

SELECT *
FROM hr.employees
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id;

사원번호가 101번인 사원부터 시작
전 단계의 사원 아이디를 매니저 아이디로 가지는 데이터 조회

BOTTOM UP

SELECT *
FROM hr.employees
START WITH employee_id = 101
CONNECT BY employee_id = PRIOR manager_id;

전 단계의 매니저 아이디를 사원 아이디로 가지는 데이터 조회

ORDER SIBLINGS BY

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

계층 검색에서 그냥 정렬하면 계층이 깨지므로 SIBLINGS 키워드를 활용해야 함
형제 노드끼리 정렬
별칭, 위치 표기법 사용 불가

행 제외

SELECT level, employee_id, lpad(last_name, length(last_name)+(level * 2) - 2, ' ') name
FROM hr.employees
WHERE employee_id != 148 -- 특정 행만 제외
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

148번 사원 데이터만 제외

SELECT level, employee_id, lpad(last_name, length(last_name)+(level * 2) - 2, ' ') name
FROM hr.employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
AND employee_id != 148  -- 148번 조직은 전부 제거, 분기 제거
ORDER SIBLINGS BY last_name;

148번 사원을 포함한 하위 사원들 전부 제거

문제

-- [문제44] SELECT문을 이용해서 1 ~ 100 출력해주세요.
SELECT level
FROM dual
CONNECT BY level <= 100;

-- [문제45] SELECT문을 이용해서 2단을 출력해주세요.
SELECT 2 || ' * ' || level || ' = ' || level * 2 "2단"
FROM dual
CONNECT BY level <= 9;

-- [문제46] SELECT문을 이용해서 2단 ~ 9단을 출력해주세요.
SELECT dan|| ' * ' || num || ' = ' || dan * num "구구단"
FROM (SELECT level+1 dan
        FROM dual
        CONNECT BY level < 9),
    (SELECT level num
        FROM dual
        CONNECT BY level <= 9);

조인 → nested loop → 중첩 for문

권한(privilege)

특정한 SQL문을 수행할 수 있는 권리
시스템 권한 : 데이터베이스에 영향을 줄 수 있는 권한
객체 권한 : 객체(테이블, 뷰, 시퀀스, 프로시저, 함수, 패키지…) 를 사용할 수 있는 권한
ROLE(롤) : 유저에게 부여할 수 있는 권한을 모아 놓은 객체

권한 관련 뷰

시스템 권한

user_sys_privs : 내가 받은 시스템 권한 확인

show user; -- hr
SELECT * FROM user_sys_privs;

dba_sys_privs : 전체 시스템 권한 확인, dba 만 접근 가능

SELECT * FROM dba_sys_privs WHERE grantee = 'HR';

role_sys_privs : 내가 받은 롤의 시스템 권한 확인

SELECT * FROM role_sys_privs;

session_privs : 현재 세션의 시스템 권한 확인

SELECT * FROM session_privs;

-- 동일
SELECT * FROM user_sys_privs
UNION
SELECT * FROM role_sys_privs;

객체 권한

user_tab_privs : 내가 받은 객체 권한 확인

SELECT *
FROM user_tab_privs;

dba_tab_privs : 전체 객체 권한 확인, dba만 접근 가능

SELECT * FROM dba_tab_privs WHERE grantee = 'HR';

role_tab_privs : 내가 받은 롤의 객체 권한 확인

SELECT * FROM role_tab_privs;

롤

session_roles : 현재 세션 롤 확인

SELECT * FROM session_roles;

user_role_privs : 내가 받은 롤 확인

SELECT * FROM user_role_privs;

dba_role_privs : 전체 롤 확인, dba만 접근 가능

SELECT * FROM dba_role_privs
WHERE grantee = 'HR';

table 생성

table 생성 가능 조건

테이블을 생성할 수 있는 시스템 권한(CREATE TABLE) 여부 확인

SELECT * FROM session_privs;

사용할 수 있는 테이블스페이스 권한 확인

-- 사용자에게 할당된 테이블스페이스 사용량 및 사용 가능 용량 조회
SELECT * FROM user_ts_quotas;

USERS 테이블스페이스에 무한(-1)으로 저장 가능
TABLESPACE_NAME : 사용자에게 할당된 테이블스페이스 이름
BYTES : 현재 사용 중인 바이트 수
MAX_BYTES : 사용자에게 허용된 최대 사용 가능 바이트 수
BLOCKS : 현재 사용 중인 블록 수
MAX_BLOCKS : 사용자에게 허용된 최대 블록 수
DROPPED : 테이블스페이스가 삭제 예약 상태인지 여부

테이블 이름, 컬럼 이름, 유저 이름, 다른 객체 이름, 제약 조건 이름

문자로 시작
문자의 길이 1 ~ 30
문자, 숫자, 특수문자(_, #, $) 가능
대소문자 구분 X
동일한 유저가 소유한 객체 이름은 중복 불가
예약어 사용 불가

컬럼 타입

number(p, s) : 가변 길이 숫자 타입
- p : 전체 자리수
- s : 소수점 자리수
varchar2(100) : 가변 길이 문자 타입, 지정된 문자 길이 안에서 필요한 만큼만 저장
char(100) : 고정 길이 문자 타입, 무조건 지정된 문자 길이로 저장
date : 날짜 타입
clob : 가변 길이 문자 타입, 4GB
blob : 가변 길이 이진 데이터 타입, 4GB
bfile : 외부 파일에 저장된 이진 데이터 타입, 4GB
- 포인트 정보만 저장

오라클 저장 구조

객체 관련 뷰(테이블)

user_tables : 사용자 소유 테이블 조회

SELECT * FROM user_tables;

all_tables : 사용자 접근 가능 테이블 조회(권한 소유)

SELECT * FROM all_tables;

dba_tables : 데이터베이스 전체 테이블 조회, dba만 조회 가능

SELECT * FROM dba_tables;

저장 관련 뷰(데이터 파일)

dba_data_files : 데이터 파일 정보 조회

SELECT * FROM dba_data_files;

dba_temp_files : temp 파일 정보 조회

SELECT * FROM dba_temp_files;

공간 관련 뷰

user_ts_quotas : 사용자 테이블스페이스 사용량 및 사용 가능 용량 조회

SELECT * FROM user_ts_quotas;

dba_ts_quotas : 전체 테이블스페이스 사용량 및 사용 가능 용량 조회

SELECT * FROM dba_ts_quotas;

유저 생성

SYS 유저만 생성 가능

CREATE USER insa
IDENTIFIED BY oracle -- 비밀번호 설정
DEFAULT TABLESPACE users -- 테이블스페이스 지정
TEMPORARY TABLESPACE temp -- 임시 테이블스페이스 지정(정렬같이 PGA 안에서 다 처리하지 못하는 경우 사용)
QUOTA 10M ON users -- 용량 지정
ACCOUNT UNLOCK; -- 계정 잠금 해제

-- 현재 생성된 유저 정보 조회
SELECT * FROM dba_users;

유저 수정

ALTER USER insa
IDENTIFIED BY oracle
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA unlimited ON users
ACCOUNT UNLOCK;

권한 관리 - DCL(Data Control Language)

GRANT

권한을 부여하는 명령어

GRANT 권한명 TO 유저명;

REVOKE

권한을 회수하는 명령어

REVOKE 권한명 FROM 유저명;

시스템 권한 부여

테이블 생성

테이블 생성 권한 없음

테이블스페이스에 저장 가능한 용량은 남아있지만, CREATE TABLE 권한이 없음

CREATE TABLE 권한 부여

GRANT CREATE TABLE TO insa;

테이블 생성

저작자표시 비영리 변경금지 (새창열림)

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

251030 TIL (0)	2025.10.30
251029 TIL (0)	2025.10.29
251027 TIL (0)	2025.10.27
251024 TIL (0)	2025.10.24
251023 TIL (0)	2025.10.23

Joy's Devlog

태그

최근글

댓글

공지사항

아카이브

문제

그룹함수

ROLLUP(8i)

CUBE(8i)

GROUPING SETS(9i)

GROUPING

문제

계층검색(Hierarchical query)

TOP DOWN

BOTTOM UP

ORDER SIBLINGS BY

행 제외

문제

권한(privilege)

권한 관련 뷰

시스템 권한

객체 권한

롤

table 생성

table 생성 가능 조건

테이블 이름, 컬럼 이름, 유저 이름, 다른 객체 이름, 제약 조건 이름

컬럼 타입

오라클 저장 구조

객체 관련 뷰(테이블)

저장 관련 뷰(데이터 파일)

공간 관련 뷰

유저 생성

유저 수정

권한 관리 - DCL(Data Control Language)

GRANT

REVOKE

시스템 권한 부여

로그인

테이블 생성

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

관련글

티스토리툴바