Challenge

Project 1

SSk Injamul Hossain20 Feb 2023

CREATE DATABASE Project_1;
USE Project_1;

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)
);

CREATE TABLE customer_paymentinfo(
customer_id INT REFERENCES customer(customer_id),
card_no INT,
cvv INT,
card_vaidity DATE
);

INSERT INTO customer VALUES (1, 'Rejaul Karim','abc@123', 20/05/1995, 1, 'Chendhre', 1234567890, 'Alibag', '[email protected]'),
(2, 'Tajun Nisha','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 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 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 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
);

TRUNCATE TABLE orders;

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);

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 customer AS c ON (c.customer_id = o.customer_id) INNER JOIN product AS p ON (o.product_id = p.product_id);

SELECT * FROM view_orders;

DROP VIEW view_orders;

DROP TABLE orders;

SELECT * FROM orders;

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)
);

CREATE TABLE catalogue(
catalogue_id INT PRIMARY KEY,
seller_id INT REFERENCES seller(seller_id),
product_id INT REFERENCES product(product_id),
product_quantity INT
);

SELECT * FROM catalogue;

CREATE VIEW seller_catalogue AS
SELECT cat.seller_id AS 'Seller_id',
s.seller_name AS 'Seller Name',
s.seller_business AS 'Seller Business',
p.product_id AS 'Product ID',
p.product_name AS 'Product Name',
cat.product_quantity AS 'Product Quantity',
p.product_cost AS 'Producr Cost'
FROM catalogue AS cat INNER JOIN seller AS s ON s.seller_id = cat.seller_id INNER JOIN product AS p ON p.product_id = cat.product_id;

SELECT * FROM seller_catalogue;

DROP TABLE catalogue;

CREATE TABLE seller_categories(
seller_id INT REFERENCES seller(seller_id),
cat_id INT REFERENCES categories(cat_id)
);

DROP TABLE seller;
SELECT * FROM seller;

DROP TABLE seller;
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 TABLE product(
product_id INT PRIMARY KEY,
product_name CHAR(50),
product_model CHAR(50),
product_color CHAR(50),
product_size CHAR(50),
product_cost INT NOT NULL,
product_discount INT,
product_isOnSale BIT
);

CREATE TABLE tags(
tag_id INT,
tag_name CHAR(50)
);

CREATE TABLE prod_tag(
product_id INT REFERENCES product(product_id),
tag_id INT REFERENCES tag(tag_id)
);

CREATE TABLE reviewer(
reviewer_id INT PRIMARY KEY,
customer_id INT REFERENCES customer(customer_id)
);

DROP TABLE reviewer;

CREATE TABLE reviews_ratings(
review_id INT PRIMARY KEY,
reviewer_id INT REFERENCES reviewer(reviewer_id),
review_detail VARCHAR(255),
rating INT,
product_id INT REFERENCES product(product_id)
);

CREATE VIEW view_reviews_ratings AS
SELECT
rr.review_id AS 'Review ID',
rr.reviewer_id AS 'ReviewerID',
c.customer_name AS 'Reviewer Name',
rr.product_id AS 'Product ID',
p.product_name AS 'Product Name',
p.product_model AS 'Product Model',
rr.review_detail AS 'Review Detail',
rr.rating AS 'Ratings',
SUM(rr.rating)/COUNT(rr.rating) AS 'Average Rating'
FROM reviews_ratings AS rr INNER JOIN reviewer AS r ON (rr.reviewer_id = r.reviewer_id) INNER JOIN
customer AS c ON (r.customer_id = c.customer_id) INNER JOIN product AS p ON (rr.product_id = p.product_id);

SELECT * FROM view_reviews_ratings;

DROP TABLE reviews_ratings;

SELECT * FROM reviews_ratings;

SELECT * FROM product;

DROP TABLE product;

CREATE TABLE categories(
cat_id INT PRIMARY KEY,
cat_name CHAR(50)
);

SELECT * FROM categories;

INSERT INTO categories VALUES(150, 'Home Appliances'),(250, 'Computers, Laptops and Mobiles'),
(350, 'Beauty and Health'),(430, 'Fasion'),(550, 'Books'),(630, 'Baby Products'),(750, 'Games and Consoles'),
(820, 'Sports and fitness'),(910, 'TV and other Electronics'),(1050, 'Car, Motorbikes and Industrial');

INSERT INTO product (product_id, product_name, product_model, product_color, product_cost) VALUES(101, 'Food Processer', 'C101', 'Black', 5500),
(102, 'Ceiling Fan', 'C102', 'Black', 3000),(201,'Laptop', 'C201', 'Black', 100000),(202, 'SmartPhone', 'C202', 'White', 50000);

INSERT INTO product (product_id, product_name, product_model, product_cost) VALUES (301, 'Perfume', 'C301', 900),
(302, 'Shaving Cream', 'C302', 600);

INSERT INTO product (product_id, product_name, product_model, product_color, product_size, product_cost)
VALUES (401, 'Shirt', 'C401', 'White', 'M', 1500),(402, 'Pant', 'C402', 'Black', '30', 2000);

SELECT * FROM product;

INSERT INTO product (product_id, product_name, product_model, product_cost) VALUES (501, 'The Lord of the Rings Set', 'C501', 1500),
(502, 'Harry Potter Set', 'C502', 3500),(601, 'Baby Shampoo', 'C601', 900),(602, 'Daipers', 'C602', 400);

INSERT INTO product (product_id, product_name, product_model, product_cost) VALUES (701, 'Pokemon Scarlet','C701', 5500),
(702, 'Pokemon Violet', 'C702', 4500);

INSERT INTO product (product_id, product_name, product_model, product_cost) VALUES(801, 'Cricket Bat','C801', 4500),
(802, 'Football', 'C802', 2000);

SELECT * FROM customer ORDER BY customer_city;

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;

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;

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

Insert into customer
INSERT INTO customer VALUES (1, 'Rejaul Karim','abc@123', 20/05/1995, 1, 'Chendhre', 1234567890, 'Alibag', '[email protected]'),(2, 'Tajun Nisha','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
);

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 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),
product_quantity INT
);

view to see seller catalogue
CREATE VIEW seller_catalogue AS
SELECT cat.seller_id AS 'Seller_id',
s.seller_name AS 'Seller Name',
s.seller_business AS 'Seller Business',
p.product_id AS 'Product ID',
p.product_name AS 'Product Name',
cat.product_quantity AS 'Product Quantity',
p.product_cost AS 'Producr Cost'
FROM catalogue AS cat INNER JOIN seller AS s ON s.seller_id = cat.seller_id INNER JOIN product AS p ON p.product_id = cat.product_id;

-- PRODUCTS

create products table
CREATE TABLE product(
product_id INT PRIMARY KEY,
product_name CHAR(50),
product_model CHAR(50),
product_color CHAR(50),
product_size CHAR(50),
product_cost INT NOT NULL,
product_discount INT,
product_isOnSale BIT
);

create product tags table
CREATE TABLE tags(
tag_id INT,
tag_name CHAR(50)
);

INSERT INTO product (product_id, product_name, product_model, product_color, product_cost) VALUES(101, 'Food Processer', 'C101', 'Black', 5500),(102, 'Ceiling Fan', 'C102', 'Black', 3000),(201,'Laptop', 'C201', 'Black', 100000),(202, 'SmartPhone', 'C202', 'White', 50000);

INSERT INTO product (product_id, product_name, product_model, product_cost) VALUES (301, 'Perfume', 'C301', 900),
(302, 'Shaving Cream', 'C302', 600);

INSERT INTO product (product_id, product_name, product_model, product_color, product_size, product_cost)
VALUES (401, 'Shirt', 'C401', 'White', 'M', 1500),(402, 'Pant', 'C402', 'Black', '30', 2000);

SELECT * FROM product;

INSERT INTO product (product_id, product_name, product_model, product_cost) VALUES (701, 'Pokemon Scarlet','C701', 5500),
(702, 'Pokemon Violet', 'C702', 4500);

INSERT INTO product (product_id, product_name, product_model, product_cost) VALUES(801, 'Cricket Bat','C801', 4500),
(802, 'Football', 'C802', 2000);

create reviewer table
CREATE TABLE reviewer(
reviewer_id INT PRIMARY KEY,
customer_id INT REFERENCES customer(customer_id)
);

create reviews and ratings table
CREATE TABLE reviews_ratings(
review_id INT PRIMARY KEY,
reviewer_id INT REFERENCES reviewer(reviewer_id),
review_detail VARCHAR(255),
rating INT,
product_id INT REFERENCES product(product_id)
);

create view to see ratings and reviews with average rating
CREATE VIEW view_reviews_ratings AS
SELECT
rr.review_id AS 'Review ID',
rr.reviewer_id AS 'ReviewerID',
c.customer_name AS 'Reviewer Name',
rr.product_id AS 'Product ID',
p.product_name AS 'Product Name',
p.product_model AS 'Product Model',
rr.review_detail AS 'Review Detail',
rr.rating AS 'Ratings',
SUM(rr.rating)/COUNT(rr.rating) AS 'Average Rating'
FROM reviews_ratings AS rr INNER JOIN reviewer AS r ON (rr.reviewer_id = r.reviewer_id) INNER JOIN customer AS c ON (r.customer_id = c.customer_id) INNER JOIN product AS p ON (rr.product_id = p.product_id);

create categories table
CREATE TABLE categories(
cat_id INT PRIMARY KEY,
cat_name CHAR(50)
);

insert values into categories table
INSERT INTO categories VALUES (100, 'Home Appliances'),
(200, 'Computers, Laptops and Mobiles'),
(300, 'Beauty and Health'),
(400, 'Fasion'),
(500, 'Books'),
(600, 'Baby Products'),
(700, 'Games and Consoles'),
(800, 'Sports and fitness'),
(900, 'TV and other Electronics'),
(1000, 'Car, Motorbikes and Industrial');

END $$
DELIMITER;

Project 1

Ship work like this
in your own course.

Talk to a career counsellor

Ship work like thisin your own course.

Talk to a career counsellor

Ship work like this
in your own course.