[Oracle] 19. 연습 문제 10 - 1 : DDL 복습 / 10

Recent Posts

Today

Total

관리 메뉴

오늘이라도

[Oracle] 19. 연습 문제 10 - 1 : DDL 복습 / 10 - 2 : DML 복습 본문

취업성공패키지 SW 개발자 교육/Oracle RDBMS

[Oracle] 19. 연습 문제 10 - 1 : DDL 복습 / 10 - 2 : DML 복습

upcake_ 2020. 5. 21. 09:32

https://github.com/upcake/Class_Examples

교육 중에 작성한 예제들은 깃허브에 올려두고 있습니다.

gif 파일은 클릭해서 보는 것이 정확합니다.

- 연습 문제 10 - 1 : DDL 복습 -

--[연습문제 10 - 1]
--1. 테이블명 : STAR_WARS(영화 정보)
--   칼럼 : EPISODE_ID    : 에피소드 아이디, 숫자 타입(5), 기본키
--          EPISODE_NAME  : 에피소드에 따른 영화 제목, 가변 문자 타입(50)
--          OPEN_YEAR     : 개봉 연도, 숫자 타입(4)
CREATE TABLE star_wars (
episode_id NUMBER(5) CONSTRAINT sw_episode_id_pk PRIMARY KEY,
episode_name VARCHAR2(50),
open_year NUMBER(4)
);

--2. 테이블명 : characters(등장인물)
--   칼럼 : character_id    : 등장인물 아이디, 숫자 타입(5), 기본키
--          character_name  : 등장인물 이름, 가변 문자 타입(30)
--          master_id       : 등장인물이 제다이일 경우, 마스터 아이디 값을 가짐, 숫자 타입(5)
--          role_id         : 등장인물 역할 아이디, 숫자 타입(4)
--          email           : 등장인물 이메일 주소, 가변 문자 타입(40)
CREATE TABLE characters (
character_id NUMBER(5) CONSTRAINT char_character_id_pk PRIMARY KEY,
character_name VARCHAR2(30),
master_id NUMBER(5),
role_id NUMBER(4),
email VARCHAR2(40)
);

--3. 테이블명 : casting(등장인물과 실제 배우의 정보)
--   칼럼 : episode_id    : 에피소드 아이디, 숫자 타입(5), 기본키
--          character_id  : 등장인물 아이디, 숫자 타입(5), 기본키
--          real_name     : 등장인물의 실제 이름, 가변 문자 타입(30)
CREATE TABLE casting (
episode_id NUMBER(5),
character_id NUMBER(5),
real_name VARCHAR2(30),
CONSTRAINT cast_episode_character_id_pk PRIMARY KEY (episode_id, character_id)
);

--캐스팅 테이블 목록
INSERT INTO CASTING VALUES ( 4, 1, '마크 해밀');
INSERT INTO CASTING VALUES ( 4, 2, '해리슨 포드');
INSERT INTO CASTING VALUES ( 4, 3, '캐리 피셔');
INSERT INTO CASTING VALUES ( 4, 4, '알렉 기네스');
INSERT INTO CASTING VALUES ( 4, 5, '데이비드 프로우즈');
INSERT INTO CASTING VALUES ( 4, 6, '제임스 얼 존스');
INSERT INTO CASTING VALUES ( 4, 7, '안소니 다니엘스');
INSERT INTO CASTING VALUES ( 4, 8, '케니 베이커');
INSERT INTO CASTING VALUES ( 4, 9, '피터 메이휴');

INSERT INTO CASTING VALUES ( 5, 1, '마크 해밀');
INSERT INTO CASTING VALUES ( 5, 2, '해리슨 포드');
INSERT INTO CASTING VALUES ( 5, 3, '캐리 피셔');
INSERT INTO CASTING VALUES ( 5, 4, '알렉 기네스');
INSERT INTO CASTING VALUES ( 5, 5, '데이비드 프로우즈');
INSERT INTO CASTING VALUES ( 5, 6, '제임스 얼 존스');
INSERT INTO CASTING VALUES ( 5, 7, '안소니 다니엘스');
INSERT INTO CASTING VALUES ( 5, 8, '케니 베이커');
INSERT INTO CASTING VALUES ( 5, 9, '피터 메이휴');
INSERT INTO CASTING VALUES ( 5, 10, '빌리 디 윌리엄스');
INSERT INTO CASTING VALUES ( 5, 11, '프랭크 오즈');

INSERT INTO CASTING VALUES ( 6, 1, '마크 해밀');
INSERT INTO CASTING VALUES ( 6, 2, '해리슨 포드');
INSERT INTO CASTING VALUES ( 6, 3, '캐리 피셔');
INSERT INTO CASTING VALUES ( 6, 4, '알렉 기네스');
INSERT INTO CASTING VALUES ( 6, 5, '데이비드 프로우즈');
INSERT INTO CASTING VALUES ( 6, 6, '제임스 얼 존스');
INSERT INTO CASTING VALUES ( 6, 7, '안소니 다니엘스');
INSERT INTO CASTING VALUES ( 6, 8, '케니 베이커');
INSERT INTO CASTING VALUES ( 6, 9, '피터 메이휴');
INSERT INTO CASTING VALUES ( 6, 10, '빌리 디 윌리엄스');
INSERT INTO CASTING VALUES ( 6, 11, '프랭크 오즈');
INSERT INTO CASTING VALUES ( 6, 12, '이언 맥디어미드');

INSERT INTO CASTING VALUES ( 1, 4, '이완 맥그리거');
INSERT INTO CASTING VALUES ( 1, 7, '안소니 다니엘스');
INSERT INTO CASTING VALUES ( 1, 8, '케니 베이커');
INSERT INTO CASTING VALUES ( 1, 11, '프랭크 오즈');
INSERT INTO CASTING VALUES ( 1, 12, '이언 맥디어미드');
INSERT INTO CASTING VALUES ( 1, 13, '제이크 로이드');
INSERT INTO CASTING VALUES ( 1, 14, '리암 니슨');
INSERT INTO CASTING VALUES ( 1, 15, '나탈리 포트만');
INSERT INTO CASTING VALUES ( 1, 16, '페닐라 어거스트');
INSERT INTO CASTING VALUES ( 1, 17, '아흐메드 베스트');
INSERT INTO CASTING VALUES ( 1, 18, '레이 파크');

INSERT INTO CASTING VALUES ( 2, 4, '이완 맥그리거');
INSERT INTO CASTING VALUES ( 2, 7, '안소니 다니엘스');
INSERT INTO CASTING VALUES ( 2, 8, '케니 베이커');
INSERT INTO CASTING VALUES ( 2, 11, '프랭크 오즈');
INSERT INTO CASTING VALUES ( 2, 12, '이언 맥디어미드');
INSERT INTO CASTING VALUES ( 2, 13, '헤이든 크리스텐슨');
INSERT INTO CASTING VALUES ( 2, 15, '나탈리 포트만');
INSERT INTO CASTING VALUES ( 2, 16, '페닐라 어거스트');
INSERT INTO CASTING VALUES ( 2, 17, '아흐메드 베스트');
INSERT INTO CASTING VALUES ( 2, 19, '테무엘라 모리슨');
INSERT INTO CASTING VALUES ( 2, 20, '사무엘 L. 잭슨');
INSERT INTO CASTING VALUES ( 2, 21, '크리스토퍼 리');

INSERT INTO CASTING VALUES ( 3, 4, '이완 맥그리거');
INSERT INTO CASTING VALUES ( 3, 7, '안소니 다니엘스');
INSERT INTO CASTING VALUES ( 3, 8, '케니 베이커');
INSERT INTO CASTING VALUES ( 3, 11, '프랭크 오즈');
INSERT INTO CASTING VALUES ( 3, 12, '이언 맥디어미드');
INSERT INTO CASTING VALUES ( 3, 13, '헤이든 크리스텐슨');
INSERT INTO CASTING VALUES ( 3, 15, '나탈리 포트만');
INSERT INTO CASTING VALUES ( 3, 17, '아흐메드 베스트');
INSERT INTO CASTING VALUES ( 3, 19, '테무엘라 모리슨');
INSERT INTO CASTING VALUES ( 3, 20, '사무엘 L. 잭슨');
INSERT INTO CASTING VALUES ( 3, 21, '크리스토퍼 리');

--4. STAR_WARS 테이블에 다음과 같이 저장한다.
INSERT INTO star_wars VALUES (4, '새로운 희망(A New Hope)', 1977);
INSERT INTO star_wars VALUES (5, '제국의 역습(The Empires Strikes Back)', 1980);
INSERT INTO star_wars VALUES (6, '제다이의 귀환(Return of the Jedi)', 1983);
INSERT INTO star_wars VALUES (1, '보이지 않는 위험(The Phantom Menace)', 1999);
INSERT INTO star_wars VALUES (2, '클론의 습격(Attack of the Clones)', 2002);
INSERT INTO star_wars VALUES (3, '시스의 복수(Revenge of the Sith)', 2005);

--5. CHARACTERS 테이블에 다음과 같이 저장한다.
INSERT INTO characters VALUES (1, '루크 스카이워커', '', '', 'luke@jedai.com');
INSERT INTO characters VALUES (2, '한 솔로', '', '', 'solo@alliance.com');
INSERT INTO characters VALUES (3, '레이아 공주', '', '', 'leia@alliance.com');
INSERT INTO characters VALUES (4, '오비완 케노비', '', '', 'Obi-Wan@jedai.com');
INSERT INTO characters VALUES (5, '다쓰 베이더', '', '', 'vader@sith.com');
INSERT INTO characters VALUES (6, '다쓰 베이더(목소리)', '', '', 'vader_voice@sith.com');
INSERT INTO characters VALUES (7, 'C-3PO', '', '', 'c3po@alliance.com');
INSERT INTO characters VALUES (8, 'R2-D2', '', '', 'r2d2@alliance.com');
INSERT INTO characters VALUES (9, '츄바카', '', '', 'Chewbacca@alliance.com');
INSERT INTO characters VALUES (10, '랜도 칼리시안', '', '', '');
INSERT INTO characters VALUES (11, '요다', '', '', 'yoda@jedai.com');
INSERT INTO characters VALUES (12, '다쓰 시디어스', '', '', 'sidious@sith.com');
INSERT INTO characters VALUES (13, '아나킨 스카이워커', '', '', 'Anakin@jedai,com');
INSERT INTO characters VALUES (14, '콰이곤 진', '', '', '');
INSERT INTO characters VALUES (15, '아미달라 여왕', '', '', '');
INSERT INTO characters VALUES (16, '아나킨 어머니', '', '', '');
INSERT INTO characters VALUES (17, '자자빙크스(목소리)', '', '', '');
INSERT INTO characters VALUES (18, '다쓰 몰', '', '', 'maul@sith.com');
INSERT INTO characters VALUES (19, '장고 펫', '', '', '');
INSERT INTO characters VALUES (20, '마스터 윈두', '', '', 'windu@jedai.com');
INSERT INTO characters VALUES (21, '두쿠 백작', '', '', 'dooku@jedai.com');

--6. ROLES 테이블을 생성하여 다음과 같이 저장한다.
CREATE TABLE roles (
role_id NUMBER(4) CONSTRAINT roles_role_id_pk PRIMARY KEY,
role_name VARCHAR2(30)
);

INSERT INTO roles VALUES (1001, '제다이');
INSERT INTO roles VALUES (1002, '시스');
INSERT INTO roles VALUES (1003, '반란군');

--7. CHARACTERS 테이블의 ROLE_ID 칼럼의 데이터가 ROLES 테이블의 ROLE_ID 칼럼의 데이터를 참조하도록 CHARACTERS 테이블에 참조키를 생성한다.
ALTER TABLE characters
ADD CONSTRAINT char_role_id_fk FOREIGN KEY(role_id) REFERENCES roles (role_id);

--8. 참조키를 생성했으면 CHARACTERS 테이블의 ROLE_ID 값을 변경한다.
--이메일이 sith 이면 시스족인 1002, alliance 이면 반란군인 1003에 해당한다.
--그리고 제다이 기사는 루크 스카이워커, 오비완 케노비, 요다, 아나킨 스카이워커,
--콰이곤 진, 마스터 윈두, 두쿠 백작으로 1001에 해당한다.
UPDATE characters
SET role_id = 1002
WHERE LOWER(email) LIKE '%sith%';

UPDATE characters
SET role_id = 1003
WHERE LOWER(email) LIKE '%alliance%';

UPDATE characters
SET role_id = 1001
WHERE character_name IN ('루크 스카이워커', '오비완 케노비', '요다', '아나킨 스카이워커', '콰이곤 진', '마스터 윈두', '두쿠 백작');

--9. CHARACTERS 테이블의 MASTER_ID 칼럼은 EMPLOYEES 테이블의 MANAGER_ID와 같은 역할을 한다.
--다음의 인물을 보고 그 마스터의 CHARACTER_ID 값을 찾아 MASTER_ID 칼럼을 변경한다.
UPDATE characters
SET master_id = (SELECT character_id FROM characters WHERE character_name = '오비완 케노비')
WHERE character_name IN ('아나킨 스카이워커', '루크 스카이워커');

UPDATE characters
SET master_id = (SELECT character_id FROM characters WHERE character_name = '요다')
WHERE character_name IN ('마스터 윈두', '두쿠 백작');

UPDATE characters
SET master_id = (SELECT character_id FROM characters WHERE character_name = '다쓰 시디어스')
WHERE character_name IN ('다쓰 베이더', '다쓰 몰');

UPDATE characters
SET master_id = (SELECT character_id FROM characters WHERE character_name = '콰이곤 진')
WHERE character_name IN ('오비완 케노비');

UPDATE characters
SET master_id = (SELECT character_id FROM characters WHERE character_name = '두쿠 백작')
WHERE character_name IN ('콰이곤 진');

--10. CASTING 테이블의 기본키는 EPISODE_ID와 CHARACTER_ID이다.
--두 칼럼은 각각 STAR_WARS와 CHARACTERS 테이블의 기본키를 참조하고 있다.
--CASTING 테이블에 각각 이 두 테이블의 칼럼을 참조하도록 참조키를 생성한다.
ALTER TABLE casting
ADD CONSTRAINT cast_episode_id_fk FOREIGN KEY(episode_id) REFERENCES star_wars (episode_id);

ALTER TABLE casting
ADD CONSTRAINT cast_character_id_fk FOREIGN KEY(character_id) REFERENCES characters (character_id);

- 연습 문제 10 - 2 : DML 복습 -

--[연습문제 10 - 2]
--1. CHARACTERS 테이블의 EMAIL 칼럼에는 각 배열들의 이메일 주소가 저장되어 있다.
--이메일 정보가 없는 배역들의 모든 정보를 조회하는 쿼리문을 작성한다.
SELECT *
FROM characters
WHERE email IS NULL;

--2. CHARACTERS 테이블에는 스타워즈에 등장하는 각 배역들의 정보가 들어 있다.
--이들 중 그 역할이 시스에 해당하는 등장인물을 조회하는 쿼리문을 작성한다.
SELECT *
FROM characters
WHERE role_id = 1002;

--3. 에피소드 4에 출연한 배우들의 실제 이름을 조회하는 쿼리문을 작성한다.
SELECT real_name
FROM casting
WHERE episode_id = 4;

--4. 에피소드 5에 출연한 배우들의 배역 이름과 실제 이름을 조회하는 쿼리문을 작성한다.
SELECT character_name, real_name
FROM casting ca, characters ch
WHERE ca.character_id = ch.character_id(+)
AND episode_id = 5;

--5. 다음은 에피소드 2에 출연한 모든 배우들의 배역 이름, 실제 이름, 역할을 조회하는 쿼리문이다.
--이 쿼리문을 국제표준 조인문으로 바꾸어 작성한다.
SELECT c.character_name, p.real_name, r.role_name
FROM characters c, casting p, roles r
WHERE c.character_id = p.character_id
AND c.role_id = r.role_id(+)
AND p.episode_id = 2;

SELECT c.character_name, p.real_name, r.role_name
FROM characters c LEFT JOIN casting p
ON c.character_id = p.character_id
LEFT JOIN roles r
ON c.role_id = r.role_id
WHERE p.episode_id = 2;

--6. CHARACTERS 테이블에서 배역 이름, 이메일, 이메일 아이디를 조회하는 쿼리문을 작성한다.
--단, 이메일이 id@jedai.com일 경우 이메일 아이디는 id이다.
SELECT character_name, email,
        (SELECT SUBSTR(email, 0, INSTR(email, '@') - 1) FROM characters WHERE email = c.email) email_id
FROM characters c;

--7. 역할이 제다이에 해당하는 배역들의 배역 이름과 그의 마스터 이름을 조회하여 다음의 결과가 나오도록 작성한 쿼리문이다.
--() 안을 알맞게 채워본다.
--SELECT c.character_name, (          )
--FROM characters c, roles r, characters m
--WHERE c.role_id = r.role_id
--AND r.role_name = '제다이'
--AND c.master_id = m.character_id(+)
--ORDER BY 1;

SELECT c.character_name, NVL(m.character_name, '제다이 중의 제다이') master_name
FROM characters c, roles r, characters m
WHERE c.role_id = r.role_id
AND r.role_name = '제다이'
AND c.master_id = m.character_id(+)
ORDER BY 1;

--8. 역할이 제다이에 해당하는 배역들의 배역 이름, 이메일, 마스터의 이메일을 조회하고,
--결과에 제다이 기사의 이메일이 있으면 제다이 기사의 이메일을,
--없으면 마스터의 이메일을 사용하는 EMAILS라는 칼럼까지 추가하여 조회하는 쿼리문을 작성한다.
SELECT c.character_name, c.email, m.email master_email,
        NVL2(c.email, c.email, m.email) emails
FROM characters c, characters m
WHERE c.role_id = 1001
AND c.master_id = m.character_id(+);

--9. 스타워즈 시리즈 별로 출연한 배우의 수를 파악하고자 한다.
--에피소드 이름과 출연 배우 수를 개봉년도 순으로 조회하는 쿼리문을 작성한다.
SELECT sw.episode_name, COUNT(ca.episode_id) actor_cnt
FROM star_wars sw, casting ca
WHERE sw.episode_id = ca.episode_id
GROUP BY sw.episode_name, open_year
ORDER BY open_year;

--10. 스타워즈 전체 시리즈에서 각 배우별 배역 이름, 실제 이름, 출연 횟수를 조회하는데
--출연 횟수가 많은 배역 이름, 실제 이름 순으로 조회하는 쿼리문을 작성한다.
SELECT character_name, real_name, COUNT(*) appear_cnt
FROM casting ca, characters ch
WHERE ca.character_id = ch.character_id
GROUP BY character_name, real_name
ORDER BY 3 DESC;

--11. 10번의 쿼리문을 참고하여 출연 횟수가 많은 상위 3명의 배역명, 실명, 출연 횟수를 조회하는 쿼리문을 작성한다.
SELECT *
FROM (SELECT RANK() OVER(ORDER BY COUNT(*) DESC) rank, character_name, real_name, COUNT(*) appear_cnt 
        FROM casting ca, characters ch
        WHERE ca.character_id = ch.character_id
        GROUP BY character_name, real_name)
WHERE rank <= 3;

--12. 스타워즈 시리즈 별로 어떤 시리즈에 몇 명의 배우가 출연했는지 조회하고자 한다.
--에피소드 시리즈 번호, 에피소드 이름, 출연 배우 수를 조회하는데
--출연 배우 수가 많은 순으로 조회하는 쿼리문을 작성한다.
SELECT sw.episode_id, episode_name, COUNT(ca.episode_id) actor_cnt
FROM star_wars sw, casting ca
WHERE sw.episode_id = ca.episode_id
GROUP BY sw.episode_id, episode_name
ORDER BY 3 DESC;

▼코드 전문

--[연습문제 10 - 1]
--1. 테이블명 : STAR_WARS(영화 정보)
--   칼럼 : EPISODE_ID    : 에피소드 아이디, 숫자 타입(5), 기본키
--          EPISODE_NAME  : 에피소드에 따른 영화 제목, 가변 문자 타입(50)
--          OPEN_YEAR     : 개봉 연도, 숫자 타입(4)
CREATE TABLE star_wars (
episode_id NUMBER(5) CONSTRAINT sw_episode_id_pk PRIMARY KEY,
episode_name VARCHAR2(50),
open_year NUMBER(4)
);

--2. 테이블명 : characters(등장인물)
--   칼럼 : character_id    : 등장인물 아이디, 숫자 타입(5), 기본키
--          character_name  : 등장인물 이름, 가변 문자 타입(30)
--          master_id       : 등장인물이 제다이일 경우, 마스터 아이디 값을 가짐, 숫자 타입(5)
--          role_id         : 등장인물 역할 아이디, 숫자 타입(4)
--          email           : 등장인물 이메일 주소, 가변 문자 타입(40)
CREATE TABLE characters (
character_id NUMBER(5) CONSTRAINT char_character_id_pk PRIMARY KEY,
character_name VARCHAR2(30),
master_id NUMBER(5),
role_id NUMBER(4),
email VARCHAR2(40)
);

--3. 테이블명 : casting(등장인물과 실제 배우의 정보)
--   칼럼 : episode_id    : 에피소드 아이디, 숫자 타입(5), 기본키
--          character_id  : 등장인물 아이디, 숫자 타입(5), 기본키
--          real_name     : 등장인물의 실제 이름, 가변 문자 타입(30)
CREATE TABLE casting (
episode_id NUMBER(5),
character_id NUMBER(5),
real_name VARCHAR2(30),
CONSTRAINT cast_episode_character_id_pk PRIMARY KEY (episode_id, character_id)
);

--캐스팅 테이블 목록
INSERT INTO CASTING VALUES ( 4, 1, '마크 해밀');
INSERT INTO CASTING VALUES ( 4, 2, '해리슨 포드');
INSERT INTO CASTING VALUES ( 4, 3, '캐리 피셔');
INSERT INTO CASTING VALUES ( 4, 4, '알렉 기네스');
INSERT INTO CASTING VALUES ( 4, 5, '데이비드 프로우즈');
INSERT INTO CASTING VALUES ( 4, 6, '제임스 얼 존스');
INSERT INTO CASTING VALUES ( 4, 7, '안소니 다니엘스');
INSERT INTO CASTING VALUES ( 4, 8, '케니 베이커');
INSERT INTO CASTING VALUES ( 4, 9, '피터 메이휴');

INSERT INTO CASTING VALUES ( 5, 1, '마크 해밀');
INSERT INTO CASTING VALUES ( 5, 2, '해리슨 포드');
INSERT INTO CASTING VALUES ( 5, 3, '캐리 피셔');
INSERT INTO CASTING VALUES ( 5, 4, '알렉 기네스');
INSERT INTO CASTING VALUES ( 5, 5, '데이비드 프로우즈');
INSERT INTO CASTING VALUES ( 5, 6, '제임스 얼 존스');
INSERT INTO CASTING VALUES ( 5, 7, '안소니 다니엘스');
INSERT INTO CASTING VALUES ( 5, 8, '케니 베이커');
INSERT INTO CASTING VALUES ( 5, 9, '피터 메이휴');
INSERT INTO CASTING VALUES ( 5, 10, '빌리 디 윌리엄스');
INSERT INTO CASTING VALUES ( 5, 11, '프랭크 오즈');

INSERT INTO CASTING VALUES ( 6, 1, '마크 해밀');
INSERT INTO CASTING VALUES ( 6, 2, '해리슨 포드');
INSERT INTO CASTING VALUES ( 6, 3, '캐리 피셔');
INSERT INTO CASTING VALUES ( 6, 4, '알렉 기네스');
INSERT INTO CASTING VALUES ( 6, 5, '데이비드 프로우즈');
INSERT INTO CASTING VALUES ( 6, 6, '제임스 얼 존스');
INSERT INTO CASTING VALUES ( 6, 7, '안소니 다니엘스');
INSERT INTO CASTING VALUES ( 6, 8, '케니 베이커');
INSERT INTO CASTING VALUES ( 6, 9, '피터 메이휴');
INSERT INTO CASTING VALUES ( 6, 10, '빌리 디 윌리엄스');
INSERT INTO CASTING VALUES ( 6, 11, '프랭크 오즈');
INSERT INTO CASTING VALUES ( 6, 12, '이언 맥디어미드');

INSERT INTO CASTING VALUES ( 1, 4, '이완 맥그리거');
INSERT INTO CASTING VALUES ( 1, 7, '안소니 다니엘스');
INSERT INTO CASTING VALUES ( 1, 8, '케니 베이커');
INSERT INTO CASTING VALUES ( 1, 11, '프랭크 오즈');
INSERT INTO CASTING VALUES ( 1, 12, '이언 맥디어미드');
INSERT INTO CASTING VALUES ( 1, 13, '제이크 로이드');
INSERT INTO CASTING VALUES ( 1, 14, '리암 니슨');
INSERT INTO CASTING VALUES ( 1, 15, '나탈리 포트만');
INSERT INTO CASTING VALUES ( 1, 16, '페닐라 어거스트');
INSERT INTO CASTING VALUES ( 1, 17, '아흐메드 베스트');
INSERT INTO CASTING VALUES ( 1, 18, '레이 파크');

INSERT INTO CASTING VALUES ( 2, 4, '이완 맥그리거');
INSERT INTO CASTING VALUES ( 2, 7, '안소니 다니엘스');
INSERT INTO CASTING VALUES ( 2, 8, '케니 베이커');
INSERT INTO CASTING VALUES ( 2, 11, '프랭크 오즈');
INSERT INTO CASTING VALUES ( 2, 12, '이언 맥디어미드');
INSERT INTO CASTING VALUES ( 2, 13, '헤이든 크리스텐슨');
INSERT INTO CASTING VALUES ( 2, 15, '나탈리 포트만');
INSERT INTO CASTING VALUES ( 2, 16, '페닐라 어거스트');
INSERT INTO CASTING VALUES ( 2, 17, '아흐메드 베스트');
INSERT INTO CASTING VALUES ( 2, 19, '테무엘라 모리슨');
INSERT INTO CASTING VALUES ( 2, 20, '사무엘 L. 잭슨');
INSERT INTO CASTING VALUES ( 2, 21, '크리스토퍼 리');

INSERT INTO CASTING VALUES ( 3, 4, '이완 맥그리거');
INSERT INTO CASTING VALUES ( 3, 7, '안소니 다니엘스');
INSERT INTO CASTING VALUES ( 3, 8, '케니 베이커');
INSERT INTO CASTING VALUES ( 3, 11, '프랭크 오즈');
INSERT INTO CASTING VALUES ( 3, 12, '이언 맥디어미드');
INSERT INTO CASTING VALUES ( 3, 13, '헤이든 크리스텐슨');
INSERT INTO CASTING VALUES ( 3, 15, '나탈리 포트만');
INSERT INTO CASTING VALUES ( 3, 17, '아흐메드 베스트');
INSERT INTO CASTING VALUES ( 3, 19, '테무엘라 모리슨');
INSERT INTO CASTING VALUES ( 3, 20, '사무엘 L. 잭슨');
INSERT INTO CASTING VALUES ( 3, 21, '크리스토퍼 리');

--4. STAR_WARS 테이블에 다음과 같이 저장한다.
INSERT INTO star_wars VALUES (4, '새로운 희망(A New Hope)', 1977);
INSERT INTO star_wars VALUES (5, '제국의 역습(The Empires Strikes Back)', 1980);
INSERT INTO star_wars VALUES (6, '제다이의 귀환(Return of the Jedi)', 1983);
INSERT INTO star_wars VALUES (1, '보이지 않는 위험(The Phantom Menace)', 1999);
INSERT INTO star_wars VALUES (2, '클론의 습격(Attack of the Clones)', 2002);
INSERT INTO star_wars VALUES (3, '시스의 복수(Revenge of the Sith)', 2005);

--5. CHARACTERS 테이블에 다음과 같이 저장한다.
INSERT INTO characters VALUES (1, '루크 스카이워커', '', '', 'luke@jedai.com');
INSERT INTO characters VALUES (2, '한 솔로', '', '', 'solo@alliance.com');
INSERT INTO characters VALUES (3, '레이아 공주', '', '', 'leia@alliance.com');
INSERT INTO characters VALUES (4, '오비완 케노비', '', '', 'Obi-Wan@jedai.com');
INSERT INTO characters VALUES (5, '다쓰 베이더', '', '', 'vader@sith.com');
INSERT INTO characters VALUES (6, '다쓰 베이더(목소리)', '', '', 'vader_voice@sith.com');
INSERT INTO characters VALUES (7, 'C-3PO', '', '', 'c3po@alliance.com');
INSERT INTO characters VALUES (8, 'R2-D2', '', '', 'r2d2@alliance.com');
INSERT INTO characters VALUES (9, '츄바카', '', '', 'Chewbacca@alliance.com');
INSERT INTO characters VALUES (10, '랜도 칼리시안', '', '', '');
INSERT INTO characters VALUES (11, '요다', '', '', 'yoda@jedai.com');
INSERT INTO characters VALUES (12, '다쓰 시디어스', '', '', 'sidious@sith.com');
INSERT INTO characters VALUES (13, '아나킨 스카이워커', '', '', 'Anakin@jedai,com');
INSERT INTO characters VALUES (14, '콰이곤 진', '', '', '');
INSERT INTO characters VALUES (15, '아미달라 여왕', '', '', '');
INSERT INTO characters VALUES (16, '아나킨 어머니', '', '', '');
INSERT INTO characters VALUES (17, '자자빙크스(목소리)', '', '', '');
INSERT INTO characters VALUES (18, '다쓰 몰', '', '', 'maul@sith.com');
INSERT INTO characters VALUES (19, '장고 펫', '', '', '');
INSERT INTO characters VALUES (20, '마스터 윈두', '', '', 'windu@jedai.com');
INSERT INTO characters VALUES (21, '두쿠 백작', '', '', 'dooku@jedai.com');

--6. ROLES 테이블을 생성하여 다음과 같이 저장한다.
CREATE TABLE roles (
role_id NUMBER(4) CONSTRAINT roles_role_id_pk PRIMARY KEY,
role_name VARCHAR2(30)
);

INSERT INTO roles VALUES (1001, '제다이');
INSERT INTO roles VALUES (1002, '시스');
INSERT INTO roles VALUES (1003, '반란군');

--7. CHARACTERS 테이블의 ROLE_ID 칼럼의 데이터가 ROLES 테이블의 ROLE_ID 칼럼의 데이터를 참조하도록 CHARACTERS 테이블에 참조키를 생성한다.
ALTER TABLE characters
ADD CONSTRAINT char_role_id_fk FOREIGN KEY(role_id) REFERENCES roles (role_id);

--8. 참조키를 생성했으면 CHARACTERS 테이블의 ROLE_ID 값을 변경한다.
--이메일이 sith 이면 시스족인 1002, alliance 이면 반란군인 1003에 해당한다.
--그리고 제다이 기사는 루크 스카이워커, 오비완 케노비, 요다, 아나킨 스카이워커,
--콰이곤 진, 마스터 윈두, 두쿠 백작으로 1001에 해당한다.
UPDATE characters
SET role_id = 1002
WHERE LOWER(email) LIKE '%sith%';

UPDATE characters
SET role_id = 1003
WHERE LOWER(email) LIKE '%alliance%';

UPDATE characters
SET role_id = 1001
WHERE character_name IN ('루크 스카이워커', '오비완 케노비', '요다', '아나킨 스카이워커', '콰이곤 진', '마스터 윈두', '두쿠 백작');

--9. CHARACTERS 테이블의 MASTER_ID 칼럼은 EMPLOYEES 테이블의 MANAGER_ID와 같은 역할을 한다.
--다음의 인물을 보고 그 마스터의 CHARACTER_ID 값을 찾아 MASTER_ID 칼럼을 변경한다.
UPDATE characters
SET master_id = (SELECT character_id FROM characters WHERE character_name = '오비완 케노비')
WHERE character_name IN ('아나킨 스카이워커', '루크 스카이워커');

UPDATE characters
SET master_id = (SELECT character_id FROM characters WHERE character_name = '요다')
WHERE character_name IN ('마스터 윈두', '두쿠 백작');

UPDATE characters
SET master_id = (SELECT character_id FROM characters WHERE character_name = '다쓰 시디어스')
WHERE character_name IN ('다쓰 베이더', '다쓰 몰');

UPDATE characters
SET master_id = (SELECT character_id FROM characters WHERE character_name = '콰이곤 진')
WHERE character_name IN ('오비완 케노비');

UPDATE characters
SET master_id = (SELECT character_id FROM characters WHERE character_name = '두쿠 백작')
WHERE character_name IN ('콰이곤 진');

--10. CASTING 테이블의 기본키는 EPISODE_ID와 CHARACTER_ID이다.
--두 칼럼은 각각 STAR_WARS와 CHARACTERS 테이블의 기본키를 참조하고 있다.
--CASTING 테이블에 각각 이 두 테이블의 칼럼을 참조하도록 참조키를 생성한다.
ALTER TABLE casting
ADD CONSTRAINT cast_episode_id_fk FOREIGN KEY(episode_id) REFERENCES star_wars (episode_id);

ALTER TABLE casting
ADD CONSTRAINT cast_character_id_fk FOREIGN KEY(character_id) REFERENCES characters (character_id);

--------------------------------------------------------------------------------------------------------------------------------
--[연습문제 10 - 2]
--1. CHARACTERS 테이블의 EMAIL 칼럼에는 각 배열들의 이메일 주소가 저장되어 있다.
--이메일 정보가 없는 배역들의 모든 정보를 조회하는 쿼리문을 작성한다.
SELECT *
FROM characters
WHERE email IS NULL;

--2. CHARACTERS 테이블에는 스타워즈에 등장하는 각 배역들의 정보가 들어 있다.
--이들 중 그 역할이 시스에 해당하는 등장인물을 조회하는 쿼리문을 작성한다.
SELECT *
FROM characters
WHERE role_id = 1002;

--3. 에피소드 4에 출연한 배우들의 실제 이름을 조회하는 쿼리문을 작성한다.
SELECT real_name
FROM casting
WHERE episode_id = 4;

--4. 에피소드 5에 출연한 배우들의 배역 이름과 실제 이름을 조회하는 쿼리문을 작성한다.
SELECT character_name, real_name
FROM casting ca, characters ch
WHERE ca.character_id = ch.character_id(+)
AND episode_id = 5;

--5. 다음은 에피소드 2에 출연한 모든 배우들의 배역 이름, 실제 이름, 역할을 조회하는 쿼리문이다.
--이 쿼리문을 국제표준 조인문으로 바꾸어 작성한다.
SELECT c.character_name, p.real_name, r.role_name
FROM characters c, casting p, roles r
WHERE c.character_id = p.character_id
AND c.role_id = r.role_id(+)
AND p.episode_id = 2;

SELECT c.character_name, p.real_name, r.role_name
FROM characters c LEFT JOIN casting p
ON c.character_id = p.character_id
LEFT JOIN roles r
ON c.role_id = r.role_id
WHERE p.episode_id = 2;

--6. CHARACTERS 테이블에서 배역 이름, 이메일, 이메일 아이디를 조회하는 쿼리문을 작성한다.
--단, 이메일이 id@jedai.com일 경우 이메일 아이디는 id이다.
SELECT character_name, email,
        (SELECT SUBSTR(email, 0, INSTR(email, '@') - 1) FROM characters WHERE email = c.email) email_id
FROM characters c;

--7. 역할이 제다이에 해당하는 배역들의 배역 이름과 그의 마스터 이름을 조회하여 다음의 결과가 나오도록 작성한 쿼리문이다.
--() 안을 알맞게 채워본다.
--SELECT c.character_name, (          )
--FROM characters c, roles r, characters m
--WHERE c.role_id = r.role_id
--AND r.role_name = '제다이'
--AND c.master_id = m.character_id(+)
--ORDER BY 1;

SELECT c.character_name, NVL(m.character_name, '제다이 중의 제다이') master_name
FROM characters c, roles r, characters m
WHERE c.role_id = r.role_id
AND r.role_name = '제다이'
AND c.master_id = m.character_id(+)
ORDER BY 1;

--8. 역할이 제다이에 해당하는 배역들의 배역 이름, 이메일, 마스터의 이메일을 조회하고,
--결과에 제다이 기사의 이메일이 있으면 제다이 기사의 이메일을,
--없으면 마스터의 이메일을 사용하는 EMAILS라는 칼럼까지 추가하여 조회하는 쿼리문을 작성한다.
SELECT c.character_name, c.email, m.email master_email,
        NVL2(c.email, c.email, m.email) emails
FROM characters c, characters m
WHERE c.role_id = 1001
AND c.master_id = m.character_id(+);

--9. 스타워즈 시리즈 별로 출연한 배우의 수를 파악하고자 한다.
--에피소드 이름과 출연 배우 수를 개봉년도 순으로 조회하는 쿼리문을 작성한다.
SELECT sw.episode_name, COUNT(ca.episode_id) actor_cnt
FROM star_wars sw, casting ca
WHERE sw.episode_id = ca.episode_id
GROUP BY sw.episode_name, open_year
ORDER BY open_year;

--10. 스타워즈 전체 시리즈에서 각 배우별 배역 이름, 실제 이름, 출연 횟수를 조회하는데
--출연 횟수가 많은 배역 이름, 실제 이름 순으로 조회하는 쿼리문을 작성한다.
SELECT character_name, real_name, COUNT(*) appear_cnt
FROM casting ca, characters ch
WHERE ca.character_id = ch.character_id
GROUP BY character_name, real_name
ORDER BY 3 DESC;

--11. 10번의 쿼리문을 참고하여 출연 횟수가 많은 상위 3명의 배역명, 실명, 출연 횟수를 조회하는 쿼리문을 작성한다.
SELECT *
FROM (SELECT RANK() OVER(ORDER BY COUNT(*) DESC) rank, character_name, real_name, COUNT(*) appear_cnt 
        FROM casting ca, characters ch
        WHERE ca.character_id = ch.character_id
        GROUP BY character_name, real_name)
WHERE rank <= 3;        

--12. 스타워즈 시리즈 별로 어떤 시리즈에 몇 명의 배우가 출연했는지 조회하고자 한다.
--에피소드 시리즈 번호, 에피소드 이름, 출연 배우 수를 조회하는데
--출연 배우 수가 많은 순으로 조회하는 쿼리문을 작성한다.
SELECT sw.episode_id, episode_name, COUNT(ca.episode_id) actor_cnt
FROM star_wars sw, casting ca
WHERE sw.episode_id = ca.episode_id
GROUP BY sw.episode_id, episode_name
ORDER BY 3 DESC;

--------------------------------------------------------------------------------------------------------------------------------
SELECT * FROM star_wars;
SELECT * FROM characters;
SELECT * FROM casting;
DROP TABLE star_wars;
DROP TABLE characters;
DROP TABLE casting;
DROP TABLE ROLES;
COMMIT;
--------------------------------------------------------------------------------------------------------------------------------

'취업성공패키지 SW 개발자 교육 > Oracle RDBMS' 카테고리의 다른 글

[Oracle] 18. 제약 조건 : PRIMARY KEY, FOREIGN KEY / VIEW / SEQUENCE (0)	2020.05.20
[Oracle] 17. DDL : CREATE, ALERT, DROP, TRUNCATE, RENAME / 제약 조건 : NOT NULL, CHECK, DROP CONSTRAINT, UNIQUE (0)	2020.05.19
[Oracle] 16. DML : INSERT, ITAS, CTAS,UPDATE, DELETE (0)	2020.05.18
[Oracle] 15. ROWNUM/ RANK() OVER(), DENSE_RANK() OVER() /연습문제 6 - 4/SET 연산자 : UNION, INTERSECT, MINUS (0)	2020.05.15
[Oracle] 14. 상호 연관 서브 쿼리, 인라인 뷰 서브 쿼리 (0)	2020.05.14

'취업성공패키지 SW 개발자 교육/Oracle RDBMS' Related Articles

오늘이라도

[Oracle] 19. 연습 문제 10 - 1 : DDL 복습 / 10 - 2 : DML 복습 본문

[Oracle] 19. 연습 문제 10 - 1 : DDL 복습 / 10 - 2 : DML 복습

- 연습 문제 10 - 1 : DDL 복습 -

- 연습 문제 10 - 2 : DML 복습 -

'취업성공패키지 SW 개발자 교육 > Oracle RDBMS' 카테고리의 다른 글

티스토리툴바