Project 1

TASK-1

create table customers (
cust_id int not null primary key ,
f_name char(50),
l_name char(50),
member_type char(1),
Constraint check_member_type
check (member_type='p' or member_type='n')
);
insert into customers
values
(3,'Z','Shadani','n'),
(4,'B','Shadani','p'),
(5,'C','Shadani','n'),
(6,'D','Shadani','n'),
(7,'E','Shadani','n');W

insert into customers
values
(2,'KK','Shadani','p');

create table product(
product_id int not null primary key ,
product_name char(50),
brand char(50),
cost int not null,
color char(20),
suplier_id int not null ,
foreign key (supplier_id) references supplier(supplier_id)
);

create table supplier(
supplier_id int not null primary key ,
f_name char(50),
l_name char(50),
comapny_name char(50),
phone int(10) not null,
product_id int not null,

foreign key (product_id) references product(product_id));

ALTER TABLE customers
ADD column

(company_name char(60),
phone_no int(10) not null,
email char(60));

set autocommit=1;

create table address
(cust_id int not null,
street_name char(100),

zip_code int(7),
country char(50),
foreign key (cust_id) references customers(cust_id)
);

insert into address values
(1,'kafla',753002,'India'),
(2,'buxi',753001,'India'),
(3,'shelter',753001,'India'),
(4,'choudhary',75300,'India'),
(5,'badambadi',753006,'India'),
(6,'manglabag',753008,'India'),
(7,'pension lane',753009,'India');



create table supplier
(
supplier_id int not null primary key ,
cust_id int not null,
prod_id int not null,
f_name char(50),
l_name char(50),
foreign key (cust_id) references customers(cust_id),
foreign key (prod_id) references product(product_id)
);
insert into supplier values
(100,1,'kafla',753002,'India'),
(101,2,'buxi',753001,'India'),
(103,3,'shelter',753001,'India'),
(104,4,'choudhary',75300,'India'),
(105,5,'badambadi',753006,'India'),
(106,6,'manglabag',753008,'India'),
(107,7,'pension lane',753009,'India');




ALTER TABLE product
ADD column
cat_id int not null after product_name;

insert into product values
(200,'Iphone',300,'Apple',1000,'black',106,14,10),
(201,'Ipad',300,'Apple',1200,'White',106,22,3),
(202,'T-shirt',301,'Nike',300,'Green',100,38,300),
(203,'Pant',301,'Puma',800,'Blue',100,32,100),
(204,'Basmati',302,'India Gate',200,'NA',103,25,3),
(205,'Jeera',302,'MDH',20,'NA',103,10,3000),
(206,'Lens',303,'BL',450,'Colourless',107,1,30),
(207,'GLass',303,'Crizal',80,'Transparent',106,8,443);

create table category (
cat_id int not null primary key,
cat_name char(100),
cat_desc char(50));

ALTER TABLE product
ADD CONSTRAINT FK_ProdCat
FOREIGN KEY (cat_id)
REFERENCES category (cat_id);
create table subcategory (
subcat_id int not null primary key,
cat_id int not null ,
scat_name char(100),
scat_desc char(50),
FOREIGN KEY (cat_id) REFERENCES category (cat_id));

select *from category;
select *from product;
select *from subcategory;

alter table category
add column subcat_id int not null after cat_id;

alter table category
add foreign key (subcat_id) references subcategory(subcat_id);

select *from product;

insert into category
values
(300,'Electronics','Warehouse-Manglabag'),
(301,'Clothing','Warehouse-Kafla'),
(302,'Groceries','Warehouse-Shelter'),
(303,'Eyewear','Warehouse-Manglabag_PensionLane');

alter table category
drop subcat_id;

insert into subcategory
values
(1,300,'Mobile','Iphone 14 Pro MAX'),
(2,301,'Bottom Wear','All Season'),
(3,301,'Upper Wear','Polos and T-shirt'),
(4,302,'Rice','Arwa'),
(5,302,'Spices','MDH and Everest'),
(6,303,'Lens','Lenskart'),
(7,303,'Specs','Lenskart');

-- Review Table for Customer
create table review
(cust_id int not null,
prod_id int not null,
rating int(1) not null ,
Constraint check_rating
check (rating between 1 and 5),
cust_feedback char(100));

select *from review;

insert into review
values
(1,200,5,'Good',1),
(1,202,4,'Ok',2),
(1,206,3,'Ok',3),
(3,203,5,'Good',4);

alter table review
add Constraint check_feedback
check (cust_feedback='Good' or cust_feedback='Ok'or cust_feedback='Bad' );
select *from product;

alter table review
add column review_id int not null primary key;

select *from address;

create table c1order
(
order_id int primary key not null,
supplier_id int not null,
cust_id int not null,
order_status char not null
constraint check_status
check (order_status='cancelled' or order_status='completed' or order_status='ordered' or order_status='packed' or order_status='shipped' or order_status='delivered')
);

alter table c1order
add column order_amnt int not null after order_id;

create table cart
(cust_id int not null,
prod_id int not null,
cart_size int not null,

FOREIGN KEY (cust_id) REFERENCES customers (cust_id),
FOREIGN KEY (prod_id) REFERENCES product (product_id));

alter table cart
add column order_id int not null,
add foreign key (order_id) references c1order(order_id);

select *from cart

-- --Task3--

-- 1

select *from customer
order by city asc;

-- 2

alter table product
rename column product_id to prod_id,
rename column product_name to prod_name,
rename column cat_id to prod_catid,
rename column brand to prod_brand,
rename column cost to prod_cost,
rename column color to prod_color,
rename column suplier_id to prod_supplierid,
rename column size to prod_size,
rename column quantity to prod_quantity;

-- 3

select c.f_name||c.l_name as cname,c.cust_id as cid
from customers c where exists
(select 1 from orders where c.cust_id=orders.customer_id);

--4

select c.f_name||c.l_name as cname,c.cust_id as cid
from customers c where exists
(select 1 from orders where c.cust_id=orders.customer_id)
and c.f_name||c.l_name LIKE 'a%a';

--5

SELECT *
FROM c1order
WHERE order_status = 'delivered'
AND order_amnt > 10000
AND cust_id IN (SELECT cust_id FROM c1order GROUP BY cust_id HAVING COUNT(*) > 1);

--6

DELIMITER $$$

Create Procedure 'ecommerce'.'test'()

BEGIN

CREATE TABLE CUSTOMER(

cust_id int not null primary key ,
f_name char(50),
l_name char(50),
member_type char(1),
Constraint check_member_type
check (member_type='p' or member_type='n'));

INSERT INTO CUSTOMER VALUES

(3,'Z','Shadani','n'),
(4,'B','Shadani','p'),
(5,'C','Shadani','n'),
(6,'D','Shadani','n'),
(7,'E','Shadani','n');

alter table product
rename column product_id to prod_id,
rename column product_name to prod_name,
rename column cat_id to prod_catid,
rename column brand to prod_brand,
rename column cost to prod_cost,
rename column color to prod_color,
rename column suplier_id to prod_supplierid,
rename column size to prod_size,
rename column quantity to prod_quantity;

END $$$

DELIMITER ;