/*
|| Script:  SPM_2_1.sql
|| Purpose: Generate several SQL statements that perform Data
||          Warehouse query loads on an Oracle 10gR2 database
||          for capture into a SQL Tuning Set (STS) that will be 
||          transferred to an Oracle 11g database to demonstrate
||          SQL Plan Baseline seeding.
*/

-----
-- Query:   SPM_2_1.1
-- Purpose: Summarize sales activity within Country and Promotion
-----    
SELECT /*SPM_2_1.1*/
     CTY.country_total_id
    ,PR.promo_total_id
    ,COUNT(S.amount_sold)
    ,SUM(S.amount_sold)
    ,SUM(S.quantity_sold)
  FROM 
     sh.sales S
    ,sh.customers C
    ,sh.countries CTY
    ,sh.promotions PR
 WHERE S.cust_id = C.cust_id
   AND C.country_id = CTY.country_id 
   AND S.promo_id = PR.promo_id 
 GROUP BY 
     CTY.country_total_id
    ,PR.promo_total_id 
;

-----
-- Query:   SPM_2_1.2
-- Purpose: Summarize sales activity within Country and Promotion
-----    
SELECT /*SPM_2_1.2*/
     CTY.country_id
    ,CTY.country_subregion_id
    ,CTY.country_region_id
    ,CTY.country_total_id
    ,PR.promo_total_id
    ,COUNT(S.amount_sold)
    ,SUM(S.amount_sold)
    ,SUM(S.quantity_sold)
  FROM 
     sh.sales S
    ,sh.customers C
    ,sh.countries CTY
    ,sh.promotions PR 
 WHERE S.cust_id = C.cust_id 
   AND C.country_id = CTY.country_id 
   AND S.promo_id = PR.promo_id 
GROUP BY 
     CTY.country_id
    ,CTY.country_subregion_id
    ,CTY.country_region_id
    ,CTY.country_total_id
    ,PR.promo_total_id
;

-----
-- Query:   SPM_2_1.3
-- Purpose: Summarize sales activity within Country, Product, 
--          Channel and Promotion
-----    
SELECT /*SPM_2_1.3*/
     CTY.country_total_id
    ,P.prod_id
    ,P.prod_subcategory_id
    ,P.prod_category_id
    ,P.prod_total_id
    ,CH.channel_id    
    ,CH.channel_class_id
    ,CH.channel_total_id
    ,PR.promo_total_id
    ,COUNT(S.amount_sold)
    ,SUM(S.amount_sold)
    ,SUM(S.quantity_sold)
  FROM 
     sh.sales S
    ,sh.customers C
    ,sh.countries CTY
    ,sh.products P
    ,sh.channels CH
    ,sh.promotions PR
 WHERE S.cust_id = C.cust_id 
   AND C.country_id = CTY.country_id 
   AND S.prod_id = P.prod_id 
   AND S.channel_id = CH.channel_id 
   AND S.promo_id = PR.promo_id 
 GROUP BY 
     CTY.country_total_id
    ,P.prod_id
    ,P.prod_subcategory_id
    ,P.prod_category_id
    ,P.prod_total_id
    ,CH.channel_id
    ,CH.channel_class_id
    ,CH.channel_total_id
    ,PR.promo_total_id 
;

-----
-- Query:   SPM_2_1.4
-- Purpose: Summarize sales activity within Country, Product, 
--          Channel and Promotion
-----    
SELECT /*SPM_2_1.4*/ 
     CTY.country_total_id
    ,P.prod_category_id
    ,P.prod_total_id
    ,CH.channel_id
    ,CH.channel_class_id
    ,CH.channel_total_id
    ,PR.promo_total_id 
    ,COUNT(S.amount_sold)
    ,SUM(S.amount_sold)
    ,SUM(S.quantity_sold)
  FROM 
     sh.sales S
    ,sh.customers C
    ,sh.countries CTY
    ,sh.products P
    ,sh.channels CH
    ,sh.promotions PR
 WHERE S.cust_id = C.cust_id 
   AND C.country_id = CTY.country_id 
   AND S.prod_id = P.prod_id 
   AND S.channel_id = CH.channel_id 
   AND S.promo_id = PR.promo_id
 GROUP BY 
     CTY.country_total_id
    ,P.prod_category_id
    ,P.prod_total_id
    ,CH.channel_id
    ,CH.channel_class_id
    ,CH.channel_total_id
    ,PR.promo_total_id
;

-----
-- Query:   SPM_2_1.5
-- Purpose: Summarize sales activity within Country, Product, 
--          Channel and Promotion
-----    
SELECT /*SPM_2_1.5*/
     CTY.country_id
    ,CTY.country_subregion_id
    ,CTY.country_region_id
    ,CTY.country_total_id
    ,P.prod_id
    ,P.prod_subcategory_id
    ,P.prod_category_id
    ,P.prod_total_id
    ,CH.channel_id
    ,CH.channel_class_id
    ,CH.channel_total_id
    ,PR.promo_total_id
    ,COUNT(S.amount_sold)
    ,SUM(S.amount_sold)
    ,SUM(S.quantity_sold)
  FROM 
     sh.sales S
    ,sh.customers C
    ,sh.countries CTY
    ,sh.products P
    ,sh.channels CH
    ,sh.promotions PR
 WHERE S.cust_id = C.cust_id 
   AND C.country_id = CTY.country_id 
   AND S.prod_id = P.prod_id 
   AND S.channel_id = CH.channel_id 
   AND S.promo_id = PR.promo_id 
 GROUP BY 
     CTY.country_id
    ,CTY.country_subregion_id
    ,CTY.country_region_id
    ,CTY.country_total_id
    ,P.prod_id
    ,P.prod_subcategory_id
    ,P.prod_category_id
    ,P.prod_total_id
    ,CH.channel_id
    ,CH.channel_class_id
    ,CH.channel_total_id
    ,PR.promo_total_id
;