Project 1
Project 1:
Project 1 : DATABASE (MySQL)
Create Entity Relationship Diagram for Ecommerce Website :
(The Ecommerce project ER diagram send into attached draw.io file)
Task 1,Task 2 ,Task3 into attached sql database file and USE ecommerce; database.
Task 3 : QUE ANS
Q 1 WAQ to fetch the details of the customers in the order of their city
Query :
SELECT * FROM customer ORDER BY city;
Q 2 WAQ to alter the table product by renaming all the columns by preceding them with 'prod-' .
Query :
ALTER TABLE product RENAME COLUMN productID TO prod_ID;
ALTER TABLE product RENAME COLUMN product_name TO prod_name;
ALTER TABLE product RENAME COLUMN quant TO prod_quant;
ALTER TABLE product RENAME COLUMN color TO prod_color;
ALTER TABLE product RENAME COLUMN size TO prod_size;
ALTER TABLE product RENAME COLUMN Actual_cost TO prod_actual_cost;
ALTER TABLE product RENAME COLUMN Discount_Coupon TO prod_Disc_coupon;
ALTER TABLE product RENAME COLUMN Discount_Amt TO prod_Disc_amt;
ALTER TABLE product RENAME COLUMN Total_cost TO prod_total_cost;
ALTER TABLE product RENAME COLUMN customerID TO prod_customerID;
ALTER TABLE product RENAME COLUMN sellerID TO prod_SellerID;
ALTER TABLE product RENAME COLUMN brand TO prod_brand;
ALTER TABLE product RENAME COLUMN Discription TO prod_Discription;
ALTER TABLE product RENAME COLUMN tags TO prod_tags;
ALTER TABLE product RENAME COLUMN cust_reviws TO prod_cust_review;
Q 3 WAQ to fetch the details of customer who have purchased at least one product
Query:
SELECT c.customerID,c.customer_name,COUNT(o.orderID)TOTAL_ORDERS FROM
customer c INNER JOIN orders o ON c.customerID = o.customerID GROUP BY c.customerID HAVING COUNT(o.orderID)>=1;
Q 4 WAQ to fetch the details of the customer who have name starting from A and ending from A and has purchased more than one item.
Query:
SELECT c.customerID,c.customer_name,COUNT(o.orderID)TOTAL_ORDERS FROM
customer c INNER JOIN orders o ON c.customerID = o.customerID GROUP BY c.customerID HAVING COUNT(o.orderID)>1 AND c.customer_name LIKE "A%A";
Q 5. WAQ to all the information about the orders which are already delivered and has cost more than 10000 and also has been purchased by more than 1 customer.
Query :
SELECT c.customerID,c.customer_name,SUM(order_cost)"Purchased Amount" FROM
orders o LEFT JOIN customer c ON c.customerID = o.customerID WHERE o.order_status ="Delivered" AND order_cost>10000 AND c.customerID HAVING COUNT(o.orderID)>1;
Q 6. Write a simple procedure to run all the table creation statements. insert statements and alter statements at once.
Query :
DELIMETER $$
CREATE PROCEDURE
Create_All_Tables_and_puting_insertion()
BEGIN
CREATE TABLE admin_(
adminID INT,
admin_Pass VARCHAR(255)UNIQUE NOT NULL,
admin_name VARCHAR(255)NOT NULL,
EmailID VARCHAR(255),
contact_No VARCHAR(255)
);
INSERT INTO admin_ VALUES(1001,'sp9**','Sathvik pandey','[email protected]','8935925950'),
(1002,'pratp3','Pratham vani','[email protected]','9930453560');
CREATE TABLE seller(
sellerID INT PRIMARY KEY NOT NULL,
seller_Pass VARCHAR(255),
seller_Name VARCHAR(255),
Email VARCHAR(255),
contact_No VARCHAR(10)
);
INSERT INTO seller VALUES(3001,'*****','Mayank Rajput','[email protected]','9025422500'),
(3005,'******','Ishwar more','[email protected]','8347920290'),
(3002,'*****','Prasad Girase','[email protected]','9909540309'),
(3004,'******','Vandana Enterprise','[email protected]','8329299345');
CREATE TABLE customer(
customerID INT PRIMARY KEY NOT NULL,
customer_Pass VARCHAR(100),
cust_type VARCHAR (100),
customer_name CHAR(50) ,
DOB DATE,
gender CHAR(50),
contact_no CHAR(10),
del_address VARCHAR(255),
city CHAR(50),
email VARCHAR(50),
current_order VARCHAR(255) NOT NULL,
prev_order VARCHAR(255) NOT NULL,
paymentsavedinfo CHAR(50)
);
INSERT INTO customer VALUES(16111,'*****','Premium','Kishor Patil','1998-02-23','Male','7023435454','flat no 102,Kothrud','Pune','[email protected]','T-shirt','Computer Monitor','yes'),
(18209,'*****','Normal','Riya Deshmukh','1993-05-18','Female','7823430454','43,Sunshine Colony','Pune','[email protected]','Brown Rice','Wooden Dining Table','yes'),
(23103,'*****','Normal','Vikas Kadam','1996-12-01','Male','8832759042','flat no 302 ,Malhar baug ','Nashik','[email protected]','Wooden Dining Table','T-Shirt','yes'),
(78115,'******','Premium','Jay Patil','2001-09-13','Male','9014431560','flat no 20,shiv colony','SambhajiNagar','[email protected]','Computer Monitor','Headphones','yes'),
(90110,'*****','Premium','Atharva Ahuja','1994-07-15','Male','8865435494','102 ,Manas Society','Nashik','[email protected]','Cooking Oil','Computer Mouse','yes');
CREATE TABLE product(
productID INT PRIMARY KEY NOT NULL,
product_name CHAR(50),
quant INT,
color CHAR(20),
size CHAR(20),
Actual_cost DECIMAL(7,2),
Discount_Coupon CHAR(20),
Discount_Amt DECIMAL(7,2),
Total_cost DECIMAL(7,2),
customerID INT,
FOREIGN KEY(customerID) REFERENCES customer(customerID),
sellerID INT,
FOREIGN KEY(sellerID) REFERENCES seller(sellerID),
brand CHAR(50),
Discription VARCHAR(100),
tags VARCHAR(255),
cust_reviws CHAR(20)
);
INSERT INTO product VALUES(101,'T-Shirt',2,'Yellow/Red','XL',530,'SHOP1',31,499,16111,3005,'Levis',' 2-combo pack with multicolor,Material use:cotton','Fasion','4.3 Stars'),
(134,' Wooden Dining Table ',1,'white/Brown','long',15500,'FURN15',2325,13175,23103,3004,'Solimo','6 seater with 3 year warrany Standard quality wood Indian Design','Furniture','4.7 Stars'),
(122,'Brown Rice',2,'Brown','long grains',650,'GLOCE',100,550,18209,3001,'Daawat','weight 5KG,Zero cholestrol,whole grain','Food','3.7 Stars'),
(922,'Computer Monitor',1,'Black/grey','27 inch',15000,'OFF20',3000,12000,78115,3002,'Acer','Warranty 1 year','computer','4.2 Stars'),
(320,'Cooking Oil',1,'Gold Can','5L',1000,'GLOCE',110,890,90110,3004,'Fortune','Healty Refined oil','food','4.1 Stars'),
(136,'wooden Chairs',1,'silver','10',10000,'Furn',110,10119.90,90110,3004,'Solimo','wood chairs','Furnitures','3.7 Stars');
CREATE TABLE categories (
categoriesID INT NOT NULL,
categorieName VARCHAR(255),
productID INT,
FOREIGN KEY (productID)REFERENCES product(productID)
);
INSERT INTO categories VALUES(1,'Glocery',122),
(2,'Clothing',101),
(3,'ELectronics',922),
(4,'Furniture',134),
(4,'Furniture',136);
CREATE TABLE subcategories(
subcateID INT ,
subcateNames VARCHAR(100),
categoriesID INT,
productID INT REFERENCES categories(categoriesID),
FOREIGN KEY (productID) REFERENCES product(productID)
);
INSERT INTO subcategories VALUES(05,'Mens Fashion',2,101),
(011,'Tables',4,134),
(08,'Computer Accessories',3,922),
(01,'Rice',1,122),
(02,'Oil',1,320);
CREATE TABLE orders(
orderID INT ,
order_Date DATE,
order_status VARCHAR(255),
order_cost DECIMAL(7,2) REFERENCES product(Total_cost),
customerID INT,
FOREIGN KEY (customerID) REFERENCES customer(customerID),
productID INT ,
FOREIGN KEY (productID)REFERENCES product(productID)
);
INSERT INTO orders VALUES(567,'2021-09-02','Recieved',499,16111,101),
(897,'2021-08-26','shipped',550,18209,122),
(967,'2021-07-14','shipped',890,23103,134),
(167,'2021-09-01','Delivered',11040,78115,922),
(107,'2021-09-05','Cancelled',13175,90110,134),
(109,'2021-08-10','Delivered',10119.90,90110,136);
CREATE TABLE payment(
paymentID INT PRIMARY KEY ,
paymentCategorie VARCHAR(100),
customerID INT REFERENCES customer(customerID),
orderID INT REFERENCES orders(orderID)
);
INSERT INTO payment VALUES(1233,'UPI',78115,167),
(1130,'UPI',18209,897),
(1433,'Debit Card',23103,967),
(1823,'Cash on Delivery',90110,107),
(1505,'Credit card',90110,109),
(1003,'Cash on Delivery',16111,567);
CREATE TABLE shipment(
shipmentID INT ,
shipmentDate DATE,
shipment_city VARCHAR(100),
invoice VARCHAR(100),
orderID INT REFERENCES orders(orderID),
paymentID INT REFERENCES payment(paymentID),
customerID INT REFERENCES customers(customerID)
);
INSERT INTO shipment VALUES (13344,'2021-09-05','Delhi','Overall Amount pending via Cash on Delivery including GST + Charge',567,1003,16111),
(15067,'2021-08-27','Delhi','Overall Amount paid via upi including GST + Charge',897,1823,18209),
(13040,'2021-08-29','Pune','Overall Amount paid via upi including GST + Charge',167,1233,78115),
(10044,'2021-07-29','Jaipur','Overall Amount paid via Debit Card including GST + Charge',967,1433,23103),
(18003,'2021-09-01','Pune','Overall Amount paid via Cash on Delivery including GST + Charge',107,1823,90110),
(18000,'2021-08-07','Jaipur','Overall Amount paid via Credit card including GST + Charge',109,1003,90110);
ALTER TABLE product RENAME COLUMN productID TO prod_ID;
ALTER TABLE product RENAME COLUMN product_name TO prod_name;
ALTER TABLE product RENAME COLUMN quant TO prod_quant;
ALTER TABLE product RENAME COLUMN color TO prod_color;
ALTER TABLE product RENAME COLUMN size TO prod_size;
ALTER TABLE product RENAME COLUMN Actual_cost TO prod_actual_cost;
ALTER TABLE product RENAME COLUMN Discount_Coupon TO prod_Disc_coupon;
ALTER TABLE product RENAME COLUMN Discount_Amt TO prod_Disc_amt;
ALTER TABLE product RENAME COLUMN Total_cost TO prod_total_cost;
ALTER TABLE product RENAME COLUMN customerID TO prod_customerID;
ALTER TABLE product RENAME COLUMN sellerID TO prod_SellerID;
ALTER TABLE product RENAME COLUMN brand TO prod_brand;
ALTER TABLE product RENAME COLUMN Discription TO prod_Discription;
ALTER TABLE product RENAME COLUMN tags TO prod_tags;
ALTER TABLE product RENAME COLUMN cust_reviws TO prod_cust_review;
2.
ans: