SQL을 활용한 데이터 베이스 마지막 과제 (총 종합 문제)

과제 -> 14장 연습문제 풀기

과제 제출은 다음주 월요일 까지

-> 해답은 2권이 있음

-> 해답보지말고 고민해서 풀기

 

* 이름.sql.equal 으로 만들어서 월요일 밤 9시에 슬랙으로 제출하기

 

2.번문제는 안해도됨

 

3.문제 풀기 ->c번 생략

 

4. 테이블 insert작업( 제대로 쓰기)

 

d.번은 book_date, exp_ret_Date를 적으면 안된다!

자동계산으로 되야 한다 sysdate-3 이렇게 계산 식이 들어가야 한다

 

b번 문제. 03/03/01 형식으로 변경해서 넣기

 

5번 문제 -> view 생성,데이터가 다 출력될것이다

 

6번 문제 -> 컬럼의 구조 변경하고 데이터 삽입해야함

 

7번 테이블 수정

 

8번 생략 

 

1,3,4,5,6,7 ->숙제

 

[연습14]

1. 다음 테이블 인스턴스 차트를 기반으로 테이블을 생성하십시오, 적합한 데이터 유형을 선택하고 무결성 제약 조건을 추가해야 합니다.

 

a. 테이블 이름: MEMBER

열 이름	member_id	last_name	first_name	address	city	phone	join_date
키 유형	pk
널/고유	nn,u	nn					nn
기본값							system date
데이터 유형	number	varchar2	varchar2	varchar2	varchar2	varchar2	date
길이	10	25	25	100	30	15

join_date system date -> sysdate

기본값 default 

 

b. 테이블 이름: TITLE

열 이름	title_id	title	description	rating	category	release_date
키 유형	PK
널/고유	NN,U	nn	nn
확인				g,pg,r,nc17,nr	drama, comedy, action, chlid, scifi, documen, tary
데이터 유형	number	varchar2	varchar2	varchar2	varchar2	date
길이	10	60	400	4	20

check 제약조건

c. 테이블 이름: title_copy

열 이름	copy_id	title_id	status
키 유형	pk	pk,fk
널/고유	nn,u	nn,u	nn
확인			available, destroyed, renited reserved
fk 참조 테이블		title
fk 참조 열		title_id
데이터 유형	number	number	varchar2
길이	10	10	15

c테이블 pk가 2개일때 복합프라이머키

 

d. 테이블 이름: rental

열 이름	book_date	member_id	copy_id	act_ret_date	exp_ret_date	title_id
키 유형	pk	pk,fk1	pk,fk2			pk,fk2
기본 값	system date				system date+2days
fk 참조 테이블		member	title_copy			title_copy
fk 참조 열		member_id	copy_id			title_id
데이터 유형	date	number	number	date	data	number
길이		10	10			10

테이블이름 : 복합 fk 사용

e. 테이블이름: reservation

열 이름	res_date	member_id	title_id
키 유형	pk	pk,fk1	pk,fk2
널/고유	nn,u	nn,u	nn
fk참조 테이블		member	title
fk 참조 열		member_id	title_id
데이터 유형	date	number	number
길이		10	10

e. reservation -> 

 

3. member 테이블 및 title 테이블의 각 행(row)을 고유하게 식별하는 시퀀스를 생성하십시오.

    a. member 테이블의 회원 번호는 101부터 시작하고 값이 캐시되지 않도록 하십시오. 시퀀스 이름은 member_id_seq로          지정하십시오.

   b. title 테이블의 제목 번호는 91부터 시작하고 캐시되지 않도록 하십시오. 시퀀스 이름은 totle_id_Seq로 지정하십시어.

 

4. 테이블에 데이터를 추가하고 추가할 각 데이터 집합에 대한 스크립트를 작성하십시오.

    a. title 테이블에 영화 제목을 추가하십시오. 영화 정보를 입력할 스크립트를 작성하여 lab14_4a.sql 이라는 이름으로

        저장하십시오. 시퀀스를 사용하여 각 제목을 고유하게 식별하고 출시일을 DD-MON-YYYY 형식으로 입력하십시오.

       문자 필드에 사용된 작은 따옴표는 특별하게 처리된다는 점을 기억하십시오. 추가한 항목을 확인하십시오.

TITLE

Willi and Christmas Too

Alien Again

The glob

My Day Off

Miracles on lce

Soda Gang

Title	Description	Rating	Category	Release_date
Willi and Christmass too	All of Willie's friends make a Christmas list for Santa, but Willie has yet to add his own wish list.	G	CHILD	05-OCT-1995
Alien Again	Yet another installation of science fiction history. Can the heroine save the planet from the alien life form?	R	SCIFI	19-MAY-1995
The Glob	A metor crashes near a small American town and unleashes carnivorous goo in this classic	NR	SCIFI	12-AUG-1995
My Day Off	With a little luck and a lot of ingenuity, a teenager skips school for a dat in New York.	PG	COMEDY	12-JUL-1995
Miracles on Ice	A six-year-old has doubts about Santa Claus, but she discovers that miracles really do exist.	PG	DRAMA	12-SEP-1995
Soda Gang	After discovering a cache of drugs, a young couple find themselves pitted against a vicious gang.	NR	ACTION	01-JUM-1995

b. MEMBER 테이블에 다음 데이터를 추가하십시오. INSERT문을 labl4_4b.sql 이라는 이름의 스크립트에 추가하고 스크립트의 명령을 실행하십시오. 시퀀스를 사용하여 회원 번호를 추가해야 합니다.

First_Name	Last-Name	Address	City	Phone	Join_Date
Carmen	Velasquez	283King Street	Seattle	206-899-6666	08-MAR-1990
LaDoris	Ngao	5Modrany	Bratislava	586-355-8882	08-MAG-1990
Midori	Nagayama	68Via Centrale	Sao Paolo	254-852-5764	17-JUN-1991
Mark	Quick-to-See	6921 King Way	Lagos	63-559-7777	07-APR-1990
Audry	Ropeburn	86 Chu Street	Hong Kong	41-559-87	18-JAN-1991
Molly	Urguhart	3035 Lauier	Quebec	418-542-9988	18-JAN-1991

c. TITLE_COPY 테이블에 다음 영화 복사본을 추가하십시오.

    참고: 이 연습 문제에서는 TITLE_ID 번호를 사용할 수 있습니다.

Title	Copy_id	Status
Willie and Christmas Too	1	AVAILABLE
Alien Again	1	AVAILABLE
	2	RENTED
The Glob	1	AVAILABLE
My Day Off	1	AVAILABLE
	2	AVAILABLE
	3	RENTED
Miracles on Ice	1	AVAILABLE
Soda Gang	1	AVAILABLE

d. RENTAL 테이블에 다음 대여 항목을 추가하십시오.

참고: 제목 번호는 시퀀스 번호에 따라 달라집니다.

Title_Id	Copy_Id	Member_Id	Book_date	Exp_Ret_Date	Act_Ret_Date
92	1	101	3 days ago	1 day ago	2 days ago
93	2	101	1 day ago	1 ady from now
95	3	102	2 days ago	Today
97	1	106	4 days ago	2 days ago	2 days ago

5. 영화 제목, 각 복사본의 대여 가능 여부, 반환 예정일(대여된 경우)을 표시하는 TITLE_AVAIL이라는 이름의 뷰를 생성하고 뷰의 모든 행(row)을 질의한 후 결과를 제목별로 정렬하십시오.

참고: 결과는  각기 다를 수 있습니다.

TITLE	COPY_ID	STATUS	EXP_RET_D
Alien Again	1	AVAILABLE
Alien Again	2	RENTED	26-SEP-01
Miracles on lce	1	AVAILABLE
My Day Off	1	AVAILABLE
My Day Off	2	AVAILABLE
My Day Off	3	RENTED	27-SEP-01
Soda Gang	1	AVAILABLE	25-SEP-01
The Glob	1	AVAILABLE
Willie and Christmas Too	1	AVAILABLE	26-SEP-01

6. 테이블의 데이터를 변경하십시오.

    a. 새 제목을 추가하십시오. 이 영화의 제목은 "Intersteller Wars"이고 등급은 PG이며 SCIFI(공상 과학 영화)로

        분류됩니다. 출시일은 1977년 7월 7일 이며 영화에 대한 정보는 "Futuristic interstellar action movie.

        Can the rebels save the humans from the evil empire?" 입니다. 두 개의 복사본에 대해 TITLE_COPY 레코드를

        추가해야 합니다.

    b. 예약 항목 두 개를 입력하십시오. 하나는 "Interstellar Wars"를 빌리려는 Carmen Velasquez에 대한 항목이고 다른

        하나는 "Soda Gang"을 빌리려는 Mark Quick-to-See에 대한 항목입니다.

    c. 고객 Carmen Velasquez가 영화 "Intersteller Wars"의 복사본 1을 빌렸으므로 영화 예약 항목에서 Carmen Velasquez

        를 제거하고 대여 정보를 기록하십시오. 반환 예정일에는 기본값을 사용할 수 있도록 하고, 작성한 뷰를 사용하여

         대여 정보가 기록되었는지 확인하십시오.

         참조: 결과는 각기 다를 수 있습니다.

TITLE	COPY_ID	STATUS	EXP_RET_D
Alien Again	1	AVAILABLE
Alien Again	2	RENTED	26-SEP-01
Interstellar Wars	1	RENTED	29-SEP-01
Interstellar Wars	2	AVAILABLE
Miracles on ice	1	AVAILABLE
My Day Off	1	AVAILABLE
My Day Off	2	AVAILABLE
My Day Off	3	RENTED	27-SEP-01
Soda Gang	1	AVAILABLE	25-SEP-01
The Glob	1	AVAILABLE
Willi and Christmas Too	1	AVAILABLE	26-SEP-01

7. 테이블 중 하나를 수정하십시오.

a. TITE 테이블에 PRICE 열을 추가하여 비디오 구입 가격을 기록하십시오. 열은 소수 둘째 자리까지 포함하여 총8자리로 표시하십시오. 수정 내용을 확인하십시오.

Name	Null?	Type
TITLE_ID	NOT NULL	NUMBER(10)
TITLE	NOT NULL	VARCHAR2(60)
DESCRIPION	NOT NULL	VARCHAR2(400)
RATING		VARCHAR2(4)
CATEGORY		VARCHAR2(20)
RELEASE_DATE		DATE
PRICE		NUMBER(8,2)

b. 다음 목록에 따라 각 비디오의 가격을 갱신하는 UPADATE문을 포함하는 labl4_7b.sql 이라는 스크립트를 생성하고 스크립트의 명령을 실행하십시오.

참고: 이 연습 문제에서는 TITLE_ID 번호를 사용할 수 있습니다.

Title	Price
Willie and Christmas Too	25
Alien Again	35
The Glob	35
My Day Off	35
Miracles on Ice	30
Soda Gang	35
Interstellar Wars	29

c. 이후에는 모든 제목에 가격이 포함되도록 하십시오. 제약 조건을 확인하십시오.

CONSTRAINT_NAME	C	SEARCH_CONDITION
TITLE_TITLE_NN	C	"TITLE" IS NOT NULL
...
TITLE_PRICE_NN	C	"PRICE" IS NOT NULL

create table member
(member_id number(10),
 last_name varchar2(25) not null,
 first_name varchar2(25),
 address varchar2(25),
 city varchar2(30),
 phone varchar2(15),
 join_date date default sysdate not null,
 constraint member_member_id_pk primary key(member_id)
);

create table title
(title_id number(10),
 title varchar2(60) not null,
 description varchar2(400) not null,
 rating varchar2(4),
 category varchar2(20),
 release_date date,
 constraint title_title_id_pk primary key(title_id),
 constraint title_rating_cka check (rating in('G' , 'PG' , 'R', 'NC17', 'NR')),
 constraint title_category_ckb check (category in ('DRAMA', 'COMEDY', 'ACTION', 'CHILD', 'SCIFI', 'DOCUMENTARY'))
);

create table title_copy
(copy_id number(10),
 title_id number(10),
 status varchar2(15) not null,
 constraint title_copy_pk primary key(copy_id, title_id),
 constraint title_copy_status_ck check(status in('AVAILABLE','DESTROYED','RENTED','RESERVED'))
);

create table rental
(book_date date default sysdate,
 member_id number(10),
 copy_id number(10),
 act_ret_date date,
 exp_ret_date date default sysdate+2,
 title_id number(10),
 constraint rental_pk primary key(book_date, member_id,copy_id, title_id),
 constraint rental_copy_id_title_id_fk foreign key(copy_id, title_id) references title_copy(copy_id,title_id)
);

create table reservation
(res_date date,
 member_id number(10),
 title_id number(10),
 constraint reservation_res_date_pk primary key(res_date, member_id, title_id),
 constraint reservation_member_id_fk foreign key(member_id) references member(member_id),
 constraint reservation_title_id_fk2 foreign key(title_id) references title(title_id)
);

create sequence member_id_seq
start with 101
nocache;

create sequence title_id_seq
start with 92
nocache;

insert into title values(title_id_seq.nextval,'Willie and christmas Too', 'All of Wille''s friends make a Christmas list for Santa, but Willie has yet to add his own wish list.', 'G', 'CHILD','1995-10-05');
insert into title values(title_id_seq.nextval,'Alien Again','Yet another installation of science fiction history.Can the heroine save the planet from the alien life form?','R','SCIFI','1995-05-19');
insert into title values(title_id_seq.nextval,'The Glob', 'A meteor crashes near a small American town and unleashes carnivorous goo in this classic.', 'NR','SCIFI','1995-08-12');
insert into title values(title_id_seq.nextval,'My Day Off','With a little luck and a lot of ingenuity, a teenager skips school for a day in New York','PG','COMEDY','1995-07-12');
insert into title values(title_id_seq.nextval,'Miracles on Ice','A six-year-old has doubts about Santa Claus, but she discovers that miracles really do exist.','PG','DRAMA','1995-09-12');
insert into title values(title_id_seq.nextval,'Soda Gang','After discovering a cache of drugs, a young couple find themselves pitted against a vicious gang.','NR','ACTION','1995-06-01');

insert into member values(member_id_seq.nextval,'Carmen','Velasquez', '283 King Street', 'Seattle', '206-899-6666','1990-03-08');
insert into member values(member_id_seq.nextval,'LaDoris','Ngao','5Modrany','Bratislava','586-355-8882','1990-03-08');
insert into member values(member_id_seq.nextval,'Midori','Nagayama','68 Via Centrale','Sao Paolo','254-852-5764','1991-06-17');
insert into member values(member_id_seq.nextval,'Mark','Quick-to-See','6921 King Way','Lagos','63-559-7777','1990-04-07');
insert into member values(member_id_seq.nextval,'Audry','Ropeburn','86 Chu Street','Hong Kong', '41-559-87','1991-01-18');
insert into member values(member_id_seq.nextval,'Molly','Urguhart','3035 Laurier','Quebec','418-542-9988','1991-01-18');

insert into title_copy values(1,92,'AVAILABLE');
insert into title_copy values(1,93,'AVAILABLE');
insert into title_copy values(2,93,'RENTED');
insert into title_copy values(1,94,'AVAILABLE');
insert into title_copy values(1,95,'AVAILABLE');
insert into title_copy values(2,95,'AVAILABLE');
insert into title_copy values(3,95,'RENTED');
insert into title_copy values(1,96,'AVAILABLE');
insert into title_copy values(1,97,'AVAILABLE');

insert into rental values(sysdate-3,101,1,sysdate-2,sysdate,92);
insert into rental values(sysdate,101,2,null,sysdate-1,93);
insert into rental values(sysdate-2,102,3,null,sysdate,95);
insert into rental values(sysdate-4,106,1,sysdate-2,sysdate-2,97);


create or replace view TITLE_AVAIL
as select title, copy_id, status, exp_ret_date
from title
  left join title_copy using(title_id)
  left join rental using(title_id,copy_id);
select*
from TITLE_AVAIL


insert into title values(title_id_seq.nextval, 'Interstellar Wars','Futuristic intersteller action moive. Can the rebels save the humans from the evil Empire?','PG','SCIFI','1977-07-07');
insert into title_copy values(1,98,'AVAILABLE');
insert into title_copy values(2,98,'AVAILABLE');

insert into reservation values(sysdate,101,98);
insert into reservation values(sysdate,104,97);

insert into rental values(98,1,101);
update title_copy
set status = 'RENTED'
where title_id = 98
and copy_id =1;
delete
from reservation
where member_id=101;

select*
from title_avail
order by title,copy_id;

alter table title
add (price number(8,2));
describe title

alter table title add (price number(8,2));
update title set price=25
where title_id = 92;
update title set price=35
where title_id = 93;
update title set price=35
where title_id = 94;
update title set price=35
where title_id = 95;
update title set price=30
where title_id = 96;
update title set price=35
where title_id = 97;
update title set price=29
where title_id = 98;

alter table title
modify (price constraint title_price_nn not null);
select constraint_name, constraint_type,search_condition
from user_constraints
where table_name = 'TITLE';