db3주차

데이타 베이스


-- db 3주차  9월 7일
-- 0. 회원의 등록현황을 출력하기 위한 뷰를 생성하시오.
create view members_report
as
select uid, uname, age, email, lname, members.ddd, (members.ddd+'-'+phone) tel, members.cname, dc
from members, location, class
where members.ddd = location.ddd and members.cname = class.cname
-- 뷰테이블 검색
select * from members_report
-- 1. 상품의 품목별 판매량과 판매 후 재고량을 구하는 뷰를 생성하시오.
create view stock_rpeort
as  select uid, uname, age,members .ddd, phone, email
 from members, location, class
 where members.ddd = location.ddd and members.cname = class.cname
-- 만들어진 뷰테이블 검색
select * from stock_rpeort

-- 뷰 내용 확인
sp_helptext stock_rpeort
-- 2. 회원별 상품별 판매현황을 뷰로 만들기
create view sale_report
as
select members.uid, members.uname, goods.gname, goods.price, sales.amount, (goods.price * sales.amount)tot
from members, sales, goods
where members.uid = sales.uid and goods.gid = sales.gid
--  뷰내용 검색하기
select * from sale_report

-- 3. 물품을 구입한 회원을 출력
create view salemember
as
select uid, uname, email, phone
from members
where EXISTS(select amount from sales  where uid = members.uid)
-- 뷰내용 검색하기
select * from salemember
-- 4. 뷰가 참조하고 있는 테이블들과 뷰의 정보보기
sp_depends sale_report -- 참조 내용
-- 5. 시스템에 존재하는 여러 뷰들에 대한 정보확인 명령
select * from information_schema.views

-- 뷰의 수정과 삭제
/* 뷰 사용상의 제약 예제 */
-- 1. 집계함수나 group by 를 이용하여 정의된 뷰
select * from members
-- 각 지역별 회원수로 만들어진 뷰
create view ddd_count
as
 select ddd, count(ddd)'회원수'
 from members
group by ddd
-- 뷰 결과 검색
select * from ddd_count
-- 삽입해보기 (삽입불가능 원인은?)
insert ddd_count
values('051', 5)  -- 뷰 또는 함수에 집계가 있어서 업데이트 불가능하다.
-- 삽입해보기2 ( 2개 이상의 테이블을 이용하여 만들어진 뷰에 자료를 수정할 경우에는 한번에 한 테이블만 엑세스 해야한다.
select * from members
select * from location
create view members_info
as
 select uid, uname, age, members.ddd, lname, cname
 from members, location
 where members.ddd = location.ddd
-- 뷰 정보 보기
select * from members_info
-- 뷰 수정하기
-- 다음과 같이 수정하면 오류 발생==> 원인은?
update members_info
set lname=' 성남', cname='대우'  -- 두 테이블을 참조한다. 여러 자료들에 영향을 주기 때문에 업데이트 할수 없다.
where uid = 7328
-- 테이블 하나 수정
update members_info
set cname='대우' -- 한 테이블을 수정하기 때문에 업데이트 가능하다.
where uid = 7328