Project 1

To the ecommerce database already created, add/extend the following functionalities in your ER diagram if not present yet, create tables, normalize your design and create MySQL database with sample information in it. Remember to optimise your database to search on name, categories, date of product for latest and brand name. Reason how your design is doing this.

Submit your updated ER and tables diagrams. You can use dbdiagram.io for this. Submit all SQL queries used to define your database and insert information in it.

Your ecommerce database should store data for the following. Design your database however you wish, provided that these functionalities should be taken care of-

1. Coupon codes with discount rule and percentage per coupon
2. Order- shipments- invoices
3. User wishlist
4. Replacements catering to different orders- status
5. Reviews from customers for every product
6. Product- name, color, seller, sizes available, quantity available, cost, current discount, place of origin for delivery calculations, isFeatured, popularityIndex, avgratings, totalratings, reviews for ratings, review country, isOnSale, categories, brands, images, questions with answers about the products, description, tags, related products ids
7. Customer- gender, dob, city, pw, name, email, delivery addresses, contacts, current and prev orders, payment info saved
8. Orders- status- cancelled/completed/ordered/packed/shipped/delivered, deliveryexpectation/deliverydate, orderdate
9. Seller- Name, mobile no, password, categories of products provided

 Created table,er diagram,with all the necessary information ,added the query ,normalized,updated the database with latest information which took care of all necessary functionalities
1.
SELECT coupon                        
     , coupon                             
     , COUNT(coupon)                   
     , SUM(subtotal)                        
     , SUM(total_paid)                          
     , AVG(total_paid)                 
     , AVG(subtotal)           
     , (
        SUM(discount_amount) - SUM(total_paid)
       )                                        AS 'Cumulative Loss'

     , CONCAT(ROUND((
        COUNT(coupon_code) / (SELECT COUNT(*) FROM product s)
       ) * 100, 1), '%')                  AS 'Percentage'

FROM     product
WHERE    coupon       IS NOT NULL
GROUP BY coupon
ORDER BY COUNT(coupon) DESC;

2.

Select

s.o as 'Sales Order',

s.ItemCOde as ' Item',

p.Quantity as ' QTY',

p.InvQty as 'QTY ',

s.order as 'Invoice',

FROM ordr o

inner join order o.orderid=p.order id

WHERE o.invoice=500;

3.Customer_Orders : Customer_ID, Order_ID, Order_date , Product_ID, email

Wishlist : Customer_ID, Created_Date , Product_ID

select w.Customer_ID,w.Product_ID from Wishlist w inner join Customer_Orders od on od.Product_ID=w.Prodcuct_ID and od.Customer_ID=w.Customer_ID4. SELECT a.cust_name,a.city, b.ord_no, b.ord_date,b.purch_amt AS "Order Amount" FROM customer a LEFT OUTER JOIN orders b ON a.customer_id=b.customer_id order by b.ord_date;

5.SELECT product_name, product_price FROM product WHERE product_price = (SELECT MIN(pro_price) FROM item_mast);

6.SELECT customer_name,city, FROM customer ORDER BY customer_id;

7. SELECT orderstatus, deliverydate, orderdate,ord_no, ord_date, purch_amt FROM orders WHERE order id=2;
8. SELECT seller Name, seller mobile no, seller password FROM SELLER;
9. SELECT gender, dob, city, pw, name, email, delivery addresses, contacts FROM Customer;

10. SELECT name, color, seller, sizes available, quantity available, cost, current discount FROM PRODUCT;11. SELECT * FROM products WHERE category IN ( SELECT id FROM category UNION SELECT c.id FROM category c JOIN category c2 ON c2.id = c.parent #created the table for customer seller product payment categories order shippment

create table customer( customer name varchar(20) PRIMARY KEY, customer address varchar(20),customer contact int(20),customer id int(20) NOT NULL,customer gender varchar (20) NOT NULL
customer dob varchar(20) NOT NULL
customer city varchar(30) NOT NULL
customer pw varchar(40) NOT NULL
customer email varchar(20) NOT NULL
order id varchar(20) NOT NULL
payment id int (20) NOT NULL);

create table seller ( seller id int(20) PRIMARY KEY,seller name varchar(50) NOT NULL
mobile number int(20) NOT NULL
seller password varchar(20) NOT NULL
category id int(20) NOT NULL);

create table product(product id int(20) PRIMARY KEY NOT NULL,product name varchar(20) NOT NULL,product color varchar(20),seller id int(20) NOT NULL,product size int(20) NOT NULL,quantity varchar(20));

create table payment(payment id int(20) PRIMARY KEY NOT NULL,payment details varchar(20) NOT NULL,payment type Varchar(20));

create table categories(category_id int(20) PRIMARY KEY NOT NULL,shipment id int(20) NOT NULL)

create table order(order id int(20) PRIMARY KEY NOT NULL,shipment id int(20) NOT NULL,order status varchar(20) NOT NULL,order date date NOT NULL)

create table shipment(shipment id int (20) PRIMARY KEY NOT NULL,order id int(20) NOT NULL,shipment date date NOT NULL);

Insert the values into table :dummy tablle

Insert into customer values( ram, marathalli,98567576557757,10,male,30/08/1989,bangalore,abc,[email protected],1,50)

Insert into customer values( jenny, mekodt,8897576557757,11,Female,9/08/1976,bangalore,def,[email protected],2,51);

Insert into seller values ( 2,kish,979867697679,sg@jk,5);

Insert into seller values ( 3,yathi,8897697679,uh@jk,6);