-----
-- Query: SPM_3_1.sql
-----
VARIABLE cust_last_name VARCHAR2(30);
VARIABLE beg_prod_id NUMBER;
VARIABLE end_prod_id NUMBER;
BEGIN
    :cust_last_name := 'Sand%';
    :beg_prod_id := 100;
    :end_prod_id := 500;
END;
/
PRINT :cust_last_name :beg_prod_id :end_prod_id
SELECT /*SPM_3.1*/
     S.cust_id
	,C.cust_last_name
    ,S.prod_id
	,P.prod_name
    ,SUM(S.amount_sold)
    ,SUM(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 :beg_prod_id AND :end_prod_id
   AND C.cust_last_name LIKE :cust_last_name
 GROUP BY 
     S.cust_id
    ,C.cust_last_name
    ,S.prod_id
    ,P.prod_name
 ORDER BY 
     S.cust_id
    ,C.cust_last_name
    ,S.prod_id
    ,P.prod_name
;