SQL 응용 - 시험(코드 버전)

[DATABASE/Oracle] - SQL - 응용 시험

시험-SQL응용.sql

1. 다음 sql구문 중 틀린 것은?

① SELECT ENAME, SAL
② FROM EMP
WHERE SAL > (SELECT ③ MAX(SAL), MIN(SAL) ④ FROM EMP);
-- 정답 3번 : 2번의 WHERE 절 sal > 은 max보다 큰것이 나올 수 없으므로, MIN(SAL)이여야 합니다.
select ename,sal from emp where sal > (select min(sal) from emp);
 

2. 급여가 500이 넘는 사원과 같은 부서에 근무하는 직원을 조회하는 쿼리문을 완성하시오
select ename, sal, deptno from emp
where DEPTNO in ( select distinct deptno
from emp where sal > 500 );
 

3. 서브쿼리가 위치할 수 없는 곳은?

① table_list문 (FROM) ② select_list문(select) ③ where, having 문 ④ group by문
정답: 4번 group by 문

 

4. 다음 중 틀린 곳은?

update ① table emp ② set sal = sal + 100 where deptno = ( select ③ deptno from dept ④ where dname = ‘경리부’ )
정답: 1번 update emp 로 수정하여야 합니다. [update table 테이블명]은 사용하지 않습니다.
 

5. emp06 테이블에 저장된 사원 정보중 과장들의 급여를 100로 인상하는 sql문을 작성하시오(서브쿼리 사용하기)
--
;
select * from emp06 where job='과장';

update emp06 set sal = sal*2.0
where job = '과장';

 
 

6. 박중훈과 동일한 직급을 가진 사원을 출력하는 SQL문을 작성하시오.(서브쿼리)

select * from emp where job=(select job from emp where ename='박중훈');
 

7. 직속상관이 감우성인 사원의 이름과 급여, 직급을 출력하시오
select ename, sal from emp where mgr=(select empno from emp where ename='감우성');
 

8. 부서별로 가장 급여를 적게 받는 사원의 정보를 출력하시오.
select empno, ename, sal, deptno 
from emp where sal in (select min(sal) from emp group by deptno);
 

9. 20번 부서에 소속된 사원 정보 출력하는 뷰를 만드시오 (뷰 이름은 : emp_view20)
CREATE OR REPLACE VIEW EMP_VIEW20
AS SELECT e.ename, d.dname, d.deptno FROM EMP E, DEPT D 
WHERE E.DEPTNO=D.DEPTNO;

desc emp_view20;
select * from emp_view20;

 

10. 뷰를 사용하는 이유를 서술하시오.
긴 쿼리문, 많이 쓰는 쿼리문을 뷰로 만들어 필요한 데이터로만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이하고 명령문이 간단해집니다.

뷰를 통해서만 데이터에 접근하게 하면 뷰에 나타나지 않는 데이터를 안전하게 보호하는 효율적인 기법으로 사용할 수 있습니다.

뷰는 데이터 보정작업, 처리과정 시험 등 임시적인 작업을 위한 용도로 활용되며, 뷰는 조인문의 사용 최소화로 사용상의 편의성을 최대화 합니다.


 

11. 10번 부서로 변경이 안되는 즉, 특정 컬럼값이 변경하지 못하는 뷰를 만드세요.
원본 테이블은 emp를 emp_copy로 생성하여 emp_copy 테이블을 사용하시오.
deptno 컬럼을 변경하지 못하는 뷰로 만드시고 update문을 써서 변경하여 보고 에러인 것 확인해 보기
create table emp_copy as select * from emp; -- emp_copy 테이블 생성 
create or replace view emp_view30 -- 뷰 생성
as
    select empno, ename, sal, comm, deptno
    from emp_copy where DEPTNO=30
with  check option; 

select * from emp_view30; -- 오류 확인해보기, 급여가 250 초과인 사람은 부서번호 20 이동하기
update emp_view30
set deptno = 20 
where sal > 250; 
 

12. 인라인 뷰를 사용하여서 입사일이 가장 느린 7명만 검색해 보세요.
 select rownum, ve.empno, ve.ename, ve.HIREDATE
    from (select empno, ename, hiredate from emp order by HIREDATE desc ) ve -- ve = 별칭
    where rownum <= 7;

13. 인덱스란?
SQL 명령문의 처리속도를 향상시키기 위해서 컬럼에 대해서 생성하는 오라클 객체입니다.

인덱스의 장단점을 서술하시오.

장점 : -검색 속도가 향상된다. 시스템에 걸리는 부하를 줄여서 시스템 전체 성능을 향상시킨다.

단점 : - 인덱스를 위한 추가적인 공간이 필요하다. 인덱스를 생성하는 데 시간이 걸린다.

        - 데이터의 변경 작업(INSERT/UPDATE/DELETE)이 자주 일어날 경우에는 성능 저하된다.

 

14. 부서 테이블에 부서번호를 자동으로 부여하시오

create table dept_ex (
    deptno number(4) primary key,
    dname varchar2(15),
    loc varchar2(15)
);

CREATE SEQUENCE dept_ex_seq
    start with 100 -- 100으로 시작
    increment by 10 -- 10씩 증가
    maxvalue 2000; -- 2000까지 허용
    
INSERT INTO dept_ex values ( dept_ex_seq.nextval, '경영부', '김포'); -- 시퀀스명, 컬럼 값 ~~
select * from dept_ex;

예) 10 11 12 13 ... 100 
SQL문 쓰고, 결과 캡쳐 

15. 사원 테이블의 직급인덱스를 생성하시오
create table emp01 as
select * from emp; -- 테이블 복사
select * from emp01; -- 잘 조회됐는지 확인

CREATE INDEX idx_emp01_job -- 인덱스 생성
on emp01(job);

select * from USER_IND_COLUMNS where TABLE_NAME in ('EMP01');
 

16. korea라는 사용자계정을 만들기 (부여 받은 시스템 권한을 다른 사용자엑 부여할 수 있도록)


 SQL> create user korea identified by 1234; -- 계정 생성
SQL> grant create table, create view to KOREA WITH ADMIN OPTION;


17. 시스템 권한이 ‘새롭게 사용자를 생성하는 권한’, ‘사용자를 삭제하는 권한’,
사용자를 위한 시스템 권한인 ‘데이터베이스에 접속할 수 있는 권한’, ‘사용자 스키마에서 테이블을 생성할 수 있는 권한’, ‘사용자 스키마에서 뷰를 생성할 수 있는 권한’을 권한을 부여하세요. ( korea라는 사용자에게 )
SQL> grant create user to korea; -- 새롭게 사용자를 생성하는 권한
SQL> grant drop user to korea; -- 사용자를 삭제하는 권한 
SQL> grant create session, create table, create view to korea; -- 데이터베이스 접속 권한, 사용자 스키마 테이블 생성 권한, 사용자 스키마에서 뷰를 생성할 수 있는 권한부여


 
18. korea사용자의 권한 중 ‘새롭게 사용자를 생성하는 권한’,‘사용자 스키마에서 뷰를 생성할 수 있는 권한’을 koreait라는 사용자에게 권한을 부여하시오.
SQL> sys as sysdba 계정으로 접속 후
SQL> create user koreait identified by 1234; -- koreait 계정 생성 
SQL> grant create session to koreait; -- koreait 세션 접속 권한 부여

SQL> conn korea /1234; -- korea 계정 접속
SQL> grant create user, create view to koreait; -- 유저 생성, 뷰 생성 권한을 korea계정에서 koreait로 부여


19. 스키마란?
객체를 소유한 사용자명을 의미합니다. 객체명 앞에 소속 사용자명을 기술하여 사용합니다.
객체는 테이블, 뷰, 인덱스와 같은 구조를 포함하는 논리적인 데이터 저장 구조를 가진 객체를 말합니다.
 

20. 롤 관련 데이터 딕셔너리에 대하여 서술하시오
ROLE_SYS_PRIVS : 롤에 부여된 시스템 권한 정보

ROLE_TAB_PRIVS : 롤에 부여된 테이블 관련 권한 정보

USER_ROLE_PRIVS : 접근 가능한 롤 정보

USER_TAB_PRIVS_MADE : 해당 사용자 소유의 오브젝트에 대한 오브젝트 권한 정보

USER_TAB_PRIVS_RECD : 사용자에게 부여된 오브젝트 권한 정보

USER_COL_PRIVS_MADE : 사용자 소유의 오브젝트중 칼럼에 부여된 오브젝트 권한 정보

USER_COL_PRIVS_REDC : 사용자에게 부여된 특정 칼럼에 대한 오브젝트 권한 정보
 

21. FOR문으로 부서번호를 계산한 후 이를 SELECT문의 WHERE절에 지정하여 부서 정보를 얻어오는 PL/SQL문으로 작성하시오
DECLARE
  VDEPT DEPT%ROWTYPE;

BEGIN

  DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명');
  DBMS_OUTPUT.PUT_LINE('----------------------------');

  FOR CNT IN 1..4 LOOP  
    SELECT * INTO VDEPT FROM DEPT
    WHERE DEPTNO = 10* CNT;  
    DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO||'/'||VDEPT.DNAME||'/'||VDEPT.LOC);
  END LOOP;
END;
/
 

결과

부서번호 부서명 지역명

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

10 경리부 서울

20 인사부 인천

30 영업부 용인

40 전산부 수원

 

22. while loop문으로 별을 삼각형 구도로 출력하기 -- 프로시저로 별찍기 

*

**

***

****

*****
DECLARE 

  V_CNT NUMBER := 1;
  V_STR VARCHAR2(10) := NULL;

BEGIN

  WHILE V_CNT <= 5 LOOP
 
    V_STR := V_STR || '*';
    DBMS_OUTPUT.PUT_LINE(V_STR);
    V_CNT := V_CNT + 1;

  END LOOP;  
END;
/