Challenge

Project 1

AAniket Garg8 Dec 2022

-- TASK_01 & TASK_02
-- ECOMMERCES STORE PROJECT 1 : ** Welcome to our Ecommerces database**
CREATE DATABASE ECOMMERCE;

USE ECOMMERCE;

CREATE TABLE Ecomerce_Store(
Admin_id INT PRIMARY KEY,
Customer_id INT UNIQUE KEY NOT NULL,
Seller_id INT UNIQUE KEY NOT NULL
);

DESCRIBE Ecomerce_Store;

INSERT INTO Ecomerce_store VALUES(101,201,301),(102,202,302),(103,203,303),(104,204,304),(105,205,305);

SELECT * FROM Ecomerce_store;

CREATE TABLE ADMIN(
Admin_id INT PRIMARY KEY,
NAME VARCHAR(255) NOT NULL,
PASSWORD VARCHAR(255) UNIQUE NOT NULL,
mobile_no VARCHAR(17) NOT NULL,
city VARCHAR(255) NOT NULL,
FOREIGN KEY(Admin_id) REFERENCES Ecomerce_Store(Admin_id)
);

DESCRIBE ADMIN;

INSERT INTO ADMIN VALUES(101,"Aniket Garg","GFD789","7011101967","New Delhi"),
(102,"Sarthak","WRT458","9685424212","Gurugram"),
(103,"Kaushal ","ABC123","9685454545","Kashmir"),
(104,"Kunal","BCD123","9356424145","Dadar"),
(105,"Kumod","CDE234","9876565232","Thane");

SELECT * FROM ADMIN;

CREATE TABLE Customer(
customer_id INT PRIMARY KEY,
NAME VARCHAR(255) NOT NULL,
pwd VARCHAR(255) NOT NULL,
dob DATE NOT NULL,
gender CHAR(1) NOT NULL,
city VARCHAR(255) NOT NULL,
email VARCHAR(255) DEFAULT "Unassigned",
contact VARCHAR(20) NOT NULL,
delivery_Add VARCHAR(255) NOT NULL,
coupon_id INT REFERENCES Coupon(coupon_id),
FOREIGN KEY(Customer_id) REFERENCES Ecomerce_Store(Customer_id)
);

DESCRIBE Customer;

INSERT INTO customer VALUES(201,"Aniket","ABC456","1997-11-19","M","Sarita Vihar","[email protected]","9958741236","Madanpur Khader,Sarita Vihar ",857456),
(202,"Tiwari","123ABF","2002-11-05","F","Kolkata","[email protected]","8785969632","Asannsol ,Kolkata",789565),
(203,"Kirthi","125GTH","1998-12-18","F","MTB","[email protected]","8574125263","121/240 model town",NULL),
(204,"Amaira","435OPI","2000-12-28","F","Gurugram","[email protected]","7852365445","Jhansi Road",NULL),
(205,"Kamal","123RSD","1999-02-07","M","Noida","[email protected]","9856452355","Skytech sec 76 noida",856235);

SELECT * FROM CUSTOMER;

CREATE TABLE Seller(
seller_id INT PRIMARY KEY,
NAME VARCHAR(255) NOT NULL,
gender CHAR(1) NOT NULL,
PASSWORD VARCHAR(255) UNIQUE NOT NULL,
mobile_no VARCHAR(17) NOT NULL,
city VARCHAR(255) NOT NULL,
categry_of_pro VARCHAR(255) NOT NULL,
FOREIGN KEY(seller_id) REFERENCES Ecomerce_Store(seller_id)
);

DESCRIBE Seller;

INSERT INTO Seller VALUES(301,"Aniket","M","856ERT","7011011256","Sarita Vihar","Electronic"),
(302,"Ram","M","745GHT","7856963625","Gaya","Furniture"),
(303,"Aman","M","741HJY","64832937391","Jaipur","Home & Kitchen"),
(304,"Anand","M","125GHY","9383920211","Nimrana","Fashion"),
(305,"Amod","M","145HJU","9846859373","Daosa","Electronic");

SELECT * FROM Seller;

CREATE TABLE Product(
categery_id INT NOT NULL,
categery_name VARCHAR(255)NOT NULL REFERENCES Seller(categery_name),
pro_id INT PRIMARY KEY,
NAME VARCHAR(255) NOT NULL,
color VARCHAR(255) NOT NULL,
cost INT NOT NULL,
size_available DECIMAL(3,2) NOT NULL,
quantity_available INT NOT NULL,
seller_name VARCHAR(255) NOT NULL,
current_discount INT DEFAULT 0,
Place_origing_delivery VARCHAR(255) NOT NULL,
rating INT NOT NULL,
isonsale CHAR(1) NOT NULL,
brands VARCHAR(255) NOT NULL,
images VARCHAR(255) NOT NULL,
questions VARCHAR(255) NOT NULL
);

DESCRIBE product;

INSERT INTO Product VALUES(11,"Electronic",21,"Samsung mobile","Green",30000,7.1,20,"Ram",1000,"INDIA",4,"Y","sdsa","idsa","questins"),
(11,"Electronic",22,"Oppo mobile","Blue",70000,7.7,230,"Aniket",0,"INDIA",5,"Y","sdsa","idsa","questins"),
(11,"Electronic",23,"I_phone","Green",50000,6.1,2330,"Amod",2000,"INDIA",4,"Y","sdsa","idsa","questins"),
(11,"Electronic",24,"Redmi mobile","voilet",40000,5.2,300,"Amod",3000,"INDIA",4,"Y","sdsa","idsa","questins"),
(11,"Electronic",25,"Boss mobile","Red",20000,7.2,10,"Aniket",1000,"INDIA",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",26,"Table","Orange",3000,7.1,23,"Ram",700,"America",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",27,"Chair","Orange",2000,8.1,33,"Anand",800,"America",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",28,"Bench","Orange",2000,6.8,44,"Aman",900,"America",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",29,"Stul","Orange",700,5.1,55,"Aman",600,"America",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",30,"sofa","Orange",10000,3.6,66,"Amod",1000,"INDIA",4,"Y","sdsa","idsa","questins"),
(13,"cloths",31,"Shirt","Blue",450,4.2,77,"Aniket",50,"China",4,"Y","sdsa","idsa","questins"),
(13,"cloths",32," Normal Phant","Purple",300,5.1,88,"Anand",50,"China",4,"Y","sdsa","idsa","questins"),
(13,"cloths",33,"Track phant","Brown",350,5.1,99,"Aman",20,"China",4,"Y","sdsa","idsa","questins"),
(13,"cloths",34,"T_shirt","Brown",250,5.1,100,"Amod",80,"INDIA",4,"Y","sdsa","idsa","questins"),
(13,"cloths",35,"Hoodie","Yellow",520,6.1,120,"Amod",30,"INDIA",4,"Y","sdsa","idsa","questins");

SELECT * FROM Product;

CREATE TABLE orders(
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
pro_id INT NOT NULL,
order_cost INT NOT NULL,
order_status VARCHAR(200) DEFAULT "ORDERED",
order_date DATE NOT NULL,
delivery_date DATE NOT NULL,
how_many_product INT NOT NULL,
FOREIGN KEY (customer_id)REFERENCES Customer(customer_id),
FOREIGN KEY(pro_id) REFERENCES Product(pro_id)
);

DESCRIBE orders;

INSERT INTO orders VALUES(10001,204,21,30000,"cancelled","2022-11-23","2022-11-24",1),
(10002,204,22,70000,"Delivered","2022-11-23","2022-11-24",1),
(10003,204,22,70000,"Delivered","2022-11-23","2022-11-24",1),
(10004,201,26,6000,"ordered","2022-11-23","2022-10-26",1),
(10005,202,35,490,"Delivered","2022-11-23","2022-11-25",1),
(10006,203,35,490,"shiped","2022-11-23","2022-11-24",1);

SELECT * FROM orders;

CREATE TABLE Coupon(
coupon_id INT PRIMARY KEY,
percentage_discount INT NOT NULL
);

DESCRIBE Coupon;

INSERT INTO Coupon VALUES(857456,20),(789565,38),(856235,50);

SELECT * FROM Coupon;

CREATE TABLE reviewes(
rev_id INT PRIMARY KEY,
pro_id INT NOT NULL,
customer_id INT NOT NULL REFERENCES Customer(customer_id),
rating INT NOT NULL,
average_rating INT NOT NULL,
total_rating INT NOT NULL,
review_country VARCHAR(255) NOT NULL,
FOREIGN KEY(pro_id) REFERENCES Product(pro_id)
);

DESCRIBE REVIEWES;

INSERT INTO reviewes VALUES(2012,22,202,6,12,2000,"Jaipur");

SELECT * FROM reviewes;

CREATE TABLE Wishlist(
wish_id INT PRIMARY KEY,
productname VARCHAR(255),
product_price INT NOT NULL,
pro_id INT NOT NULL REFERENCES product(pro_id),
customer_id INT NOT NULL REFERENCES customer(customer_id)
);

DESCRIBE Wishlist;

INSERT INTO wishlist VALUES(7001,"Samsung mobile",30000,21,202);

SELECT * FROM wishlist;

CREATE TABLE replacement (
rep_id INT PRIMARY KEY,
order_id INT NOT NULL,
productname VARCHAR(255) NOT NULL,
order_status VARCHAR(255) NOT NULL,
customer_id INT NOT NULL REFERENCES customer(customer_id),
FOREIGN KEY(order_id) REFERENCES orders(order_id)
);

DESCRIBE replacement;

INSERT INTO replacement VALUES(2001,10001,"sumsung mobile","returned",204);

SELECT * FROM replacement;

CREATE TABLE shipmentinvoices(
invoice_id INT PRIMARY KEY,
cust_id INT NOT NULL REFERENCES customer1(cust_id),
pro_id INT NOT NULL REFERENCES product(pro_id),
order_id INT NOT NULL,
productname VARCHAR(255) NOT NULL,
paymentway VARCHAR(255) DEFAULT "CASH",
FOREIGN KEY(order_id) REFERENCES orders(order_id)
);

DESCRIBE shipmentinvoices;

INSERT INTO shipmentinvoices VALUES(790012,202,35,10005,"Hoodie","Gpay");

SELECT * FROM shipmentinvoices;

-- TASK__03
-- QUESTION 01
SELECT * FROM Customer ORDER BY city;

-- QUESTION 02
SELECT * FROM product;
ALTER TABLE product RENAME COLUMN categery_id TO prod_categery_id;
ALTER TABLE product RENAME COLUMN categery_name TO prod_categery_name;
ALTER TABLE product RENAME COLUMN pro_id TO prod_id;
ALTER TABLE product RENAME COLUMN NAME TO prod_name;
ALTER TABLE product RENAME COLUMN color TO prod_color;
ALTER TABLE product RENAME COLUMN cost TO prod_cost;
ALTER TABLE product RENAME COLUMN size_available TO prod_size_available;
ALTER TABLE product RENAME COLUMN quantity_available TO prod_quantity_available;
ALTER TABLE product RENAME COLUMN seller_name TO prod_seller_name;
ALTER TABLE product RENAME COLUMN current_discount TO prod_current_discount;
ALTER TABLE product RENAME COLUMN Place_origing_delivery TO prod_Place_origing_delivery;
ALTER TABLE product RENAME COLUMN rating TO prod_rating;
ALTER TABLE product RENAME COLUMN isonsale TO prod_isonsale;
ALTER TABLE product RENAME COLUMN brands TO prod_brands;
ALTER TABLE product RENAME COLUMN images TO prod_images;
ALTER TABLE product RENAME COLUMN questions TO prod_questions;
SELECT * FROM product;

-- QUETION 03

SELECT C.NAME,C.customer_id,COUNT(O.Order_ID)TOTAL_ORDERS FROM customer C INNER JOIN ORDERS O ON
C.customer_id=O.customer_id GROUP BY C.customer_id HAVING COUNT(O.Order_ID)>=1;

-- QUETION 04

SELECT C.NAME,C.customer_id,COUNT(O.Order_ID)TOTAL_ORDERS FROM customer C INNER JOIN ORDERS O ON
C.customer_id=O.customer_id GROUP BY C.CUSTOMER_ID HAVING COUNT(O.ORDER_ID)>1 AND C.name LIKE "A%A";

-- QUESTION 05
SELECT * FROM orders;
SELECT * FROM customer;
SELECT C.NAME,C.customer_id,O.order_status,SUM(order_cost) "Purchesed amount" FROM Orders O LEFT JOIN Customer C
ON C.customer_id=O.customer_id WHERE O.order_status="Delivered" AND order_cost>10000
GROUP BY C.CUSTOMER_ID HAVING COUNT(O.ORDER_ID)>1;

-- QUESTION 06

DELIMITER $$
CREATE PROCEDURE Create_All_Tables_and_puting_insertion()
BEGIN

CREATE TABLE Ecomerce_Store(
Admin_id INT PRIMARY KEY,
Customer_id INT UNIQUE KEY NOT NULL,
Seller_id INT UNIQUE KEY NOT NULL
);

DESCRIBE Ecomerce_Store;

INSERT INTO Ecomerce_store VALUES(101,201,301),(102,202,302),(103,203,303),(104,204,304),(105,205,305);

SELECT * FROM Ecomerce_store;

DESCRIBE ADMIN;

SELECT * FROM ADMIN;

DESCRIBE Customer;

SELECT * FROM CUSTOMER;

DESCRIBE Seller;

SELECT * FROM Seller;

DESCRIBE product;

SELECT * FROM Product;

DESCRIBE orders;

SELECT * FROM orders;

CREATE TABLE Coupon(
coupon_id INT PRIMARY KEY,
percentage_discount INT NOT NULL
);

DESCRIBE Coupon;

INSERT INTO Coupon VALUES(857456,20),(789565,38),(856235,50);

SELECT * FROM Coupon;

DESCRIBE REVIEWES;

INSERT INTO reviewes VALUES(2012,22,202,6,12,2000,"Jaipur");

SELECT * FROM reviewes;

DESCRIBE Wishlist;

INSERT INTO wishlist VALUES(7001,"Samsung mobile",30000,21,202);

SELECT * FROM wishlist;

DESCRIBE replacement;

INSERT INTO replacement VALUES(2001,10001,"sumsung mobile","returned",204);

SELECT * FROM replacement;

DESCRIBE shipmentinvoices;

INSERT INTO shipmentinvoices VALUES(790012,202,35,10005,"Hoodie","Gpay");

ALTER TABLE product RENAME COLUMN categery_id TO prod_categery_id;
ALTER TABLE product RENAME COLUMN categery_name TO prod_categery_name;
ALTER TABLE product RENAME COLUMN pro_id TO prod_id;
ALTER TABLE product RENAME COLUMN NAME TO prod_name;
ALTER TABLE product RENAME COLUMN color TO prod_color;
ALTER TABLE product RENAME COLUMN cost TO prod_cost;
ALTER TABLE product RENAME COLUMN size_available TO prod_size_available;
ALTER TABLE product RENAME COLUMN quantity_available TO prod_quantity_available;
ALTER TABLE product RENAME COLUMN seller_name TO prod_seller_name;
ALTER TABLE product RENAME COLUMN current_discount TO prod_current_discount;
ALTER TABLE product RENAME COLUMN Place_origing_delivery TO prod_Place_origing_delivery;
ALTER TABLE product RENAME COLUMN rating TO prod_rating;
ALTER TABLE product RENAME COLUMN isonsale TO prod_isonsale;
ALTER TABLE product RENAME COLUMN brands TO prod_brands;
ALTER TABLE product RENAME COLUMN images TO prod_images;
ALTER TABLE product RENAME COLUMN questions TO prod_questions;
END $$
DELIMITER ;

Project 1

Ship work like this
in your own course.

Talk to a career counsellor

Ship work like thisin your own course.

Talk to a career counsellor

Ship work like this
in your own course.