Project 2
1)Design your ecommerce database in MongoDB with the specifications provided Below :
Ans:-
db.inventory.insertMany([
{
item: "journal",
price: 9.99,
qty: 25,
size: { h: 14, l: 21, w: 1 },
features: "Beautiful, handmade journal.",
categories: ["writing", "bestseller"],
image: "items/journal.jpg"
},
{
item: "Note Book",
price: 39.00,
qty: 50,
size: { h: 14, l: 21, w: 1 },
features: "Long and white",
categories: ["writing", "bestseller"],
image: "items/Notebook.jpg"
},
{
item: "Pen",
price: 19.99,
qty: 25,
features: "Blue,Soft writing",
categories: ["writing", "bestseller"],
image: "items/pen.jpg"
}
]);
db.customers.insertMany([
{
_id: "[email protected]",
fname: "John",
lname: "st",
Password: "Abc@123",
emailVerified: false,
usertype:"Normal",
Doj:"2022-10-21"
address: {
country: "United States",
street1: "99 Main Street",
street2: "Apt #3",
city: "Boston",
state: "MA",
zip: "74586"
}
},
{
_id: "[email protected]",
fname: "kohli",
lname: "virat",
Password: "pqr@123",
emailVerified: false,
usertype:"Premium",
Doj:"2022-8-2"
address: {
country: "India",
street1: "99 Main Street",
street2: "Apt #3",
city: "Delhi",
state: "Delhi",
zip: "400035"
}
},
{
_id: "[email protected]",
fname: "smith",
lname: "steve",
Password: "fgh@123",
emailVerified: false,
usertype:"Normal",
Doj:"2022-4-12"
address: {
country: "Australia",
street1: "99 Main Street",
street2: "Apt #3",
city: "Sidney",
state: "sidney",
zip: "323558"
}
}
] );
db.payments.insertMany([
{
customerId: "journalfanatic@e/mail.com",
status: "verified",
gateway: "stripe",
type: "credit",
amount: NumberDecimal(1.00),
card: {
brand: "Visa",
pan: "4242424242424242",
expirationMonth: 1,
expirationYear: 2090,
cvv: 123
}
}
{
customerId: "[email protected]",
status: "verified",
gateway: "stripe",
type: "debit",
amount: NumberDecimal(1.00),
card: {
brand: "Rupay",
pan: "15645435155555",
expirationMonth: 1,
expirationYear: 2025,
cvv: 456
}
}
{
customerId: "[email protected]",
status: "verified",
gateway: "COD",
type: "cash",
amount: NumberDecimal(1.00)
}
]);
db.orders.insertMany([
{
customerId: "[email protected]",
paymentId: "600e6f37aa2232f59e273082",
paymentStatus: "paid",
status: "shippedAwaitingDelivery",
currency: "USD",
totalCost: NumberDecimal(39.85)
items: [
{ sku: "154B",
quantity: "2",
price: NumberDecimal(14.99),
discount: NumberDecimal(1.00),
preTaxTotal: NumberDecimal(27.98),
tax: NumberDecimal(1.00),
total: NumberDecimal(28.98),
}, { sku: "154A",
quantity: "1",
price: NumberDecimal(9.99),
preTaxTotal: NumberDecimal(9.99),
tax: NumberDecimal(.87),
total: NumberDecimal(10.86)
}
],
shipping: {
address:{
street1: "50 Work Street",
street2: "Floor 16",
city: "Chicago",
state: "IL",
country: "USA",
zip: "60601"
},
origin: {
street1: "1 Penn Ave",
street2: "",
city: "New York",
state: "NY",
country: "USA",
zipCode: "46281"
},
carrier: "USPS",
tracking: "123412341234"
}
},
{
customerId: "[email protected]",
paymentStatus: "COD",
status: "shippedAwaitingDelivery",
currency: "INR",
totalCost: NumberDecimal(39.85)
items: [
{ sku: "1544B",
quantity: "2",
price: NumberDecimal(14.99),
discount: NumberDecimal(1.00),
preTaxTotal: NumberDecimal(27.98),
tax: NumberDecimal(1.00),
total: NumberDecimal(28.98),
}, { sku: "666y",
quantity: "1",
price: NumberDecimal(9.99),
preTaxTotal: NumberDecimal(9.99),
tax: NumberDecimal(.87),
total: NumberDecimal(10.86)
}
],
shipping: {
address:{
street1: "Lingampally",
street2: "Ground Floor",
city: "Hyderabad",
state: "Telangana",
country: "India",
zip: "500016"
},
origin: {
street1: "Begumpet",
street2: "HPS",
city: "Hyderabad",
state: "Telangana",
country: "India",
zipCode: "500080"
},
carrier: "Delhivery",
tracking: "64548546487"
}
},
]);
Simple Queries to perform on ecommerce db
1)Write a query to find the average order value for all Customers.
Ans:-db.orders.aggregate(
[{
$group:
{
_id: "$item",
avgAmount: { $avg: { $multiply: [ "$price", "$quantity" ] } },
}}])
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
Ans:-db.getCollection('orders').find(
$and:
[{Doj:{$gt:"2022-09-29"},{$ordervalue:{$gt:10000}}]).limit(10)
[{
$lookup:
{
from: 'customers',
localField: 'customer_id',
foreignField: '_id',
as: 'orderdetails'
}
}]);
3)Query your database to find the sum of all orders received in this month
Ans:-db.orders.aggregate(
[{
$group:
{
_id: "$item",
TotalAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },
}}])
4)Query your database to find customers count in various cities, group them by names of cities
Ans:-b.customers.aggregate(
[{
$group:
{
_id: "$city",
cutomersincity: { $count:"customer_id"},
}}])
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.
Ans:-