view

뷰(view)
. 정보보호 및 관리를 위해 필요한 정보를 추출하여 가상테이블 형태(view)로 제공
        . 실제 테이블을 기준으로 생성된다
        . 작업결과를 임시로 보관 
        . 복잡한 질의를 간소화
        . 뷰를 기초로 또 다른 뷰를 생성할 수도 있다
        . 뷰도 하나의 객체로 관리된다
        . 목적
- 데이터베이스 보호
            - 데이터의 독립성을 높이기 위함
            - 다양한 구조의 테이블 효과 
            - 작업의 단순화 등
. 생성
create view 뷰이름 as
select ... from ~ where ~
                
        . 뷰 작업시 주의사항
- 원본에 not null 존재한다면, view 에서도 필히 입력해야 한다.
            - 그룹함수를 사용한 뷰에서 데이터 입력은 제한된다.
            - 뷰에서 가공한 데이터는 원본에 입력될 수 없다.
            - 뷰끼리 조인한 뷰에서는 데이터 입력을 할 수 없다.

 

# 인원제한이 100명 이상인 강의의 강의번호, 강의이름, 교수번호, 인원제한만 출력할 수 있는
# '대강의' 뷰를 생성하시오
select * from 강의;
create view 대강의 as
	select 강의번호, 강의이름, 교수번호, 인원제한 from 강의 where 인원제한 >= 100;
select * from 대강의;

# 소속학과가 '컴퓨터과학과'인 교수들의 모든 정보를 출력해주는 '컴퓨터과학과' 뷰를 생성하시오
create view 컴퓨터과학과 as
	select * from 교수 where 소속학과 = '컴퓨터과학과';
select * from 컴퓨터과학과;

# 학생중에 컴퓨터과학과 학생들만 출력할 수 있는 '컴과학생' 뷰를 생성하시오
create view 컴과학생 as
	select * from 학생 where 소속학과 = '컴퓨터과학과';
select * from 컴퓨터과학과;

# 컴과학생뷰를 통해 성별이 남자인 학생의 정보를 조회하시오
select * from 컴과학생 where 성별='남자';
select * from 컴과학생;
# 컴과학생뷰를 통해 나이를 오름차순/내림차순으로 정렬하여 조회하시오
select * from 컴과학생 order by 나이 asc ;
select * from 컴과학생 order by 나이 desc;
# 컴과학생뷰를 통해 201426007, 컴퓨터과학과, 장용준, 2학년, 남학생, 27살, 010-5487-1354, 서울시 성북구 학생정보를 입력하시오
insert into 컴과학생(학생번호,소속학과,학생이름,학년,성별,나이,전화번호,주소) 
	values (201426017, '컴퓨터과학과', '장용준', 2, '남', 27, '010-5487-1354', '서울시 성북구');
select * from 컴과학생;

---

뷰 수정

alter view 뷰이름(컬럼명, 컬럼명, ..) as
  select 컬럼명, 컬럼명, ..
    from 테이블명
            [where 조건]

# 대강의 뷰에서 기존의 강의번호, 강의이름, 교수번호, 인원제한 컬럼에 강의실과 강의시간을 추가하시오
desc 대강의;
alter view  대강의(강의번호, 강의이름,교수번호,인원제한, 강의실, 강의시간) as
	select 강의번호, 강의시간, 교수번호, 인원제한, 강의실, 강의시간 
		from 강의
        where 인원제한 >= 100;
select * from 대강의;

# 1. 강의를 담당하는 교수정보를 확인할 수 있는
# 강의번호, 강의이름, 강의시간, 이수구분, 인원제한, 교수번호, 교수이름, 직위, 전화번호, 연구실위치를 포함하는
# '상세강의정보' 뷰를 생성하시오
create view 상세강의정보 as
select  강의.강의번호, 강의.강의이름, 강의.강의시간, 강의.이수구분, 강의.인원제한, 강의.교수번호, 교수.교수이름, 교수.직위, 교수.전화번호, 교수.연구실위치
from 강의, 교수
where 강의.교수번호=교수.교수번호;
select * from 상세강의정보;

# 2. 뷰를 통해 데이터를 입력하시오
# 강의번호 C020, 강의이름 데이터베이스 설계구현, 강의실 구관 202호, 강의시간 금요일 6~9교시, 이수구분 전공필수, 인원제한 80명
# 교수번호 C015, 교수이름 정호용, 직위 조교수, 전화번호 010-5513-6697, 연구실위치 경북대학 의 정보를 입력하고 확인하시오
insert into 상세강의정보(강의번호, 강의이름, 강의시간, 이수구분, 인원제한, 교수번호, 교수이름, 직위, 전화번호, 연구실위치)
	values ('C020', '데이터베이스 설계구현', '금요일 6~9교시', '전공필수', 80, 'C015', '정보성', '조교수', '010-5513-6697', '경북대학');

# 3. '대강의'뷰를 이용하여 강의번호 C001, 강의이름 컴퓨터의 이해, 교수번호 C029, 인원제한 300, 강의실 강당,
#		강의시간 월요일 4~6교시 인 강의정보를 입력하시오
select * from 대강의;
insert into 대강의(강의번호, 강의이름, 교수번호, 인원제한, 강의실, 강의시간)
	values('C001', '컴퓨터의 이해', 'C029', 300, '강당', '월요일 4~6교시');

# 4. 소속학과별 학생의 소속학과, 평균나이를 확인할 수 있는 '학과평균나이' 뷰를 생성하시오
#	이때, '학과평균나이' 뷰에 소속학과의 평균나이를 '평균나이'라는 이름의 컬럼으로 포함시키시오
create view 학과평균나이 as 
select 소속학과, avg(나이) 평균나이 from 학생 group by 소속학과;
select * from 학과평균나이;  

# 5. '학과평균나이'뷰에 소속학과 가정학과, 평균나이 27.189를 입력하시오
insert into 학과평균나이 (소속학과, 평균나이) values (가정학과, 27.189);

# 6. '컴과학생' 뷰를 사용하여 학생번호 201426007 학생의 소속학과를 통계학과로 수정하시오.
select * from 컴과학생;
update 컴과학생 set 소속학과 = '통계학과' where 학생번호='201426007';

---

# 실습
create table 급여 (
사번 varchar(10) primary key ,
직급 varchar(10) not null, 
본봉 varchar(10), 
수당 char(10), 
공제액 varchar(10), 
상여금 varchar(10));
insert into 급여(사번,직급,본봉,수당,공제액,상여금)
values ('a001','사원','700,000','-','220,000','500,000');
insert into 급여(사번,직급,본봉,수당,공제액,상여금)
values ('b001','부장','900,000','200,000','300,000','600,000');
insert into 급여(사번,직급,본봉,수당,공제액,상여금)
values ('a002','사원','600,000','-','150,000','450,000');
insert into 급여(사번,직급,본봉,수당,공제액,상여금)
values ('b002','과장','800,000','100,000','230,000','450,000');
insert into 급여(사번,직급,본봉,수당,공제액,상여금)
values ('a003','사원','600,000','-','200,000','600,000');
insert into 급여(사번,직급,본봉,수당,공제액,상여금)
values ('b003','과장','800,000','100,000','200,000','500,000');
insert into 급여(사번,직급,본봉,수당,공제액,상여금)
values ('a004','부장','900,000','200,000','150,000','400,000');

select * from 급여;

create table 신상(
사번 varchar(10) primary key,
성명 varchar(20) not null,
부양가족수 int,
생년월일 varchar(13),
성별 char(4),
소속부서 varchar (20));
insert into 신상(사번,성명,부양가족수,생년월일,성별,소속부서)
values ('a001','이창영',1,'1960.1.7','남','홍보부');
insert into 신상(사번,성명,부양가족수,생년월일,성별,소속부서)
values ('b001','손재영',0,'1970.4.7','여','영업부');
insert into 신상(사번,성명,부양가족수,생년월일,성별,소속부서)
values ('a002','박우식',2,'1969.12.4','남','홍보부');
insert into 신상(사번,성명,부양가족수,생년월일,성별,소속부서)
values ('b002','조창주',3,'1973.5.7','남','영업부');
insert into 신상(사번,성명,부양가족수,생년월일,성별,소속부서)
values ('a003','김인수',2,'1965.9.12','남','홍보부');
insert into 신상(사번,성명,부양가족수,생년월일,성별,소속부서)
values ('b003','최연주',1,'1969.12.5','여','자재부');
insert into 신상(사번,성명,부양가족수,생년월일,성별,소속부서)
values ('a004','서진숙',2,'1964.12.8','여','구매부');

select * from 신상;

# 연습문제
#1. 모든 사원의 사번,성명,성별,소속부서를 검색하시오
select 사번, 성명, 성별, 소속부서 from 신상;

#2. 성별이 '남'이고, 소속부서가 '홍보부'인 사원의 사번,성명,성별,소속부서를 사번이 높은 순서대로 출력되도록 검색하시오
select 사번,성명,성별,소속부서 from 신상 where 성별 ='남' order by 사번 asc ;

#3. 급여테이블에서 직원들의 평균 본봉, 평균 수당, 평균 공제액을 검색하시오
select avg(본봉), avg(수당), avg(공제액) from 급여;

#4. 신상테이블을 부서별로 그룹화하여 인원수를 검색하시오
select 소속부서, count(*) as 인원수 from 신상 group by 소속부서;

#5. 사번이 'a003'에 해당하는 사원의 사번, 성명, 본봉, 성별, 소속부서를 검색하시오
select a.사번, a.성명, b.본봉, a.성별, a.소속부서 from 신상 as a inner join 급여 as b on a.사번 = 'a003';

#7. 급여테이블과 신상테이블을 이용하여 홍보부, 자재부 소속사원들의 사번, 직급, 본봉을 검색할 수 있는 뷰를 생성하시오
create view 급여신상 as
select 신상.사번, 급여.직급, 급여.본봉 from 신상 left join 급여 on 신상.사번 = 급여.사번 where 소속부서 = '자재부' or 소속부서 = '홍보부';
select * from 급여신상;

#8. 직급이 '사원'에 해당하는 사원들의 사번, 성명, 직급, 수당, 부양가족수를 구할 수 있는 뷰를 생성하시오
create view 사원정보 as
select a.사번, a.성명, b.직급, b.수당, a.부양가족수 from 신상 as a left join 급여 as b
on a.사번 = b.사번;
select * from 사원정보;