submission_Keerthiga_Sekar (1).sql

/*

-----------------------------------------------------------------------------------------------------------------------------------
                                               Guidelines
-----------------------------------------------------------------------------------------------------------------------------------

The provided document is a guide for the project. Follow the instructions and take the necessary steps to finish
the project in the SQL file			

-----------------------------------------------------------------------------------------------------------------------------------

                                                         Queries
                                               
-----------------------------------------------------------------------------------------------------------------------------------*/

-- 1. WRITE A QUERY TO DISPLAY CUSTOMER FULL NAME WITH THEIR TITLE (MR/MS), BOTH FIRST NAME AND LAST NAME ARE IN UPPER CASE WITH 
-- CUSTOMER EMAIL ID, CUSTOMER CREATIONDATE AND DISPLAY CUSTOMER’S CATEGORY AFTER APPLYING BELOW CATEGORIZATION RULES:
	-- i.IF CUSTOMER CREATION DATE YEAR <2005 THEN CATEGORY A
    -- ii.IF CUSTOMER CREATION DATE YEAR >=2005 AND <2011 THEN CATEGORY B
    -- iii.IF CUSTOMER CREATION DATE YEAR>= 2011 THEN CATEGORY C
    
    -- HINT: USE CASE STATEMENT, NO PERMANENT CHANGE IN TABLE REQUIRED. [NOTE: TABLES TO BE USED -ONLINE_CUSTOMER TABLE]
SELECT
	CONCAT(
	CASE
		WHEN CUSTOMER_GENDER='M' THEN 'Mr. '
		WHEN CUSTOMER_GENDER='F' THEN 'Ms. '
	END,
    UPPER(CUSTOMER_FNAME),' ',UPPER(CUSTOMER_LNAME)) FULL_NAME,
    CUSTOMER_EMAIL,CUSTOMER_CREATION_DATE,
	CASE 
		WHEN YEAR(CUSTOMER_CREATION_DATE) < 2005 THEN "CATEGORY A"
		WHEN YEAR(CUSTOMER_CREATION_DATE) >= 2005 AND YEAR(CUSTOMER_CREATION_DATE) < 2011 THEN "CATEGORY B"
		WHEN YEAR(CUSTOMER_CREATION_DATE) >= 2011 THEN "CATEGORY C"
	END AS CATEGORY 
FROM online_customer;



-- 2. WRITE A QUERY TO DISPLAY THE FOLLOWING INFORMATION FOR THE PRODUCTS, WHICH HAVE NOT BEEN SOLD:  PRODUCT_ID, PRODUCT_DESC, 
-- PRODUCT_QUANTITY_AVAIL, PRODUCT_PRICE,INVENTORY VALUES(PRODUCT_QUANTITY_AVAIL*PRODUCT_PRICE), NEW_PRICE AFTER APPLYING DISCOUNT 
-- AS PER BELOW CRITERIA. SORT THE OUTPUT WITH RESPECT TO DECREASING VALUE OF INVENTORY_VALUE.
	-- i.IF PRODUCT PRICE > 20,000 THEN APPLY 20% DISCOUNT
    -- ii.IF PRODUCT PRICE > 10,000 THEN APPLY 15% DISCOUNT
    -- iii.IF PRODUCT PRICE =< 10,000 THEN APPLY 10% DISCOUNT
    
    -- HINT: USE CASE STATEMENT, NO PERMANENT CHANGE IN TABLE REQUIRED. [NOTE: TABLES TO BE USED -PRODUCT, ORDER_ITEMS TABLE] 
SELECT 
	p.PRODUCT_ID,
	p.PRODUCT_DESC,
    p.PRODUCT_QUANTITY_AVAIL,
    p.PRODUCT_PRICE,
    (p.PRODUCT_QUANTITY_AVAIL * p.PRODUCT_PRICE) INVENTORY_VALUES,
    CASE 
		WHEN p.PRODUCT_PRICE >20000 THEN p.PRODUCT_PRICE*0.8
		WHEN p.PRODUCT_PRICE >10000 THEN p.PRODUCT_PRICE*0.85
		ELSE p.PRODUCT_PRICE*0.9
	END AS NEW_PRICE
FROM product p
LEFT JOIN order_items oi USING (PRODUCT_ID)
WHERE oi.ORDER_ID IS NULL
ORDER BY INVENTORY_VALUES DESC;
        




-- 3. WRITE A QUERY TO DISPLAY PRODUCT_CLASS_CODE, PRODUCT_CLASS_DESCRIPTION, COUNT OF PRODUCT TYPE IN EACH PRODUCT CLASS, 
-- INVENTORY VALUE (P.PRODUCT_QUANTITY_AVAIL*P.PRODUCT_PRICE). INFORMATION SHOULD BE DISPLAYED FOR ONLY THOSE PRODUCT_CLASS_CODE 
-- WHICH HAVE MORE THAN 1,00,000 INVENTORY VALUE. SORT THE OUTPUT WITH RESPECT TO DECREASING VALUE OF INVENTORY_VALUE.
	-- [NOTE: TABLES TO BE USED -PRODUCT, PRODUCT_CLASS]

SELECT 
    pc.product_class_code,
    pc.PRODUCT_CLASS_DESC,
    COUNT(pc.PRODUCT_CLASS_DESC) AS product_type_count,
    SUM(p.product_quantity_avail * p.product_price) AS inventory_value
FROM product p
JOIN product_class pc ON p.product_class_code = pc.product_class_code
GROUP BY pc.product_class_code, pc.PRODUCT_CLASS_DESC
HAVING inventory_value > 100000
ORDER BY inventory_value DESC;  


-- 4. WRITE A QUERY TO DISPLAY CUSTOMER_ID, FULL NAME, CUSTOMER_EMAIL, CUSTOMER_PHONE AND COUNTRY OF CUSTOMERS WHO HAVE CANCELLED 
-- ALL THE ORDERS PLACED BY THEM(USE SUB-QUERY)
	-- [NOTE: TABLES TO BE USED - ONLINE_CUSTOMER, ADDRESSS, ORDER_HEADER]
    
SELECT CUSTOMER_ID , FULL_NAME , CUSTOMER_EMAIL , CUSTOMER_PHONE , COUNTRY FROM
( SELECT OH.CUSTOMER_ID ,
CONCAT ( CUSTOMER_FNAME ,' ' , CUSTOMER_LNAME ) AS FULL_NAME ,
OC.CUSTOMER_EMAIL ,
OC.CUSTOMER_PHONE ,
A.COUNTRY
FROM ORDER_HEADER OH
LEFT JOIN ONLINE_CUSTOMER OC ON OH.CUSTOMER_ID = OC.CUSTOMER_ID
LEFT JOIN ADDRESS A ON OC.ADDRESS_ID = A.ADDRESS_ID
WHERE OH.ORDER_STATUS = 'CANCELLED' ) s
WHERE CUSTOMER_ID NOT IN ( SELECT OH.CUSTOMER_ID FROM
ORDER_HEADER OH WHERE OH.ORDER_STATUS != 'CANCELLED' );


        
-- 5. WRITE A QUERY TO DISPLAY SHIPPER NAME, CITY TO WHICH IT IS CATERING, NUMBER OF CUSTOMER CATERED BY THE SHIPPER IN THE CITY AND 
-- NUMBER OF CONSIGNMENTS DELIVERED TO THAT CITY FOR SHIPPER DHL(9 ROWS)
	-- [NOTE: TABLES TO BE USED -SHIPPER, ONLINE_CUSTOMER, ADDRESSS, ORDER_HEADER]
SELECT 
    s.SHIPPER_NAME,
    a.CITY,
    COUNT(DISTINCT oc.CUSTOMER_ID) AS NUMBER_OF_CUSTOMERS,
    COUNT(oh.ORDER_ID) AS NUMBER_OF_CONSIGNMENTS
FROM SHIPPER s
JOIN ORDER_HEADER oh ON s.SHIPPER_ID = oh.SHIPPER_ID
JOIN ONLINE_CUSTOMER oc ON oh.CUSTOMER_ID = oc.CUSTOMER_ID
JOIN ADDRESS a ON oc.ADDRESS_ID = a.ADDRESS_ID
WHERE s.SHIPPER_NAME = 'DHL'
GROUP BY s.SHIPPER_NAME, a.CITY
HAVING COUNT(oh.ORDER_ID) > 0;



-- 6. WRITE A QUERY TO DISPLAY CUSTOMER ID, CUSTOMER FULL NAME, TOTAL QUANTITY AND TOTAL VALUE (QUANTITY*PRICE) SHIPPED WHERE MODE 
-- OF PAYMENT IS CASH AND CUSTOMER LAST NAME STARTS WITH 'G'
	-- [NOTE: TABLES TO BE USED -ONLINE_CUSTOMER, ORDER_ITEMS, PRODUCT, ORDER_HEADER]
SELECT 
    oc.customer_id,
    CONCAT(oc.customer_fname, ' ', oc.customer_lname) AS full_name,
    SUM(oi.product_quantity) AS total_quantity,
    SUM(oi.product_quantity * p.product_price) AS total_value
FROM ONLINE_CUSTOMER oc
JOIN ORDER_HEADER oh ON oc.customer_id = oh.customer_id
JOIN ORDER_ITEMS oi ON oh.order_id = oi.order_id
JOIN PRODUCT p ON oi.product_id = p.product_id
WHERE oh.payment_mode = 'CASH'AND oc.customer_lname LIKE 'G%'
GROUP BY oc.customer_id, full_name;


    
-- 7. WRITE A QUERY TO DISPLAY ORDER_ID AND VOLUME OF BIGGEST ORDER (IN TERMS OF VOLUME) THAT CAN FIT IN CARTON ID 10  
	-- [NOTE: TABLES TO BE USED -CARTON, ORDER_ITEMS, PRODUCT]
   
SELECT O.ORDER_ID, O.TOTAL_VOLUME
FROM (
    SELECT OI.ORDER_ID, SUM(P.LEN * P.WIDTH * P.HEIGHT * OI.PRODUCT_QUANTITY) AS TOTAL_VOLUME
    FROM ORDER_ITEMS OI
    JOIN PRODUCT P ON OI.PRODUCT_ID = P.PRODUCT_ID
    GROUP BY OI.ORDER_ID
) O
WHERE O.TOTAL_VOLUME <= (
    SELECT C.LEN*C.WIDTH*C.HEIGHT
    FROM CARTON C
    WHERE C.CARTON_ID = 10
)
ORDER BY O.TOTAL_VOLUME DESC
LIMIT 1;




-- 8. WRITE A QUERY TO DISPLAY PRODUCT_ID, PRODUCT_DESC, PRODUCT_QUANTITY_AVAIL, QUANTITY SOLD, AND SHOW INVENTORY STATUS OF 
-- PRODUCTS AS BELOW AS PER BELOW CONDITION:
	-- A.FOR ELECTRONICS AND COMPUTER CATEGORIES, 
		-- i.IF SALES TILL DATE IS ZERO THEN SHOW 'NO SALES IN PAST, GIVE DISCOUNT TO REDUCE INVENTORY',
        -- ii.IF INVENTORY QUANTITY IS LESS THAN 10% OF QUANTITY SOLD, SHOW 'LOW INVENTORY, NEED TO ADD INVENTORY', 
        -- iii.IF INVENTORY QUANTITY IS LESS THAN 50% OF QUANTITY SOLD, SHOW 'MEDIUM INVENTORY, NEED TO ADD SOME INVENTORY', 
        -- iv.IF INVENTORY QUANTITY IS MORE OR EQUAL TO 50% OF QUANTITY SOLD, SHOW 'SUFFICIENT INVENTORY'
	-- B.FOR MOBILES AND WATCHES CATEGORIES, 
		-- i.IF SALES TILL DATE IS ZERO THEN SHOW 'NO SALES IN PAST, GIVE DISCOUNT TO REDUCE INVENTORY', 
        -- ii.IF INVENTORY QUANTITY IS LESS THAN 20% OF QUANTITY SOLD, SHOW 'LOW INVENTORY, NEED TO ADD INVENTORY',  
        -- iii.IF INVENTORY QUANTITY IS LESS THAN 60% OF QUANTITY SOLD, SHOW 'MEDIUM INVENTORY, NEED TO ADD SOME INVENTORY', 
        -- iv.IF INVENTORY QUANTITY IS MORE OR EQUAL TO 60% OF QUANTITY SOLD, SHOW 'SUFFICIENT INVENTORY'
	-- C.REST OF THE CATEGORIES, 
		-- i.IF SALES TILL DATE IS ZERO THEN SHOW 'NO SALES IN PAST, GIVE DISCOUNT TO REDUCE INVENTORY', 
        -- ii.IF INVENTORY QUANTITY IS LESS THAN 30% OF QUANTITY SOLD, SHOW 'LOW INVENTORY, NEED TO ADD INVENTORY',  
        -- iii.IF INVENTORY QUANTITY IS LESS THAN 70% OF QUANTITY SOLD, SHOW 'MEDIUM INVENTORY, NEED TO ADD SOME INVENTORY', 
        -- iv. IF INVENTORY QUANTITY IS MORE OR EQUAL TO 70% OF QUANTITY SOLD, SHOW 'SUFFICIENT INVENTORY'
        
			-- [NOTE: TABLES TO BE USED -PRODUCT, PRODUCT_CLASS, ORDER_ITEMS] (USE SUB-QUERY)
SELECT 
    p.PRODUCT_ID,
    p.PRODUCT_DESC,
    pc.PRODUCT_CLASS_DESC,
    p.PRODUCT_QUANTITY_AVAIL,
    COALESCE(SUM(oi.PRODUCT_QUANTITY), 0) AS QUANTITY_SOLD,
CASE 
        WHEN pc.PRODUCT_CLASS_DESC IN ('ELECTRONICS', 'COMPUTER') THEN
            CASE 
                WHEN COALESCE(SUM(oi.PRODUCT_QUANTITY), 0) = 0 THEN 'NO SALES IN PAST, GIVE DISCOUNT TO REDUCE INVENTORY'
                WHEN p.PRODUCT_QUANTITY_AVAIL < 0.1 * COALESCE(SUM(oi.PRODUCT_QUANTITY), 0) THEN 'LOW INVENTORY, NEED TO ADD INVENTORY'
                WHEN p.PRODUCT_QUANTITY_AVAIL < 0.5 * COALESCE(SUM(oi.PRODUCT_QUANTITY), 0) THEN 'MEDIUM INVENTORY, NEED TO ADD SOME INVENTORY'
                ELSE 'SUFFICIENT INVENTORY'
            END
        WHEN pc.PRODUCT_CLASS_DESC IN ('MOBILES', 'WATCHES') THEN
            CASE 
                WHEN COALESCE(SUM(oi.PRODUCT_QUANTITY), 0) = 0 THEN 'NO SALES IN PAST, GIVE DISCOUNT TO REDUCE INVENTORY'
                WHEN p.PRODUCT_QUANTITY_AVAIL < 0.2 * COALESCE(SUM(oi.PRODUCT_QUANTITY), 0) THEN 'LOW INVENTORY, NEED TO ADD INVENTORY'
                WHEN p.PRODUCT_QUANTITY_AVAIL < 0.6 * COALESCE(SUM(oi.PRODUCT_QUANTITY), 0) THEN 'MEDIUM INVENTORY, NEED TO ADD SOME INVENTORY'
                ELSE 'SUFFICIENT INVENTORY'
            END
        ELSE
            CASE 
                WHEN COALESCE(SUM(oi.PRODUCT_QUANTITY), 0) = 0 THEN 'NO SALES IN PAST, GIVE DISCOUNT TO REDUCE INVENTORY'
                WHEN p.PRODUCT_QUANTITY_AVAIL < 0.3 * COALESCE(SUM(oi.PRODUCT_QUANTITY), 0) THEN 'LOW INVENTORY, NEED TO ADD INVENTORY'
                WHEN p.PRODUCT_QUANTITY_AVAIL < 0.7 * COALESCE(SUM(oi.PRODUCT_QUANTITY), 0) THEN 'MEDIUM INVENTORY, NEED TO ADD SOME INVENTORY'
                ELSE 'SUFFICIENT INVENTORY'
            END
END AS INVENTORY_STATUS
FROM PRODUCT p
JOIN PRODUCT_CLASS pc ON p.PRODUCT_CLASS_CODE = pc.PRODUCT_CLASS_CODE
LEFT JOIN ORDER_ITEMS oi ON p.PRODUCT_ID = oi.PRODUCT_ID
GROUP BY p.PRODUCT_ID, p.PRODUCT_DESC, p.PRODUCT_QUANTITY_AVAIL, pc.PRODUCT_CLASS_DESC;
    
-- 9. WRITE A QUERY TO DISPLAY PRODUCT_ID, PRODUCT_DESC AND TOTAL QUANTITY OF PRODUCTS WHICH ARE SOLD TOGETHER WITH PRODUCT ID 201 
-- AND ARE NOT SHIPPED TO CITY BANGALORE AND NEW DELHI. DISPLAY THE OUTPUT IN DESCENDING ORDER WITH RESPECT TO TOT_QTY.(USE SUB-QUERY)
	-- [NOTE: TABLES TO BE USED -ORDER_ITEMS,PRODUCT,ORDER_HEADER, ONLINE_CUSTOMER, ADDRESS]
SELECT
S.PRODUCT_ID ,
S.PRODUCT_DESC ,
S.TOT_QTY FROM
( SELECT P.PRODUCT_ID , P.PRODUCT_DESC , SUM(PRODUCT_QUANTITY) AS TOT_QTY
FROM ORDER_ITEMS OI
INNER JOIN PRODUCT P WHERE OI.PRODUCT_ID = P.PRODUCT_ID
AND ORDER_ID IN
( SELECT OI.ORDER_ID FROM ORDER_ITEMS OI
JOIN ORDER_HEADER OH ON OI.ORDER_ID = OH.ORDER_ID
JOIN ONLINE_CUSTOMER OC ON OH.CUSTOMER_ID = OC.CUSTOMER_ID
JOIN ADDRESS A ON OC.ADDRESS_ID = A.ADDRESS_ID WHERE OI.PRODUCT_ID = 201
AND OH.ORDER_STATUS = 'SHIPPED'
AND A.CITY NOT IN ( 'BANGALORE' , 'NEW DELHI' ) )
AND P.PRODUCT_ID != 201
GROUP BY P.PRODUCT_ID , PRODUCT_DESC ) S
ORDER BY TOT_QTY DESC;


-- 10. WRITE A QUERY TO DISPLAY THE ORDER_ID,CUSTOMER_ID AND CUSTOMER FULLNAME AND TOTAL QUANTITY OF PRODUCTS SHIPPED FOR ORDER IDS 
-- WHICH ARE EVENAND SHIPPED TO ADDRESS WHERE PINCODE IS NOT STARTING WITH "5" 
	-- [NOTE: TABLES TO BE USED - ONLINE_CUSTOMER,ORDER_HEADER, ORDER_ITEMS, ADDRESS]
SELECT OH.ORDER_ID ,
OC.CUSTOMER_ID ,
CONCAT ( OC.CUSTOMER_FNAME ,' ' , OC.CUSTOMER_LNAME ) AS FULLNAME ,
SUM(OI.PRODUCT_QUANTITY) AS TOT_QTY
FROM ONLINE_CUSTOMER OC
INNER JOIN ORDER_HEADER OH ON OC.CUSTOMER_ID = OH.CUSTOMER_ID
INNER JOIN ORDER_ITEMS OI ON OH.ORDER_ID = OI.ORDER_ID INNER JOIN
ADDRESS A ON A.ADDRESS_ID = OC.ADDRESS_ID
WHERE MOD(OH.ORDER_ID, 2) = 0
AND OH.ORDER_STATUS = 'SHIPPED'
AND A.PINCODE NOT LIKE "5%"
GROUP BY OH.ORDER_ID , OC.CUSTOMER_ID , FULLNAME;