Creating Database
-- Creating the schema
create schema supply_db;-- Using the schema
use supply_db;Creating tables Department
DROP TABLE IF EXISTS department;CREATE TABLE department (
`Id` int NOT NULL,
`Name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`Id`)
);DROP TABLE IF EXISTS category;CREATE TABLE category (
`Id` int NOT NULL,
`Name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`Id`)
);DROP TABLE IF EXISTS customer_info;CREATE TABLE customer_info (
`Id` int NOT NULL,
`City` varchar(25) DEFAULT NULL,
`First_Name` varchar(15) DEFAULT NULL,
`Last_Name` varchar(15) DEFAULT NULL,
`Segment` varchar(15) DEFAULT NULL,
`State` varchar(2) DEFAULT NULL,
`Street` varchar(40) DEFAULT NULL,
`Zipcode` varchar(5) DEFAULT NULL,
PRIMARY KEY (`Id`)
);DROP TABLE IF EXISTS product_info ;CREATE TABLE product_info (
`Product_Id` int NOT NULL,
`Product_Name` varchar(50) DEFAULT NULL,
`Category_Id` int DEFAULT NULL,
`Department_Id` int DEFAULT NULL,
`Product_Price` decimal(12,2) DEFAULT NULL,
PRIMARY KEY (`Product_Id`)
);DROP TABLE IF EXISTS ordered_items;CREATE TABLE ordered_items (
`Order_Item_Id` int NOT NULL,
`Order_Id` int NOT NULL,
`Item_Id` int NOT NULL,
`Quantity` int DEFAULT NULL,
`Sales` decimal(12,2) DEFAULT NULL,
PRIMARY KEY (`Order_Item_Id`)
);DROP TABLE IF EXISTS orders;CREATE TABLE orders (
`Order_Id` int NOT NULL,
`Type` varchar(10) DEFAULT NULL,
`Real_Shipping_Days` int DEFAULT NULL,
`Scheduled_Shipping_Days` int DEFAULT NULL,
`Customer_Id` int DEFAULT NULL,
`Order_City` varchar(20) DEFAULT NULL,
`Order_Date` date DEFAULT NULL,
`Order_Region` varchar(15) DEFAULT NULL,
`Order_State` varchar(20) DEFAULT NULL,
`Order_Status` varchar(20) DEFAULT NULL,
`Shipping_Mode` varchar(20) DEFAULT NULL,
PRIMARY KEY (`Order_Id`)
);Inserting values in the tables
INSERT INTO department VALUES (1,'Indoors'),(2,'Fitness'),(3,'Footwear'),(4,'Apparel'),(5,'Golf'),(6,'Outdoors'),(7,'Fan Shop'),(8,'Book Shop'),(9,'Discs Shop'),(10,'Technology'),(11,'Pet Shop'),(12,'Health and Beauty ');INSERT INTO category VALUES (2,'Soccer'),(3,'Baseball & Softball'),(4,'Basketball'),(5,'Lacrosse'),(6,'Tennis & Racquet'),(7,'Hockey'),(9,'Cardio Equipment'),(10,'Strength Training'),(11,'Fitness Accessories'),(12,'Boxing & MMA'),(13,'Electronics'),(16,'As Seen on TV!'),(17,'Cleats'),(18,'Mens Footwear'),(24,'Womens Apparel'),(26,'Girls Apparel'),(29,'Shop By Sport'),(30,'Mens Golf Clubs'),(31,'Womens Golf Clubs'),(32,'Golf Apparel'),(33,'Golf Shoes'),(34,'Golf Bags & Carts'),(35,'Golf Gloves'),(36,'Golf Balls'),(37,'Electronics'),(38,'Kids Golf Clubs'),(40,'Accessories'),(41,'Trade-In'),(43,'Camping & Hiking'),(44,'Hunting & Shooting'),(45,'Fishing'),(46,'Indoor/Outdoor Games'),(48,'Water Sports'),(59,'Books '),(60,'Baby '),(61,'CDs '),(62,'Cameras '),(63,'Childrens Clothing'),(64,'Computers'),(65,'Consumer Electronics'),(66,'Crafts'),(67,'DVDs'),(68,'Garden'),(69,'Health and Beauty'),(70,'Mens Clothing'),(71,'Music'),(72,'Pet Supplies'),(73,'Sporting Goods'),(74,'Toys'),(75,'Video Games'),(76,'Womens Clothing');INSERT INTO customer_info VALUES (1,'Brownsville','Richard','Hernandez','Consumer','TX','6303 Heather Plaza','78521'),(8,'Lawrence','Megan','Smith','Corporate','MA','3047 Foggy Forest Plaza','01841'),(16,'Caguas','Tiffany','Smith','Corporate','PR','6651 Iron Port','00725'),(17,'Taylor','Mary','Robinson','Consumer','MI','1325 Noble Pike','48180');INSERT INTO product_info VALUES (37,'adidas Kids F5 Messi FG Soccer Cleat',3,2,34.99),(44,'adidas Mens F10 Messi TRX FG Soccer Cleat',3,2,59.99),(93,'Under Armour Mens Tech II T-Shirt',5,2,24.99),(116,'Nike Mens Comfort 2 Slide',6,2,44.99),(134,'Nike Womens Legend V-Neck T-Shirt',7,2,25),(135,'Nike Dri-FIT Crew Sock 6 Pack',7,2,22),(172,'Nike Womens Tempo Shorts',9,3,30),(191,'Nike Mens Free 5.0+ Running Shoe',9,3,99.99),(235,'Under Armour Hustle Storm Medium Duffle Bag',11,3,34.99),(249,'Under Armour Womens Micro G Skulpt Running S',12,3,54.97),(273,'Under Armour Kids Mercenary Slide',13,3,27.99),(276,'Under Armour Womens Ignite Slide',13,3,31.99);INSERT INTO ordered_items VALUES (50787,20337,1004,1,399.98),(50791,20338,365,2,119.98),(50788,20338,502,2,100),(50789,20338,191,4,399.96),(50790,20338,365,4,239.96),(50800,20346,1004,1,399.98),(50801,20346,502,3,150),(50836,20361,365,1,59.99),(50838,20363,403,1,129.99),(50839,20363,822,2,95.98),(50837,20363,627,4,159.96),(50840,20364,403,1,129.99),(50841,20364,191,2,199.98),(50842,20365,502,2,100),(50843,20365,1004,1,399.98),(50844,20365,957,1,299.98),(50845,20366,365,2,119.98),(50847,20368,191,5,499.95),(50874,20380,957,1,299.98),(50875,20380,191,3,299.97),(50876,20380,804,5,99.95),(50877,20380,1073,1,199.99),(50878,20381,1073,1,199.99),(50879,20381,191,1,99.99),(50880,20381,1004,1,399.98),(50949,20407,957,1,299.98);INSERT INTO orders VALUES (20337,'DEBIT',3,4,8838,'Kolhapur','2018-10-23','South Asia','Maharashtra','ON_HOLD','Standard Class'),(20338,'PAYMENT',4,4,5231,'Kolhapur','2018-10-23','South Asia','Maharashtra','PENDING_PAYMENT','Standard Class'),(20346,'DEBIT',2,2,6365,'Bareilly','2018-10-23','South Asia','Uttar Pradesh','COMPLETE','Second Class'),(20361,'TRANSFER',2,2,5217,'Raipur','2018-10-24','South Asia','Rajastan','PENDING','Second Class'),(20363,'TRANSFER',4,2,7667,'Raipur','2018-10-24','South Asia','Rajastan','PENDING','Second Class');Part 1:
use supply_db ;
Question : Golf related products
List all products in categories related to golf. Display the Product_Id, Product_Name in the output. Sort the output in the order of product id.
Hint: You can identify a Golf category by the name of the category that contains golf.
*/
-- **********************************************************************************************************************************
/*
Question : Most sold golf products
Find the top 10 most sold products (based on sales) in categories related to golf. Display the Product_Name and Sales column in the output. Sort the output in the descending order of sales.
Hint: You can identify a Golf category by the name of the category that contains golf.
HINT:
Use orders, ordered_items, product_info, and category tables from the Supply chain dataset.
*/
-- **********************************************************************************************************************************
/*
Question: Segment wise orders
Find the number of orders by each customer segment for orders. Sort the result from the highest to the lowest
number of orders.The output table should have the following information:
-Customer_segment
-Orders
*/
-- **********************************************************************************************************************************
/*
Question : Percentage of order split
Description: Find the percentage of split of orders by each customer segment for orders that took six days
to ship (based on Real_Shipping_Days). Sort the result from the highest to the lowest percentage of split orders,
rounding off to one decimal place. The output table should have the following information:
-Customer_segment
-Percentage_order_split
HINT:
Use the orders and customer_info tables from the Supply chain dataset.
*/
-- **********************************************************************************************************************************
Part 2:
use supply_db ;
/* Question: Month-wise NIKE sales
Description:
Find the combined month-wise sales and quantities sold for all the Nike products.
The months should be formatted as ‘YYYY-MM’ (for example, ‘2019-01’ for January 2019).
Sort the output based on the month column (from the oldest to newest). The output should have following columns :
-Month
-Quantities_sold
-Sales
HINT:
Use orders, ordered_items, and product_info tables from the Supply chain dataset.
*/
-- **********************************************************************************************************************************
/*
Question : Costliest products
Description: What are the top five costliest products in the catalogue? Provide the following information/details:
-Product_Id
-Product_Name
-Category_Name
-Department_Name
-Product_Price
Sort the result in the descending order of the Product_Price.
HINT:
Use product_info, category, and department tables from the Supply chain dataset.
*/
-- **********************************************************************************************************************************
/*
Question : Cash customers
Description: Identify the top 10 most ordered items based on sales from all the ‘CASH’ type orders.
Provide the Product Name, Sales, and Distinct Order count for these items. Sort the table in descending
order of Order counts and for the cases where the order count is the same, sort based on sales (highest to
lowest) within that group.
HINT: Use orders, ordered_items, and product_info tables from the Supply chain dataset.
*/
-- **********************************************************************************************************************************
/*
Question : Customers from texas
Obtain all the details from the Orders table (all columns) for customer orders in the state of Texas (TX),
whose street address contains the word ‘Plaza’ but not the word ‘Mountain’. The output should be sorted by the Order_Id.
HINT: Use orders and customer_info tables from the Supply chain dataset.
*/
-- **********************************************************************************************************************************
/*
Question: Home office
For all the orders of the customers belonging to “Home Office” Segment and have ordered items belonging to
“Apparel” or “Outdoors” departments. Compute the total count of such orders. The final output should contain the
following columns:
-Order_Count
*/
-- **********************************************************************************************************************************
/*
Question : Within state ranking
For all the orders of the customers belonging to “Home Office” Segment and have ordered items belonging
to “Apparel” or “Outdoors” departments. Compute the count of orders for all combinations of Order_State and Order_City.
Rank each Order_City within each Order State based on the descending order of their order count (use dense_rank).
The states should be ordered alphabetically, and Order_Cities within each state should be ordered based on their rank.
If there is a clash in the city ranking, in such cases, it must be ordered alphabetically based on the city name.
The final output should contain the following columns:
-Order_State
-Order_City
-Order_Count
-City_rank
HINT: Use orders, ordered_items, product_info, customer_info, and department tables from the Supply chain dataset.
*/
-- **********************************************************************************************************************************
/*
Question : Underestimated orders
Rank (using row_number so that irrespective of the duplicates, so you obtain a unique ranking) the
shipping mode for each year, based on the number of orders when the shipping days were underestimated
(i.e., Scheduled_Shipping_Days < Real_Shipping_Days). The shipping mode with the highest orders that meet
the required criteria should appear first. Consider only ‘COMPLETE’ and ‘CLOSED’ orders and those belonging to
the customer segment: ‘Consumer’. The final output should contain the following columns:
-Shipping_Mode,
-Shipping_Underestimated_Order_Count,
-Shipping_Mode_Rank
HINT: Use orders and customer_info tables from the Supply chain dataset.
*/
-- **********************************************************************************************************************************
Hire Database expert to get advance level database expert help, assignment help, project help. You can send your assignment requirement details at:
realcode4you@gmail.com
Comments