Project 1

TASK_1

CREATE TABLE Payment (
payment_i` int(150),
catergory_id int(150),
date` date
);

CREATE TABLE catergories (
catergory_name varchar(150),
catergory_Id int(50),
catergory_type varchar(150),
FOREIGN KEY (`catergory_type`) REFERENCES `catergories`(`catergory_type`)
);

CREATE TABLE shopping_order (
order_id varchar(150),
customer_id int(50),
date date,
<type>,
FOREIGN KEY (customer_id) REFERENCES catergorid(catergory_Id)
);

CREATE TABLE customer (
Customer_name varchar(200),
Customer_id int(150),
Contact_num int(150),
Address varchar(200 ),
FOREIGN KEY (`Customer_id`) REFERENCES `shopping_order`(`customer_id`)
);

CREATE TABLE Seller (
Seller_id int(50),
Product_id int(50),
name varchar(150)
);

CREATE TABLE Deliveris (
Delivery_id varchar(150),
cusromer_id int(50),
Date Date
);

CREATE TABLE Payment Reports (
report_id int(10),
Customer_id int(150),
order_id int/(150),
Product_id int(150),
payment_id int(150),
FOREIGN KEY (order_id) REFERENCES shopping_order(customer_id),
FOREIGN KEY (payment_id) REFERENCES Payment Reports(payment_id),
FOREIGN KEY (Customer_id) REFERENCES customer(Customer_name),
FOREIGN KEY (Product_id) REFERENCES Payment Reports(Product_id)
);

CREATE TABLE Product (
product_id int(150),
product_name varchar(200),
catergory_type varchar(150),
FOREIGN KEY (product_name) REFERENCES Seller(Seller_id),
FOREIGN KEY (product_id) REFERENCES Payment Reports(payment_id)
);

TASK_2

-->
CREATE TABLE coupon_codes (
code VARCHAR(255) NOT NULL PRIMARY KEY,
discount_rule VARCHAR(255) NOT NULL,
discount_percentage DECIMAL(5,2) NOT NULL
);

-->
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL
);

-->
CREATE TABLE shipments (
shipment_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
shipment_date DATE NOT NULL,
shipment_address VARCHAR(255) NOT NULL
);

CREATE TABLE invoices (
invoice_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
invoice_date DATE NOT NULL,
invoice_amount DECIMAL(10, 2) NOT NULL
);

-->
CREATE TABLE replacements (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
replacement_status ENUM('requested', 'approved', 'shipped', 'received', 'rejected') NOT NULL,
requested_date DATE NOT NULL,
updated_date DATE
);

-->
CREATE TABLE product_reviews (
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
customer_id INT NOT NULL,
rating INT NOT NULL,
review_text TEXT NOT NULL,
review_date DATE NOT NULL
);

-->
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
color VARCHAR(255),
seller_id INT NOT NULL,
sizes VARCHAR(255),
quantity INT NOT NULL,
cost DECIMAL(10, 2) NOT NULL,
discount DECIMAL(10, 2),
delivery_origin VARCHAR(255),
is_featured BOOLEAN NOT NULL DEFAULT 0,
popularity_index INT,
avg_rating DECIMAL(10, 2),
total_ratings INT,
review_text TEXT,
review_country VARCHAR(255),
is_on_sale BOOLEAN NOT NULL DEFAULT 0,
categories TEXT,
brand VARCHAR(255),
image TEXT,
description TEXT,
tags TEXT,
related_product_ids TEXT
);

-->
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
gender ENUM('male', 'female', 'other'),
dob DATE,
city VARCHAR(255),
password VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
delivery_addresses TEXT,
contacts TEXT,
current_orders TEXT,
prev_orders TEXT,
payment_info TEXT
);

-->
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
status ENUM('cancelled', 'completed', 'ordered', 'packed', 'shipped', 'delivered'),
delivery_expectation DATETIME,
delivery_date DATETIME,
order_date DATETIME NOT NULL
);

-->
CREATE TABLE sellers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
mobile_no VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
categories TEXT
);

TASK-3

1.
SELECT * FROM customers
ORDER BY city;

2.
ALTER TABLE products
RENAME COLUMN name TO prod_name,
RENAME COLUMN color TO prod_color,
RENAME COLUMN seller TO prod_seller,
RENAME COLUMN sizes_available TO prod_sizes_available,
RENAME COLUMN quantity_available TO prod_quantity_available,
RENAME COLUMN cost TO prod_cost,
RENAME COLUMN current_discount TO prod_current_discount,
RENAME COLUMN place_of_origin_for_delivery TO prod_place_of_origin_for_delivery,
RENAME COLUMN is_featured TO prod_is_featured,
RENAME COLUMN popularity_index TO prod_popularity_index,
RENAME COLUMN avg_ratings TO prod_avg_ratings,
RENAME COLUMN total_ratings TO prod_total_ratings,
RENAME COLUMN is_on_sale TO prod_is_on_sale,
RENAME COLUMN categories TO prod_categories,
RENAME COLUMN brands TO prod_brands,
RENAME COLUMN images TO prod_images,
RENAME COLUMN description TO prod_description,
RENAME COLUMN tags TO prod_tags,
RENAME COLUMN related_products_ids TO prod_related_products_ids;

3.
SELECT * FROM customers
WHERE EXISTS (
SELECT * FROM orders
WHERE customers.id = orders.customer_id
);

4.
SELECT * FROM customers
WHERE name LIKE 'A%' AND name LIKE '%A' AND
EXISTS (
SELECT customer_id, COUNT(*)
FROM orders
WHERE customers.id = orders.customer_id
GROUP BY customer_id
HAVING COUNT(*) > 1
);

5.
SELECT * FROM orders
WHERE status = 'delivered' AND cost > 10000 AND
EXISTS (
SELECT order_id, COUNT(*)
FROM order_items
WHERE orders.id = order_items.order_id
GROUP BY order_id
HAVING COUNT(*) > 1
);

6.
DELIMITER $$

CREATE PROCEDURE run_all_sql_statements()