Project 1

Task 1

CREATE TABLE product(
prod_id INT,
NAME VARCHAR(255),
color CHAR(50),
seller CHAR(50),
sizes_available CHAR(10),
quantity_available INT,
cost DECIMAL(6,2),
place_of_orgin_for_delivery VARCHAR(255),
calculations VARCHAR(255),
isFeatured CHAR(50),
popularityIndex INT,
avgratings INT,
totalratings INT,
reviews_for_ratings INT,
review_country CHAR(50),
isOnSale CHAR(10),
categories CHAR(10),
brands CHAR(50),
images CHAR(10),
questions_with_answers_about_the VARCHAR(255),
products VARCHAR(255),
description VARCHAR(255),
tags CHAR(50),
related_products_ids CHAR(50),
PRIMARY KEY(prod_id)
);

CREATE TABLE customer(
cust_id INT,
gender CHAR(50),
dob DATE,
city CHAR(50),
pw INT,
NAME CHAR(50),
email CHAR(50),
delivery_address VARCHAR(255),
contacts INT,
current_and_previous_orders VARCHAR(255),
payment_info_saved CHAR(50),
PRIMARY KEY(cust_id),
FOREIGN KEY product REFERENCES product(prod_id)
);

CREATE TABLE orders(
ord_id INT,
STATUS CHAR(50),
deliveryexpectation DATE,
orderdate DATE,
PRIMARY KEY(ord_id)
);

CREATE TABLE seller(
sell_id INT,
NAME CHAR(50),
mobile_no INT,
PASSWORD CHAR(50),
categories_of_product_provided VARCHAR(255),
PRIMARY KEY(sell_id)
);

INSERT INTO product VALUES (101,'Football','purple','Surya','Medium',25,4999.00,'India','Over 50Million+','Mobile application',5,8,10,26,'German','Yes','Sports','one8','N/A','What is the material stiched?','one8','good quality built with butyl rubber bladder','Indian merchandise','50,40,20');
INSERT INTO product VALUES (102,'Cricket bat','white','Sudhan','Large',12,499.00,'United Kingdom','Over 10Million+','Mobile application',15,9,10,34,'India','Yes','Sports','Reebok','N/A','Does the bat can handle cork balls?','Kukaboora','Yes, it is made up of fine wooden pieces','England','46,28,61');
INSERT INTO product VALUES (103,'Cork ball','red','Sriram','6',116,1899.00,'India','8000+','Mobile application',14,7,10,06,'Bangladesh','Yes','Sports','DLF','N/A','What is the weight of the ball?','DLF','155 grams','ICC world cricket','204,58,37');

DESCRIBE product;
SELECT * FROM product;

INSERT INTO customer VALUES(201,'Male','2022/11/21','Chennai',1,'Ajith','[email protected]','No-4,christ street,anna nagar,chennai-47',822030,'Football','Debit card');
INSERT INTO customer VALUES(202,'Male','2022/11/20','Mumbai',2,'Bala','[email protected]','D-6,edwards road, gandhi road, mumbai-66',824846,'G-shock wrist watch & Cricket bat','Credit card');
INSERT INTO customer VALUES(203,'Male','2022/11/19','Hyderabad',3,'Kumar','[email protected]','D-1,mount road, fahiz road,hyderabad-35',956123,'Axe perfume & Corck ball','Cash');

SELECT * FROM customer;

INSERT INTO seller VALUES(301,'Surya',428365,'Surya123','sports utilizations');
INSERT INTO seller VALUES(302,'Sudhan',428369,'Sudhan123','sports utilizations');
INSERT INTO seller VALUES(303,'Sriram',428366,'Sriram123','sports utilizations');

SELECT * FROM seller;

INSERT INTO orders VALUES(401,'shipped','2022/11/28','2022/11/21');
INSERT INTO orders VALUES(402,'ordered','2022/11/26','2022/11/20');
INSERT INTO orders VALUES(403,'cancelled','2022/11/25','2022/11/19');

SELECT * FROM orders;

-----------------------

TASK 3 TASK-3

1) SELECT * FROM customers ORDER BY city;

2) ALTER TABLE product RENAME COLUMN prod_;

3) SELECT * FROM customer WHERE product >=1;

4) SELECT * FROM customer WHERE NAME START LIKE "%A%" , END LIKE %A% AND item >=1;

5) SELECT * FROM orders WHERE STATUS LIKE "%delivered%" , cost >= 10000 AND customer >=1;

6