SQL Assignment 3 Answers.txt

1) SELECT 
     c.CUSTOMER_ID, 
     c.CUST_FIRST_NAME, 
     c.CUST_LAST_NAME, 
     SUM(oi.QUANTITY * oi.UNIT_PRICE) AS TOTAL_SPENT
  FROM  DEMO_CUSTOMERS c
  JOIN DEMO_ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID
  JOIN DEMO_ORDER_ITEMS oi ON o.ORDER_ID = oi.ORDER_ID
  GROUP BY c.CUSTOMER_ID, c.CUST_FIRST_NAME, c.CUST_LAST_NAME
  ORDER BY TOTAL_SPENT DESC
  FETCH FIRST 3 ROWS ONLY;


2) SELECT 
      c.CUSTOMER_ID, 
      c.CUST_FIRST_NAME, 
      c.CUST_LAST_NAME, 
      COUNT(o.ORDER_ID) AS ORDER_COUNT
   FROM DEMO_CUSTOMERS c
   JOIN DEMO_ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID
   GROUP BY  c.CUSTOMER_ID, c.CUST_FIRST_NAME, c.CUST_LAST_NAME
   ORDER BY ORDER_COUNT DESC
   FETCH FIRST 3 ROWS ONLY;


3) SELECT 
      p.PRODUCT_ID, 
      p.PRODUCT_NAME, 
      SUM(oi.QUANTITY) AS TOTAL_QUANTITY_SOLD
   FROM DEMO_PRODUCTS p
   JOIN DEMO_ORDER_ITEMS oi ON p.PRODUCT_ID = oi.PRODUCT_ID
   GROUP BY  p.PRODUCT_ID, p.PRODUCT_NAME
   ORDER BY TOTAL_QUANTITY_SOLD DESC
   FETCH FIRST 3 ROWS ONLY;


4) SELECT 
      c.CUST_LAST_NAME, 
      p.PRODUCT_NAME, 
      SUM(oi.QUANTITY) AS TOTAL_QUANTITY
   FROM DEMO_CUSTOMERS c
   JOIN DEMO_ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID
   JOIN DEMO_ORDER_ITEMS oi ON o.ORDER_ID = oi.ORDER_ID
   JOIN DEMO_PRODUCTS p ON oi.PRODUCT_ID = p.PRODUCT_ID
   GROUP BY c.CUST_LAST_NAME, p.PRODUCT_NAME
   ORDER BY c.CUST_LAST_NAME, p.PRODUCT_NAME;


5) SELECT 
      CUST_LAST_NAME,
      MAX(CASE WHEN PRODUCT_NAME = 'Product A' THEN TOTAL_QUANTITY ELSE 0 END) AS Product_A,
      MAX(CASE WHEN PRODUCT_NAME = 'Product B' THEN TOTAL_QUANTITY ELSE 0 END) AS Product_B,
      MAX(CASE WHEN PRODUCT_NAME = 'Product C' THEN TOTAL_QUANTITY ELSE 0 END) AS Product_C
   FROM (
       SELECT 
          c.CUST_LAST_NAME, 
          p.PRODUCT_NAME, 
          SUM(oi.QUANTITY) AS TOTAL_QUANTITY
       FROM DEMO_CUSTOMERS c
       JOIN DEMO_ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID
       JOIN DEMO_ORDER_ITEMS oi ON o.ORDER_ID = oi.ORDER_ID
       JOIN DEMO_PRODUCTS p ON oi.PRODUCT_ID = p.PRODUCT_ID
       GROUP BY c.CUST_LAST_NAME, p.PRODUCT_NAME
   ) pivoted_data
   GROUP BY CUST_LAST_NAME;


6) SELECT 
      EXTRACT(YEAR FROM o.ORDER_TIMESTAMP) AS ORDER_YEAR, 
      COUNT(o.ORDER_ID) AS TOTAL_ORDERS
   FROM DEMO_ORDERS o
   GROUP BY EXTRACT(YEAR FROM o.ORDER_TIMESTAMP)
   ORDER BY TOTAL_ORDERS DESC
   FETCH FIRST 1 ROW ONLY;


7) 


8) SELECT 
      p.CATEGORY, 
      SUM(oi.QUANTITY) AS TOTAL_QUANTITY_SOLD
   FROM DEMO_PRODUCTS p
   JOIN DEMO_ORDER_ITEMS oi ON p.PRODUCT_ID = oi.PRODUCT_ID
   GROUP BY p.CATEGORY
   ORDER BY TOTAL_QUANTITY_SOLD DESC
   FETCH FIRST 1 ROW ONLY;


9) SELECT 
      p.CATEGORY, 
      SUM(oi.QUANTITY) AS TOTAL_QUANTITY_SOLD
   FROM DEMO_PRODUCTS p
   JOIN DEMO_ORDER_ITEMS oi ON p.PRODUCT_ID = oi.PRODUCT_ID
   GROUP BY p.CATEGORY
   ORDER BY TOTAL_QUANTITY_SOLD DESC
   OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;


10) SELECT 
       c.CUST_FIRST_NAME, 
       c.CUST_LAST_NAME, 
       p.PRODUCT_NAME, 
       SUM(oi.QUANTITY) AS TOTAL_QUANTITY
    FROM DEMO_CUSTOMERS c
    JOIN DEMO_ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID
    JOIN DEMO_ORDER_ITEMS oi ON o.ORDER_ID = oi.ORDER_ID
    JOIN DEMO_PRODUCTS p ON oi.PRODUCT_ID = p.PRODUCT_ID
    GROUP BY 
        ROLLUP (c.CUST_FIRST_NAME, c.CUST_LAST_NAME, p.PRODUCT_NAME)
    ORDER BY c.CUST_FIRST_NAME, c.CUST_LAST_NAME, p.PRODUCT_NAME;