Project 1
Task:3
1.
select orders.ord_id as orderId,customer.cust_fname as CustomerFirstName,customer.cust_lname as CustomerLastName,customer.cust_city as city
from orders ,customer WHERE orders.cust_id=customer.cust_id;
2.
alter table product rename column pro_id to prod_id;
alter table product rename column pro_name to prod_name;
alter table product rename column pro_price to prod_price;
alter table product rename column pro_Size to prod_Size;
alter table product rename column pro_color to prod_color;
alter table product rename column pro_quantity to prod_quanitity;
3.
select * from customer where cust_id in (select distinct cust_id from orders);
4.
select * from customer where cust_fname like 'A%' and cust_lname like'%a'and cust_id in (select cust_id from orders
group by cust_id having count(ord_id) >1
);
select c.cust_fname as customerFname,c.cust_lname as customerlname,o.ord_qty from customer c,orders o
where cust_fname like 'A%' and cust_lname like '%a' and o.ord_qty>1;
5.
select * from orders where ord_status ='Delivered' and ord_cost> 10000 and prod_id in
(select prod_id from orders group by prod_id having count(distinct cust_id)>1);
(select prod_id from orders group by prod_id having count(distinct cust_id)>1);
6.
delimiter $$
create procedure `Ecommerce_database`. RunAllStatements()
begin
create table Admin(
adm_id int primary key ,
adm_fname varchar(100),
adm_lname varchar(100),
adm_password varchar(20),
adm_ph int,
adm_email varchar(100)
);
create table seller(
sel_id int primary key,
sel_fname varchar (30),
sel_lname varchar(30),
sel_gender char(1),
sel_city varchar(50),
sel_dob date,
sel_email varchar(50),
sel_contact int,
sel_password varchar(20)
);
create table customer(
cust_id int primary key,
cust_fname varchar(30),
cust_lname varchar(30),
cust_gender char(1),
cust_dob date,
cust_city varchar(30),
cust_ph int,
cust_email varchar(30),
cust_add varchar(100)
);
create table categories(
cat_id int primary key,
cat_name varchar(50)
);
create table product(
pro_id int primary key,
pro_name varchar(50),
pro_price int,
pro_size varchar(5),
pro_color varchar(20),
pro_quantity int(5),
cat_id int,
cat_name varchar(50),
foreign key (cat_id) references categories(cat_id)
);
create table coupon(
coup_id int primary key,
pro_id int,
discount_rule varchar(40),
prec_per_coup char(20),
foreign key (pro_id)references product(pro_id)
);
create table orders(
cust_id int,
ord_id int primary key,
pro_id int,
ord_qty int,
ord_date date,
sel_id int ,
foreign key(cust_id) references customer(cust_id),
foreign key(sel_id)references seller(sel_id),
foreign key(pro_id)references product(pro_id)
);
create table payment(
bill_id int,
ord_id int,
bill_amt int,
pay_method varchar(50),
primary key (bill_id),
foreign key (ord_id) references orders(ord_id)
);
create table Track(
tr_id int,
ord_id int,
status varchar(29),
primary key (tr_id),
foreign key(ord_id)references orders(ord_id)
);
create table mycard(
card_type char(20),
ord_id int,
bill_id int,
tr_id int,
ord_status varchar(50),
foreign key(ord_id)references orders(ord_id),
foreign key(bill_id)references payment(bill_id),
foreign key(tr_id)references Track(tr_id)
);
create table feedback(
pro_id int,
cust_id int,
num_o_stars varchar(20),
review varchar(100),
foreign key (pro_id)references product(pro_id),
foreign key(cust_id)references customer(cust_id)
);
insert into Admin (adm_id ,adm_fname,adm_lname,adm_password,adm_ph,adm_email) values
(777,'THOMAS','MTS','mts777',77777777,'[email protected]');
insert into seller( sel_id ,sel_fname,sel_lname,sel_gender,sel_city,sel_dob, sel_email,sel_contact,sel_password)values
(5001,'Muthu','Kumar','M','Chennai','1998-5-23','[email protected]',94563214,'muthu123'),
(5002,'Raj','Kumar','M','Banaglore','2000-7-23','[email protected]',94677574,'rajku123'),
(5003,'vijaya','Lakshmi','F','Chennai','1996-8-01','[email protected]',88599444,'vijaya1245'),
(5004,'Joseph','vijay','M','London','1985-6-21','[email protected]',6636236,'vijay777'),
(5005,'Manjau','Varier','F','Kerala','1991-7-23','[email protected]',94256553,'varier123');
insert into customer(cust_id,cust_fname,cust_lname,cust_gender,cust_dob,cust_city,cust_ph,cust_email,cust_add)
values(101,'siva','kumar','M','2000-2-13','chennai',1234566,'[email protected]','no.3/3,krishnagiri,635112'),
(102,'Raja','kumari','F','2003-2-15','kerala',47233466,'[email protected]','no.7/3,kochi,735117'),
(103,'Alwin','Thomas','M','2003-8-15','delhi',87945635,'[email protected]','no.12/133,kptm,635117'),
(104,'Muthu','Lakshmi','F','2002-3-7','salem',77583756,'[email protected]','no.2/3,jerryroad,635115'),
(105,'Selvaraj','kumar','M','1998-2-13','Dharumapuri',677465734,'[email protected]','no.54/3,palacode,6352334'),
(106,'Vidhya','raj','F','1987-7-13','chennai',48755885,'[email protected]','no.3/7,redhills,600003'),
(107,'Akash','Babu','M','2000-8-13','Bangolure',276446534,'[email protected]','no.45/3,majestic,735112');
select * from customer;
update customer set cust_fname='Amanda',cust_lname = 'Angela' where cust_id = 102;
insert into categories(cat_id,cat_name) values (201,'Mens'),(202,'womens'),(203,'kids'),(204,'sports');
insert into product (pro_id,pro_name,pro_Price ,pro_Size,pro_color,pro_quantity,cat_id,cat_name) values
(701,'T_shirt',999,'M','Blue',2,201,'Mens'),
(702,'Sari',2999,'FZ','Green',5,202,'Womens'),
(703,'shorts',555,'s','Red',1,203,'Kids'),
(704,'MRF bat',9999,'20*30','1',2,204,'sports'),
(705,'shoes',3999,'9','Blue with white',3,201,'Mens'),
(706,'Top',1999,'xl','Blue',2,202,'Womens'),
(707,'Jens',899,'M','Black',5,203,'kids');
insert into coupon(coup_id,pro_id,discount_rule,prec_per_coup) values
(601,701,'Min spend 30000','30%'),
(602,702,'Max Buy 2 products ','10%'),
(603,703,'Min Spend 50000','50%'),
(604,704,'SBI Cridit Card to ues buy products','70%'),
(605,705,'send my account 100000 ','100%'),
(606,706,'Axis bank to pay','20%'),
(607,707,'min spend 70000','30%');
insert into orders(cust_id,ord_id,pro_id,ord_qty,ord_date,sel_id)values
(101,301,701,1,'2023-3-7',5001),
(102,302,702,3,'2023-5-13',5004),
(103,303,705,3,'2022-4-7',5005),
(107,305,701,4,'2021-3-7',5003),
(105,307,706,2,'2023-1-7',5002),
(101,310,703,1,'2023-3-7',5005);
insert into payment(bill_id,ord_id,bill_amt,pay_method) values
(1001,301,2000,'Gpay'),
(1002,307,1000,'COD'),
(1003,303,7000,'NetBanking'),
(1005,305,999,'Gpay'),
(1006,310,3999,'COD');
insert into Track(tr_id,ord_id,status)values
(401,301,'shipped'),
(402,305,'packed'),
(403,303,'Ready to delivery'),
(404,307,'traveling'),
(405,302,'Dispached');
insert into mycard(card_type,ord_id,bill_id,tr_id,ord_status)values
('Platinum',301,1001,401,'waiting for delivery'),
('Silver',303,1003,403,'Delivered'),
('Dimond',310,1006,405,'Canceled'),
('platinum',305,1005,402,'Today Expted to delivery'),
('Dimond',307,1002,404,'shipped');
insert into feedback(pro_id,cust_id,num_o_stars,review)values
(701,101,'3 stars','Good'),
(702,102,'5 stars','Super'),
(703,103,'4 stars','Quality is Good'),
(704,104,'3 stars','OK'),
(705,105,'2 stars','Quality is not good'),
(706,106,'1 star','Bad'),
(707,107,'5 stars','Very Good');
alter table product rename column pro_id to prod_id;
alter table product rename column pro_name to prod_name;
alter table product rename column pro_price to prod_price;
alter table product rename column pro_Size to prod_Size;
alter table product rename column pro_color to prod_color;
alter table product rename column pro_quantity to prod_quanitity;
end$$
please refer the mysql file belove i mentioned .........