■ DATABASE OBJECT
11장. 기타 스키마 생성
*데이터 베이스 오브젝트(객체) 5가지
1. table
2. view
3. index
4. sequence
5. synonym
■VIEW
■view 가 무엇인가?
"쿼리의 결과"
*view 가 필요한 이유?
1. 아까 상엽이 데이터를 100건을 중복해서
출력하는 쿼리를 number100 테이블을
생성하지 말고 출력하시오!
create table number100
as
select rownum as in
from dual
connect by rownum < 101;
이렇게 하지 말라는 거지
근데 이거 설명 안끝내고
바로 예제2번 문제로 넘어가셨음.
2. 부서번호, 이름, 월급, 자기가 속한
부서번호의 평균월급을 출력하는데
자기의 월급이
자기가 속한 부서번호의 평균월급보다
더 큰 사원들만 출력하시오
select *
from
( select deptno, ename, sal,
round(avg(sal)over(partition by deptno))평균월급
from emp
)
where sal > 평균월급;
**********여기서
부서평균월급을 랭크먹이면??
select deptno, ename, sal, 부서평균월급,
dense_rank()over(partition by deptno
order by 부서평균월급 desc) 부서순위
from (select deptno, ename, sal,
round(avg(sal)over(partition by deptno))부서평균월급
from emp);
왜 ㅠㅠㅠㅠㅠ 1,2,3 으로 안나오니 ㅠㅠㅠㅠ
partition by 지우니까 나옴.
select deptno, ename, sal, 부서평균월급,
dense_rank()over(order by 부서평균월급 desc) 부서순위
from (select deptno, ename, sal,
round(avg(sal)over(partition by deptno))부서평균월급
from emp);
부서평균은 부서번호 별로 총 3개가 나온다.
2917, 2175, 1567
근데 순위를 위해서 또 파티션을 해주면
내가 함수처리해준 것이라서,
테이블에 없는 평균을 부서번호와 매칭시켜야 하는데
그런 부서번호가 없다.
그래서 없는 번호라는 가정하에
부서평균을 매칭시켜서
랭크를 낸 것이고
순서를 매길 수 없어서 다 1로 뽑힌거다
(에러 안난게 신기할 뿐)
위 결과를 너무 자주본다
따로 만들어서 자주 사용해야 하는데.
*테이블 VS 뷰(view)
1. 테이블로 제작
create table deptno_avg
as
select deptno, ename, sal,
round(avg(sal)over(partition by deptno))평균월급
from emp;
select * from deptno_avg;
평균월급보다 많이 받는 사람을 뽑으려면
select * from deptno_avg
where sal> 평균월급;
쿼리가 훨씬 심플해지는 걸 볼 수 있다.
문제398.
deptno_avg 테이블의
10번 부서번호인 사원들의 월급을
전부 0으로 변경하시오.
update emp
set sal = 0
where deptno = 10;
그리고
select * from deptno_avg
where sal > 평균월급;
emp 값을 update 해도
변동사항 없이 따로 논다.
drop table emp
하고 다시 데이터새로 넣으시오~~
*view 생성
create view deptno_avg
as
select *
from
( select deptno, ename, sal,
round(avg(sal)over(partition by deptno))부서평균
from emp);
select * from deptno_avg
where sal > 부서평균;
update emp
set sal = 0
where deptno = 10;
그리고
select * from deptno_avg;
view 는
모테이블 값 변경해도
같이 변경되는 걸 알 수 있다.
※설명: table은 데이터를 저장하지만
view 는 데이터를 저장하지 않고
view 를 쿼리하면
view 생성했을 때의
실제 쿼리문이 실행이 된다.
문제399.
이름, 나이, 전공, 순위를 출력하는
view 를 생성하시오!
(순위는 나이가 높은 순서에 대한
순위임)
view 이름: emp2_v
create view emp2_v
as
select ename, age, major,
dense_rank()over(order by age desc)순위
from emp2;
확인
select * from emp2_v;
문제400.
우리반에서 나이가
1위부터 3위까지만 ,
이름, 나이, 전공을 출력하시오
(만들어 둔 뷰를 이용하시오)
select * from emp2_v
where 순위 between 1 and 3;
select *
from
(
select ename, age, major,
dense_rank()over(order by age desc)순위
from emp2)
where 순위 between 1 and 3
문제401.
겨울왕국 대본을 입력할
테이블 winter_kingdom
라는 테이블을 아래와 같이 생성하고
겨울왕국 스크립트를 입력하시오!
create table winter_kingdom
(winter_text clob);
또는
create table winter_kingdom
(winter_text varchar2(4000));
문제402.
겨울왕국 대본을 단어별로
쪼개서 결과를 출력하시오
select win_text,
regexp_substr(lower(win_text), '[^ ]+' ) aaa
from winter_kingdom;
lower <<< win_text 를 소문자로 바꾸고
^ <<꺽쇄인데 아니다 라는 뜻
[^ ] <<< 공백이 아닌걸 잘라내라
+ <<< 여러개 단어
select win_text, regexp_substr(lower(win_text), '[^ ]+' ) aaa
from winter_kingdom;
문제403.
위의 쿼리를 view 로 만들고
view 를 쿼리하는데
겨울왕국 대본의 단어, 단어별 건수를
출력하는데
그 건수가 높은 것부터
출력하시오(점심시간 문제)
CREATE VIEW winter_v
AS
SELECT *
FROM(
select regexp_substr(lower(win_text), '[^ ]+',1,1) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,2) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,3) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,4) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,5) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,6) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,7) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,8) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,9) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,10) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,11) aaa1 from winter_kingdom
union all
:
:
select regexp_substr(lower(win_text), '[^ ]+',1,22) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,23) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,24) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,25) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,26) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,27) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,28) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,29) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,30) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,31) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,32) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,33) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,34) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,35) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,36) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,37) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,38) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,39) aaa1 from winter_kingdom
union all
select regexp_substr(lower(win_text), '[^ ]+',1,40) aaa1 from WINTER_KINGDOM
);
SELECT * FROM winter_v;
SELECT aaa1, COUNT(*) ct
FROM winter_v
WHERE aaa1 IS NOT null
GROUP BY aaa1
ORDER BY ct DESC;
문제404.
셜록홈즈 대본을 오라클 데이터 베이스에
입력하고
셜록홈즈에서 가장 많이 나오는 단어가
무엇인지 1위부터 10위까지 출력하시오.
create table SHERLOCK
(script clob);
CREATE VIEW sherlock_script
AS
select regexp_substr(lower(script), '[^ ]+',1,1) aaa1 from sherlock
union all
select regexp_substr(lower(script), '[^ ]+',1,2) aaa1 from sherlock
union all
select regexp_substr(lower(script), '[^ ]+',1,3) aaa1 from sherlock
union all
select regexp_substr(lower(script), '[^ ]+',1,4) aaa1 from sherlock
union all
select regexp_substr(lower(script), '[^ ]+',1,5) aaa1 from sherlock
union all
select regexp_substr(lower(script), '[^ ]+',1,6) aaa1 from sherlock
union all
select regexp_substr(lower(script), '[^ ]+',1,7) aaa1 from sherlock
union all
:
:
select regexp_substr(lower(script), '[^ ]+',1,39) aaa1 from sherlock
union all
select regexp_substr(lower(script), '[^ ]+',1,40) aaa1 FROM sherlock;
SELECT * FROM sherlock_script;
SELECT *
FROM (
SELECT aaa1, cnt, RANK() OVER ( ORDER BY cnt DESC ) 순위
FROM (
SELECT aaa1, COUNT(*) cnt
FROM sherlock_script
WHERE aaa1 IS NOT NULL
GROUP BY aaa1
)
)
WHERE 순위 BETWEEN 1 AND 10;
문제405.
겨울왕국 대본에서 anna 라는
단어가 몇 번 나오는가?
SELECT * FROM winter_v;
SELECT aaa1, COUNT(*) ct
FROM winter_v
WHERE aaa1 IS NOT null
and aaa1 = lower(anna)
GROUP BY aaa1
ORDER BY ct DESC;
선생님답
SELECT COUNT(*) ct
FROM winter_v
WHERE LOWER(aaa1) LIKE 'anna%';
문제406.
겨울왕국 대본에는
긍정적인 단어가 많은가
부정적인 단어가 많은가
1. 긍정사전 테이블 생성
create table positive_words
(p_text varchar2(2000));
2. 부정사전 테이블 생성
create table negative_words
(n_text varchar2(2000));
확인
select * from positive_words;
select * from negative_words;
카운트 해서 보면
긍정: 2007
부정: 4783
문제407.
부서 테이블에서 부서번호를 출력하는데
사원테이블에 있는 부서번호만 출력하시오
select deptno
from dept
where deptno in(select deptno from emp);
겨울왕국에서
긍정단어가 몇 건인지 조회하려면
CREATE view winter_positive
as
select aaa1
from winter_v
where lower(aaa1) in (
select lower(p_text)
from positive_words );
SELECT COUNT(*) FROM winter_positive;
-단순/복합 VIEW
■ view 의 종류 2가지(페이지 115)
단순view 복합view
테이블의 갯수 1개 2개이상
함수 포함 안함 포함
그룹함수 포함 안함 포함
DML 여부 가능 불가능할 수도
있다.
문제408.
사원이름, 부서위치를 출력하는
view 를 생성하시오
(뷰이름: emp303)
create view emp303
as
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno;
화면 캡처: 2018-10-30 오후 3:15
컬럼명에
e.ename, d.loc 라고 써져서
출력되지 않는다***중요****
문제409.
위의 emp303 view 를 수정하는데
king의 부서위치를
seoul 로 변경하시오
1.
update emp303
set loc= 'SEOUL'
where ename= 'KING';
2.
update emp303
set ename='aaa'
where ename='KING';
>>>>>>>>위 2개 안됨!!!!!!!!!!
alter table dept
add constraint dept_deptno_pk primary key(deptno);
제약 걸으면 됨
1번 쿼리는 된다
하지만
2번 쿼리(지역 변경) 은 안된다.
왜???????????????????????????
원래는 되야 하는데
오라클 내부상...웅.....
view 는 제약걸어야만 된다.
모체 테이블 수정하면
뷰테이블도 알아서 수정된다.
문제410.
직업, 직업별 토탈월급을 출력하는
view 를 생성하시오
(view 이름 : dept_sumsal)
create view dept_sumsal
as
select job, sum(sal)
from emp
group by job;
에러뜸. 알리아스 (별칭) 주라고 뜸
view 생성시
그룹함수에는 꼭 알리아스를
정해줘야 한다
create view dept_sumsal
as
select job, sum(sal) as "토탈월급"
from emp
group by job;
문제411.
dept_sumsal 뷰를 수정하는데
job 이 manager 의 토탈월급을 2000으로 수정하시오
update dept_sumsal
set 토탈월급 = 2000
where job = 'MANAGER';
***이건 매끈하게 수정된다.
왜냐면 내가 알리아스로 지정해줘서
컬럼명이 일치한다.
복합view 일 때만 컬럼명이 바뀌어서
나오니
이때만 주의할 것
문제412.
scott 이 가지고 있는 view 리스트를
확인하시오.
select * from user_views;
-VIEW 의 옵션
■ view 의 옵션 2가지
1. with check option :
뷰 생성시 where 절에서
기술한 조건에 위배되게끔 뷰를 수정
못하게 하는 옵션.
2. with read only :
뷰 전체를 수정 못하게 하는 옵션
문제413.
직업이 SALESMAN 인 사원들의
사원번호, 이름, 직업, 월급을
출력하는 뷰를 생성하시오
create view emp45
as
select empno, ename, job, sal
from emp
where job = 'SALESMAN'
with check option;
select * from emp45;
update emp45
set sal = 0
where ename ='ALLEN';
여기까지는 갱신 잘 됨
update emp45
set job = 'MANAGER'
where ename = 'ALLEN';
이건 안 됨
with check option :
뷰 생성시 where 절에서
기술한 조건에 위배되게끔 뷰를 수정
못하게 하는 옵션.
그래서 job 을 건드는 건 안 됩니다.
문제414.
사원번호, 이름, 월급, 직업을 출력하는
view 를 생성하는데
월급을 9000 이상으로는 수정 못하게 하는
뷰를 생성하시오
create view emp414
as
select empno, ename, sal, job
from emp
where sal < 9000
with check option;
*******<= 등호 까지 붙이면 안된데여
9000 도 포함시키지 말아야 한다앙
update emp414
set sal= 9500
where ename = 'ALLEN';
안됩니다 with check option 에 위배되지요
문제415.
직업이 SALESMAN 인 사원들의
사원번호, 이름, 월급, 직업을 출력하는 뷰를 생성하는데
뷰 전체 데이터를 수정, 삭제, 입력을 못하게 하시오
create view emp2222
as
select empno, ename, sal, job
from emp
where job = 'SALESMAN'
with read only;
with read only :
뷰 전체를 수정 못하게 하는 옵션
문제416.
지금 만든 뷰를 삭제하시오
drop view emp2222;
문제417.
scott 이 가지고 있는 모든 뷰를 다 삭제하시오
select * from user_views;
select 'drop view '|| view_name || ';'
from user_views;
drop view DEPTNO_AVG;
drop view DEPT_SUMSAL;
drop view EMP2_V;
drop view EMP303;
drop view EMP414;
drop view EMP45;
drop view SHERLOCK_SCRIPT;
drop view WINTER_KINGDOM_V;
drop view WINTER_POSITIVE;
drop view WINTER_V;
■ database object 5가지
1. table
2. view
3. sequence
4. index
5. sysnonym
■
" 번호를 생성하는 db object"
예:
seq1 시퀀스를 생성하시오
create sequence seq1;
select seq1.nextval
from dual;
계속 실행할 때 마다 숫자값이 올라간다.
1씩 올라가고 중간에 건너뛰는것 없이
순서대로 출력한다
dept 테이블에 시퀀싱 하시오
insert into dept(deptno, loc, dname)
values(seq1.nextval, 'seoul', 'sales');
문제418.
아래의 테이블을 생성하고
아래의 테이블에 번호를
1번부터 1000번까지 입력하시오!
create table emp418
(empno number(10) );
사원번호 컬럼있는 테이블 emp418 생성
create sequence seq2;
시퀀스를 하나 만든다
***이건 프로그래밍 배워야 쓸 수 있는 쿼리문.
선생님이 그냥 보여주심
begin
for i in 1..1000 loop
insert into emp418
values(seq2.nextval);
end loop;
end;
/
select * from emp418;
순서:
테이블 생성-시퀀스 생성-insert에 컬럼 중 시컨스 줄 거 함께 넣기-천번치기......
문제419.
scott이 가지고 있는 시퀀스를 확인하시오
select * from user_sequences;
문제420.
scott 이 가지고 있는
시퀀스를 삭제 하시오.
(알터 아님요 테이블 오브젝트 잖아여)
drop sequence seq1;
drop sequence seq2;
■ 데이터베이스의 객체 5개
1. table
2. view
3. index
4. sequence
5. synonym
■SYNONYM
" 하나의 객체에 대해서 기존 이름외에
다른 이름을 부여하는 것"
예:
핸드폰 기계는 하나인데
번호가 2개 일 수 있다.
create synonym employee
for emp;
select * from employee;
같은내용 다른이름 ㅎㅎ
근데 드랍시도하면
drop table employee;
안된다~
보안상
테이블DDL 권한 안주려고
synonym 해서 테이블 준다.
문제446.
employee 테이블을 삭제해보시오
select * from user_synonyms;
drop synonym employee;
*********질문********(index/is not null 에 대한)
에 대한*********답
create index emp_hire_func
on emp( to_char(hiredate) );
select ename, hiredate
from emp
where to_char(hiredate) is not null;
select ename
from emp
where ename is not null ;
그냥 is not null 연산자 쓰지 마시고요(선생님)
아님 컬럼 2개로 인덱스 잡으세요(용원쓰)
create index 인덱스이름
on 테이블(컬럼1, 컬럼2) ;
'sql' 카테고리의 다른 글
| 13-2. DATABASE OBJECT(임시테이블, 외부테이블, flashback) (0) | 2019.03.31 |
|---|---|
| 13-1. DATABASE OBJECT(INDEX) (0) | 2019.03.31 |
| 12. 제약 (0) | 2019.03.31 |
| 11-4. 데이터 조작 언어(계층형 질의문) (0) | 2019.03.31 |
| 11-3. 데이터 조작 언어(DCL문) (0) | 2019.03.31 |
