Project 2
test>use ecommerce
switched to db ecommerce // database creation
ecommerce>
ecommerce>db.createcollection("customers") //table creation
ecommerce>db.customers.InsertMany([{"ID":"[email protected]",
"Fname": "Bhuvana","Lname":"Kuppan",
"contact": 12345677891,
"Address":{"Country":"India","State":"Tamilnadu","City":"chennai","Street":"abcd","ZIP":"600073"},
"Password":"asdfgf;lkjhj",
"Primier customer":true,
"shipping address":{"Country":"India","State":"Tamilnadu","City":"chennai","Street":"abcd","ZIP":"600073"}
}
{"ID":"[email protected]",
"Fname": "Manju","Lname":"Kuppan",
"contact": 976543211,
"Address":{"Country":"India","State":"Tamilnadu","City":"chennai","Street":"abcd","ZIP":"603310"},
"Password":"asdfgf;lkjhj",
"Primier customer":false,
"shipping address":{"Country":"India","State":"Tamilnadu","City":"chennai","Street":"abcd","ZIP":"603310"}
])
ecommerce>db.createcollection("products")
ecommerce>db.products.InsertMany([
{"Name of the product": ladies wear,
"seller":"Bhuvana",
"Products":[{
"product":"womens top",
"Price":545,
"quantity":20,
feature:"Good to wear",
}]
},
{"Name of the product": Kid wear,
"seller":"Bhuvana",
"Products":[{
"product":" kids wear ",
"Price":500,
"quantity":20,
"feature":"comfort to wear",
}]
},
ecommerce>db.createcollection("cart")
ecommerce>db.cart.InsertMany([
{
id:"12094768698"
{
"Userid":"[email protected]",
"PaymentStatus":"processed",
"Status":"Shipped",
"Amount":500,
"Item":[
{"Sku":"4ph"
"quantity":1,
"Price":29.99,
"discountt":"1.99",
"tax":12%,
}
"Shipping address":{"Country":"India",
"State":"Tamilnadu",
"City":"chennai",
"Street":"abcd",
"ZIP":"600073"}
}])
ecommerce>db.createcollection("Payments")
ecommerce>db.payments.InsertMany([
{
id:"12094768698"
}
{
"UserId" : "[email protected]",
"Type" :"credit status":varified
"card":{
"type":"visa",
"Last four number":"1212",
"ExpiryMonth":12,
""expiry year":2023,
"cvv verified":true
}
"tracking id":30406789
}])
ecommerce>db.createcollection("Category")
ecommerce>db.Category.InsertMany([
{
id:"1234587975"
}
{
"category id":"category",
"Category name":["Women's wears",
"Mens wears",
"Kids wear"]
}])
ecommerce>db.createcollection("Subcategories")
ecommerce>db.subcategories.InsertMany([
{
"id":"1344676879"
}
{
"Subcategory":{
"women's wear" :["top",
"Jeans","Western","chudi","saree" ],
"Men's wear" :["jeans","shirt","dhottis"],
"Kids wear" :["frogs","casuals","summer wears"]
}
])
ecommerce>db.createcollection("seller")
ecommerce>db.seller.InsertMany([
{
"id":2345678988765,
}
{
"sellerid":"seller@123",
"Seller Name":"Anitha",
"Contact No":"34478090443",
"provided category":"women's wear",
"Product verified":true,
"Shop name":"Anitha Boutique"}
])
ecommerce>db.createcollection("")
ecommerce>db.cart.InsertMany([
{
"id":2345678988765,
}
{"AdminId":"Admin@1",
"Admin Name": "Parthi",
"customer verified":true,
"seller verified":ture,
"product verified":true,
}
])
Simple Queries to perform on ecommerce db
1.Write a query to find the average order value for all Customers.
ecommerce>db.customers.find({ $avg:
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
ecommerce>db.customers.find({
$and: [{
"orders" :
{$exists: 1
),
month:{
$lt: 2
}
]})
3.Query your database to find the sum of all orders received in this month
db.orders.aggregate($and:[
{
$group: {
_id: null,
totalorders: { $sum: '$orders' },
},
month:{$lte:1}
},
]);
4.Query your database to find customers count in various cities, group them by names of cities
ecommerce>db.customers.count( [
{
$group: {
city: [chennai,bangalore,Delhi],
}
] )
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.
ecommerce>db.seller.find([
{
customers:{
city:chennai,
}
}
])