[Oracle] PL/SQL Class 4 - 과제2, 과제3

문제1.]  모든 사원에 대한 부서 이름, 사원 수, 평균 급여를 표시하는 쿼리문을 작성하라. 
열 별칭을 각각 Dname, Number of People, Salary로 한다.

답 :  select 

(select dname from dept de where de.deptno = em.deptno) "dname", 
count(*) "number of people", 
avg(sal) "평균급여"
from emp em
group by deptno;

해설 : 사원수, 평균 급여를 추출하기 위해 GROUP BY 구문을 이용하여 deptno 기준으로 그룹화 작업을 하고
컬럼에 부서명을 표시하기 위하여 dept 테이블을 JOIN 하여 서브 쿼리(스칼라 서브 쿼리)로 각 부서 번호에 맞는 부서명을
표시합니다.

문제2.]  아래와 같은 업무(JOB)를 하는 사람의 급여 합계를 표시하는 질의를 작성하라.

답 : select
sum(case job when 'ANALYST' then sal end) "ANALYST",
sum(case job when 'SALESMAN' then sal end) "SALESMAN",
sum(case job when 'CLERK' then sal end) "CLERK"
from emp;

해설 : 컬럼 단위별로 업무 기준 급여 합계를 표시해야 하며 CASE 조건문을 통해 업무에 맞는 급여를 추출한 뒤에
SUM() 함수를 컬럼 별로 사용하여 급여 합계를 처리하여 표시합니다.

문제3.]  다음의 SQL문은 ‘ORA-00934: …’ 예외가 발생한다. 이를 해결하기 위한 문장을 재작성하라.

답 : SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
having AVG(salary) > 8000;

해설 : SQL 판단 순서에 따라 FROM - WHERE - GROUP - HAVING - SELECT으로 진행이 되며,
WHERE 절에 AVG() 함수를 사용할 경우 아직 GROUP BY으로 그룹화 작업이 처리되지 않아 AVG() 함수 사용이
불가능합니다. 따라서 GROUP BY 그룹화 작업이 된 이후에 판단되는 HAVING 을 사용하여
그룹화 작업 이후에 조건 판단을 하여 추출합니다.

문제4.]  아래 문장을 순서대로 실행하였다. 올바른 결과를 모두 고르시오.

SQL> COMMIT;
SQL> DELETE EMP WHERE DEPTNO=10;
SQL> UPDATE EMP SET SAL=SAL+1000 WHERE DEPTNO=20;
SQL> ALTER TABLE TEST ADD(NAME VARCHAR2(5));
SQL> INSERT INTO TEST(NAME) VALUES(‘SCOTT’)
SQL> UPDATE EMP SET DEPTNO=40 WHERE DEPTNO=30;
SQL> ROLLBACK ;

답 :  2 , 4

해설 : ※ COMMIT : 버퍼에서 수행하고 있는 작업이 정상적으로 처리가 되어 DBF에 저장 하고 
     실행 중인 트랜젝션을 종료 하는 명령어
※ ALTER 명령은 DDL 명령어로 사용시 자동으로 COMMIT 과 동일한 기능을 실행 합니다.

1. EMP 테이블에 10번 부서원은 삭제 되지 않았다.
= DELETE 명령 이후 ALTER 명령이 실행되어 삭제된 결과로 저장이 되어 정상적으로 삭제된 것이 맞습니다.

3. EMP 테이블에 20번 부서원의 급여는 인상되지 않았다.
= UPDATE 명령 이후 ALTER 명령이 실행되어 UPDATE 실행 결과가 저장이 되어 인상 처리가 되었습니다.

5. EMP 테이블에 부서번호가 40인 데이터가 존재한다.
= ALTER 명령 이후에 실행된 명령어이며 이후 ROLLBACK 명령어가 실행되었기 때문에 데이터가 존재 하지 않습니다.

6. TEST 테이블에 SCOTT은 존재하지 않고 EMP 테이블의 급여 인상은 없다.
= ROLLBACK 으로 인하여 INSERT 명령은 실행 되지 않아 SCOTT는 존재 하지않으며 , ALTER 이전에 처리된 UPDATE명령으 로 EMP 테이블 급여 인상은 되었습니다.

문제5.]  다음의 제약조건 중 테이블 수준에서 부여할 수 없는 것은?

① unique // 
② primary key
③ not null
④ check

답 :  3

해설 :  unique, primart key, check 는 테이블 단위로 설정 가능 // not null은 컬럼 단위로만 설정 가능


문제6.]  부서번호가 10이거나 20인 사원의 사원번호, 이름, 급여, 업무, 부서명, 급여등급을 출력하라

답 : select 
em.empno "사원번호",
em.ename "이름",
em.sal "급여",
em.job "업무",
(select d.dname from dept d where d.deptno = em.deptno) "부서명",
gr.grade "급여등급"
from emp em, salgrade gr
where (em.deptno = 10 or em.deptno = 20)
and em.sal>gr.losal and em.sal<gr.hisal;

해설 :  부서 번호가 10 또는 20인 사원에 대해서 출력 해야 하며 WHERE 절에 조건을 주어 행 제한을 하고
제한된 행으로 부서명 , 급여등급으로 표시해야 하므로 각 dept , salgrade 테이블을 참고하여
부서명 컬럼은 dept 테이블 JOIN(Inner Join) 을 통한 서브쿼리(스칼라 서브쿼리)를 이용하여 부서명을 추출 하고 
급여등급은 salgrade 테이블을 JOIN(Non-Equi Join) 통해 등급별 급여 범위에 맞는 급여등급을 표시 합니다.

문제7.]  EMP 테이블에서 부하직원이 없는 사원의 모든 정보를 EXISTS를 이용하여 쿼리문을 작성하라. 

답 : select * from emp em
where not exists(select mg.ename from emp mg where mg.mgr = em.empno);

해설 : EXISTS 연산자는 서브쿼리의 결과가 존재하면 외부쿼리가 실행되는 연산자로
상사번호와 사원번호가 서로 비교 하였을때 결과가 없을때에만 외부쿼리가 실행 되어야 합니다.
따라서, NOT EXISTS 사용하여 서브쿼리(상관쿼리)의 결과가 없을때에만 외부쿼리가 실행 되도록 하여 사원의 모든 정보를 표시 합니다.

문제8.]  EMP 테이블에서  ENAME 컬럼에 특수문자 ‘S_’를 포함하는 사원의 모든 정보를 출력하라..

답 : select * from emp
where ename like '%S#_%' escape '#';

해설 :  'S_' 의 '_' 는 연산자로 해당 문자가 연사자로 사용되지 않고 문자로 사용되기 위하여 
escape 사용하여 문자로 인식하게 합니다.

문제9.]  부서번호가 10인 사원의 커미션과 부서 번호가 일치하는 사원의 이름, 부서 번호, 급여를 출력하라.

답 :  select ename "이름", deptno "부서번호", sal "급여" from emp em
where (deptno,nvl(comm,-1)) in 
(select deptno,nvl(comm,-1) from emp mg where mg.empno = em.empno and deptno=10);

해설 :  부서번호가 10인 사원의 커미션, 부서번호 와 일치하는 사원을 추출 해야 합니다.
따라서 서브쿼리(상관쿼리)로 부서가 10인 사원의 커미션과 부서번호를 추출 하여 외부쿼리와 비교를 하여 표시합니다.
단, 서브쿼리에서 null 값이 존재하여 null을 대체하기 위하여 음수 처리를 합니다.

문제10.]  업무별 급여평균이 2000을 초과하는 각 업무에 대해 업무(JOB)와 업무별 급여평균을 출력하라.
         급여평균의 컬럼 ALIAS는 AVG_SAL로 출력하고 급여평균을 오름차순으로 정렬하라.

답 : select job, avg(sal) "AVG_SAL" from emp
group by job
having avg(sal) > 2000
order by 2;

해설 :  업무별 급여평균이 2000을 초과하는 것을 확인  하기 위하여 업무별 급여평균을 먼저 구해야 하며,
GROUP BY 를 이용하여 업무 기준으로 그룹을 하고 이후 HAVING 과 AVG() 함수를 이용하여 2000 초과하는지
비교 연산처리를 하여 데이터를 추출 한 뒤에 ORDER BY 를 사용하여 급여평균을 기준으로 오름차순 정렬 합니다.


문제11.]  EMP 테이블에서 사원번호(EMPNO), 사원이름(ENAME), 관리자번호(EMPNO), 관리자 이름(ENAME), 관리자의 부서명(DNAME)을 출력. 단, 관리자가 없는 사람(KING)도 출력되도록 작성하라.

답 : select 
em.empno "사원번호", 
em.ename "사원이름",
mg.empno "관리자번호", 
mg.ename "관리자이름",
(select d.dname from dept d where d.deptno = mg.deptno) "관리자 부서명"
from emp em, emp mg
where em.mgr = mg.empno(+);

해설 : 각 사원에 맞는 관리자의 정보를 추출 해야하며, 셀프 조인을 통하여 관리자의 정보를 추출 하고 , 
서브쿼리(스칼라 서브쿼리)를 이용하여 관리자가 속해있는 부서명을 추출 하여 표시 합니다.

문제12.]  EMP 테이블에서 업무(JOB)별 급여평균 중 가장 많은 급여평균의 업무(JOB)와 급여평균을 출력하라.

답 : select job, avg(sal) from emp
group by job
having avg(sal) = (select max(avg(sal)) from emp group by job);

해설 : 업무별 급여 평균을 구해야 하며, 그러기 위해 GROUP BY 를 사용하여 업무별 급여 평균을 구합니다.
또, 업무별 급여평균 중 가장 많은 급여평균의 업무과 급여 평균을 구해야 하므로 서브쿼리를 이용하여 
가장많은 급여 평균을 구하고 HAVING 을 이용하여 업무별 급여 평균 과 가장 많은 급여평균의 비교 연산을 통하여
업무를 추출 하여 표시 합니다.

문제13.]  EMP 테이블에서 부서(DEPTNO)별 최소 급여를 받는 사원의 모든 정보를 출력하라.

답 :  select * from emp
where (deptno, sal) in (select deptno, min(sal) from emp group by deptno);

해설 : 부서별 최소 급여를 먼저 추출 하기 위하여 서브쿼리안에 GROUP BY 를 사용하여 부서별로 그룹화 작업을 하고 MIN 함수를 
사용하여 최소 급여를 추출한 뒤에 외부쿼리 비교 연산을 하여 최소 급여를 받는 사원의 모든 정보를 출력 합니다.

문제14.]  EMP 테이블에서 ROWNUM을 이용하여 가장 최근에 입사한 사원 순서로 3명을 출력하라.

답 : with 
emp_hire as (select empno,ename,hiredate from emp order by 3 desc)
select rownum, ename, hiredate from emp_hire
where rownum <=3;

해설 : WITH문으로 가장 최근에 입사한 사원순 으로 정렬된 테이블을 생성하고 ROWNUM으로 행제한을 하여
가장 최근에 입사한 사원 3명을 출력 합니다.

문제15.]  전체 사원 급여의 30% 이상을 차지하는 부서명과 그 부서의 급여합계를 출력하는 쿼리문이다 다음을 채워라.

WITH   ①        AS (SELECT ②      , SUM(SAL) TOT FROM EMP NATURAL JOIN DEPT
GROUP BY DNAME)
SELECT DNAME, TOT FROM DEPT_TOT
WHERE TOT >= (             ③                       ); 

답 :  ① dept_tot
② dname
③ select(sum(sal)*0.3) from emp

해설 :  WITH문으로 문법은 아래 와 같습니다.

WITH table1 as ( SELECT ~ )
SELECT ~ FROM table1

1번  쿼리문 FROM 절에 지정된 테이블명이 DEPT_TOT 이므로 WITH문에 테이블명은 DEPT_TOT 입니다.

2번  쿼리문 컬럼에 DNAME, TOT 2개의 컬럼이 DEPT_TOT 테이블에서 추출 되었으며, 컬럼 SUM(SAL) 의 별칭의 TOT  이므로 다른 컬럼의 이름은 DNAME 입니다.

3번  전체 사원 급여의 30% 의 값을 추출하는 서브쿼리 입니다.

문제16.]  다음을 채워라.

MERGE INTO EMP  E
  ①       (SELECT EMPNO,ENAME,SAL+1000 AS INSAL,DEPTNO+10 AS DNO
FROM EMP
WHERE DEPTNO=20)  T
ON (E.EMPNO=T.EMPNO)
WHEN MATCHED THEN 
UPDATE 
SET E.ENAME=T.ENAME, E.SAL=    ②    , E.DEPTNO=T.DNO
WHEN NOT MATCHED THEN 
INSERT (E.EMPNO,E.ENAME,E.SAL,E.DEPTNO)
VALUES(             ③                        )

답 :  ① USING
② t.insal
③ t.empno, t.ename, t.insal, t.dno

해설 :  MERGE문으로 문법은 아래 와 같습니다.

merge into 테이블명 
using 비교테이블명 on(두 테이블의 비교문-기본키대상)
when matched then
update set~
when not matched then
insert values~;

1번 문법에 따라서 비교테이블 앞에 오는 명령어로 USING 입니다.
2번 비교테이블의 별칭이 ' T ' 이며 E 테이블의 SAL 컬럼에 병합되는 컬럼으로 T 테이블의 SAL컬럼 입니다.
3번 INSET 되는 컬럼의 순서가 E.EMPNO, E.ENAME, E.SAL, E.DEPTNO 이며, 순서에 맞게 병합되는 T 테이블의 컬럼도
T.EMPNO, T.ENAME, T.SAL, T.DEPTNO 입니다.

문제17.]  SALES 부서에 근무하는 사원들의 급여를 5% 인상하라.

답 : update emp set sal=sal*1.05 where deptno = (select deptno from dept where dname = 'SALES');

해설 :  emp 테이블에는 부서번호로 되어 있어 부서명을 알 수 없으므로 서브쿼리를 이용하여 'SALES' 의 부서번호를 추출하여
비교 연산을 하고 5% 인상된 급여를 UPDATE 를 이용하여 테이블에 입력 합니다.

문제18.]  EMP 테이블의 20번 부서원들의 급여를 자신의 소속업무(JOB) 평균급여와 같게 변경하라.

답 : update emp em 
set sal = (select avg(sal) from emp mg where mg.job = em.job) 
where deptno = 20;

해설 :  20번 부서원들의 행만 추출 될 수 있도록 WHERE절에 조건을 추가하여 행 제한을 하고 서브쿼리(셀프조인)를 이용하여
자신의 소속업무의 평균 급여를 추출하여 UPDATE를 통해 변경 합니다.

문제19.]  EMP 테이블의 DEPTNO 컬럼 FK제약(제약명:FK_DEPTNO)을 비활성화(DISABLE) 하라.

답 :  alter table emp disable constraint FK_DEPTNO;

해설 :  테이블 속성에 대한 변경으로 ALTER 구문을 사용하여 제약명 ' FK_DEPTNO ' 을 ' DISABLE ' 으로 변경 합니다.

문제20.]  조건절에 사용되는 모든 컬럼은 인덱스가 생성되어 있다. 인덱스를 사용하지 못한 경우는?

A. SELECT ENAME FROM EMP WHERE ENAME=’ADAMS’;
B. SELECT ENAME, SAL FROM EMP WHERE SAL>=2000;
C. SELECT ENAME, JOB FROM EMP WHERE JOB<>’SALESMAN’;
D. SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE BETWEEN ‘85/01/01’ AND ‘90/12/31’;

답 : C

해설 : 부정 연산자 사용을 할 경우 인덱스 사용불가

문제21.]  USER에게 객체(Object)의 권한을 상속하여 부여하는 옵션은 어느 것인가?

A. WITH ADMIN OPTION
B. WITH GRANT OPTION
C. WITH CHECK OPTION
D. WITH OBJECT OPTION

답 : B

해설 : 객체 액세스 권한을 상속 하는 것으로 ' WITH GRANT OPTION ' 상속절로 권한 상속

문제22.]  SCOTT이 소유한 EMP TABLE 객체를 엑세스 권한이 있는 모든 계정이 소유자 지정없이 [INFO]라는 이름으로 엑세스 
하도록 동의어를 생성하는 명령이다. 다음을 채워라.

SQL> CONN /AS SYSDBA
SQL> CREATE   ①       SYNONYM INFO FOR    ②         ;

답 : ① public  ② scott.emp

해설 : Synonym(동의어) 형식
create [public] synonym sy_name for table_name;

create ① [public] synonym info for ② scott.emp; ( 계정.(계정이 소유한 테이블명) )



문제23.]  SNO, SNAME 이라는 두 개의 컬럼을 가진 TEST 테이블을 생성하라.  
           처리조건) SNO 컬럼은 숫자 5자리, SNAME 컬럼은 가변형 문자 10자리로 생성할 것

답 :  create table test(sno=number(5), sname=varchar2(10));

해설 :  테이블 객체 생성으로 CREATE TABLE 구문을 사용하고, 테이블명은 test로 지정하여
sno 컬럼은 숫자형 5자리인 number(5) 컬럼 속성을 지정하고
sname 컬럼은 가변형 문자 10자리인 varchar2(10) 컬럼 속정을 지정하여 객체 생성을 합니다.


문제24.]  존재하는 TEST 테이블의 SNAME 컬럼에 제약명이 SNAME_NN인 NOT NULL제약을 
          컬럼 레벨로 추가하라.

답 : ALTER TABLE TEST MODIFY 'SNAME_NN' NOT NULL FROM EMP;

해설 : TEST 테이블(객체)의 컬럼 속성을 변경 하는것으로 ALTER TABLE ~ MIDIFY를 사용하여 변경 한다.


문제25.]  KIM이라는 계정의 비밀번호를 ORACLE로 변경하라.

답 : alter user KIM identified by ORACLE

해설 : user(객체) 수정으로 alter user를 이용하고 비밀번호를 identified 통해 변경한다.


문제26.]  다음의 문장으로 생성된 뷰 empvu20에 대한 SQL문으로 부적합 것은?

CREATE OR REPLACE VIEW empvu20
AS SELECT empno, ename, sal, job, d_id
FROM employees
WHERE d_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;

①  INSERT INTO empvu20(empno, ename, d_id)
VALUES(1234, ‘Tmax’, 20);

②  DELETE empvu20;

③  UPDATE empvu20
SET D_ID = 30
WHERE job LIKE ‘%SALES%’;

④ DROP VIEW empvu20;

답 : 3

풀이 empvu20 뷰에는 d_id컬럼에 20에 대한 행 데이터만 있어 d_id = 30 에 대한 update는 처리 불가능 하다.


문제27.]  다음의 문장들이 가지는 의미를 설명하라.
GRANT update (department_name, location_id) ON departments TO scott, hr;

답 : departments 테이블의 department_name, location_id 2개의 컬럼의 대한 update 권한을 scott, hr 계정에 부여한다.

해설 : GRANT 부여권한 (대상컬럼) ON (대상테이블) TO (부여 계정);

문제28.]  릴레이션 R1에 저장된 튜플(행 데이터)이 릴레이션 R2에 있는 튜플을 참조하려면 참조되는 튜플이 반드시 R2에 존재해야 
한다는 데이터 무결성 규칙은?

A. 개체 무결성 규칙(Entity Integrity Rule)
B. 참조 무결성 규칙(Referential Integrity Rule)
C. 영역 무결성 규칙(Domain Integrity Rule)
D. 트리거 규칙(Trigger Rule)

답 : B

해설 : 1) 참조 무결성 (Referential integrity)
관계형 데이터베이스 모델에서 참조 무결성은 참조 관계에 있는 두 테이블의 데이터가 항상 일관된 값을 갖도록 
유지되는  것을 말한다.

문제29.]  다음의 관계와 관련된 설명 중 옳지 않은 것은?

A. 객체간의 관계를 생성하지 않는 것이 객체간의 독립적인 관리를 위해 권장된다.
B. 관계명은 구체적이어야 한다.
C. 관계는 업무 규칙을 반영하거나, 혹은 생성하는 작용을 한다.
D. 다대다 관계는 다른 세번째 테이블 생성 및 각각의 초기화 테이블로부터 연결된 일대다수 관계생성에 의해 
구현된다.

답 : A

해설 : 객체간의 관계 생성을 통해 다양한 데이터 추출이 가능하고 또한, 관계 생성을 하게 될 경우 같은 작업을 반복하는게 아닌
관계 생성된 객체에서 데이터를 가져 올 수있어 유지보수, 관리가 더욱 용이하다

문제30.]  관계 데이터베이스의 정규화에 대한 설명으로 옳지 않은 것은?

A. 정규화를 거치지 않으면 여러 가지 상이한 종류의 정보를 하나의 릴레이션으로 표현하여 
그 릴레이션을 조작할 때 이상(Anomaly) 현상이 발생할 수 있다.
B. 정규화의 목적은 각 릴레이션에 분산된 종속성을 하나의 릴레이션에 통합하는 것이다.
C. 이상(Anomaly) 현상은 데이터들 간에 존재하는 함수종속이 하나의 원인이 될 수 있다.
D. 정규화가 잘못되면 데이터의 불필요한 중복이 야기되어 릴레이션을 조작할 때 문제가 발생할 수 있다

답 : B

해설 : 관계형 데이터베이스의 설계에서 중복을 최소화하게 데이터를 구조화하는 프로세스를 정규화라고 한다.
정규화의 기본 목표는 관련이 없는 함수 종속성은 별개의 릴레이션으로 표현하는 것이다.

 

SQL 쪽지시험 문제 풀이.txt

0.02MB

 

ORACLE TEST                                              게임데이터분석 1기 성명:                   

※아래의 테이블을 참조하라.

EMP

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17	800		20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30
7566	JONES	MANAGER	7839	1981-04-02	2975		20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850		30
7782	CLARK	MANAGER	7839	1981-06-09	2450		10
7788	SCOTT	ANALYST	7566	1987-07-13	3000		20
7839	KING	PRESIDENT		1981-11-17	5000		10
7844	TURNER	SALESMAN	7698	1981-09-08	1500	0	30
7876	ADAMS	CLERK	7788	1987-07-13	1100		20
7900	JAMES	CLERK	7698	1981-12-03	950		30
7902	FORD	ANALYST	7566	1981-12-03	3000		20
7934	MILLER	CLERK	7782	1982-01-23	1300		10

 

DEPT				SALGRADE
DEPTNO	DNAME	LOC		GRADE	LOSAL	HISAL
10	ACCOUNTING	NEW YORK		1	700	1200
20	RESEARCH	DALLAS		2	1201	1400
30	SALES	CHICAGO		3	1401	2000
40	OPERATIONS	BOSTON		4	2001	3000
				5	3001	9999

※ 해당 컬럼의 설명입니다. 문제해결에 참고하세요.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
사원번호	사원명	업무	관리자번호	입사일	급여	수당	부서번호

 

DEPTNO	DNAME	LOC		GRADE	LOSAL	HISAL
부서번호	부서명	근무지역		급여등급	하한값	상한값

문제1.] 모든 사원에 대한 부서 이름, 사원 수, 평균 급여를 표시하는 쿼리문을 작성하라.
          열 별칭을 각각 Dname, Number of People, Salary로 한다.

답: select (select dname from dept d where d.deptno=e.deptno) "Dname", count(empno) "Number of People", avg(sal) "Salary" from emp e

group by e.deptno;

해설: 부서 이름과 사원 수 평균 급여를 함께 추출하기 위해서는 서로 다른 테이블을 함께 조합해야 하므로 join으로 진행, 테이블에 별칭을 부여한다. dname 컬럼은 다른 컬럼들처럼 emp가 아닌 dept에서 추출하므로 dept의 데이터와 emp의 데이터와 같다는 것을 표기해준다. 마지막으로 부서별로 묶어주는 group by 절로 마무리한다.

문제2.] 아래와 같은 업무(JOB)를 하는 사람의 급여 합계를 표시하는 질의를 작성하라.

ANALYST	SALESMAN	CLERK
6000	5600	4150

문제3.] 다음의 SQL문은 ‘ORA-00934: …’ 예외가 발생한다. 이를 해결하기 위한 문장을 재작성하라.

SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id;

답:

 

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 8000;

 

해설: ORA-00934 에러는 그룹함수를 허가하지 않는다는 뜻으로, 원 예시의 경우에서처럼 그룹함수 지정 전에 where 조건으로 먼저 avg(sal)>8000을 속아내게 되면  뒤에 그룹 지정이 의미가 없어지므로, department_id 별 그룹 지정을 먼저하고 그룹 함수 뒤에 사용할 수 있는 조건절인 having을 사용하여 에러를 해결해야 한다.

문제4.] 아래 문장을 순서대로 실행하였다. 올바른 결과를 모두 고르시오.

SQL> COMMIT;  SQL> DELETE EMP WHERE DEPTNO=10;  SQL> UPDATE EMP SET SAL=SAL+1000 WHERE DEPTNO=20;  SQL> ALTER TABLE TEST ADD(NAME VARCHAR2(5));  SQL> INSERT INTO TEST(NAME) VALUES(‘SCOTT’)  SQL> UPDATE EMP SET DEPTNO=40 WHERE DEPTNO=30;  SQL> ROLLBACK ;
①	EMP 테이블에 10번 부서원은 삭제 되지 않았다.
②	EMP 테이블에 10번 부서원은 삭제되었다.
③	EMP 테이블에 20번 부서원의 급여는 인상되지 않았다.
④	TEST 테이블에 NAME 컬럼은 생성되었다.
⑤	EMP 테이블에 부서번호가 40인 데이터가 존재한다.
⑥	TEST 테이블에 SCOTT은 존재하지 않고 EMP 테이블의 급여 인상은 없다.

해설: 마지막에 Rollback;을 하면 아래에서부터 위로 진행한 명령들을 되돌리게 되는데, 중간에 ALTER~ 문의 경우 자동으로 트랜젝션이 종료(데이터베이스 저장) 되므로, 사실상 Rollback 시점에서 Alter~ 문 아래 시점까지의 명령만 되돌려진다고 보아야 한다.

문제5.] 다음의 제약조건 중 테이블 수준에서 부여할 수 없는 것은?

①	unique
②	primary key
③	not null
④	check

해설: 제약 조건에 해당하는 보기의 unique, primary key, not null, check는 모두 데이터의 무결성을 유지, 보장하고 특정 컬럼에 원하는 조건의 데이터만 저장하기 위해 사용되는 것들이다.  그중 not null은 테이블이 아닌 컬럼 단위로만 설정할 수 있다.

문제6.] : 부서번호가 10이거나 20인 사원의 사원번호, 이름, 급여, 업무, 부서명, 급여등급을 출력하라.

답: select e.empno, e,ename, e.sal, e.job, d.dname, s.grade from emp e, dept.d, salgrade s

where e.deptno=d.deptno and e.deptno=10 or e.deptno=20 and (e.sal>=losal and e.sal<=hisal);

해설: 추출을 원하는 컬럼들이 서로 다른 테이블로부터 가져와야 될 경우 join을 사용한다. join을 사용할 경우 생기는 컬럼의 모호성을 해결하기 위해 지었던 별칭간에 관계 정리를 위해 조건절에 각 테이블의 기본키와 왜래키인 e.deptno와 d.deptno가 같다는 걸 밝힌다. 그 뒤에 부서번호가 10이거나 20인 사원의 조건을 더하고 마무리로 s.grade의 조건이 e.sal에서 최소값과 최대값 사이에 이뤄진다는 것을 밝힌다.

문제7.] EMP 테이블에서 부하직원이 없는 사원의 모든 정보를 EXISTS를 이용하여 쿼리문을 작성하라.

답: select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno from emp e

where e.empno=m.empno and not exists(select m.ename from emp m where m.mgr=e.empno);

해설: 사원 정보 추출을 위한 모든 컬럼을 작성하고, 회사의 사원이자 매니저이지 않은 사원(그럼에도 역시 회사의 사원인)인 조건을 더하기 위해 셀프 join을 사용하여 별칭을 써서 내용은 같지만 다른 두 개의 테이블을 설정한다. 여기에 '부하직원이 없는 사원'을 참으로 설정하기 위한 조건을 not exists를 서브쿼리로 사용하여 m.ename 중에 관리자 번호와 사원번호가 일치하는 경우를 거짓으로 규정하여 남은 행을 추출한다.  

문제8.] EMP 테이블에서  ENAME 컬럼에 특수문자 ‘S_’를 포함하는 사원의 모든 정보를 출력하라..

답: select ename from emp

where emane like 'S#_%' escape '#';

해설: 특정 문자 S에 더하여 특수문자 언더바 '_'가 붙어있는 값을 찾기 위해서는 조건으로 escape를 사용해야 한다. escape는 찾기 위한 특수 문자 앞에 특수문자(예로, 파운드(#)나 골뱅이(@))기호를 붙인 뒤 뒤에 escape '#' 또는 escape '@'로 마무리하는 식이다.

문제9.] 부서번호가 10인 사원의 커미션과 부서 번호가 일치하는 사원의 이름, 부서 번호, 급여를 출력하라.

풀지 못했습니다..

문제10.] 업무별 급여평균이 2000을 초과하는 각 업무에 대해 업무(JOB)와 업무별 급여평균을 출력하라.
         급여평균의 컬럼 ALIAS는 AVG_SAL로 출력하고 급여평균을

답: select job as "JOB", avg(sal) as "AVG_SAL"

group by job

having avg(sal)>2000

order by avg(sal)

해설: 추출할 컬럼 값인 "JOB"과 "AVG_SAL"을 select에서 설정하고 직업별로 묶어내도록 group by 를 지정한 뒤에 having 절을 사용하여 평균 급여가 2000 이상인 조건을 붙인다. 여기서 이 조건을 where 절에서 쓰지 않은 이유는 [문제3.]에서와 같이 수치를 따지기 전에 먼저 수행되어야 하는 group by 조건이 있기 때문에 그 뒤에 오는 having 절을 사용한 것이다. 마지막으로 급여 평균을 오름차순으로 정렬하기 위한 조건을 붙이고 마무리한다.오름차순으로 정렬하라.

문제11.] EMP 테이블에서 사원번호(EMPNO), 사원이름(ENAME), 관리자번호(EMPNO), 관리자 이름(ENAME), 관리자의 부서명(DNAME)을 출력. 단, 관리자가 없는 사람(KING)도 출력되도록 작성하라.

답: select e.empno as "사원번호", e.ename as "사원이름", e.mgr as "관리자번호", d.ename  as "관리자이름", c.dname from emp e, emp d, dept c

where e.mgr=d.empno(+) and d.deptno=c.deptno(+);

해설: 추출하고자 하는 컬럼을 나열하고, 서로 다른 테이블에서 가져와야 하는 컬럼이 있기에 별칭을 부여하는 join을 사용한다. 세 개의 테이블을 join하는 것으로  e.mgr과 d.empno가 같으나 마스터가 되는 컬럼이 e.mgr로 d.empno에 (+)를 붙인다. 마찬가지로 d.deptno와 c.deptno에서의 경우 d.deptno가 마스터로 c.deptno 옆에 (+)를 붙인다.

문제12.] EMP 테이블에서 업무(JOB)별 급여평균 중 가장 많은 급여평균의 업무(JOB)와 급여평균을 출력하라.

답: select job, avg(sal) from emp

group by job

having avg(sal)=(select max(avg(sal)) from emp group by job);

해설: 추출하고자 하는 직업과 업무별 급여 평균을 추출하기 위해 job과 avg(sal)을 emp 테이블에서 불러들인 뒤 직업별로 묶는 group by job을 조건으로 한다. 그 뒤에 가장 많은 급여평균의 업무와 급여 평균을 솎아 내기 위해 group by 다음에 쓰는 절인 having에 서브쿼리로 최대값을 조건으로 정해준다.

문제13.] EMP 테이블에서 부서(DEPTNO)별 최소 급여를 받는 사원의 모든 정보를 출력하라.

답: select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp

where sal in (select min(sal) from emp group by deptno);

해설: 추출하고자 하는 특정사원의 모든 정보 컬럼을 emp 테이블에서 추출하고 부서별 최소 급여를 받는 사원을 속아내기 위한 where 조건절에 서브쿼리를 사용한다.

문제14.] EMP 테이블에서 ROWNUM을 이용하여 가장 최근에 입사한 사원 순서로 3명을 출력하라.

답: select ename, hiredate from emp

order by hiredate desc

having rownum <=3;

해설: 추출하고자 하는 사원에 대한 정보 컬럼 이름(ename)과 입사일(hiredate)를 두고 추출하되  rownum을 활용해 위에서 셋만 행데이터를 추린다.

문제15.] 전체 사원 급여의 30% 이상을 차지하는 부서명과 그 부서의 급여합계를 출력하는 쿼리문이다

다음을 채워라.

WITH   ①        AS (SELECT ②      , SUM(SAL) TOT FROM EMP NATURAL JOIN DEPT

GROUP BY DNAME)

SELECT DNAME, TOT FROM DEPT_TOT

WHERE TOT >= (             ③                       );

답: 1. DEPT_TOT / 2. dname / 3. select (sum(sal)*0.3) from emp

해설: 1. WITH 문 시작에서 WITH 바로 다음에는 가상 테이블의 이름이 들어간다. 따라서 두 번째 줄에서 유추해볼 수 있듯이 1번은 DEPT_TOT이다. / 2. 두 번째 줄을 통해 순서상 유추해보자면 dname이 들어갈 것이다. / 3. TOT의 조건이 전체 사원 급여의 30% 이상을 나타내는 서브쿼리문이 채워져야 한다.

문제16.] 다음을 채워라.

         MERGE INTO EMP  E

  ①      (SELECT EMPNO,ENAME,SAL+1000 AS INSAL,DEPTNO+10 AS DNO

                   FROM EMP

                   WHERE DEPTNO=20)  T

ON (E.EMPNO=T.EMPNO)

WHEN MATCHED THEN

UPDATE

SET E.ENAME=T.ENAME, E.SAL=    ②    , E.DEPTNO=T.DNO

WHEN NOT MATCHED THEN

INSERT (E.EMPNO,E.ENAME,E.SAL,E.DEPTNO)

VALUES(             ③                        )

 

답: 1. USING / 2. T.INSAL / 3. T.EMPNO, T.ENAME, T.SAL, T.DEPTNO

 

해설: 1. MERGE 문의 문법에 따라 비교 테이블문을 기준으로 왼쪽에는 USING, 오른쪽에는 ON이 온다. / 2. E 테이블의 E.SAL과 병치하는 T 테이블의 컬럼은 T.INSAL이다. / 3. INSERT 뒤에 쓰인 E 테이블 컬럼들과 병치되는 T 컬럼의 T.EMPNO, T.ENAME, T.SAL, T.DEPTNO가 와야 한다.

문제17.] SALES 부서에 근무하는 사원들의 급여를 5% 인상하라.

답: update emp set sal=sal*1.05 where deptno = (select deptno from dept where dname = 'SALES')

해설: 데이터를 수정하는 것이기 때문에 데이터를 조작하는  명령 중 수정에 해당하는 update를 사용한다. 대신 SALE 부서에 근무하는 사원들로 한정하므로 where 조건절을 더하여 부서는 SALES라는 걸 가리켜야 하는데, 수정을 하는 emp 테이블에는 부서명이 없으므로 부서명(deptno)가 SALES라는 걸 가리키기 위해 서브쿼리를 활용한다.

문제18.] EMP 테이블의 20번 부서원들의 급여를 자신의 소속업무(JOB) 평균급여와 같게 변경하라.

답: update emp e set sal =(select avg(sal) from emp s where e.job=s.job)

group by deptno=20;

해설: 데이터를 수정하는 것이기 떄문에 데이터를 조작하는 명령 중 수정에 해당하는 update를 사용한다. 수정할 데이터가 있는 emp 테이블에서 또한 소속업무의 평균급여를 추출해내야 하기에 셀프join을 하여 같은 테이블이지만 각기 다른 테이블로써 별칭을 부여한다. 따라서 수정할 급여(sal) 데이터는 서브쿼리를 통해 소속업무의 평균급여와 같다고 표기하고 마지막으로 20번 부서원을 구분하는 의미의 group by 함수를 지정해준다.

문제19.] EMP 테이블의 DEPTNO 컬럼 FK제약(제약명:FK_DEPTNO)을 비활성화(DISABLE) 하라.

답: alter table emp disable constraint FK_DEPTNO;

해설: 객체인 테이블의 컬럼을 수정하는 것이므로 수정을 뜻하는 alter를 사용한다.

문제20.] 조건절에 사용되는 모든 컬럼은 인덱스가 생성되어 있다. 인덱스를 사용하지 못한 경우는?

A.   SELECT ENAME FROM EMP WHERE ENAME=’ADAMS’;

B.   SELECT ENAME, SAL FROM EMP WHERE SAL>=2000;

C.   SELECT ENAME, JOB FROM EMP WHERE JOB<>’SALESMAN’;

D.   SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE BETWEEN ‘85/01/01’ AND ‘90/12/31’;

해설: 다른지를 묻는 부정 연산자 <>를 사용하면 인덱스를 사용할 수 없다.

문제21.] USER에게 객체(Object)의 권한을 상속하여 부여하는 옵션은 어느 것인가?

A.   WITH ADMIN OPTION

B.   WITH GRANT OPTION

C.   WITH CHECK OPTION

D.   WITH OBJECT OPTION

해설: 데이터  관리, 제어 명령은 권한과 관련한 명령으로 객체에 권한을 부여(Grant)하거나 회수(Revoke)할 때 쓰인다.

문제22.] SCOTT이 소유한 EMP TABLE 객체를 엑세스 권한이 있는 모든 계정이 소유자 지정없이 [INFO]라는 이름으로 엑세스 하도록 동의어를 생성하는 명령이다. 다음을 채워라.

SQL> CONN /AS SYSDBA

SQL> CREATE   ①       SYNONYM INFO FOR    ②         ;

답: 1. public / 2. scott.emp

해설: 엑세스 권한이 있는 모든 계정이 소유자 지정 없이 액세스 할 수 있도록 하는 것은 Public Synonym을 생성하는 것이다. 따라서 1번의 답은 create public synonym 'public'이 되어야 한다. 뒤에는 Scott이 소유한 테이블 객체를 액세스하도록 하는 것이므로 for 뒤에 스콧의 emp 테이블이라는 걸 가리키는 scott.emp가 붙는다.

문제23.] SNO, SNAME 이라는 두 개의 컬럼을 가진 TEST 테이블을 생성하라. 
          처리조건) SNO 컬럼은 숫자 5자리, SNAME 컬럼은 가변형 문자 10자리로 생성할 것

답: create table TEST(sno=number(5), sname=varchar2(10));

해설: 객체인 테이블을 만드는 경우 생성을 뜻하는 create가 사용된다. TEST라는 테이블에 더해서 문제 요구 조건인 두 개의 컬럼을 속성과 함께 입력한다.

문제24.] 존재하는 TEST 테이블의 SNAME 컬럼에 제약명이 SNAME_NN인 NOT NULL제약을
         컬럼 레벨로 추가하라.

답: alter table TEST modify 'SNAME_NN' not null from emp;

해설: 객체인 테이블의 컬럼 속성을 변경하기 때문에 alter를 사용한다.  

문제25.] KIM이라는 계정의 비밀번호를 ORACLE로 변경하라.

답: alter user KIM identified by ORACLE

해설: 객체인 사용자와 관련한 수정이므로 alter을 사용하고 뒤에 indetified by를 통해 비밀번호를 변경한다.

문제26.]  다음의 문장으로 생성된 뷰 empvu20에 대한 SQL문으로 부적합 것은?

 

CREATE OR REPLACE VIEW empvu20 AS SELECT empno, ename, sal, job, d_id FROM employees WHERE d_id = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck ;
①	INSERT INTO empvu20(empno, ename, d_id) VALUES(1234, ‘Tmax’, 20);
①	DELETE empvu20;
③	UPDATE empvu20 SET D_ID = 30 WHERE job LIKE ‘%SALES%’;
④	DROP VIEW empvu20;

해설: 생성된 뷰 empvu20은 조건절을 통해 d_id 20만 추출해서 보고 있으므로 d_id=30과 관련한 처리를 할 수 없다.

 

문제27.] 다음의 문장들이 가지는 의미를 설명하라.

GRANT update (department_name, location_id) ON departments TO scott, hr;

답: Scott, hr 계정에  department_name 과 location_id를 수정(update)할 수 있는 권한을 상속한다.

문제28.] 릴레이션 R1에 저장된 튜플(행 데이터)이 릴레이션 R2에 있는 튜플을 참조하려면 참조되는 튜플이 반드시 R2에 존재해야 한다는 데이터 무결성 규칙은?

A.   개체 무결성 규칙(Entity Integrity Rule)

B.   참조 무결성 규칙(Referential Integrity Rule)

C.   영역 무결성 규칙(Domain Integrity Rule)

D.   트리거 규칙(Trigger Rule)

해설: 데이터가 서로 관계를 하기 위하여 참조를 해야하고 그러기 위해서는 참조하는 테이블간의 데이터가 같아야 하는, 그러니까 무결성을 가져야 한다.

문제29.] 다음의 관계와 관련된 설명 중 옳지 않은 것은?

A.   객체간의 관계를 생성하지 않는 것이 객체간의 독립적인 관리를 위해 권장된다.

B.   관계명은 구체적이어야 한다.

C.   관계는 업무 규칙을 반영하거나, 혹은 생성하는 작용을 한다.

D.   다대다 관계는 다른 세번째 테이블 생성 및 각각의 초기화 테이블로부터 연결된 일대다수 관계생성에 의해 구현된다.

해설: 객체간의 관계를 생성해야 관리에 유용하다.

문제30.] 관계 데이터베이스의 정규화에 대한 설명으로 옳지 않은 것은?

A.   정규화를 거치지 않으면 여러 가지 상이한 종류의 정보를 하나의 릴레이션으로 표현하여 그 릴레이션을 조작할 때 이상(Anomaly) 현상이 발생할 수 있다.

B.   정규화의 목적은 각 릴레이션에 분산된 종속성을 하나의 릴레이션에 통합하는 것이다.

C.   이상(Anomaly) 현상은 데이터들 간에 존재하는 함수종속이 하나의 원인이 될 수 있다.

D.   정규화가 잘못되면 데이터의 불필요한 중복이 야기되어 릴레이션을 조작할 때 문제가 발생할 수 있다.

 해설: 정규화의 목적은 각 릴레이션에 분산된 종속성을 통합 하는 것이 아니라 분산하는 것이다.

 

 

SQL_시험 답, 해설.doc

0.10MB