Project 1

The queries can also be found in the atached zip file along with the ER Diagram.

-- Q1.
SELECT * FROM customer ORDER BY customer_city;

-- Q2.
ALTER TABLE product CHANGE COLUMN product_id prod_id INT;
ALTER TABLE product CHANGE COLUMN product_name prod_name CHAR(50);
ALTER TABLE product CHANGE COLUMN product_model prod_model CHAR(50);
ALTER TABLE product CHANGE COLUMN product_color prod_color CHAR(50);
ALTER TABLE product CHANGE COLUMN product_size prod_size CHAR(50);
ALTER TABLE product CHANGE COLUMN product_cost prod_cost INT;
ALTER TABLE product CHANGE COLUMN product_discount prod_discount INT;
ALTER TABLE product CHANGE COLUMN product_isOnSale prod_isOnSale BIT;

-- select * from customer as c inner join orders as o on o.customer_id = c.customer_id;

-- Q3.
SELECT
c.customer_id AS 'CustomerID',
c.customer_name AS 'Customer Name',
c.is_premium AS 'Primium',
c.customer_contactno AS 'Contact Number',
c.customer_email AS 'Email',
c.customer_address AS 'Customer Address'
FROM customer AS c INNER JOIN orders AS o ON o.customer_id = c.customer_id;

-- Q4.
SELECT
c.customer_id AS 'CustomerID',
c.customer_name AS 'Customer Name',
c.is_premium AS 'Primium',
c.customer_contactno AS 'Contact Number',
c.customer_email AS 'Email',
c.customer_address AS 'Customer Address'
FROM customer AS c INNER JOIN orders AS o ON o.customer_id = c.customer_id WHERE (c.customer_name LIKE 'a%' OR c.customer_name LIKE '%a');

-- Q5.
SELECT
o.order_id AS 'OrderId',
p.product_name AS 'Product Name',
p.product_model AS 'Product Model',
p.product_color AS 'Product Color',
p.product_cost AS 'Cost per Item',
COUNT(p.product_id) AS 'TimesOrdered'
FROM orders AS o INNER JOIN product AS p ON (o.product_id = p.product_id) GROUP BY p.product_id HAVING COUNT(p.product_id) > 1 AND p.product_cost > 10000;

DELIMITER $$

CREATE PROCEDURE universal_creator()
BEGIN

-- CUSTOMER

-- create customer table

CREATE TABLE customer(
customer_id INT PRIMARY KEY,
customer_name CHAR(50),
customer_password CHAR(50),
customer_dob DATE,
is_premium BIT,
customer_address VARCHAR(255),
customer_contactno BIGINT,
customer_city CHAR(50),
customer_email CHAR(50)
);

-- Insert into customer
INSERT INTO customer VALUES (1, 'Shubham Barwad','abc@123', 20/05/1995, 1, 'Chendhre', 1234567890, 'Alibag', '[email protected]'),
(2, 'Nishi Deshmukh','abc@123', 05/09/1996, 0, 'RIL Township', 2345678901, 'Nagothane', '[email protected]'),
(3, 'Ankush Saluja','abc@123', 29/12/1993, 0, 'Roadpali', 5678901234, 'Kalamboli','[email protected]');

-- create customer cart
CREATE TABLE cart(
cart_item_id INT PRIMARY KEY,
cart_item_qty INT,
product_id INT REFERENCES product(product_id),
customer_id INT REFERENCES customer(customer_id)
);

-- insert into cart
INSERT INTO cart VALUES (1, 2, 102, 1),
(2, 1, 201, 1),
(3, 1, 202, 1),
(4, 1, 501, 1),
(5, 1, 401, 2),
(6, 1, 801, 2),
(7, 1, 601, 2),
(8, 1, 502, 2),
(9, 1, 402, 3),
(10, 1, 201, 3),
(11, 1, 701, 3),
(12, 1, 802, 3);

-- create customer payment info table
CREATE TABLE customer_paymentinfo(
customer_id INT REFERENCES customer(customer_id),
card_no INT,
card_vaidity DATE
);

-- create order table
CREATE TABLE orders(
order_id INT PRIMARY KEY,
cart_item_id INT REFERENCES cart(cart_item_id),
product_id INT REFERENCES product(product_id),
customer_id INT REFERENCES customer(customer_id),
order_qty INT,
order_status VARCHAR(255),
order_date DATE,
order_deliverydate DATE
);

-- Insert into orders table
INSERT INTO orders (order_id, product_id, customer_id, order_qty, order_status, order_date, order_deliverydate) VALUES (1, 201, 1, 1, 'Delivered', 19/11/2022, 21/11/2022),
(2, 501, 1, 1, 'Dispatched', 20/11/2022, 23/11/2022),
(3, 601, 2, 1, 'Undispatched', 20/11/2022, 25/11/2022),
(4, 702, 2, 1, 'Cancelled', 17/11/2022, NULL),
(5, 201, 2, 1, 'Delivered', 19/11/2022, 21/11/2022),
(6, 501, 2, 1, 'Dispatched', 20/11/2022, 23/11/2022);

-- view to see all order of specific customer order
CREATE VIEW view_orders AS
SELECT
o.order_id AS 'OrderId',
p.product_name AS 'Product Name',
p.product_model AS 'Product Model',
p.product_size AS 'Product Size',
p.product_color AS 'Product Color',
p.product_cost AS 'Cost per Item',
o.order_qty AS 'Order Quantity',
p.product_cost*o.order_qty AS 'Total Cost',
o.order_status AS 'Order Status',
o.order_deliverydate AS 'Expected Delivery'
FROM orders AS o INNER JOIN cart AS c ON (c.cart_item_id = o.cart_item_id) INNER JOIN product AS p ON (c.product_id = p.product_id);

-- SELLER

-- create seller table
CREATE TABLE seller(
seller_id INT PRIMARY KEY,
seller_name CHAR(50),
seller_password CHAR(50),
seller_mobileno BIGINT,
seller_business VARCHAR(255),
seller_address VARCHAR(255),
seller_city CHAR(50)
);

-- insert seller info
INSERT INTO seller VALUES (1, 'Jethalal Gada','abc123', 6789123450, 'Gada Electronics', 'Andheri', 'Mumbai'),
(2, 'Ankur Kumar','abc123', 7890123456, 'ABC Sprots and Fasion', 'Connaught Place','Delhi');

-- create seller catalogue
CREATE TABLE catalogue(
catalogue_id INT PRIMARY KEY,
seller_id INT REFERENCES seller(seller_id),
product_id INT REFERENCES product(product_id),