Project 1

Task1 and Task2

create table customer(cust_id int primary key,cust_name varchar(50),gender varchar(2),DOB date,password varchar(20),Address varchar100),contact_No int,order_id int foreing key(order_id)references orders(order_id), product_id int foreing key(product_id)references products(product_id), wish_id int foreing key(wish_id)references wishlist(wish_id), pay_id int foreing key(pay_id)references payment(pay_id));

insrt into customer values(101,"Mano",F,"02-01-1999",Mano@123,"chennai",044-8888 8888),

(102,"Manju",F,"03-04-2001",Manju@123,"chennai",044-2222 2222),

(103,"Anitha",F,"05-06-2003",Anitha@123,"Banglore",044-2222 8888),

(104,"Mani",M,"27-06-2006",Mani@123,"chennai",044-8888 2222),

(105,"Vithya",F,"28-02-2004",Mano@123,"chennai",044-8888 8888));

create table seller(seller_id int primary key,seller_name varchar(50),contact_no int,password varchar(20),category_of_product_provider varchar(50),order_id int foreing key(order_id)references orders(order_id), product_id int foreing key(product_id)references products(product_id), wish_id int foreing key(wish_id)references wishlist(wish_id), pay_id int foreing key(pay_id)references payment(pay_id));

insert into seller values(001,"Bhuvi",8111058948,"Bhuvi@789","Women's wear"),

(002,"subanu",631928375,"suban@789","Men'swear"),

(003,"seetha",8260705338,"seetha@789","Kid'swear"),

(004,"Renu",7448853244,"Renu@789","foot wear"));

 create table order(order_id in primary key,order_date date,total_order varchar(50),order_status_id int foreing key(order_status_id)references order_status(order_status_id),pay_id int foreing key(pay_id)references payment(pay_id),cust_id int foreing key(cust_id)references customer(cust_id),seller_id int foreing key(seller_id)references seller(seller_id));

create table order_status(order_status_id int primary key, status varchar(30));

insert into order_status values(021,"Out_of_delivery"),

(022,"shipping"),

(023," delivered"),

(024,"cancelled"),

(025,"unable to deliver today"));

insert into order values(000001,"5-08-2023", '1'),

(000002,"06-08-2023", '3'),

(000003,"04-08-2023", '4'),

(000004,"02-08-2023", '2'));

create table shipment(shipment_id int primary key,method_name varchar(50),price decimal(10,20));

insert into shipment values(2001,"air cargo" Rs.65),

(2002,"railway" Rs.35),(2001,"truck" 00));

create table product(product_id int primary key,product_color varchar(50),size[] varchar(50),cost decimal(10,2),seller_id int foreing key(seller_id) references seller(seller_id),disc_id int foreing key(disc_id)references dicount(disc_id),review_id int foreing key(reveiw_id)references reveiws(review_id),description varchar(200));

insert into product values(101,"Black","[S,M,L,XL]",500.15,"cotton and comfort"),

(102,"Red","[S,M,L,XL]",405.15,"rayon good cloth"),

(103,"Black","[S,M,L,XL]",1000.10,"cripe perfect"),

(104,"Black","[S,M,L,XL]",300.20,"party wear"),

(105,"Black","[S,M,L,XL]",.100.50,"regular use cloths"));

create table reviews(review_id int primary key,rating_value varchar(20),comment varchar(150),customer_id int foreing key(cust_id)references customer(cust_id));

insert into reviews values(0001,"4","good product value for money"),

(0002,"3","nice product value for money"),

(0003,"1","worst product don't buy it"),

(0004,"3","not bad"),

(0005,"4","very comfortable for wearing it"));

create table discount(disc_id int primary key,coupen_code varchar(50),dicc_descreption,discount_rate varchar(50),start_date date,end_date date);

insert into discount values(111,"FES100","Buy Rs.2000 get offer","15-08-23","15-09-23"),

(112,"rakshabandh sale","buy one get one", 50%,"01-08-23","01-09-23"),

(113," sale","buy two get one", 25%,"01-08-23","31-09-23"));

create table wishlist(wish_is int primary key,product_id int foreing key(product_id)references product(product-id));

insert into wishlist values((201),(202),(203));

Task3

1.fetch the customers in the order of their city

select * form customer order by city ASC;

2.alter the table product by renaming all the columns by preceding them with 'prod.'

sp_rename 'product,[product_id]'='prod_id','COLUMN'

sp_rename 'product,[product_size]'='prod_name','COLUMN'

sp_rename 'product,[product_colur]'='prod_size,'COLUMN'

sp_rename 'product,[product_price]'='prod_price','COLUMN'

3.fetch the details of customer who have purchased at keast one product

select customer_id,MIN(No_of_purchases)from order GROUP BY (no_of_purchases)<= 1;

4. fetch the customer details strating from A and ending from A and purchases more than one item

select cust_name from customers where cust_name LIKE '%A' AND MAX(purchase_id)>1;

5. fetch the orders which are already delivers and has cost more than 10000 and also been purchaed by more than one customers.

select * from orders where purchase_amount >(select AVG(purchase_amount) FROM orders b where b.customer_id = a.customer_id);

6.write a simple procedure to run allthe table creation and insert statementand alter statement at once

create proceture SelectAllCustomers AS select * FROM Customers

GO;// it is for creatinf the procedure

EXEC SelectAllCustomers; // execute the procedure.