Project 2

1. Design your e-commerce database in MongoDB with the specifications provided Below:

use Ecommerce

db.customer.insertMany([ { _id: ObjectId("63a305581c29465330162849"), [
{
_id: ObjectId("63a305581c29465330162849"),
customer_id: 121,
customer_name: 'harry',
customer_city:'delhi',
address: '61/maple road delhi',
order_value: 2000,
email: '[email protected]',
type: [ 'normal_member', 'premium_member' ]
},
{
_id: ObjectId("63a305581c2946533016284a"),
customer_id: 122,
customer_name: 'rohan',
customer_city:'mumbai',
address: '81/ofg road mumbai',
order_value: 3200,
email: '[email protected]',
type: [ 'normal_member', 'premium_member' ]
},
{
_id: ObjectId("63a305581c2946533016284b"),
customer_id: 123,
customer_name: 'jimmy',
customer_city:'hydrebad',
address: '78/ewr road hydrebad',
order_value: 2500,
email: '[email protected]',
type: [ 'normal_member', 'premium_member' ]
},
{
_id: ObjectId("63a305581c2946533016284c"),
customer_id: 124,
customer_name: 'prem',
customer_city:'banglore',
address: '25/grer road banglore',
order_value: 1500,
email: '[email protected]',
type: [ 'normal_member', 'premium_member' ]
}
])

db.product.insertMany([
{
_id: ObjectId("63a30f851c2946533016284d"),
product_id: 221,
categories: { electronic: 'Mobile', cloths: 'Pants' },
brands: { mobile: 'iphone', pants: 'puma' },
segments: [ 'first_time_visitor', 'loyal_visiter' ],
price: { mobile: 10000, pants: 2000 },
quantity: { mobile: 10, pants: 20 }
},
{
_id: ObjectId("63a30f851c2946533016284e"),
product_id: 222,
categories: { electronic: 'tablet', cloths: 'shirt' },
brands: { tablet: 'samsung', shirt: 'levis' },
segments: [ 'first_time_visitor', 'loyal_visiter' ],
price: { mobile: 10000, pants: 2000 },
quantity: { mobile: 10, pants: 20 }
},
{
_id: ObjectId("63a30f851c2946533016284f"),
product_id: 223,
categories: { electronic: 'oven', cloths: 'sweater' },
brands: { oven: 'samsung', sweater: 'levis' },
segments: [ 'first_time_visitor', 'loyal_visiter' ],
price: { oven: 5000, sweater: 1200 },
quantity: { oven: 99, sweater: 256 }
}
])

db.cart.insert([
{
_id: ObjectId("63a324531c29465330162850"),
customer_id: 121,
product_id: 221,
status: 'check out',
quantity: 2,
total: 10200
},
{
_id: ObjectId("63a324531c29465330162851"),
customer_id: 122,
product_id: 222,
status: 'add to cart',
quantity: 1,
total: 10000
},
{
_id: ObjectId("63a324531c29465330162852"),
customer_id: 123,
product_id: 223,
status: 'check out',
quantity: 1,
total: 6200
}
])

db.order.insert([
{
_id: ObjectId("63a32cb61c29465330162853"),
product_id: 221,
shipping: {
customer_name: 'harry',
address: '61/maple road delhi',
delivery_note: 'leave at gate'
},
tracking: {
company: 'utr',
tracking_no: '2545hdh251',
status: 'out for delivery'
},
payment: { method: 'visa', transaction_id: '28481hvgb1844849hy' }
},
{
_id: ObjectId("63a32cb61c29465330162854"),
product_id: 223,
shipping: {
customer_name: 'jimmy',
address: '78/ewr road hydrebad',
delivery_note: 'rang bell'
},
tracking: { company: 'utr', tracking_no: '2545hdh859', status: 'packaging' },
payment: {
method: 'cash on delivery',
transaction_id: '28481hvgb184jyigib548'
}
}
])

db.seller.insert([
{
_id: ObjectId("63a32f821c29465330162855"),
seller_id: 321,
address: '658/gfr road lucknow',
product_id: 221,
add_product: [ 'electronic', 'cloths' ],
status: 'order recieved'
},
{
_id: ObjectId("63a32f821c29465330162856"),
seller_id: 322,
address: '85/yhg road lucknow',
product_id: 222,
add_product: [ 'electronic', 'cloths' ],
status: 'pending payment'
},
{
_id: ObjectId("63a32f821c29465330162857"),
seller_id: 323,
address: '471/gvuy road lucknow',
product_id: 223,
add_product: [ 'electronic', 'cloths' ],
status: 'order recived'
}
])

db.admin.insert([
{
_id: ObjectId("63a3394d1c29465330162858"),
admin_id: 421,
admin_name: 'gugly',
add_product: [ 'electronic', 'cloths' ],
email: '[email protected]'
},
{
_id: ObjectId("63a3394d1c29465330162859"),
admin_id: 422,
admin_name: 'simmy',
add_product: [ 'electronic', 'cloths' ],
email: '[email protected]'
},
{
_id: ObjectId("63a3394d1c2946533016285a"),
admin_id: 423,
admin_name: 'gatwar',
add_product: [ 'electronic', 'cloths' ],
email: '[email protected]'
}
])

Simple Queries to perform on ecommerce db

Q. No 1:

db.customer.aggregate([{$group:{_id:'order_value', avg:{$avg:'$order_value'}}}]);

Q. No 2:

db.customer.find().sort(customer_id:1).limit(3)

Q. No 3:

db.order.aggregate([{$group:{_id:'Total_cost', sum:{$sum:'$product_cost'}}}]);

Q. No 4:

db.customers.aggregate([{$group:{_id:'customer_city', $match:{customer_city}}}]);

Q. No 5:

db.seller.aggregate([{$group:{_id: "seller_city", $match:{'customer_city'}}}, { $sort: { $count: { "order_qty"} : -1 }}]);