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-city
- Create your collections for your ecommerce database in MongoDB and fill it with sample information
Products
Customers
Orders
Cart
Seller
db.createCollection("product")
{ ok: 1 }
db.product.insertMany([
{ "_id": 1, "name": "watch", "price": 999, "image": "watch.jpg", "description": "Time is priceless so don't waste your time" },
{ "_id": 2, "name": "mobilecover", "price": 200, "image": "mobilecover.jpg", "description": "It safes the mobile from damage" },
{ "_id": 3, "name": "pen", "price": 100, "image": "pen.jpg", "description": "writes well"},
{ "_id": 4, "name": "bag", "price": 300, "image": "bag.jpg", "description": "carry heavy weight"},
{ "_id": 5, "name": "accessories", "price":500, "image": "acc.jpg", "description": "good in quality"},
{ "_id": 6, "name": "laptop", "price":30000, "image": "laptop.jpg", "description": "looks slim"},
{ "_id": 7, "name": "mobile", "price":15000, "image": "mobile.jpg", "description":"with good processor"},
{ "_id": 8, "name": "TV", "price":40000, "image": "tv.jpg", "description": "oled display"},
{ "_id": 9, "name": "shoe","price":3000,"image":"shoe.jpg", "description": "looks good"},
{ "_id": 10,"name": "bracklet", "price":4000, "image": "brack.jpg","description":"looks shine"}
]);
{
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("customer")
{ ok: 1 }
db.customer.insertMany([
{"_id":1,"name":"Esho","email":"[email protected]","address":"247 cross road","city":"bangalore","date":1/11,"pur_value":999},
{"_id":2,"name":"Anna","email":"[email protected]","address":"255 bridge road","city":"electronic city","date":4/10,"pur_value":200},
{"_id":3,"name":"Krish","email":"[email protected]","address":"143 first street road","city":"mangalore","date":25/10,"pur_value":100},
{"_id":4,"name":"gokul","email":"[email protected]","address":"245 cross road","city":"cochi","date":5/11,"pur_value":300},
{"_id":5,"name":"sree","email":"[email protected]","address":"246 cross road","city":"kerala","date":15/10,"pur_value":500},
{"_id":6,"name":"dhanesh","email":"[email protected]","address":"248 cross road","city":"chennai","date":15/11,"pur_value":30000},
{"_id":7,"name":"hemanth","email":"[email protected]","address":"249 cross road","city":"bangalore","date":2/10,"pur_value":15000},
{"_id":8,"name":"banu","email":"[email protected]","address":"241 cross road","city":"kanada","date":18/9,"pur_value":40000},
{"_id":9,"name":"pathri","email":"[email protected]","address":"242 cross road","city":"coorg","date":22/10,"pur_value":3000},
{"_id":10,"name":"nithi","email":"[email protected]","address":"243 cross road","city":"erode","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("order")
{ ok: 1 }
db.order.insertMany([
{ "_id":1,"customer_id":1,"items":[{"product_id":1,"quantity":1}],"total":999,"status":"pending","month":"nov"},
{ "_id":2,"customer_id":2,"items":[{"product_id":2,"quantity":1}],"total":200,"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":15000,"status":"delivered","month":"oct"},
{ "_id":8,"customer_id":8,"items":[{"product_id":8,"quantity":1}],"total":40000,"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("cart")
{ ok: 1 }
db.cart.insertMany([
{"_id":1,"customer_id":1,"items":[{"product_id":1,"quantity":1}],"total":999},
{"_id":2,"customer_id":2,"items":[{"product_id":2,"quantity":1}],"total":200},
{ "_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":15000},
{ "_id":8,"customer_id":8,"items":[{"product_id":8,"quantity":1}],"total":40000},
{ "_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("seller")
{ ok: 1 }
db.seller.insertMany([
{"_id":1,"name":"david","email":"[email protected]","city":"bangalore"},
{"_id":2,"name":"elvin","email":"[email protected]","city":"chennai"},
{"_id":3,"name":"sofia","email":"[email protected]","city":"hosur"},
{"_id":4,"name":"abdul","email":"[email protected]","city":"mysore"},
{"_id":5,"name":"monisha","email":"[email protected]","city":"erode"},
{"_id":6,"name":"deepa","email":"[email protected]","city":"tirupur"},
{"_id":7,"name":"hema","email":"[email protected]","city":"bangalore"},
{"_id":8,"name":"nandy","email":"[email protected]","city":"chennai"},
{"_id":9,"name":"sandy","email":"[email protected]","city":"mysore"},
{"_id":10,"name":"popsy","email":"[email protected]","city":"karnataka"}
]);
{
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
- Write a query to find the average order value for all Customers.
db.customer.aggregate([{$group:{_id:"$name",Averagevalue:{$avg:"$pur_value"}}}]);
[
{ _id: 'nithi', Averagevalue: 4000 },
{ _id: 'Krish', Averagevalue: 100 },
{ _id: 'Anna', Averagevalue: 200 },
{ _id: 'gokul', Averagevalue: 300 },
{ _id: 'pathri', Averagevalue: 3000 },
{ _id: 'hemanth', Averagevalue: 15000 },
{ _id: 'sree', Averagevalue: 500 },
{ _id: 'Esho', Averagevalue: 999 },
{ _id: 'banu', Averagevalue: 40000 },
{ _id: 'dhanesh', Averagevalue: 30000 }
]
(or)
db.customer.aggregate(
[
{
$group:
{
"_id":"_id",
AverageValue:{$avg: "$pur_value"}
}
}
]
);
[ { _id: '_id', AverageValue: 9409.9 }
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.find({date:{$lt:25/10}}).sort({"pur_value":-1}).limit(10);
[
{
_id: 8,
name: 'banu',
email: '[email protected]',
address: '241 cross road',
city: 'kanada',
date: 2,
pur_value: 40000
},
{
_id: 6,
name: 'dhanesh',
email: '[email protected]',
address: '248 cross road',
city: 'chennai',
date: 1.3636363636363635,
pur_value: 30000
},
{
_id: 7,
name: 'hemanth',
email: '[email protected]',
address: '249 cross road',
city: 'bangalore',
date: 0.2,
pur_value: 15000
},
{
_id: 10,
name: 'nithi',
email: '[email protected]',
address: '243 cross road',
city: 'erode',
date: 0.9090909090909091,
pur_value: 4000
},
{
_id: 9,
name: 'pathri',
email: '[email protected]',
address: '242 cross road',
city: 'coorg',
date: 2.2,
pur_value: 3000
},
{
_id: 1,
name: 'Esho',
email: '[email protected]',
address: '247 cross road',
city: 'bangalore',
date: 0.09090909090909091,
pur_value: 999
},
{
_id: 5,
name: 'sree',
email: '[email protected]',
address: '246 cross road',
city: 'kerala',
date: 1.5,
pur_value: 500
},
{
_id: 4,
name: 'gokul',
email: '[email protected]',
address: '245 cross road',
city: 'cochi',
date: 0.45454545454545453,
pur_value: 300
},
{
_id: 2,
name: 'Anna',
email: '[email protected]',
address: '255 bridge road',
city: 'electronic city',
date: 0.4,
pur_value: 200
}
]
3.Query your database to find the sum of all orders received in this month
db.order.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.customer.aggregate( [ { $group:{_id:"$city",count:{$sum:1}}}]);
[
{ _id: 'mangalore', count: 1 },
{ _id: 'electronic city', count: 1 },
{ _id: 'chennai', count: 1 },
{ _id: 'kanada', count: 1 },
{ _id: 'coorg', count: 1 },
{ _id: 'bangalore', count: 2 },
{ _id: 'erode', count: 1 },
{ _id: 'cochi', count: 1 },
{ _id: 'kerala', 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('seller').aggregate([{
$lookup:
{
from:'order',
localField:'id',
foreignField:'city',
as:'same city'
}
}]);