Introduction
LOG: 3:08 AM 6/24/2020
NCS <SQL 응용> 시험을 마치고, 복기 및 추가 내용 정리가 필요하다고 생각되어 그 내용을 이 곳에 정리한다. 기출된 문제들과 직접 작성했던 답안들을 가감없이 옮겨낸 후, 보충이 필요한 부분은 추가로 주석을 달겠다.
SQL 응용 - 서술형(신)
1. 데이터베이스의 관리자 계정과 사용자 계정의 역할에 대하여 설명하시오. (40점)
기존 작성 답안:
관리자 계정: 즉 Super User.
sys, system 두 가지의 계정을 기본적으로 갖고 있으며,
데이터베이스 생성 권한은 sys만 가지고 있으며 system은 가지고 있지 않다.
그 외 CRUD에 필요한 모든 권한을 다 가지고 있다.
또한 이러한 권한을 사용자 계정에 부여할 수 있다.
DDL인 grant를 통해 권한을 부여하고, revoke를 통해 회수할 수 있다.
사용자 계정:
신규 생성하게 되면 권한이 없는 상태로,
관리자 계정으로부터 권한을 부여받아야 해당 작업을 할 수 있다.
(데이터베이스 보안성 향상)
일반적으로 권한의 묶음인 resources Role을 부여하는 경우가 많다.
관리자 계정(Administrator) 계정이란 Database 생성과 관리를 담당하며, DB에 대한 모든 권한과 책임을 지는 계정이다.
또한 사용자(user) 계정은 General User Account라고 부르며, Database에 대한 Query(질의), 갱신, 보고서 작성 등의 작업을 수행할 수 있는 계정이다. 이는 보안상 업무에 필요한 최소한의 권한만 가지는 것을 원칙으로 한다.
2. 다음은 오라클 DB의 집합 연산자(set operator)의 종류와 특징을 설명하시오. (25점)
기존 작성 답안:
SET OPERATOR란?
union의 상위 개념인 집합 연산자.
join이 Column과 Column간의 결합을 도출하는 것이라면
set operator는 Row와 Row간의 결합을 말한다.
- UNION/UNION ALL
합집합. 예를 들어 두 테이블의 정보를 조회할 때 A테이블과 B테이블의 데이터간 대응/미대응 여부와 관계없이 두 테이블의 모든 데이터를 조회한다. 때문에 결합될 두 쿼리문의 선언 순서는 중요하지 않다. (어차피 합집합의 결과, 즉 모든 데이터를 리턴할 것이므로)
이 때 union과 union all의 결정적인 차이점이라면
1. 중복 데이터의 허용 여부
2. 정렬 여부이다.
union의 경우 합집합한 모든 데이터 중 중복 데이터는 허용하지 않으며,
이를 기본적으로 오름차순 정렬한 값을 리턴하나,
union all은 데이터의 중복 여부와 상관없이 "모든" 데이터를 리턴하며,
별도의 정렬 처리가 자동적으로 이뤄지지 않는다.
문법의 기본 양식은 다음과 같다:
e.g.
--UNION
select dept_code
from employee
union
select dept_id
from department;
--UNION ALL
select dept_id
from department
union all
select dept_code
from employee;
이와 같이 선언하였을 때 department 테이블과 employee 테이블의
dept id/dept_code의 Column이 동일하게 각 24행으로 구성되어 있으며
그 값들이 완전하게 같다고 할 때,
union의 조회 결과는 24행이 출력되며 자동으로 오름차순 정렬되고,
union all의 조회 결과는 정렬없이 48행이 출력될 것이다.
-
INTERSECT
교집합. 결합한 두 테이블의 "대응"데이터만이 결과로 도출된다.
이 역시 각 쿼리의 선언 순서는 중요하지 않다. -
MINUS
차집합.
먼저 선언된 테이블의 모든 데이터에서 후위에 선언된 테이블의 모든 데이터를 뺀다.
따라서 위의 union, union all, intersect와 다르게 각 쿼리의 선언 순서가 매우 중요하며,
선언 순서가 달라지면 도출되는 결과도 완전히 달라지게 된다.
3. 오라클 PL/SQL 블록 문법 작성 구조를 기술하시오. (10점)
기존 작성 답안:
DECLARE(부차)
사용할 변수와 데이터 타입, 그 사이즈등을 선언한다.
또한 변수 선언 후에는 반드시 세미콜론을 붙여줘야 한다.
e.g.
num_1 number;
num_2 number;
name varchar2(20);
BEGIN(필수)
declare에서 선언한 변수에 값을 대입하거나,
연산문을 입력하거나, 출력문을 입력하는 등의 작업을 한다.
e.g.
num_1 := 20;
num_2 := 30;
EXCEPTION(부차)
예외처리할 예외 및 예외 발생시에 실행시킬 실행 구문을 작성한다.
END;(필수)
/(익명 블럭의 끝을 알림)
4. 다음의 요구사항에 대한 PL/SQL 구문을 작성하시오. (25점)
(요구사항)
- EMP 테이블에서 ENAME 이 'ALLEN' 인 직원의 사번(EMPNO)과 이름(ENAME) 을 조회하는 구문을 실행한다.
- 조회된 사번은 ENO 변수에 이름은 ENM 변수에 기록한다.
- ENO 변수의 자료형은 EMP 테이블의 EMPNO 컬럼의 자료형을 참조한다.
- ENM 변수의 자료형은 EMP 테이블의 ENAME 컬럼의 자료형을 참조한다.
- 아래의 형식으로 출력 처리한다.
사번 이름
-------------------------------------
1001 ALLEN
기존 작성 답안:
DECLARE
emp_row emp%rowtype;
BEGIN
select *
into emp_row
from emp
where ename = 'ALLEN';
dbms_output.put_line('사번 이름');
dbms_output.put_line('----------------------------------');
dbms_output.put_line(emp_row.empno || emp_row.ename);
END;
/
--DECLARE
--eno emp.empno%type;
--enm emp.ename%type;
--BEGIN
--select empno, ename
--into eno, enm
--from emp
--where ename = 'ALLEN';
--dbms_output.put_line('사번 이름');
--dbms_output.put_line('--------------------------------');
--dbms_output.put_line(eno || ' ' || enm );
--END;
--/모범답안:
DECLARE ENO EMP.EMPNO%TYPE; ENM EMP.ENAME%TYPE; BEGIN SELECT EMPNO, ENAME INTO ENO, ENM FROM EMP WHERE ENAME = ‘ALLEN’; DBMS_OUTPUT.PUT_LINE(‘사번 이름’); DBMS_OUTPUT.PUT_LINE(‘-----------------------‘); DBMS_OUTPUT.PUT_LINE(ENO || ‘ ‘ || ENM); END; /
SQL 응용 - 문제해결 시나리오
1. 오라클 PL/SQL 구문을 익명블록으로 작성하고 바로 실행하였으나 결과가 출력되지 않았다, 문제점과 해결방법을 [원인](10점)에 기술하고, 아래의 해결조건을 적용한 PL/SQL 구문을 [조치내용](30점)에 기술하시오. (40점)
(해결조건)
- PL/SQL 익명블록으로 작성
- 특정 사원이 커미션을 받는지 안 받는지를 구분해서 출력하는 구문으로 작성
출력 예: 사번 7788 은 SCOTT 사원이고 커미션을 받지 않습니다.
출력 예: 사번 7654 은 MARTIN 사원이고 커미션을 1400 받습니다. - SCOTT 계정의 EMP 테이블을 이용
사번 EMP.EMPNO, 이름 EMP.ENAME, 커미션 EMP.COMM - 해당 컬럼을 참조하는 변수들을 선언하고, 변수에 조회결과를 저장한 다을 출력되게 작성
- PL/SQL 조건문을 사용
기존 작성 답안:
[원인] 알맞게 익명 블록을 작성하였음에도 결과가 출력되지 않았다면
set serveroutput on;
을 빠트렸을 가능성이 크다.
이는 콘솔창 출력을 활성화 시키는 구문으로,
세션마다 실행하여야 한다.
따라서 익명 블럭을 작성하기 전에
set serveroutput on;
를 작성한 후 익명블럭을 재실행하면 제대로 출력된다.
[조치내용]
set serveroutput on;
DECLARE
eno emp.empno%type;
enm emp.ename%type;
ecomm emp.comm%type;
BEGIN
select empno, ename, comm
into eno, enm, ecomm
from emp
where empno = '&사번';
if ecomm is not null then
dbms_output.put_line('사번 '|| eno || '는 ' || enm || '사원이고
커미션을 ' || ecomm ||' 받습니다.');
else
dbms_output.put_line('커미션을 '|| ecomm ||'받지 않습니다.');
end if;
END;
/2. 아래의 내용에 따라 사용자 정의 롤을 만들어 사용자에게 롤로 권한을 부여하는 명령구문을 [원인](20점)에 기술하고, 아래의 공지사항을 저장할 NOTICE 테이블의 스키마를 참조하여 최근에 등록된 공지글 5개를 조회하는 TOP-N 분석 구문을 RANK() 함수와 ROWNUM을 각각 사용하여 2개의 SELECT 구문을 [조치내용](40점)에 작성하시오. (60점)
[원인]
- 생성할 롤 이름 : MYROLE
- 롤에 부여할 권한 : CREATE SESSION, CREATE TABLE, CREATE VIEW
- MYROLE 권한을 부여받을 사용자 : MYMY
[조치내용]
1. RANK() 함수를 사용한 TOP-N 분석 구문 작성
2. ROWNUM 사용한 TOP-N 분석 구문 작성
- 최근 등록된 공지글 5개 조회
- 모든 컬럼 조회함
- NOTICE 테이블 스키마
기존 작성 답안:
[원인]
create role MYROLE;
grant create session to MYROLE;
grant create table to MYROLE;
grant create view to MYROLE;
grant MYROLE to MYMY;[조치내용]
--1
select N.*,
rank() over(order by noticedate desc) RANK
from notice N
where rownum <= 5
order by noticedate desc;
--2
select *
from(
select *
from NOTICE
order by noticedate desc) N
where rownum <= 5;RANK() OVER() 사용한 구문, 모범답안:
SELECT * FROM (SELECT NOTICENO, NOTICETITLE, NOTICEWRITER, NOTICEDATE, NOTICECONTENT, ORIGINAL_FILEPATH, RENAME_FILEPATH, RANK() OVER (ORDER BY NOTICEDATE DESC) RANK FROM NOTICE) WHERE RANK >= 1 AND RANK <= 5; --OR select * from( select E.*, rank() over(order by hire_date DESC) rank from employee E ) E where rank between 1 and 5;RANK() OVER() 사용한 구문의 경우에는 표시할 행을 잘라낼 때에 rownum을 사용하지 않고, Inline View + Inline View Alias를 사용하여야 한다. rownum은 각 행을 해당 테이블에 대입할 때에(즉 insert시에) 부여된 고유의 번호로, where 조건절에 rownum을 사용하여 표시할 행을 자르는 경우 그 정렬 순위가 의도한 결과와 다르게 도출되게 된다!
