Project 2

Creating database, collections, and fields:

use eCommerce;

db.createCollection('Product')

db.Product.insertMany(
[
{
prod_id: 101,
prod_name: 'Books',
price: 199,
category: [
{ name: 'Harry Potter', author: 'JK Rowling', quantity: 60 },
{ name: 'Inner Engineering', author: 'Sadguru', quantity: 100 },
{ name: 'Epic Shit', author: 'Ankur Warikoo', quantity: 50 }
],
feature: 'Hard cover'
},
{
prod_id: 102,
prod_name: 'Notebooks',
price: 99,
category: [
{ name: 'Classmate', quantity: 100 },
{ name: 'Doms', quantity: 150 },
{ name: 'Schoolmate', quantity: 200 }
],
feature: 'Hard cover'
}
]
);

db.Product.find().pretty();

db.createCollection('Customer')

db.Customer.insertMany([
{
_id : '[email protected]',
cust_name : 'Customer1',
password : 'customer@1',
billing_address : {
cust_city : 'Banglore',
zip : 110087
},
shipping_assress : {
city : 'Banglore',
zip : 110084
},

joining_date: '2010-09-23'
},
{
_id : '[email protected]',
cust_name : 'Customer2',
password : 'customer@2',
billing_address : {
cust_city : 'Delhi',
zip : 110094
},
shipping_assress : {
city : 'Delhi',
zip : 110094
},

joining_date: '2021-08-14'
},

{
_id : '[email protected]',
cust_name : 'Customer3',
password : 'customer@3',
billing_address : {
city : 'Pune',
zip : 110061
}
}

]);

db.Customer.find().pretty();

db.Payment.insertMany([
{
user_id : '[email protected]',
type : 'credit card',
status : 'verified',
card : {type : 'visa', card_no: 'xxxx0898', expiry_year : 2024, cvv : true }

},
{
user_id : '[email protected]',
type : 'credit card',
status : 'verified',
card : {type : 'rupay', card_no: 'xxxx1212', expiry_year : 2023, cvv : true }
},
{
user_id : '[email protected]',
type : 'cash',
status : 'verified',
token : 'secret token'
}
]);

db.Payment.find().pretty()

db.createCollection('Orders')

db.Payments.insertMany([
{
user_id : '[email protected]',
type : 'credit card',
status : 'verified',
card : {type : 'visa', card_no: 'xxxx0898', expiry_year : 2024, cvv : true }
},
{
user_id : '[email protected]',
type : 'credit card',
status : 'verified',
card : {type : 'rupay', card_no: 'xxxx1212', expiry_year : 2023, cvv : true }
},
{
user_id : '[email protected]',
type : 'cash',
status : 'verified',
token : 'secret token'
}
]);

db.Orders.insertMany([
{
user_id : '[email protected]',
Status : 'Shipped',
Payment : 400,

items : [
{ name : 'Epic Shit' , quantity : 1 , price : 199.00 , discounts : 2.00 , afterTax: 200 },
{ name : 'Doms' , quantity : 2 , price : 99.00 , discounts : 00 , afterTax: 100 }
],
shippingAddress : {
city : 'Banglore',
zip : 110084
},
billingAddress : 'same as above',
tracking_id : 123451
},
{
user_id : '[email protected]',
Status : 'Delivered',
Payment : 500,
items : [
{ name : 'Harry Potter' , quantity : 2 , price : 199.00 , discounts : 2.00 , afterTax: 200 },
{ name : 'Classmate' , quantity : 1 , price : 99.00 , discounts : 00 , afterTax: 100 }
],
shippingAddress : {
city : 'Delhi',
zip : 110094
},
billingAddress :{
city : 'Delhi',
zip : 110006
},
tracking_id : 123452
}
]);

db.Orders.find().pretty()

db.createCollection('Seller')

db.Seller.insertMany(
{
seller_id : 1,
sel_name : 'Seller1',
sel_city : 'Delhi',
add_products : [
{ prod_name : 'book', add_date : '2022-10-09', price: 200},
{ prod_name : 'ball pen', add_date : '2022-11-21', price: 50},
{ prod_name : 'notebook', add_date : '2022-11-19', price: 100}
]
},
{
seller_id : 2,
sel_name : 'Seller2',
sel_city : 'Banglore',
add_products : [
{ prod_name : 'gel pen', add_date : '2022-11-25', price: 70},
{ prod_name : 'ball pen', add_date : '2022-10-01', price: 50},
{ prod_name : 'bag', add_date : '2022-10-29', price: 700}
]
}
);

db.Seller.find().pretty()

Answer 1)

db.Orders.aggregate(
[
{
$group:
{
_id:"$items",
avgAmount: { $avg: { $multiply: [ "$price", "$quantity" ] } },
avgQuantity: { $avg: "$quantity" }
}
}
]
)

Answer 2)

db.customer.find(
{
'joining_date' :
{
'$lt' : 2 }}).sort({ order_value : -1 }).limit( 10 ).pretty()

Answer 3)

db.Orders.aggregate(
[
{
$group:
{
_id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
totalAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },
count: { $sum: 1 }
}
}
]
)

Answer 4)

db.customer.aggregate([{ '$group' : { _id : '$cust_city' , 'count' : { '$sum' : 1 }, 'cust_name' : { '$push' : 'cust_name' }}},
{'$project' : {'size' : 1 , 'customer_count' : {'$size' : '$cust_name' },}}])

Answer 5)

db.getCollection('seller').aggregate([{
$lookup:
{
from : 'customer',
localField : 'sel_city',
foreignField : 'cust_city',
as : 'customerDetails'
}
},
{
"$sort":{
"products_sold" : -1
}},
{ $match : { 'customerDetails' : { $ne : []}}},
]);