Project 2
PROJECT 2
mongoDb codes:
use Ecommerce
db.Ecommerce.insertMany([
{admin_id:11},{admin_id:12},{sel_id:31},{sel_id:32},{sel_id:33},{sel_id:34},{sel_id:35},{cust_id:22},{cust_id:21},{cust_id:23},{cust_id:24},{cust_id:25},{cust_id:26},
{cust_id:27},{cust_id:28},{cust_id:29},{cust_id:211},{cust_id:212},{cust_id:213},{cust_id:214},{cust_id:215},])
db.Admin.insertMany([{admin_id:11,admin_name:"Aniket Garg",admin_city:"New Delhi",admin_mobil:"7011101967"},{admin_id:12,admin_name:"Amod",admin_city:"New Delhi",admin_mobil:"9899657845"}])
db.customer.insertMany([
{cust_type:"Premium",cust_id:21,cust_name:"Arvind",cust_gender:"M",cust_Doj_as_per_date:"18-11-2022",cust_Doj_as_per_month:"1 Month",cust_city:"Noida",cust_mb_no:"7845858596",order_value:30000},
{cust_type:"Premium",cust_id:22,cust_name:"Varun",cust_gender:"M",cust_Doj_as_per_date:"22-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"pkf",cust_mb_no:"7412525263",order_value:20000},
{cust_type:"Premium",cust_id:23,cust_name:"Tarun",cust_gender:"M",cust_Doj_as_per_date:"23-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"gpd",cust_mb_no:"7845969685",order_value:3000},
{cust_type:"Normal",cust_id:24,cust_name:"Kalyan",cust_gender:"M",cust_Doj_as_per_date:"24-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"uhj",cust_mb_no:"9685565623",order_value:6000},
{cust_type:"Premium",cust_id:25,cust_name:"vasuli",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"llpk",cust_mb_no:"9696854545",order_value:5000},
{cust_type:"Normal",cust_id:26,cust_name:"kundan",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"lkj",cust_mb_no:"8596748585",order_value:8000},
{cust_type:"Premium",cust_id:27,cust_name:"abhinandan",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"hujk",cust_mb_no:"7458454585",order_value:4000},
{cust_type:"Normal",cust_id:28,cust_name:"parth",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"pki",cust_mb_no:"8745451252",order_value:5000},
{cust_type:"Premium",cust_id:29,cust_name:"purav",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"ert",cust_mb_no:"7485747485",order_value:4000},
{cust_type:"Premium",cust_id:211,cust_name:"jagdish",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"gurugram",cust_mb_no:"9685748596",order_value:4000},
{cust_type:"Normal",cust_id:212,cust_name:"pradip",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"telangana",cust_mb_no:"7485451252",order_value:4500},
{cust_type:"Premium",cust_id:213,cust_name:"adil",cust_gender:"M",cust_Doj_as_per_date:"25-09-2022",cust_Doj_as_per_month:"2 Month",cust_city:"shahinbagh",cust_mb_no:"789564512",order_value:3000},
{cust_type:"Normal",cust_id:214,cust_name:"naresh",cust_gender:"M",cust_Doj_as_per_date:"25-09-2022",cust_Doj_as_per_month:"2 Month",cust_city:"delhi",cust_mb_no:"7874863735",order_value:2000},
{cust_type:"Premium",cust_id:215,cust_name:"vicky",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"palwal",cust_mb_no:"9865321257",order_value:20000}
])
db.seller.insertMany([
{sel_id:31,sel_name:"Aniket",sel_city:"Delhi",pro_catagory:"Furniture",sel_mbl_no:"7425121252",products_sold:23},
{sel_id:32,sel_name:"Aniket",sel_city:"Tajakistan",pro_catagory:"Cloths",sel_mbl_no:"7411122257",products_sold:22},
{sel_id:33,sel_name:"Aniket",sel_city:"Udaipur",pro_catagory:"Electronic",sel_mbl_no:"7411554488",products_sold:2},
{sel_id:34,sel_name:"Aniket",sel_city:"Lonavla",pro_catagory:"Furniture",sel_mbl_no:"7788994455",products_sold:30},
{sel_id:35,sel_name:"Aniket",sel_city:"Nasik",pro_catagory:"Electronic",sel_mbl_no:"7788996633",products_sold:20}])
db.product.insertMany([
{pro_catagory:"Furniture",pro_id:101,pro_name:"Table",pro_cost:10000,sel_id:31},
{pro_catagory:"Furniture",pro_id:102,pro_name:"Stul",pro_cost:20000,sel_id:31},
{pro_catagory:"Furniture",pro_id:103,pro_name:"Chair",pro_cost:3000,sel_id:31},
{pro_catagory:"Furniture",pro_id:104,pro_name:"sofa",pro_cost:4000,sel_id:31},
{pro_catagory:"Furniture",pro_id:105,pro_name:"Bench",pro_cost:5000,sel_id:31},
{pro_catagory:"Furniture",pro_id:106,pro_name:"Dining Table",pro_cost:6000,sel_id:31},
{pro_catagory:"Electronic",pro_id:107,pro_name:"Oppo mobile",pro_cost:16000,sel_id:33},
{pro_catagory:"Electronic",pro_id:109,pro_name:"I_phone",pro_cost:12000,sel_id:33},
{pro_catagory:"Electronic",pro_id:110,pro_name:"Redmi",pro_cost:13000,sel_id:33},
{pro_catagory:"Electronic",pro_id:111,pro_name:"Boss mobile",pro_cost:10000,sel_id:33},
{pro_catagory:"Electronic",pro_id:112,pro_name:"Samsung mobile",pro_cost:12000,sel_id:33},
{pro_catagory:"Electronic",pro_id:113,pro_name:"Pocko",pro_cost:14000,sel_id:33},
{pro_catagory:"Cloths",pro_id:114,pro_name:"Shirt",pro_cost:1000,sel_id:33,sel_id:32},
{pro_catagory:"Cloths",pro_id:115,pro_name:"Normal Phant",pro_cost:800,sel_id:32},
{pro_catagory:"Cloths",pro_id:116,pro_name:"jeans pant",pro_cost:2000,sel_id:32},
{pro_catagory:"Cloths",pro_id:117,pro_name:"Track phant",pro_cost:900,sel_id:32},
{pro_catagory:"Cloths",pro_id:118,pro_name:"T_shirt",pro_cost:800,sel_id:32},
{pro_catagory:"Cloths",pro_id:119,pro_name:"Hoodie",pro_cost:1000,sel_id:32},
{pro_catagory:"Cloths",pro_id:120,pro_name:"Hoodie",pro_cost:1000,sel_id:32}
])
db.cart.insertMany([
{cart_id:10001,cust_id:21,pro_id:101,pro_cost:2000},
{cart_id:10002,cust_id:22,pro_id:102,pro_cost:2000},
{cart_id:10003,cust_id:23,pro_id:105,pro_cost:2000},
{cart_id:10003,cust_id:24,pro_id:106,pro_cost:2000},
{cart_id:10003,cust_id:25,pro_id:107,pro_cost:2000}
])
db.orders.insertMany([
{order_id:7001,order_value:10000,order_date:"20-11-2022",order_month:"This month",order_status:"Packed",pro_id:101,cust_id:21,sel_id:31,delevery_addr:"78/110 noida skytech",orders:1},
{order_id:7002,order_value:10000,order_date:"20-11-2022",order_month:"This month",order_status:"Delivered",pro_id:101,cust_id:22,sel_id:31,delevery_addr:"123/233 noida techtower",orders:1},
{order_id:7003,order_value:10000,order_date:"20-11-2022",order_month:"This month",order_status:"Packed",pro_id:101,cust_id:23,sel_id:31,delevery_addr:"232/34 gurugram",orders:1},
{order_id:7004,order_value:10000,order_date:"20-11-2022",order_month:"This month",order_status:"Packed",pro_id:101,cust_id:24,sel_id:31,delevery_addr:"d70 dairy farm",orders:1},
{order_id:7005,order_value:10000,order_date:"20-11-2022",order_month:"This month",order_status:"Delivered",pro_id:101,cust_id:25,sel_id:31,delevery_addr:"f85 dairy farm",orders:1},
{order_id:7006,order_value:20000,order_date:"20-11-2022",order_month:"This month",order_status:"Packed",pro_id:102,cust_id:26,sel_id:31,delevery_addr:"e70 dairy farm",orders:1},
{order_id:7007,order_value:20000,order_date:"20-11-2022",order_month:"This month",order_status:"Delivered",pro_id:102,cust_id:27,sel_id:31,delevery_addr:"d170 dairy farm",orders:1},
{order_id:7008,order_value:20000,order_date:"21-10-2022",order_month:"1 month",order_status:"Packed",pro_id:102,cust_id:28,sel_id:31,delevery_addr:"d710 dairy park",orders:1},
{order_id:7009,order_value:20000,order_date:"22-10-2022",order_month:"1 month",order_status:"Delivered",pro_id:102,cust_id:29,sel_id:31,delevery_addr:"d570 delhi 6",orders:1},
{order_id:7010,order_value:20000,order_date:"22-10-2022",order_month:"1 month",order_status:"Delivered",pro_id:102,cust_id:211,sel_id:31,delevery_addr:"w213 street 4 ",orders:1},
{order_id:7011,order_value:20000,order_date:"22-10-2022",order_month:"1 month",order_status:"Delivered",pro_id:102,cust_id:212,sel_id:31,delevery_addr:"d204 mp street",orders:1},
{order_id:7012,order_value:20000,order_date:"23-10-2022",order_month:"1 month",order_status:"Packed",pro_id:102,cust_id:213,sel_id:31,delevery_addr:"102 tech tower",orders:1},
{order_id:7013,order_value:2000,order_date:"24-10-2022",order_month:"1 month",order_status:"Delivered",pro_id:101,cust_id:214,sel_id:31,delevery_addr:"852 tech tower",orders:1},
{order_id:7014,order_value:2000,order_date:"24-10-2022",order_month:"1 month",order_status:"Packed",pro_id:101,cust_id:21,sel_id:3,delevery_addr:"232 street 12",orders:1},
{order_id:7015,order_value:2000,order_date:"25-10-2022",order_month:"1 month",order_status:"Delivered",pro_id:101,cust_id:21,sel_id:31,delevery_addr:"nathuram marg delhi",orders:1},
{order_id:7016,order_value:2000,order_date:"25-10-2022",order_month:"1 month",order_status:"Delivered",pro_id:101,cust_id:21,sel_id:31,delevery_addr:"sarita vihar delhi",orders:1},
{order_id:7017,order_value:2000,order_date:"26-10-2022",order_month:"1 month",order_status:"Delivered",pro_id:101,cust_id:21,sel_id:31,delevery_addr:"122d tree house society",orders:1},
{order_id:7018,order_value:2000,order_date:"26-10-2022",order_month:"1 month",order_status:"Packed",pro_id:101,cust_id:21,sel_id:31,delevery_addr:"124d Bird view street",orders:1},
{order_id:7019,order_value:2000,order_date:"27-10-2022",order_month:"1 month",order_status:"Delivered",pro_id:101,cust_id:21,sel_id:3,delevery_addr:"184d Bird view street",orders:1},
{order_id:7020,order_value:2000,order_date:"27-10-2022",order_month:"1 month",order_status:"Delivered",pro_id:101,cust_id:21,sel_id:31,delevery_addr:"174d Bird view street",orders:1}
])
db.payment.insert({pay_id:1,cust_id:21,order_value:20000,pay_status:"Paid",pay_type:"Gpay"})
db.shipment.insert({shipment_id:10201,deliver_add:"320/14 skytech noida",shiping_status:"processed"})
1.write a query to find the average order value for all customers
Ans:
db.orders.aggregate([{$group:{_id: "$cust_id",avgAmount: { $avg: "$order_value" }}}])
2.Query your database to get top 10 customer whose orders have the
highest value and who have been customers since less than 2 months
ANS:
db.customer.find({"cust_Doj_as_per_month":{"$lt":"2 Months"}}).sort({order_value:-1}).limit(10).pretty()
3.Query your database to find the sum of all orders received in this month
ANS:
db.orders.aggregate([{ $match: { order_month:"This month" } }, { $group: { _id:{month:"$order_month"}, This_Month_orders: { $sum: "$orders" } } }])
4.Query you database to find customers count in various cities, group them by name of cities
ANS:
db.customer.aggregate([{ "$group": { _id: {Customer_city:"$cust_city"}, "count": { "$sum": 1 }, "cust_name": { "$push": "cust_name" } } },
{ "$project": { "size": 1, "customer_count_same_city": { "$size": "$cust_name" } } }])
5.Query you 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:
db.getCollection('seller').aggregate([{
$lookup:
{
from:'customer',
localField:'sel_city',
foreignField:'cust_city',
as:'customerdetails'
}
},
{
"$sort":{
"products_sold":-1
}},
{ $match : { "customerdetails" : { $ne : []}}},
]);