Project 2

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

use ecommerceweb

db.createCollection('customer')

db.customer.insertOne({
'cust_id': 01,
'email': '[email protected]',
'password': 12547,
'first_name': 'Jas',
'middle_name': 'Singh',
'last_name': 'Ajimal',
'dob': 1998-11-05,
'city': 'Chennai',
'current_order': 'Shirt',
'prev_order': 'Jeans'})

db.createCollection('brand_categories_pivot')

db.brand_categories_pivot.insertMany([{
'id': 01,
'brand_id': 01,
'category_id': 01},

{'id': 02,
'brand_id': 02,
'category_id': 02
}])

db.createCollection('brand_subcategories_pivot')

db.brand_subcategories_pivot.insertMany([{
'id': 01,
'brand_id': 01,
'subcategories_id': 01},

{'id': 02,
'brand_id': 02,
'subcategories_id': 02
}])

db.createCollection('brands')

db.brands.insertMany([{
'brand_id': 01,
'name': 'Biba'},

{'brand_id': 02,
'name': 'Laira'
}])

db.createCollection('cart_item')

db.cart_item.insertOne({
'cart_id': 01,
'cust_id':01,
'product_id': 01,
'session_id': 01,
'token': 'FIRSTORDER',
'SKU': 'ABC',
'price': 1000,
'discount': '10%',
'quantity': 2,
'status': 'shipped',
'active': 'yes'})

db.createCollection('category')

db.category.insertOne({
'category_id': 01,
'name': 'Shirt',
'parent_id': 01,
'desc': 'Formal Cotton Shirt',
'title': 'Women Shirts',
'meta_title': 'Formal Wear'})

db.createCollection('cust_address')

db.cust_address.insertOne({
'cust_id': 01,
'address_line1': 'Vinoba Nagar',
'address_line2': 'New Road',
'city': 'Chennai',
'postal_code': 123654,
'country': 'India',
'mobile_no': 8596748597

})

db.createCollection('cust_payment')

db.cust_payment.insertOne({
'cust_id': 01,
'payment_type': 'phonepe',
'provider': 'Mr.Rao',
'acc_no': 123654789,
'expiry_date': 2025-06-07
})

db.createCollection('order_details')

db.order_details.insertOne({
'order_id': 01,
'cust_id': 01,
'total': 2000,
'promo': 'NEWONE',
'discount': '10%',
'grand_total': 2000,
'payment_id': 123,
})

db.createCollection('product')

db.product.insertOne({
'product_id': 01,
'cust_id': 01,
'name': 'Jas Singh Ajimal',
'desc': 'Formal Shirt',
'SKU': 'ABC',
'color': 'black',
'seller': 'John',
'sizes_available': 34,
'quantity_available': 20,
'cost': 2000,
'current_discount': '10%',
'origin_place': 'mumbai',
'isFeatured': 'yes',
'popularity_index': 5,
'avg_ratings': 5,
'total_ratings': 97,
'rating_reviews': 82,
'country_review': 205,
'isOnSale': 'yes',
'categories': 'shirt',
'category_id': 01,
'inventory_id': 01,
'brands': 'Biba'
})

db.createCollection('product_inventory')

db.product_inventory.insertOne({
'inventory_id': 01,
'quantity': 2
})

db.createCollection('order_items')

db.order_items.insertOne({
'order_id': 01,
'product_id': 01,
'cust_id': 01,
'session_id': 12389,
'quantity': 2,
'token': 'FIRSTORDER',
'status': 'shipped',
'sub_total': 2000,
'item_discount': '10%',
'tax': '2%',
'shipping': 'in progress',
'total': 2000
})

db.createCollection('payment_details')

db.payment_details.insertOne({
'payment_id': 123,
'order_id': 01,
'cust_id': 01,
'amount': 2000,
'code': 1234,
'provider': 'Mr.Rao',
'type': 'online',
'mode': 'phonepe',
'status': 'shipped'
})

db.createCollection('product_review')

db.product_review.insertOne({
'product_id': 01,
'parent_id': 01,
'title': 'Women Cotton Shirts',
'rating': 5
})

db.createCollection('seller')

db.seller.insertOne({
'seller_id': 01,
'name': 'John',
'mob_no': 8759482537,
'password': 'abcdqwer',
'product_categories': 'shirt'
})

db.createCollection('shopping_session')

db.shopping_session.insertOne({
'shopping_id': 01,
'cust_id': 01,
'total': 2000
})

db.createCollection('sub_categories')

db.sub_categories.insertOne({
'id': 01,
'name': 'shirt',
'category_id': 01
})

Simple Queries to perform on ecommerce db

1. Write a query to find the average order value for all Customers.

db.order_items.aggregate(
[
{
$group:
{
_id: "cust_id",
avgtotal: {$avg: "$total"}
}
}
]
)

2. Query your database to find the sum of all orders received in this month

db.order_items.aggregate(
[
{
$group:
{
_id: "cust_id",
totalAmount: {$sum: "$Amount"},
count: {$sum: 1}
}
}
]
)

3. Query your database to find customers count in various cities, group them by names of cities

db.customer.find(
[
{
$group:
{
_id: "name",
count: {$city: 1}
}
}
]
)