Project 1

Task1:

ER diagram has been attached

Task 2:

-- Table for storing customers
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
gender VARCHAR(10),
date_of_birth DATE,
city VARCHAR(100),
password VARCHAR(100)
);

-- Table for storing delivery addresses
CREATE TABLE delivery_addresses (
address_id INT PRIMARY KEY,
customer_id INT,
address VARCHAR(200),
contact_number VARCHAR(20),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Table for storing payment information
CREATE TABLE payment_info (
payment_id INT PRIMARY KEY,
customer_id INT,
payment_method VARCHAR(100),
card_number VARCHAR(16),
expiry_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Table for storing products
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100),
color VARCHAR(50),
seller VARCHAR(100),
sizes_available VARCHAR(100),
quantity_available INT,
cost DECIMAL(10, 2),
current_discount DECIMAL(5, 2),
place_of_origin VARCHAR(100),
is_featured BOOLEAN,
popularity_index INT,
average_rating DECIMAL(3, 2),
total_ratings INT,
is_on_sale BOOLEAN,
categories VARCHAR(200),
brands VARCHAR(200),
images VARCHAR(200),
questions_answers JSON,
description TEXT,
tags VARCHAR(200),
related_products_ids VARCHAR(200)
);

-- Table for storing coupon codes
CREATE TABLE coupon_codes (
coupon_id INT PRIMARY KEY,
discount_rule VARCHAR(100),
percentage DECIMAL(5, 2)
);

-- Table for storing orders
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10, 2),
coupon_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (coupon_id) REFERENCES coupon_codes(coupon_id)
);

-- Table for storing shipments
CREATE TABLE shipments (
shipment_id INT PRIMARY KEY,
order_id INT,
shipment_date DATE,
tracking_number VARCHAR(100),
status VARCHAR(100),
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- Table for storing invoices
CREATE TABLE invoices (
invoice_id INT PRIMARY KEY,
order_id INT,
invoice_date DATE,
amount_due DECIMAL(10, 2),
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- Table for storing wishlists
CREATE TABLE wishlists (
wishlist_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Table for storing replacements
CREATE TABLE replacements (
replacement_id INT PRIMARY KEY,
order_id INT,
product_id INT,
status VARCHAR(100),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Table for storing reviews
CREATE TABLE reviews (
review_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
rating DECIMAL(3, 2),
review TEXT,
country VARCHAR(100),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Table for storing questions and answers
CREATE TABLE questions_answers (
qa_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
question TEXT,
answer TEXT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Task 3:

WAQ to fetch the details of the customers in the order of their city

SELECT *
FROM customers
ORDER BY city;

WAQ to alter the table product by renaming all the columns by preceding them with 'prod_'

ALTER TABLE product
RENAME COLUMN id TO prod_id,
RENAME COLUMN name TO prod_name,
RENAME COLUMN price TO prod_price;

WAQ to fetch the details of customer who have purchased at least one product

SELECT *
FROM customers
WHERE customer_id IN (
SELECT DISTINCT customer_id
FROM orders
WHERE customer_id IS NOT NULL
);

WAQ to fetch the details of the customer who have name starting from A and ending from A and has purchased more than one item.

SELECT *
FROM customers
WHERE name LIKE 'A%A' AND customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1
);

WAQ to all the information about the orders which are already delivered and has cost more than 10000 and also has been purchased by more than 1 customer

SELECT *
FROM orders
WHERE status = 'delivered'
AND cost > 10000
AND product_id IN (
SELECT product_id
FROM orders
GROUP BY product_id
HAVING COUNT(DISTINCT customer_id) > 1
);

Write a simple procedure to run all the table creation statements, insert statements

DELIMITER //

CREATE PROCEDURE RunTableCreationAndInserts()
BEGIN
-- Table creation statements
CREATE TABLE IF NOT EXISTS customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
city VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS products (
product_id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10, 2)
);

CREATE TABLE IF NOT EXISTS orders (
order_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
status VARCHAR(50),
cost DECIMAL(10, 2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Insert statements
INSERT INTO customers (customer_id, name, city)
VALUES (1, 'John Doe', 'New York'),
(2, 'Jane Smith', 'Los Angeles');

INSERT INTO products (product_id, name, price)
VALUES (1, 'Widget A', 10.99),
(2, 'Widget B', 19.99);

INSERT INTO orders (order_id, customer_id, product_id, status, cost)
VALUES (1, 1, 1, 'delivered', 10.99),
(2, 2, 2, 'delivered', 19.99);
END //

DELIMITER ;

CALL RunTableCreationAndInserts();