Project 2

db.customer.insertMany([
{
_id: 1,
customer_id: 1,
customer_fname:'Steve',
customer_lname:'Andrew',
customer_dob:1995,
customer_address:'taj hotel',
customer_city:'chennai',
ord_date:'2022-11-23',
product_cost:25000,
is_premium: 1,
customer_contactno:1234657890,
customer_email:'[email protected]'
},
{
_id: 2,
customer_id: 2,
customer_fname:'Nishanth',
customer_lname:'raj',
customer_dob:1996,
customer_address:'RIL Township',
customer_city:'uttarakand',
ord_date:'2022-11-28',
product_cost:23000,
is_premium: 0,
customer_contactno:2345678901,
customer_email:'[email protected]'
},
{
_id: 3,
customer_id: 3,
customer_fname:'Anjan',
customer_lname:'prabhu',
customer_dob:1993,
customer_address:'ranjith towers',
customer_city:'hyderabad',
ord_date:'2022-11-32',
product_cost:54366,
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
}]);
db.seller.insertMany([
{
_id: 1,
seller_name: 'Denver',
seller_business: 'trav Electronics',
seller_mobileno: 4567891230,
seller_address: 'finland',
seller_city: 'Australia'
},
{
_id: 2,
seller_name: 'Kumar',
seller_business: 'ABC Sports and Fasion',
seller_mobileno: 7890123456,
seller_address: 'raj Place',
seller_city: 'Delhi'
}
]);

db.catalog.insertMany([
{
seller_name: 'Jegada',
product_name: 'Smart Phone',
product_qty: 2,
},
{
seller_name: 'Rammy',
product_name: 'Laptop',
product_qty: 3,
},
{
seller_name: 'Anthony',
product_name: 'Pokemon Scarlet',
product_qty: 2,
},
{
seller_name: 'davis',
product_name: 'Nintendo Switch',
product_qty: 8,
}
]);
db.admin.insertMany([
{
admin_name:'Ronnie',
product_id:401,
product_req:0,
seller_id:20,
},
{
admin_name:'Nazim',
product_id:302,
product_req:1,
seller_id:54,
}])
db.trackdetails.insertMany([
{
product_id:301,
shipping_status:0,
},
{
product_id:501,
shipping_status:3,
}
])
db.payment.insertMany([
{
customer_id:2201,
product_id:323,
product_price:3000,
payment_status:0,
},
{
customer_id:2201,
product_id:323,
product_price:3000,
payment_status:1,
}
])
db.products.insertMany([
{
_id:23,
product_id:323,
product_catogrie:'Electronic Device',
product_cost:'23000',
product_name:'laptop'
},
{
_id:28,
product_id:328,
product_catogrie:'Electronic Device',
product_cost:'28000',
product_name:'Speaker'
}
])
db.order.inertMany([
{
order_id:2328,
product_id:323,
},
{
order_id:2823,
product_id:328,
}
)]

queries:
q1.ans:
db.products.aggregate([{$group:{_id:'product_cost', avg:{$avg:'$product_cost'}}}])

q2.ans:
db.customer.aggregate([{$match:{ord_date:{$lt:"2022-12-23"}}}{$sort:{purchase_amount:-1},{$limit:10}])db.customer.insert({"ord_date":"2022-11-28","product_cost":25000})

q3.ans:
db.order.aggregate([{$match:ord_date:{$gte:"2022-11-01",$lte:"2022-11-30"},{$group:{_id:'Total_cost', sum:{$sum:'$product_cost'}}}])

q4.ans:
db.customer.aggregate([{$group:{_id:'customer_city',"count":{"$sum":1}}}])

q5.ans:
db.seller.aggregate([{
$lookup:
{
from:'products',
localField:'seller_id',
foreignField:'_id',
as : "Seller"
}
},
{
$match:{"customer.customer_city":"$seller.seller_city"}
},
{
$group:{_id:'seller_city', $match:{seller_city}}
},
{
$sort:{"product_cost":-1}
}
])