Project 2
CUSTOMERS COLLECTION
db.Customers.insertMany([
{ cus_id:1,cus_name:"Subarna",email:"[email protected]",city:"Chennai",order_value:2000,order_date:"2023-01-13"},
{ cus_id:2,cus_name:"Sumi",email:"[email protected]",city:"Bangalore",order_value:3000,order_date:"2023-01-13"},
{ cus_id:3,cus_name:"Riya",email:"[email protected]",city:"Mumbai",order_value:1000,order_date:"2023-02-13"},
{ cus_id:4,cus_name:"Santhiya",email:"[email protected]",city:"delhi",order_value:2000,order_date:"2023-02-16"},{cus_id:5,cus_name:"Irish",email:"[email protected]",city:"Chennai",order_value:2000,order_date:"2023-01-19"},
{ cus_id:6,cus_name:"Abisha",email:"[email protected]",city:"aAndhra",order_value:4000,order_date:"2023-01-13"},
{ cus_id:7,cus_name:"Grace",email:"[email protected]",city:"Nagercoil",order_value:9000,order_date:"2023-03-13"},
{ cus_id:8,cus_name:"Sam",email:"[email protected]",city:"delhi",order_value:2000,order_date:"2023-02-16"}
])
PRODUCT COLLECTION
db.product.insertMany([
{pro_id:1,pro_name:"Watch",cost:5000,qty:5},
{pro_id:2,pro_name:"TV",cost:35000,qty:3},
{pro_id:3,pro_name:"Mobile",cost:50000,qty:9},
{pro_id:4,pro_name:"Smart watch",cost:5000,qty:2},
{pro_id:5,pro_name:"Washing Machine",cost:45000,qty:1},
{pro_id:6,pro_name:"Air Conditioner",cost:35000,qty:2},
{pro_id:7,pro_name:"Airpods",cost:3000,qty:1},
{pro_id:8,pro_name:"Smart watch",cost:5000,qty:2}
])
ORDERS COLLECTION]
db.orders.insertMany([
{order_id:1,cus_id:2,order_status:"Delivered",order_date:"2022-01-13"},
{order_id:2,cus_id:1,order_status:"Delivered",order_date:"2023-01-01"},
{order_id:1,cus_id:3,order_status:"Not Delivered",order_date:"2022-11-13"},
{order_id:3,cus_id:2,order_status:"Delivered",order_date:"2022-05-23"},
{order_id:2,cus_id:4,order_status:"Delivered",order_date:"2022-12-21"},
{order_id:1,cus_id:3,order_status:"Not Delivered",order_date:"2022-08-13"}
])
SELLER COLLECTION
db.seller.insertMany([
{seller_id:1,seller_name:"Rishab",seller_city:"Bangalore",product_sold:6},
{seller_id:2,seller_name:"Dinesh",seller_city:"Chennai",product_sold:8},
{seller_id:3,seller_name:"Mahesh",seller_city:"delhi",product_sold:4},
{seller_id:4,seller_name:"Marshal",seller_city:"Mumbai",product_sold:10},
{seller_id:5,seller_name:"Darwin",seller_city:"Nagercoil",product_sold:2},
{seller_id:6,seller_name:"Krishna",seller_city:"delhi",product_sold:9}
])
BILLING COLLECTION
db.billing.insertMany([
{bill_id:1,bill_date:"2022-01-13",delivery_date:"2022-01-13",payment:"Cash on Delivery"},
{bill_id:1,bill_date:"2023-01-01",delivery_date:"2022-01-07",payment:"Online Payment"},
{bill_id:1,bill_date:"2023-02-02",delivery_date:"2023-02-02",payment:"Cash on Delivery"},
{bill_id:1,bill_date:"2022-12-23",delivery_date:"2022-01-13",payment:"Cash on Delivery"},
{bill_id:1,bill_date:"2023-01-01",delivery_date:"2022-01-07",payment:"Online Payment"},
{bill_id:1,bill_date:"2023-02-02",delivery_date:"2023-02-02",payment:"Cash on Delivery"}
])
PURCHASE RECORD COLLECTION
db.purchaserecord.insertMany([
{pur_id:1,price:5000,qty:5},
{pur_id:2,price:50000,qty:9},
{pur_id:3,price:35000,qty:3},
{pur_id:4,price:9000,qty:5},
{pur_id:5,price:45000,qty:9},
{pur_id:6,price:35000,qty:3}
])
CANCELLATION COLLECTION
db.cancellation.insertMany([
{cancel_id:1,cancel_date:"2022-12-20",refund_amt:5000},
{cancel_id:2,cancel_date:"2022-01-13",refund_amt:9000},
{cancel_id:3,cancel_date:"2022-12-23",refund_amt:50000},
{cancel_id:4,cancel_date:"2022-01-13",refund_amt:5000},
{cancel_id:5,cancel_date:"2022-01-01",refund_amt:45000}
])
1. db.Customer.aggregate([
{$group:{_id:"cus_id",average_orders:{$avg:"$order_value"}}}
])
2. db.Customers.aggregate([
{$match:{order_date:{$lt:"2023-02-16"}}},
{$sort:{order_value:-1}}
])
3. db.orders.aggregate([
{$match:{order_date:{$gte:"2022-11-01",$lte:"2022-11-30"}}},
{$group:{_id:"customer_id",orders:{$sum:1}}}
])
4. db.Customers.aggregate([
{$match:{"Bangalore","Chennai","delhi"}},
{$count:"Cities"}
])
5.db.seller.aggregate([
{
$lookup:
{
from:"Customers",
localField:"city",
foreignField:"seller_city",
as:"Order of highest number of products "
}
}
])