SQL 실습 정리 - 3

날짜:

  본 실습은 Oracle9i introduction to Oracle9i SQL vol2의 '14장 sql 강습' 을 토대로 진행했습니다.


※조인(JOIN)

두 개 이상의 테이블을 조건에 맞춰 하나의 테이블처럼 조회하는 것

조인 조건은 테이블의 개수-1 만큼 필요함


INNER JOIN

SELECT ... FROM tableA (INNER) JOIN tableB on tableA 열 = tableB 열 [where 조건]

두 테이블의 공통되는 열을 기준으로 조인


NATURAL JOIN

SELECT ... FROM tableA NATURAL JOIN tableB  [where 조건]

두 테이블의 동일한 열을 기준으로 자동으로 조인(열이 같은 모든 값으로 조인된다)


OUTER JOIN

동일한 열 값이 없는 경우,  NULL  값으로 대체하여 조인하는 기법

사용 예제 테이블

    tableA                           tableB

FULL OUTER JOIN

SELECT ... FROM tableA FULL OUTER JOIN tableB on tableA 열 = tableB 열 [where 조건]

두 열이 같은 값에 대해서 조인하되, tableA, tableB 에 해당하는 열이 없더라도 출력, 조인 조건에 해당하지 않으므로 NULL 값으로 대체


LEFT OUTER JOIN

SELECT ... FROM tableA LEFT OUTER JOIN  tableB on tableA 열 = tableB 열 [where 조건]

두 열이 같은 값에 대해서 조인하되, tableA 에 해당하는 열이 없더라도 출력, 조인 조건에 해당하지 않으므로 NULL 값으로 대체


RIGHT OUTER JOIN

SELECT ... FROM tableA RIGHT OUTER JOIN tableB on tableA 열 = tableB 열 [where 조건]

두 열이 같은 값에 대해서 조인하되,  tableB 에 해당하는 열이 없더라도 출력, 조인 조건에 해당하지 않으므로 NULL 값으로 대체





※VIEW

실제 객체가 존재하기보단 토대인 서브쿼리가 실행되어 테이블 처럼 보여주는 객체

DML(INSERT, UPDATE, DELETE)에 대해 제약이 있으며, 서브쿼리로 만들어지기 때문에 필요한 데이터만 출력이 가능하다.


기본 형식

CREATE VIEW view 이름 AS (SELECT ... );


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





VIEW 생성 스크립트

create view title_avail as select t.title, c.copy_id, c.status, r.exp_ret_date

from title t, title_copy c, rental r

where t.title_id = c.title_id and

c.title_id= r.title_id(+) and c.copy_id=r.copy_id(+)

order by title, c.copy_id;


6. 테이블의 데이터 변경

a. TITLE table 에 다음의 작품을 추가하고, TITLE_COPY 에 두 개의 복사본을 생성하십시오.

작품명 : Interstellar Wars

설명 : Futuristic interstellar action movie. Can the rebels save the humans from the evil empire?

등급 : PG

장르 : SCIFI

출시일 : 1977-07-07


삽입 스크립트

--TITLE 추가
insert into title values(title_id_seq.nextval, 'Interstellar Wars',
'Futuristic interstellar action movie. Can the rebels save the humans from the evil empire?',
'PG','SCIFI',TO_DATE('1977-07-07', 'YYYY-MM-DD'));

--TITLE_COPY 복사본 2개 추가

insert into title_copy values(1,title_id_seq.currval, 'AVAILABLE');

insert into title_copy values(2,title_id_seq.currval, 'AVAILABLE');


b. Interstellar Wars 를 대여하려는 Carmen velasquez 와 Soda Gang 을 대여하려는 Mark Quick-to-See 에 대한 항목을 입력하시오


RESERVATION 삽입 스크립트

insert into reservation values(sysdate,(select member_id from member where last_name = 'Carmen' and first_name = 'Velasquez'),(select title_id from title where title = 'Interstellar Wars'));

insert into reservation values(sysdate,(select member_id from member where last_name = 'Mark' and first_name = 'Quick-to-See'),(select title_id from title where title = 'Soda Gang'));


※DELETE
해당 하는 행의 삭제

기본 형식
DELETE FROM table 명 WHERE 조건식(조건식이 없는 경우 테이블의 모든 행 삭제)

※UPDATE
해당 하는 행의 데이터 갱신
UPDATE table 명 set 열 1 = 값[, 열 2 = 값....] where 조건식 (조건식이 없는 경우 테이블의 모든 행 갱신)


6.Carmen velasquez 가 영화 Interstellar Wars 를 대여했다. 이때, 예약 항목에서  Interstellar Wars의 항목을 제거하고 대여 정보를 기록 하시오. 반환 예정일에는 기본값을 사용 할 수 있도록 하고, 작성한 뷰를 사용하여 대여 정보가 기록되었는지 확인하시오

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

수정 및 변경 스크립트

--유저 데이터의 삽입
--서브쿼리를 이용하여 해당 유저의 정보를 검색 및 값 반환
insert into rental(title_id,  member_id, book_date, copy_id)
select r.title_id, r.member_id, r.res_date, t.copy_id 
from reservation r, title_copy t 
where r.title_id = t.title_id and
r.member_id = (select member_id from member where last_name = 'Carmen' and first_name = 'Velasquez') and 
t.title_id = (select title_id from title where title = 'Interstellar Wars') and status = 'AVAILABLE' and rownum = 1 
;

--해당 작품에 대한 상태 갱신
update title_copy set status = 'RENTED' 
where title_id = (select title_id from reservation where member_id = (select member_id from member where last_name = 'Carmen' and first_name = 'Velasquez')
and copy_id  = 
(
    select copy_id from title_copy where title_id = 
    (select title_id from reservation where member_id = 
     (select member_id from member where last_name = 'Carmen' and first_name = 'Velasquez')) 
     and status = 'AVAILABLE' AND ROWNUM = 1
    ) 
);

--예약 정보 삭제
delete from reservation 
where member_id = (
    select member_id from member 
    where last_name = 'Carmen' and first_name = 'Velasquez');
해당 테이블을 확인


-해당 작품에 대해 상태 갱신 완료


댓글

댓글 쓰기