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

1. 예제 테이블

col ADMEDIANAME for a30
col ADNAME for a30

-- 광고
SELECT * FROM Advertisement;

ADID       ADNAME
---------- ------------------------------
AD001      Ad Name 1
AD002      Ad Name 2
AD003      Ad Name 3

-- 광고게시
SELECT * FROM AdPosting;

ADPOSTINGI ADID       ADMEDIAID  ADSTARTDA ADENDDATE
---------- ---------- ---------- --------- ---------
POST001    AD001      MEDIA01    01-JAN-24 15-JAN-24
POST002    AD002      MEDIA01    05-JAN-24 20-JAN-24
POST003    AD003      MEDIA02    01-FEB-24 15-FEB-24

-- 광고매체
SELECT * FROM AdMedia;

ADMEDIAID  ADMEDIANAME
---------- ------------------------------
MEDIA01    Media Name 1
MEDIA02    Media Name 2

예제쿼리.txt

0.00MB

 

2. 문제 보기

-- 1번
SELECT C.AdMediaName, B.AdName, A.AdStartDate
FROM AdPosting A, Advertisement B, AdMedia C,
(
	SELECT D.AdMediaID, MIN(D.AdStartDate) AS AdStartDate
	FROM AdPosting D
	WHERE D.AdMediaID = C.AdMediaID
	GROUP BY D.AdMediaID
) D
WHERE A.AdStartDate = D.AdStartDate
AND A.AdMediaID = D.AdMediaID
AND A.AdID = B.AdID
AND A.AdMediaID = C.AdMediaID
ORDER BY C.AdMediaName;

ERROR at line 6:
ORA-00904: "C"."ADMEDIAID": invalid identifier

 

일반적으로 말하는 인라인뷰, 즉 외부 쿼리의 FROM 절에 포함된 서브쿼리 내부에서는 서브쿼리에서 직접 참조한 테이블의 컬럼만 참조할 수 있습니다. C는 외부 쿼리 테이블이라서, 실행하면 '유효하지 않은 식별자'라는 에러가 발생합니다.

 

-- 2번
SELECT C.AdMediaName, B.AdName, A.AdStartDate
FROM AdPosting A, Advertisement B, AdMedia C,
(
	SELECT AdMediaID, MIN(AdStartDate) AS AdStartDate
	FROM AdPosting
	GROUP BY AdMediaID
) D
WHERE A.AdStartDate = D.AdStartDate
AND A.AdMediaID = D.AdMediaID
AND A.AdID = B.AdID
AND A.AdMediaID = C.AdMediaID
ORDER BY C.AdMediaName;

ADMEDIANAME                    ADNAME                         ADSTARTDA
------------------------------ ------------------------------ ---------
Media Name 1                   Ad Name 1                      01-JAN-24
Media Name 2                   Ad Name 3                      01-FEB-24

 

-- 3번
SELECT C.AdMediaName, B.AdName, A.AdStartDate
FROM AdPosting A, Advertisement B, AdMedia C,
(
	SELECT MIN(AdMediaID) AS AdMediaID, MIN(AdStartDate) AS AdStartDate
	FROM AdPosting
	GROUP BY AdID
) D
WHERE A.AdStartDate = D.AdStartDate
AND A.AdMediaID = D.AdMediaID
AND A.AdID = B.AdID
AND A.AdMediaID = C.AdMediaID
ORDER BY C.AdMediaName;

ADMEDIANAME                    ADNAME                         ADSTARTDA
------------------------------ ------------------------------ ---------
Media Name 1                   Ad Name 1                      01-JAN-24
Media Name 1                   Ad Name 2                      05-JAN-24
Media Name 2                   Ad Name 3                      01-FEB-24

 

-- 4번
SELECT C.AdMediaName, B.AdName, A.AdStartDate
FROM AdPosting A, Advertisement B, AdMedia C,
(
	SELECT MIN(AdMediaID) AS AdMediaID, MIN(AdStartDate) AS AdStartDate
	FROM AdPosting
) D
WHERE A.AdStartDate = D.AdStartDate
AND A.AdMediaID = D.AdMediaID
AND A.AdID = B.AdID
AND A.AdMediaID = C.AdMediaID
ORDER BY C.AdMediaName;

ADMEDIANAME                    ADNAME                         ADSTARTDA
------------------------------ ------------------------------ ---------
Media Name 1                   Ad Name 1                      01-JAN-24