-----------------------------------------------------------------------------------------------------------------------------------
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]
SOLUTION - ALTER TABLE ONLINE_CUSTOMER
ADD COLUMN CUSTOMER_FNAME VARCHAR(20),
ADD COLUMN CUSTOMER_LNAME VARCHAR(20),
ADD COLUMN CUSTOMER_EMAIL VARCHAR(30),
ADD COLUMN CUSTOMER_PHONE BIGINT,
ADD COLUMN ADDRESS_ID INT,
ADD COLUMN CUSTOMER_CREATION_DATE DATE,
ADD COLUMN CUSTOMER_USERNAME VARCHAR(20),
ADD COLUMN CUSTOMER_GENDER CHAR(1)
SHOW COLUMNS FROM ONLINE_CUSTOMER;
SELECT CUSTOMER_ID, CUSTOMER_FNAME, CUSTOMER_LNAME, CUSTOMER_EMAIL,
CUSTOMER_CREATION_DATE, CUSTOMER_GENDER
FROM ONLINE_CUSTOMER
LIMIT 10;
-- Check the structure of the ONLINE_CUSTOMER table
SHOW COLUMNS FROM ONLINE_CUSTOMER;
-- Check if there is any data in the table
SELECT * FROM ONLINE_CUSTOMER LIMIT 10;
SELECT COUNT(*) AS total_records FROM ONLINE_CUSTOMER;
SELECT COUNT(*) AS non_null_fname FROM ONLINE_CUSTOMER WHERE CUSTOMER_FNAME IS NOT
NULL;
SELECT COUNT(*) AS non_null_lname FROM ONLINE_CUSTOMER WHERE CUSTOMER_LNAME IS NOT
NULL;
SELECT COUNT(*) AS non_null_email FROM ONLINE_CUSTOMER WHERE CUSTOMER_EMAIL IS NOT
NULL;
SELECT COUNT(*) AS non_null_creation_date FROM ONLINE_CUSTOMER WHERE
CUSTOMER_CREATION_DATE IS NOT NULL;
SELECT COUNT(*) AS non_null_gender FROM ONLINE_CUSTOMER WHERE CUSTOMER_GENDER IS NOT
NULL;
SELECT
CUSTOMER_FNAME,
CUSTOMER_LNAME,
CUSTOMER_EMAIL,
CUSTOMER_CREATION_DATE,
CUSTOMER_GENDER
FROM
ONLINE_CUSTOMER
LIMIT 10;
SELECT
CUSTOMER_ID,
CUSTOMER_FNAME,
CUSTOMER_LNAME,
CUSTOMER_EMAIL,
CUSTOMER_CREATION_DATE,
CUSTOMER_GENDER,
CONCAT(
CASE
WHEN CUSTOMER_GENDER = 'M' THEN 'Mr. '
WHEN CUSTOMER_GENDER = 'F' THEN 'Ms. '
ELSE ''
END,
UPPER(CUSTOMER_FNAME),
' ',
UPPER(CUSTOMER_LNAME)
) AS CUSTOMER_FULL_NAME,
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'
ELSE ''
END AS CUSTOMER_CATEGORY
FROM
ONLINE_CUSTOMER
LIMIT 10;
-- 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]
SOLUTION-
SELECT
p.PRODUCT_ID,
p.PRODUCT_DESC,
p.PRODUCT_QUANTITY_AVAIL,
p.PRODUCT_PRICE,
p.PRODUCT_QUANTITY_AVAIL * p.PRODUCT_PRICE AS INVENTORY_VALUE,
CASE
WHEN p.PRODUCT_PRICE > 20000 THEN p.PRODUCT_PRICE * 0.8 -- 20% discount
WHEN p.PRODUCT_PRICE > 10000 THEN p.PRODUCT_PRICE * 0.85 -- 15% discount
ELSE p.PRODUCT_PRICE * 0.9 -- 10% discount
END AS NEW_PRICE
FROM
PRODUCT p
LEFT JOIN
ORDER_ITEMS oi ON p.PRODUCT_ID = oi.PRODUCT_ID
WHERE
oi.PRODUCT_ID IS NULL
ORDER BY
INVENTORY_VALUE 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]
SOLUTION –
SELECT
pc.PRODUCT_CLASS_CODE,
pc.PRODUCT_CLASS_DESC,
COUNT(p.PRODUCT_ID) AS PRODUCT_TYPE_COUNT,
SUM(p.PRODUCT_QUANTITY_AVAIL * p.PRODUCT_PRICE) AS INVENTORY_VALUE
FROM
PRODUCT_CLASS pc
JOIN
PRODUCT p ON pc.PRODUCT_CLASS_CODE = p.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]
SOLUTION-
SELECT
oc.CUSTOMER_ID,
CONCAT(UPPER(oc.CUSTOMER_FNAME), ' ', UPPER(oc.CUSTOMER_LNAME)) AS FULL_NAME,
oc.CUSTOMER_EMAIL,
oc.CUSTOMER_PHONE,
a.COUNTRY
FROM
ONLINE_CUSTOMER oc
JOIN
ADDRESS a ON oc.ADDRESS_ID = a.ADDRESS_ID
WHERE
oc.CUSTOMER_ID IN (
SELECT
oh.CUSTOMER_ID
FROM
ORDER_HEADER oh
WHERE
oh.ORDER_STATUS = 'Cancelled'
GROUP BY
oh.CUSTOMER_ID
HAVIN0047
COUNT(*) = SUM(CASE WHEN oh.ORDER_STATUS = 'Cancelled' THEN 1 ELSE 0 END)
LIMIT 0, 50000;
-- 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]
SOLUTION-
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
ORDER BY
NUMBER_OF_CONSIGNMENTS DESC
LIMIT 9;
-- 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]
SOLUTION-
SELECT
oc.CUSTOMER_ID,
CONCAT(UPPER(oc.CUSTOMER_FNAME), ' ', UPPER(oc.CUSTOMER_LNAME)) AS
CUSTOMER_FULL_NAME,
SUM(oi.PRODUCT_QUANTITY) AS TOTAL_QUANTITY_SHIPPED,
SUM(oi.PRODUCT_QUANTITY * p.PRODUCT_PRICE) AS TOTAL_VALUE_SHIPPED
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,
oc.CUSTOMER_FNAME,
oc.CUSTOMER_LNAME
ORDER BY
oc.CUSTOMER_ID;
-- 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]
SOLUTION-
SELECT * FROM ORDER_ITEMS; --
Example 2: Summarize total quantities ordered per product SELECT PRODUCT_ID,
SUM(PRODUCT_QUANTITY) AS TOTAL_QUANTITY_ORDERED FROM ORDER_ITEMS GROUP BY
PRODUCT_ID;
-- Example 3: Joining with other tables SELECT oi.ORDER_ID, oi.PRODUCT_ID, p.PRODUCT_DESC,
oi.PRODUCT_QUANTITY FROM ORDER_ITEMS oi JOIN PRODUCT p ON oi.PRODUCT_ID = p.PRODUCT_ID
WHERE oi.ORDER_ID = 1001;
-- 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'
SOLUTION -A
#solution A
SELECT
p.PRODUCT_ID,
p.PRODUCT_DESC,
p.PRODUCT_QUANTITY_AVAIL,
COALESCE(SUM(oi.PRODUCT_QUANTITY), 0) AS QUANTITY_SOLD,
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 AS INVENTORY_STATUS
FROM
product p
LEFT JOIN
order_items oi ON p.PRODUCT_ID = oi.PRODUCT_ID
JOIN
product_class pc ON p.PRODUCT_CLASS_CODE = pc.PRODUCT_CLASS_CODE
WHERE
pc.PRODUCT_CLASS_CODE IN (2050, 2053)
GROUP BY
p.PRODUCT_ID,
p.PRODUCT_DESC,
p.PRODUCT_QUANTITY_AVAIL
LIMIT 0, 50000;
-- 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'
SOLUTION- B
SELECT
p.PRODUCT_ID,
p.PRODUCT_DESC,
p.PRODUCT_QUANTITY_AVAIL,
COALESCE(SUM(oi.PRODUCT_QUANTITY), 0) AS QUANTITY_SOLD,
CASE
-- Electronics and Computer categories
WHEN pc.PRODUCT_CLASS_CODE IN (2050, 2053) 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
-- Mobiles and Watches categories
WHEN pc.PRODUCT_CLASS_CODE IN (2055, 2057) 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
END AS INVENTORY_STATUS
FROM
product p
LEFT JOIN
order_items oi ON p.PRODUCT_ID = oi.PRODUCT_ID
JOIN
product_class pc ON p.PRODUCT_CLASS_CODE = pc.PRODUCT_CLASS_CODE
WHERE
pc.PRODUCT_CLASS_CODE IN (2050, 2053, 2055, 2057)
GROUP BY
p.PRODUCT_ID,
p.PRODUCT_DESC,
p.PRODUCT_QUANTITY_AVAIL,
pc.PRODUCT_CLASS_CODE
LIMIT 0, 50000;
-- 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)
SOLUTION –C
SELECT
p.PRODUCT_ID,
p.PRODUCT_DESC,
p.PRODUCT_QUANTITY_AVAIL,
COALESCE(sales_data.QUANTITY_SOLD, 0) AS QUANTITY_SOLD,
CASE
-- Electronics and Computer categories
WHEN pc.PRODUCT_CLASS_CODE IN (2050, 2053) THEN
CASE
WHEN COALESCE(sales_data.QUANTITY_SOLD, 0) = 0 THEN 'NO SALES IN PAST, GIVE DISCOUNT
TO REDUCE INVENTORY'
WHEN p.PRODUCT_QUANTITY_AVAIL < 0.1 * COALESCE(sales_data.QUANTITY_SOLD, 0) THEN
'LOW INVENTORY, NEED TO ADD INVENTORY'
WHEN p.PRODUCT_QUANTITY_AVAIL < 0.5 * COALESCE(sales_data.QUANTITY_SOLD, 0) THEN
'MEDIUM INVENTORY, NEED TO ADD SOME INVENTORY'
ELSE 'SUFFICIENT INVENTORY'
END
-- Mobiles and Watches categories
WHEN pc.PRODUCT_CLASS_CODE IN (2055, 2057) THEN
CASE
WHEN COALESCE(sales_data.QUANTITY_SOLD, 0) = 0 THEN 'NO SALES IN PAST, GIVE DISCOUNT
TO REDUCE INVENTORY'
WHEN p.PRODUCT_QUANTITY_AVAIL < 0.2 * COALESCE(sales_data.QUANTITY_SOLD, 0) THEN
'LOW INVENTORY, NEED TO ADD INVENTORY'
WHEN p.PRODUCT_QUANTITY_AVAIL < 0.6 * COALESCE(sales_data.QUANTITY_SOLD, 0) THEN
'MEDIUM INVENTORY, NEED TO ADD SOME INVENTORY'
ELSE 'SUFFICIENT INVENTORY'
END
-- Rest of the categories
ELSE
CASE
WHEN COALESCE(sales_data.QUANTITY_SOLD, 0) = 0 THEN 'NO SALES IN PAST, GIVE DISCOUNT
TO REDUCE INVENTORY'
WHEN p.PRODUCT_QUANTITY_AVAIL < 0.3 * COALESCE(sales_data.QUANTITY_SOLD, 0) THEN
'LOW INVENTORY, NEED TO ADD INVENTORY'
WHEN p.PRODUCT_QUANTITY_AVAIL < 0.7 * COALESCE(sales_data.QUANTITY_SOLD, 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
SELECT
oi.PRODUCT_ID,
SUM(oi.PRODUCT_QUANTITY) AS QUANTITY_SOLD
FROM
order_items oi
GROUP BY
oi.PRODUCT_ID
) AS sales_data
ON
p.PRODUCT_ID = sales_data.PRODUCT_ID
GROUP BY
p.PRODUCT_ID,
p.PRODUCT_DESC,
p.PRODUCT_QUANTITY_AVAIL,
pc.PRODUCT_CLASS_CODE,
sales_data.QUANTITY_SOLD
LIMIT 0, 50000;
-- 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]
SOLUTION -9
SELECT
p.PRODUCT_ID,
p.PRODUCT_DESC,
SUM(oi.PRODUCT_QUANTITY) AS TOT_QTY
FROM
order_items oi
JOIN
product p ON oi.PRODUCT_ID = p.PRODUCT_ID
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 -- Assuming ADDRESS_ID is in online_customer table
WHERE
oi.ORDER_ID IN (
SELECT
oi_inner.ORDER_ID
FROM
order_items oi_inner
WHERE
oi_inner.PRODUCT_ID = 201
)
AND a.CITY NOT IN ('Bangalore', 'New Delhi')
GROUP BY
p.PRODUCT_ID, p.PRODUCT_DESC
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]
SOLUTION -10
SHOW COLUMNS FROM ORDER_HEADER;
SHOW COLUMNS FROM ADDRESS;
SHOW COLUMNS FROM ONLINE_CUSTOMER;
SHOW COLUMNS FROM ORDER_ITEMS;
SHOW TABLES;
SELECT
oh.ORDER_ID,
oh.CUSTOMER_ID,
CONCAT(oc.CUSTOMER_FNAME, ' ', oc.CUSTOMER_LNAME) AS CUSTOMER_FULLNAME,
SUM(oi.PRODUCT_QUANTITY) AS TOTAL_QUANTITY_SHIPPED
FROM
ORDER_HEADER oh
JOIN
ONLINE_CUSTOMER oc ON oh.CUSTOMER_ID = oc.CUSTOMER_ID
JOIN
ORDER_ITEMS oi ON oh.ORDER_ID = oi.ORDER_ID
JOIN
ADDRESS a ON oc.ADDRESS_ID = a.ADDRESS_ID -- Adjust join condition based on your schema
WHERE
oh.ORDER_ID % 2 = 0 -- Check if ORDER_ID is even
AND LEFT(a.PINCODE, 1) <> '5' -- PINCODE does not start with '5'
GROUP BY
oh.ORDER_ID, oh.CUSTOMER_ID, oc.CUSTOMER_FNAME, oc.CUSTOMER_LNAME
ORDER BY
oh.ORDER_ID
LIMIT 50000;
