Project 2

db.customers.insertMany([
{cust_id:1,cust_name:"bala",email:"[email protected]",cust_city:"chennai",order_value:2000,order_date:"2022-12-02"},
{cust_id:2,cust_name:"murali",email:"[email protected]",cust_city:"bangalore",order_value:1000,order_date:"2022-12-21"},
{cust_id:3,cust_name:"krish",email:"[email protected]",cust_city:"pune",order_value:3000,order_date:"2022-12-25"},
{cust_id:4,cust_name:"rashan",email:"[email protected]",cust_city:"delhi",order_value:5000,order_date:"2022-12-12"},
{cust_id:5,cust_name:"rashitha",email:"[email protected]",cust_city:"chennai",order_value:4000,order_date:"2022-12-22"}
])

db.admins.insertMany([
{adm_id:1,adm_name:"vetri",adm_city:"chennai"},
{adm_id:2,adm_name:"rakshagan",adm_city:"bangalore"},
{adm_id:3,adm_name:"victor",adm_city:"mumbai"}
])

db.product.insertMany([
{pro_id:1,pro_name:"watch",cost:2000,qty:5},
{pro_id:2,pro_name:"liquired",cost:5000,qty:10},
{pro_id:3,pro_name:"mobile",cost:20000,qty:4},
{pro_id:4,pro_name:"laptop",cost:30000,qty:6},
{pro_id:5,pro_name:"bikes",cost:50000,qty:2}
])

db.order.insertMany([
{order_id:1,cust_id:2,order_status:"delivered",order_date:"2022-12-21"},
{order_id:2,cust_id:1,order_status:"delivered",order_date:"2022-12-02"},
{order_id:3,cust_id:4,order_status:"not_delivered",order_date:"2022-12-12"},
{order_id:4,cust_id:3,order_status:"not_delivered",order_date:"2022-12-25"}
])

db.seller.insertMany([
{seller_id:1,seller_name:"karthi",seller_city:"chennai",product_sold:4},
{seller_id:2,seller_name:"murugan",seller_city:"bangalore",product_sold:2},
{seller_id:3,seller_name:"balaji",seller_city:"mumbai",product_sold:5},
{seller_id:4,seller_name:"sai",seller_city:"pune",product_sold:8}
])

db.category.insertMany([
{cate_id:1,cate_name:"rolex",Prod_name:"watch"},
{cate_id:2,cate_name:"vivo",Prod_name:"mobile"},
{cate_id:3,cate_name:"asus",prod_name:"laptop"},
{cate_id:4,cate_name:"yamaha",prod_name:"bikes"}
])

db.cart.insertMany([
{cart_id:1,pro_id:2,cust_id:1,cost:5000,qty:1},
{cart_id:2,pro_id:1,cust_id:2,cost:2000,qty:1},
{cart_id:3,pro_id:3,cust_id:3,cost:20000,qty:1},
{cart_id:4,pro_id:5,cust_id:4,cost:50000,qty:1}
])

db.payment.insertMany([
{pay_id:1,pay_amt:5000,pay_type:"online"},
{pay_id:2,pay_amt:2000,pay_type:"online"},
{pay_id:3,pay_amt:20000,pay_type:"online"},
{pay_id:4,pay_amt:50000,pay_type:"online"}
])

db.shopping.insertMany([
{shop_id:1,shop_name:"amazon"},
{shop_id:2,shop_name:"flipcart"},
{shop_id:3,shop_name:"zahoo"}
])

1:

db.customers.aggregate([
{$group:{_id:"cust_id",average_orders:{$avg:"$order_value"}}}
])

2:

db.customers.aggregate([
{$match:{order_date:{$lt:"2022-12-25"}}},
{$sort:{order_value:-1}},
{$limit:10}
])

3:

db.order.aggregate([
{$match:{order_date:{$gte:"2022-12-01",$lte:"2022-12-30"}}},
{$group:{_id:"cust_id",orders:{$sum:1}}}
])

4:

db.customers.aggregate([
{$group:{_id:"$cust_city",total_city:{$sum:1}}}
])

5:

db.seller.aggregate([
{
$lookup:
{
from:"customers",
localField:"seller_city",
foreignField:"cust_city",
as:"same_cities"
}
},{$sort:{product_sold:-1}}
])