use Ecommercewebsite
db.createCollection("customers")
db.customers.insertMany([
{"customer_id":3001,"cust_name":"mack","city":"france","grade":"140","num_of_products":4,"acc_type":"normal"},
{"customer_id":3003,"cust_name":"mick","city":"washington","grade":"150","num_of_products":5,"acc_type":"premium"},
{"customer_id":3004,"cust_name":"stella","city":"rome","grade":"160","num_of_products":10,"acc_type":"premium"},
{"customer_id":3006,"cust_name":"tesla","city":"saudhi","grade":"170","num_of_products":14,"acc_type":"normal"},
{"customer_id":3008,"cust_name":"musk","city":"britian","grade":"106","num_of_products":34,"acc_type":"normal"},
{"customer_id":3010,"cust_name":"Elon","city":"dindi","grade":"180","num_of_products":11,"acc_type":"premium"},
{"customer_id":3011,"cust_name":"ranjini","city":"saudhi","grade":"190","num_of_products":13,"acc_type":"normal"},
{"customer_id":3012,"cust_name":"Ajith","city":"france","grade":"210","num_of_products":9,"acc_type":"premium"},
{"customer_id":3013,"cust_name":"vijay","city":"rome","grade":"120","num_of_products":8,"acc_type":"premium"},
{"customer_id":3002,"cust_name":"nick","city":"newyork","grade":"100","num_of_products":6,"acc_type":"normal"},
{"customer_id":3007,"cust_name":"draf","city":"coimbatore","grade":"200","num_of_products":24,"acc_type":"premium"},
{"customer_id":3009,"cust_name":"droof","city":"calfornia","grade":"100","num_of_products":7,"acc_type":"normal"},
{"customer_id":3005,"cust_name":"jozy","city":"paris","grade":"200","num_of_products":3,"acc_type":"premium"}
]);
db.createCollection("products")
db.products.insertMany([
{"product_id":1001,"product_name":"Fridge","category":"Electronics","price":10000,"tags":{"tag1":"good","tag2":"Beauty"},"brand":"Videocon","tot_quantity":20},
{"product_id":1002,"product_name":"coolpad","category":"Mobiles","price":12500,"tags":{"tag1":"good","tag2":"Long Life product"},"brand":"CoolpadMobiles","tot_quantity":100},
{"product_id":1003,"product_name":"samsunggalaxy","category":"Mobiles","price":13000,"tags":{"tag1":"good","tag2":"Good to use"},"brand":"samsung","tot_quantity":200},
{"product_id":1004,"product_name":"Table","category":"Furniture","price":4000,"tags":{"tag1":"good","tag2":"Strong"},"brand":"Lungi","tot_quantity":50},
{"product_id":1005,"product_name":"Bed","category":"Furniture","price":5000,"tags":{"tag1":"good","tag2":"Smooth"},"brand":"Lungi","tot_quantity":40}
]);
db.createCollection("orders")
db.orders.insertMany([
{"_id":0,"ord_no":7001,"ord_date":new Date("2022-06-05T10:22:00Z"),"purch_amt":10000,"customer_id":3005,"salesman_id":5003,"ord_status":"shipped"},
{"_id":1,"ord_no":7005,"ord_date":new Date("2022-05-10T12:33:00Z"),"purch_amt":12500,"customer_id":3007,"salesman_id":5001,"ord_status":"ordered"},
{"_id":2,"ord_no":7004,"ord_date":new Date("2022-08-17T19:30:00Z"),"purch_amt":5000,"customer_id":3009,"salesman_id":5007,"ord_status":"delivered"},
{"_id":3,"ord_no":7002,"ord_date":new Date("2022-07-05T13:13:00Z"),"purch_amt":30000,"customer_id":3002,"salesman_id":5002,"ord_status":"packed"},
{"_id":4,"ord_no":7003,"ord_date":new Date("2022-10-05T14:02:00Z"),"purch_amt":4000,"customer_id":3001,"salesman_id":5003,"ord_status":"shipped"},
{"_id":5,"ord_no":7006,"ord_date":new Date("2022-09-05T12:33:00Z"),"purch_amt":3000,"customer_id":3003,"salesman_id":5001,"ord_status":"delivered"},
{"_id":6,"ord_no":7007,"ord_date":new Date("2022-10-05T17:04:00Z"),"purch_amt":40000,"customer_id":3004,"salesman_id":5007,"ord_status":"ordered"},
{"_id":7,"ord_no":7008,"ord_date":new Date("2022-10-05T15:09:00Z"),"purch_amt":20000,"customer_id":3006,"salesman_id":5002,"ord_status":"shipped"},
{"_id":8,"ord_no":7009,"ord_date":new Date("2022-09-05T17:23:00Z"),"purch_amt":7000,"customer_id":3008,"salesman_id":5003,"ord_status":"ordered"},
{"_id":9,"ord_no":7010,"ord_date":new Date("2022-10-05T12:42:00Z"),"purch_amt":6000,"customer_id":3010,"salesman_id":5001,"ord_status":"ordered"},
{"_id":10,"ord_no":7011,"ord_date":new Date("2022-11-05T15:52:00Z"),"purch_amt":5000,"customer_id":3011,"salesman_id":5007,"ord_status":"delivered"},
{"_id":11,"ord_no":7012,"ord_date":new Date("2022-11-05T18:44:00Z"),"purch_amt":2000,"customer_id":3012,"salesman_id":5002,"ord_status":"ordered"},
{"_id":12,"ord_no":7013,"ord_date":new Date("2022-11-05T18:44:00Z"),"purch_amt":6000,"customer_id":3013,"salesman_id":5003,"ord_status":"ordered"}
]);
db.createCollection("payment")
db.payment.insertMany([
{"payment_id":2001,"customer_id":3005,"payment_mode":"Debit Card"},
{"payment_id":2002,"customer_id":3007,"payment_mode":"Credit Card"},
{"payment_id":2003,"customer_id":3009,"payment_mode":"Cash on delivery"},
{"payment_id":2004,"customer_id":3002,"payment_mode":"Debit Card"},
{"payment_id":2005,"customer_id":3001,"payment_mode":"Credit Card"},
{"payment_id":2006,"customer_id":3003,"payment_mode":"Cash on delivery"},
{"payment_id":2007,"customer_id":3004,"payment_mode":"Debit Card"},
{"payment_id":2008,"customer_id":3006,"payment_mode":"Credit Card"},
{"payment_id":2009,"customer_id":3008,"payment_mode":"Debit Card"},
{"payment_id":2010,"customer_id":3010,"payment_mode":"Cash on delivery"},
{"payment_id":2011,"customer_id":3011,"payment_mode":"Cash on delivery"},
{"payment_id":2012,"customer_id":3012,"payment_mode":"Credit Card"},
{"payment_id":2013,"customer_id":3013,"payment_mode":"Debit Card"}
]);
db.createCollection("admin")
db.admin.insertMany([
{"adm_id":1,"adm_name":"Pitchai"},
{"adm_id":2,"adm_name":"John"}
]);
db.createCollection("seller")
db.seller.insertMany([
{"salesman_id":5001,"Name":"james","city":"newyork","commission":Decimal128("0.15")},
{"salesman_id":5002,"Name":"nail","city":"paris","commission":Decimal128("0.19")},
{"salesman_id":5007,"Name":"paul","city":"rome","commission":Decimal128("0.13")},
{"salesman_id":5003,"Name":"mani","city":"annur","commission":Decimal128("0.12")}
]);
db.createCollection("Transaction_details")
db.Transaction_details.insertMany([
{"customer_id":3005,"ord_no":7002,"salesman_id":5003,"payment_id":2001,"tot_amount":30000,"bill_no":101},
{"customer_id":3007,"ord_no":7005,"salesman_id":5001,"payment_id":2002,"tot_amount":12500,"bill_no":102},
{"customer_id":3009,"ord_no":7004,"salesman_id":5007,"payment_id":2003,"tot_amount":5000,"bill_no":103},
{"customer_id":3002,"ord_no":7001,"salesman_id":5002,"payment_id":2004,"tot_amount":10000,"bill_no":104},
{"customer_id":3001,"ord_no":7003,"salesman_id":5003,"payment_id":2005,"tot_amount":10000,"bill_no":105},
{"customer_id":3003,"ord_no":7006,"salesman_id":5001,"payment_id":2006,"tot_amount":12000,"bill_no":106},
{"customer_id":3004,"ord_no":7007,"salesman_id":5007,"payment_id":2007,"tot_amount":30000,"bill_no":107},
{"customer_id":3006,"ord_no":7008,"salesman_id":5002,"payment_id":2008,"tot_amount":40000,"bill_no":108},
{"customer_id":3008,"ord_no":7009,"salesman_id":5003,"payment_id":2009,"tot_amount":30000,"bill_no":109},
{"customer_id":3010,"ord_no":7010,"salesman_id":5001,"payment_id":2010,"tot_amount":13000,"bill_no":110},
{"customer_id":3011,"ord_no":7011,"salesman_id":5007,"payment_id":2011,"tot_amount":15000,"bill_no":111},
{"customer_id":3012,"ord_no":7012,"salesman_id":5002,"payment_id":2012,"tot_amount":16000,"bill_no":112},
{"customer_id":3013,"ord_no":7013,"salesman_id":5003,"payment_id":2013,"tot_amount":18000,"bill_no":113}
]);
1. db.orders.aggregate([
{$group:
{
_id:"$customer_id",
"avgordervalue":{$avg:"$purch_amt"}
}
}
])
2. db.orders.find(
{
"ord_date":
{
$gte: new Date((new Date().getTime() - (60 * 24 * 60 * 60 * 1000)))
}
}
).sort({ "purch_amt": -1 }).limit(10)
3. db.orders.aggregate({$match:{"ord_date":{$gte:new Date((new Date().getTime() - (30 * 24 * 60 * 60 * 1000)))}}},{$group:{_id:"null","sum":{$sum:"$purch_amt"}}})
4. db.customers.aggregate( [ { $group : { _id : "$city" } } ] ).sort({"customer_id":1})
5. db.seller.find({}).sort({"num_of_prds where seller.city =customers.city ":-1});