Challenge

Project 1

SSandhya Rani2 Dec 2022

TASK 1 & TASK 2

E-COMMERCE STORE PROJECT 1:

CREATE DATABASE e-commerce;

USE eCommerce;

CREATE TABLE admin(
admin_id INT,
name CHAR(50) NOT NULL,
password VARCHAR(255) UNIQUE NOT NULL,
phone CHAR(20) NOT NULL,
city CHAR(50) NOT NULL
);

DESC admin;

INSERT INTO admin VALUES(11, 'Sandhya Rani', 'Sandhya@10', '7011536629', 'Delhi'),
(12, 'Raj Prajapati', 'Raj@12','8091239293', 'Uttrakhand');

SELECT* FROM admin;

CREATE TABLE customer(
cust_id int PRIMARY KEY ,
cust_name CHAR(50) NOT NULL,
cust_password VARCHAR(255)NOT NULL,
cust_gender CHAR(1) NOT NULL,
cust_dob DATETIME NOT NULL,
cust_email CHAR(50) NOT NULL,
cust_city CHAR(50) NOT NULL,
cust_Del_address VARCHAR(255) NOT NULL,
cust_phone char(20) NOT NULL,
current_order VARCHAR (255) NOT NULL,
prev_orders VARCHAR(255) NOT NULL,
cart_ID INT DEFAULT NULL,
paymentInfoSaved CHAR(30),
coupon_id INT FOREIGN KEY REFERENCES coupon(coupon_id),
FOREIGN KEY (cart_id) REFERENCES cart(cart_id)
);

DESC customer;

INSERT INTO customer VALUES
('1001', 'Janvi' , 'Jan11' , 'F' , '2003-11-14 15:23:33' , '[email protected]' , 'Delhi' , 'New Delhi' , '9011239293' , 'Dress' , 'Frock' , 105 , 'yes' , 12366),

('1002' , 'Abhi' , 'Abhi12' , 'M' , '1995-11-02 15:19:36' , '[email protected]' , 'Bangalore' , 'Bangalore' , '912349645' , 'Headphone' , 'Shirt' , 101 , 'yes' , 345678),

('1003' , 'Seeta' , 'Seeta13' , 'F' , '2022-11-05 08:11:53' , '[email protected]' , 'Maharashtra' , 'Pune' , '9816372672' , 'Shirt', 'Cap' , 103 , 'yes' , 984598),

('1004' , 'Varun' , 'Varu14' , 'M' , '2002-11-21 15:19:36' , 'varu@[email protected]' , 'Chennai' , 'Chennai' , '17396327783' , 'Cap', 'speakers' , 104 , 'yes' , 456678),

('1005' , 'Radha' , 'Ra15' , 'F' , '2000-11-12 12:41:53' , '[email protected]' , 'Delhi' , 'New Delhi' , '8735637725' , 'Macbook' , 'Laptop' , 101 , 'yes' , 5478009);

SELECT * FROM customer;

CREATE TABLE cart(
cart_id INT PRIMARY KEY ,
Item_quantity INT(11),
Total_cost INT(11)
);

DESC cart;

INSERT INTO cart VALUES
(101, 20, 3000),
(102, 10, 1200),
(103, 2, 200),
(104, 5, 2000),
(105, 10, 70000);

SELECT * FROM cart;

CREATE TABLE cart_item(
cart_id INT ,
product_id VARCHAR(100),
dateAdded DATE,
purchase VARCHAR(255),
Item_quantity INT(11),
FOREIGN KEY (cart_id) REFERENCES cart(cart_ID),
FOREIGN KEY (product_id) REFERENCES product(product_id)
);

DESC cart_item;

INSERT INTO cart_item VALUES
(101 , 'P_2' , '2022-11-07' , 'No' , 7),
(102 , 'P_1' , '2022-11-22' , 'Yes' , 3),
(103 , 'P_1' , '2022-10-19' , 'Yes' , 5),
(104 , 'P_3' , '2022-11-03' , 'Yes' , 6),
(105 , 'P_1' , '2022-11-01' , 'No' , 3);

SELECT * FROM cart_item;

CREATE TABLE category(
category_id INT,
cat_name VARCHAR(255),
product_id VARCHAR(10),
subCategory VARCHAR(100),
FOREIGN KEY (product_id) REFERENCES product(product_id)
);

DESC category;

INSERT INTO category VALUES
('201', 'Accessories', 'P_1', 'Headphones'),
('202', 'Laptops', 'P_1', 'Apple'),
('203', 'Accessories', 'P_3', 'Charger'),
('204', 'Phones', 'P_3', 'Vivo'),
(205', 'Fashion', 'P_2', 'Mens');

SELECT* FROM category;

CREATE TABLE seller(
seller_id INT PRIMARY KEY,
seller_name CHAR(50),
mobile_no INT,
seller_password VARCHAR(50),
category_of_products VARCHAR(255)
);

DESC seller;

INSERT INTO seller VALUES
('717' , 'ABC' , 59545554 , 'ABC7', 'Accessories'),
('727' , 'XYZ' , 34566565 , 'XYZ7' , 'Laptop'),
('737' , 'JKL', 83595847, 'JKL7' , 'fashion'),
('747' , 'EFG', 8549505, 'EFG7', 'Accessories'),
('757', 'PQR', 84564865, 'PQR7', 'Phones');

SELECT * FROM seller;

CREATE TABLE product(
product_id VARCHAR(10) PRIMARY KEY,
name VARCHAR(255),
color VARCHAR(255),
seller_id INT,
sizes CHAR(10),
quantity INT,
cost DECIMAL(10,2),
Discount DECIMAL(10,2),
delivery_place VARCHAR(255),
Net_cost INT(10),
isFeatured VARCHAR(255),
rating INT,
reviewCountry INT,
isOnSale VARCHAR(255),
category_id INT,
brand VARCHAR(100),
description VARCHAR(255),
tags VARCHAR(100),
related_Product VARCHAR(255),
cat_name VARCHAR(255),
seller_name CHAR(50),
questions VARCHAR(255),
image TEXT,
FOREIGN KEY (seller_id) REFERENCES seller(seller_id)
);

DESCRIBE product;

INSERT INTO product VALUES
('P_1' , 'Headphones' , 'red-black' , 717 , 'M' , 1 , 1999.00 , 300.00 , 'New Delhi' ,1699.00, 'yes' , 5 , 4 , 'yes' , 201 , 'Noise' , 'sleek design' , 'speakers' , 'Bluetooth speakers' , 'Accessories' , 'ABC' , 'questions1' , 'image1' ),

('P_2' , 'Goggles' , 'blue' , 737 , 'S' , 2 , 1499.00 , 199.00 , 'Chennai' ,1299.00, 'yes' , 5 , 9 , 'yes' , 205 , 'Rayban' , 'smooth with ears' , 'fashion' , 'Sun glasses' , 'Mens' , 'PQR' , 'questions2' , 'image2' ),

('P_3' , 'Oppo' , 'blue-black' , 747 , '4cm' , 1 , 14999.00 , 1199.00 , 'Delhi' ,13800.00, 'yes' , 5 , 6 , 'yes' , 204 , 'Oppo reno 8' , 'gorilla glass' , 'Phones' , 'samsung mobile' , 'Accessories' , 'PQR' , 'questions3' , 'image3' ),

('P_4' , 'Laptop' , 'black' , 727 , '24cm' , 1 , 28000.00 , 2000.00 , 'Pune' ,26000.00, 'yes' , 5 , 7 , 'yes' , 202 , 'Lenovo 7' , 'Amoled display' , 'Laptop' ,'Macbook' , 'Laptop' , 'XYZ' , 'questions4' , 'image4' ),

('P_5', 'Shirt', 'Yellow', '757', 'XXL' , 2 , 3000.00 , 200.00 , 'Banglore', 2800.00 , 'yes' , 4 , 8 , 'yes' , 203 , 'being human' , 'T_shirt with cap' , 'Fashion' , 'Trouser' , 'being humans jeans' , 'JKL' ,'questions ','image ');

SELECT* FROM product;

CREATE TABLE orders(
order_id VARCHAR(255) PRIMARY KEY,
status VARCHAR(100),
order_date DATE,
Dilevery_date DATE,
cust_id INT,
FOREIGN KEY (cust_id) REFERENCES customer(cust_id)
);

DESC orders;

INSERT INTO orders VALUES
('0tufhjnv' , 'shipping' , '2022-11-05' , '2022-11-08' , '1001'),
('09hjwe3' , 'delivered' , '2022-11-01' , '2022-11-05' , '1003'),
('1567o3d', 'delivered', '2022-11-02', '2022-11-04', '1002'),
('klr8y92' , 'dileverd soon' , '2022-11-04' , '2022-11-06' , '1003'),
('ormh26' , 'shiiping' , '2022-11-02' , '2022-11-07' , '1001');

SELECT * FROM orders;

CREATE TABLE payment(
pay_id INT PRIMARY KEY,
pay_amount INT,
pay_date DATE,
pay_type CHAR(50),
pay_status CHAR(60),
cust_id INT
);

DESC payment;

INSERT INTO payment VALUES (1 , 2000 , '2019/08/11', 'UPI' , 'successful' , '1001'),
(2 , 40000 , '2019/04/15' , 'credit card' , 'successful' , '1002'),
(3 , 1000 , '2020/06/04' , 'GPAY' , 'successful' , '1001'),
(4 , 900 , '2021/06/25' , 'Debit card' , 'successful' , '1004'),
(5 , 2000 , '2021/08/23' , 'Phone pay' , 'successful' , '1003');

SELECT * FROM payment;

CREATE TABLE coupon(
coupon_id INT PRIMARY KEY,
coupon_code VARCHAR(255),
coupon_discount INT NOT NULL
);

DESC coupon;

INSERT INTO Coupon VALUES(123451, 'BLACKFRIDAY' , 18),
(123452, 'SALE20' , 20),
(123453, 'DISCOUNT50' , 50);

SELECT * FROM coupon;

CREATE TABLE reviews(
rev_id INT PRIMARY KEY,
product_id VARCHAR(100) NOT NULL,
cust_id INT NOT NULL REFERENCES customer(cust_id),
rating INT NOT NULL,
review_country VARCHAR(255) NOT NULL,
FOREIGN KEY(product_id) REFERENCES Product(product_id)

);

DESC reviews;

INSERT INTO reviews VALUES(2022 , 'P_3' , '1002', 5 , 'India');

SELECT* FROM reviews;

CREATE TABLE wishlist(
wish_id INT PRIMARY KEY,
productname VARCHAR(255),
product_price INT NOT NULL,
product_id VARCHAR(100) NOT NULL REFERENCES product(product_id),
cust_id INT NOT NULL REFERENCES customer(cust_id)
);

DESC wishlist;

INSERT INTO wishlist VALUES(701 , 'Samsung mobile' , 30000 ,'P_3' , 1005);

SELECT * FROM Wishlist;

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

DESC replacement;

INSERT INTO replacement VALUES(2001, '09hjwe3' ,'ASUS mobile' , 'returned' , '1001');

SELECT * FROM replacement;

CREATE TABLE shipmentInvoices(
invoice_id INT PRIMARY KEY,
cust_id INT NOT NULL REFERENCES customer(cust_id),
product_id VARCHAR(100) NOT NULL REFERENCES product(pro_id),
order_id VARCHAR(255) NOT NULL,
productname VARCHAR(255) NOT NULL,
payment_way VARCHAR(255) DEFAULT "CASH",
FOREIGN KEY(order_id) REFERENCES orders(order_id)
);

DESC shipmentInvoices;

INSERT INTO shipmentInvoices VALUES(6576575 , 1003 , 'P_4' , '0klr892' , 'Trouser' , 'Gpay');

SELECT* FROM shipmentInvoices;

SHOW TABLES;

TASK 3

Answer 1)

SELECT * FROM Customer ORDER BY city;

Answer 2)

ALTER TABLE product RENAME COLUMN product_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 seller_id TO prod_seller_id;

ALTER TABLE product RENAME COLUMN size TO prod_size;

ALTER TABLE product RENAME COLUMN quantity TO prod_quantity;

ALTER TABLE product RENAME COLUMN cost TO prod_cost;

ALTER TABLE product RENAME COLUMN discount TO prod_discount;

ALTER TABLE product RENAME COLUMN delivery_place TO prod_delivery_place;

ALTER TABLE product RENAME COLUMN net_cost TO prod_net_cost;

ALTER TABLE product RENAME COLUMN isFeatured TO prod_featured;

ALTER TABLE product RENAME COLUMN rating TO prod_rating;

ALTER TABLE product RENAME COLUMN isonsale TO prod_isonsale;

ALTER TABLE product RENAME COLUMN brand TO prod_brand;

ALTER TABLE product RENAME COLUMN description TO prod_description;

ALTER TABLE product RENAME COLUMN tags TO prod_tags;

ALTER TABLE product RENAME COLUMN image TO prod_images;

ALTER TABLE product RENAME COLUMN questions TO prod_questions;

Answer 3)

SELECT C.cust_name , C.cust_id , COUNT(O.order_id) AS TOTAL_ORDERS FROM customer C INNER JOIN orders O ON
C.cust_id = O.cust_id GROUP BY C.cust_id HAVING COUNT(O.order_id)>=1;

Answer 4)

SELECT C.cust_name,C.cust_id,COUNT(O.order_id) AS TOTAL_ORDERS FROM customer C INNER JOIN orders O ON
C.cust_id = O.cust_id GROUP BY C.cust_id HAVING COUNT(O.order_id)>1 AND C.cust_name LIKE "a%a";

Answer 5)

SELECT C.cust_name ,C.cust_id, SUM(prod_cost) AS 'Purchese amount' FROM orders O LEFT JOIN customer C
ON C.cust_id=O.cust_id WHERE O.status="Delivered" AND prod_cost >10000
AND C.cust_id HAVING COUNT(O.order_id)>1;

Answer 6)

DELIMITER $$
CREATE PROCEDURE `ecommerce`.`Create_Alter_Insert_All`()
BEGIN