SQL Project_PG DSBA _Part-1(SQLite) Write a query to Display the product details (product_class_code, product_id, product_desc, product_price,) as per the following criteria and sort them in descending order of category.

Document Content and Description Below

SQL Project Part-1(SQLite) Question 1 1) Write a query to Display the product details (product_class_code, product_id, product_desc, product_price,) as per the following criteria and sort t ... hem in descending order of category: a) If the category is 2050, increase the price by 2000 b) If the category is 2051, increase the price by 500 c) If the category is 2052, increase the price by 600. Hint: Use case statement. no permanent change in table required. (60 ROWS) [NOTE: PRODUCT TABLE] SOLUTION - Considered PRODUCT_CLASS_CODE as CATAGORY. Query SELECT PRODUCT_CLASS_CODE AS 'Product Catagory', PRODUCT_ID AS 'Product ID', PRODUCT_DESC AS 'Product Description', PRODUCT_PRICE AS 'Actual Price', CASE PRODUCT_CLASS_CODE WHEN 2050 THEN PRODUCT_PRICE+2000 -- Increase Price for Category 2050 WHEN 2051 THEN PRODUCT_PRICE+500 -- Increase Price for Category 2051 WHEN 2052 THEN PRODUCT_PRICE+600 -- Increase Price for Category 2052 ELSE PRODUCT_PRICE END AS 'Calculated Price' FROM PRODUCT -- Decending order by category(Product Class Code) ORDER BY PRODUCT_CLASS_CODE DESC; Output 2. Write a query to display (product_class_desc, product_id, product_desc, product_quantity_avail) and Show inventory status of products as below as per their available quantity: 1. For Electronics and Computer categories, if available quantity is <= 10, show 'Low stock', 11 <= qty <= 30, show 'In stock', >= 31, show 'Enough stock' 2. For Stationery and Clothes categories, if qty <= 20, show 'Low stock', 21 <= qty <= 80, show 'In stock', >= 81, show 'Enough stock' 3. Rest of the categories, if qty <= 15 – 'Low Stock', 16 <= qty <= 50 – 'In Stock', >= 51 – 'Enough stock' For all categories, if available quantity is 0, show 'Out of stock'. Hint: Use case statement. (60 ROWS) [NOTE: TABLES TO BE USED – product, product_class] Query SELECT PC.PRODUCT_CLASS_DESC AS 'Product Category', P.PRODUCT_ID AS 'Product ID', P.PRODUCT_DESC AS 'Product Description', P.PRODUCT_QUANTITY_AVAIL AS 'Product Availability', CASE -- Electronics(2050) and Computer (2053) WHEN PC.PRODUCT_CLASS_CODE IN (2050,2053) THEN CASE WHEN P.PRODUCT_QUANTITY_AVAIL =0 THEN 'Out of stock' -- Out of stock criteria WHEN P.PRODUCT_QUANTITY_AVAIL <=10 THEN 'Low stock' WHEN (P.PRODUCT_QUANTITY_AVAIL >=11 AND P.PRODUCT_QUANTITY_AVAIL <=30) THEN 'In stock' WHEN (PRODUCT_QUANTITY_AVAIL >=31) THEN 'Enough stock' END -- Stationery(2052) and Clothes(2056) WHEN PC.PRODUCT_CLASS_CODE IN (2052,2056) THEN CASE WHEN P.PRODUCT_QUANTITY_AVAIL =0 THEN 'Out of stock' -- Out of stock criteria WHEN P.PRODUCT_QUANTITY_AVAIL <=20 THEN 'Low stock' WHEN (P.PRODUCT_QUANTITY_AVAIL >=21 AND P.PRODUCT_QUANTITY_AVAIL <=80) THEN 'In stock' WHEN (PRODUCT_QUANTITY_AVAIL >=81) THEN 'Enough stock' END -- Rest of the categories ELSE CASE WHEN P.PRODUCT_QUANTITY_AVAIL =0 THEN 'Out of stock' -- Out of stock criteria WHEN P.PRODUCT_QUANTITY_AVAIL <=15 THEN 'Low stock' WHEN (P.PRODUCT_QUANTITY_AVAIL >=16 AND P.PRODUCT_QUANTITY_AVAIL <=50) THEN 'In stock' WHEN (PRODUCT_QUANTITY_AVAIL >=51) THEN 'Enough stock' END END AS 'Inventory Status' FROM PRODUCT P -- Join the Product and Product Class TABLE based on the Product Class Code INNER JOIN PRODUCT_CLASS PC ON P.PRODUCT_CLASS_CODE = PC.PRODUCT_CLASS_CODE -- Let’s do order by Product Class Code and available quantity by descending ORDER BY P.PRODUCT_CLASS_CODE,P.PRODUCT_QUANTITY_AVAIL DESC; Output 3. Write a query to Show the count of cities in all countries other than USA & MALAYSIA, with more than 1 city, in the descending order of CITIES. (2 rows) [NOTE: ADDRESS TABLE, Do not use Distinct] Query Output SELECT COUNT(CITY) AS Count_of_Cites, -- Count Of The Cities COUNTRY AS Country FROM ADDRESS GROUP BY COUNTRY -- Count of cities more than 1 and exclude the USA and Malaysia HAVING COUNTRY NOT IN ('USA','Malaysia') AND COUNT(CITY) > 1 -- Descending order of count of cities ORDER BY Count_of_Cites DESC; 4. Write a query to display the customer_id,customer full name ,city,pincode,and order details (order id,order date, product class desc, product desc, subtotal(product_quantity * product_price)) for orders shipped to cities whose pin codes do not have any 0s in them. Sort the output on customer name, order date and subtotal. (52 ROWS) [NOTE: TABLE TO BE USED - online_customer, address, order_header, order_items, product, product_class] [Show More]

Last updated: 7 months ago

Preview 1 out of 9 pages