스트워즈 연습문제
테이블 생성
▶ 테이블명 : STAR_WARS(영화 정보)
CREATE TABLE star_wars (
episode_id NUMBER(5) CONSTRAINT sw_episode_id_pk PRIMARY KEY, -- 에피소드 아이디
episode_name VARCHAR2(50), -- 에피소드에 따른 영화 제목
open_year NUMBER(4) -- 개봉연도
);
▶ 테이블명 : characters(등장인물)
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) -- 등장인물 이메일 주소
);
▶ 테이블명 : casting(등장인물과 실제 배우의 정보)
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)
);
▶ ROLES 테이블
CREATE TABLE roles (
role_id NUMBER(4) CONSTRAINT roles_role_id_pk PRIMARY KEY,
role_name VARCHAR2(30)
);
데이터 입력
▶ CASTING 테이블
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, '크리스토퍼 리');
▶ 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);
▶ 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');
▶ ROLES 테이블
INSERT INTO roles values (1001, '제다이');
INSERT INTO roles values (1002, '시스');
INSERT INTO roles values (1003, '반란군');
▶ 참조키 생성
-- CHARACTERS 테이블의 ROLE_ID 칼럼의 데이터가 ROLES 테이블의 ROLE_ID 칼럼의 데이터를 참조하도록
-- CHARACTERS 테이블에 참조키를 생성한다.
ALTER TABLE characters
ADD CONSTRAINT char_role_id_fk FOREIGN KEY(role_id) REFERENCES roles (role_id);
문제
▶ 참조키를 생성했으면 CHARACTERS 테이블의 ROLE_ID 값을 변경한다.
--이메일이 sith 이면 시스족인 1002, alliance 이면 반란군인 1003에 해당한다.
--그리고 제다이 기사는 루크 스카이워커, 오비완 케노비, 요다, 아나킨 스카이워커,
--콰이곤 진, 마스터 윈두, 두쿠 백작으로 1001에 해당한다.
UPDATE characters
SET role_id = 1002
WHERE email like '%sith%'; -- 4개의 행이 업데이트 되었습니다
UPDATE characters
SET role_id = 1003
WHERE email like '%alliance%'; -- 5개의 행이 업데이트 되었습니다
UPDATE characters
SET role_id = 1001
WHERE character_name IN ('루크 스카이워커', '오비완 케노비', '요다', '아나킨 스카이워커', '콰이곤 진', '마스터 윈두', '두
쿠 백작'); -- 7개의 행이 업데이트 되었습니다
▶ 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 = '오비완 케노비';
UPDATE characters
SET master_id = (SELECT character_id FROM characters WHERE character_name = '두쿠 백작')
WHERE character_name = '콰이곤 진';
▶ 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);
▶ SELECT * FROM characters;
문제
▶ CHARACTERS 테이블의 EMAIL 칼럼에는 각 배열들의 이메일 주소가 저장되어 있다.
--이메일 정보가 없는 배역들의 모든 정보를 조회하는 쿼리문을 작성한다.
SELECT * FROM characters WHERE email IS NULL;
▶ CHARACTERS 테이블에는 스타워즈에 등장하는 각 배역들의 정보가 들어 있다.
--이들 중 그 역할이 시스에 해당하는 등장인물을 조회하는 쿼리문을 작성한다.
SELECT c.*
FROM characters c, roles r
WHERE c.role_id = r.role_id
AND r.role_name = '시스';
▶ 에피소드 4에 출연한 배우들의 실제 이름을 조회하는 쿼리문을 작성한다.
SELECT real_name
FROM casting
WHERE episode_id = 4;
▶ 에피소드 5에 출연한 배우들의 배역 이름과 실제 이름을 조회하는 쿼리문을 작성한다.
SELECT ch.character_name, ca.real_name
FROM characters ch, casting ca
WHERE ch.character_id = ca.character_id
AND ca.episode_id = 5;
▶다음은 에피소드 2에 출연한 모든 배우들의 배역 이름, 실제 이름, 역할을 조회하는 쿼리문이다.
--이 쿼리문을 국제표준 조인문으로 바꾸어 작성한다.
-- inner join 널값을 포함하지 않는다 / outer join 널 값을 포함한다
SELECT ch.character_name, ca.real_name, ro.role_name
FROM casting ca
INNER JOIN characters ch
ON ca.character_id = ch.character_id
LEFT OUTER JOIN roles ro
ON ch.role_id = ro.role_id
WHERE episode_id = 2;
▶ CHARACTERS 테이블에서 배역 이름, 이메일, 이메일 아이디를 조회하는 쿼리문을 작성한다.
--단, 이메일이 id@jedai.com일 경우 이메일 아이디는 id이다.
-- SUBSTR( 문자열, 시작위치, 길이)
-- INSTR( 문자열, 찾을문자값) / INSTR( 문자열, 찾을문자값, 찾기를 시작할 위치, 찾은 결과의 순번)
SELECT character_name, email, SUBSTR(email,1,INSTR(email,'@')-1) AS email_id FROM characters;
SELECT character_name
, (SELECT SUBSTR(email,1,INSTR(email,'@')-1) FROM characters WHERE email = ch.email) AS email_id
FROM characters ch;
▶ 역할이 제다이에 해당하는 배역들의 배역 이름과 그의 마스터 이름을 조회
SELECT ch.character_name
, NVL((SELECT character_name FROM characters WHERE ch.master_id = character_id ), '제다이 중의 제다
이') AS MASTER_NAME
FROM characters ch, roles ro
WHERE ch.role_id = ro.role_id
AND ro.role_name = '제다이';
SELECT ch.character_name
, NVL(ch1.character_name, '제다이 중의 제다이') AS MASTER_NAME
FROM characters ch, roles ro, characters ch1
WHERE ch.role_id = ro.role_id
AND ro.role_name = '제다이'
AND ch.master_id = ch1.character_id(+)
ORDER BY 1;
-- ORDER BY 1 > 1번째 컬럼순으로 오름차순 정렬
▶ 역할이 제다이에 해당하는 배역들의 배역 이름, 이메일, 마스터의 이메일을 조회하고,
-- 결과에 제다이 기사의 이메일이 있으면 제다이 기사의 이메일을,
-- 없으면 마스터의 이메일을 사용하는 EMAILS라는 칼럼까지 추가하여 조회하는 쿼리문을 작성한다.
SELECT ch.character_name
, ch.email
, (SELECT email FROM characters WHERE ch.master_id = character_id) AS MASTER_EMAIL
, NVL(ch.email, (SELECT email FROM characters WHERE ch.master_id = character_id)) AS EMAILS
FROM characters ch, roles ro
WHERE ch.role_id = ro.role_id
AND ro.role_name = '제다이';
SELECT ch.character_name
, ch.email
, c.email AS MASTER_EMAIL
, NVL2(ch.email, ch.email, c.email) AS EMAILS
FROM characters ch, roles ro, characters c
WHERE ch.role_id = ro.role_id
AND ro.role_name = '제다이'
AND ch.master_id = c.character_id(+);
▶ 스타워즈 시리즈 별로 출연한 배우의 수를 파악하고자 한다.
--에피소드 이름과 출연 배우 수를 개봉년도 순으로 조회하는 쿼리문을 작성한다.
SELECT st.episode_name, count(*) AS ACTOR_CNT
FROM star_wars st, casting ca
WHERE st.episode_id = ca.episode_id
GROUP BY st.episode_name, st.open_year
ORDER BY st.open_year;
▶ 스타워즈 전체 시리즈에서 각 배우별 배역 이름, 실제 이름, 출연 횟수를 조회하는데
--출연 횟수가 많은 배역 이름, 실제 이름 순으로 조회하는 쿼리문을 작성한다.
SELECT ch.character_name, ca.real_name, count(*) AS APPEAR_CNT
FROM characters ch, casting ca
WHERE ch.character_id = ca.character_id
GROUP BY ch.character_name, ca.real_name
ORDER BY count(*) desc;
▶ 위의 쿼리문을 참고하여 출연 횟수가 많은 상위 3명의 배역명, 실명, 출연 횟수를 조회하는 쿼리문을 작성한다.
SELECT aa.character_name, aa.real_name, aa.APPEAR_CNT
FROM ( SELECT ch.character_name, ca.real_name, count(*) AS APPEAR_CNT
FROM characters ch, casting ca
WHERE ch.character_id = ca.character_id
GROUP BY ch.character_name, ca.real_name
ORDER BY count(*) desc) aa
WHERE ROWNUM <=3;
▶ 스타워즈 시리즈 별로 어떤 시리즈에 몇 명의 배우가 출연했는지 조회하고자 한다.
--에피소드 시리즈 번호, 에피소드 이름, 출연 배우 수를 조회하는데
--출연 배우 수가 많은 순으로 조회하는 쿼리문을 작성한다.
SELECT st.episode_id, st.episode_name, count(*) AS ACTOR_CNT
FROM star_wars st, casting ca
WHERE st.episode_id = ca.episode_id
GROUP BY st.episode_id, st.episode_name
ORDER BY actor_cnt desc;
댓글