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

1. 예제 테이블

col OSNAME for a30
col DEVICENAME for a30
col CUSTOMERNAME for a30

SELECT * FROM OS;
SELECT * FROM Device;
SELECT * FROM Customer;

-- OS 테이블
      OSID OSNAME
---------- ------------------------------
       100 Android
       200 iOS
       300 Bada
	   
-- 단말기 테이블

  DEVICEID DEVICENAME                           OSID
---------- ------------------------------ ----------
      1000 A1000                                 100
      2000 B2000                                 100
      3000 C3000                                 200
      4000 D3000                                 300
	  
-- 고객 테이블

CUSTOMERID CUSTOMERNAME                     DEVICEID
---------- ------------------------------ ----------
     11000 Hong Gil-dong                        1000
     12000 Gang Gam-chan
     13000 Yi Sun-sin
     14000 An Jung-geun                         3000
     15000 Go Gil-dong                          4000
     16000 Lee Dae-ro                           4000

예제쿼리.txt

0.00MB

 

-- 1번
SELECT A.CustomerID, A.CustomerName, B.DeviceID, B.DeviceName, C.OSID, C.OSName
FROM Customer A LEFT OUTER JOIN Device B
ON (A.CustomerID IN (11000, 12000) AND A.DeviceID = B.DeviceID) LEFT OUTER JOIN OS C
ON (B.OSID = C.OSID)
ORDER BY A.CustomerID;

CUSTOMERID CUSTOMERNAME                     DEVICEID DEVICENAME                           OSID OSNAME
---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
     11000 Hong Gil-dong                        1000 A1000                                 100 Android
     12000 Gang Gam-chan
     13000 Yi Sun-sin
     14000 An Jung-geun
     15000 Go Gil-dong
     16000 Lee Dae-ro

-- 일부 쿼리 SELECT ... IN (11000, 12000)까지
SELECT A.CustomerID, A.CustomerName, B.DeviceID, B.DeviceName
FROM Customer A LEFT OUTER JOIN Device B
ON (A.CustomerID IN (11000, 12000));

CUSTOMERID CUSTOMERNAME                     DEVICEID DEVICENAME
---------- ------------------------------ ---------- ------------------------------
     11000 Hong Gil-dong                        1000 A1000
     11000 Hong Gil-dong                        2000 B2000
     11000 Hong Gil-dong                        3000 C3000
     11000 Hong Gil-dong                        4000 D3000
     12000 Gang Gam-chan                        1000 A1000
     12000 Gang Gam-chan                        2000 B2000
     12000 Gang Gam-chan                        3000 C3000
     12000 Gang Gam-chan                        4000 D3000
     13000 Yi Sun-sin
     14000 An Jung-geun
     15000 Go Gil-dong
     16000 Lee Dae-ro

-- 일부 쿼리 SELECT ... A.단말기ID = B.단말기까지
SELECT A.CustomerID, A.CustomerName, B.DeviceID, B.DeviceName
FROM Customer A LEFT OUTER JOIN Device B
ON (A.CustomerID IN (11000, 12000)) AND A.DeviceID = B.DeviceID;

CUSTOMERID CUSTOMERNAME                     DEVICEID DEVICENAME
---------- ------------------------------ ---------- ------------------------------
     11000 Hong Gil-dong                        1000 A1000
     12000 Gang Gam-chan
     13000 Yi Sun-sin
     14000 An Jung-geun
     15000 Go Gil-dong
     16000 Lee Dae-ro