Project 2

1. Design your ecommerce database in MongoDB with the specifications provided Below :

1. Every Customer would register on the ecommerce website with all their personal information
2. Each Customer has a type of being normal or premium member
3. The customer should be able to view products and add them to cart
4. Products can belong to multiple categories, brands and segments.
5. Each category can have subcategories as well.
6. Each products can also have multiple tags associated with it.\
7. From the cart, the customer should be able to check out and make payment and his/her order would be booked which the customer can track
8. The admin of the ecommerce and the seller both can add products under multiple categories, segments and tags
9. The seller would receive the orders and should be able to fulfil them.
10. The order shipping details and the billing information are very important things to be stored in the database as well.
11. Every Seller would register on the ecommerce website with all their personal information.

1.ans:-

a)
db.customer.insertMany([
{
_id: 1,
customer_id: 1,
customer_fname:'Laxmi',
customer_lname:'Bhuyan',
customer_dob:2002,
customer_address:'Ganjam',
customer_city:'Bhanjanagar',
customer_contactno:1234657890,
customer_email:'[email protected]'
},
{
_id: 2,
customer_id: 2,
customer_fname:'Liza',
customer_lname:'Dalei',
customer_dob:2000,
customer_address:'Tanarada',
customer_city:'Bhanjanagar',
customer_contactno:2345678901,
customer_email:'[email protected]'
},
{
_id: 3,
customer_id: 3,
customer_fname:'Anchal',
customer_lname:'Dshoza',
customer_dob:2000,
customer_address:'Nagpur',
customer_city:'Maharastra',
customer_contactno:5678901234,
customer_email:'[email protected]'
}
]);
--------------------------------------------------------

b)

db.customer.insertMany([
{
_id: 1,
customer_id: 1,
customer_fname:'Laxmi',
customer_lname:'Bhuyan',
customer_dob:2002,
customer_address:'Ganjam',
customer_city:'Bhanjanagar',
premium_member: 'yes',
normal_member:'no',
customer_contactno:1234657890,
customer_email:'[email protected]'
},
{
_id: 2,
customer_id: 2,
customer_fname:'Liza',
customer_lname:'Dalei',
customer_dob:2000,
customer_address:'Tanarada',
customer_city:'Bhanjanagar',
premium_member: 'no',
normal_member:'yes',
customer_contactno:2345678901,
customer_email:'[email protected]'
},
{
_id: 3,
customer_id: 3,
customer_fname:'Anchal',
customer_lname:'Dshoza',
customer_dob:2000,
customer_address:'Nagpur',
customer_city:'Maharastra',
premium_member: 'yes',
normal_member:'no',
customer_contactno:5678901234,
customer_email:'[email protected]'
}
]);
-----------------------------------------------

c)
db.product.insertMany([
{
_id: 1,
customer_id: 1,
product:_id:201,
product_name:'shoes',
},
{
_id: 2,
customer_id: 2,
product:_id:202,
product_name:'clothes'
},
{
_id: 3,
customer_id: 2,
product:_id:203,
product_name:'phone'
},

{
_id: 4,
customer_id: 4,
product:_id:204,
product_name:'T-shirt'
}
]);

db.cart.insertMany([
{
_id: 1,
customer_id: 1,
product:_id:201,
product_name:'shoes',
add_cart:'1'
},
{
_id: 2,
customer_id: 2,
product:_id:202,
product_name:'clothes',
add_cart:'2'
},
{
_id: 3,
customer_id: 2,
product:_id:203,
product_name:'phone'
add_cart:'1'
},

{
_id: 4,
customer_id: 4,
product:_id:204,
product_name:'T-shirt'
add_cart:'3'
}
]);
------------------------------------

d)
db.product.insertMany([
{
_id:1,
categroy_name: 'Home Applicances',
brand_name:'LG',
segements:'Refrigeraor',
},
{
_id:2,
categroy_name: 'Books',
brand_name:'jungle book',
segements:'mogle'
},
{
_id:3,
categroy_name: 'Fasion',
brand_name:'mogle'
segements:'shirts'
},
{
_id:4,
categroy_name: 'Games and Consoles',
brand_name:'karfton'
segements:'bgmi'
}
]);
----------------------------------------------

e)
db.categories.insertMany([
{
_id:1,
categroy_name: 'Home Applicances',
sub_categories: ['Refrigeraor', 'cleaning appliance']

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

f)
db.products.insertMany([
{
_id:1,
product_name: 'Laptop',
product_model: 'C201',
product_brand: 'HP',
tags: ['Computers', 'Laptops'],
product_cost: 100000,
categroy_name: 'Computers, Laptop and Mobiles'
},
{
_id:2,
product_name: 'Smart Phone',
product_model: 'C202',
product_brand: 'Google',
tags: ['Mobile', 'Android'],
product_cost: 60000,
categroy_name: 'Computers, Laptop and Mobiles'
},
{
_id:3,
product_name: 'Lord of the Rings',
product_model: 'C501',
tags: ['Book', 'Novels'],
product_cost: 1500,
categroy_name: 'Books'
},
{
_id:4,
product_name: 'Pokemon Scarlet',
product_model: 'C701',
tags: ['pokemon', 'gen9'],
product_cost: 4500,
categroy_name: 'Games and Consoles'
},
{
_id:5,
product_name: 'Nintendo Switch',
product_model: 'C702',
tags: ['console'],
product_cost: 30000,
categroy_name: 'Games and Consoles'
},
]);

-----------------------------------------

g)
db.cart.insertMany([
{
_id: 1,
customer_id: 1,
product:_id:201,
product_name:'shoes',
add_cart:'1',
check_out:'order book',
make_payment:'pay'
},
{
_id: 2,
customer_id: 2,
product:_id:202,
product_name:'clothes',
add_cart:'2',
check_out:'not book'
make_payment:'pay'
},
{
_id: 3,
customer_id: 2,
product:_id:203,
product_name:'phone'
add_cart:'1',
check_out:'order book',
make_payment:'pay'
},

{
_id: 4,
customer_id: 4,
product:_id:204,
product_name:'T-shirt',
add_cart:'3',
check_out:'not book',
make_payment:'not pay'
}
]);
----------------------------------

h)
db.ecommerce.insertMany([
{
_id: 1,
customer_id: 1,
product:_id:201,
categroy_name: 'Home Applicances',
sub_categories: ['Refrigeraor', 'cleaning appliance']
add_product:'1'
},
{
_id: 1,
customer_id: 2,
product:_id:202,
categroy_name: 'Fasion',
sub_categories: ['mens', 'womens']
add_product:'2'
},
{
_id: 3,
customer_id: 3,
product:_id:203,
categroy_name: 'Games and Consoles',
sub_categories: ['titles', 'console']
add_product:'0'
}
]);

--------------------------------------

i)
db.orders.insertMany([
{
_id: 1,
product_name: 'Nintendo Switch',
customer_id: 1,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-15'),
order:'receive'

},
{
_id: 2,
product_name: 'Pokemon Scarlet',
customer_id: 2,
order_qty: 2,
order_status: 'Dispatched',
order_date: new Date('2022-11-27'),
order:' Not receive'
},
{
_id: 3,
product_name: 'Lord of the Rings',
customer_id: 3,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-10'),
order:'receive'
},
{
_id: 4,
product_name: 'Smart Phone',
customer_id: 1,
order_qty: 1,
order_status: 'cancelled',
order_date: new Date('2022-11-18'),
order:'cancel'
},
{
_id: 5,
product_name: 'Laptop',
customer_id: 1,
order_qty: 1,
order_status: 'Dispatched',
order_date: new Date('2022-11-01')
order:'Not receive'
}
]);
-------------------------------------------

j)
db.seller.insertMany([
{
_id: 1,
seller_name: 'shiva kashyap',
seller_business: 'Gada Electronics',
seller_mobileno: 4567891230,
seller_address: 'Andheri',
seller_city: 'nagpur',
product_buy:'shirt',
oder_qty:'1',
total_bill:2000,
order_status: 'Delivered',
order_date: new Date('2022-11-10')
},
{
_id: 2,
seller_name: 'xyz Harvard',
seller_business: 'ABC Sports and Fasion',
seller_mobileno: 7890123456,
seller_address: 'Connaught Place',
seller_city: 'Delhi',
product_buy:'soft toy',
oder_qty:'2',
total_bill:5000,
order_status: 'Dispatched',
order_date: new Date('2022-11-01')
}
]);
------------------------------------------------

k)
db.seller.insertMany([
{
_id: 1,
seller_name: 'shiva kashyap',
seller_business: 'Gada Electronics',
seller_mobileno: 4567891230,
seller_address: 'Andheri',
seller_city: 'Mumbai'
},
{
_id: 2,
seller_name: 'xyz Harvard',
seller_business: 'ABC Sports and Fasion',
seller_mobileno: 7890123456,
seller_address: 'Connaught Place',
seller_city: 'Delhi'
}
]);

1.Create your collections for your ecommerce database in MongoDB and fill it with sample information

2.Submit all the queries used to create the database

db.customer.insertMany([
{
_id: 1,
customer_id: 1,
customer_fname:'Laxmi',
customer_lname:'Bhuyan',
customer_dob:2002,
customer_address:'Ganjam',
customer_city:'Bhanjanagar',
is_premium: 1,
customer_contactno:1234657890,
customer_email:'[email protected]'
},
{
_id: 2,
customer_id: 2,
customer_fname:'Liza',
customer_lname:'Dalei',
customer_dob:2000,
customer_address:'Tanarada',
customer_city:'Bhanjanagar',
is_premium: 0,
customer_contactno:2345678901,
customer_email:'[email protected]'
},
{
_id: 3,
customer_id: 3,
customer_fname:'Anchal',
customer_lname:'Dshoza',
customer_dob:2000,
customer_address:'Nagpur',
customer_city:'Maharastra',
is_premium: 0,
customer_contactno:5678901234,
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 Applicances',
},
{
_id:2,
categroy_name: 'Computers, Laptop and Mobiles',
sub_categories: ['Computers', 'Laptops', 'Mobiles']
},
{
_id:3,
categroy_name: 'Books',
sub_categories: ['Fiction', 'Biography', 'Fantacy 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: 'Lord of the Rings',
product_model: 'C501',
product_segments: ['Book', 'Novels'],
product_cost: 1500,
categroy_name: 'Books'
},
{
_id:4,
product_name: 'Pokemon Scarlet',
product_model: 'C701',
product_segments: ['pokemon', 'gen9'],
product_cost: 4500,
categroy_name: 'Games and Consoles'
},
{
_id:5,
product_name: 'Nintendo Switch',
product_model: 'C702',
product_segments: ['console'],
product_cost: 30000,
categroy_name: 'Games and Consoles'
},
]);

db.orders.insertMany([
{
_id: 1,
product_name: 'Nintendo Switch',
customer_id: 1,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-15')
},
{
_id: 2,
product_name: 'Pokemon Scarlet',
customer_id: 2,
order_qty: 2,
order_status: 'Dispatched',
order_date: new Date('2022-11-27')
},
{
_id: 3,
product_name: 'Lord of the Rings',
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: 'Ankit Chaowda',
seller_business: 'Gada Electronics',
seller_mobileno: 4567891230,
seller_address: 'Andheri',
seller_city: 'Mumbai'
},
{
_id: 2,
seller_name: 'Bakely Harvard',
seller_business: 'ABC Sports and Fasion',
seller_mobileno: 7890123456,
seller_address: 'Connaught Place',
seller_city: 'Delhi'
}
]);

db.catlog.insertMany([
{
seller_name: 'Ankit Chaowda',
product_name: 'Smart Phone',
product_qty: 10,
},
{
seller_name: 'Ankit Chaowda',
product_name: 'Laptop',
product_qty: 5,
},
{
seller_name: 'Bakely Harvard',
product_name: 'Pokemon Scarlet',
product_qty: 20,
},
{
seller_name: 'Bakely Harvard',
product_name: 'Nintendo Switch',
product_qty: 15,
}
]);

Simple Queries to perform on ecommerce db
1.Write a query to find the average order value for all Customers.

1.ans:- db.products.aggregate([{$group:{_id:'order':{$avg:'$order'}}}])

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

2.ans:-

db.orders.insertMany([
{ _id: 1, product_id: 101, status: 1 },
{ _id: 2, product_id: 102, status: 1 },
{ _id: 3, product_id: 103, status: 1 },
{ _id: 4, product_id: 104, status: 1 },
{ _id: 5, product_id: 105, status: 1 },
{ _id: 6, product_id: 106, status: 1 },
{ _id: 7, product_id: 107, status: 1 },
{ _id: 8, product_id: 108, status: 1 },
{ _id: 9, product_id: 109, status: 1 },
{ _id: 10, product_id: 1010, status: 1 },
]);
db.products.insertMany([
{_id:101,product_name:'cloth', order_place:'200 months before' membre:200},
{_id:102,product_name:'soft toy', order_place:'1 days before ' membre:90},
{_id:103,product_name:'phone', order_place:'30 days before' membre:30},
{_id:101,product_name:'watch', order_place:'12 days before' membre:150},
]);

db.orders.find().sort({order:10}).limit(10)
db.products.find($match:{membre:{$lt:200}})

3)Query your database to find the sum of all orders received in this month

3.ans:- db.order.aggregate([{$group:{_id:'Total_order': {$sum:'$order'}}}])

4)Query your database to find customers count in various cities, group them by names of cities

4.ans:- db.customers.find({$count:"customer_city","customer_name"})

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.

5.ans:-

db.sellers.aggregate([$group:{_id:'seller_name':{$match:'customer_city'}}}])

db.orders.aggregate([$sort :{order: 1}:{$match:'customer_city'}}}])