Project 2
1. Design your ecommerce database in MongoDB with the specifications provided Below :
Customers:-_id-name-email-password-shipping_addres-billing_adress
Product:-_id-name-price-desscription-image-category-brand-segment-tags-in_stack
Orders:-_id-customer_id-product_id-quantity-shipping_details-billing_details-order_status
Admins:-_id-name-email-password
Sellers:-_id-name-email-passwordcity
1.Create your collections for your ecommerce database in MongoDB and fill it with sample information
Products
Customers
Orders
s_cart
Seller
db.createCollection("products")
{ ok: 1 }
db.products.insertMany([
{ "_id": 1, "name": "sofa", "price":10000,"image": "sofa.jpg","description":"its very soft"},
{ "_id": 2, "name": "mobile", "price":20000,"image": "mobile.jpg", "description":"connects through world" },
{ "_id": 3, "name": "ballpoint","price": 100, "image": "ballpoint.jpg","description": "writes well"},
{ "_id": 4, "name": "ring","price": 300,"image":"ring.jpg", "description": "looks good"},
{ "_id": 5, "name": "pen", "price":500, "image": "pen.jpg", "description":"good in quality"},
{ "_id": 6, "name": "laptop", "price":30000, "image": "laptop.jpg","description": "looks slim"},
{ "_id": 7, "name": "mobilecover", "price":150, "image": "mobilecover.jpg", "description":"safes the mobile"},
{ "_id": 8, "name": "toys", "price":5000, "image": "toys.jpg", "description": "plays well"},
{ "_id": 9, "name": "shoe","price":3000,"image":"shoe.jpg", "description": "looks good"},
{ "_id": 10,"name": "shirt", "price":4000, "image": "shirt.jpg","description":"looks great"}
]);
{
acknowledged: true,
insertedIds: {
'0': 1,
'1': 2,
'2': 3,
'3': 4,
'4': 5,
'5': 6,
'6': 7,
'7': 8,
'8': 9,
'9': 10
}
}
db.createCollection("customers")
{ ok: 1 }
db.customers.insertMany([
{"_id":1,"name":"david","email":"[email protected]","city":"bangalore","date":1/11,"pur_value":10000},
{"_id":2,"name":"elvin","email":"[email protected]","city":"chennai","date":4/10,"pur_value":20000},
{"_id":3,"name":"sofia","email":"[email protected]","city":"hosur","date":25/10,"pur_value":100},
{"_id":4,"name":"abdul","email":"[email protected]","city":"mysore","date":5/11,"pur_value":300},
{"_id":5,"name":"monisha","email":"[email protected]","city":"erode","date":15/10,"pur_value":500},
{"_id":6,"name":"deepa","email":"[email protected]","city":"tirupur","date":15/11,"pur_value":30000},
{"_id":7,"name":"hema","email":"[email protected]","city":"bangalore","date":2/10,"pur_value":150},
{"_id":8,"name":"nandy","email":"[email protected]","city":"chennai","date":18/9,"pur_value":5000},
{"_id":9,"name":"sandy","email":"[email protected]","city":"mysore","date":22/10,"pur_value":3000},
{"_id":10,"name":"popsy","email":"[email protected]","city":"karnataka","date":10/11,"pur_value":4000}
]);
{
acknowledged: true,
insertedIds: {
'0': 1,
'1': 2,
'2': 3,
'3': 4,
'4': 5,
'5': 6,
'6': 7,
'7': 8,
'8': 9,
'9': 10
}
}
db.createCollection("orders")
{ ok: 1 }
db.orders.insertMany([
{ "_id":1,"customer_id":1,"items":[{"product_id":1,"quantity":1}],"total":10000,"status":"pending","month":"nov"},
{ "_id":2,"customer_id":2,"items":[{"product_id":2,"quantity":1}],"total":20000,"status":"delivered","month":"oct"},
{ "_id":3,"customer_id":3,"items":[{"product_id":3,"quantity":1}],"total":100,"status":"pending","month":"nov"},
{ "_id":4,"customer_id":4,"items":[{"product_id":4,"quantity":1}],"total":300,"status":"pending","month":"nov"},
{ "_id":5,"customer_id":5,"items":[{"product_id":5,"quantity":1}],"total":500,"status":"delivered","month":"oct"},
{ "_id":6,"customer_id":6,"items":[{"product_id":6,"quantity":1}],"total":30000,"status":"pending","month":"nov"},
{ "_id":7,"customer_id":7,"items":[{"product_id":7,"quantity":1}],"total":150,"status":"delivered","month":"oct"},
{ "_id":8,"customer_id":8,"items":[{"product_id":8,"quantity":1}],"total":5000,"status":"delivered","month":"sep"},
{ "_id":9,"customer_id":9,"items":[{"product_id":9,"quantity":1}],"total":3000,"status":"delivered","month":"oct"},
{ "_id":10,"customer_id":10,"items":[{"product_id":10,"quantity":1}],"total":4000,"status":"pending","month":"nov"}
]);
{
acknowledged: true,
insertedIds: {
'0': 1,
'1': 2,
'2': 3,
'3': 4,
'4': 5,
'5': 6,
'6': 7,
'7': 8,
'8': 9,
'9': 10
}
}
db.createCollection("s_cart")
{ ok: 1 }
db.s_cart.insertMany([
{"_id":1,"customer_id":1,"items":[{"product_id":1,"quantity":1}],"total":10000},
{"_id":2,"customer_id":2,"items":[{"product_id":2,"quantity":1}],"total":20000},
{ "_id":3,"customer_id":3,"items":[{"product_id":3,"quantity":1}],"total":100},
{ "_id":4,"customer_id":4,"items":[{"product_id":4,"quantity":1}],"total":300},
{ "_id":5,"customer_id":5,"items":[{"product_id":5,"quantity":1}],"total":500},
{ "_id":6,"customer_id":6,"items":[{"product_id":6,"quantity":1}],"total":30000},
{ "_id":7,"customer_id":7,"items":[{"product_id":7,"quantity":1}],"total":150},
{ "_id":8,"customer_id":8,"items":[{"product_id":8,"quantity":1}],"total":5000},
{ "_id":9,"customer_id":9,"items":[{"product_id":9,"quantity":1}],"total":3000},
{ "_id":10,"customer_id":10,"items":[{"product_id":10,"quantity":1}],"total":4000}]);
{
acknowledged: true,
insertedIds: {
'0': 1,
'1': 2,
'2': 3,
'3': 4,
'4': 5,
'5': 6,
'6': 7,
'7': 8,
'8': 9,
'9': 10
}
}
db.createCollection("sellers")
{ ok: 1 }
db.sellers.insertMany([
{"_id":1,"name":"Esho","email":"[email protected]","city":"bangalore"},
{"_id":2,"name":"Anna","email":"[email protected]","city":"electronic city"},
{"_id":3,"name":"Krish","email":"[email protected]","city":"mangalore"},
{"_id":4,"name":"gokul","email":"[email protected]","city":"cochi"},
{"_id":5,"name":"sree","email":"[email protected]","city":"kerala"},
{"_id":6,"name":"dhanesh","email":"[email protected]","city":"chennai"},
{"_id":7,"name":"hemanth","email":"[email protected]","city":"bangalore"},
{"_id":8,"name":"banu","email":"[email protected]","city":"kanada"},
{"_id":9,"name":"pathri","email":"[email protected]","city":"coorg"},
{"_id":10,"name":"nithi","email":"[email protected]","city":"erode"}
]);
{
acknowledged: true,
insertedIds: {
'0': 1,
'1': 2,
'2': 3,
'3': 4,
'4': 5,
'5': 6,
'6': 7,
'7': 8,
'8': 9,
'9': 10
}
}
2.Submit all the queries used to create the database
Simple Queries to perform on ecommerce db
1.Write a query to find the average order value for all Customers.
db.customers.aggregate([{$group:{_id:"$name",Averagevalue:{$avg:"$pur_value"}}}]);
[
{ _id: 'popsy', Averagevalue: 4000 },
{ _id: 'sandy', Averagevalue: 3000 },
{ _id: 'elvin', Averagevalue: 20000 },
{ _id: 'nandy', Averagevalue: 5000 },
{ _id: 'hema', Averagevalue: 150 },
{ _id: 'deepa', Averagevalue: 30000 },
{ _id: 'david', Averagevalue: 10000 },
{ _id: 'abdul', Averagevalue: 300 },
{ _id: 'sofia', Averagevalue: 100 },
{ _id: 'monisha', Averagevalue: 500 }
]
(or)
db.customers.aggregate(
[
{
$group:
{
"_id":"_id",
AverageValue:{$avg: "$pur_value"}
}
}
]
);
[ { _id: '_id', AverageValue: 7305 } ]
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.customers.find({date:{$lt:25/10}}).sort({"pur_value":-1}).limit(10);
[
{
_id: 6,
name: 'deepa',
email: '[email protected]',
city: 'tirupur',
date: 1.3636363636363635,
pur_value: 30000
},
{
_id: 2,
name: 'elvin',
email: '[email protected]',
city: 'chennai',
date: 0.4,
pur_value: 20000
},
{
_id: 1,
name: 'david',
email: '[email protected]',
city: 'bangalore',
date: 0.09090909090909091,
pur_value: 10000
},
{
_id: 8,
name: 'nandy',
email: '[email protected]',
city: 'chennai',
date: 2,
pur_value: 5000
},
{
_id: 10,
name: 'popsy',
email: '[email protected]',
city: 'karnataka',
date: 0.9090909090909091,
pur_value: 4000
},
{
_id: 9,
name: 'sandy',
email: '[email protected]',
city: 'mysore',
date: 2.2,
pur_value: 3000
},
{
_id: 5,
name: 'monisha',
email: '[email protected]',
city: 'erode',
date: 1.5,
pur_value: 500
},
{
_id: 4,
name: 'abdul',
email: '[email protected]',
city: 'mysore',
date: 0.45454545454545453,
pur_value: 300
},
{
_id: 7,
name: 'hema',
email: '[email protected]',
city: 'bangalore',
date: 0.2,
pur_value: 150
}
]
3.Query your database to find the sum of all orders received in this month
db.orders.aggregate([{$match:{"month":"nov"}},{$count:"no of orders received in nov"}]);
[ { 'no of orders received in nov': 5 } ]
4.Query your database to find customers count in various cities, group them by names of cities
db.customers.aggregate( [ { $group:{_id:"$city",count:{$sum:1}}}]);
[
{ _id: 'bangalore', count: 2 },
{ _id: 'chennai', count: 2 },
{ _id: 'erode', count: 1 },
{ _id: 'tirupur', count: 1 },
{ _id: 'mysore', count: 2 },
{ _id: 'karnataka', count: 1 },
{ _id: 'hosur', count: 1 }
]
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.getCollection('sellers').aggregate([{
$lookup:
{
from:'orders',
localField:'id',
foreignField:'city',
as:'same city'
}
}]);