Project 2
ANSWER:-
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:"Vivek",admin_city:"Rishikesh",admin_mobil:"9890768754"},{admin_id:12,admin_name:"arun",admin_city:"Thanjavur",admin_mobil:"37901232"}])
db.customer.insertMany([
{cust_type:"Premium",cust_id:21,cust_name:"kishore",cust_gender:"M",cust_Doj_as_per_date:"28-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"Thiruvalur",cust_mb_no:"683937292917",order_value:30000},
{cust_type:"Premium",cust_id:22,cust_name:"arun",cust_gender:"M",cust_Doj_as_per_date:"22-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"mpk",cust_mb_no:"64846399272",order_value:20000},
{cust_type:"Premium",cust_id:23,cust_name:"varun",cust_gender:"M",cust_Doj_as_per_date:"23-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"gpd",cust_mb_no:"05847699202",order_value:3000},
{cust_type:"Normal",cust_id:24,cust_name:"kali",cust_gender:"M",cust_Doj_as_per_date:"24-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"gpd",cust_mb_no:"3463289012",order_value:6000},
{cust_type:"Premium",cust_id:25,cust_name:"vasu",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"lkms",cust_mb_no:"0584639893",order_value:5000},
{cust_type:"Normal",cust_id:26,cust_name:"kumar",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"mpk",cust_mb_no:"9474846383",order_value:8000},
{cust_type:"Premium",cust_id:27,cust_name:"arivazhagan",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"chennai",cust_mb_no:"9383735372",order_value:4000},
{cust_type:"Normal",cust_id:28,cust_name:"parthi",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"poneri",cust_mb_no:"94736389838",order_value:5000},
{cust_type:"Premium",cust_id:29,cust_name:"prithiv",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"ennur",cust_mb_no:"84846483983",order_value:4000},
{cust_type:"Premium",cust_id:211,cust_name:"jaga",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"kathivakkam",cust_mb_no:"94748936383",order_value:4000},
{cust_type:"Normal",cust_id:212,cust_name:"prathap",cust_gender:"M",cust_Doj_as_per_date:"25-10-2022",cust_Doj_as_per_month:"1 Month",cust_city:"villivakam",cust_mb_no:"93745478383",order_value:4500},
{cust_type:"Premium",cust_id:213,cust_name:"saravanan",cust_gender:"M",cust_Doj_as_per_date:"25-09-2022",cust_Doj_as_per_month:"2 Month",cust_city:"Thanjavur",cust_mb_no:"9374648383",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:"Thanjavur",cust_mb_no:"75647486373",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:"pereyapulliuoor",cust_mb_no:"8464638736",order_value:20000}
])
db.seller.insertMany([
{sel_id:31,sel_name:"Kalai",sel_city:"Thanjavur",pro_catagory:"furniture",sel_mbl_no:"3282943891",products_sold:23},
{sel_id:32,sel_name:"Kalai",sel_city:"mpk",pro_catagory:"cloths",sel_mbl_no:"3282943891",products_sold:22},
{sel_id:33,sel_name:"Kalai",sel_city:"gpd",pro_catagory:"Electronic",sel_mbl_no:"3282943891",products_sold:2},
{sel_id:34,sel_name:"Kalai",sel_city:"ennur",pro_catagory:"furniture",sel_mbl_no:"3282943891",products_sold:30},
{sel_id:35,sel_name:"Kalai",sel_city:"kathivakkam",pro_catagory:"Electronic",sel_mbl_no:"3282943891",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:"202/10 tharamani vk stree",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:"22/10 tharamani vk stree",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/10 tharamani vk stree",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:"242/10 tharamani vk stree",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:"252/10 tharamani vk stree",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:"262/10 tharamani vk stree",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:"272/10 tharamani vk stree",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:"282/10 tharamani vk stree",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:"292/10 tharamani vk stree",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:"213/10 tharamani vk stree",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:"204/10 tharamani vk stree",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:"205/10 tharamani vk stree",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:"206/10 tharamani vk stree",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:"207/10 tharamani vk stree",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:"202/10 tharamani vk stree",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:"202/10 tharamani vk stree",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:"202/10 tharamani vk stree",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:"202/10 tharamani vk stree",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:"202/10 tharamani vk stree",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:"202/10 tharamani vk stree",orders:1}
])
db.payment.insertOne({pay_id:1,cust_id:21,order_value:20000,pay_status:"Paid",pay_type:"Gpay"})
db.shipment.insertOne({shipment_id:10201,deliver_add:"320/10 kg nagar chennai",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 : []}}},
]);