Challenge

Project 1

MMurali Selvaraj23 Feb 2023

ER Diagram for my Ecommerce website

Create, insert queries:

-- creating Ecommerce database
create database Ecommerce;

use Ecommerce;

-- creating customer table
create table customers(
custID varchar(10) primary key,
Name varchar(50),
Dob date,
Email varchar(100),
Password varchar(30),
Phone bigint,
Address varchar(200),
City varchar(50)
);

-- creating seller table
create table sellers(
seller_id varchar(10) primary key,
name varchar(50),
email varchar(100),
password varchar(50),
address varchar(200),
phone bigint
);

-- creating category table
create table categories(
cat_id int primary key,
cat_name varchar(50)
);

-- creating product table
create table products(
prod_id varchar(10),
name varchar(50),
color varchar(10),
quantity int,
price int,
cat_id int,
seller_id varchar(10),
foreign key(seller_id) references sellers(seller_id),
foreign key(cat_id) references categories(cat_id)
);

alter table products
add primary key(prod_id);

-- creating cart table
create table cart(
cartID int primary key
);

-- creating cart_item table
create table cart_item(
cart_item_id int primary key,
prod_id varchar(10),
quantity int,
total_amount int,
cartID int,
foreign key(cartID) references cart(cartID),
foreign key(prod_id) references products(prod_id)
);

-- creating orders table
create table orders(
orderID int primary key,
orderDate date,
total_amount int,
delivery_date date,
custID varchar(10),
foreign key(custID) references customers(custID)
);

-- creating order_details table
create table order_details(
orderID int,
cartID int,
order_status varchar(100),
custID varchar(10),
foreign key(orderID) references orders(orderID),
foreign key(cartID) references cart(cartID),
foreign key(custID) references customers(custID)
);

-- creating payment table
create table payment(
payment_id int,
orderID int,
custID varchar(10),
payment_date date,
payment_method varchar(50),
total_amount int,
foreign key(orderID) references orders(orderID),
foreign key(custID) references customers(custID)
);

-- creating shppingDetails table
create table shippingDetails(
shippingID int primary key,
tracking_number int,
shipping_address varchar(100),
shippingDate date,
orderID int,
foreign key(orderID) references orders(orderID)
);

-- inerting values to all the tables

insert into customers values
('cid101', 'Murali Selvaraj', '1997-07-15', '[email protected]', '12345', 8743535321, '124, Avenue Street', 'Chennai'),
('cid102', 'Karthik Suburaj', '1991-08-12', '[email protected]', '23243', 7546453465, '119, Anna Salai', 'Chennai'),
('cid103', 'Arul Deva', '1889-11-30', '[email protected]', '13422', 8765431412, '94, Neru Street', 'Madurai'),
('cid104', 'Adithya', '1993-04-23', '[email protected]', '65324', 8965653443, '118, Raja Street', 'Trichy'),
('cid105', 'Kamal Hasan', '1883-08-12', '[email protected]', 'kamal@123', 9876543422, '122, Kamaraj Salai', 'Coimbatore'),
('cid106', 'Joseph Vijay', '1886-06-23', '[email protected]', '13224', 8634323124, '402, Juhu Road','Mumbai'),
('cid107', 'Sharuk Khan', '1880-10-23', '[email protected]', '12423', 9874342422, '154, Khewra Circle', 'Thane');

insert into sellers values
('sid100', 'Adani', '[email protected]', '24321', 'delhi cmc', 9943336206),
('sid104', 'Tata', '[email protected]', '34231', 'mumbai mnc', 8773434232),
('sid107', 'Birla', '[email protected]', '65632', '132, Gujarat', 8765432321),
('sid109', 'Akash', '[email protected]', '75432', '54, Chennai', 8755343422);

insert into categories values
(1, 'Fashion'),
(2, 'Mobiles'),
(3, 'Electronics'),
(4, 'Applances'),
(5, 'Toys'),
(6, 'Furniture'),
(7, 'Sports'),
(8, 'Medicines'),
(9, 'Stationary'),
(10, 'Beauty');

insert into products(prod_id, name, color, quantity, price, cat_id, seller_id)
values ('pid1001', 'Poco c31', 'Black', 100, 12000, 2, 'sid100'),
('pid1002', 'Moto G4', 'Blue', 1000, 10000, 2, 'sid100'),
('pid1003', 'Redmi 12', 'Green', 2500, 9500, 2, 'sid104'),
('pid1004', 'Lenovo Yoga', 'Grey', 200, 80000, 3, 'sid104'),
('pid1005', 'Canon EOS', 'Black', 500, 29000, 3, 'sid107'),
('pid1006', 'Smart Watch', 'Red Blue', 250, 12000, 3, 'sid107'),
('pid1007', 'Dinning Table', 'Brown', 3000, 15000, 6, 'sid109'),
('pid1008', 'Samsung TV', 'Black', 4500, 65000, 4, 'sid109'),
('pid1009', 'Note Book', 'yellow', 1000, 45, 9, 'sid109'),
('pid1011', 'Facewash', 'Green', 350, 209, 10, 'sid107');

insert into orders(orderID, orderDate, total_amount, delivery_date, custID)
values (1001, '2023-01-20', 50000, '2023-01-26', 'cid102'),
(1002, '2023-01-23', 45000, '2023-01-27', 'cid101'),
(1003, '2023-01-23', 5000, '2023-01-27', 'cid102'),
(1004, '2023-01-23', 10000, '2023-01-27', 'cid101'),
(1005, '2023-01-16', 5000, '2023-01-20', 'cid103'),
(1006, '2023-01-18', 7000, '2023-01-27', 'cid104'),
(1007, '2023-02-12', 30000, '2023-02-20', 'cid105'),
(1008, '2023-02-13', 15000, '2023-02-20', 'cid105'),
(1009, '2023-01-15', 8000, '2023-01-23', 'cid106'),
(1010, '2023-01-21', 10000, '2023-01-27', 'cid101'),
(1011, '2023-02-13', 4500, '2023-02-21', 'cid107'),
(1012, '2023-01-23', 10000, '2023-01-27', 'cid102'),
(1100, '2023-01-12', 20000, '2023-01-15', 'cid107'),
(1101, '2023-02-07', 100000, '2023-02-16', 'cid101'),
(1103, '2023-01-23', 60000, '2023-01-27', 'cid103'),
(1104, '2023-01-23', 10000, '2023-01-27', 'cid102'),
(1105, '2023-02-20', 5500, '2023-02-27', 'cid106');

insert into cart(cartID)
values(111), (112), (113), (114), (115);

insert into cart_item(cart_item_id, prod_id, quantity, total_amount, cartID)
values(2001, 'pid1001', 2, 24000, 111),
(2002, 'pid1004', 1, 80000, 111),
(2003, 'pid1006', 1, 12000, 112),
(2004, 'pid1006', 2, 24000, 113),
(2005, 'pid1007', 1, 15000, 112),
(2006, 'pid1007', 1, 15000, 114),
(2007, 'pid1009', 7, 315, 115),
(2008, 'pid1011', 2, 700, 114);

insert into order_details(orderID, cartID, order_status, custID)
values(1001, 111, 'Delivered', 'cid102'),
(1002, 112, 'Delivered', 'cid101'),
(1005, 113, 'Completed', 'cid103'),
(1105, 114, 'Shipped', 'cid106');

insert into payment
values(6001, 1001, 'cid102', '2023-01-20', 'Gpay', 50000),
(6002, 1002, 'cid101', '2023-01-23', 'Debit Card', 45000),
(6003, 1003, 'cid102', '2023-01-23', 'Credit Card', 5000);

insert into shippingDetails(shippingID, tracking_number, shipping_address, shippingDate, orderID)
values(9001, 11111, '124, Avenue Street', '2023-01-23', 1002);

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

select * from customers
order by city;

2. WAQ to alter the table product by renaming all the coulumns by proceding them with 'prod_' .

alter table products
rename column id to prod_id;

alter table products
rename column name to prod_name;

alter table products
rename column color to prod_color;

alter table products
rename column quantity to prod_quantity;

alter table products
rename column price to prod_price;

alter table products
rename column cat_id to prod_cat_id;

alter table products
rename column seller_id to prod_seller_id;

select * from products;

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

select c.custID, c.Name, count(o.custID) as 'purchased count' from customers c
inner join orders o on c.custID = o.custID
group by c.custID
having count(o.custID) = 1;

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

select c.custID, c.Name as 'cust_name', count(o.custID) as 'purchased count' from customers c
inner join orders o on c.custID = o.custID
where c.Name like 'A%A'
group by c.custID
having count(o.custID) > 1;

5. 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.

Before the solution to the question, I am displaying all the required tables to understand.

We need customers, orders and order_details table for this question.

customers table:

orders table:

Here I'm displaying orders table and highlighted the rows who have purchased total_amount >= 10000

order_details table:

here I'm displaying order_details table and highlighted the rows those orders are delivered

select c.custId, c.Name from customers c
inner join orders o on c.custID = o.custID
inner join order_details od on o.custID = od.custID
where o.total_amount >= 10000 and od.order_status = "delivered"
group by c.custID
having count(od.custID) > 1;

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

delimiter $$
create procedure simpleProcedure()
begin
-- creating customer table
create table customers(
custID varchar(10) primary key,
Name varchar(50),
Dob date,
Email varchar(100),
Password varchar(30),
Phone bigint,
Address varchar(200),
City varchar(50)
);