[sqld] 노랭이책(개정판) - 34번 문제(p.51)

1. 예제 테이블

col ACTOR_NAME for a30
col MOVIE_NAME for a30

select * from ACTOR;
select * from MOVIE;
select * from APPEARANCE;

-- 배우 테이블
  ACTOR_ID ACTOR_NAME                     G
---------- ------------------------------ -
         1 Tom Hardy                      M
         2 Scarlett Johansson             F
         3 Brad Pitt                      M
         
-- 영화 테이블
  MOVIE_ID MOVIE_NAME                     PRODUCTION_YEAR
---------- ------------------------------ ---------------
       101 Inception                                 2010
       102 Black Widow                               2021
       103 Fight Club                                1999
       
-- 출연 테이블
  ACTOR_ID   MOVIE_ID     SALARY
---------- ---------- ----------
         1        101       8500
         2        102       8700
         3        103       8900

예제쿼리.txt

0.00MB

 

2. 문제 보기

-- 1번
select APPEARANCE.MOVIE_NAME, MOVIE.ACTOR_NAME, APPEARANCE.SALARY
from ACTOR, MOVIE, APPEARANCE
where SALARY >= 8888
and APPEARANCE.MOVIE_ID = MOVIE.MOVIE_ID
and APPEARANCE.ACTOR_ID = ACTOR.ACTOR_ID;

ERROR at line 1:
ORA-00904: "MOVIE"."ACTOR_NAME": invalid identifier

-- 2번
select MOVIE.MOVIE_NAME, ACTOR.ACTOR_NAME, SALARY
from MOVIE, ACTOR, APPEARANCE
where APPEARANCE.SALARY >= 8888
and APPEARANCE.MOVIE_ID = MOVIE.MOVIE_ID
and MOVIE.MOVIE_ID = ACTOR.ACTOR_ID;

no rows selected

-- 3번
select MOVIE_NAME, ACTOR_NAME, SALARY
from ACTOR, MOVIE, APPEARANCE
where SALARY >= 8888
and MOVIE_ID = MOVIE.MOVIE_ID
and ACTOR_ID = ACTOR.ACTOR_ID;

ERROR at line 5:
ORA-00918: column ambiguously defined

-- 4번
select MOVIE.MOVIE_NAME, ACTOR.ACTOR_NAME, SALARY
from ACTOR, MOVIE, APPEARANCE
where SALARY >= 8888
and APPEARANCE.MOVIE_ID = MOVIE.MOVIE_ID
and APPEARANCE.ACTOR_ID = ACTOR.ACTOR_ID;

MOVIE_NAME                     ACTOR_NAME                         SALARY
------------------------------ ------------------------------ ----------
Fight Club                     Brad Pitt                            8900