Project 2

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

db.customer.insertMany([
{
_id: 1,
customer_id: 1,
customer_fname:'Md Irfan',
customer_lname:'Ansari',
customer_dob:1998,
customer_address:'Dhanbad',
customer_city:'Dhanbad',
is_premium: 1,
customer_contactno:8266907591,
customer_email:'[email protected]'
},
{
_id: 2,
customer_id: 2,
customer_fname:'fname1',
customer_lname:'lname2',
customer_dob:1996,
customer_address:'address1',
customer_city:'city1',
is_premium: 0,
customer_contactno:7200145869,
customer_email:'[email protected]'
},
{
_id: 3,
customer_id: 3,
customer_fname:'fname2',
customer_lname:'lname2',
customer_dob:1995,
customer_address:'address2',
customer_city:'city2',
is_premium: 0,
customer_contactno:6200586945,
customer_email:'[email protected]'
}
]);

db.cart.insertMany([
{
_id: 1,
cart_item_qty: 2,
product_id: 102,
customer_id: 1
},
{
_id: 2,
cart_item_qty: 1,
product_id: 201,
customer_id: 2
},
{
_id: 3,
cart_item_qty: 1,
product_id: 202,
customer_id: 3
}
,
{
_id: 4,
cart_item_qty: 1,
product_id: 501,
customer_id: 2
}
,
{
_id: 5,
cart_item_qty: 1,
product_id: 401,
customer_id: 3
}
]);

db.categories.insertMany([
{
_id:1,
categroy_name: 'Home Appliances',

sub_categories: ['A.C', 'Fridge', 'Oven']
},
{
_id:2,
categroy_name: 'Computers, Laptop and Mobiles',
sub_categories: ['Computers', 'Laptops', 'Mobiles']
},
{
_id:3,
categroy_name: 'Books',
sub_categories: ['Fiction', 'Biography', 'Fantasy Fiction','Non-Fiction']
},
{
_id:4,
categroy_name: 'Fasion',
sub_categories: ['mens', 'womens']
},
{
_id:5,
categroy_name: 'Games and Consoles',
sub_categories: ['titles', 'console']
}
]);

db.products.insertMany([
{
_id:1,
product_name: 'Laptop',
product_model: 'C201',
product_brand: 'HP',
product_segments: ['Computers', 'Laptops'],
product_cost: 100000,
categroy_name: 'Computers, Laptop and Mobiles'
},
{
_id:2,
product_name: 'Smart Phone',
product_model: 'C202',
product_brand: 'Google',
product_segments: ['Mobile', 'Android'],
product_cost: 60000,
categroy_name: 'Computers, Laptop and Mobiles'
},
{
_id:3,
product_name: 'Iron Man 2004',
product_model: 'C501',
product_segments: ['Book', 'Novels'],
product_cost: 1500,
categroy_name: 'Books'
},
{
_id:4,
product_name: 'Captain Marvel',
product_model: 'C701',
product_segments: ['pokemon', 'gen9'],
product_cost: 4500,
categroy_name: 'Games and Consoles'
},
{
_id:5,
product_name: 'Captain America',
product_model: 'C702',
product_segments: ['console'],
product_cost: 30000,
categroy_name: 'Games and Consoles'
},
]);

db.orders.insertMany([
{
_id: 1,
product_name: 'Inception',
customer_id: 1,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-15')
},
{
_id: 2,
product_name: 'Martian',
customer_id: 2,
order_qty: 2,
order_status: 'Dispatched',
order_date: new Date('2022-11-27')
},
{
_id: 3,
product_name: 'Aquaman',
customer_id: 3,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-10')
},
{
_id: 4,
product_name: 'Smart Phone',
customer_id: 1,
order_qty: 1,
order_status: 'cancelled',
order_date: new Date('2022-11-18')
},
{
_id: 5,
product_name: 'Laptop',
customer_id: 1,
order_qty: 1,
order_status: 'Dispatched',
order_date: new Date('2022-11-01')
}
]);

db.seller.insertMany([
{
_id: 1,
seller_name: 'Seller1',
seller_business: 'business1',
seller_mobileno: 6206548965,
seller_address: 'Connaught place',
seller_city: 'Mumbai'
},
{
_id: 2,
seller_name: 'Seller2',
seller_business: 'business2',
seller_mobileno: 6200548965,
seller_address: 'karnal',
seller_city: 'Punjab'
}
]);

db.catlog.insertMany([
{
seller_name: 'Seller1',
product_name: 'product1',
product_qty: 10,
},
{
seller_name: 'Seller2',
product_name: 'product2',
product_qty: 5,
},
{
seller_name: 'Seller3',
product_name: 'Product3',
product_qty: 20,
},
{
seller_name: 'Seller4',
product_name: 'product4',
product_qty: 15,
}
]);

Simple Queries to perform on ecommerce db

Q. No 1:

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

Q. No 2:

db.customers.aggregate([{$sort: {purchase_amount: 1}}]);

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 }}]);