Project 2
use Ecommerce
db.admin.insertOne([{adm_id:1001,adm_passward:"mts777",adm_name:"Thomas",adm_contact:123654}]);
db.admin.insertOne([{adm_id:1002,adm_passward:"selva14334",adm_name:"SelvaKumar",adm_contact:456654}]);
db.seller.insertMany([
{sel_id:5001,sel_name:'Nick',sel_gender:'M',sel_city:'Chennai',sel_DOB:'1998-5-23',sel_email:'[email protected]',sel_contact:'94563214'},
{sel_id:5002,sel_name:'Rosi',sel_gender:'F',sel_city:'Krishnagiri',sel_DOB:'1997-6-13',sel_email:'[email protected]',sel_contact:'78965465'},
{sel_id:5003,sel_name:'Ankit',sel_gender:'M',sel_city:'Vellor',sel_DOB:'2000-3-23',sel_email:'[email protected]',sel_contact:'896541456'},
{sel_id:5004,sel_name:'Basker',sel_gender:'M',sel_city:'Salem',sel_DOB:'1996-5-25',sel_email:'[email protected]',sel_contact:'98756212'},
{sel_id:5005,sel_name:'Meena',sel_gender:'F',sel_city:'Chennai',sel_DOB:'1995-4-16',sel_email:'[email protected]',sel_contact:'984563214'},
{sel_id:5006,sel_name:'Manju',sel_gender:'F',sel_city:'Thindukkal',sel_DOB:'1999-6-19',sel_email:'[email protected]',sel_contact:'964563214'},
{sel_id:5007,sel_name:'Arun',sel_gender:'M',sel_city:'Bangolore',sel_DOB:'2003-5-14',sel_email:'[email protected]',sel_contact:'954463214'}]);
db.customer.insertMany([
{cust_name:'Siva',cust_gender:'M',cust_city:'Chennai',cust_DOB:'1995-6-23',cust_email:'[email protected]',cust_Contact:'2343756565'},
{cust_name:'Lakshana',cust_gender:'F',cust_city:'Bangolore',cust_DOB:'2000-6-13',cust_email:'[email protected]',cust_Contact:'46538283476'},
{cust_name:'Dhamothiran',cust_gender:'M',cust_city:'Vellor',cust_DOB:'2011-6-23',cust_email:'[email protected]',cust_Contact:'9423645645'},
{cust_name:'Anbu',cust_gender:'M',cust_city:'Krishnagiri',cust_DOB:'2001-9-25',cust_email:'[email protected]',cust_Contact:'9853178256'},
{cust_name:'Kishor',cust_gender:'M',cust_city:'Chennai',cust_DOB:'2020-4-24',cust_email:'[email protected]',cust_Contact:'9889633'},
{cust_name:'Akila',cust_gender:'F',cust_city:'Thindukkal',cust_DOB:'1998-6-29',cust_email:'[email protected]',cust_Contact:'96445646455'},
{cust_name:'Arun',cust_gender:'M',cust_city:'Chennai',cust_DOB:'2019-5-19',cust_email:'[email protected]',cust_Contact:'963573214'},
{cust_name:'Chandrakala',cust_gender:'F',cust_city:'Thindukkal',cust_DOB:'2016-10-26',cust_email:'[email protected]',cust_Contact:'945645614'},
{cust_name:'Kasthuri',cust_gender:'F',cust_city:'Thiruchi',cust_DOB:'2003-4-18',cust_email:'[email protected]',cust_Contact:'934345614'},
{cust_name:'Krishnan',cust_gender:'M',cust_city:'Kerala',cust_DOB:'2007-9-10',cust_email:'[email protected]',cust_Contact:'98765563347'},
{cust_name:'Hema',cust_gender:'F',cust_city:'Hosur',cust_DOB:'2008-9-10',cust_email:'[email protected]',cust_Contact:'96465563'},
{cust_name:'Kanika',cust_gender:'F',cust_city:'Dharmapuri',cust_DOB:'2007-10-10',cust_email:'[email protected]',cust_Contact:'78768963'},
{cust_name:'Pattu',cust_gender:'M',cust_city:'Chennai',cust_DOB:'2007-9-10',cust_email:'[email protected]',cust_Contact:'75335563'}]);
db.product.insertMany([
{"product":[{"categories":"MensWear"},{"sub_categories":'Jens'}], "product_Tag":[{"Price":1000},{"size":"xx"},{"Fabric":"cotton"}]}]);
db.product.insertMany([
{"product":[{"categories":"MensWear"},{"sub_categories":'Jens'}], "product_Tag":[{"Price":1000},{"size":"x"},{"Fabric":"cotton"}]},
{"product":[{"categories":"MensWear"},{"sub_categories":'T-shirt'}], "product_Tag":[{"Price":2000},{"size":"xxx"},{"Fabric":"cotton"}]},
{"product":[{"categories":"MensWear"},{"sub_categories":'Jens'}], "product_Tag":[{"Price":3000},{"size":"s"},{"Fabric":"synthetic"}]},
{"product":[{"categories":"MensWear"},{"sub_categories":'phant'}], "product_Tag":[{"Price":1000},{"size":"x"},{"Fabric":"cotton"}]},
{"product":[{"categories":"MensWear"},{"sub_categories":'Jens'}], "product_Tag":[{"Price":1000},{"size":"xx"},{"Fabric":"silk"}]},
{"product":[{"categories":"Womens"},{"sub_categories":'chudithar'}], "product_Tag":[{"Price":5000},{"size":"xx"},{"Fabric":"silk"}]},
{"product":[{"categories":"Womens"},{"sub_categories":'sarees'}], "product_Tag":[{"Price":4000},{"size":"lxx"},{"Fabric":"cotton"}]},
{"product":[{"categories":"Womens"},{"sub_categories":'Forg'}], "product_Tag":[{"Price":6000},{"size":"m"},{"Fabric":"cotton"}]},
{"product":[{"categories":"Womens"},{"sub_categories":'choli'}], "product_Tag":[{"Price":1000},{"size":"m"},{"Fabric":"cotton"}]},
{"product":[{"categories":"Womens"},{"sub_categories":'sarees'}], "product_Tag":[{"Price":3000},{"size":"l"},{"Fabric":"cotton"}]},
{"product":[{"categories":"KidsWear"},{"sub_categories":'midi'}], "product_Tag":[{"Price":2000},{"size":"xxx"},{"Fabric":"cotton"}]},
{"product":[{"categories":"KidsWear"},{"sub_categories":'choli'}], "product_Tag":[{"Price":2000},{"size":"S"},{"Fabric":"cotton"}]},
{"product":[{"categories":"KidsWear"},{"sub_categories":'gown'}], "product_Tag":[{"Price":5000},{"size":"xx"},{"Fabric":"cotton"}]},
{"product":[{"categories":"KidsWear"},{"sub_categories":'choli'}], "product_Tag":[{"Price":1000},{"size":"xx"},{"Fabric":"cotton"}]},
]);
db.order.insertMany([
{"cus_name":"Siva","order_id":'10001',"product_name":"jens","ord_items":10,"purc_amount":10000,"order_date":"2022-8-16",
"my_card":[{"card_type":"Premium"},{"status":"Devlivery"}], "Payment":[{"Bill_amount":9999},{"payment_type":"COD"}]}
]);
db.order.insertMany([
{"cus_name":"Pattu","order_id":'10002',"product_name":"choli","ord_items":9,"purc_amount":90000,"order_date":"2022-10-26",
"my_card":[{"card_type":"Normal"},{"status":"Packing"}], "Payment":[{"Bill_amount":8999},{"payment_type":"G-pay"}]},
{"cus_name":"Kanika","order_id":'10003',"product_name":"gown","ord_items":5,"purc_amount":15000,"order_date":"2022-9-9",
"my_card":[{"card_type":"Normal"},{"status":"Track"}], "Payment":[{"Bill_amount":15000},{"payment_type":"COD"}]},
{"cus_name":'Hema',"order_id":'10003',"product_name":"Frog","ord_items":2,"purc_amount":12000,"order_date":"2022-8-30",
"my_card":[{"card_type":"Premium"},{"status":"Devlivery"}], "Payment":[{"Bill_amount":9999},{"payment_type":"PhonePay"}]},
{"cus_name":"Krishnan","order_id":'10004',"product_name":"sarees","ord_items":3,"purc_amount":12000,"order_date":"2022-9-5",
"my_card":[{"card_type":"Premium"},{"status":"Devlivery"}], "Payment":[{"Bill_amount":12000},{"payment_type":"PhonePay"}]},
{"cus_name":"Anbu","order_id":'10005',"product_name":"midi","ord_items":5,"purc_amount":10000,"order_date":"2022-9-10",
"my_card":[{"card_type":"Premium"},{"status":"Devlivery"}], "Payment":[{"Bill_amount":9999},{"payment_type":"COD"}]},
{"cus_name":"Dhamothiran","order_id":'2250',"product_name":"jens","ord_items":10,"purc_amount":10000,"order_date":"2022-8-09",
"my_card":[{"card_type":"Normal"},{"status":"Devlivery"}], "Payment":[{"Bill_amount":9999},{"payment_type":"G-pay"}]},
{"cus_name":"Siva","order_id":'2260',"product_name":"chudithar","ord_items":2,"purc_amount":10000,"order_date":"2022-7-16",
"my_card":[{"card_type":"Premium"},{"status":"Cancel"}], "Payment":[{"Bill_amount":9999},{"payment_type":"G-pay"}]},
{"cus_name":"Kishor","order_id":'2265',"product_name":"jens","ord_items":10,"purc_amount":10000,"order_date":"2022-8-21",
"my_card":[{"card_type":"Premium"},{"status":"Devlivery"}], "Payment":[{"Bill_amount":9999},{"payment_type":"Net_Banking"}]},
{"cus_name":"Lakshana","order_id":'2254',"product_name":"jens","ord_items":10,"purc_amount":10000,"order_date":"2022-5-16",
"my_card":[{"card_type":"Premium"},{"status":"Packing"}], "Payment":[{"Bill_amount":9999},{"payment_type":"Net_Banking"}]},
{"cus_name":"Akila","order_id":'2285',"product_name":"jens","ord_items":10,"purc_amount":10000,"order_date":"2022-9-16",
"my_card":[{"card_type":"Normal"},{"status":"Devlivery"}], "Payment":[{"Bill_amount":9999},{"payment_type":"COD"}]},
{"cus_name":"Arun","order_id":'2249',"product_name":"chudithar","ord_items":2,"purc_amount":10000,"order_date":"2022-8-16",
"my_card":[{"card_type":"Premium"},{"status":"Devlivery"}], "Payment":[{"Bill_amount":10000},{"payment_type":"Net_Banking"}]},
{"cus_name":"Siva","order_id":'2235',"product_name":"phant","ord_items":15,"purc_amount":15000,"order_date":"2022-8-16",
"my_card":[{"card_type":"Premium"},{"status":"Devlivery"}], "Payment":[{"Bill_amount":15000},{"payment_type":"COD"}]},
{"cus_name":"Pattu","order_id":'2278',"product_name":"jens","ord_items":10,"purc_amount":10000,"order_date":"2022-8-16",
"my_card":[{"card_type":"Normal"},{"status":"Devlivery"}], "Payment":[{"Bill_amount":9999},{"payment_type":"COD"}]},
{"cus_name":"Siva","order_id":'2263',"product_name":"midi","ord_items":7,"purc_amount":14000,"order_date":"2022-8-16",
"my_card":[{"card_type":"Premium"},{"status":"Devlivery"}], "Payment":[{"Bill_amount":14000},{"payment_type":"COD"}]},
{"cus_name":"Lakshana","order_id":'2252',"product_name":"T-shirt","ord_items":5,"purc_amount":10000,"order_date":"2022-8-16",
"my_card":[{"card_type":"Premium"},{"status":"Devlivery"}], "Payment":[{"Bill_amount":9999},{"payment_type":"COD"}]},
]);
1.
db.order.aggregate ([{ "$group": { "_id": '$order', "purc_amount": {"$avg": "$purc_amount"}}}]);
2.
db.order.aggregate([{"$match":{"order_date":{$gt:2022-8-30}},$sort:{"purc_amount":-1},{limit(10)}]);
3.
db.order.aggregate([{"$match":{"order_date":{'$order':{$gt:2022-8-1,$lt:2022-8-30}}},{$group:{_id:'cus_name',"order_ithems":{$sum:$ord_items}}}}]);
4.
db.customer.aggregate([{"$group":{"_id":'$cus_city', count:{$sum:1}}}]);
5.
db.getCollection('seller').aggregate([{$lookup:{from:'customer',localField:'sel_id',
foreignField:'city',as:'city'}},{$sort:{ord_items:-1}}]);