Project 2
db.customers.insertMany([
{cust_id:1,cust_name:"Rejaul",email:"[email protected]",city:"kolkata",order_value:2000,order_date:"2023-01-13"},
{cust_id:2,cust_name:"Salim",email:"[email protected]",city:"bihar",order_value:3000,order_date:"2022-01-13"},
{cust_id:3,cust_name:"Riya",email:"[email protected]",city:"mumbai",order_value:1000,order_date:"2023-02-13"},
{cust_id:4,cust_name:"Injamul",email:"[email protected]",city:"kolkata",order_value:2000,order_date:"2023-01-13"},
{cust_id:5,cust_name:"Danish",email:"[email protected]",city:"delhi",order_value:2500,order_date:"2023-02-13"},
{cust_id:6,cust_name:"Swati",email:"[email protected]",city:"chenni",order_value:3000,order_date:"2022-02-13"},
{cust_id:7,cust_name:"Rahul",email:"[email protected]",city:"mumbai",order_value:3500,order_date:"2022-03-13"}
])
{
acknowledged: true,
insertedIds: {
'0': ObjectId("63fc55962c19f58c03b9fec5"),
'1': ObjectId("63fc55962c19f58c03b9fec6"),
'2': ObjectId("63fc55962c19f58c03b9fec7"),
'3': ObjectId("63fc55962c19f58c03b9fec8"),
'4': ObjectId("63fc55962c19f58c03b9fec9"),
'5': ObjectId("63fc55962c19f58c03b9feca"),
'6': ObjectId("63fc55962c19f58c03b9fecb")
}
}
db.customer.insert({normalmemer:"5",premiummember:"2"})
db.product.insertMany([
{pro_id:1,pro_name:"watch",cost:5000,qty:5},
{pro_id:2,pro_name:"TV",cost:30000,qty:4},
{pro_id:3,pro_name:"Mobile",cost:60000,qty:9},
{pro_id:4,pro_name:"Smart watch",cost:5000,qty:5},
{pro_id:5,pro_name:"shirt",cost:1200,qty:10},
{pro_id:6,pro_name:"saree",cost:3000,qty:8},
{pro_id:7,pro_name:"jeans",cost:2000,qty:7}
])
{
acknowledged: true,
insertedIds: {
'0': ObjectId("63fc58942c19f58c03b9fecf"),
'1': ObjectId("63fc58942c19f58c03b9fed0"),
'2': ObjectId("63fc58942c19f58c03b9fed1"),
'3': ObjectId("63fc58942c19f58c03b9fed2"),
'4': ObjectId("63fc58942c19f58c03b9fed3"),
'5': ObjectId("63fc58942c19f58c03b9fed4"),
'6': ObjectId("63fc58942c19f58c03b9fed5")
}
}
db.order.insertMany([
{order_id:1,cust_id:2,order_status:"delivered",order_date:"2022-01-13"},
{order_id:2,cust_id:3,order_status:"delivered",order_date:"2023-01-13"},
{order_id:3,cust_id:4,order_status:"not delivered",order_date:"2022-12-13"}
])
db.seller.insertMany([
... {seller_id:1,seller_name:"Rahul",city:"bangalore",products_sold:4},
... {seller_id:2,seller_name:"Asif",city:"kolkata",products_sold:8},
... {seller_id:3,seller_name:"Anjali",city:"bangalore",products_sold:9},
... {seller_id:4,seller_name:"Arif",city:"chenni",products_sold:10},
... {seller_id:5,seller_name:"Kartik",city:"mumbai",products_sold:7},
... {seller_id:6,seller_name:"Atif",city:"mumbai",products_sold:8},
... {seller_id:7,seller_name:"Aktar",city:"kolkata",products_sold:6}
... ])
{
acknowledged: true,
insertedIds: {
'0': ObjectId("63fc5a0e2c19f58c03b9fed6"),
'1': ObjectId("63fc5a0e2c19f58c03b9fed7"),
'2': ObjectId("63fc5a0e2c19f58c03b9fed8"),
'3': ObjectId("63fc5a0e2c19f58c03b9fed9"),
'4': ObjectId("63fc5a0e2c19f58c03b9feda"),
'5': ObjectId("63fc5a0e2c19f58c03b9fedb"),
'6': ObjectId("63fc5a0e2c19f58c03b9fedc")
}
}
db.cart.insertMany([
{cart_id:1,pro_id:2,status:"active",qty:4,cost:7000},
{cart_id:2,pro_id:1,status:"active",qty:6,cost:65000},
{cart_id:3,pro_id:2,status:"active",qty:4,cost:80000}
])
{
acknowledged: true,
insertedIds: {true,
'0': ObjectId("63fc5be32c19f58c03b9fedd"),
'1': ObjectId("63fc5be32c19f58c03b9fede"),
'2': ObjectId("63fc5be32c19f58c03b9fedf"),
} '2': ObjectId("63fc57d92c19f58c03b9fece")
} }
db.category.insertOne([
{subcategories:4}
])
{
acknowledged: true,
insertedId: ObjectId("63fc5c912c19f58c03b9fee0")
}
db.admin.insertMany([
{_id:1,seller_id:1-7}
])
{ acknowledged: true, insertedIds: { '0': 1 } }
Ans-1.
db.customers.aggregate([
{$group:{_id:"cust_id",average_order:{$avg:"$order_value"}}}
])
Ans-2.
db.order.aggregate([
{$match:{order_date:{$lt:"2023-01-01"}}},
{$sort:{order_value:-1}}
])
Ans-3.
db.order.aggregate([
{$match:{order_date:{$gte:"2022-01-01",$lte:"2022-01-30"}}},
{$group:{_id:"cust_id",order:{$sum:1}}}
])
Ans-4.
db.customers.find({},{cust_name:1,city:1,_id:0})
[
{ cust_name: 'Rejaul', city: 'kolkata' },
{ cust_name: 'Salim', city: 'bihar' },
{ cust_name: 'Riya', city: 'mumbai' },
{ cust_name: 'Injamul', city: 'kolkata' },
{ cust_name: 'Danish', city: 'delhi' },
{ cust_name: 'Swati', city: 'chenni' },
{ cust_name: 'Rahul', city: 'mumbai' }
]
Ans-5.
db.seller.aggregate([
{
$lookup:
{
from:"customers",
localField:"city",
foreignField:"city",
as:"order details"
}},
{$sort:{products_sold:-1}},
])
[
{
_id: ObjectId("63fc5a0e2c19f58c03b9fed9"),
seller_id: 4,
seller_name: 'Arif',
city: 'chenni',
products_sold: 10,
'order details': [
{
_id: ObjectId("63fc55962c19f58c03b9feca"),
cust_id: 6,
cust_name: 'Swati',
email: '[email protected]',
city: 'chenni',
order_value: 3000,
order_date: '2022-02-13'
}
]
},
{
_id: ObjectId("63fc5a0e2c19f58c03b9fed8"),
seller_id: 3,
seller_name: 'Anjali',
city: 'bangalore',
products_sold: 9,
'order details': []
},
{
_id: ObjectId("63fc5a0e2c19f58c03b9fed7"),
seller_id: 2,
seller_name: 'Asif',
city: 'kolkata',
products_sold: 8,
'order details': [
{
_id: ObjectId("63fc55962c19f58c03b9fec5"),
cust_id: 1,
cust_name: 'Rejaul',
email: '[email protected]',
city: 'kolkata',
order_value: 2000,
order_date: '2023-01-13'
},
{
_id: ObjectId("63fc55962c19f58c03b9fec8"),
cust_id: 4,
cust_name: 'Injamul',
email: '[email protected]',
city: 'kolkata',
order_value: 2000,
order_date: '2023-01-13'
}
]
},
{
_id: ObjectId("63fc5a0e2c19f58c03b9fedb"),
seller_id: 6,
seller_name: 'Atif',
city: 'mumbai',
products_sold: 8,
'order details': [
{
_id: ObjectId("63fc55962c19f58c03b9fec7"),
cust_id: 3,
cust_name: 'Riya',
email: '[email protected]',
city: 'mumbai',
order_value: 1000,
order_date: '2023-02-13'
},
{
_id: ObjectId("63fc55962c19f58c03b9fecb"),
cust_id: 7,
cust_name: 'Rahul',
email: '[email protected]',
city: 'mumbai',
order_value: 3500,
order_date: '2022-03-13'
}
]
},
{
_id: ObjectId("63fc5a0e2c19f58c03b9feda"),
seller_id: 5,
seller_name: 'Kartik',
city: 'mumbai',
products_sold: 7,
'order details': [
{
_id: ObjectId("63fc55962c19f58c03b9fec7"),
cust_id: 3,
cust_name: 'Riya',
email: '[email protected]',
city: 'mumbai',
order_value: 1000,
order_date: '2023-02-13'
},
{
_id: ObjectId("63fc55962c19f58c03b9fecb"),
cust_id: 7,
cust_name: 'Rahul',
email: '[email protected]',
city: 'mumbai',
order_value: 3500,
order_date: '2022-03-13'
}
]
},
{
_id: ObjectId("63fc5a0e2c19f58c03b9fedc"),
seller_id: 7,
seller_name: 'Aktar',
city: 'kolkata',
products_sold: 6,
'order details': [
{
_id: ObjectId("63fc55962c19f58c03b9fec5"),
cust_id: 1,
cust_name: 'Rejaul',
email: '[email protected]',
city: 'kolkata',
order_value: 2000,
order_date: '2023-01-13'
},
{
_id: ObjectId("63fc55962c19f58c03b9fec8"),
cust_id: 4,
cust_name: 'Injamul',
email: '[email protected]',
city: 'kolkata',
order_value: 2000,
order_date: '2023-01-13'
}
]
},
{
_id: ObjectId("63fc5a0e2c19f58c03b9fed6"),
seller_id: 1,
seller_name: 'Rahul',
city: 'bangalore',
products_sold: 4,
'order details': []
}
]