주안점 : view/join/constraint(제약조건)
문제1) 난이도(평이) (View, 온라인 도움말)
온라인 도움말에서 “create view”라는 검색어를 이용하여 검색 -> “뷰 만들기” 확인(정독할 것!)하십시오. (중요하므로 모든 문구를 빠짐없이 정독하십시오.)
# 뷰(View) : 가상적인 테이블(virtual table). 필요한 부분만을 가져와서 말 그대로 보여주는 가상 테이블.
문제2) 난이도(중) (view의 속성) (온라인 도움말, 교안, 쿼리 분석기)
온라인 도움말을 정독하고 쿼리 분서기를 탐색한 후, 다음 중 view에 대한 설명 중 옳은 것은 “O”, 틀린 것은 “X”를 표기합니다.
1) 뷰(view)와 테이블(table)의 이름은 동일할 수 있다. ( )
2) default 키워드는 뷰에서 사용할 수 있다. ( )
3) 뷰(view)로는 조인할 수 없다. ( )
4) 뷰(view)와 테이블(table)은 서로 조인(join)할 수 없다. ( )
5) 뷰(view)에서도 테이블의 데이터를 수정할 수 있다. ( )
6) 뷰(view)에서는 테이블의 데이터를 수정할 수 있으되 삭제할 수는 없다. ( )
7) 뷰의 컬럼(필드)에서 산술식 사용시 열 이름을 지정해야 한다. ( )
8) 뷰 자체를 삭제할 수 없다.
9) 쿼리 분석기를 통해서 뷰의 기본적인 SQL문 작성 형식을 파악할 수 있다. ( )
10) 쿼리 분석기에서 뷰의 열(컬럼)으로 진입하였을 때 SQL문 형식으로 뷰의 열의 형식을 볼 수 있도록 메뉴가 활성화된다. ( )
11) 뷰를 작성할 때 굳이 식별자의 규칙에 따르지 않아도 좋다. ( )
12) 인덱스된 뷰 정의에서는 ORDER BY 키워드를 포함할 수 있습니다. ( )
13) 임시 테이블에서는 뷰를 만들 수 없다. ( )
답)
1) 뷰(view)와 테이블(table)의 이름은 동일할 수 있다. ( X ) (도움말)
2) default 키워드는 뷰에서 사용할 수 있다. ( X ) (도움말)
3) 뷰(view)로는 조인할 수 없다. ( X ) (교안)
4) 뷰(view)와 테이블(table)은 서로 조인(join)할 수 없다. ( X ) (교안)
5) 뷰(view)에서도 테이블의 데이터를 수정할 수 있다. ( O ) (도움말)
6) 뷰(view)에서는 테이블의 데이터를 수정할 수 있으되 삭제할 수는 없다. (X) (도움말)
7) 뷰의 컬럼(필드)에서 산술식 사용시 열 이름을 필히 지정해야 한다. (O) (도움말)
8) 뷰 자체를 삭제할 수 없다. ( X ) (도움말)
9) 쿼리 분석기를 통해서 뷰의 기본적인 SQL문 작성 형식을 파악할 수 있다. ( O )
10) 쿼리 분석기에서 뷰의 열(컬럼)으로 진입하였을 때 SQL문 형식으로 뷰의 열의 형식을 볼 수 있도록 메뉴가 활성화된다. ( X )
11) 뷰를 작성할 때 굳이 식별자의 규칙에 따르지 않아도 좋다. ( X ) (도움말)
12) 인덱스된 뷰 정의에서는 ORDER BY 키워드를 포함할 수 있습니다. ( X )
(도움말)
13) 임시 테이블에서는 뷰를 만들 수 없다. ( O ) (도움말)
“뷰 만들기”, “뷰 수정” 이라는 검색어로 “뷰의 수정 및 이름 바꾸기” 기사를 찾아서 정독할 것. 참고로 위의 내용은 교과서 p.422에서 다시금 참조할 수 있습니다.
이미 많이 들은 얘기이지만 “식별자 규칙”으로 검색하여 확인합니다.
문제3) 난이도(평이) (view/join)
교과서 p.154-155에 나와 있는 inner 조인문에 대한 뷰(view)를 작성하십시오.
단, view의 이름은 titles_join_view 라고 지정합니다. 또한 각 테이블에 대한 별칭(alias)
를 각각 t, p 라고 지정하고 “on” 이하의 절에서는 이러한 별칭을 사용하여 필드를 사용합니다.
답)
create view title_join_view
as select title, price, pub_name
from titles t inner join publishers p
on t.pub_id = p.pub_id
-- 단, 여기서 inner는 생략할 수 있습니다.
문제4) 난이도(평이) (view)
위의 뷰의 구조와 결과를 전체적으로 보는 SQL문을 작성하십시오.
답)
sp_help title_join_view
select * from title_join_view
문제5) 난이도(평이) (view, 쿼리분석기, union) (p.413)
쿼리분석기에서 pubs 데이터베이스의 syssegments 라는 시스템 뷰의 명세를 찾아서 create SQL 구문 형식으로 데이터를 덤핑(dumping)하여 답란에 복사하십시오. 단, create 이외의 문장은 기재하지 않습니다.
답)
CREATE VIEW syssegments (segment, name, status) AS
SELECT 0, 'system' , 0
SELECT 1, 'default' , 1
SELECT 2, 'logsegment' , 0
-- 참고로 위의 내용을 덤핑한 이유는 뷰의 형식을 작성할 때 위와 같이 컬럼을 ( , , ) 형식으로 사용할 수 있다는 점과 union을 사용하여 다중 SQL문을 배열할 수 있다는 것을 보기 위해서 출제한 것입니다.
-- 또한 우리는 이러한 덤핑을 통해서 우리가 조작하지 않은 SQL문이 삽입됨으로써 일일이 “Go” 등의 명령문을 기입하지 않아도 MS SQL 서버 측에서 자동적으로 기입될 수 있도록 배려한다는 것을 알 수 있습니다.
문제6) 난이도(중) (view, 쿼리분석기)
문제 “3”에서 작성한 view를 쿼리분석기를 사용하여 데이터 덤핑하되 덤핑한 SQL구문을 사용하여 뷰를 다음 조건과 같이 교정(수정)하도록 합니다.
조건)
title 필드에는 t_title 이라는 별칭(alias)를, price에는 t_price, pub_name은 t_pub_name
SQL구문내의 titles 대한 별칭은 tt로 변경하고, publishers에 대한 별칭은 pp로 변경합니다.
다만 변경 구문 이외의 “GO” 등의 별도의 구문들은 답안에 기재하지 않아도 됩니다.
답)
ALTER view title_join_view
as select title t_title, price t_price, pub_name t_pub_name
from titles tt join publishers pp
on tt.pub_id = pp.pub_id
-- 위와 같은 문제를 통해서 뷰를 어떻게 변경할 수 있는지를 살펴보았습니다. 역시 테이블처럼 alter를 사용하는데 역시 뷰 자체가 가상적인 테이블이기 때문에 별칭을 사용하였을 때극서이 그대로 뷰의 컬럼으로 반영된다는 것이 일반적인 테이블에서의 컬럼 별칭 사용과 다른 점이라고 볼 수 있습니다.
문제7) 난이도(중) (view, 엔터프라이즈 매니저, 온라인 도움말) (p.413)
이번에는 enterprise manager에 진입하여 위에서 만든 뷰의 내용을 확인합니다.
그리고 디자인 뷰의 메뉴를 통해서 위 문제에서 변경한 부분을 다시금 아래의 조건에 맞도록 변경하여 실행한 구문(select)을 답안에 복사 기재합니다. 단, 두가지 변경 방법이 있는데 일단, ERD 메뉴를 사용하지 말고 텍스트 그리드(grid)를 사용합니다.
사용방법이 잘 이해되지 않으면 온라인 도움말에서 “뷰 디자이너 창”이라는 검색어를 기입한 후 해결합니다.
조건)
pub_id를 view에서 볼 수 있는 필드로 추가하되 별칭(alias)은 t_pub_id 라고 합니다.
단, 정렬방식은 내림차순을 사용합니다.
답)
SELECT TOP 100 PERCENT tt.title AS t_title, tt.price AS t_price,
pp.pub_name AS t_pub_name, tt.pub_id AS t_pub_id
FROM dbo.titles tt INNER JOIN
dbo.publishers pp ON tt.pub_id = pp.pub_id
ORDER BY tt.pub_id DESC
-- 위의 문제는 EM(Enterprise Manager)를 사용하여 어떻게 뷰를 조작할 수 있는지를 확인하는 문제입니다. 이렇게 메뉴를 사용하면 쉽게 뷰를 조작할 수 있습니다.
문제에서 전제한대로 온라인 도움말을 충분히 활용하면 문제없이 뷰를 수정할 수 있습니다.
문제8) 난이도(평이), (view, table, em/design view)
위의 EM(Enterprise Manager)의 디자인 뷰를 이용하여 위에서 작성된 “뷰”의 데이터를 아래의 조건과 같이 변경 적용합니다. 이때 실제 테이블에서 아래의 데이터를 title 필드로 조회하는 SQL문을 작성하십시오. 단, 출력시 title_id와 더불어 3개의 컬럼을 출력합니다.
조건)
t_title : But Is It User Friendly?
t_price : 23.95
답)
select title_id, title, price
from titles
where title='But Is It User Friendly?';
-- 위의 문제는 뷰에서도 충분히 테이블의 내용을 변경할 수 있다는 것을 보여주기 위한 문제입니다.
문제9) 난이도(평이), (view)
문제 “7”에서 제시된 SQL문을 “title_temp_view”라는 이름으로 뷰를 작성하고 전체 컬럼을조회한 후 바로 지우는 쿼리문을 작성하십시오. (작성할 총 SQL
답)
create view title_temp_view
as SELECT TOP 100 PERCENT tt.title AS t_title, tt.price AS t_price,
pp.pub_name AS t_pub_name, tt.pub_id AS t_pub_id
FROM dbo.titles tt INNER JOIN
dbo.publishers pp ON tt.pub_id = pp.pub_id
ORDER BY tt.pub_id DESC
select * from title_temp_view
drop view title_temp_view
문제10) 난이도(고), (constraint:제약조건)
먼저 아래의 조건의 테이블을 작성하는 쿼리문을 만듭니다. 테이블 작성시 PK에 해당되는 키는 제약조건(constraint) 키워드를 반드시 사용하여 작성합니다.
작성 후, 별도로 제시한 제약조건을 사용하여 테이블을 변경하는 쿼리문을 작성하십시오.
단, 제약조건의 이름은 member_fk 라고 명명합니다.
테이블명 : member_temp_table
|
필드 |
자료형 |
기정값 |
null 허용 |
제약조건 |
설명 |
|
member_id |
varchar(20) |
|
not null |
PK(primary key) |
회원아이디 |
|
member_pw |
varchar(20) |
|
not null |
|
회원비밀번호 |
|
member_intro |
varchar(200) |
|
|
|
회원소개 |
|
member_jumin |
char(13) |
|
not null |
unique |
회원주민번호 |
제약조건)
member_id 를 외래키로 설정합니다.
답)
create table member_temp_table (
member_id varchar(20) not null
constraint member_id primary key,
member_pw varchar(20) not null,
member_intro varchar(200),
member_jumin char(13) not null unique
)
alter table member_temp_table
add constraint member_fk
foreign key (member_id)
references member_temp_table(member_id)