Project 2
customer_id: 1,
product:_id:111,
product_name:'Fashion',
add_cart:'2',
check_out:'order book',
make_payment:'pay'
},
{
_id: 2,
customer_id: 2,
product:_id:222,
product_name:'Cosmetics',
add_cart:'2',
check_out:'not book'
make_payment:'pay'
},
{
_id: 3,
customer_id: 3,
product:_id:333,
product_name:'Mobile Phones'
add_cart:'1',
check_out:'order book',
make_payment:'pay'
},
{
_id: 4,
customer_id: 4,
product:_id:444,
product_name:'Laptops',
add_cart:'3',
check_out:'not book',
make_payment:'not pay'
}
]);
----------------------------------
h)db.ecommerce.insertMany([
{
_id: 1,
customer_id: 1,
product:_id:111,
categroy_name: 'Home Applicances',
sub_categories: ['Refrigeraor', 'Washing Machine']
add_product:'1'
},
{
_id: 1,
customer_id: 2,
product:_id:222,
categroy_name: 'Fasion',
sub_categories: ['Shirt', 'Pant']
add_product:'2'
},
{
_id: 3,
customer_id: 3,
product:_id:333,
categroy_name: 'Mobiles',
sub_categories: ['Samsung', 'Nokia']
add_product:'0'
}
]);
i)
db.orders.insertMany([
{
_id: 1,
product_name: 'Puma',
customer_id: 1,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-15'),
order:'receive'
},
{
_id: 2,
product_name: 'Denim',
customer_id: 2,
order_qty: 2,
order_status: 'Dispatched',
order_date: new Date('2022-11-27'),
order:' Not receive'
},
{
_id: 3,
product_name: 'Sony',
customer_id: 3,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-10'),
order:'receive'
},
{
_id: 4,
product_name: 'Smart Phone',
customer_id: 1,
order_qty: 1,
order_status: 'cancelled',
order_date: new Date('2022-11-18'),
order:'cancel'
},
{
_id: 5,
product_name: 'Laptop',
customer_id: 1,
order_qty: 1,
order_status: 'Dispatched',
order_date: new Date('2022-11-01')
order:'Not receive'
}
]);
j)db.seller.insertMany([
{
_id: 1,
seller_name: 'Girias',
seller_business: 'Electronics',
seller_mobileno: 4567891230,
seller_address: 'TN',
seller_city: 'Cbe',
product_buy:'Refridgerator',
oder_qty:'1',
total_bill:20000,
order_status: 'Delivered',
order_date: new Date('2022-11-10')
},
{
_id: 2,
seller_name: 'Denim',
seller_business: 'Fashion',
seller_mobileno: 7890123456,
seller_address: 'Shirt',
seller_city: 'Banglore',
product_buy:'Shirt',
oder_qty:'2',
total_bill:5000,
order_status: 'Dispatched',
order_date: new Date('2022-11-01')
}
]);
k)db.seller.insertMany([
{
_id: 1,
seller_name: 'shiva',
seller_business: 'Electronics',
seller_mobileno: 4567891230,
seller_address: 'Tn',
seller_city: 'India'
},
{
_id: 2,
seller_name: 'RTRRS',
seller_business: 'Fashion',
seller_mobileno: 7890123456,
seller_address: 'London',
seller_city: 'America'
}
]);
----------------------------------------------
1.Create your collections for your ecommerce database in MongoDB and fill it with sample information
2.Submit all the queries used to create the database
db.customer.insertMany([
{
_id: 1,
customer_id: 1,
customer_fname:'Sridhar',
customer_lname:'Rajendran',
customer_dob:1996,
customer_address:'Tn',
customer_city:'Tn',
is_premium: 1,
customer_contactno:9012345767,
customer_email:'[email protected]'
},
{
_id: 2,
customer_id: 2,
customer_fname:'Varsha',
customer_lname:'Harsha',
customer_dob:2000,
customer_address:'Tn',
customer_city:'Tn',
is_premium: 0,
customer_contactno:2345678901,
customer_email:'[email protected]'
},
{
_id: 3,
customer_id: 3,
customer_fname:'dhoni',
customer_lname:'msd',
customer_dob:1996,
customer_address:'csk',
customer_city:'Chennai',
is_premium: 0,
customer_contactno:5678901234,
customer_email:'[email protected]'
}
]);
db.cart.insertMany([
{
_id: 1,
cart_item_qty: 2,
product_id: 222,
customer_id: 1
},
{
_id: 2,
cart_item_qty: 1,
product_id: 221,
customer_id: 2
},
{
_id: 3,
cart_item_qty: 1,
product_id: 222,
customer_id: 3
}
,
{
_id: 4,
cart_item_qty: 1,
product_id: 555,
customer_id: 2
}
,
{
_id: 5,
cart_item_qty: 1,
product_id: 444,
customer_id: 3
}
]);
db.categories.insertMany([
{
_id:1,
categroy_name: 'Home Applicances',
},
{
_id:2,
categroy_name: 'Computers, Laptop and Mobiles',
sub_categories: ['Computers', 'Laptops', 'Mobiles']
},
{
_id:3,
categroy_name: 'Books',
sub_categories: ['Fiction', 'Biography', 'Fantacy Fiction','Non Fiction']
},
{
_id:4,
categroy_name: 'Fashion',
sub_categories: ['mens', 'womens']
},
{
_id:5,
categroy_name: 'Games and Consoles',
sub_categories: ['titles', 'console']
}
]);
db.products.insertMany([
{
_id:1,
product_name: 'Laptop',
product_model: 'C201',
product_brand: 'HP',
product_segments: ['Computers', 'Laptops'],
product_cost: 100000,
categroy_name: 'Computers, Laptop and Mobiles'
},
{
_id:2,
product_name: 'Smart Phone',
product_model: 'C202',
product_brand: 'Google',
product_segments: ['Mobile', 'Android'],
product_cost: 60000,
categroy_name: 'Computers, Laptop and Mobiles'
},
{
_id:3,
product_name: 'Lord of the Rings',
product_model: 'C501',
product_segments: ['Book', 'Novels'],
product_cost: 1500,
categroy_name: 'Books'
},
{
_id:4,
product_name: 'Pokemon Scarlet',
product_model: 'C701',
product_segments: ['pokemon', 'gen9'],
product_cost: 4500,
categroy_name: 'Games and Consoles'
},
{
_id:5,
product_name: 'Nintendo Switch',
product_model: 'C702',
product_segments: ['console'],
product_cost: 30000,
categroy_name: 'Games and Consoles'
},
]);
db.orders.insertMany([
{
_id: 1,
product_name: 'Books',
customer_id: 1,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-15')
},
{
_id: 2,
product_name: 'Accessiors',
customer_id: 2,
order_qty: 2,
order_status: 'Dispatched',
order_date: new Date('2022-11-27')
},
{
_id: 3,
product_name: 'Games',
customer_id: 3,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-10')
},
{
_id: 4,
product_name: 'Smart Phone',
customer_id: 1,
order_qty: 1,
order_status: 'cancelled',
order_date: new Date('2022-11-18')
},
{
_id: 5,
product_name: 'Laptop',
customer_id: 1,
order_qty: 1,
order_status: 'Dispatched',
order_date: new Date('2022-11-01')
}
]);
db.seller.insertMany([
{
_id: 1,
seller_name: 'Girias',
seller_business: 'Electronics',
seller_mobileno: 4567891230,
seller_address: 'Tn',
seller_city: 'Mumbai'
},
{
_id: 2,
seller_name: 'RTRRS',
seller_business: 'Fashion',
seller_mobileno: 7890123456,
seller_address: Vitenom',
seller_city: 'Delhi'
}
]);
db.catlog.insertMany([
{
seller_name: 'Ankit Chaowda',
product_name: 'Smart Phone',
product_qty: 10,
},
{
seller_name: 'Ankit Chaowda',
product_name: 'Laptop',
product_qty: 5,
},
{
seller_name: 'Bakely Harvard',
product_name: 'Pokemon Scarlet',
product_qty: 20,
},
{
seller_name: 'Bakely Harvard',
product_name: 'Nintendo Switch',
product_qty: 15,
}
]);
1.Write a query to find the average order value for all Customers.
db.products.aggregate([{$group:{_id:'order':{$avg:'$order'}}}])
2)Query your database to get top 10 customers whose orders have the highest value and who have been customers since less than 2 months
db.orders.insertMany([
{ _id: 1, product_id: 111, status: 1 },
{ _id: 2, product_id: 222, status: 1 },
{ _id: 3, product_id: 333, status: 1 },
{ _id: 4, product_id: 444, status: 1 },
{ _id: 5, product_id: 555, status: 1 },
{ _id: 6, product_id: 666, status: 1 },
{ _id: 7, product_id: 777, status: 1 },
{ _id: 8, product_id: 888, status: 1 },
{ _id: 9, product_id: 999, status: 1 },
{ _id: 10, product_id: 1010, status: 1 },
]);
db.products.insertMany([
{_id:101,product_name:'cloth', order_place:'200 months before' membre:200},
{_id:102,product_name:'soft toy', order_place:'1 days before ' membre:90},
{_id:103,product_name:'phone', order_place:'30 days before' membre:30},
{_id:101,product_name:'watch', order_place:'12 days before' membre:150},
]);
db.orders.find().sort({order:10}).limit(10)
db.products.find($match:{membre:{$lt:200}})
3)Query your database to find the sum of all orders received in this month
db.order.aggregate([{$group:{_id:'Total_order': {$sum:'$order'}}}])
4)Query your database to find customers count in various cities, group them by names of cities
db.customers.find({$count:"customer_city","customer_name"})
5) Query your 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.
db.sellers.aggregate([$group:{_id:'seller_name':{$match:'customer_city'}}}])
db.orders.aggregate([$sort :{order: 1}:{$match:'customer_city'}}}])