Project 2

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

a.)Every Customer would register on the ecommerce website with all their personal information

b.)Each Customer has a type of being normal or premium member

c.)The customer should be able to view products and add them to cart

d.)Products can belong to multiple categories, brands and segments.

e.)Each category can have subcategories as well.

f.)Each products can also have multiple tags associated with it.\

g.)From the cart, the customer should be able to check out and make payment and his/her order would be booked which the customer can track

h.)The admin of the ecommerce and the seller both can add products under multiple categories, segments and tags

i.)The seller would receive the orders and should be able to fulfil them.

j.)The order shipping details and the billing information are very important things to be stored in the database as well

k.)Every Seller would register on the ecommerce website with all their personal information.

ANSWERS

a.)Every Customer would register on the ecommerce website with all their personal information

QUERY

db.customers.insertmany({name:"S.Muthu kumar",age:34,D.O.B:"08/04/1988",Phone_number:9876543210,address:"234,vanaarapettai street,Tirunelveli",email_id:"[email protected]"});

-------------------------------------------------------------------------------------------------------------------------------

b.)Each Customer has a type of being normal or premium member

QUERY

db.customers.insertone({Type_of_member:"normal or premium"});

--------------------------------------------------------------------------------------------------------------------------------

c.)The customer should be able to view products and add them to cart

QUERY

for view

db.createView("all_products","products",[{$match:{allproducts:1} }]);

for add to cart

cart.products.push({ _id: new ObjectId(productToAdd._id), name: productToAdd.name, price: productToAdd.price, quantity: 1 }); }

--------------------------------------------------------------------------------------------------------------------------------

d.)Products can belong to multiple categories, brands and segments.

QUERY

db.products.add( {

Category: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Category',
    required: true
  },

Brand: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Brand',
    required: true
  },

Segments: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Segments',
    required: true
  },
});

-------------------------------------------------------------------------------------------------------------

e.)Each category can have subcategories as well.

QUERY

db.products.add ( {

SubCategory: {
      type: mongoose.Schema.Types.ObjectId,
      ref: 'SubCategory',
      required: false
  },});
--------------------------------------------------------------------------------------------------------------
f.)Each products can also have multiple tags associated with it.\

QUERY

db.tags.insert({"tags":["red", "tall", "cheap"]});
db.tags.insert({"tags":["blue", "tall", "expensive"]});
db.tags.insert({"tags":["blue", "little", "cheap"]}); 

--------------------------------------------------------------------------------------------------------------
g.)From the cart, the customer should be able to check out and make payment and his/her order would be 
booked which the customer can track

QUERY

db.products.find({payment:"paid",order:"booked",track_order:"yes"});
------------------------------------------------------------------------------------------------------------
h.)The admin of the ecommerce and the seller both can add products under multiple categories, segments and tags

QUERY

db.products.insert({category_id:"23387",segments:[{segment_id:"23",tags:tag_id:"45"},{},{}}]});

------------------------------------------------------------------------------------------------------------

j.)The order shipping details and the billing information are very important things to be stored in 
the database as well

QUERY

db.productbills.insertOne( {
product_shipped:"shipped",
address:"23,Nesamanikandan street,
phone_number:9876543210,
year:2020,

billing_details:["Received"],
total_amount:[2354],
mode_of_payment:["Cash on delivery"]

});
------------------------------------------------------------------------------------------------------------------

k.)Every Seller would register on the ecommerce website with all their personal information.

QUERY

db.sellers.insertmany({name:"S.Raj kumar",age:44,D.O.B:"23/04/1988",Phone_number:0123456789,address:"234,vanaarapettai street,Tiruchirapalli",email_id:"[email protected]"});

------------------------------------------------------------------------------------------------------------

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

ANSWERS

1. Create your collections for your ecommerce database in MongoDB and fill it with sample information

QUERY

db.createCollection("Name","website link","customer care","about us","contact us");

db.details.insert( { Name:"AJIO",website link:"https://ajio.com",customer care:"Mr.Vel murugan",about us:"we are mainly for fullfill your satisfaction",contact us:9876543221 } );

----------------------------------------------------------------------------------------------------------------------------------

2.)Submit all the queries used to create the database

QUERY

MONGODB use DATABASE_NAME is used to create database.

use DATABASE_NAME;

To give database name

use details;

SUBMIT BELOW THE QUERY TO DISPLAYS THE DATABASE NAME

db.details.insert( { Name:"AJIO",website link:"https://ajio.com",customer care:"Mr.Vel murugan",
about us:"we are mainly for fullfill your satisfaction",contact us:9876543221 } );
-------------------------------------------------------------------------------------------------------------

Simple Queries to perform on ecommerce db

1. Write a query to find the average order value for all Customers.
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
3. Query your database to find the sum of all orders received in this month
4. Query your database to find customers count in various cities, group them by names of cities
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.

ANSWERS

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

QUERY

SELECT AVG (orders.purchase_amount),customers.customer_id FROM orders INNER JOIN customers ON orders.customer_id=customers.customer_id;

-------------------------------------------------------------------------------------------------------------------------------

 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.

QUERY
   SELECT MAX((orders_value) FROM customers WHERE current_date -purchased_date < 62;
    -------------------------------------------------------------------------------------------------------
3.) Query your database to find the sum of all orders received in this month

QUERY

  SELECT SUM(orders) WHERE ordered_date BETWEEN value_1 and value_2;

   --------------------------------------------------------------------------------------------------------

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

QUERY

     SELECT COUNT(customer_id),city FROM `customer_details` GROUP BY city;

      -----------------------------------------------------------------------------------------------------
      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.

    QUERY

     SELECT s.seller_id, s.name AS seller_name, s.city AS seller_city, c.city AS customer_city, 
COUNT(*) AS total_products_sold FROM sellers s JOIN products p ON s.seller_id = p.seller_id JOIN orders o ON 
p.product_id = o.product_id JOIN customers c ON o.customer_id = c.customer_id WHERE s.city = c.city GROUP BY 
s.seller_id, s.name, s.city, c.city ORDER BY total_products_sold DESC;

--------------------------------********************************************------------------------------------
--------------------------------********************************************------------------------------------
--------------------------------********************************************------------------------------------