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

1. 예제 테이블

set lines 1000
col CUSTOMER_NAME for a30

SELECT * FROM CUSTOMER;
SELECT CUSTOMER_ID, TO_CHAR(USAGE_TIME, 'YYYY-MM-DD HH24:MI:SS') AS USAGE_TIME, USAGE_AMOUNT
FROM TIME_USAGE;
SELECT TO_CHAR(START_TIME, 'YYYY-MM-DD HH24:MI:SS') AS START_TIME,
TO_CHAR(END_TIME, 'YYYY-MM-DD HH24:MI:SS') AS END_TIME,
RATE FROM TIME_SLOT;

-- 고객 테이블
CUSTOMER_ID CUSTOMER_NAME                  DATE_OF_B
----------- ------------------------------ ---------
          1 Alice                          01-JAN-90
          2 Bob                            20-FEB-85
          3 Charlie                        15-MAR-92

-- 시간대별 사용량 테이블
CUSTOMER_ID USAGE_TIME                     USAGE_AMOUNT
----------- ------------------------------ ------------
          1 2024-08-21 08:00:00                       2
          2 2024-08-21 15:00:00                       3
          3 2024-08-21 20:00:00                       1

-- 시간대구간 테이블
START_TIME          END_TIME                  RATE
------------------- ------------------- ----------
2024-08-21 00:00:00 2024-08-21 11:59:59         10
2024-08-21 12:00:00 2024-08-21 17:59:59         15
2024-08-21 18:00:00 2024-08-21 23:59:59         20

예제쿼리.txt

0.00MB

 

 

2. 문제 보기

-- 1번
SELECT A.CUSTOMER_ID, A.CUSTOMER_NAME, SUM(B.USAGE_AMOUNT * C.RATE) AS USAGE_AMOUNT_TOTAL
FROM CUSTOMER A INNER JOIN TIME_USAGE B
ON (A.CUSTOMER_ID = B.CUSTOMER_ID) INNER JOIN TIME_SLOT C
ON (B.USAGE_TIME <= C.START_TIME AND B.USAGE_TIME >= C.END_TIME)
GROUP BY A.CUSTOMER_ID, A.CUSTOMER_NAME
ORDER BY A.CUSTOMER_ID, A.CUSTOMER_NAME;

no rows selected

-- 1번 (부등호 반대)
SELECT A.CUSTOMER_ID, A.CUSTOMER_NAME, SUM(B.USAGE_AMOUNT * C.RATE) AS USAGE_AMOUNT_TOTAL
FROM CUSTOMER A INNER JOIN TIME_USAGE B
ON (A.CUSTOMER_ID = B.CUSTOMER_ID) INNER JOIN TIME_SLOT C
ON (B.USAGE_TIME >= C.START_TIME AND B.USAGE_TIME <= C.END_TIME)
GROUP BY A.CUSTOMER_ID, A.CUSTOMER_NAME
ORDER BY A.CUSTOMER_ID, A.CUSTOMER_NAME;

CUSTOMER_ID CUSTOMER_NAME                  USAGE_AMOUNT_TOTAL
----------- ------------------------------ ------------------
          1 Alice                                          20
          2 Bob                                            45
          3 Charlie                                        20

-- 2번
SELECT A.CUSTOMER_ID, A.CUSTOMER_NAME, SUM(B.USAGE_AMOUNT * C.RATE) AS USAGE_AMOUNT_TOTAL
FROM CUSTOMER A INNER JOIN TIME_USAGE B INNER JOIN TIME_SLOT C
ON (A.CUSTOMER_ID = B.CUSTOMER_ID AND B.USAGE_TIME
BETWEEN C.START_TIME AND C.END_TIME)
GROUP BY A.CUSTOMER_ID, A.CUSTOMER_NAME
ORDER BY A.CUSTOMER_ID, A.CUSTOMER_NAME;

ERROR at line 5:
ORA-00905: missing keyword

// INNER JOIN 구문이 잘못 사용됨
// 각각의 INNER JOIN 구문은 두 테이블 간의 조인 조건을 명시해야 합니다.
// FROM CUSTOMER A INNER JOIN TIME_USAGE B INNER JOIN TIME_SLOT C

-- 3번
SELECT A.CUSTOMER_ID, A.CUSTOMER_NAME, SUM(B.USAGE_AMOUNT * C.RATE) AS USAGE_AMOUNT_TOTAL
FROM CUSTOMER A INNER JOIN TIME_USAGE B
ON (A.CUSTOMER_ID = B.CUSTOMER_ID) INNER JOIN TIME_SLOT C
ON (B.USAGE_TIME BETWEEN C.START_TIME AND C.END_TIME)
GROUP BY A.CUSTOMER_ID, A.CUSTOMER_NAME
ORDER BY A.CUSTOMER_ID, A.CUSTOMER_NAME;

CUSTOMER_ID CUSTOMER_NAME                  USAGE_AMOUNT_TOTAL
----------- ------------------------------ ------------------
          1 Alice                                          20
          2 Bob                                            45
          3 Charlie                                        20

-- 4번
SELECT A.CUSTOMER_ID, A.CUSTOMER_NAME, SUM(B.USAGE_AMOUNT * C.RATE) AS USAGE_AMOUNT_TOTAL
FROM CUSTOMER A INNER JOIN TIME_USAGE B
ON (A.CUSTOMER_ID = B.CUSTOMER_ID) BETWEEN JOIN TIME_SLOT C
GROUP BY A.CUSTOMER_ID, A.CUSTOMER_NAME
ORDER BY A.CUSTOMER_ID, A.CUSTOMER_NAME;

ERROR at line 3:
ORA-00933: SQL command not properly ended