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

1. 예제 테이블

col SERVICENAME for a30
col SERVICEURL for a30

SELECT * FROM Service;
SELECT * FROM ServiceUsage;

 SERVICEID SERVICENAME                    SERVICEURL
---------- ------------------------------ ------------------------------
         1 Streaming                      http://streaming.com
         2 Gaming                         http://gaming.com
         3 E-commerce                     http://ecommerce.com

 SERVICEID   MEMBERID USAGETIMESTAMP
---------- ---------- ------------------------------
         1        101 2024-08-01 10:00:00
         2        102 2024-08-02 11:00:00

SELECT A.ServiceID, B.ServiceName, B.ServiceURL
FROM (SELECT ServiceID
		FROM Service
		INTERSECT
		SELECT ServiceID
		FROM ServiceUsage) A, Service B
WHERE A.ServiceID = B.ServiceID;

 SERVICEID SERVICENAME                    SERVICEURL
---------- ------------------------------ ------------------------------
         1 Streaming                      http://streaming.com
         2 Gaming                         http://gaming.com

예제쿼리.txt

0.00MB

 

2. 문제 보기

-- 1번
SELECT B.ServiceID, A.ServiceName, A.ServiceURL
FROM Service A, ServiceUsage B
WHERE A.ServiceID = B.ServiceID;

 SERVICEID SERVICENAME                    SERVICEURL
---------- ------------------------------ ------------------------------
         1 Streaming                      http://streaming.com
         1 Streaming                      http://streaming.com
         2 Gaming                         http://gaming.com
         
-- GROUP BY를 수행하면 동일한 결과
SELECT B.ServiceID, A.ServiceName, A.ServiceURL
FROM Service A, ServiceUsage B
WHERE A.ServiceID = B.ServiceID
GROUP BY B.ServiceID, A.ServiceName, A.ServiceURL; -- GROUP BY절 추가

 SERVICEID SERVICENAME                    SERVICEURL
---------- ------------------------------ ------------------------------
         2 Gaming                         http://gaming.com
         1 Streaming                      http://streaming.com

-- 2번
SELECT X.ServiceID, X.ServiceName, X.ServiceURL
FROM
Service X
WHERE NOT EXISTS (SELECT 1
                  FROM (SELECT ServiceID
                        FROM Service
                        MINUS
                        SELECT ServiceID
                        FROM ServiceUsage) Y
                  WHERE X.ServiceID = Y.ServiceID);
				  
 SERVICEID SERVICENAME                    SERVICEURL
---------- ------------------------------ ------------------------------
         1 Streaming                      http://streaming.com
         2 Gaming                         http://gaming.com

-- 3번
SELECT B.ServiceID, A.ServiceName, A.ServiceURL
FROM Service A LEFT OUTER JOIN ServiceUsage B
ON (A.ServiceID = B.ServiceID)
WHERE B.ServiceID IS NULL
GROUP BY B.ServiceID, A.ServiceName, A.ServiceURL;

 SERVICEID SERVICENAME                    SERVICEURL
---------- ------------------------------ ------------------------------
           E-commerce                     http://ecommerce.com

-- B.서비스ID IS NOT NULL로 변경
SELECT B.ServiceID, A.ServiceName, A.ServiceURL
FROM Service A LEFT OUTER JOIN ServiceUsage B
ON (A.ServiceID = B.ServiceID)
WHERE B.ServiceID IS NOT NULL -- NOT NULL로 변경
GROUP BY B.ServiceID, A.ServiceName, A.ServiceURL;

 SERVICEID SERVICENAME                    SERVICEURL
---------- ------------------------------ ------------------------------
         2 Gaming                         http://gaming.com
         1 Streaming                      http://streaming.com

-- 4번
SELECT A.ServiceID, A.ServiceName, A.ServiceURL
FROM Service A
WHERE ServiceID IN (SELECT ServiceID
                        FROM ServiceUsage
                        MINUS
                        SELECT ServiceID
                        FROM Service);

no rows selected

-- 서비스와 서비스이용 테이블의 순서를 변경 / IN절을 NOT IN으로 변경
SELECT A.ServiceID, A.ServiceName, A.ServiceURL
FROM Service A
WHERE ServiceID NOT IN (SELECT ServiceID -- NOT IN으로 변경
                        FROM Service -- 순서변경(ServiceUsage -> Service)
                        MINUS
                        SELECT ServiceID
                        FROM ServiceUsage); -- 순서변경(Service -> ServiceUsage)
						
 SERVICEID SERVICENAME                    SERVICEURL
---------- ------------------------------ ------------------------------
         1 Streaming                      http://streaming.com
         2 Gaming                         http://gaming.com