Challenge

Project 1

PPavan Kolhe29 May 2023

Entity Relationship Diagram by using draw.io of the ecommerce database

TASK 1 and 2.

-- create a eccomerce database
create database ecommerce;
use ecommerce;

-- CUSTOMER table

create table customers ( cust_id int auto_increment primary key ,
cust_fname varchar(20) not null,
cust_lname varchar(20),
cust_email varchar(20) not null,
cust_passwd varchar(20) not null,
cust_address varchar(150),
cust_phoneno int(11) not null,
cust_dob date,
cust_city varchar(15));

-- CATEGORY table

create table categories ( category_id int(11) primary key,
category_name varchar(25),
category_type varchar(25) );

-- PRODUCT TABLE

create table products ( product_id int primary key,
product_name varchar(50),
product_price int,
product_description varchar(255),
category_id int,
foreign key(category_id) references categories(category_id));

-- SELLER TABLE

create table seller ( seller_id int(11) primary key,
seller_name varchar(30),
seller_mobno int,
product_id int,
foreign key(product_id) references products (product_id));

-- ORDERS TABLE

create table orders ( order_id int(11) primary key,
cust_id int(11),
product_id int(11),
quantity int(10),
price double,
order_date date,
foreign key(cust_id) references customers(cust_id),
foreign key(product_id) references products(product_id) );

-- PAYMENTS TABLE

create table payments(payment_id int primary key,
order_id int,
payment_date date,
foreign key(order_id) references orders(order_id));

-- DELIVER table

create table delivery ( delivery_id int primary key,
cust_id int,
delivery_date date,
foreign key(cust_id) references customers(cust_id));

-- cart table
create table cart( cart_id int primary key,
cust_id int,
foreign key(cust_id) references customers(cust_id));

-- REVIEWS table

create table reviews ( review_id int primary key,
product_id int,
cust_id int,
review_stars int(5),
num_of_rating int(11),
foreign key(product_id) references products(product_id),
foreign key(cust_id) references customers(cust_id) );

TASk 3.

-- 1. WAQ to fetch the details of customers in order of the their city.
select * from customers order by cust_city;

-- 2. WAQ to alter the table product by renaming all the coloumn by preceding them with 'prod_'.
ALTER TABLE products RENAME column product_id to prod_id;
ALTER TABLE products RENAME column product_name to prod_name;
ALTER TABLE products RENAME column product_price to prod_price;
ALTER TABLE products RENAME column product_description to prod_description;

-- 3. WAQ to fetch the details of customers who have purchases at least one product.

SELECT c.cust_id, c.cust_fname, c.cust_lname FROM customers c inner join orders o
on c.cust_id = o.cust_id;

-- 4. WAQ to fetch details of customers who have name starting from A and ending from A
-- and purchased morev than one items

SELECT c.cust_id, c.cust_fname, c.cust_lname FROM customers c inner join orders o
on c.cust_id = o.cust_id AND cust_name like 'A%A';

-- 5. WAQ to all information about the orders which are alredy delivered and
-- has cost more than 10000 and also has been purchase by more than one customers.

SELECT * FROM orders where delivery_status = "done" AND price > 10000 AND
(SELECT count(cust_id) from orders where price > 10000) >1;

-- 6. Write a simple procedure to run all the creation statement, insert statement and alter statement at once

CREATE PROCEDURE ecommerce
AS

BEGIN
create table customers ( cust_id int auto_increment primary key ,
cust_fname varchar(20) not null,
cust_lname varchar(20),
cust_email varchar(20) not null,
cust_passwd varchar(20) not null,
cust_address varchar(150),
cust_phoneno int(11) not null,
cust_dob date,
cust_city varchar(15));