Project 2

1)

db_test1> db.products.insert({
... name:"hari",
... id:"[email protected]",
... description: "i am here for shopping .....",
... productname:"cellphone",
... productcost:"456848",
... productavailability:"yes",
... addtocart:"yes",
... typeofpayment:"cashondelivery",
... pincodeofdeliverylocation:"522601",
... checkavaialabiltyofdeliver:"yesorno",
... paymentstatus:"success",
... ordertracking:"packing",
... despatchdate:"10-11-22",
... quality:"good",
... typeofcustomer:"premium"
... });
{
acknowledged: true,
insertedIds: { '0': ObjectId("638a10018c50e1b4187a698a") }

1)

select customer_id as Customer,
       count(order_id) as orders,
       avg(order_value) as average_order
       count(case when order_value>5000 then customers.order_value else Null End) as valuegreaterthan5k
       from Customers
       group by Customer;
2)

SELECT customer_id , COUNT(order_id) AS NoofOrders
FROM customer_order_details
GROUP BY customer_id


3)

db.getSumOfFieldsDemo.aggregate([ { $match: { Status: "Active" } }, { $group: { _id: "$Customer_Id", TotalSum: { $sum: "$Price" } } } ]);

4)

db.teams.aggregate([
    {$group : {_id:"$position", count:{$sum:1}}}
])

5)

db.CUSTOMER.insertOne({"CID":1,"CNAME":"Mark"});
db.CUSTOMER.insertOne({"CID":2,"CNAME":"Chris"});
db.CUSTOMER.insertOne({"CID":3,"CNAME":"James"});

db.SHOP.insertOne({"Bill_No":1,"TYPE":"SELL","Amount":1000,"CID":1});
db.SHOP.insertOne({"Bill_No":2,"TYPE":"SELL","Amount":350,"CID":2});
db.SHOP.insertOne({"Bill_No":3,"TYPE":"PURCHASE","Amount":450,"CID":1});
db.SHOP.insertOne({"Bill_No":4,"TYPE":"PURCHASE","Amount":360,"CID":3});
db.SHOP.insertOne({"Bill_No":5,"TYPE":"SELL","Amount":800,"CID":3});