You are hired by a chain of online retail stores“
Fastkart”. They have provided you with “Fastkart” database and seek answers to the following queries as the results from these queries will help the company in making data-driven decisions that will impact the overall growth of the online retail stores.
All the questionscomes under MYSQL and the queries should be executed in MYSQL. (SQL Script -
Fastkart.sql
)
All Questions carry 8 marks. Total Marks (8 x 10) = 80
Note:In case the file gets open in the browser, Please copy the entire text(including comment) and paste it on MySQL workbench. Execute all the commands to create a database schema. In case, if you are able to download the file, please use MySQL Workbench, File>open SQL Script>FastKart.SQL and then execute all the statements to create a database schema.
All Questions carry 8 marks. Total Marks (8 x 10) = 80
1. List Top 3 products based on QuantityAvailable. (productid, productname, QuantityAvailable ). (3 Rows) [Note: Products] This area will be used by the assessor to leave comments related to this 80pts 2. Display Emailld of those customers who have done more than ten purchases. (Emailld, Total_Transactions). (5 Rows) [Note: Purchasedetails, products] This area will be used by the assessor to leave comments related to this 8.0 pts 3. List the Total QuantityAvailable category wise in descending order. (Name of the category, QuantityAvailable) (7 Rows) [Note: products, categories] This area will be used by the assessor to leave comments related to this 8.0 pts 4. Display Productld, ProductName, CategoryName, Total_Purchased_Quantity for the product which has been sold maximum in terms of quantity? (1 Row) [Note: purchasedetails, products, categories] This area will be used by the assessor to leave comments related to this 8.0 pts 5. Display the number of male and female customers in fastkart. (2 Rows) [Note: roles, users] This area will be used by the assessor to leave comments related to this 8.0 pts 6. Display Productld, ProductName, Price and Item_Classes of all the products where This area will be Item_Classes are as follows: If the price of an item is less than 2,000 then “Affordable”, If fa iii 0 the price of an item is in between 2,000 and 50,000 then *High End Stuff If the price of | 12 =" | 80pis an item is more than 50,000 then “Luxury”. (57 Rows) — 7. Write a query to display Productld, ProductName, CategoryName, Old_Price(price) and New_Price as per the following criteria a. If the category is “Motors”, decrease the price by | This area will be 3000 b. If the category is “Electronics”, increase the price by 50 c. If the category is fo Wicks o leave c . “Fashion, increase the price by 150 For the rest of the categories price remains same. Hint: bos Hi o Use case statement, there should be no permanent change done in table/DB. (57 Rows) | ¢iion [Note: products, categories] This area will be ' | used by the assessor 8. Display the percentage of females present among all Users. (Round up to 2 decimal any 20m places) Add *%" sign while displaying the percentage. (1 Row) [Note: users] im od criterion. This area will be 9. Display the average balance for both card types for those records only where edges CVVNumber > 333 and NameOnCard ends with the alphabet "e". (2 Rows) [Note: toleave comments | 8.0 pts carddetails] related to this This area will be 10. What is the 2nd most valuable item available which does not belong to the “Motor” | used by the assessor category. Value of an item = Price * QuantityAvailable. Display ProductName, toleave comments | 8.0 pts CategoryName, value. (1 Row) [Note: products, categories] related to this criterion. Total Points: 80.0