Project 2

use Ecommerce
db.Ecommerce.insertMany([
{admin_id:1001},{admin_id:1002},{admin_id:1003},{admin_id:1004},{admin_id:1005},cust_id:2001},{cust_id:2002},{cust_id:2003},{cust_id:2004},{cust_id:2005},{seller_id:3001},{seller_id:3002},{seller_id:3003},{seller_id:3004},{seller_id:3005}]);
db.Admin.insertMany([
{admin_id:1001,admin_name:"Saravana",admin_city:"TRICHY",admin_mobil:"0123456789"},
{admin_id:1002,admin_name:"Balu",admin_city:"TRICHY",admin_mobil:"0123456789"},
{admin_id:1002,admin_name:"Priya",admin_city:"TRICHY",admin_mobil:"0123456789"},
{admin_id:1002,admin_name:"Anu",admin_city:"TRICHY",admin_mobil:"0123456789"},
{admin_id:1002,admin_name:"BHarathi",admin_city:"TRICHY",admin_mobil:"0123456789"}
]);
db.Customer.insertMany([
{cust_type:"Premium",cust_id:2001,cust_name:"Karthi",cust_gender:"M",cust_Doj_as_per_date:"05-03-2022",cust_city:"Ramanad",cust_mb_no:"0000000000",order_value:30000},
{cust_type:"Premium",cust_id:2002,cust_name:"Abu",cust_gender:"M",cust_Doj_as_per_date:"13-06-2022",cust_city:"Trichy",cust_mb_no:"1111111111",order_value:3000},
{cust_type:"Normal",cust_id:2003,cust_name:"Sundar",cust_gender:"M",cust_Doj_as_per_date:"26-03-2022",cust_city:"Thanjore",cust_mb_no:"2222222222",order_value:6000},
{cust_type:"Premium",cust_id:2004,cust_name:"Praveen",cust_gender:"M",cust_Doj_as_per_date:"26-05-2022",cust_city:"Mannargudi",cust_mb_no:"3333333333",order_value:5000},
{cust_type:"Normal",cust_id:2005,cust_name:"Thiru",cust_gender:"M",cust_Doj_as_per_date:"28-07-2022",cust_city:"Chennai",cust_mb_no:"4444444444",order_value:8000}
]);
db.Seller.insertMany([
{seller_id:3001,seller_name:"Kumar",seller_city:"Thiruvalur",prod_catagory:"Electronic",seller_mobile_no:"5555555555",prod_sold:10},
{seller_id:3002,seller_name:"Mani",seller_city:"Thiruppur",prod_catagory:"Cloths",seller_mobile_no:"6666666666",prod_sold:29},
{seller_id:3003,seller_name:"Guna",seller_city:"Chennai",prod_catagory:"Electronic",seller_mobile_no:"7777777777",prod_sold:42},
{seller_id:3004,seller_name:"Ram",seller_city:"Coimbatore",prod_catagory:"Furniture",seller_mobile_no:"8888888888",prod_sold:70},
{seller_id:3005,seller_name:"Sadha",seller_city:"Madurai",prod_catagory:"Furniture",seller_mobile_no:"99999999999",prod_sold:03}
]);
db.Product.insertMany([
{prod_category:"Furniture",prod_id:01,prod_name:"Table",prod_color:"Brown",prod_size:"10.5",prod_cost:5000,seller_id:3004},
{prod_category:"Furniture",prod_id:02,prod_name:"Chair",prod_color:"Black",prod_size:"5.5",prod_cost:1000,seller_id:3004},
{prod_category:"Furniture",prod_id:03,prod_name:"Fan",prod_color:"Blue",prod_size:"4.5",prod_cost:3000,seller_id:3005},
{prod_category:"Furniture",prod_id:04,prod_name:"Sofa",prod_color:"Black",prod_size:"55.5",prod_cost:40000,seller_id:3004},
{prod_category:"Furniture",prod_id:05,prod_name:"Grinder",prod_color:"White",prod_size:"15.5",prod_cost:8000,seler_id:3005},
{prod_category:"Electronic",prod_id:06,prod_name:"Vivo mobile",prod_color:"Gray",prod_size:"6.1",prod_cost:30000,seller_id:3001},
{prod_category:"Electronic",prod_id:07,prod_name:"Oppo mobile",prod_color:"Blue",prod_size:"6.5",prod_cost:20000,seller_id:3003},
{prod_category:"Electronic",prod_id:08,prod_name:"Apple phone",prod_color:"White",prod_size:"5.0",prod_cost:80000,seller_id:3003},
{prod_category:"Electronic",prod_id:09,prod_name:"Redmi mobile",prod_color:"Voilet",prod_size:"6.7",prod_cost:25000,seller_id:3001},
{prod_category:"Electronic",prod_id:10,prod_name:"Samsung mobile",prod_color:"Red",prod_size:"6.0",prod_cost:15000,seller_id:3001},
{prod_category:"Cloths",prod_id:11,prod_name:"Shirt",prod_color:"White",prod_size:"40",prod_cost:1000,seller_id:3002},
{prod_category:"Cloths",prod_id:12,prod_name:"Phant",prod_color:"Black",prod_size:"34",prod_cost:2000,seller_id:3002},
{prod_category:"Cloths",prod_id:13,prod_name:"Track phant",prod_color:"Gray",prod_size:"42",prod_cost:8000,seller_id:3002},
{prod_category:"Cloths",prod_id:14,prod_name:"T_shirt",prod_color:"Yellow",prod_size:"40",prod_cost:500,seller_id:3002},
{prod_category:"Cloths",prod_id:15,prod_name:"Jeans",prod_color:"Blue",prod_size:"34",prod_cost:1500,seller_id:3002}
]) ;
db.Cart.insertMany([
{cart_id:10001,cust_id:2001,prod_id:01,prod_cost:5000},
{cart_id:10002,cust_id:2002,prod_id:10,prod_cost:15000},
{cart_id:10003,cust_id:2003,prod_id:15,prod_cost:1500},
{cart_id:10003,cust_id:2004,prod_id:06,prod_cost:30000},
{cart_id:10003,cust_id:2005,prod_id:10,prod_cost:15000}
]);
db.Orders.insertMany([
{order_id:20001,cust_id:2004,seller_id:3006, prod_id:18,order_cost:10000,order_date:"28-11-2022",order_month:"This month",order_status:"Packed",delivery_add:"1 100 Valluvar STREET Ramanad",how_many_products:1},
{order_id:20002,cust_id:2001,seller_id:3010, prod_id:21,order_cost:5000,order_date:"20-09-2022",order_month:"2 month",order_status:"Cancelled",delivery_add:"2 200 Star STREET Trichy",how_many_products:1},
{order_id:20003,cust_id:2004,seller_id:3100, prod_id:56,order_cost:16000,order_date:"12-11-2022",order_month:"This month",order_status:"Delivered",delivery_add:"1 100 Valluvar STREET Ramanad",how_many_products:1},
{order_id:20004,cust_id:2003,seller_id:3166, prod_id:44,order_cost:3000,order_date:"26-10-2022",order_month:"1 month",order_status:"Delivered",delivery_add:"3 300 New STREET Thanjore",how_many_products:1},
{order_id:20005,cust_id:2005,seller_id:3009, prod_id:29,order_cost:7000,order_date:"05-11-2022",order_month:"This month",order_status:"Shiped",delivery_add:"4 400 Old STREET Mannargudi",how_many_products:1},
{order_id:20006,cust_id:2002,seller_id:3001, prod_id:13,order_cost:800,order_date:"30-11-2022",order_month:"This month",order_status:"Ordered",delivery_add:"5 500 Solai STREET Chennai",how_many_products:1}
]);
db.Payment.insertOne({payment_id:101,cust_id:2001,prod_cost:20000,payment_status:"Paid",pay_type:"cash"});
db.Shipment.insertOne({shipment_id:790012,deliver_add:"320/10 kk nagar chennai",shiping_status:"Packed"});

1.
db.Orders.aggregate([{$group:{_id: "$cust_id",avgAmount: { $avg: "$order_cost" }}}]);

2.
db.Customer.find({"cust_Doj_as_per_date":{"$lt":"2 Months"}}).sort({order_cost:1}).limit(10).pretty()

3.
db.Orders.aggregate([{ $match: { order_month:"This month" } }, { $group: { _id:{month:"$order_month"}, This_Month_orders: { $sum: "$Orders" } } }]);

4.
db.Customer.aggregate([{ "$group": { _id: {Customer_city:"$cust_city"}, "count": { "$sum": 1 }, "cust_name": { "$push": "cust_name" } } },
{ "$project": { "size": 1, "customer_count_same_city": { "$size": "$cust_name" } } }]);

5.
db.getCollection("Seller").aggregate([{
$lookup:
{
from:"Customer",
localField:"seller_city",
foreignField:"cust_city",
as:"customerdetails"
}
},
{
"$sort":{
"products_sold":-1
}},
{ $match : { "customerdetails" : { $ne : []}}},
]);