Project 1

TASK:1

CREATE TABLE customer(
cust_id INT,
cust_name CHAR(50),
email CHAR(50),
PASSWORD CHAR(50),
phone INT,
PRIMARY KEY(cust_id)
);

INSERT INTO customer VALUES(111,'John','[email protected]','1234',95432);
INSERT INTO customer VALUES(112,'Anitha','[email protected]','4321',95553);
INSERT INTO customer VALUES(113,'Robert','[email protected]','1258',99731);
INSERT INTO customer VALUES(114,'Alena','[email protected]','1298',95111);

CREATE TABLE seller(
sell_id INT,
sell_name CHAR(50),
phone INT,
address VARCHAR(255),
PRIMARY KEY(sell_id)
);

INSERT INTO seller VALUES(222,'Raven',97668,'Chennai'),
(223,'Harish',77637,'Bangalore'),
(224,'Raj',65432,'Coimbatore'),
(225,'Ram',90007,'Trichy');

CREATE TABLE courrier(
cour_id INT,
cour_name CHAR(50),
phone INT,
PRIMARY KEY(cour_id)
);

INSERT INTO courrier VALUES(333,'Halen',00023),
(334,'Amir',00024),
(335,'Bala',00025),
(336,'Abdul',00026);

CREATE TABLE category(
cat_id INT,
cat_name CHAR(50),
PRIMARY KEY(cat_id)
);

INSERT INTO category VALUES(444,'Iphone'),
(445,'Shoes'),
(446,'Boat'),
(447,'MuscleBlaze');

CREATE TABLE product(
prod_id INT,
prod_name CHAR(50),
price INT,
stock INT,
sell_id INT,
cat_id INT,
PRIMARY KEY(prod_id),
FOREIGN KEY(sell_id) REFERENCES seller(sell_id),
FOREIGN KEY(cat_id) REFERENCES category(cat_id)
);

INSERT INTO product VALUES(555,'Mobile',20000,10,222,444),
(556,'Footwear',1000,20,223,445),
(557,'Watches',2000,15,224,446),
(558,'Proteins',3000,10,225,447);

CREATE TABLE orders(
ord_id INT,
ord_date DATE,
delivery_status CHAR(50),
cust_id INT,
prod_id INT,
cour_id INT,
PRIMARY KEY(ord_id),
FOREIGN KEY(cust_id) REFERENCES customer(cust_id),
FOREIGN KEY(prod_id) REFERENCES product(prod_id),
FOREIGN KEY(cour_id) REFERENCES courrier(cour_id)
);

INSERT INTO orders VALUES(666,'2022/07/07','Processing',111,555,333),
(667,'2021/06/05','Shipped',112,556,334),
(668,'2020/12/21','Delivered',113,557,335),
(669,'2021/11/11','Processing',114,558,336);

TASK:3

1.
select city from customer order by city;

2.
alter table product rename name to prod_name;

3.
select cust_id from orders
where ord_id >1;

4.
select cust_name from customer
where cust_name like A%;

5.
select cust_name from orders
where price >10000;

6.
create procedure `ecommerce`.`ecAmazon`()
BEGIN
CREATE TABLE customer(
cust_id INT,
cust_name CHAR(50),
email CHAR(50),
PASSWORD CHAR(50),
phone INT,
PRIMARY KEY(cust_id)
);
INSERT INTO customer VALUES(111,'John','[email protected]','1234',95432);
INSERT INTO customer VALUES(112,'Anitha','[email protected]','4321',95553);
INSERT INTO customer VALUES(113,'Robert','[email protected]','1258',99731);
INSERT INTO customer VALUES(114,'Alena','[email protected]','1298',95111);

ALTER TABLE customer
ADD city char(50);

ALTER TABLE customer
DROP COLUMN city;

ALTER TABLE customer
ALTER COLUMN cust_name varchar(255);

END$$
DELIMITER;