Project 1
USE midline_estoer;
-- Members Table
CREATE TABLE Mamber(
member_id INT NOT NULL,
member_type CHAR(50),
month CHAR(10),
prise INT
);
DROP TABLE Mamber;
INSERT INTO Mamber VALUES
(100001,'Base','1 Month',350),
(100002,'Advance','6 Month',999),
(100003,'Premium','1 Year',1999);
SELECT *FROM Mamber;
-- user table
CREATE TABLE user(
user_id int NOT NULL,
username CHAR(50),
password CHAR(50),
member_id INT REFERENCES member(member_id)
);
DROP TABLE user;
INSERT INTO user VALUES
(101,'gaurapant',34870,100001),
(102,'mukesh',35920,100002),
(103,'Nikhil',36594,100003),
(104,'Soniya',37268,100002),
(105,'Nikita',37942,100003),
(106,'Rohini',38616,100002),
(107,'Sohan',34870, 100002),
(108,'Mukesh',35920,100003),
(109,'Nikhil',36594,100001),
(110,'Puja' ,37268,100002),
(111,'Awek',37942,100001),
(112,'Alok',38616,100001);
SELECT *FROM user;
-- member ship assign
CREATE TABLE member_data As SELECT m.`member_type`, m.`month`,m.`prise`,
u.`username`,u.`password` FROM Mamber m
INNER JOIN user u ON m.`member_id` = u.`member_id`;
SELECT *FROM member_data;
-- customer_inforcation
CREATE TABLE customer(
cus_id INT NOT NULL,
cus_fname CHAR(50),
cus_lname CHAR(50),
cus_dob DATE,
gender CHAR(5),
cus_email CHAR(50),
cus_number LONG,
cus_address VARCHAR(255),
c_city CHAR(50),
c_state CHAR(50),
user_id INT REFERENCES user(user_id),
cart_data int REFERENCES cart(cart_id),
payment_id long,
Product_Type INT,
PRIMARY KEY(cus_id)
);
DESCRIBE customer;
INSERT INTO customer VALUES
(1001,'Gaurav','Pant','1995-06-20','m','[email protected]',9586475014,'N.H-8, Near Samarkha Crossing','Anand','Gujarat',101,1000011,100001,2000011),
(1002,'Mukesh','Kumar','1998-05-05','m','[email protected]',9685475231,'Stock broker', 'Mumbai', 'Maharashtra',102,1000012,100002,2000012),
(1003,'Nikhil','Kumar','2000-03-09','m','[email protected]',7450315786,'Asa Ram Complex Chottu Ram','Chowk', 'Haryana',103,1000013,100003,2000013),
(1004,'Soniya','Shama','2002-01-12','f','[email protected]',3647856214,'vill-anghuthi post-bichpuri','agra', 'Uttar pradesh',104,1000014,100004,2000014),
(1005,'Nikita','Bhisht','2003-11-17','f','[email protected]',8567452110,'At.Post-Waranghushi Waranghushi','Ahmadnagar','Maharashtra',105,1000015,100005,2000015),
(1006,'Rohini','Joshi','2005-09-21','f','[email protected]',7540315429,'Village-Bari Post Office-Bari Landmark-Karshubai Sikhar Temple', 'Ahmadnagar', 'Maharastrah',106,1000016,100006,2000016);
DROP TABLE customer;
SELECT *FROM customer;
CREATE TABLE customerlogin AS SELECT c.`cus_fname`, c.`cus_lname`,c.`cus_email`,
m.`username`,m.`password` FROM customer c
INNER JOIN user m ON c.`user_id` = m.`user_id`;
DROP TABLE loginpotal;
DESCRIBE customerlogin;
SELECT * FROM customerlogin;
-- seller_informaction
CREATE TABLE seller(
sel_id INT NOT NULL,
sel_fname CHAR(50),
sel_lname CHAR(50),
dob DATE,
gender CHAR(5),
email_id CHAR(50),
Phone_num long,
sel_address VARCHAR(255),
user_id INT REFERENCES user(user_id),
PRIMARY KEY(sel_id)
);
-- DROP TABLE seller;
INSERT INTO seller VALUES
(2001,'Sohan','joshi','1995-06-20','m','[email protected]',7453156408,'Village Ambedkar Nagar No 4 Jammu PS Katras Dhanbad Jharkhand',107),
(2002,'Mukesh','mehta','1998-05-05','m','[email protected]',9658426160,'Near Govt School Manikyapur Village & Kanheipur Post Ganjam Orissa',108),
(2003,'Nikhil','Kumar','2000-03-09','m','[email protected]',7586482314,'Deogam budhamba polasara ganjam polosara Ganjam Orissa',109),
(2004,'Puja','Duhudi','2002-01-12','f','[email protected]',1249687591,'66 RAGHUNATH MANDIR DHUNKEPADA DHUNKAPADA BUGUDA GANJAM DHUNKAPADA Ganjam Orissa',110),
(2005,'Awek','Parida','2003-11-17','m','[email protected]',7594856175,'At. Po. Banshelki Tq Udgir Banshelki Latur Maharashtra',111),
(2006,'Alok','Pradhan','2005-09-21','m','[email protected]',9585859696,'DUDHAURNA SALAMATPUR GAJIPUR UP',112);
SELECT *FROM seller;
CREATE TABLE sellerlogin AS SELECT s.`sel_fname`, s.`sel_lname`,s.`email_id`,
m.`username`,m.`password` FROM seller s
INNER JOIN user m ON s.`user_id` = m.`user_id`;
DESCRIBE sellerlogin;
SELECT * FROM sellerlogin;
-- product Details
----- Type Of Product
CREATE TABLE Product_Type(
Type_Id INT,
Type_Name CHAR(100),
cus_id int REFERENCES customer(cus_id)
);
describe product_type;
INSERT INTO product_type VALUES
(2000011, 'Electronic Product',1001),
(2000012, 'Tech Product',1002),
(2000013, 'Kitchen product',1003),
(2000014, 'Daily use product',1004),
(2000015, 'Fachion',1005),
(2000016, 'Medical',1006);
SELECT * FROM product_type;
CREATE Table Product(
product_id int NOT NULL,
product_name CHAR(100),
product_prise DOUBLE,
vendor_id int REFERENCES seller(sel_id),
product_type int REFERENCES product_type(type_id)
);
DROP TABLE Product_Type;
INSERT INTO Product VALUES
(3000011 ,'Bata Shoes', 1000, 2001, 2000015),
(3000012 ,'Reebook Shoes' ,800, 2002, 2000015),
(3000013 ,'Samsung M30' ,12000 ,2003, 2000012),
(3000014 ,'Motorola G8+' ,19000 ,2004 ,2000012),
(3000015 ,'Paracetamol' ,800 ,2005 ,2000016),
(3000016 ,'Acetaminophen' ,5060 ,2006 ,2000016),
(3000017 ,'Jewellery', 15620, 2006, 2000015),
(3000018 ,'Oil' ,150 ,2005 ,2000013),
(3000019 ,'Hair Oil' ,100, 2005, 2000013),
(3000020 ,'Titan Watch ',2500, 2004, 2000015),
(3000021 ,'Sonata Watch' ,1500 ,2001, 2000015),
(3000022 ,'Monitor', 15999 ,2001 ,2000012),
(3000023 ,'CMOS Camera ',5000 ,2006 ,2000012),
(3000024 ,'TP-Link USB Bluetooth', 1500, 2006, 2000012),
(3000025 ,'Kurta' ,500 ,2003 ,2000015),
(3000026 ,'Safari Thorium ',750 ,2001 ,2000015),
(3000027 ,'Weighing Scale' ,300, 2004, 2000012),
(3000028 ,'Trimmer', 2500 ,2005 ,2000012),
(3000029 ,'Dry Iron', 2000, 2001 ,2000011),
(3000030 ,'Auserio Mens Slip-On' ,600 ,2005 ,2000015);
ALTER TABLE product RENAME COLUMN product_name to prod_name;
ALTER TABLE product RENAME COLUMN product_id to prod_id;
ALTER TABLE product RENAME COLUMN product_prise to prod_prise;
ALTER TABLE product RENAME COLUMN product_type to prod_type;
SELECT *FROM Product ;
CREATE Table Addcart AS SELECT p.`prod_id`,p.`prod_name`,p.`prod_prise`,cus.`cus_fname`,cus.`cus_lname`,
cus.`cus_number`,cus.`cus_address`,cus.`c_city`,cus.`c_state`,cus.`payment_id`
FROM product p INNER JOIN customer cus ON p.`prod_type` = cus.`Product_Type`;
DROP TABLE Addcart;
SELECT *FROM Addcart ;
-- total number product count and total prise / customer payment
CREATE Table Total_Num_Of_prod AS SELECT cus_fname AS First_Name,cus_lname AS Last_Name ,cus_number AS Number,cus_address AS Address,c_city AS City,c_state AS State, COUNT(payment_id) AS Product_COUNT,payment_id AS Payment,
SUM(prod_prise) AS Prise
FROM Addcart GROUP BY cus_fname;
SELECT *FROM Total_Num_Of_prod ;
CREATE TABLE Midline_estor as SELECT p.`prod_id` AS Produce_Id, p.`prod_name` AS Product, p.`prod_prise` AS Prise, ty.`Type_Name` FROM
product_type ty INNER JOIN product p on ty.`type_id` = p.`prod_type`;
DROP TABLE Midline_estor;
SELECT *FROM Midline_estor;
-- Cart Details
CREATE TABLE cart(
cart_id int NOT NULL,
product_id INT REFERENCES Midline_estor(Product_id),
quantity INT,
cus_id int REFERENCES customer(cus_id),
order_id INT
);
DROP TABLE cart;
INSERT INTO cart VALUES
(1000011 ,3000011 ,1 ,1001,1000001),
(1000013 ,3000017 ,1 ,1002,1000002),
(1000014 ,3000014 ,1 ,1003,1000003),
(1000015 ,3000028 ,1 ,1004,1000004),
(1000016 ,3000014 ,1 ,1005,1000005),
(1000017 ,3000015 ,1 ,1006,1000006);
SELECT *FROM cart;
CREATE TABLE Seller_Update AS
SELECT cus.`cus_id` AS CustomerId, cus.`cus_fname` AS Fname,cus.`cus_lname` AS Lname, cus.`cus_email` AS Email_Id,
cus.`cus_number`AS Number, cus.`cus_address` AS Address, cus.`c_city` AS City,
cus.`c_state` AS State, cr.`Product_id`, cr.`quantity` AS Quantity , cr.`order_id`
FROM customer cus INNER JOIN
cart cr ON cus.`cus_id`= cr.`cus_id`
DROP TABLE Seller_Update;
SELECT *FROM Seller_Update;
-- Payment Details
CREATE TABLE payment(
payment_id INT NOT NULL,
cust_id INT REFERENCES customer(cus_id),
Ac_number long,
Bank CHAR(100),
PRIMARY KEY(payment_id)
);
describe payment;
INSERT INTO payment VALUES
(100001 ,1001, 3685446237952030 ,'SBI'),
(100002, 1002 ,4937515149549500 ,'ICICI'),
(100003 ,1003 ,4808437630180080 ,'SBI'),
(100004, 1004 ,4828526348154570 ,'HDFC'),
(100005 ,1005, 4248168108403530 ,'SBI'),
(100006 ,1006 ,4068107840469510, 'ICICI');
SELECT *FROM payment;
-- Product Order Details
CREATE TABLE Order_Details(
order_id int NOT NULL,
cust_id INT REFERENCES customer(cus_id),
cart_id INT REFERENCES cart(cart_id),
payment_id INT REFERENCES payment(payment_id),
payment_date DATE,
seller_update INT REFERENCES Order_Update(order_number),
order_date DATE,
ship_date DATE,
ship_method CHAR(100),
Delivery_date DATE
);
describe Order_Details;
INSERT INTO Order_Details VALUES
(1000001 ,1001 ,1000011 ,100001 ,'2019-12-20',3000011,'2019-12-20', '2019-12-20', 'Delivery', '2019-12-30'),
(1000002 ,1001 ,1000012 ,100001 ,'2020-02-01',3000012,'2020-02-29', '2020-02-01', 'Ecom', '2020-02-07'),
(1000003 ,1003 ,1000013 ,100003 ,'2020-03-01',3000013,'2020-03-01', '2020-03-01', 'DTDC', '2020-03-03'),
(1000004 ,1002 ,1000014 ,100002 ,'2019-04-01',3000014,'2019-04-01', '2019-04-01', 'Indian post', '2019-04-07'),
(1000005 ,1003 ,1000015 ,100003 ,'2012-05-07',3000015,'2022-05-07', '2022-05-07', 'Indian post', '2022-05-09'),
(1000006 ,1004 ,1000016 ,100004 ,'2019-03-08',3000016,'2019-03-08', '2019-03-08', 'Indian post', '2019-03-15'),
(1000007 ,1005 ,1000017 ,100005 ,'2020-05-20',3000017,'2020-05-20', '2020-05-21', 'DTDC', '2020-05-22');
DROP TABLE Order_Details;
SELECT *FROM Order_Details;
DELIMITER $$
CREATE PROCEDURE `Midline_eStoer`.`customer`(IN searchdata varchar(100))
BEGIN
SELECT *FROM customer WHERE cus_fname = searchdata;
END$$
DELIMITER;
DROP PROCEDURE customer;
CALL customer('nikhil');
DELIMITER $$
CREATE PROCEDURE `Midline_eStoer`.`product`( )
BEGIN
SELECT *FROM Product;
END$$
DELIMITER;
CALL Product();
DELIMITER $$
CREATE PROCEDURE `Midline_eStoer`.`Mamber`()
BEGIN
SELECT *FROM Mamber;
END$$
DELIMITER;
DROP PROCEDURE customer;
CALL Mamber();
DELIMITER $$
CREATE PROCEDURE `Midline_eStoer`.`member_data`()
BEGIN
SELECT *FROM member_data;
END$$
DELIMITER;
DROP PROCEDURE user;
CALL member_data();
DELIMITER $$
CREATE PROCEDURE `Midline_eStoer`.`seller`()
BEGIN
SELECT *FROM seller;
END$$
DELIMITER;
DROP PROCEDURE seller;
CALL seller();
DELIMITER $$
CREATE PROCEDURE `Midline_eStoer`.`Addcart`()
BEGIN
SELECT *FROM Addcart;
END$$
DELIMITER;
DROP PROCEDURE Addcart;
CALL Addcart();
DELIMITER $$
CREATE PROCEDURE `Midline_eStoer`.`Total_Num_Of_prod`()
BEGIN
SELECT *FROM Total_Num_Of_prod;
END$$
DELIMITER;
DROP PROCEDURE Total_Num_Of_prod;
CALL Total_Num_Of_prod();
DELIMITER $$
CREATE PROCEDURE `Midline_eStoer`.`Midline_estor`()
BEGIN
SELECT *FROM Midline_estor;
END$$
DELIMITER;
DROP PROCEDURE Midline_estor;
CALL Midline_estor();