BlogFor BusinessTalk to a counsellor
CoursesIIT ProgramsPlacementsLog in
BlogFor Business
Talk to a counsellor
Challenge

Project 2

RRaaghavan R4 Dec 2022

{
"address": "123 Main Street",
"city": "New York",
"state": "NY",
"zip": "10001",
"country": "USA",
"type": "normal"
}

Product Collection

{
"_id": ObjectId("5f2049fa78e7164ef8e8d140"),
"name": "Product 1",
"price": 100,
"categories": [
ObjectId("5f1fcc4b78e7164ef8e8d12f"),
ObjectId("5f1fcc4b78e7164ef8e8d12e")
],
"brand": ObjectId("5f1fcc4b78e7164ef8e8d12d"),
"segments": [
ObjectId("5f1fcc4b78e7164ef8e8d12c")
],
"tags": [
ObjectId("5f1fcc4b78e7164ef8e8d12b")
]
}

Category Collection

{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d12f"),
"name": "Category 1",
"parent": null
}

Brand Collection

{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d12d"),
"name": "Brand 1"
}

Segment Collection

{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d12c"),
"name": "Segment 1"
}

Tag Collection

{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d12b"),
"name": "Tag 1"
}

Admin Collection

{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d12a"),
"name": "Admin 1",
"email": "[email protected]",
"password": "123456"
}

Seller Collection

{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d129"),
"name": "Seller 1",
"email": "[email protected]",
"password": "123456"
}

Cart Collection

{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d128"),
"customer": ObjectId("5f1fcc4b78e7164ef8e8d127"),
"products": [
ObjectId("5f1fcc4b78e7164ef8e8d126")
],
"total": 100
}

Order Collection

{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d125"),
"customer": ObjectId("5f1fcc4b78e7164ef8e8d124"),
"products": [
ObjectId("5f1fcc4b78e7164ef8e8d123")
],
"total": 100,
"billing_address": "123 Main Street",
"billing_city": "New York",
"billing_state": "NY",
"billing_zip": "10001",
"billing_country": "USA",
"shipping_address": "123 Main Street",
"shipping_city": "New York",
"shipping_state": "NY",
"shipping_zip": "10001",
"shipping_country": "USA",
"status": "pending"
}

{
_id: 1,
customer_id: 1,
product:_id:201,
product_name:'e-gadgets',
add_cart:'4',

check_out:'order book',
make_payment:'pay'
},
{
_id: 2,
customer_id: 2,
product:_id:202,
product_name:'laptops',
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', 'mixergrinder']
add_product:'1'
},
{
_id: 1,
customer_id: 2,
product:_id:202,
categroy_name: 'Fasion',
sub_categories: ['mens', 'womens', 'child']
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: 'Legrand Switch',
customer_id: 1,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-15'),
order:'receive'

},
{
_id: 2,
product_name: 'beyblade toy',
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 traders',
seller_business: 'GG Electronics',
seller_mobileno: 4567891230,
seller_address: 'Andhra',
seller_city: 'pondicherry',
product_buy:'shirt',
oder_qty:'1',
total_bill:2000,
order_status: 'Delivered',
order_date: new Date('2022-11-10')
},
{
_id: 2,
seller_name: 'xyz gamers',
seller_business: 'ABC Sports and Fasion',
seller_mobileno: 7890123456,
seller_address: 'pothanur',
seller_city: 'Delhi',
product_buy:'baby 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 traders',
seller_business: 'GG Electronics',
seller_mobileno: 4567891230,
seller_address: 'Andhara',
seller_city: 'pondicherry'
},
{
_id: 2,
seller_name: 'xyz gamers',
seller_business: 'ABC Sports and Fasion',
seller_mobileno: 7890123456,
seller_address: 'pothanur',
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:'Raju',
customer_lname:'Bhai',
customer_dob:2002,
customer_address:'mumbai',
customer_city:'Nagarcoil',
is_premium: 1,
customer_contactno:9012345767,
customer_email:'[email protected]'
},
{
_id: 2,
customer_id: 2,
customer_fname:'Liza',
customer_lname:'Dolly',
customer_dob:2000,
customer_address:'villupuram',
customer_city:'Nagaland',
is_premium: 0,
customer_contactno:2345678901,
customer_email:'[email protected]'
},
{
_id: 3,
customer_id: 3,
customer_fname:'Anjali',
customer_lname:'jay',
customer_dob:2000,
customer_address:'coimbatore',
customer_city:'tiruppur',
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: 'Beyblade toy',
product_model: 'C701',
product_segments: ['pokemon', 'gen9'],
product_cost: 4500,
categroy_name: 'Games and Consoles'
},
{
_id:5,
product_name: 'Legrand Switch',
product_model: 'C702',
product_segments: ['console'],
product_cost: 30000,
categroy_name: 'Games and Consoles'
},
]);

db.orders.insertMany([
{
_id: 1,
product_name: 'Legrand Switch',
customer_id: 1,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-15')
},
{
_id: 2,
product_name: 'Beyblade toy',
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: 'Archana',
seller_business: 'Gada Electronics',
seller_mobileno: 4567891230,
seller_address: 'Andhara',
seller_city: 'Mumbai'
},
{
_id: 2,
seller_name: 'Bcake Harvard',
seller_business: 'ABC Sports and Fasion',
seller_mobileno: 7890123456,
seller_address: 'pondicherry',
seller_city: 'Delhi'
}
]);

db.catlog.insertMany([
{
seller_name: 'Archana',
product_name: 'Smart Phone',
product_qty: 10,
},
{
seller_name: 'Archana',
product_name: 'Laptop',
product_qty: 5,
},
{
seller_name: 'Bcake Harvard',
product_name: 'Beyblade toy',
product_qty: 20,
},
{
seller_name: 'Bcake Harvard',
product_name: 'Legrand Switch',
product_qty: 15,
}
]);

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

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

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

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

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

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.

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

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

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

Your portfolio, next

Ship work like this
in your own course.

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.