Project 2 : Ecommerce DATABASE (MongoDB)

Q. Design your ecommerce database in MongoDB with the specifications provided

Q.Simple Queries to perform on ecommerce db

(attached into notpad file /txt file.)

1. Design your ecommerce database in MongoDB with the specifications provided.

Ans :

Fistly Create Database Ecommerce Query : use Ecommerce;then insert values in many collection using insertMany([]) statement.

1.customer

db.customers.insertMany([
{
_id: 1,
customer_id: 1,
customer_pass:'xyz1',
customer_fname:'Rajesh',
customer_lname:'Shah',
customer_gender:'M',
customer_dob:'13/11/2000',
customer_address:'12,pawan nagar',
customer_city:'Nashik',
customer_type:'premium',
customer_contactno:8567546421,
customer_email:'[email protected]'
},
{
_id: 2,
customer_id: 2,
customer_pass:'y2z3',
customer_fname:'Lata',
customer_lname:'Mohan',
customer_gender:'F',
customer_dob:'12/01/1999',
customer_address:'Sardar-Patel society',
customer_city:'Mumbai',
customer_type:'premium',
customer_contactno:7834239575,
customer_email:'[email protected]'
},
{
_id: 3,
customer_id: 3,
customer_pass:'zxz1',
customer_fname:'Rahul',
customer_lname:'More',
customer_gender:'M',
customer_dob:'12/03/1990',
customer_address:'flat 2,Sharada nagar',
customer_city:'Nagpur',
customer_type:'Normal',
customer_contactno:8043403893,
customer_email:'[email protected]'
}
]);

2.Cart

db.cart.insertMany([
{
_id: 1,
cart_id:1,
cart_item_qty: 3,
product_id: 3,
customer_id: 1
},
{
_id: 2,
cart_id:2,
cart_item_qty: 1,
product_id: 1,
customer_id: 2
},
{
_id: 3,
cart_id:3,
cart_item_qty: 5,
product_id: 2,
customer_id: 3
}
,
{
_id: 4,
cart_id:4,
cart_item_qty: 1,
product_id: 5,
customer_id: 5
}
,
{
_id: 5,
cart_id:5,
cart_item_qty: 4,
product_id: 7,
customer_id: 4
}
]);

3.Category

db.categories.insertMany([
{
_id:1,
category_id:1,
category_name: 'Glocery',
sub_categories: ['Rice', 'Atta', 'Oil']
},
{
_id:2,
category_id:2,
category_name: 'Electronics',
sub_categories: ['Computers', 'Laptops', 'Mobiles']
},
{
_id:3,
category_id:3,
category_name: 'Books',
sub_categories: ['Fiction', 'Biography', 'Fantacy Fiction','Non Fiction']
},
{
_id:4,
category_id:4,
category_name: 'Fashion',
sub_categories: ['mens', 'womens','kids']
},
{
_id:5,
category_id:5,
category_name: 'Games and Consoles',
sub_categories: ['titles', 'console']
}
]);

4.Product

db.products.insertMany([
{
_id:1,
product_id:1,
product_name:'Laptop',
product_model:'EL90',
product_brand:'Lenovo',
product_segments: ['Computers', 'Laptops'],
product_cost: 75000,
categories_name: 'Electronics'
},
{
_id:2,
product_id:2,
product_name: 'Smart Phone',
product_model: 'EG1',
product_brand: 'Google',
product_segments: ['smartphone', 'Android'],
product_cost: 80000,
categories_name: 'Electronics'
},
{
_id:3,
product_id:3,
product_name: 'Happiness in Mind',
product_model: 'B001',
product_segments: ['Book', 'Novel'],
product_cost: 750,
categroy_name: 'Books'
},
{
_id:4,
product_id:4,
product_name: 'Pepe Jeans',
product_model: 'FAM1',
product_segments: ['pepejeans', 'mensjeans',slim],
product_cost: 1500,
categroy_name: 'Fashion'
},
{
_id:5,
product_id:5,
product_name: 'Fortune mustard Oil',
product_model: 'GL30',
product_segments: ['Fresh_oil','oils'],
product_cost: 620,
categroy_name: 'Glocery'
},
]);

5.Orders

db.orders.insertMany([
{
_id: 1,
product_id:5,
product_name: 'Fortune mustard Oil',
customer_id: 3,
order_qty: 2,
order_status: 'Delivered',
order_date: new Date('2022-09-10')
},
{
_id: 2,
product_id:4,
product_name: 'Pepe Jeans',
customer_id: 1,
order_qty: 1,
order_status: 'Dispatched',
order_date: new Date('2022-11-27')
},
{
_id: 3,
product_id:3,
product_name: 'Happiness in Mind',
customer_id: 4,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-10')
},
{
_id: 4,
product_id:2,
product_name: 'Smart Phone',
customer_id: 2,
order_qty: 1,
order_status: 'cancelled',
order_date: new Date('2022-11-18')
},
{
_id: 5,
product_id:1,
product_name: 'Laptop',
customer_id: 5,
order_qty: 1,
order_status: 'Dispatched',
order_date: new Date('2022-11-01')
}
]);

6.Seller

db.sellers.insertMany([
{
_id: 1,
seller_id:1,
seller_name: 'Ratan Gaikwad',
seller_business: 'RK Electronics',
seller_mobileno: 8456789123,
seller_address: 'Dadar',
seller_city: 'Mumbai'
},
{
_id: 2,
seller_id:2,
seller_name: 'Ajay Kumar',
seller_business: 'ABC Sports and Fashion',
seller_mobileno: 7890123456,
seller_address: 'MG Market Place',
seller_city: 'Delhi'
}
{
_id: 3,
seller_id:3,
seller_name: 'Sudesh Rajan',
seller_business: 'SR Kirana Shop',
seller_mobileno: 9901248560,
seller_address: 'Near Bajarpeth',
seller_city: 'Pune'
}
{
_id: 4,
seller_id:4,
seller_name: 'Reena Kumari',
seller_business: 'Unity Book Stall',
seller_mobileno: 8701249561,
seller_address: 'Shop no S-3 Mahalaxmi Mall ',
seller_city: 'Mumbai'
}
]);

7.Catlog

db.catlog.insertMany([
{
seller_name: 'Ajay Kumar',
product_name: 'Pepe Jeans',
product_qty: 50,
},
{
seller_name: 'Ratan Gaikwad',
product_name: 'Laptop',
product_qty: 15,
},
{
seller_name: 'Sudesh Rajan',
product_name: 'Fortune mustard Oil',
product_qty: 30,
},
{
seller_name: 'Reena Kumari',
product_name: 'Happiness in Mind',
product_qty: 15,
}
]);

Q 2 Simple Queries to perform on ecommerce db
QUESTIONS ANS :

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

Query:

• db.products.aggregate([{$group:{_id:'product_cost', avg:{$avg:'$product_cost'}}}])

2.Query your database to get top 10 customers whose orders have the highest values and who have been customers since less than two months.

Query:

• db.customers.aggregate([{$sort: {purchase_amount:-1}])

3.Query your database to find the sum of all orders recieved in this month.

Query:

• db.orders.aggregate([{$group:{_id:'Total_cost', sum:{$sum:'$product_cost'}}}])

4.Query you databse to find customers count in the various cities,group them by names of cities.

Query:

• db.customers.aggregate([{$group:{_id:'customer_city', $match:{customer_city}}}])

5.Query your databse 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 the same city to lowest.

Query:

• db.getCollection('seller').aggregate([{
  $lookup:
  {
  from:'customer',
  localField:'seller_city',
  foreignField:'cust_city',
  as:'customerdetails'
  }
  },
  {
  "$sort":{
  "cust_city":-1
  }
  },
  { $match : {
  "customerdetails" : { $ne : []}
  }
  },
  ]);