Project 1

create database socialmedia;
use socialmedia;

create table admins(
admin_id int primary key,
admin_password int,
admin_name char(50)
);

create table seller(
seller_id int primary key,
seller_password int,
seller_fname char(50),
seller_lname char(50),
seller_city char(50),
gender char(20)
);

create table customer(
cust_id int primary key,
cust_password int,
cust_fname char(50),
cust_lname char(50),
cust_city char(50),
gender char(50)
);

create table products(
product_id int,
product_name char(50),
pro_types char(50)
);

create table categories(
cate_id int,
cate_name char(50),
cate_type char(50)
);

create table subcategories(
subcate_id int,
subcate_name char(50),
quantity int,
price int
);

create table cart(
subcate_id int,
price int,
quantity int,
cust_id int,
foreign key(cust_id) references customer(cust_id)
);

create table payment(
pay_id int,
pay_amt int,
pay_date date,
pay_desc double,
sucategory_id int
);

create table orders(
order_id int,
order_type char(50),
order_desc double,
subcate_id int
);

create table shopping(
shop_id int,
shop_name char(50),
shop_type char(50),
shop_date date,
shop_desc double
);

create table reviewer(
rev_id int,
rev_name char(50),
no_of_ratings double
);

insert into admins values (01,1234,"murali"),(02,4321,"bala");
insert into seller values(01,1122,"riyaz","khan","bangalore","male"),(02,2233,"chandra","babu","chennai","male");
insert into customer values(01,3333,"murali","khan","chennai","male"),(02,4444,"bala","krish","bangalore","male"),(03,4867,"raksha","khan","pune","male"),(04,6657,"jaya","suba","chennai","female");
insert into products values(01,"furniture","house"),(02,"electronics","showroom"),(03,"automobile parts","company");
insert into categories values(01,"wood","furniture"),(02,"plastics","electronics"),(03,"iron","automobiles");
insert into subcategories values(01,"coffee table",5,50000),(02,"bed",3,30000),(03,"switch box",8,500),(04,"wires",3,900),(05,"spanner",6,100),(06,"jockey",8,5000);
insert into cart values(01,50000,1,01),(03,500,1,04),(06,5000,2,02);
insert into payment values(01,50000,"2023-02-21",0.10,01),(02,500,"2023-02-22",0.10,04),(03,5000,"2023-02-19",0.10,02);
insert into orders values(01,"online",0.10,01),(02,"online",0.10,02);
insert into shopping values(01,"flipcart","online","2023-02-21",0.10),(02,"amazon","online","2023-02-22",0.10),(03,"flipcart","online","2023-02-19",0.10);
insert into reviewer values(1,"murali",8.9),(2,"bala",7.4),(3,"krish",9.8);

1.

select * from customer order by cust_city asc;

2.

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 pro_types to prod_types;

3.

select * from cart where cust_id>=1;

4.

select * from customer where cust_fname like "a%a";

5.

select orders.order_id,orders.order_type,orders.order_desc,cart.price from orders inner join cart on orders.subcate_id = cart.subcate_id where price >10000;

6.

delimiter $$
create procedure socialmedia.test()
begin
select * from customer;
select * from products;
end $$

call test();