Project 1

Attached ER diagram, table creation and insert statements in a file which includes TASK 1 and TASK 2. PFA below

TASK 3 ->

1. SELECT *
FROM Customer
ORDER BY city;

2. ALTER TABLE Product
RENAME COLUMN product_id TO prod_id,
RENAME COLUMN product_name TO prod_name,
RENAME COLUMN product_price TO prod_price,
RENAME COLUMN quantity TO prod_quantity;

3. SELECT c.cust_id, c.cust_name, c.gender, c.city, p.payment_id, o.ord_id, o.ord_date, o.status
FROM Customer c, Payment p, Order o
WHERE c.cust_id = p.cust_id AND p.ord_id = o.ord_id;

4. SELECT *
FROM Customer c, Order o
WHERE c.cust_name LIKE 'A%A'
AND o.ord_id> 1

5. SELECT *
FROM Order
WHERE status = 'delivered'
AND ord_value > 10000
AND id IN (
SELECT cust_id
FROM Customer
HAVING COUNT(DISTINCT cust_id) > 1
);

6. DELIMITER $$

CREATE PROCEDURE myecommerce()

BEGIN

CREATE TABLE Customer (
cust_id int NOT NULL,
cust_name varchar(20),
email varchar(50),
gender varchar(1),
city varchar(20),
ph_no varchar(10),
PRIMARY KEY (cust_id)
);

CREATE TABLE Product (
product_id int NOT NULL,
sell_id int,
product_name varchar(20),
product_price int,
quantity int,
PRIMARY KEY (product_id)
);

CREATE TABLE Seller (
sell_id int NOT NULL,
prod_id int,
city varchar(20),
ph_no varchar(10),
PRIMARY KEY (sell_id)
);

CREATE TABLE Order (
ord_id int NOT NULL,
prod_id int,
ord_value int,
ord_date date,
status varchar(10),
PRIMARY KEY (ord_id)
);

CREATE TABLE Payment (
payment_id int NOT NULL,
cust_id int,
ord_id int,
card_no varchar(16),
ship_address varchar(30),
PRIMARY KEY (payment_id)
);

CREATE TABLE View (
cust_id int,
prod_id int,
categories varchar(20),
sort_by varchar(20),
FOREIGN KEY (cust_id) REFERENCES Customer(cust_id),
FOREIGN KEY (prod_id) REFERENCES Product(product_id)
);

CREATE TABLE Sell (
sell_id int,
prod_id int,
FOREIGN KEY (sell_id) REFERENCES Seller(sell_id),
FOREIGN KEY (prod_id) REFERENCES Product(product_id)
);

CREATE TABLE Cart (
cart_id int,
prod_id int,
FOREIGN KEY (prod_id) REFERENCES Product(product_id)
);

CREATE TABLE Confirm (
ord_id int,
payment_id int,
FOREIGN KEY (ord_id) REFERENCES Order(ord_id),
FOREIGN KEY (payment_id) REFERENCES Payment(payment_id)
);

CREATE TABLE Ship (
cust_id int,
payment_id int,
FOREIGN KEY (cust_id) REFERENCES Customer(cust_id),
FOREIGN KEY (payment_id) REFERENCES Payment(payment_id)
);

INSERT INTO Customer VALUES ('101', 'JAMES', '[email protected]', 'M','Mumbai', '7878445541');
INSERT INTO Customer VALUES ('102', 'ANKITHA', '[email protected]', 'F','Hyderabad', '8799836214');
INSERT INTO Customer VALUES ('103', 'SHEK', '[email protected]', 'M','Mumbai', '9745678767');

INSERT INTO Product VALUES ('1111', '201', 'T-shirt', '1000','50');
INSERT INTO Product VALUES ('1112', '202', 'Jeans', '2000','30');
INSERT INTO Product VALUES ('1113', '203', 'Kurtis', '500','40');

INSERT INTO Seller VALUES ('201', '1111', 'Gurgon', '9743574267');
INSERT INTO Seller VALUES ('202', '1112', 'Delhi', '8747618167');
INSERT INTO Seller VALUES ('203', '1113', 'Surat', '9645653269');

INSERT INTO Order VALUES ('5001', '1111', '13000', '2022-08-24','delivered');
INSERT INTO Order VALUES ('5002', '1112', '11500', '2022-05-14','delivered');
INSERT INTO Order VALUES ('5003', '1113', '2000', '2022-02-10','shipped');

INSERT INTO Payment VALUES ('8001', '101', '5001', '1000200030004000','Hyderabad');
INSERT INTO Payment VALUES ('8002', '102', '5002', '2000300040005000','Mumbai');
INSERT INTO Payment VALUES ('8003', '103', '5003', '3000400050006000','Mumbai');

INSERT INTO View VALUES ('101', '1111', 'Men', 'Low-to-high');
INSERT INTO View VALUES ('102', '1112', 'Woen', 'High-to-low');
INSERT INTO View VALUES ('103', '1113', 'Kids', 'Discount');

INSERT INTO Sell VALUES ('201', '1111');
INSERT INTO Sell VALUES ('202', '1112');
INSERT INTO Sell VALUES ('203', '1113');

INSERT INTO Cart VALUES ('1', '1111');
INSERT INTO Cart VALUES ('2', '1112');
INSERT INTO Cart VALUES ('3', '1113');

INSERT INTO Confirm VALUES ('5001', '8001');
INSERT INTO Confirm VALUES ('5002', '8002');
INSERT INTO Confirm VALUES ('5003', '8003');

INSERT INTO Ship VALUES ('101', '8001');
INSERT INTO Ship VALUES ('102', '8002');
INSERT INTO Ship VALUES ('103', '8003');

END$$

DELIMITER;