Project 1

The file has been uploaded.

CREATE DATABASE amazon;
USE amazon;

Ans 6:-
DELIMITER $$
CREATE PROCEDURE 'amazon'.'ecamazon'()
BEGIN
CREATE TABLE product(NAME CHAR(50),product_id INT PRIMARY KEY,color VARCHAR(250),seller VARCHAR(255),size INT,available CHAR(50),quantity INT,cost INT,current_discount INT,place_of_originr_for VARCHAR(250),calculations INT,isfeatured CHAR(20),popularityindex CHAR(50),avgratings DECIMAL(10,2),total_ratings DECIMAL(10,2),reviews_for_rating VARCHAR(190),reviews_country VARCHAR(255),IsOnSale VARCHAR(50));
INSERT INTO product VALUES('jeans',201,'black','habitt',30,'yes',1,800,2,'surat',10,'yes','alot',7.60,10.0,'fabricisgood','india','No');
INSERT INTO product VALUES('mobile',202,'purple','mi',0,'yes',1,10000,1,'delhi',1,'yes','no',9.00,10.0,'featuresisgood','india','No');
INSERT INTO product VALUES('shoes',203,'white','nike',5,'yes',2,15000,1,'delhi',1,'yes','no',9.50,10.0,'comfortable','india','No');

CREATE TABLE category(brands VARCHAR(255),product_description VARCHAR(190),tags CHAR(50),related_products_ids INT);
INSERT INTO category VALUES('Zara','prdouctisabout clothing','clothes',10);
INSERT INTO category VALUES('Gucci','prdouct is about handpurse','fashioncategory',12);

CREATE TABLE customer(customer_id INT PRIMARY KEY,prod_quantity INT,gender CHAR(50),dob DATE,city VARCHAR(200),NAME VARCHAR(255),email CHAR(100),delivery_address VARCHAR(255),contacts BIGINT,current_previous_order VARCHAR(200),payment_info CHAR(100));
INSERT INTO customer VALUES(101,2,'F','1999-10-16','narnaul','komal','[email protected]','Moh_salampura',999999999,'jeans','Cash on delivery');
INSERT INTO customer VALUES(102,1,'F','2000-10-26','delhi','tina','[email protected]','Dwarka sector',999999999,'mobile','Credit card');
INSERT INTO customer VALUES(103,3,'F','1990-11-16','delhi','asha','[email protected]','palamsector',99998899,'book','debit card');

CREATE TABLE order1(order_id INT PRIMARY KEY,STATUS VARCHAR(255),deliverydate DATE,orderdate DATE,total_amount INT,total_customer INT,product_id INT REFERENCES product(product_id));
INSERT INTO order1 VALUES(1001,'ordered','1990-11-25','1990-11-13',5000,2,201);
INSERT INTO ORDER1 VALUES(1002,'packed','2000-9-22','2000-9-13',10000,3,202);
INSERT INTO ORDER1 VALUES(1004,'delivered','1999-8-5','1999-8-13',15000,2,203);

CREATE TABLE seller(NAME VARCHAR(200),mobile BIGINT,PASSWORD CHAR(200),category_provided VARCHAR(200));
INSERT INTO seller VALUES('Habitt',1234567,'habitttt','clothes');
INSERT INTO seller VALUES('MI',9087867,'sagarrr','electronics');

CREATE TABLE discount(coupon_codes INT,coupon_code_rule VARCHAR(200));
INSERT INTO discount VALUES(25,"you have to order above 500");
INSERT INTO discount VALUES(20,"If you order above 1000 then you are able to buy one get one coupon");

CREATE TABLE USER(user_id INT,user_name VARCHAR(200),wishlist VARCHAR(50));
INSERT INTO USER VALUES(1000,"seller","book");

Ans 1:-
SELECT * FROM customer ORDER BY city;
Ans 2:-
ALTER TABLE product CHANGE COLUMN NAME prod_name CHAR(50);
ALTER TABLE product CHANGE COLUMN product_id prod_id INT;
ALTER TABLE product CHANGE COLUMN color prod_color VARCHAR(255);
ALTER TABLE product CHANGE COLUMN seller prod_seller VARCHAR(255);
ALTER TABLE product CHANGE COLUMN size prod_size INT;
ALTER TABLE product CHANGE COLUMN available prod_available CHAR(50);

Ans 3:-
SELECT * FROM customer WHERE prod_quantity>1;
Ans 4:-
SELECT NAME FROM customer WHERE NAME LIKE 'A%A' AND prod_quantity>1;
Ans 5:-
SELECT * FROM order1 WHERE STATUS='delivered' AND total_amount>10000 AND total_customer>1;

END$$
DELIMITER;

CALL ecamazon();