Project 1
-- TASK_01 & TASK_02
-- ECOMMERCES STORE PROJECT_01 : ** Welcome to our E-commerce database**
CREATE DATABASE ECOMMERCES;
USE ECOMMERCES;
CREATE TABLE Ecomerces_Store(
Admin_id INT PRIMARY KEY,
Customer_id INT UNIQUE KEY NOT NULL,
Seller_id INT UNIQUE KEY NOT NULL
);
DESCRIBE Ecomerces_Store;
INSERT INTO Ecomerces_store VALUES(101,201,301),(102,202,302),(103,203,303),(104,204,304),(105,205,305);
SELECT * FROM Ecomerces_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 Ecomerces_Store(Admin_id)
);
DESCRIBE Admin;
INSERT INTO Admin VALUES(101,"KISHORE R","20k22","93932892892","GPD"),
(001,"MAPALA","20r22","32424224242","CHENNAI"),
(002,"GOINTHAN","20i22","32448983840","SALAM"),
(003,"MACHA ","20s22","657589738932","KANJIPURAM"),
(004,"MAMA","20h22","58594352892","MADURAI");
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 Ecomerces_Store(Customer_id)
);
DESCRIBE Customer;
INSERT INTO customer VALUES(201,"SAIRAM","20K22","1999-08-30","M","THIRUVALUR","[email protected]","9790023123","23 METTUPALAYAM CHENNAI ",123456),
(202,"MAMA","20R22","2000-01-01","F","CHENNAI","[email protected]","00000000005","23 METTUPALAYAM STREET CHENNAI",123457),
(203,"MACHA","20S22","1999-07-12","M","MADURAI","[email protected]","000000006","23 METTUPALAYAM STREET ADAYAR",NULL),
(204,"DANI DANIALS","20I22","2000-12-18","F","CHENNAI","DANNI [email protected]","0000000007","301/10 CRORE STREET CHENNAI",NULL),
(205,"GOINTHAN","20H22","1998-03-01","M","USA","[email protected]","0000000008","23 METTUPALAYAM STREET TADA",123458);
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 Ecomerces_Store(seller_id)
);
DESCRIBE Seller;
INSERT INTO Seller VALUES(301,"SAIRAM","M","20K22","93932892892","Thiruvalur","Electronic"),
(001,"KUMAR","M","20R22","000000000","Chennai","Furniture"),
(002,"TAMIL","M","20I22","0000000000","SALEM","cloths"),
(003,"NAVVEN","M","20S22","0000000000","THURACHY","Electronic"),
(004,"RANGA","M","20H22","0000000000","KANCHIPURAM","Furniture");
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,"KISHORE",1000,"INDIA",4,"Y","sdsa","idsa","questins"),
(11,"Electronic",22,"Oppo mobile","Blue",70000,7.7,230,"KISHORE",0,"INDIA",5,"Y","sdsa","idsa","questins"),
(11,"Electronic",23,"I_phone","Green",50000,6.1,2330,"Aadarsh",2000,"INDIA",4,"Y","sdsa","idsa","questins"),
(11,"Electronic",24,"Redmi mobile","voilet",40000,5.2,300,"Aadarsh",3000,"INDIA",4,"Y","sdsa","idsa","questins"),
(11,"Electronic",25,"Boss mobile","Red",20000,7.2,10,"KISHORE",1000,"INDIA",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",26,"Table","Orange",3000,7.1,23,"Aaditya",700,"America",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",27,"Chair","Orange",2000,8.1,33,"Aaditya",800,"America",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",28,"Bench","Orange",2000,6.8,44,"Amar",900,"America",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",29,"Stul","Orange",700,5.1,55,"Amar",600,"America",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",30,"sofa","Orange",10000,3.6,66,"Amar",1000,"INDIA",4,"Y","sdsa","idsa","questins"),
(13,"cloths",31,"Shirt","Blue",450,4.2,77,"Ashock",50,"China",4,"Y","sdsa","idsa","questins"),
(13,"cloths",32," Normal Phant","Purple",300,5.1,88,"Ashock",50,"China",4,"Y","sdsa","idsa","questins"),
(13,"cloths",33,"Track phant","Brown",350,5.1,99,"Ashock",20,"China",4,"Y","sdsa","idsa","questins"),
(13,"cloths",34,"T_shirt","Brown",250,5.1,100,"Ashock",80,"INDIA",4,"Y","sdsa","idsa","questins"),
(13,"cloths",35,"Hoodie","Yellow",520,6.1,120,"Ashock",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(123456,20),(123457,38),(123458,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,"Kanyakumary");
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 Ecomerces_Store(
Admin_id INT PRIMARY KEY,
Customer_id INT UNIQUE KEY NOT NULL,
Seller_id INT UNIQUE KEY NOT NULL
);
INSERT INTO Ecomerces_store VALUES(101,201,301),(102,202,302),(103,203,303),(104,204,304),(105,205,305);
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 Ecomerces_Store(Admin_id)
);
INSERT INTO Admin VALUES(101,"SAIRAM R","20k22","93932892892","GPD"),
(102,"KARTHICK","20r22","32424224242","CHENNAI"),
(103,"KUMAR ","20i22","32448983840","THIRUVALUR"),
(104,"KALAI","20s22","657589738932","KANJIPURAM"),
(105,"VINOTH","20h22","58594352892","THANJAUVR");
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 Ecomerces_Store(Customer_id)
);
INSERT INTO customer VALUES(201,"SAIRAM","20K22","1999-08-30","M","THIRUVALUR","[email protected]","9790023123","23 METTUPALAYAM CHENNAI ",123456),
(202,"MAMA","20R22","2000-01-01","F","CHENNAI","[email protected]","00000000005","23 METTUPALAYAM STREET CHENNAI",123457),
(203,"MACHA","20S22","1999-07-12","M","MADURAI","[email protected]","000000006","23 METTUPALAYAM STREET ADAYAR",NULL),
(204,"DANI DANIALS","20I22","2000-12-18","F","CHENNAI","DANNI [email protected]","0000000007","301/10 CRORE STREET CHENNAI",NULL),
(205,"GOINTHAN","20H22","1998-03-01","M","USA","[email protected]","0000000008","23 METTUPALAYAM STREET TADA",123458);
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 Ecomerces_Store(seller_id)
);
INSERT INTO Seller VALUES(301,"KISHORE","M","20K22","93932892892","Thiruvalur","Electronic"),
(302,"MAMA","M","20R22","9232112232","CHENNAI","Furniture"),
(303,"AshoK","M","20I22","64832937391","ADAYAR","cloths"),
(304,"MAPALA","M","20S22","9383920211","ASHOK NAGAR","Electronic"),
(305,"KT","M","20H22","9846859373","DELHI","Furniture");
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
);
INSERT INTO Product VALUES(11,"Electronic",21,"Samsung mobile","Green",30000,7.1,20,"SAIRAM",1000,"INDIA",4,"Y","sdsa","idsa","questins"),
(11,"Electronic",22,"Oppo mobile","Blue",70000,7.7,230,"SAIRAM",0,"INDIA",5,"Y","sdsa","idsa","questins"),
(11,"Electronic",23,"I_phone","Green",50000,6.1,2330,"MACHA",2000,"INDIA",4,"Y","sdsa","idsa","questins"),
(11,"Electronic",24,"Redmi mobile","voilet",40000,5.2,300,"Aadarsh",3000,"INDIA",4,"Y","sdsa","idsa","questins"),
(11,"Electronic",25,"Boss mobile","Red",20000,7.2,10,"SAIRAM",1000,"INDIA",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",26,"Table","Orange",3000,7.1,23,"MAMA",700,"America",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",27,"Chair","Orange",2000,8.1,33,"ASHOK",800,"America",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",28,"Bench","Orange",2000,6.8,44,"KT",900,"America",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",29,"Stul","Orange",700,5.1,55,"GOINTHAN",600,"America",4,"Y","sdsa","idsa","questins"),
(12,"Furniture",30,"sofa","Orange",10000,3.6,66,"MAMA",1000,"INDIA",4,"Y","sdsa","idsa","questins"),
(13,"cloths",31,"Shirt","Blue",450,4.2,77,"Ashok",50,"China",4,"Y","sdsa","idsa","questins"),
(13,"cloths",32," Normal Phant","Purple",300,5.1,88,"Ashock",50,"China",4,"Y","sdsa","idsa","questins"),
(13,"cloths",33,"Track phant","Brown",350,5.1,99,"GOINTHAN",20,"China",4,"Y","sdsa","idsa","questins"),
(13,"cloths",34,"T_shirt","Brown",250,5.1,100,"MAMA",80,"INDIA",4,"Y","sdsa","idsa","questins"),
(13,"cloths",35,"Hoodie","Yellow",520,6.1,120,"MACHA",30,"INDIA",4,"Y","sdsa","idsa","questins");
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)
);
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,"packed","2022-11-23","2022-11-25",1),
(10006,203,35,490,"shiped","2022-11-23","2022-11-24",1),
(10007,202,22,70000,"Delivered","2022-11-22","2022-11-23",1);
INSERT INTO orders VALUES (10012,201,22,70000,"Delivered","2022-11-22","2022-11-23",1);
CREATE TABLE Coupon(
coupon_id INT PRIMARY KEY,
percentage_discount INT NOT NULL
);
INSERT INTO Coupon VALUES(123456,20),(123457,38),(123458,50);
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)
);
INSERT INTO reviewes VALUES(2012,22,202,6,12,2000,"SRI LANKA");
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)
);
INSERT INTO wishlist VALUES(7001,"Samsung mobile",30000,21,202);
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)
);
INSERT INTO replacement VALUES(2001,10001,"sumsung mobile","returned",204);
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)
);
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 ;