Project 2
==> Below i attached the JSON file for all inserted data in file format.
2. Submit all the queries used to create the database
creating database :
use Ecommerce
create Collection Customers and insert data in it:
db.Customers.insertOne({firstName:"Vaibhav", lastName:"Gopnar", Mobile:"8459056683", Address:{Houseno:3, city:"Pune", State:"Maharashtra", Pincode:"345554", CustomerType:"Premium"}})
db.Customers.insertMany([{firstName:"Ganesh", lastName:"Pande", Mobile:"4566556683", Address:{Houseno:2, city:"Nanded", State:"Maharashtra", Pincode:"435634", CustomerType:"Premium"}}, {firstName:"Abhi", lastName:"Sharma", Mobile:"9089231456", Address:{FlatNo:5, city:"Bangalore", State:"TamilNadu", Pincode:"674312"}}, {firstName:"Pallavi", lastName:"Naidu", Mobile:"9989483484", Address:{city:"Hyderabad", Street:"Charminar Road", State:"Telangana", Pincode:"555643"}}, {firstName:"Rajesh", lastName:"Kohli", Mobile:"4534232323", Address:{city:"Chennai", State:"TamilNadu", Pincode:"857545"}}])
create Collection Seller and insert data in it :
db.Seller.insertMany([{sellerName:"RAdisTxtiles", Mobile:"9876890087", email:"[email protected]",Products:[{ProductName:"MacBook"},{ProductName:"Lazer Shues"},{ProductName:"Rebion SunGlasses"}]}, {sellerName:"Radhe AutoMobile", Mobile:"4567393009", email:"[email protected]",Products:"T-Shirt"}, {sellerName:"BDGroup", Mobile:"345554545", email:"[email protected]",Products:[{productName:"Watch"},{productName:"Belt"}]}, {sellerName:"Kirti",Mobile:"0897989766",email:"[email protected]", Products:[{productName:"SoyaOil"},{productName:"Pamolyne"}]}, {sellerName:"Teene", Mobile:"09340934093", email:"[email protected]", Products:{ProductName:"Healthfit"}}, {sellerName:"Sony",Mobile:"8888832321",Products:[{ProuctName:"Sounds"},{ProductName:"LED Display"}], email:"[email protected]"}])
create Collection Products and insert data in it :
db.Products.insertOne({ProductName:"macbook", color:"black", sellerid:"6768cfc5cbf1f6e4ed25c95a", quantity:3, Prize:"70000",brand:"Apple", Category:"Laptop", segments:"", tags:"Good processor, good for gaming "})
db.Products.insertOne({ProductName:"Shoes", color:"black", sellerid:"6768cfc5cbf1f6e4ed25c95a", quantity:10, Prize:"1000",brand:"Woodland", Category:"mens shoes", segments:"", tags:"Reliable, Rough and tough "})
db.Products.insertOne({ProductName:"Sun glasses", color:"yellow-black", sellerid:"6768cfc5cbf1f6e4ed25c95b", quantity:4, Prize:"3000",brand:"Rey Ban", Category:"sunglasses", segments:[{Type:"polarised"}, {Design:"Rectangle"}], tags:"Reliable, Fashinable "})
create Collection Cart :
db.cart.insertOne({quantity:3, total:120000,Products:[{P_Name:"macbook", color:"black", brand:"Apple"},{P_Name:"Watch", color:"Silver",brand:"Titan"},{P_Name:"Vivo29", color:"Blue", brand:"Vivo"}]})
db.cart.insertOne({customer_ID:"6368bd44cbf1f6e4ed25c956", quantity:4, total:10000,Products:[{P_Name:"sunglasses", color:"yellow-black", brand:"Rey Ban"},{P_Name:"Shoes", color:"Red",brand:"Nike"}]})
create collection Payment:
db.Payments.insertOne({customer_id:"6368bd44cbf1f6e4ed25c956",status:"Verified",getway:"strip",type:"credit",amount:NumberDecimal(400.00),card:{brand:"Visa", pan:"v473ureh64",expirymonth:3,expiryyear:2040,cvv:123}})
db.Payments.insertOne({customer_id:"6368bd44cbf1f6e4ed25c957",status:"Verified",getway:"epay",type:"debit",amount:NumberDecimal(290.00)})
create Collection Orders - all the detaile of shipment, tracking:
db.Orders.insertOne({created_on:new ISODate("2022-04-10T08:40:25.232Z"), shipping:{customer_id:"6368bd44cbf1f6e4ed25c957",customerName:"Pallavi",address:"flatno-5", city:"Hyderabad",state:"Telangana",delivery:"leave at the gate", tracking:{company:"ups", tracking_number:"83748347x8sa", status:"On the way", expectationtime:new ISODate("2022-12-11T10:30:34.666Z")},}, payment:{method:"epay", payment_id:"636934d1cbf1f6e4ed25c968"}, products:{productName:"macbook", quantity:2, cost:120000}})
Simple Queries to perform on ecommerce db
1.Write a query to find the average order value for all Customers.
==>
db.customer.aggregate([{$group: {_id:null, avg_orders:{$avg:"$order_quantity"}}}])
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.customer.aggregate([{$group: {_id:null, max_orders:{$max:"$order_quantity"},{$count:"customerid"}}}])
3.Query your database to find the sum of all orders received in this month
==> db.Orders.aggregate([{month:1, $group: {sum_val:{$sum:"$orders"}}}])
4.Query your database to find customers count in various cities, group them by names of cities
==>
db.Customers.aggregate( [ { cities: { $sum: { $city: [ "$Bangalore", "$Chennai" ] }, $orderby: { city : 1 } }, customers:{ $count:"firstname"} } ])5.Query your database to get all the sellers who have base in the same city in which the customers who ordered their products. Order them in the order of highest number of products sold to customers in same city to lowest.
==> db.Seller.aggregate(
[ $groupby: {sellerName:{$in{"Bangalore"}, customerName:{$in{sellerid:"34555324"}}}, $orderby:{ProductQuantity:{$gt:10}}}
]
)