[새싹] 현대IT&E 231101 기록 - Oracle 11~12

최정윤·2023년 11월 1일
oraclesql
0

새싹

목록 보기
12/67

11. 서브 쿼리

11.3 서브 쿼리를 이용하여 테이블을 생성하고, 데이터를 조작하기

11.3.4 서브 쿼리를 이용한 데이터 변경

-- 테이블 내용 변경하기 
update dept01
set loc = (select loc from dept01
where deptno=20)
where deptno=40;

서브 쿼리를 이용하여 데이터 삭제하기

-- 테이블의 내용을 전부 삭제하기 
delete emp02;

-- 테이블에 데이터 추가하기
insert into emp02
select * from emp;

drop table emp02 purge;

create table emp02
as
select * from emp;

-- 데이터 삭제하
delete emp02
where deptno = (select dptno
from dept
where dname='영업부');

Quiz

1️⃣ 다음 설명 중 틀린 것은? (②)

① = ANY, =SOME은 IN과 의미가 같다.
② 메인 쿼리에서 서브 쿼리의 결과 값으로 하나만 리턴받으려면, 서브 쿼리에서 GROUP BY를 사용해야 한다.
③ ALL은 서브 쿼리에서 돌려주는 모든 값이 비교 조건을 만족할 때, 참이 된다.
④ 메인 쿼리의 SELECT 문에서 select_list에 해당하는 값으로 서브 쿼리를 사용할 수 있는데, 이때 반드시 단일 값만을 리턴해야 한다.

서브 쿼리의 결과 값을 메인 쿼리에서 하나만 리턴받으려면 GROUP BY를 사용할 필요가 없습니다. 일반적으로 서브 쿼리에서 단일 값을 리턴하는지 확인하면 됩니다.

2️⃣ 다음 SQL 구문 중 틀린 것은? (③)

① select ename, sal
② from emp
where sal > (select ③ max(sal), min(sal)
			④ from emp);

서브쿼리(subquery)가 여러 개의 값을 반환하는데, 비교 연산자는 하나의 값을 비교해야 한다. 올바른 쿼리를 작성하려면 서브쿼리에서 최대 및 최소 값을 가져와 비교해야 한다. 

SELECT ename, sal
FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp) OR sal > (SELECT MIN(sal) FROM emp);

3️⃣ 급여가 500이 넘는 사원과 같은 부서에 근무하는 직원을 조회하는 쿼리문을 완성하기 위해서 ____에 들어갈 수 없는 것을 고르시오. (②)

select ename, sal, deptno
from emp
where deptno ____ (select distinct deptno  
from emp
where sal > 500);

① IN
② NOT EXISTS
③ =
④ =SOME

4️⃣ 다음 서브 쿼리가 위치 할 수 없는 곳은? (④)

① table_list 문(FROM)
② select_list 문(SELECT)
③ WHERE, HAVING 문
④ GROUP BY 문

5️⃣ 다음중 틀린 곳은? (①)

update ① table emp
② set sal = sal + 100
where deptno = (select ③ deptno
from dept
④ where dname = '경리부');

6️⃣ 다음 서브 쿼리를 사용하는 문장 중 문법에 맞지 않게 작성된 것은? (④)

① ```sql
select * from emp
where deptno = (select deptno
from dept
where dname = '경리부');

② ```sql
delete emp
where ename = (select ename from emp
where empno = 1001);

③ ```sql
insert into dept01
select * from dept01;

④ ```sql
select * into emp2 from emp;
INSERT INTO emp2
SELECT * FROM emp;

7️⃣ 서브 쿼리를 사용하여 테이블 emp06에 emp 테이블의 모든 데이터를 추가합니다.

create table emp06
as
select * from emp;

select * from emp06;

8️⃣ emp06 테이블에 저장된 사원 정보 중 과장들의 최소 급여보다 많은 급여를 받는 사원들의 이름과 급여와 직급을 출력하되, 과장은 출력하지 않는 SQL 문을 완성하시오.

SELECT ename, sal, job
FROM emp06
WHERE job != '과장' AND sal > (SELECT MIN(sal) FROM emp06 WHERE job = '과장');

9️⃣ emp06 테이블에 저장된 사원 정보 중 인천에 위치한 부서에 소속된 사원들의 급여를 100 인상하는 SQL 문을 완성하시오.

UPDATE emp06
SET sal = sal + 100
WHERE deptno IN (SELECT deptno FROM dept WHERE loc = '인천');


🔟 emp06 테이블에서 경리부에 소속된 사원들만 삭제하는 SQL문을 완성하시오.

delete from emp06
WHERE deptno IN (SELECT deptno FROM dept WHERE loc = '인천');

select * from emp06;
select * from dept;

1️⃣1️⃣ '이문세'와 같은 부서에서 근무하는 사원의 이름과 부서 번호를 출력하는 SQL문을 완성하시오.

SELECT ename, deptno
FROM emp06
WHERE deptno IN (SELECT deptno FROM emp06 WHERE ename = '이문세');

1️⃣2️⃣ '이문세'와 동일한 직급을 가진 사원을 출력하는 SQL문을 완성하시오.

SELECT *
FROM emp06
WHERE job = (SELECT job FROM emp06 WHERE ename = '이문세');

1️⃣3️⃣ '이문세'의 급여와 동일하거나 더 많이 받는 사원명과 급여를 출력하는 SQL 문을 완성하시오.

SELECT ename, sal
FROM emp06
WHERE sal >= (SELECT sal FROM emp06 WHERE ename = '이문세');

1️⃣4️⃣ '인천'에서 근무하는 사원의 이름, 부서 번호를 출력하는 SQL 문을 완성하시오.

SELECT ename, deptno
FROM emp06
WHERE deptno in (SELECT deptno FROM dept WHERE loc = '인천');

1️⃣5️⃣ 직속상관이 감우성인 사원의 이름과 급여를 출력하는 SQL 문을 완성하시오.

SELECT ename, sal, mgr
FROM emp06
WHERE mgr in (SELECT empno FROM emp06 WHERE ename = '감우성');

1️⃣6️⃣ 부서별로 가장 급여를 많이 받는 사원의 정보(사원 번호, 사원 이름, 급여, 부서번호)를 출력하는 SQL 문을 완성하시오.

SELECT  empno, ename, sal, deptno
FROM    emp e
WHERE   sal = ( SELECT  max(sal)
                FROM    emp e2
                WHERE   e.deptno = e2.deptno
                GROUP   BY deptno );

1️⃣7️⃣ 직급이 과장인 사원이 속한 부서의 부서 번호와 부서명과 지역을 출력하는 SQL문을 완성하시오.

select deptno, dname, loc
from dept
where deptno in
(select distinct deptno from emp
where job = '과장');

1️⃣8️⃣ 과장보다 급여를 많이 받는 사원들의 이름과 급여와 직급을 출력하되, 과장은 출력하지 않는 SQL 문을 완성하시오.

SELECT ename, sal, job
FROM emp06 e
WHERE EXISTS (
    SELECT 1
    FROM emp06
    WHERE job = '과장' AND sal < e.sal
);

12. 가상 테이블 뷰

-- 필요한 쿼리만 작성해서 저장한 가상 테이블 뷰
create view testview
as
select ename, sal from emp;
select * from testview;

12.1 뷰의 개념과 뷰 생성하기

  • 뷰는 물리적인 테이블을 근거로 하는 논리적인 가상 테이블이라고 정의할 수 있다.
  • 뷰를 가상 테이블이라고 하는 이뉴는 실질적으로 데이터를 저장하고 있지 않더라도 사용자는 마치 테이블을 사용하는 것과 동일하게 뷰를 사용할 수 있기 때문이다.

뷰의 기본 테이블 생성하기

-- emp를 원본 테이블로 하여 emp_copy 테이블 생성하기
create table emp_copy
as
select * from emp;

select * from emp_copy;

12.1.3 뷰의 필요성

-- 30번 부서에 소속된 사원 정보 출력하기
select empno, ename, deptno
from emp_copy
where deptno=30;

-- 30번 부서에 소속된 사원 정보 출력하는 뷰 정의하기
create view emp_view30
as
select empno, ename, deptno
from emp_copy
where deptno=30;

-- 30번 부서에 소속된 사원 정보를 출력하는 뷰 사용하기
select * from emp_view30;


12.2 뷰의 내부구조와 USER_VIEWS 데이터 딕셔너리

-- user_views 테이블의 구조 살피기
desc USER_VIEWS;

뷰에 insert 문으로 데이터 추가하기

-- 뷰에 insert 문으로 데이터 추가하기
insert into emp_view30
values(1111, 'aaaa', 30);

-- 30번 부서에 소속된 사원 정보를 출력하는 뷰 사용하기
select *
from emp_view30;

-- 기본 테이블을 살피기
select * from emp_copy;

12.2.1 뷰를 사용하는 이유

  • 복잡하고 긴 쿼리문을 뷰를 정의하면 접근을 단순화시킬 수 있다.
-- 사원 테이블과 부서 테이블을 조인한 뷰 생성하기 
create view emp_view_dept as
select e.empno, e.ename, e.sal, e.deptno, d.dname, d.loc
from emp_copy e, dept d
where e.deptno = d.deptno;

-- 사원 테이블과 부서 테이블을 조인한 뷰 사용하기
select * from emp_view_dept;
  • 보안에 유리하다.

12.3 뷰 제거하기

-- 뷰 삭제하기 
drop view emp_view_dept;

12.4 뷰의 다양한 옵션

12.4.1 뷰를 수정하기 위한 OR REPLACE 옵션

-- create view로 뷰를 수정하는 오류를 범한 예
create view emp_view30
as
select empno, ename, sal, comm, deptno
from emp_copy
where deptno=30;

-- emp_view30 뷰의 구조 살피기
desc emp_view30;

-- emp_view30 뷰 변경하기
create OR REPLACE view emp_view30
as
select empno, ename, sal, comm, deptno
from emp_copy
where depno=30;

-- emp_view30 뷰의 구조 살피기
desc emp_view30

12.4.2 기본 테이블 없이 뷰를 생성하기 위한 FORCE 옵션

-- 테이블이 존재하지 않음을 확인하기
desc employees;

-- 30번 부서에 소속된 사원을 조회하는 뷰 정의하기
create or replace view employees_view
as
select empno, ename, deptno
from employees
where deptno=30;

-- 기본 테이블 없이 뷰 정의하기
create or replace FORCE view employees_view
as
select empno, ename, deptno
from employees
where depno=30;

-- 뷰 내용 살피기
select view_name, text
from user_views;

-- 기본 테이블이 있는 경우에만 뷰가 생성되도록 하기
create or replace NOFORCE view existtable_view
as
select empno, ename, deptno
from employees
shere deptno=30;

-- 뷰 내용 살피기
select view_name, tet
from user_views;

-- force | noforce 옵션 없이 뷰 정의하기
create or replace view existtable_view
as
select empno, ename, deptno
from employees
where deptno=30;

12.4.3 조건 컬럼 값을 변경하지 못하게 하는 WITH CHECK OPTION

-- 30번 부서에 소속된 사원을 조회하는 뷰 정의하기
create or replace view emp_view30
as
select empno, ename, sal, comm, deptno
from emp_copy
where deptno=30;

-- 30번 부서에 소속된 사원 정보를 출력하는 뷰 사용하기
select *
from emp_view30;

-- 급여가 1200 이상인 사원을 20번 부서로 변경하기
update emp_view30 set deptno=20
where sal>=1200;

-- 30번 부서에 소속된 사원 정보를 출력하는 뷰 사용하기
select *
from emp_view30;

-- 조건 컬럼 값을 변경하지 못하는 뷰 정의하기
create or replace view VIEW_CHK30
as
select empno, ename, sal, comm, deptno
from emp_copy
where deptno=30 WITH CHECK OPTION;

select * from view_chk30;
update view_chk30 set deptno=20
where sal >= 300;

WITH CHECK OPTION 옵션으로 뷰 생성하기

-- emp을 원본 테이블로 하여 emp_copy 테이블 생성하기
create table mp_copy2
as
select * from emp;

-- 조건 컬럼 값을 변경하지 못하는 뷰 정의하기
create or replace view VIEW_CHK30
as
select empno, ename, sal, comm, deptno
from emp_copy2
where deptno=30 WITH CHECK OPTION;

-- 급여가 1200 이상인 사원은 20번 부서로 변경하기
update VIEW_CHK30 set deptno=20
where sal >=1200;

-- 30번 부서에 소속된 사원 정보를 출력하는 뷰 사용하기
select *
from VIEW_CHK30;

12.4.4 뷰를 통해 기본 테이블의 변경을 막는 WITH READ ONLY 옵션

WITH READ ONLY 옵션은 뷰를 통해서는 기본 테이블의 어떤 컬럼에 대해서도 내용을 절대 변경할 수 없도록 하는 것이다.

WITH CHECK OPTION과 WITH READ ONLY 비교하기

-- 커미션을 모두 1000으로 변경하기
update view_chk30 set comm=1000;
select *
from VIEW

12.5 인라인 뷰로 TOP-N

ROWNUM 컬럼 성격 파악하기

-- ROWNUM 컬럼 값 출력하기
select rownum, empno, ename, hiredate
from emp;

-- 입사일을 기준으로 오름차순 정렬하기
select empno, ename, hiredate
from emp
order by hiredate;

-- 입사일을 기준으로 오름차순 정렬한 후 rownum 컬럼 출력하기
select rownum, empno, ename, hiredate
from emp
order by hiredate;

뷰와 rownum 컬럼으로 top-n 구하기

-- 입사일을 기준으로 오름차순 정렬한 뷰 정의하기
create or replace view view_hire
as
select empno, ename, hiredate
from emp
order by hiredate;

-- 입사일을 기준으로 오름차순 정렬한 뷰로 rownum 컬럼 출력하기
select rownum, empno, ename, hiredate
from view_hire;

-- 입사일이 빠른 사람 5명 가져오기
select rownum, empno, ename, hiredate
from view_hire
where rownum<=5;

12.5.1 인라인 뷰로 TOP-N 구하기

-- 인라인 뷰로 입사일이 빠른 사람 5명만 출력하기
select rownum, empno, ename, hiredate
from (select empno, ename, hiredate
from emp
order by hiredate)
where rownum<=5;

-- 인라인 뷰로 원하는 범위 사람 출력하기
select B.*
from (select rownum as rnum, A.*
from (select empno, ename, hiredate
from emp
order by hiredate) A) B
where rnum between 11 and 20;
create table board(
no number(5),
title varchar2(34) not null,
re number(3) default 0,
regdate date default sysdate);

create sequence seq_board
start with 1 increment by 1 nocache;

begin
    for i in 1..10002 loop
    insert into board
    values(seq_board.nextval,
 'With A Little Help From My Friends',
      trunc(dbms_random.value(0,100)),
      sysdate-((10002-i)/24/6));
 end loop;
 end;
  /
  
set pages 50000;
col regdate for a20;
col title for a34;
col re for 99;
col no for 99999;

select no, title, re,
to_char(regdate,'YYYY-MM-DD hh24:mi:ss') as regdate
from board
where regdate > sysdate - 1/24
order by no desc;
 
 select count(*) from board;
 
 define thisPage=1;
 define pageSize=5;
 
select rownum, no, title, re,
to_char(regdate,'YYYY-MM-DD') as regdate
from board
where rownum between (&thisPage-1)*&pageSize+1 and &thisPage*&pageSize
order by no desc;

define thisPage = 2/

select A.*
from   (select no, title, re,
               to_char(regdate,'YYYY-MM-DD') as regdate
       from    board
order by no desc) A
where rownum between (&thisPage-1)*&pageSize+1 and &thisPage*&pageSize;

select B.*
from   (select rownum as rnum, A.*
from (select no, title, re, to_char(regdate,'YYYY-MM-DD') as regdate
from board
order by no desc) A) B
where rnum between (&thisPage-1)*&pageSize+1
and    &thisPage*&pageSize;

define thisPage=1;

select B.*
from (select rownum as rnum, A.*
from (select no, title, re,
to_char(regdate, 'YYYY-MM-DD') as regdate
from board
order by no desc) A) B
where rnum between (&thisPage-1)*&pageSize+1
and &thisPage*&pageSize;

define thisPage=2/
create table board (
no        number,      
title     varchar2(34) not null,
re        number(3) default 0,
regdate   date  default sysdate);

alter table board add constraint pk_board
primary key(no);

create sequence seq_board
start with 1 increment by 1 nocache;

begin
  for i in 1..2000000 loop
    insert into board
    values(seq_board.nextval,
       'With A Little Help From My Friends',
       trunc(dbms_random.value(0,100)),
       sysdate-((2000000-i)/24/6));
    end loop;
  end;
/

commit;

set autot on
set timing on
col content noprint

select B.*
from   (select rownum as rnum, A.*
       from   (select no, title, re,
                       case to_char(regdate,'YYYY-MM-DD')
                       when to_char(sysdate,'YYYY-MM-DD')
                       then to_char(regdate,'HH24:MI:SS')
                       else to_char(regdate,'YYYY-MM-DD')
                       end as regdate
               from   board
               order  by no desc) A) B
where   rnum between (&thisPage-1)*&pageSize+1
and     &thisPage*&pageSize;

SELECT no, title, re,
       case to_char(regdate,'YYYY-MM-DD')
       when to_char(sysdate,'YYYY-MM-DD')
       then to_char(regdate,'HH24:MI:SS')
       else to_char(regdate,'YYYY-MM-DD')
       end as regdate
from   board
order  by no desc
OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;
profile
최정윤
개발 기록장
이전 포스트

[새싹] 현대IT&E 231031 기록 - Oracle 7~11

다음 포스트

[새싹] 현대IT&E 231102 기록 - Oracle 13~14

0개의 댓글