Project 2

PROJECT 2 WORKSHOP2 (MongoDB)

CUSTOMERS -

db.customers.insertMany([
{_id:1,Customer_id:101,Cust_Name:"Ram",Cust_type:"normal",phone:90001,Cust_address:"ram naghar",city:"delhi",state:"delhi",postalCode:"110001",country:"India",creditLimit:50000},
{_id:2,Customer_id:102,Cust_Name:"Shyam",Cust_type:"premium",phone:90002,Cust_address:"shyam naghar",city:"delhi",state:"delhi",postalCode:110001,country:"India",creditLimit:50000},
{_id:3,Customer_id:103,Cust_Name:"Mohan",Cust_type:"normal",phone:90003,Cust_address:"mohan naghar",city:"delhi",state:"delhi",postalCode:110001,country:"India",creditLimit:50000},
{_id:4,Customer_id:104,Cust_Name:"Shoan",Cust_type:"premium",phone:90004,Cust_address:"shoan naghar",city:"delhi",state:"delhi",postalCode:110001,country:"India",creditLimit:50000}
]);

ORDERS -
db.orders.insertMany([
{_id:9,order_id:201,customer_id:101,order_Number:1001,Payment_id:501,order_date:"2022-08-23",ship_date:"2022-08-24",shipper_id:601,TransactStatus:"sucessful",Paid:500,Payment_date:"2022-08-26"},
{_id:10,order_id:202,customer_id:102,order_Number:1002,Payment_id:502,order_date:"2022-08-25",ship_date:"2022-08-24",shipper_id:602,TransactStatus:"sucessful",Paid:500,Payment_date:"2022-08-26"},
{_id:11,order_id:203,customer_id:103,order_Number:1003,Payment_id:503,order_date:"2022-06-24",ship_date:"2022-06-24",shipper_id:603,TransactStatus:"sucessful",Paid:500,Payment_date:"2022-08-26"},
{_id:12,order_id:204,customer_id:104,order_Number:1004,Payment_id:504,order_date:"2022-08-24",ship_date:"2022-08-24",shipper_id:604,TransactStatus:"sucessful",Paid:500,Payment_date:"2022-08-26"}
]);

PRODUCTS -
db.products.insertMany([
{_id:5,product_Id:301,productName:"fan",productScale:"4kg",quantityInStock:500,buyPrice:1700,MSRP:2500},
{_id:6,product_Id:302,productName:"toy",productScale:"light_weight",quantityInStock:1000,buyPrice:500,MSRP:800},
{_id:7,product_Id:303,productName:"shirt",productScale:"XL",quantityInStock:200,buyPrice:700,MSRP:700},
{_id:8,product_Id:304,productName:"pen",productScale:"smooth writing",quantityInStock:2000,buyPrice:10,MSRP:15}
]);

PAYMENTS-

db.payments.insertMany( [
{_id:13,Payment_id:501,Payment_type:"online",allowed:"debit_card"},
{_id:14,Payment_id:502,Payment_type:"online",allowed:"debit_card"},
{_id:15,Payment_id:503,Payment_type:"online",allowed:"credit_card"},
{_id:16,Payment_id:504,Payment_type:"online",allowed:"debit_card"}
] );

SHIPPER -

db.shipper.insertMany([
{_id:17,shipper_id:601,companyName:"abc",phone:"222324"},
{_id:18,shipper_id:602,companyName:"bcd",phone:"222325"},
{_id:19,shipper_id:603,companyName:"cde",phone:"222326"},
{_id:20,shipper_id:604,companyName:"def",phone:"222327"}
]);

CATEGORY-

db.category.insertMany([
{_id:21,category_id:701,categoryName:"toys",description:"baby car"},
{_id:22,category_id:702,categoryName:"toys",description:"baby doll"},
{_id:23,category_id:703,categoryName:"Instruments",description:"TV"},
{_id:24,category_id:704,categoryName:"cloths",description:"men shirt"}
]);

SUPPLIER-

db.supplier.insertMany([
{_id:25,supplier_id:901,companyName:"abc",itemName:"fan",address:"50_ram_naghar",city:"delhi",state:"delhi",postalCode:110001,country:"India",phone:800001,Email:"[email protected]",PaymentMethod:"online",customer_id:201},
{_id:26,supplier_id:902,companyName:"bcd",itemName:"toy",address:"52_shyam_naghar",city:"delhi",state:"delhi",postalCode:110001,country:"India",phone:800001,Email:"[email protected]",PaymentMethod:"online",customer_id:202},
{_id:27,supplier_id:903,companyName:"cde",itemName:"shirt",address:"53_mohan_naghar",city:"delhi",state:"delhi",postalCode:110001,country:"India",phone:800001,Email:"[email protected]",PaymentMethod:"online",customer_id:203},
{_id:28,supplier_id:904,companyName:"abc",itemName:"pen",address:"50_ram_naghar",city:"delhi",state:"delhi",postalCode:110001,country:"India",phone:800001,Email:"[email protected]",PaymentMethod:"online",customer_id:204}
]);

ORDERDetails-

db.orderDetails.insertMany([
{_id:29,order_id:201,product_id:301,orderNumber:401,price:200,quantity:5,total:1000,size:"M",colour:"red",shipDate:"2022-05-23",billDate:"2022-05-23"},
{_id:30,order_id:202,product_id:302,orderNumber:402,price:100,quantity:2,total:1000,size:"M",colour:"red",shipDate:"2022-05-23",billDate:"2022-05-23"},
{_id:31,order_id:203,product_id:303,orderNumber:403,price:300,quantity:10,total:1000,size:"M",colour:"red",shipDate:"2022-05-23",billDate:"2022-05-23"},
{_id:32,order_id:204,product_id:304,orderNumber:404,price:500,quantity:3,total:1000,size:"M",colour:"red",shipDate:"2022-05-23",billDate:"2022-05-23"}
]);

ADMIN-

db.admin.insertMany([
{_id:33,admin_id:801,supplier_id:901,product_id:301},
{_id:34,admin_id:802,supplier_id:902,product_id:302},
{_id:35,admin_id:803,supplier_id:903,product_id:303},
{_id:36,admin_id:804,supplier_id:904,product_id:304}
]);

CART-
db.cart.insertMany([
{_id:37,cart_id:851,customer_id:101,payment_id:501,order_id:201},
{_id:38,cart_id:852,customer_id:102,payment_id:502,order_id:202},
{_id:39,cart_id:853,customer_id:103,payment_id:503,order_id:203},
{_id:40,cart_id:854,customer_id:104,payment_id:504,order_id:204}
]);

QUE 2 (1)

SOLUTION:-
db.orders.aggregate([{$group:{_id:"order_id",AverageValue:{$avg:"$Paid"}}}]);

QUE 2 (2)

SOLUTION:-

db.orders.find({order_date:{$lt:"2022-11-15"}}).sort( {"Paid":1} ).limit(10);

QUE 2 (3)

SOLUTION:-

db.orders.aggregate([{$match:{order_date:{$gte:"2022-07-15",$lte:"2022-09-30"}}}, {$group: {_id:"$customer_id",num_of_orders:{$sum:"$order_Number"}}}])

QUE 2 (4)

SOLUTION:-

db.customers.aggregate( [ {"$group":{_id:"$city","count":{"$sum":1} } } ] );

QUE 2 (5)

SOLUTION:-

db.getCollection('supplier').aggregate([{
$lookup:
{
from:'Customers',
localField:'city',
foreignField:'city',
as:'customerdetails'
}
},
{
"$sort":{
"quantity":-1
}
}]);