Project 1

ER diagram is submitted as link

task 1 and task 2:

CREATE DATABASE shopnow;
USE shopnow;
CREATE TABLE Cart(
Cart_id VARCHAR(7) PRIMARY KEY
);
CREATE TABLE seller(
seller_id VARCHAR(6) PRIMARY KEY,
s_pass VARCHAR(10),
sel_name VARCHAR(20),
address VARCHAR(10),
mob_no VARCHAR(20) NOT NULL,
categories VARCHAR(20)
);
CREATE TABLE product(
product_id INT PRIMARY KEY,
color VARCHAR(20),
p_Size VARCHAR(20),
gender CHAR(1),
cost INT(5),
quantity INT(2),
seller_id VARCHAR(20),
FOREIGN KEY (seller_id) REFERENCES seller(seller_id)
);
CREATE TABLE orders(
order_id INT(5) PRIMARY KEY,
product_id INT,
order_status VARCHAR(255) NOT NULL,
delivery_expectation VARCHAR(255) NOT NULL,
delivery_date VARCHAR(20) NOT NULL,
order_date VARCHAR(20) NOT NULL,
FOREIGN KEY (product_id) REFERENCES product(product_id)
);
CREATE TABLE customer(
cus_name VARCHAR(20),
product_id INT,
order_id INT(5),
cus_id VARCHAR(20) PRIMARY KEY,
Address VARCHAR(20),
Pincode INT(6),
Phone_number_s INT(11),
Cart_id VARCHAR(7) NOT NULL,
cust_city VARCHAR(15),
FOREIGN KEY(product_id) REFERENCES product(product_id)
);
CREATE TABLE Cart_products(
quantity_wished INT PRIMARY KEY,
date_added VARCHAR(10),
product_id INT,
FOREIGN KEY (product_id) REFERENCES product(product_id)
);
CREATE TABLE review_of_products(
rev_id INT PRIMARY KEY,
rev_stars INT,
product_id INT,
FOREIGN KEY (product_id) REFERENCES product(product_id)
);
INSERT INTO Cart VALUES('crt101');
INSERT INTO seller VALUES('sid100','12345','aman','delhi cmc','7878987382','diff2');
INSERT INTO product VALUES(23282,'red','32','M',1200,10,'sid100');
INSERT INTO orders VALUES(3001,23282,'replaced','reached','20-2-2022','18-2-2022');
INSERT INTO customer VALUES('abhi',23282,1001,'pavi23','G-453','632014',98931358, 'crt1011','Ahmedabad');
INSERT INTO Cart_products VALUES(3,'20-10-1999',123);
INSERT INTO review_of_products VALUES(493,4,1002);

task 3:

1.ans:

SELECT * FROM customer
ORDER BY cust_city ASC;

2.ans:

ALTER TABLE product CHANGE COLUMN product_id prod_id INT;
ALTER TABLE product CHANGE COLUMN color prod_color VARCHAR(20);
ALTER TABLE product CHANGE COLUMN p_Size prod_Size INT;
ALTER TABLE product CHANGE COLUMN gender prod_gender CHAR(1);
ALTER TABLE product CHANGE COLUMN cost prod_cost INT;
ALTER TABLE product CHANGE COLUMN quantity prod_quatity INT;
ALTER TABLE product CHANGE COLUMN seller_id prod_seller_id INT;

3.ans:

SELECT *
FROM customer
INNER JOIN product ON customer.product_id = product.product_id;

4.ans :

SELECT *
FROM customer
INNER JOIN product ON customer.product_id = product.product_id
WHERE cus_name LIKE 'A%a' AND HAVING COUNT(order_id)>1;

5.ans:

SELECT *
FROM orders
LEFT JOIN product ON orders.product_id = product.product_id
WHERE product.prod_cost>10000;

6.ans:

CREATE DATABASE shopnow.ecommerce;
USE shopnow.ecommerce;
DELIMITER $$
CREATE PROCEDURE 'shopnow.ecommerce',shopnow.ecommerce1()
BEGIN
CREATE TABLE Cart(
Cart_id VARCHAR(7) PRIMARY KEY
);
CREATE TABLE seller(
seller_id VARCHAR(6) PRIMARY KEY,
s_pass VARCHAR(10),
sel_name VARCHAR(20),
address VARCHAR(10),
mob_no INT(20) NOT NULL,
categories VARCHAR(20)
);
CREATE TABLE product(
product_id INT PRIMARY KEY,
color VARCHAR(20),
p_Size VARCHAR(20),
gender CHAR(1),
cost INT(5),
quantity INT(2),
seller_id VARCHAR(20),
FOREIGN KEY (seller_id) REFERENCES seller(seller_id)
);
CREATE TABLE orders(
order_id INT(5) PRIMARY KEY,
product_id INT,
order_status VARCHAR(255) NOT NULL,
delivery_expectation VARCHAR(255) NOT NULL,
delivery_date VARCHAR(20) NOT NULL,
order_date VARCHAR(20) NOT NULL,
FOREIGN KEY (product_id) REFERENCES product(product_id)
);
INSERT INTO Cart VALUES('crt101');
INSERT INTO seller VALUES('sid100','12345','aman','delhi cmc',7878987382,'diff2');
INSERT INTO product VALUES(23282,'red','32','M',1200,10,'sid100');

ALTER TABLE product CHANGE COLUMN color prod_color VARCHAR(20);
ALTER TABLE product CHANGE COLUMN p_Size prod_Size INT;
ALTER TABLE product CHANGE COLUMN gender prod_gender CHAR(1);

END $$
DELIMITER
CALL shopnow.ecommerce1();