MYSQL 뷰&VIEW 예제&문제 15

MySQL 뷰 생성

CREATE VIEW 뷰이름

AS SELECT문

-- student 테이블로부터 모든 학생의 학번과 학년, 반을 가지고 있는 뷰 테이블을 생성하라

create view student_1

as select * from student;

select * from student_1;

-- 등록한 학생의 학번과 등록년도에 대한 뷰테이블을 생성하라

-- 

create view student_2

as select s.stu_no, a.att_year

from student s, attend a

where s.stu_no = a.stu_no;

select * from student_2;

create view v_fee

as select stu_no, fee_year

from fee

where fee_year is not null;

select * from v_fee;

-- 재학생의 학번과 이름, 성별, 입학년도, 생년, 월, 일, 나이에 대한 뷰 테이블을 생성하라

create view student_6

as select stu_no, stu_name, id_num,substring(id_num,8,1) "sex", substring(id_num,1,2),substring(id_num,3,2),substring(id_num,5,2), 

year(now()) - birth_year +1 "age"

from student;

select * from student_6;

-- 재학생 중 21세 이상인 여학생의 학번, 이름, 성별, 출생년도, 나이를 출력하라

-- 

select stu_no, stu_name 

from student_6

where age > 20 and sex = 2;

-- 재학생중 21세에 해당하는 학생의 성년식 행사를 위한 명단을 출하라. 단, 출력 형식은 학과, 

-- 학년, 학번, 이름, 생년, 월, 일, 나이를 출력하라 

-- 

-- 뷰 테이블에서 재학생 중 20세 이상이고, 2000~2004년에 입학한 학생을 구하라 

-- 

-- 뷰 테이블을 삭제하라. 

-- 

drop viw student_8;

-- 전라남도 여수지역 (우편번호 550)에 살고 있는 학생의 학번, 이름, 현주소의 우편번호 3자리를

-- 가지는 뷰 테이블을 생성하라 

--

create view v_address(hak,irum, hpost) as

    select stu_no, stu_name, substring(post_no,1,3)

    from student

    where substring(post_no,1,3) = '550'

    select * from v_address;

    

 

-- 등록금 총액별로 학생 인원 수 현황을 생성하는 뷰테이블을 생성하라 

-- 

create view v_feetotal1(fee_total,row_total) as

select fee_pay,count(*)

from fee

GROUP BY fee_pay

select * from v_feetotal1;

-- 등록금 총액별로 학생 인원 수 현황을 생성한 뷰 테이블 의 내용을 출력하라 

-- 

select fee_total, row_total

from v_feetotal1;

-- 학적테이블에서 1985년 이전에 출생한 모든 학생에 대한 뷰 테이블을 생성하라

-- 

create view v_old as

select *

from student

where birth_year < 1985;

-- 

-- 위의 테이블에서 학번이 20001001인 학생의 출생년도를 1986으로 변경하라 

-- 

update v_old

set birth_year = '1986'

where stu_no = '20001001';

select stu_no, stu_name, birth_year from v_old;

select stu_no, stu_name, birth_year from student;

update student

set birth_year = '1981'

where stu_no = '20001001';

-- 

-- 학적테이블에서 1985년 이전에 출생한 모든 학생에 대한 뷰테이블을 생성하라 

-- 

create view v_old12 as

select *

from student

where birth_year < 1985

with CHECK OPTION;

-- 위 테이블에서 학번이 20001001인 학생의 출생 년도를 1986으로 변경하라 

update v_old12

    set birth_year = '1986'

    where stu_no ='20001001'; 

-- 등록 테이블로 부터 학번과 학생별 등록금 납입 총액의 합계로 구성하는 뷰 테이블을 생성하라

-- 

CREATE view totals (stu_no, fee_total)as

select stu_no, sum(fee_pay)

from fee

GROUP BY stu_no;

-- 뷰 테이블로 부터 학생별 등록금 납입총액의 최대값을 구하라  

-- 

select max(fee_total)

from totals

-- 뷰 테이블과 학적테이블을 이용하여  학번, 이름, 납입 총액을 출력하라.

-- 

select s.stu_no, s.stu_name, t.fee_total

from student s, totals t

where s.stu_no = t.stu_no

-- 수강 신청한 학생 중에서 뷰 테이블에 존재하는 학생의 학번, 납입총액을 출력하라

-- 

select stu_no, fee_total

from totals

where stu_no in

(select stu_no from attend);

-- 뷰 테이블에 존재하는 학생의 학번, 납입총액을 출력하라. 단, 출력 순서는 납입 총액 내림차순으로 

-- 정렬한다. 

-- 

select stu_no, fee_total

from totals

order by fee_total desc;

-- 뷰 테이블에 존재하는 학생의 학번과 동아리 테이블에 존재하는 학생의 학번을 학번 오름차순으로 정렬하여 출력하라.

-- 

select stu_no from totals

union

select stu_no from circle

order by stu_no;

-- 수강신청 테이블에서 학생별, 수강년도별, 학기별로 그룹을 만들고 이 그룹의 수강신청 학점이 

-- 5학점 이상인 학생의 학번, 연도, 학기, 수강학점계를 뷰 테이블 을 생성하라 

-- 

select stu_no, att_year, att_term

from attend

group by stu_no, att_year, att_term

having sum(att_point) > 5;

-- 적어도 한번 이상 등록한 학생들을 학적테이블과 동일한 가상 테이블 aa 를 생성하라 

-- 

create view aa as

select * from student

where stu_no 

IN

(select stu_no

from fee);

-- 적어도 한번은 등록하고 남자인 학생의 학번과 이름을 출력하라 

-- 

select stu_no, stu_name

from aa

where substring(id_num,8,1)=1;

-- 응용분야

-- 

-- 

-- 학급 3반에서 등록한 학생의 학번과 반을 출력하라

-- 

select stu_no, class

from student

where stu_no

in

(select stu_no from fee)

and class = 3;

-- 학급별로 등록한 학생의 학급 통계를 출력하라 

-- 

select class, count(*)

from  student

where stu_no

in

(select stu_no from fee)

GROUP BY class;

-- 등록한 학생의 학번과 반으로 구성되는 테이블 aaaa를 생성하라

-- 

create view aaaa as

select * from student

where stu_no 

IN

(select stu_no

from fee);

select * from aaaa where class=3;

select count(*) from aaaa GROUP BY class;

-- 재학생 중 수강신쳥 연도, 학기와 등록년도, 학기가 동일한 학생의 학번과 이름, 수강년도, 수강학기를 

-- 출력하라 

-- 

-- 재학생 중 2006년에 수강 신청을 했으며 2006년에 등록한 학생의 평균 등록금보다 더 많은 등록금을 납부해야하고, 성별이 남자인 학생의 학번과 이름을 출력하라

-- 1

CREATE view greater1 as

select DISTINCT stu_no

from fee

where fee_total > (select avg(fee_total) from fee where fee_year=2006);

-- 2

create view first as

select DISTINCT stu_no

from attend

where stu_no in 

(select stu_no from attend where att_year = '2006')

-- 3 

select stu_no, stu_name

from student

where SUBSTRING(ID_NUM,8,1) = '1'

AND STU_NO IN

(SELECT STU_NO FROM GREATER)

AND STU_NO IN 

(SELECT STU_NO FROM FIRST);

use haksa;
 
 
 
MySQL 뷰 생성
CREATE VIEW 뷰이름
AS SELECT문
 
-- student 테이블로부터 모든 학생의 학번과 학년, 반을 가지고 있는 뷰 테이블을 생성하라
 
create view student_1
as select * from student;
 
select * from student_1;
 
 
 
-- 등록한 학생의 학번과 등록년도에 대한 뷰테이블을 생성하라
-- 
 
create view student_2
as select s.stu_no, a.att_year
from student s, attend a
where s.stu_no = a.stu_no;
 
select * from student_2;
 
 
create view v_fee
as select stu_no, fee_year
from fee
where fee_year is not null;
 
select * from v_fee;
 
 
-- 재학생의 학번과 이름, 성별, 입학년도, 생년, 월, 일, 나이에 대한 뷰 테이블을 생성하라
 
 
create view student_6
as select stu_no, stu_name, id_num,substring(id_num,8,1) "sex", substring(id_num,1,2),substring(id_num,3,2),substring(id_num,5,2), 
year(now()) - birth_year +1 "age"
from student;
 
select * from student_6;
 
 
-- 재학생 중 21세 이상인 여학생의 학번, 이름, 성별, 출생년도, 나이를 출력하라
-- 
 
select stu_no, stu_name 
from student_6
where age > 20 and sex = 2;
 
 
-- 재학생중 21세에 해당하는 학생의 성년식 행사를 위한 명단을 출하라. 단, 출력 형식은 학과, 
-- 학년, 학번, 이름, 생년, 월, 일, 나이를 출력하라 
-- 
 
-- 뷰 테이블에서 재학생 중 20세 이상이고, 2000~2004년에 입학한 학생을 구하라 
-- 
 
 
-- 뷰 테이블을 삭제하라. 
-- 
drop viw student_8;
 
 
-- 전라남도 여수지역 (우편번호 550)에 살고 있는 학생의 학번, 이름, 현주소의 우편번호 3자리를
-- 가지는 뷰 테이블을 생성하라 
--
 
 
    create view v_address(hak,irum, hpost) as
    select stu_no, stu_name, substring(post_no,1,3)
    from student
    where substring(post_no,1,3) = '550'
    
    select * from v_address;
    
 
-- 등록금 총액별로 학생 인원 수 현황을 생성하는 뷰테이블을 생성하라 
-- 
 
create view v_feetotal1(fee_total,row_total) as
select fee_pay,count(*)
from fee
GROUP BY fee_pay
 
select * from v_feetotal1;
 
-- 등록금 총액별로 학생 인원 수 현황을 생성한 뷰 테이블 의 내용을 출력하라 
-- 
 
select fee_total, row_total
from v_feetotal1;
 
-- 학적테이블에서 1985년 이전에 출생한 모든 학생에 대한 뷰 테이블을 생성하라
-- 
 
create view v_old as
select *
from student
where birth_year < 1985;
 
 
-- 
-- 위의 테이블에서 학번이 20001001인 학생의 출생년도를 1986으로 변경하라 
-- 
 
update v_old
set birth_year = '1986'
where stu_no = '20001001';
 
 
select stu_no, stu_name, birth_year from v_old;
 
 
select stu_no, stu_name, birth_year from student;
 
 
update student
set birth_year = '1981'
where stu_no = '20001001';
 
 
 
 
 
-- 
-- 학적테이블에서 1985년 이전에 출생한 모든 학생에 대한 뷰테이블을 생성하라 
-- 
create view v_old12 as
select *
from student
where birth_year < 1985
with CHECK OPTION;
 
 
-- 위 테이블에서 학번이 20001001인 학생의 출생 년도를 1986으로 변경하라 
 
 
    update v_old12
    set birth_year = '1986'
    where stu_no ='20001001'; 
 
 
 
 
-- 
-- 
-- --
-- 등록 테이블로 부터 학번과 학생별 등록금 납입 총액의 합계로 구성하는 뷰 테이블을 생성하라
-- 
 
CREATE view totals (stu_no, fee_total)as
select stu_no, sum(fee_pay)
from fee
GROUP BY stu_no;
 
-- 뷰 테이블로 부터 학생별 등록금 납입총액의 최대값을 구하라  
-- 
 
select max(fee_total)
from totals
 
-- 뷰 테이블과 학적테이블을 이용하여  학번, 이름, 납입 총액을 출력하라.
-- 
 
select s.stu_no, s.stu_name, t.fee_total
from student s, totals t
where s.stu_no = t.stu_no
 
 
-- 수강 신청한 학생 중에서 뷰 테이블에 존재하는 학생의 학번, 납입총액을 출력하라
-- 
 
select stu_no, fee_total
from totals
where stu_no in
(select stu_no from attend);
 
 
-- 뷰 테이블에 존재하는 학생의 학번, 납입총액을 출력하라. 단, 출력 순서는 납입 총액 내림차순으로 
-- 정렬한다. 
-- 
select stu_no, fee_total
from totals
order by fee_total desc;
 
-- 뷰 테이블에 존재하는 학생의 학번과 동아리 테이블에 존재하는 학생의 학번을 학번 오름차순으로 정렬하여 출력하라.
-- 
 
select stu_no from totals
union
select stu_no from circle
order by stu_no;
 
-- 수강신청 테이블에서 학생별, 수강년도별, 학기별로 그룹을 만들고 이 그룹의 수강신청 학점이 
-- 5학점 이상인 학생의 학번, 연도, 학기, 수강학점계를 뷰 테이블 을 생성하라 
-- 
 
 
select stu_no, att_year, att_term
from attend
group by stu_no, att_year, att_term
having sum(att_point) > 5;
 
 
-- 적어도 한번 이상 등록한 학생들을 학적테이블과 동일한 가상 테이블 aa 를 생성하라 
-- 
create view aa as
select * from student
where stu_no 
IN
(select stu_no
from fee);
 
 
 
-- 적어도 한번은 등록하고 남자인 학생의 학번과 이름을 출력하라 
-- 
 
select stu_no, stu_name
from aa
where substring(id_num,8,1)=1;
 
 
-- 응용분야
-- 
-- 
-- 학급 3반에서 등록한 학생의 학번과 반을 출력하라
-- 
 
select stu_no, class
from student
where stu_no
in
(select stu_no from fee)
and class = 3;
 
-- 학급별로 등록한 학생의 학급 통계를 출력하라 
-- 
 
select class, count(*)
from  student
where stu_no
in
(select stu_no from fee)
GROUP BY class;
 
 
-- 등록한 학생의 학번과 반으로 구성되는 테이블 aaaa를 생성하라
-- 
 
create view aaaa as
select * from student
where stu_no 
IN
(select stu_no
from fee);
 
 
select * from aaaa where class=3;
 
 
select count(*) from aaaa GROUP BY class;
 
 
-- 재학생 중 수강신쳥 연도, 학기와 등록년도, 학기가 동일한 학생의 학번과 이름, 수강년도, 수강학기를 
-- 출력하라 
-- 
 
 
-- 재학생 중 2006년에 수강 신청을 했으며 2006년에 등록한 학생의 평균 등록금보다 더 많은 등록금을 납부해야하고, 성별이 남자인 학생의 학번과 이름을 출력하라
 
-- 1
 
CREATE view greater1 as
select DISTINCT stu_no
from fee
where fee_total > (select avg(fee_total) from fee where fee_year=2006);
 
 
 
-- 2
create view first as
select DISTINCT stu_no
from attend
where stu_no in 
(select stu_no from attend where att_year = '2006')
 
 
-- 3 
select stu_no, stu_name
from student
where SUBSTRING(ID_NUM,8,1) = '1'
AND STU_NO IN
(SELECT STU_NO FROM GREATER)
AND STU_NO IN 
(SELECT STU_NO FROM FIRST);