Challenge

Project 1

SSharath P27 Nov 2022

Project 1 : Create Entity Relationship Diagram for Ecommerce Website

The Ecommerce project ER diagram is attached in draw.io file & image as shown below

Task 1,Task 2 ,Task3 attached sql database file and USE sakala_marukatte; database)

Task 3 : Answers given below

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 product_id TO prod_id;
ALTER TABLE product RENAME COLUMN product_name TO prod_name;
ALTER TABLE product RENAME COLUMN product_description TO prod_description;
ALTER TABLE product RENAME COLUMN avail_qty TO prod_qty;
ALTER TABLE product RENAME COLUMN Price TO prod_price;
ALTER TABLE product RENAME COLUMN current_discount TO prod_currnt_discount;
ALTER TABLE product RENAME COLUMN color TO prod_color;
ALTER TABLE product RENAME COLUMN reviews TO prod_reviews;
ALTER TABLE product RENAME COLUMN discount_coupon TO prod_dicnt_coupon;
ALTER TABLE product RENAME COLUMN avg_ratings TO prod_avg_rating;
ALTER TABLE product RENAME COLUMN total_ratings TO prod_total_ratings;
ALTER TABLE product RENAME COLUMN review_country TO prod_rev_country;
ALTER TABLE product RENAME COLUMN related_prod_id TO prod_related_id;

Q 3 WAQ to fetch the details of customer who have purchased at least one product

Query:

SELECT customer.cust_id,customer.cust_fname,customer.cust_lname,
COUNT(order_details.order_id) order_quantity
FROM customer INNER JOIN order_details
ON customer.cust_id = order_details.cust_id
GROUP BY customer.cust_id HAVING COUNT(order_details.order_id)>=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:

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 customer.cust_id,customer.cust_fname,customer.cust_lname,
SUM(total_order_cost)"Purchased Amount"
FROM order_details LEFT JOIN customer
ON customer.cust_id = order_details.cust_id
WHERE order_details.order_status ="delivered"
AND total_order_cost>10000 AND customer.cust_id
HAVING COUNT(order_details.order_id)>1;

Q 6. Write a simple procedure to run all the table creation statements. insert statements and alter statements at once.

Query :

DELIMITER$$

CREATE PROCEDURE `sakala_marukatte`.`sakala_marukatte_samiti`()

BEGIN

CREATE TABLE customer(cust_id INT PRIMARY KEY,cust_fname CHAR(50),
cust_lname CHAR(50),gender CHAR(20),city CHAR(50),cust_address CHAR(255),
cust_email_ID VARCHAR(100),cust_cont_number VARCHAR(50),Premium_member CHAR(50),
normal_member CHAR(50),current_order CHAR(100),cust_password VARCHAR(100),
previous_order CHAR(100),country CHAR(50),postal_code INT);

INSERT INTO customer VALUES(101,'Ravi','Badagabettu','M','Bengaluru',
'#1/1, Vijayanagar, 4th cross','[email protected]','888xxxx587','Yes','No',
'Atlas','**********','Titan Watch','India',500001),(102,'Piyush',
'Goyal','M','Mumbai','#1/1, dhara, 4th cross','[email protected]',
'897xxxx587','No','Yes','Minoxidal','**********','Hyervita',
'India',580001),(103,'Ananda','adagabetta','M','Bengaluru',
'#1/1, Vijayanagar, 4th cross','[email protected]','888xxxx587','Yes','No',
'Atlas','**********','Titan Watch','India',500001);

CREATE TABLE seller(seller_id INT PRIMARY KEY,seller_fname CHAR(50),
seller_lname CHAR(50),seller_password VARCHAR(100),
seller_cont_number VARCHAR(50),seller_email_ID VARCHAR(100));

INSERT INTO seller VALUES(201,'Raj','Shetty','*********','846xxxx828',
'[email protected]'),(202,'Shine','Shetty','*********','850xxxx850',
'[email protected]');

CREATE TABLE category(category_id INT PRIMARY KEY,cat_name CHAR(50),
sub_cat CHAR(50),images VARCHAR(50),tag_id VARCHAR(20),
segments VARCHAR(100),brand_name CHAR(50));

INSERT INTO category VALUES(601,'grocery','food','photo',111,
'Healthy and natural','India Gate'),(602,'Toy','game accessory',
'photo',112,'Sports','Sports club');

CREATE TABLE administrator(admin_id INT PRIMARY KEY,
admin_fname CHAR(50),admin_lname CHAR(50),admin_password VARCHAR(100));

INSERT INTO administrator VALUES(301,'Kantha','Raju',
'*******'),(302,'Rama','Kantha','*******');

CREATE TABLE billing_info(billing_id INT PRIMARY KEY, bill_date DATE,
billing_address VARCHAR(255),credit_card_id INT,credit_card_number VARCHAR(100),
credit_card_exp_date DATE,credit_card_pin VARCHAR(50), total_discount DECIMAL(8,2));

INSERT INTO billing_info VALUES (701,'2001-12-10','hebbala,1st phase',
001,'********2015','2003-05-31','****',0.05),(702,'2005-10-01',
'Peenya,1st phase',002,'********1978','2023-04-30','****',0.1);

CREATE TABLE shipping_details(shipper_id INT PRIMARY KEY,
company_name CHAR(50),shipper_contact_num VARCHAR(20),
shipping_address CHAR(255));

INSERT INTO shipping_details VALUES(901,'HMT Rice','657xxxx589',
'Nelamangala'),(902,'Varun toys','625xxxx855','Hosur');

CREATE TABLE product(product_id INT PRIMARY KEY,product_name CHAR(80),
Product_description CHAR(255),avail_qty INT,Price DECIMAL(15,2),
current_discount DECIMAL(5,2),color CHAR(20),reviews VARCHAR(50),
discount_coupon VARCHAR(50),avg_ratings DECIMAL(5,2),
total_ratings DECIMAL(5,2),review_country CHAR(50),
related_prod_ID VARCHAR(50),seller_id INT,category_id INT,shipper_id INT,
FOREIGN KEY(seller_id) REFERENCES seller(seller_id),
FOREIGN KEY(category_id) REFERENCES category(category_id),
FOREIGN KEY(shipper_id) REFERENCES shipping_details(shipper_id));

INSERT INTO product VALUES(501,'Rice','Basmati Rice',1,1400,0.05,
'White','50','GGHG',4.2,4,'India',301,201,601,901),(502,'Tennins ball',
'Rubber made',5,200,0,'Yellow','110','HGGD',4.5,3,'India',333,202,602,
902);

CREATE TABLE Add_remove_products(prod_added CHAR(20), prod_remov CHAR(20),
category_id INT,admin_id INT,seller_id INT,
FOREIGN KEY (category_id) REFERENCES category(category_id),
FOREIGN KEY (admin_id) REFERENCES administrator(admin_id),
FOREIGN KEY (seller_id) REFERENCES seller(seller_id));

INSERT INTO add_remove_products VALUES('yes','no',601,301,
202),('yes','yes',602,302,202);

CREATE TABLE logins(seller_id INT,cust_id INT,admin_id INT,
FOREIGN KEY(seller_id) REFERENCES seller(seller_id),
FOREIGN KEY(cust_id) REFERENCES customer(cust_id),
FOREIGN KEY(admin_id) REFERENCES administrator(admin_id));

INSERT INTO logins VALUES(201,101,301),(202,102,302);

CREATE TABLE cart(cart_id INT PRIMARY KEY, no_of_products INT,
checkout CHAR(20),product_id INT,cust_id INT,
FOREIGN KEY (product_id) REFERENCES product(product_id),
FOREIGN KEY (cust_id) REFERENCES customer(cust_id));

INSERT INTO cart VALUES(401,2,'Yes',501,102),(402,2,'no',501,
102),(403,1,'Yes',502,101);

CREATE TABLE orders(order_id INT PRIMARY KEY, order_date DATE,
delivered_date DATE,order_replacements CHAR(50),shipper_id INT,
FOREIGN KEY (shipper_id) REFERENCES shipping_details(shipper_id));

INSERT INTO orders VALUES(1001,'2005-02-01','2005-02-03','No',
901),(1002,'2005-02-03','2005-02-05','Yes',902);

CREATE TABLE order_details(order_status CHAR(70),order_quantity INT,
cust_id INT,seller_id INT,product_id INT,billing_id INT,order_id INT,
total_order_cost DECIMAL(15,2) REFERENCES product(Price),
FOREIGN KEY (cust_id) REFERENCES customer(cust_id),
FOREIGN KEY (seller_id) REFERENCES seller(seller_id),
FOREIGN KEY (product_id) REFERENCES product(product_id),
FOREIGN KEY (billing_id) REFERENCES billing_info(billing_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id));

INSERT INTO order_details VALUES('delivered',1,102,201,501,
701,1001,1400),('packed',2,102,202,502,702,1002,200),('delivered',
2,101,201,501,701,1002,15000),('packed',5,102,202,502,
702,1001,1500),('delivered',4,103,201,501,701,
1001,18000),('delivered',4,103,201,501,701,
1002,18000);

END$$
DELIMITER;

CALL sakala_marukatte_samiti()

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.