Project 1
Task - 1&2
create database Ecommerce;
use Ecommerce;
create table customers (
cust_id int,
cust_gender varchar(40),dob int,
cust_city varchar(40) ,
cust_password varchar(40),
cust_name varchar(40),
cust_email varchar(40),
cust_delivery address varchar(40),
cust_contacts int,
current_ord varchar(40),
previous_ord varchar(40),
payment_info varchar(40),
primary key(cust_id)
);
create table products (
_id int,
name varchar(40),
color varchar(40),
seller_id int,
size int,
quantity int,
cost int,
discount int,
delivery_address varchar(40),
featured varchar(40),
popularity int,
avg_rating int,
cont_review varchar(40),
onsale varchar(40),
categories varchar(40),
brands varchar(40),
images varchar(40),
qus and ans varchar(40),
description varchar(40),
tags varchar(40),
primary key(prod_id),
seller_id references seller(seller_id)
);
create table orders(
ord_id int,
status varchar(40),
tel_no int,
del_date int,
order date int,
primary key(ord_id)
);
create table seller(
seller_id int,
sel_name varchar(40),
tel_no int,
sel_password int,
sel_category varchar(40),
primary key(seller_id)
);
insert into customers values
(1,"male",05-06-2000,"new password","sam","[email protected]","Madurai
Tamil Nadu",9878986372,"red shirt","white shoe","not paid");
(2,"female",04-06-2000,"old password","Anu","[email protected]","chennai
Tamil Nadu",6457986372,"blue shirt","white jean","paid");
insert into products values
(1,"shirt","blue",21,30,10,600,50,"chennai,Tamil nadu","Double side shirt",
70,55,"Good quality","India","Available","casual wear","Levis",
"Is returning the Product is applicable - yes","Do not Dry wash",
"Buy one get one");
(2,"Shoe","White",14,9,100,900,30,"Bangalore,karnataka","Washable",
90,75,"Affordable price","India","Available","Semi casual","US Polo",
"Is returning the Product is applicable - yes","Light weight",
"All season shoes");
insert into orders values
(1,"ordered",65984578,07-12-2022,02-12-2022);
(2,"packed",95984528,05-12-2022,01-12-2022);
insert into seller values
(1,"Joe",6532987412,23456,"Houseold Products");
(2,"vino",9832987412,23456,"Cosmetics");
Task -3
Answer 1) Select * from customers oredr by city;
Answer 2) a)ALTER TABLE products RENAME COLUMN _id TO prod_id;
b)ALTER TABLE products RENAME COLUMN name TO prod_name;
c)ALTER TABLE products RENAME COLUMN color TO prod_color;
d)ALTER TABLE products RENAME COLUMN seller_id TO prodseller_id;
e)ALTER TABLE products RENAME COLUMN size TO prod_id;
f)ALTER TABLE products RENAME COLUMN quantity TO prod_quantity;
g)ALTER TABLE products RENAME COLUMN cost TO prod_cost;
h)ALTER TABLE products RENAME COLUMN discount TO prod_discount;
i)ALTER TABLE products RENAME COLUMN delivery_address TO proddelivery_address;
j)ALTER TABLE products RENAME COLUMN features TO prod_features;
k)ALTER TABLE products RENAME COLUMN popularity TO prod_popularity;
l)ALTER TABLE products RENAME COLUMN avg_rating TO prodavg_rating;
m)ALTER TABLE products RENAME COLUMN review TO prod_review;
n)ALTER TABLE products RENAME COLUMN cont_review TO prodcont_review;
o)ALTER TABLE products RENAME COLUMN onsale TO prod_onsale;
p)ALTER TABLE products RENAME COLUMN category TO prod_category;
q)ALTER TABLE products RENAME COLUMN brands TO prod_brands;
r)ALTER TABLE products RENAME COLUMN images TO prod_images;
s)ALTER TABLE products RENAME COLUMN que and ans TO prod_que and ans;
t)ALTER TABLE products RENAME COLUMN description TO prod_description;
u)ALTER TABLE products RENAME COLUMN tags TO prod_tags;
v)ALTER TABLE products RENAME COLUMN rel_prod TO prod_rel_prod;
Answer 3) Select * from customers where prod_id<= 1;
Answer 4) Select * from customer where cust_name like 'A%'and '%A'and prod_id<= 1;
Answer 5) Select * from orders where status="delivered" and prod_cost=1000 and
cust_id<=1;
Answer 6)
Delimiter $$
create procedure `Ecommerce`.`statements`( )
Begin
create table customers (
cust_id int,
cust_gender varchar(40),dob int,
cust_city varchar(40) ,
cust_password varchar(40),
cust_name varchar(40),
cust_email varchar(40),
cust_delivery address varchar(40),
cust_contacts int,
current_ord varchar(40),
previous_ord varchar(40),
payment_info varchar(40),
primary key(cust_id)
);
create table products (
_id int,
name varchar(40),
color varchar(40),
seller_id int,
size int,
quantity int,
cost int,
discount int,
delivery_address varchar(40),
featured varchar(40),
popularity int,
avg_rating int,
cont_review varchar(40),
onsale varchar(40),
categories varchar(40),
brands varchar(40),
images varchar(40),
qus and ans varchar(40),
description varchar(40),
tags varchar(40),
primary key(prod_id),
seller_id references seller(seller_id)
);
create table orders(
ord_id int,
status varchar(40),
tel_no int,
del_date int,
order date int,
primary key(ord_id)
);
create table seller(
seller_id int,
sel_name varchar(40),
tel_no int,
sel_password int,
sel_category varchar(40),
primary key(seller_id)
);
insert into customers values
(1,"male",05-06-2000,"new password","sam","[email protected]","Madurai
Tamil Nadu",9878986372,"red shirt","white shoe","not paid");
(2,"female",04-06-2000,"old password","Anu","[email protected]","chennai
Tamil Nadu",6457986372,"blue shirt","white jean","paid");
insert into products values
(1,"shirt","blue",21,30,10,600,50,"chennai,Tamil nadu","Double side shirt",
70,55,"Good quality","India","Available","casual wear","Levis",
"Is returning the Product is applicable - yes","Do not Dry wash",
"Buy one get one");
(2,"Shoe","White",14,9,100,900,30,"Bangalore,karnataka","Washable",
90,75,"Affordable price","India","Available","Semi casual","US Polo",
"Is returning the Product is applicable - yes","Light weight",
"All season shoes");
insert into orders values
(1,"ordered",65984578,07-12-2022,02-12-2022);
(2,"packed",95984528,05-12-2022,01-12-2022);
insert into seller values
(1,"Joe",6532987412,23456,"Houseold Products");
(2,"vino",9832987412,23456,"Cosmetics");
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 seller_id TO prodseller_id;
ALTER TABLE products RENAME COLUMN size TO prod_id;
ALTER TABLE products RENAME COLUMN quantity TO prod_quantity;
ALTER TABLE products RENAME COLUMN cost TO prod_cost;
ALTER TABLE products RENAME COLUMN discount TO prod_discount;
ALTER TABLE products RENAME COLUMN delivery_address TO proddelivery_address;
ALTER TABLE products RENAME COLUMN features TO prod_features;
ALTER TABLE products RENAME COLUMN popularity TO prod_popularity;
ALTER TABLE products RENAME COLUMN avg_rating TO prodavg_rating;
ALTER TABLE products RENAME COLUMN review TO prod_review;
ALTER TABLE products RENAME COLUMN cont_review TO prodcont_review;
ALTER TABLE products RENAME COLUMN onsale TO prod_onsale;
ALTER TABLE products RENAME COLUMN category TO prod_category;
ALTER TABLE products RENAME COLUMN brands TO prod_brands;
ALTER TABLE products RENAME COLUMN images TO prod_images;
ALTER TABLE products RENAME COLUMN que and ans TO prod_que and ans;
ALTER TABLE products RENAME COLUMN description TO prod_description;
ALTER TABLE products RENAME COLUMN tags TO prod_tags;
ALTER TABLE products RENAME COLUMN rel_prod TO prod_rel_prod;
End $$
Delimiter;
call statements();
ER diagram is attached below.