[OracleDB] 새싹 실습 문제 정리

1. sesac이라는 테이블스페이스를 100m로 만드세요.

create tablespace sesac
  datafile '/opt/oracle/oradata/XE/sesac.dbf' size 100m;

 

2. sesac/sesac123이라는 유저/패스를 생성하세요.

     default tablespace는 sesac을 사용하고 temporary space는 temp를 사용하도록 지정하세요.

create user sesac
  identified by sesac
  default tablespace sesac
  temporary tablespace temp
  quota unlimited on sesac;

# quota unlimited on 구문은 특정 사용자에게  DB 테이블스페이스의 공간 할당량을 설정할 때 사용된다.

즉 해당 코드대로라면 sesac이라는 사용자는 sesac 테이블스페이스에 대해 무제한의 저장공간을 사용할 수 있게 된다.

 

3. 아래와 같이 employees 테이블을 생성하세요.

create table employees (
    employee_id number(7),
    last_name varchar2(25),
    first_name varchar2(25),
    deptno number(2),
    phone_number varchar2(20)
);

 

4. 3에서 생성한 테이블에 아래와 같이 4건의 데이터를 입력하세요.

insert into employees (employee_id, last_name, first_name, deptno, phone_number)
values (1, 'Biri', 'Ben', 10, '123-4566');
insert into employees (employee_id, last_name, first_name, deptno, phone_number)
values (2, 'Dancs', 'Betty', 20, '123-7890');
insert into employees (employee_id, last_name, first_name, deptno, phone_number)
values (3, 'Newman', 'Chad', 20, '123-8888');
insert into employees (employee_id, last_name, first_name, deptno, phone_number)
values (1, 'New', 'haha', 20, '123-8888');

 

5. employees 테이블의 EMPLOYEE_ID열에 PRIMARY KEY 제약조건을 추가하세요.

     만약, 제약조건이 걸리지 않는다면 이유를 말하시고, 수정 후 제약조건을 추가해 보세요.

alter table employees add constraint pk_employee_id primary key (employee_id);

-> pk 조건을 추가하려면 중복된 값이 없어야 하는데 employee_id가 3인 행이 두 개 있어서 중복이 발생한 상태이기 때문에 에러가 발생한다.

아래는 수정한 코드

update employees
set employee_id = 4
where last_name = 'New';

alter table employees add constraint pk_employee_id primary key (employee_id);

 

6. 생성한 EMPLOY 테이블을 삭제해 보세요. 

drop table employees;

 

----------------------------------------------------------------------------------------------------------------------

점검 문제

1. 오늘 소개팅한 상대와 사귀기로 했습니다. 사귄 지 100일이 되는 기념일 날짜를 계산하는 SQL 쿼리를 작성하세요.

SELECT SYSDATE + 100 FROM dual;

 

2. NULL에 대한 논리 연산표를 완성하세요.

NULL은 알 수 없는 값을 의미하므로 NULL or FALSE의 답은 NULL이다. 따라서 답은 아래와 같다.

 

3. 다음 Table에 대한 설명이 아닌 것을 고르세요.

• A: RDBMS의 논리적인 데이터 저장구조
• B: 행(Row)과 열(Column)으로 구성된 2차원 구조
• C: 데이터를 저장하기 위한 물리적 구조
• D: 다른 VIEW들을 기초로 한 Named Select이다.

정답: D -> 다른 VIEW들을 기초로 한 Named Select는 Table이 아닌 View를 의미한다.

 

4. 오라클의 오브젝트 중 기본 키 값을 생성하기 위해 자동으로 고유한 숫자 값을 생성하는 것은 무엇인가요?

정답: SEQUENCE

 

5. 오라클 오브젝트 중 잘 만들었을 때 쿼리 성능을 향상시킬 수 있는 것은 무엇인가요?

정답: INDEX

 

6. 다음 중 DML(Data Manipulation Language)에 해당하지 않는 것은?

• INSERT
• UPDATE
• COMMIT
• MERGE

정답: COMMIT

 

7. 다음 중 DDL(Data Definition Language)에 해당하지 않는 것은?

• ROLLBACK
• CREATE
• ALTER
• DROP

정답: ROLLBACK

 

8. 다음 중 ALIAS에 대한 설명으로 잘못된 것을 고르세요.

• A: 컬럼 이름을 재 정의한 별칭이다.
• B: 스페이스, 특수문자, 혹은 대소문자를 유지하기 위해 Single Quotes(' ')로 처리한다.
• C: AS 키워드는 사용 여부에 관계없이 적용된다.
• D: SELF JOIN 에는 반드시 사용해야 한다.

정답: B -> ' '이 아니라 " "

 

9. 다음 중 대소문자 조작 함수가 아닌 것은?

• A: LOWER
• B: UPPER
• C: TRIM
• D: INITCAP

정답: C -> TRIM은 문자열의 공백을 제거하는 함수

 

10. 다음 중 그룹함수가 아닌 것은?

• A: NVL
• B: COUNT
• C: SUM
• D: MAX

정답: A -> NVL은 널처리 함수로 data가 NULL일 때 설정한 임의의 값으로 바꿔준다.

 

11. 문자열을 DATE 타입으로 변환하는 함수는 무엇인가요?

정답: TO_DATE

 

12. EMPLOYEES 테이블에서 COMMISSION_PCT 컬럼이 NULL인 사원들의 LAST_NAME을 조회하는 올바른 쿼리를 쓰세요.

SELECT last_name FROM employees WHERE commission_pct IS NULL;

 

13. EMPLOYEES 테이블에서 LAST_NAME 컬럼의 세 번째 문자가 e인 사원들을 조회하는 쿼리를 쓰세요.

SELECT * FROM employees WHERE last_name LIKE '__e%';

 

14. 다음 중 SET OPERATOR에 해당하지 않는 것을 고르세요.

• A: UNION ALL
• B: MERGE
• C: MINUS
• D: INTERSECT

정답: B

 

15. 오라클 데이터베이스의 트랜잭션과 트랜잭션 사이에 서로 LOCK을 걸어두어 레코드를 접근하다 둘 다 대기 상태가 되는 현상을 무엇이라 하는가?

정답: 데드락

 

16. 오라클 데이터베이스에서 데드락이 발생할 때 일어나는 현상 및 대처 방법으로 잘못된 것을 2개 고르세요.

• A: 시스템이 완전히 다운되며 OS에서 프로세스를 종료한다.
• B: 데드락 발생 후 3초가 지나면 먼저 락에 걸렸던 세션 사용자의 명령이 취소된다.
• C: 먼저 데드락이 풀렸다면 내게 락을 건 사용자도 한 번 당해봐야 하므로 트랜잭션을 종료하지 않은 채로 컴퓨터를 켜놓고 귀가한다.
• D: 먼저 데드락이 풀리면 트랜잭션을 종료한다.

정답: A, C

 

17. SQL의 대표적인 6가지 절인 FROM, GROUP BY, HAVING, ORDER BY, SELECT, WHERE를 쿼리 작성 시 기술하는 순서로 올바르게 쓰세요. (주의: SQL 처리 순서가 아님)

정답: SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY

 

18. 현재 트랜잭션 동안 수행된 모든 명령어를 취소하기 위한 SQL 명령문은 무엇인가요?

정답: ROLLBACK

 

19. 다음 쿼리가 수행하는 작업의 의미를 서술하세요.

SELECT last_name  
FROM employees  
WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');

정답: last_name이 Abel인 사원의 급여보다 더 많은 급여를 받는 사원의 last_name을 출력한다.

 

20. 오라클의 트랜잭션에 대한 설명 중 잘못된 것을 고르세요.

• A: 하나 혹은 두 개 이상의 SQL문들로 이루어진 논리적인 작업단위
• B: 하나의 트랜잭션 내 모든 SQL은 함께 성공하거나 함께 실패해야 한다.
• C: 하나의 트랜잭션은 COMMIT 혹은 ROLLBACK으로 종료된다.
• D: SAVEPOINT는 하나의 트랜잭션 안의 모든 변화들을 데이터베이스에 영구적으로 반영한다. 

정답: D -> SAVEPOINT가 아니라 COMMIT

 

21. 트랜잭션이 시작된 시점의 데이터를 기준으로 일관성 있는 조회 결과를 유지하도록 보장하는 메커니즘은 무엇인가요?

정답: 읽기 일관성

 

22. 오라클의 오브젝트 중 named select라고 불리며 쿼리를 저장할 수 있는 오브젝트는?

정답: VIEW

 

23. 특정 데이터베이스 오브젝트에 대해 별칭을 설정하여 다른 스키마나 복잡한 오브젝트 이름을 간편하게 참조할 수 있도록 하는 오라클의 오브젝트는 무엇인가요?

정답: SYNONYM

 

24. 데이터베이스에서 기본 키가 중복되거나 외래 키가 참조되지 않는 값을 입력할 때 발생하는 오류는 무엇인가요?

정답: 무결성 제약조건

 

25. 데이터베이스 자원을 효율적으로 관리하기 위해 다양한 정보를 저장하며, 데이터베이스 서버에 의해 자동으로 갱신되는 시스템 테이블은 무엇인가요?

정답: 데이터 딕셔너리

 

26. 다음은 T_EMP 테이블의 인스턴스 차트입니다. 주어진 정보를 바탕으로 T_EMP 테이블을 생성하는 SQL 문을 작성하세요.

CREATE TABLE T_EMP (
    EMPNO NUMBER(4) NOT NULL,
    ENAME VARCHAR2(30),
    HP VARCHAR2(11),
    SAL NUMBER
);

 

27. EMPLOYEES 테이블에서 SALARY가 10000보다 크고 JOB_ID에 문자열 MAN이 포함된 사원의 이름(last_name)을 조회하는 쿼리를 작성하세요.

SELECT last_name 
FROM employees 
WHERE salary > 10000 AND job_id LIKE '%MAN%';

 

28. 다음 [ ]에 들어갈 수 있는 문장을 쓰세요.

SELECT department_id, last_name, AVG(salary) 
FROM employees 
GROUP BY [ ]

정답: department_id, last_name 
        -> 만약 Group by가 없다면 department_id와 last_name은 회원 수 만큼 행이 존재하는데 급여의 평균인 AVG(salary)는 하나 밖에 없으므로 행의 개수가 맞지 않아 오류가 발생한다. 따라서 select에 그룹함수가 존재하면 나머지를 Group by로 반드시 묶어주어야 한다.

 

29. 다음 ORACLE JOIN을 ANSI JOIN으로 다시 작성하세요.

// ORACLE JOIN
SELECT E.LAST_NAME, D.DEPARTMENT_NAME 
FROM EMPLOYEES E, DEPARTMENTS D 
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

// ANSI JOIN
SELECT E.LAST_NAME, D.DEPARTMENT_NAME 
FROM employees E 
INNER JOIN departments D 
ON E.department_id = D.department_id;

ORACLE JOIN을 ANSI JOIN으로 바꾸는 꿀팁: ,(쉼표)를 JOIN으로, WHERE을 ON으로 바꾼다.

 

30. ORACLE JOIN에서 불가능한 JOIN을 2개 쓰세요

정답: FULL OUTER JOIN, NATURAL JOIN

 

31. ANSI JOIN에서 가능한 JOIN을 4개 쓰세요

정답: FULL OUTER JOIN, JOIN~ON, NATURAL JOIN, JOIN~USING

 

32. ANSI JOIN 중 NATURAL JOIN을 지양해야 하는 이유는 무엇인가요?

정답: 조인 조건으로 사용되는 컬럼이 자동으로 결정되어 오류를 유발할 수 있기 때문에

 

33. 다음 SQL 쿼리는 employees 테이블에서 특정 문자열을 포함하는 이메일과 성(last_name)을 조회하기 위해 작성되었습니다. 다만, 쿼리 작성 시 일부 문제가 존재합니다. 쿼리의 문제점을 찾고, 개선 방안을 제시하세요.

SELECT last_name, email 
FROM employees 
WHERE UPPER(email) LIKE '%' || 'AR' || '%';

정답: UPPER(email)은 내부 컬럼에 사용한 함수이기 때문에 만약 회원이 5000만명 이면 email을 대문자로 바꾸는 작업을 5000만번 수행해야하므로 메모리가 크게 낭비된다. 

따라서 email을 입력할 때 조건을 추가하거나 AR을 UPPER(AR)로 바꾸는 것이 합리적이다.

 

34. 회사에서 직원들을 위해 사원들을 팀으로 나누려 합니다. employee_id가 짝수인 사원을 "짝꿍", 홀수인 사원을 "홀수"라고 구분하려 할 때, 이를 SQL로 구현하는 방법을 작성하세요.

SELECT employee_id, last_name, 
    CASE MOD(employee_id, 2) 
        WHEN 0 THEN '짝꿍' 
        WHEN 1 THEN '홀수' 
        ELSE '오류' 
    END AS team 
FROM employees;

# decode를 사용할 수도 있음( decode(mod(employee_id, 2), 0, '백군', 1, '청군'))

 

35. 다음 세 가지 SQL 쿼리가 수행하는 목적과 그 방식의 차이를 설명하세요.

(1)

SELECT last_name, salary, department_id 
FROM employees 
WHERE department_id || '' || salary IN ( 
    SELECT department_id || '' || MAX(salary) 
    FROM employees 
    GROUP BY department_id 
);

(2)

SELECT employee_id, last_name, salary, department_id 
FROM employees e 
WHERE salary = (SELECT MAX(salary) 
                FROM employees e2 
                WHERE e2.department_id = e.department_id);

(3)

SELECT last_name, salary, department_id 
FROM employees e 
WHERE (department_id, salary) IN (SELECT department_id, MAX(salary) 
                                  FROM employees d 
                                  GROUP BY department_id);

정답: 목적-> 부서 별로 가장 높은 급여를 받는 사원을 조회한다.

 

36. 전체 부서 중에서 평사원(ST_CLERK) 직책을 가진 사원이 없는 부서의 department_id를 조회하는 SQL 쿼리를 작성하세요.

SELECT department_id 
FROM departments 
MINUS 
SELECT department_id 
FROM employees 
WHERE job_id = 'ST_CLERK';