Project 2
ecommerce database in MongoDB is designed with the specifications provided:
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.
All the queries to create ecommerce dtabase is given below
1.Databse creation query
use sakala_marukatte
with the above database is created
2.Queries to create collections are given below
db.customer.insertMany([
{_id:1,cust_id:101,cust_password:"**********",cust_fname:"Ravi",cust_lname:"Badagabettu",
gender:"M",cont_number:"888xxxx587",emailID:"[email protected]",
cust_Address:"#1/1, Vijayanagar,4th cross",cust_city:"Bengaluru",postal_code:500001,
country:"India",membership:"normal",current_order:"Rice",previous_order:"Titan watch"},
{_id:2,cust_id:102,cust_password:"**********",cust_fname:"Piyush",cust_lname:"Goyal",
gender:"M",cont_number:"897xxxx587",emailID:"[email protected]",
cust_Address:"#1/1, dhara, 4th cross",cust_city:"Mumbai",postal_code:580001,
country:"India",membership:"premium",current_order:"Minoxidal",previous_order:"Hyervita"},
{_id:3,cust_id:103,cust_password:"**********",cust_fname:"Manasa",cust_lname:"Anantha",
gender:"F",cont_number:"687xxxx867",emailID:"[email protected]",
cust_Address:"#1/1, mgroad, 4th main, 5thcross",cust_city:"Mysore",postal_code:480001,
country:"India",membership:"premium",current_order:"Lipstick",previous_order:"Hair dryer"},
{_id:4,cust_id:104,cust_password:"**********",cust_fname:"Suman",cust_lname:"Ranganath",
gender:"F",cont_number:"686xxxx858",emailID:"[email protected]",
cust_Address:"#1/1, vijayanagar, 5thcross",cust_city:"Hassan",postal_code:680001,
country:"India",membership:"normal",current_order:"T-shirt",previous_order:"Pant"},
{_id:5,cust_id:105,cust_password:"**********",cust_fname:"Shankar",cust_lname:"Bidari",
gender:"M",cont_number:"857xxxx586",emailID:"[email protected]",
cust_Address:"#1/1, rammandir,8th cross",cust_city:"Bengaluru",postal_code:500801,
country:"India",membership:"normal",current_order:"Raincoat",previous_order:"Umbrella"},
{_id:6,cust_id:106,cust_password:"**********",cust_fname:"Gayatri",cust_lname:"Ramappa",
gender:"F",cont_number:"787xxxx597",emailID:"[email protected]",
cust_Address:"#1/1, bandehalli, kramkote",cust_city:"Tumkur",postal_code:590001,
country:"India",membership:"premium",current_order:"Window Screen",previous_order:"Pillow cover"},
{_id:7,cust_id:107,cust_password:"**********",cust_fname:"Shreya",cust_lname:"Thimmaraju",
gender:"F",cont_number:"657xxxx868",emailID:"[email protected]",
cust_Address:"#1/1, peenya, 1st stage",cust_city:"Bengaluru",postal_code:460058,
country:"India",membership:"premium",current_order:"smart phone back cover",
previous_order:"pullover"},
{_id:8,cust_id:108,cust_password:"**********",cust_fname:"Dileep",cust_lname:"Kumar",
gender:"M",cont_number:"685xxxx857",emailID:"[email protected]",
cust_Address:"#1/1, doddaballapur",cust_city:"Bengaluru Rural",postal_code:870001,
country:"India",membership:"normal",current_order:"Tiffin box",previous_order:"Nonstick cook wear"},
{_id:9,cust_id:109,cust_password:"**********",cust_fname:"Ashoka",cust_lname:"Timmarayappa",
gender:"M",cont_number:"858xxxx578",emailID:"[email protected]",
cust_Address:"#1/1, bandehalli, kuramkote",cust_city:"Tumkur",postal_code:570001,
country:"India",membership:"normal",current_order:"Badminton Racket",
previous_order:"Shuttle Racket"},
{_id:10,cust_id:110,cust_password:"**********",cust_fname:"Rajini",cust_lname:"Panagar",
gender:"F",cont_number:"875xxxx654",emailID:"[email protected]",
cust_Address:"#1/1, batawadi, near venkateshwara temple",cust_city:"Tumkur",postal_code:580052,
country:"India",membership:"premium",current_order:"Oneplus earbuds",previous_order:"selfie stick"},
{_id:11,cust_id:111,cust_password:"**********",cust_fname:"Shwetha",cust_lname:"Changappa",
gender:"F",cont_number:"789xxxx898",emailID:"[email protected]",
cust_Address:"#1/1, shettihalli, 8th main, 5thcross",cust_city:"Tumkur",postal_code:480001,
country:"India",membership:"premium",current_order:"Ceiling fan",previous_order:"Table fan"},
{_id:12,cust_id:112,cust_password:"**********",cust_fname:"Tribhuvan",cust_lname:"Gowda",
gender:"M",cont_number:"985xxxx758",emailID:"[email protected]",
cust_Address:"#1/1, mandipete, bengaluru road",cust_city:"Hassan",postal_code:680001,
country:"India",membership:"normal",current_order:"UPSC book",previous_order:"FDA previos papers"}
]);
db.products.insertMany([
{_id:1,prod_id:501,name:"Rice",decription:"Basmati",avail_qty:10,color:"White",price:1400,
category:"grocery",Sub_category:"Food",brand:"India gate",Tag_id:"GGHG"},
{_id:2,prod_id:502,name:"Raincoat",decription:"clothing that protects you from rain",
avail_qty:8,color:"Green",price:750,category:"Textile",Sub_category:"Rain Jacket",
brand:"Ramraj",Tag_id:"KLMN"},
{_id:3,prod_id:503,name:"UPSC book",decription:"book covers all syllabus",avail_qty:20,
color:"Blue",price:1200,category:"Competitive exams",Sub_category:"UPSC",brand:"Arihant",
Tag_id:"NOPK"},
{_id:4,prod_id:504,name:"Ceiling fan",decription:"Air distributor",avail_qty:5,
color:"brown",price:1400,category:"Electronics",Sub_category:"Fans",brand:"Havells",
Tag_id:"MNIK"},
{_id:5,prod_id:505,name:"Oneplus earbuds",decription:"bluetooth earbuds",avail_qty:2,
color:"Black",price:1800,category:"Electronics",Sub_category:"earbuds",brand:"Oneplus",
Tag_id:"PRKD"},
{_id:6,prod_id:506,name:"Badminton Racket",decription:"Badminton playing racket",
avail_qty:18,color:"Sky blue",price:1100,category:"Sports",Sub_category:"Badminton",
brand:"Ravi",Tag_id:"KLMN"},
{_id:7,prod_id:507,name:"Tiffin box",decription:"to carry tiffin & lunch",avail_qty:15,
color:"Chromium",price:250,category:"Utensils",Sub_category:"Lunch box",brand:"Nandi",
Tag_id:"NOPK"},
{_id:8,prod_id:508,name:"smart phone back cover",decription:"phone protection cover",
avail_qty:8,color:"Multiple color",price:300,category:"Mobile accesories",
Sub_category:"back covers",brand:"Scooter",Tag_id:"MNIK"},
{_id:9,prod_id:509,name:"Lipstick",decription:"beauty lips",avail_qty:30,
color:"Red",price:100,category:"Cosmetics",Sub_category:"Lip care",brand:"Dazller",
Tag_id:"NOPK"},
{_id:10,prod_id:510,name:"Minoxidal",decription:"Hair fall reduction lotion",avail_qty:25,
color:"Grey",price:680,category:"Chemical",Sub_category:"Hair care",brand:"H&H",
Tag_id:"MNIK"}
]);
db.seller.insertMany([
{_id:1,seller_id:201,seller_fname:"Raj",seller_lname:"Shetty",seller_password:"*******",
seller_cont_num:"846xxxx828",seller_mail:"[email protected]",seller_city:"Tumkur"},
{_id:2,seller_id:202,seller_fname:"Shine",seller_lname:"Shetty",seller_password:"*******",
seller_cont_num:"850xxxx850",seller_mail:"[email protected]",seller_city:"Hassan"},
{_id:3,seller_id:203,seller_fname:"Rishab",seller_lname:"Shetty",seller_password:"*******",
seller_cont_num:"987xxxx587",seller_mail:"[email protected]",seller_city:"Bengaluru"},
{_id:4,seller_id:204,seller_fname:"Rakshith",seller_lname:"Shetty",seller_password:"*******",
seller_cont_num:"888xxxx828",seller_mail:"[email protected]",seller_city:"Mysore"},
{_id:4,seller_id:204,seller_fname:"Rakshith",seller_lname:"Shetty",seller_password:"*******",
seller_cont_num:"888xxxx828",seller_mail:"[email protected]",seller_city:"Mumbai"},
{_id:4,seller_id:204,seller_fname:"Rakshith",seller_lname:"Shetty",seller_password:"*******",
seller_cont_num:"888xxxx828",seller_mail:"[email protected]",seller_city:"Bengaluru Rural"}
]);
db.admin.insertMany([
{_id:1,admin_id:301,admin_fname:"Kantha",admin_lname:"Raju",admin_password:"*******"},
{_id:2,admin_id:302,admin_fname:"Rama",admin_lname:"Kantha",admin_password:"*******"}
]);
db.cart.insertMany([
{_id:1,cart_id:401,checkout:"Yes"},
{_id:2,cart_id:402,checkout:"no"}
]);
db.billingInfo.insertMany([
{_id:1,bill_id:701,billing_Address:"Tumkur",credit_card_id:001,credit_card_pin:"****",
credit_card_number:"********021",credit_card_exp_date:"2025-08-11"},
{_id:2,bill_id:702,billing_Address:"Bengaluru",credit_card_id:021,credit_card_pin:"****",
credit_card_number:"********179",credit_card_exp_date:"2025-05-08"}
]);
db.shippingDetails.insertMany([
{_id:1,shipper_id:901,companyName:"XYZ",
shipper_contact_num:"657xxxx589",shipping_address:"Tumkur"},
{_id:2,shipper_id:902,companyName:"MNO",
shipper_contact_num:"625xxxx855",shipping_address:"Bengaluru"},
{_id:3,shipper_id:903,companyName:"PQR",
shipper_contact_num:"657xxxx589",shipping_address:"Hassan"},
{_id:4,shipper_id:904,companyName:"JKL",
shipper_contact_num:"625xxxx855",shipping_address:"Bengaluru Rural"},
{_id:5,shipper_id:905,companyName:"SKM",
shipper_contact_num:"657xxxx589",shipping_address:"Mysore"},
{_id:6,shipper_id:906,companyName:"BSN",
shipper_contact_num:"625xxxx855",shipping_address:"Mumbai"}
]);
db.orders.insertMany([
{_id:1,order_id:1001,order_date:'2022-08-09',delivered_date:'2022-08-11',ordered_qty:8,
cust_id:102,prod_id:510,seller_id:203,Total_order_cost:5440},
{_id:2,order_id:1002,order_date:'2022-08-10',delivered_date:'2022-08-12',ordered_qty:2,
cust_id:101,prod_id:501,seller_id:203,Total_order_cost:2800},
{_id:3,order_id:1003,order_date:'2022-09-03',delivered_date:'2022-09-05',ordered_qty:2,
cust_id:109,prod_id:506,seller_id:203,Total_order_cost:2200},
{_id:4,order_id:1004,order_date:'2022-09-04',delivered_date:'2022-09-06',ordered_qty:3,
cust_id:108,prod_id:507,seller_id:203,Total_order_cost:750},
{_id:5,order_id:1005,order_date:'2022-09-05',delivered_date:'2022-09-07',ordered_qty:1,
cust_id:101,prod_id:501,seller_id:203,Total_order_cost:1400},
{_id:6,order_id:1006,order_date:'2022-09-10',delivered_date:'2022-09-12',ordered_qty:4,
cust_id:102,prod_id:510,seller_id:203,Total_order_cost:2720},
{_id:7,order_id:1007,order_date:'2022-09-15',delivered_date:'2022-09-17',ordered_qty:2,
cust_id:112,prod_id:503,seller_id:203,Total_order_cost:2400},
{_id:8,order_id:1008,order_date:'2022-09-18',delivered_date:'2022-09-20',ordered_qty:3,
cust_id:111,prod_id:504,seller_id:203,Total_order_cost:3200},
{_id:9,order_id:1009,order_date:'2022-09-20',delivered_date:'2022-09-22',ordered_qty:1,
cust_id:110,prod_id:505,seller_id:203,Total_order_cost:1800},
{_id:10,order_id:1010,order_date:'2022-09-28',delivered_date:'2022-09-29',ordered_qty:6,
cust_id:105,prod_id:502,seller_id:203,Total_order_cost:1500},
{_id:11,order_id:1011,order_date:'2022-10-11',delivered_date:'2022-10-13',ordered_qty:14,
cust_id:103,prod_id:509,seller_id:203,Total_order_cost:500},
{_id:12,order_id:1012,order_date:'2022-11-12',delivered_date:'2022-11-14',ordered_qty:10,
cust_id:107,prod_id:508,seller_id:203,Total_order_cost:3000}
]);
Simple Queries to perform on ecommerce db
These 100% succesfully verified queries.
1.Write a query to find the average order value for all Customers.
Query:
db.orders.aggregate([
{$group:{
_id:1,Average_order_value_of_all_customers:{$avg:'$Total_order_cost'}}}
]);
2.Query your database to get top 10 customers whose orders have the highest values and who have been customers since less than two months.
Query:
db.orders.find(
{
order_date:{$lt:("2022-10-01")}
},
{cust_id:1,Total_order_cost:2,order_date:3,_id:0}
).sort({Total_order_cost:-1});
3.Query your database to find the sum of all orders recieved in this month.
Query:
db.orders.aggregate([
{
$match: {
order_date: {$gt:("2022-10-31")}
}
},
{
$group:{
_id:{order_inThis_month:"$order_date"},
count:{$sum:1}
}
},
]);
4.Query you databse to find customers count in the various cities,group them by names of cities.
Query:
db.customer.aggregate([
{
$group: {
_id: { various_cities : "$cust_city"},
count: {$sum: 1}
}
},
{$match: {
count: {"$gte": 1}
}
}
]);
5.Query your databse 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 the same city to lowest.
Query:
db.getCollection('seller').aggregate([{
$lookup:
{
from:'customer',
localField:'seller_city',
foreignField:'cust_city',
as:'customerdetails'
}
},
{
"$sort":{
"cust_city":-1
}
},
{ $match : {
"customerdetails" : { $ne : []}
}
},
]);
Queries execution images below:
