Project 2

use ecommerce_store

db.customer.insertMany({"cus_name": "Magesh", "cus_id":"111, "address":"Theni", "contact":5555555555, "email":"magesh@12", membership: "premium"},

{"cus_name": "Bhavani", "cus_id":"112, "address":"Trichy", "contact":6666666666, "email":"bhavani@23", membership: 'premium"},

{"cus_name": "Gokul", "cus_id":"113, "address":"Theni", "contact":4444444444, "email":"gokul@24", "membership" : "normal"},

{"cus_name": "kiran", "cus_id":"114, "address":"Coimbatore", "contact":3333333333, "email":"kiran@22", "membership": "normal"})

db.seller.insertMany({"seller_id":211, "seller_name":"Ramesh", "shop_name": "haristore", contact":7777777777, "email":"ramesh@65"},

{"seller_id":212, "seller_name":"Prabha", "shop_name": "muthu electronics", contact":1111111111, "email":"prabha@89"},

{"seller_id":213, "seller_name":"Thamizh", "shop_name": "Menswear", contact":5555555555, "email":"formens@44"})

db.admin.insertMany({"admin_id":001, "admin_name":"Tamil Mani", "contact": 2222222222},

{"admin_id":002, "admin_name":"Aravith", "contact": 3333333333},

db.product.insertMany({"product":{_id":"15001, "name":"laptop"}, "catalog":{"catagory":"gadgets", "brand":"lenovo"}, "cost":50000, "stock":5, "seller_id":121, "rating":4, tag:['gadget', 'lenovo', 'laptop']},

{"product":{_id":"15002, "name":"laptop"}, "catalog":{"catagory":"gadget", "brand":"hp"}, "cost":85000, "stock":3, "seller_id":121, "rating":5, tag:['gadget', 'lenovo', 'laptop']},

{"product":{_id":"16001, "name":"Oil"}, "catalog":{"catagory":"groceries", "brand":"goldwinner"}, "cost":200, "stock":15, "seller_id":124, "rating":3,tag:['groceries', 'goldwinner', 'oil']},

{"product:{_id":"17001, "name":"formal shirt"}, "catalog":{"catagory":"clothes", "brand":"nick"}, "cost":799, "stock":5, "seller_id":123, "rating":4, tag:['cloth', 'shirt', 'formals']},

{"product:{_id":"17002, "name":"formal pant"}, "catalog":{"catagory":"clothes", "brand":"otto"}, "cost":1499, "stock":10, "seller_id":123, "rating":4.5, tag:['cloth', 'pant', 'formals']},

{"product:{_id":"18001, "name":"table fan"}, "catalog":{"catagory":"electronics", "brand":"chrompton"}, "cost":1500, "stock":2, "seller_id":122, "rating":3, tag:['fan', 'electronics', 'chrompton']},

{"product:{_id":"18002, "name":"tubelight"}, "catalog":{"catagory":"electronics", "brand":"chrompton"}, "cost":200, "stock":5, "seller_id":122, "rating":4, tag:['light', 'chrompton', 'electronics']})

db.cart.insertMany({"product":{_id":"15001, "name":"laptop"}, "cus_id":111, "catalog.catagries":"gadgets"},

{"product":{_id":"15002, "name":"laptop"}, "cus_id":112, "catalog.catagries":"gadgets"},

{"product:{_id":"17001, "name":"formal shirt"}, "cus_id":111, "catalog.catagries":"clothes"},

{"product:{_id":"17002, "name":"formal pant"}, "cus_id":111, "catalog.catagries":"clothes"},

{"product":{_id":"16001, "name":"Oil"},"cus_id":113, "catalog.catagries":"groceries"},

{"product:{_id":"18002, "name":"tubelight"}, "cus_id":114, "catalog.catagries":"electronics"})

db.orders.insertMany({"order_id":13122022, "product_id":15001, "cost":50000, "cus_id":111, "payment":"done", "billing":56431},

{"order_id":23112022, "product_id":15002, "cus_id":112, "cost":85000, "payment":"done", "billing":68266},

{"order_id":12052022, "product_id":17001, "cus_id":111, "cost":799, "payment":"done", "billing":75126},

{"order_id":18062022, "product_id":17002, "cus_id":113, "cost":1499", payment":"done", "billing":65430}

Simple Queries to perform on ecommerce db

1. Write a query to find the average order value for all Customers.

db.orders.aggregate([{$group: {order_id: "$cus_id", value : {$avg:"$cost"}}}])

2. Query your database to get top 10 customers whose orders have the highest value and who have been customers since less than 2 months

db.order.find({"cus_name":1, "cus_id":0}).sort({"cost":1})

3. Query your database to find the sum of all orders received in this month

db.order.aggregate([{$group: {order_id:"$order_id", Total_order:{$sum:"$sum"}}}])

4. Query your database to find customers count in various cities, group them by names of cities

db.customer.aggregate([{$group: {cus_id:"$cus_name", city:{$sum:1}}}])