-----
-- Query: SPM_3_2.1
-- Optimizer will use  "star transformation" method to retrieve a 
-- smaller subset more efficiently
----- 
SELECT /*SPM_3_2.1*/
     S.cust_id
	,C.cust_last_name
    ,S.prod_id
	,P.prod_name
    ,S.amount_sold
    ,S.quantity_sold
  FROM 
     sh.sales S
    ,sh.customers C
    ,sh.products P
 WHERE S.cust_id = C.cust_id
   AND S.prod_id = P.prod_id
   AND S.prod_id BETWEEN 100 AND 500
   AND C.cust_last_name LIKE 'Sand%'
 ORDER BY 
     S.cust_id
    ,C.cust_last_name
    ,S.prod_id
    ,P.prod_name
;

-----
-- Query: SPM_3_2.2
-- Optimizer will choose a full table scan of SH.CUSTOMERS because of the larger
-- subset of data that's retrieved
----- 
SELECT /*SPM_3_2.2*/
     S.cust_id
	,C.cust_last_name
    ,S.prod_id
	,P.prod_name
    ,S.amount_sold
    ,S.quantity_sold
  FROM 
     sh.sales S
    ,sh.customers C
    ,sh.products P
 WHERE S.cust_id = C.cust_id
   AND S.prod_id = P.prod_id
   AND S.prod_id BETWEEN 0 AND 99999
   AND C.cust_last_name LIKE 'Zil%'
 ORDER BY 
     S.cust_id
    ,C.cust_last_name
    ,S.prod_id
    ,P.prod_name
;

-----
-- Query: SPM_3_2.3
-- Optimizer will still choose a full table scan of SH.CUSTOMERS but 
-- return an even larger result set
----- 
SELECT /*SPM_3_2.3*/
     S.cust_id
	,C.cust_last_name
    ,S.prod_id
	,P.prod_name
    ,S.amount_sold
    ,S.quantity_sold
  FROM 
     sh.sales S
    ,sh.customers C
    ,sh.products P
 WHERE S.cust_id = C.cust_id
   AND S.prod_id = P.prod_id
   AND S.prod_id BETWEEN 0 AND 99999
   AND C.cust_last_name LIKE 'S%'
 ORDER BY 
     S.cust_id
    ,C.cust_last_name
    ,S.prod_id
    ,P.prod_name
;