Project 1

TASK 1&2:

CREATE TABLE coupon_codes (
id INT AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(20) NOT NULL UNIQUE,
discount_rule ENUM('percentage', 'flat_amount') NOT NULL,
discount_amount DECIMAL(10,2) NOT NULL
);

CREATE TABLE user_wishlist (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES customers(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);

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,
review_date DATE NOT NULL,
FOREIGN KEY (product_id) REFERENCES products(id),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
color VARCHAR(50),
seller VARCHAR(255),
quantity_available INT NOT NULL,
cost DECIMAL(10,2) NOT NULL,
discount DECIMAL(10,2) NOT NULL,
place_of_origin VARCHAR(255),
is_featured TINYINT(1),
popularity_index INT,
avg_rating DECIMAL(3,2),
total_ratings INT,
is_on_sale TINYINT(1),
description TEXT,
category_id INT NOT NULL,
brand_id INT NOT NULL,
image_path VARCHAR(255),
related_product_ids VARCHAR(255),
FOREIGN KEY (category_id) REFERENCES product_categories(id),
FOREIGN KEY (brand_id) REFERENCES product_brands(id)
);

CREATE TABLE product_categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE product_brands (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
gender ENUM('Male', 'Female', 'Other') NOT NULL,
dob DATE,
city VARCHAR(255),
password VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
payment_info TEXT,
delivery_address TEXT,
contact_number VARCHAR(20)
);

CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATETIME NOT NULL,
order_status ENUM('cancelled', 'completed', 'ordered', 'packed', 'shipped', 'delivered'),
order_total DECIMAL(10,2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_shipments (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
shipment_date DATETIME NOT NULL,
shipping_address TEXT NOT NULL,
delivery_date DATETIME NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id)
);

CREATE TABLE order_invoices (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
invoice_number VARCHAR(255) NOT NULL,
invoice_date DATETIME NOT NULL,
invoice_total DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id)

);

TASK3:

[11:33 PM, 2/24/2023] Kavi: Task 3:

1.

SELECT *
FROM Customers
ORDER BY City DESC;

2.

ALTER TABLE product
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 order_shipments TO prod_order_shipments,
RENAME COLUMN invoices TO prod_invoices,
RENAME COLUMN replacements TO prod_replacements,
RENAME COLUMN status TO prod_status,
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 calculations TO prod_calculations,
RENAME COLUMN isFeatured TO prod_isFeatured,
RENAME COLUMN popularityIndex TO prod_popularityIndex,
RENAME COLUMN avgratings TO prod_avgratings,
RENAME COLUMN totalratings TO prod_totalratings,
RENAME COLUMN reviews_for_ratings TO prod_reviews_for_ratings,
RENAME COLUMN review_country TO prod_review_country,
RENAME COLUMN isOnSale TO prod_isOnSale,
RENAME COLUMN categories TO prod_categories,
RENAME COLUMN brands TO prod_brands,
RENAME COLUMN images TO prod_images,
RENAME COLUMN questions_with_answers_about_the_products TO prod_questions_with_answers_about_the_products,
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 Customer
WHERE customer_id IN (
SELECT DISTINCT customer_id FROM Orders
)

4.

SELECT c.*
FROM Customer c
INNER JOIN Orders o ON c.customer_id = o.customer_id
INNER JOIN OrderDetails od ON o.order_id = od.order_id
WHERE c.name LIKE 'A%a' OR c.name LIKE 'a%A'
GROUP BY c.customer_id
HAVING COUNT(od.product_id) > 1;

5.

SELECT o.order_id, o.order_date, o.total_cost, o.order_status, COUNT(DISTINCT c.customer_id) as num_customers
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'delivered' AND o.total_cost > 10000
GROUP BY o.order_id
HAVING num_customers > 1;

6.CREATE PROCEDURE RunAllStatements
AS
BEGIN