Project 1

create database project1;
use project1;
create table Customer(
Cust_UserId VARCHAR(50) primary key,
Cust_PW varchar(50),
Cust_name varchar(50),
Cust_MailId varchar(50),
Cust_Gender varchar(1),
Cust_Age int,
Cust_Address varchar(200),
Cust_Ph_No int,
Cust_Order_History varchar(50),
Cust_Payment_Info varchar(100),
Cust_City VARCHAR(50)
);
INSERT INTO CUSTOMER VALUES("C01","CUSTOM123","SHANU","[email protected]","M",24,"AZAD COLONY
SAHARANPUR UP NDIA",9870,"NULL","PHONEPE,GPAY,PAYTM,INTERNET BANKING","SAHARANPUR");
INSERT INTO CUSTOMER VALUES("C02","ABCDEF","RIYA","[email protected]","F",20,"HNO-246 PADAPPAI
TAMBARAM CHENNAI INDIA",98700,"4","PHONEPE,GPAY,PAYTM,INTERNET BANKING","CHENNAI");
INSERT INTO CUSTOMER VALUES("C03","9870","AFFAN","[email protected]","M",16,"AZAD COLONY
SAHARANPUR UP NDIA",93587,"10","PHONEPE,GPAY,PAYTM,INTERNET BANKING","SAHARANPUR");
INSERT INTO CUSTOMER VALUES("C04","@1234","Afsha","[email protected]","F",23,"H-A30,Lane no-3
Jogabai Extension ,Zakir nagar",9988,"3","PHONEPE,GPAY,PAYTM,INTERNET BANKING","New Delhi");
INSERT INTO CUSTOMER VALUES("C05","@HSGS","SNEHA","[email protected]","F",21,"H-A30,Lane no-3
KOLKATA ",987733,"1","PHONEPE,GPAY,PAYTM,INTERNET BANKING","KOLKATA");
SELECT * FROM CUSTOMER;
-- ------>> QUESTION 1 3 5
SELECT * FROM CUSTOMER ORDER BY CUST_CITY;
SELECT * FROM CUSTOMER WHERE CUST_ORDER_HISTORY>1;
SELECT * FROM CUSTOMER WHERE Cust_name LIKE "a%a" AND CUST_ORDER_HISTORY>1;
C01 CUSTOM123 SHANU [email protected] M 24
AZAD COLONY
SAHARANPUR
UP NDIA
9870 NULL PHONEPE,GPAY,PAYTM,INTERNET
BANKING SAHARANPUR
C02 ABCDEF RIYA [email protected] F 20
HNO-246
PADAPPAI
TAMBARAM
CHENNAI INDIA
98700 4
PHONEPE,GPAY,PAYTM,INTERNET
BANKING CHENNAI
C03 9870 AFFAN [email protected] M 16
AZAD COLONY
SAHARANPUR
UP NDIA
93587 10 PHONEPE,GPAY,PAYTM,INTERNET
BANKING SAHARANPUR
C04 @1234 Afsha [email protected] F 23
H-A30,Lane no-3
Jogabai Extension
,Zakir nagar
9988 3
PHONEPE,GPAY,PAYTM,INTERNET
BANKING New Delhi
C05 @HSGS SNEHA [email protected] F 21 H-A30,Lane no-3
KOLKATA 987733 1
PHONEPE,GPAY,PAYTM,INTERNET
BANKING KOLKATA
create table Seller(
Seller_UserId VARCHAR(50) Primary key,
Seller_Password varchar(50),
Seller_Name varchar(50),
Seller_Ph_No int,
Seller_MailId varchar(50),
Seller_Address varchar(200),
Product_Category varchar(100),
Brands varchar(50)
);
SELECT * FROM seller;
INSERT INTO SELLER VALUES("S01","@1234","UZAIF MIRZA",9927113,"[email protected]","H-NO 246,LANE
NO 4 ,JOGABAI,NEW DELHI INDIA","IRON HANDICRAFTS","HEENA SHOPPEE");
INSERT INTO SELLER VALUES("S02","@kill","SHANU HANDICRAFTS",9870788,"[email protected]","H-NO
401,LANE NO 1 ,AZAD COLONY,SAHARANPUR,UP INDIA","IRON HANDICRAFTS","SS HANDICRAFTS");
INSERT INTO SELLER VALUES("S03","@YESH","IRFAN MIRZA",987898,"[email protected]","H-NO
1502,LANE NO 11 ,AZAD COLONY,SAHARANPUR,UP INDIA","BEAUTY PRODUCT ND GROCERIES","MIRZA GENEREAL
STORE");
INSERT INTO SELLER VALUES("S04","@sachin","SACHIN RANA",9870000,"[email protected]","SHOP NO
123,MANDI SAMITI ROAD,SAHARANPUR,UP INDIA","ELECTRONICS","SACHIN ELECTRONICS");
S01 @1234 UZAIF MIRZA 9927113 [email protected] H-NO 246,LANE NO 4
,JOGABAI,NEW DELHI INDIA
IRON
HANDICRAFTS
HEENA
SHOPPEE
S02 @kill SHANU
HANDICRAFTS 9870788 [email protected]
H-NO 401,LANE NO 1 ,AZAD
COLONY,SAHARANPUR,UP
INDIA
IRON
HANDICRAFTS
SS
HANDICRAFTS
S03 @YESH IRFAN MIRZA 987898 [email protected]
H-NO 1502,LANE NO 11 ,AZAD
COLONY,SAHARANPUR,UP
INDIA
BEAUTY
PRODUCT ND
GROCERIES
MIRZA
GENEREAL
STORE
S04 @sachin SACHIN RANA 9870000 [email protected] SHOP NO 123,MANDI SAMITI
ROAD,SAHARANPUR,UP INDIA ELECTRONICS SACHIN
ELECTRONICS
create table Product(
Product_id VARCHAR(50) primary key,
Product_name varchar(200) not null,
Colour varchar(50),
Size varchar(50),
Quantity int,
Cost int,
Discount int,
Rating FLOAT,
Cast varchar(50),
Category varchar(50),
Brands varchar(50) references Seller(Brands),
Product_description varchar(100)
);
-- -------->>>>> QUESTION 2
ALTER TABLE PRODUCT RENAME COLUMN PRODUCT_ID TO PROD_ID;ALTER TABLE PRODUCT RENAME COLUMN
Quantity TO PROD_QUANTITY;
ALTER TABLE PRODUCT RENAME COLUMN Product_name TO PROD_NAME;ALTER TABLE PRODUCT RENAME COLUMN
Discount TO PROD_DISCOUNT;
ALTER TABLE PRODUCT RENAME COLUMN Colour TO PROD_COLOUR;ALTER TABLE PRODUCT RENAME COLUMN Rating
TO PROD_RATING;
ALTER TABLE PRODUCT RENAME COLUMN Size TO PROD_SIZE;ALTER TABLE PRODUCT RENAME COLUMN Cast TO
PROD_CAST;
ALTER TABLE PRODUCT RENAME COLUMN Category TO PROD_CATEGORY;ALTER TABLE PRODUCT RENAME COLUMN
Brands TO PROD_BRANDS;
ALTER TABLE PRODUCT RENAME COLUMN Product_description TO PROD_DESCRIPTION;
SELECT * FROM product;
INSERT INTO PRODUCT VALUES("P01","CUP STAND MADE OF IRON","BLACK/WHITE","L-22 : H-10 : W-12
INCHES",200,499,200,8.8,"MADE UP OF IRON","IRON HANDICRAFTS","SS HANDICRAFTS","This is made by strong iron
,usefull to keep cup,mug,beer glass etc.");
INSERT INTO PRODUCT VALUES("P02","AC PANASONIC","WHITE","L-30 : H-18 : W-10 INCHES",100,14888,599,9.9,"IRON
,FIBER,PLASTIC","ELECTRONICS","SACHIN ELECTRONICS","PANASONIC PRESSENTS AC TO KEEP COOL YOUR ROOM,THIS
IS AMAZING IN SUMMERS,");
INSERT INTO PRODUCT VALUES("P03","REFRIGERATOR SUPERCOOL","WHITE/GREE/BLACK/RED/BLUE","L-36 : H-80 : W30 INCHES",50,24999,1099,9.5,"HARD PLASTIC,IRON","ELECTRONICS","SACHIN ELECTRONICS","PANASONIC PRESSENTS
AN REFRIGERATOR, USE FIRST THEN BELIEVE");
INSERT INTO PRODUCT VALUES("P04","DINING TABLE"," BROWN ","L-60 : H-24 : W-30
INCHES",8,8999,320,8,"SANGWAN W00D","IRON HANDICRAFTS","SS HANDICRAFTS","This is made by SANGWAN
WOOD");
INSERT INTO PRODUCT VALUES("P05","HIMALYA FACE WASH","GREEN","L-8 : H-1 : W-1
INCHES",5000,110,40,9.5,"PLASTIC,CHEMICAL,NEEM,LIMO","BEAUTY PRODUCTS ND GROCEIES","MIRZA GENERAL
STORE","A FACE WASH MADEUP PF NEEM,LIME WHICH WILL REMOVE DUST,DARK CIRCLE.");
P0
1
CUP STAND
MADE OF
IRON
BLACK/WHITE
L-22 :
H-10 :
W-12
INCHE
S
200 499 200 8.
8
MADE UP OF IRON
IRON
HANDICRAF
TS
SS
HANDICRAF
TS
This is made by
strong iron
,usefull to keep
cup,mug,beer
glass etc.
P0
2
AC
PANASONIC WHITE
L-30 :
H-18 :
W-10
INCHE
S
100 1488
8
599 9.
9
IRON ,FIBER,PLASTIC ELECTRONI
CS
SACHIN
ELECTRONI
CS
PANASONIC
PRESSENTS
AC TO
KEEP COOL
YOUR
ROOM,THIS IS
AMAZING IN
SUMMERS,
P0
3
REFRIGERAT
OR
SUPERCOOL
WHITE/GREE/BLACK/RED/
BLUE
L-36 :
H-80 :
W-30
INCHE
S
50 2499
9
109
9
9.
5
HARD PLASTIC,IRON ELECTRONI
CS
SACHIN
ELECTRONI
CS
PANASONIC
PRESSENTS
AN
REFRIGERAT
OR, USE
FIRST THEN
BELIEVE
P0
4
DINING
TABLE BROWN
L-60 :
H-24 :
W-30
INCHE
S
8 8999 320 8 SANGWAN W00D
IRON
HANDICRAF
TS
SS
HANDICRAF
TS
This is made by
SANGWAN
WOOD
P0
5
HIMALYA
FACE WASH GREEN
L-8 :
H-1 :
W-1
INCHE
S
500
0
110 40 9.
5
PLASTIC,CHEMICAL,NEEM,
LIMO
BEAUTY
PRODUCTS
ND
GROCEIES
MIRZA
GENERAL
STORE
A FACE WASH
MADEUP PF
NEEM,LIME
WHICH WILL
REMOVE
DUST,DARK
CIRCLE.
create table Orders(
Order_Id VARCHAR(50) primary key,
Status varchar(500),
delivery_date date,
order_date date,
Expected_delivery varchar(100),
Product_id VARCHAR(10) references Product(Product_id ),
Product_name varchar(200) references Product (Product_name ),
Product_description varchar(100) references Product(Product_description),
Cust_name varchar(50) references Customer (Cust_name),
Cust_MailId varchar(50) references Customer (Cust_MailId),
Cust_Ph_No int references Customer (Cust_Ph_No),
Cust_Address varchar(200) references Customer (Cust_Address),
Seller_Name varchar(50)references Seller(Seller_Name),
Brands varchar(50) references Seller(Brands ),
COST INT references PRODUCT(COST)
);
SELECT * FROM orders;
-- ----->>> Question 5
select * from orders where COST>10000 and Status="DELIVERED";
O0
1
DELIVER
ED
202
3-
02-
17
202
3-
02-
08
On
FRIDAY
P0
3
REFRIGERAT
OR
SUPERCOOL
PANASONIC
PRESSENTS
AN
REFRIGERAT
OR, USE
FIRST THEN
BELIEVE
SHAN
U
[email protected]
M
987083
83
AZAD
COLONY
SAHARANP
UR UP
INDIA
SACHI
N
RANA
SACHIN
ELECTRONI
CS
2499
9
O0
2
DELIVER
ED
202
3-
02-
16
202
3-
02-
07
ON
THURSD
AY
P0
3
REFRIGERAT
OR
SUPERCOOL
PANASONIC
PRESSENTS
AN
REFRIGERAT
OR, USE
FIRST THEN
BELIEVE
AFFA
N
AFFANWASIF@GMAIL
.COM
935871
26
AZAD
COLONY
SAHARANP
UR UP
INDIA
SACHI
N
RANA
SACHIN
ELECTRONI
CS
2499
9
INSERT INTO ORDERS VALUES("O01","DELIVERED","2023-02-17","2023-02-8","On FRIDAY","P03","REFRIGERATOR
SUPERCOOL","PANASONIC PRESSENTS AN REFRIGERATOR, USE FIRST THEN
BELIEVE","SHANU","[email protected]",98708383,"AZAD COLONY SAHARANPUR UP INDIA","SACHIN
RANA","SACHIN ELECTRONICS",24999);
INSERT INTO ORDERS VALUES("O02","DELIVERED","2023-02-16","2023-02-07","ON THURSDAY","P03","REFRIGERATOR
SUPERCOOL","PANASONIC PRESSENTS AN REFRIGERATOR, USE FIRST THEN
BELIEVE","AFFAN","[email protected]",93587126,"AZAD COLONY SAHARANPUR UP INDIA","SACHIN
RANA","SACHIN ELECTRONICS",24999);
INSERT INTO ORDERS VALUES("O03","BOOKED","2023-02-23","2023-02-15","ON WEDNESDAY","P01","CUT STAND
MADE OF IRON","This is made by strong iron ,usefull to keep cup,mug,beer glass
etc.","SHANU","[email protected]",98708383,"AZAD COLONY SAHARANPUR UP INDIA","UZAIF MIRZA","HEENA
SHOPEE",499);
INSERT INTO ORDERS VALUES("O04","BOOKED","2023-02-24","2023-02-9","ON THURSDAY","P05","HIMALYA FACE
WASH","A FACE WASH MADEUP PF NEEM,LIME WHICH WILL REMOVE DUST,DARK
CIRCLE.","Afsha","[email protected]",998877662,"H-A30,Lane no-3 Jogabai Extension ,Zakir nagar","IRFAN
MIRZA","MIRZA GENEREAL STORE",110);
O0
1
DELIVER
ED
202
3-
02-
17
202
3-
02-
08
On
FRIDAY
P0
3
REFRIGERA
TOR
SUPERCOOL
PANASONIC
PRESSENTS
AN
REFRIGERAT
SHAN
U
[email protected]
OM
9870838
3
AZAD
COLONY
SAHARANP
SACHI
N
RANA
SACHIN
ELECTRON
ICS
2499
9
OR, USE
FIRST THEN
BELIEVE
UR UP
INDIA
O0
2
DELIVER
ED
202
3-
02-
16
202
3-
02-
07
ON
THURSDA
Y
P0
3
REFRIGERA
TOR
SUPERCOOL
PANASONIC
PRESSENTS
AN
REFRIGERAT
OR, USE
FIRST THEN
BELIEVE
AFFA
N
AFFANWASIF@GMAIL
.COM
9358712
6
AZAD
COLONY
SAHARANP
UR UP
INDIA
SACHI
N
RANA
SACHIN
ELECTRON
ICS
2499
9
O0
3
BOOKED
202
3-
02-
23
202
3-
02-
15
ON
WEDNESD
AY
P0
1
CUT STAND
MADE OF
IRON
This is made
by strong iron
,usefull to keep
cup,mug,beer
glass etc.
SHAN
U
[email protected]
OM
9870838
3
AZAD
COLONY
SAHARANP
UR UP
INDIA
UZAIF
MIRZ
A
HEENA
SHOPEE 499
O0
4
BOOKED
202
3-
02-
24
202
3-
02-
09
ON
THURSDA
Y
P0
5
HIMALYA
FACE WASH
A FACE
WASH
MADEUP PF
NEEM,LIME
WHICH WILL
REMOVE
DUST,DARK
CIRCLE.
Afsha AfshaNoor.123@GMAIL
.COM
9988776
62
H-A30,Lane
no-3 Jogabai
Extension
,Zakir nagar
IRFAN
MIRZ
A
MIRZA
GENEREAL
STORE
110
create table Admin(
Admin_UserId VARCHAR(10) primary key,
Admin_Password varchar(50),
Admin_Name varchar(50),
Admin_Salary int,
Admin_Position varchar(50),
Admin_Age int,
Admin_gender varchar(1),
Admin_Address varchar(100)
);
SELECT * FROM admin;
INSERT INTO ADMIN VALUES("A01","@ANJALI","ANJALI KUMARI",40000,"BACK END ROLE",22,"F","DARRA
ALI,MUMBAI");
INSERT INTO ADMIN VALUES("A02","@RAISY","RAISY RAPHY",44000,"FRONT END ROLE",31,"F","VIKAS
GANJ,MUMBAI");
INSERT INTO ADMIN VALUES("A03","@RAJESHVARI","RAJESHVARI",39000,"FULL STACK DEVLOPER",28,"F","ZAKIR
NAGAR,NEW DELHI");
A01 @ANJALI ANJALI KUMARI 40000 BACK END ROLE 22 F DARRA ALI,MUMBAI
A02 @RAISY RAISY RAPHY 44000 FRONT END ROLE 31 F VIKAS GANJ,MUMBAI
A03 @RAJESHVARI RAJESHVARI 39000 FULL STACK DEVLOPER 28 F ZAKIR NAGAR,NEW DELHI
create table category(
Category_Id VARCHAR(50) primary key,
Category_Name varchar(100),
Category_Descrption varchar(250)
);
SELECT * FROM Category;
INSERT INTO CATEGORY VALUES("CATEGORY_01","IRON HANDICRAFTS","PRODUCTS MADE UP OF IRON AND WOOD");
INSERT INTO CATEGORY VALUES("CATEGORY_02","BEAUTY PRODUCTS AND GROCERIES","PRODUCTS WHICH CAN
CONTAIN BEAUTY PRODUCTS AND ALSO HROCERIES");
INSERT INTO CATEGORY VALUES("CATEGORY_03","ELECTRONICS","PRODUCTS OF ELECTRONIC WHICH CAN RUN BY
ELECTRIC");
INSERT INTO CATEGORY VALUES("CATEGORY_04","GARMENTS","CLOTHES/BOOT/BEDSHEET");
INSERT INTO CATEGORY VALUES("CATEGORY_05","HOME APPLIANCES","DECORATIVE,ELECTRONICS,KITCHEN");
INSERT INTO CATEGORY VALUES("CATEGORY_06","FOOD","VEG/NON VEG/COOCKED/UNCOOCKED");
CATEGORY_01 IRON HANDICRAFTS PRODUCTS MADE UP OF IRON AND WOOD
CATEGORY_02 BEAUTY PRODUCTS
AND GROCERIES
PRODUCTS WHICH CAN CONTAIN BEAUTY PRODUCTS
AND ALSO HROCERIES
CATEGORY_03 ELECTRONICS PRODUCTS OF ELECTRONIC WHICH CAN RUN BY
ELECTRIC
CATEGORY_04 GARMENTS CLOTHES/BOOT/BEDSHEET
CATEGORY_05 HOME APPLIANCES DECORATIVE,ELECTRONICS,KITCHEN
CATEGORY_06 FOOD VEG/NON VEG/COOCKED/UNCOOCKED
create table SubCategory(
Subcategory_Id VARCHAR(50) primary key,
Subcategory_Name varchar(100),
Subcategory_description varchar(200),
Category_Name varchar(100) references CATEGORY(Category_Name),
Category_Id VARCHAR(50) references category(Category_Id)
);
SELECT * FROM subcategory;
INSERT INTO SubCategory VALUES("SUB_CATEGORY_001","IRON","PRODUCTS MADE UP OF IRON ","IRON
HANDICRAFTS","CATEGORY_01");
INSERT INTO SubCategory VALUES("SUB_CATEGORY_002","WOOD","PRODUCTS MADE UP OF WOOD ","IRON
HANDICRAFTS","CATEGORY_01");
INSERT INTO SubCategory VALUES("SUB_CATEGORY_003","ELECTRONIC KICHTEN ITEM","ELECTRONICSS
","ELECTRONICS","CATEGORY_03");
INSERT INTO SubCategory VALUES("SUB_CATEGORY_004","CLOTHES","JEANS,SHIRT,T
SHIRT,LOWER","GARMENTS","CATEGORY_04");
INSERT INTO SubCategory
VALUES("SUB_CATEGORY_005","BOOT","BOYS/GIRLS/BABIES","GARMENTS","CATEGORY_04");
INSERT INTO SubCategory VALUES("SUB_CATEGORY_006","DECORATIVE ITEMS OF WOOD","PRODUCTS MADE UP
WOOD ","HOME APPLIANCES","CATEGORY_05");
INSERT INTO SubCategory VALUES("SUB_CATEGORY_007","DECORATIVE","PRODUCTS MADE UP OF IRON ","HOME
APPLIANCES","CATEGORY_05");
SUB_CATEGORY_001 IRON PRODUCTS MADE UP OF IRON IRON HANDICRAFTS CATEGORY_01
SUB_CATEGORY_002 WOOD PRODUCTS MADE UP OF WOOD IRON HANDICRAFTS CATEGORY_01
SUB_CATEGORY_003 ELECTRONIC KICHTEN ITEM ELECTRONICSS ELECTRONICS CATEGORY_03
SUB_CATEGORY_004 CLOTHES JEANS,SHIRT,T SHIRT,LOWER GARMENTS CATEGORY_04
SUB_CATEGORY_005 BOOT BOYS/GIRLS/BABIES GARMENTS CATEGORY_04
SUB_CATEGORY_006 DECORATIVE ITEMS OF WOOD PRODUCTS MADE UP WOOD HOME APPLIANCES CATEGORY_05
SUB_CATEGORY_007 DECORATIVE PRODUCTS MADE UP OF IRON HOME APPLIANCES CATEGORY_05
Create Table Cart(
Order_Id VARCHAR(10) references Orders(Order_id),
Product_id VARCHAR(10) references Product (Product_id ),
Product_name varchar(200) not null references Product (Product_name ),
Cost int references Product (Cost),
Product_description varchar(100) references Product (Product_description ),
Seller_Name varchar(50) references Seller (Seller_Name),
Brands varchar(50) references Seller (Brands)
);
SELECT * FROM Cart;
INSERT INTO Cart VALUES("O01","P03","REFRIGERATOR SUPERCOOL",24999,"PANASONIC PRESSENTS AN
REFRIGERATOR, USE FIRST THEN BELIEVE","SACHIN RANA","SACHIN ELECTRONICS");
INSERT INTO Cart VALUES("O02","P03","REFRIGERATOR SUPERCOOL",24999,"PANASONIC PRESSENTS AN
REFRIGERATOR, USE FIRST THEN BELIEVE","SACHIN RANA","SACHIN ELECTRONICS");
INSERT INTO Cart VALUES("O03","P01","CUT STAND MADE OF IRON",499,"This is made by strong iron ,usefull to keep
cup,mug,beer glass etc.","UZAIF MIRZA","HEENA SHOPEE");
INSERT INTO Cart VALUES("O04","P05","HIMALYA FACE WASH",110,"A FACE WASH MADEUP PF NEEM,LIME WHICH
WILL REMOVE DUST,DARK CIRCLE.","IRFAN MIRZA","MIRZA GENEREAL STORE");
O01 P03 REFRIGERATOR
SUPERCOOL 24999 PANASONIC PRESSENTS AN REFRIGERATOR, USE FIRST THEN
BELIEVE
SACHIN
RANA
SACHIN
ELECTRONICS
O02 P03 REFRIGERATOR
SUPERCOOL 24999 PANASONIC PRESSENTS AN REFRIGERATOR, USE FIRST THEN
BELIEVE
SACHIN
RANA
SACHIN
ELECTRONICS
O03 P01 CUT STAND MADE OF
IRON 499 This is made by strong iron ,usefull to keep cup,mug,beer glass etc. UZAIF
MIRZA HEENA SHOPEE
O04 P05 HIMALYA FACE WASH 110 A FACE WASH MADEUP PF NEEM,LIME WHICH WILL REMOVE
DUST,DARK CIRCLE.
IRFAN
MIRZA
MIRZA GENEREAL
STORE
create table ShippingDetails(
Shipper_Id varchar(10) primary key,
Shipper_Name varchar(100) not null,
Shipping_Address varchar(200),
Order_Weight varchar(50),
Order_Size varchar(50),
Tracking_Number int
);
SELECT * FROM shippingdetails;
INSERT INTO SHIPPINGDETAILS VALUES("SHIPPER_01","RAHUL KUMAR","H-A30,Lane no-3 Jogabai Extension ,Zakir
nagar","300-GM","6X4",374527);
INSERT INTO SHIPPINGDETAILS VALUES("SHIPPER_02","SLAMAN NABEEL","H-A30,Lane no3,PADAPPAI,CHEENAI","1KG","12X12",725532);
INSERT INTO SHIPPINGDETAILS VALUES("SHIPPER_03","ADAM AZEES","H-A30,Lane no-3,ALI ROAD MUMBAI","5KGGM","80X23",75763);
INSERT INTO SHIPPINGDETAILS VALUES("SHIPPER_04","SULEMAN RANA","H-A30,Lane no-3,BALLU PUR CHOWK
DEHRADUN","880 KG","20X30",735382);
SHIPPER_01 RAHUL KUMAR H-A30,Lane no-3 Jogabai Extension ,Zakir nagar 300-GM 6X4 374527
SHIPPER_02 SLAMAN NABEEL H-A30,Lane no-3,PADAPPAI,CHEENAI 1KG 12X12 725532
SHIPPER_03 ADAM AZEES H-A30,Lane no-3,ALI ROAD MUMBAI 5KG-GM 80X23 75763
SHIPPER_04 SULEMAN RANA H-A30,Lane no-3,BALLU PUR CHOWK DEHRADUN 880 KG 20X30 735382
create table Reviews(
Cust_name varchar(50) references Customer(Cust_name),
Cust_MailId varchar(50) references Customer(Cust_MailId),
Rev_Rating float,
Product_id VARCHAR(50) references Product(Product_id),
Product_name varchar(50) references Product(Product_name),
Rev_Comment varchar(500)
);
INSERT INTO REVIEWS VALUES("SHANU","[email protected]",8.8,"P03","REFRIGERATOR SUPERCOOL","ITS AN
AMAZING REFRIDGERATOR THANKS TO SACHIN ELECTRONICS");
INSERT INTO REVIEWS VALUES("RAEES","[email protected]",9.9,"P04","REFRIGERATOR SUPERCOOL","ITS AN
AMAZING REFRIDGERATOR THANKS TO SACHIN ELECTRONICS");
INSERT INTO REVIEWS VALUES("SONU MALIK","[email protected]",8,"P02","HIMALYA FACE WASH","ITS
AN AMAZING PRODUCT THANKS TO MIRZA GENERAL STORE");
INSERT INTO REVIEWS VALUES("HITESH HIPRARA","[email protected]",9,"P01","CUP STAND","ITS AN AMAZING
CUP STAND THANKS TO SS HANDICRAFTS");
SHANU [email protected] 8.8 P03 REFRIGERATOR
SUPERCOOL
ITS AN AMAZING REFRIDGERATOR THANKS TO SACHIN
ELECTRONICS
RAEES [email protected] 9.9 P04 REFRIGERATOR
SUPERCOOL
ITS AN AMAZING REFRIDGERATOR THANKS TO SACHIN
ELECTRONICS
SONU MALIK [email protected] 8 P02 HIMALYA FACE WASH ITS AN AMAZING PRODUCT THANKS TO MIRZA
GENERAL STORE
HITESH
HIPRARA [email protected] 9 P01 CUP STAND ITS AN AMAZING CUP STAND THANKS TO SS
HANDICRAFTS
QUESTION 6 ------
CREATE DATABASE SHOWWA;
USE SHOWWA;
delimiter $$
create procedure project()
begin
create table Customer(
Cust_UserId VARCHAR(50) primary key,
Cust_PW varchar(50),
Cust_name varchar(50),
Cust_MailId varchar(50),
Cust_Gender varchar(1),
Cust_Age int,
Cust_Address varchar(200),
Cust_Ph_No int,
Cust_Order_History varchar(50),
Cust_Payment_Info varchar(100),
Cust_City VARCHAR(50)
);
INSERT INTO CUSTOMER VALUES("C01","CUSTOM123","SHANU","[email protected]","M",24,"AZAD COLONY
SAHARANPUR UP NDIA",9870,"NULL","PHONEPE,GPAY,PAYTM,INTERNET BANKING","SAHARANPUR");
INSERT INTO CUSTOMER VALUES("C02","ABCDEF","RIYA","[email protected]","F",20,"HNO-246 PADAPPAI
TAMBARAM CHENNAI INDIA",98700,"4","PHONEPE,GPAY,PAYTM,INTERNET BANKING","CHENNAI");
INSERT INTO CUSTOMER VALUES("C03","9870","AFFAN","[email protected]","M",16,"AZAD COLONY
SAHARANPUR UP NDIA",93587,"10","PHONEPE,GPAY,PAYTM,INTERNET BANKING","SAHARANPUR");
INSERT INTO CUSTOMER VALUES("C04","@1234","Afsha","[email protected]","F",23,"H-A30,Lane no-3
Jogabai Extension ,Zakir nagar",9988,"3","PHONEPE,GPAY,PAYTM,INTERNET BANKING","New Delhi");
INSERT INTO CUSTOMER VALUES("C05","@HSGS","SNEHA","[email protected]","F",21,"H-A30,Lane no-3
KOLKATA ",987733,"1","PHONEPE,GPAY,PAYTM,INTERNET BANKING","KOLKATA");
SELECT * FROM CUSTOMER;
create table Seller(
Seller_UserId VARCHAR(50) Primary key,
Seller_Password varchar(50),
Seller_Name varchar(50),
Seller_Ph_No int,
Seller_MailId varchar(50),
Seller_Address varchar(200),
Product_Category varchar(100),
Brands varchar(50)
);
INSERT INTO SELLER VALUES("S01","@1234","UZAIF MIRZA",9927113,"[email protected]","H-NO 246,LANE
NO 4 ,JOGABAI,NEW DELHI INDIA","IRON HANDICRAFTS","HEENA SHOPPEE");
INSERT INTO SELLER VALUES("S02","@kill","SHANU HANDICRAFTS",9870788,"[email protected]","H-NO
401,LANE NO 1 ,AZAD COLONY,SAHARANPUR,UP INDIA","IRON HANDICRAFTS","SS HANDICRAFTS");
INSERT INTO SELLER VALUES("S03","@YESH","IRFAN MIRZA",987898,"[email protected]","H-NO
1502,LANE NO 11 ,AZAD COLONY,SAHARANPUR,UP INDIA","BEAUTY PRODUCT ND GROCERIES","MIRZA GENEREAL
STORE");
INSERT INTO SELLER VALUES("S04","@sachin","SACHIN RANA",9870000,"[email protected]","SHOP NO
123,MANDI SAMITI ROAD,SAHARANPUR,UP INDIA","ELECTRONICS","SACHIN ELECTRONICS");
SELECT * FROM seller;
create table Product(
Product_id VARCHAR(50) primary key,
Product_name varchar(200) not null,
Colour varchar(50),
Size varchar(50),
Quantity int,
Cost int,
Discount int,
Rating FLOAT,
Cast varchar(50),
Category varchar(50),
Brands varchar(50) references Seller(Brands),
Product_description varchar(100)
);
ALTER TABLE PRODUCT RENAME COLUMN PRODUCT_ID TO PROD_ID;ALTER TABLE PRODUCT RENAME COLUMN
Quantity TO PROD_QUANTITY;
ALTER TABLE PRODUCT RENAME COLUMN Product_name TO PROD_NAME;ALTER TABLE PRODUCT RENAME COLUMN
Discount TO PROD_DISCOUNT;
ALTER TABLE PRODUCT RENAME COLUMN Colour TO PROD_COLOUR;ALTER TABLE PRODUCT RENAME COLUMN Rating
TO PROD_RATING;
ALTER TABLE PRODUCT RENAME COLUMN Size TO PROD_SIZE;ALTER TABLE PRODUCT RENAME COLUMN Cast TO
PROD_CAST;
ALTER TABLE PRODUCT RENAME COLUMN Category TO PROD_CATEGORY;ALTER TABLE PRODUCT RENAME COLUMN
Brands TO PROD_BRANDS;
ALTER TABLE PRODUCT RENAME COLUMN Product_description TO PROD_DESCRIPTION;
INSERT INTO PRODUCT VALUES("P01","CUP STAND MADE OF IRON","BLACK/WHITE","L-22 : H-10 : W-12
INCHES",200,499,200,8.8,"MADE UP OF IRON","IRON HANDICRAFTS","SS HANDICRAFTS","This is made by strong iron
,usefull to keep cup,mug,beer glass etc.");
INSERT INTO PRODUCT VALUES("P02","AC PANASONIC","WHITE","L-30 : H-18 : W-10 INCHES",100,14888,599,9.9,"IRON
,FIBER,PLASTIC","ELECTRONICS","SACHIN ELECTRONICS","PANASONIC PRESSENTS AC TO KEEP COOL YOUR ROOM,THIS
IS AMAZING IN SUMMERS,");
INSERT INTO PRODUCT VALUES("P03","REFRIGERATOR SUPERCOOL","WHITE/GREE/BLACK/RED/BLUE","L-36 : H-80 : W30 INCHES",50,24999,1099,9.5,"HARD PLASTIC,IRON","ELECTRONICS","SACHIN ELECTRONICS","PANASONIC PRESSENTS
AN REFRIGERATOR, USE FIRST THEN BELIEVE");
INSERT INTO PRODUCT VALUES("P04","DINING TABLE"," BROWN ","L-60 : H-24 : W-30
INCHES",8,8999,320,8,"SANGWAN W00D","IRON HANDICRAFTS","SS HANDICRAFTS","This is made by SANGWAN
WOOD");
INSERT INTO PRODUCT VALUES("P05","HIMALYA FACE WASH","GREEN","L-8 : H-1 : W-1
INCHES",5000,110,40,9.5,"PLASTIC,CHEMICAL,NEEM,LIMO","BEAUTY PRODUCTS ND GROCEIES","MIRZA GENERAL
STORE","A FACE WASH MADEUP PF NEEM,LIME WHICH WILL REMOVE DUST,DARK CIRCLE.");
SELECT * FROM product;
create table Orders(
Order_Id VARCHAR(50) primary key,
Status varchar(500),
delivery_date date,
order_date date,
Expected_delivery varchar(100),
Product_id VARCHAR(10) references Product(Product_id ),
Product_name varchar(200) references Product (Product_name ),
Product_description varchar(100) references Product(Product_description),
Cust_name varchar(50) references Customer (Cust_name),
Cust_MailId varchar(50) references Customer (Cust_MailId),
Cust_Ph_No int references Customer (Cust_Ph_No),
Cust_Address varchar(200) references Customer (Cust_Address),
Seller_Name varchar(50)references Seller(Seller_Name),
Brands varchar(50) references Seller(Brands ),
COST INT references PRODUCT(COST)
);
INSERT INTO ORDERS VALUES("O01","DELIVERED","2023-02-17","2023-02-8","On FRIDAY","P03","REFRIGERATOR
SUPERCOOL","PANASONIC PRESSENTS AN REFRIGERATOR, USE FIRST THEN
BELIEVE","SHANU","[email protected]",98708383,"AZAD COLONY SAHARANPUR UP INDIA","SACHIN
RANA","SACHIN ELECTRONICS",24999);
INSERT INTO ORDERS VALUES("O02","DELIVERED","2023-02-16","2023-02-07","ON THURSDAY","P03","REFRIGERATOR
SUPERCOOL","PANASONIC PRESSENTS AN REFRIGERATOR, USE FIRST THEN
BELIEVE","AFFAN","[email protected]",93587126,"AZAD COLONY SAHARANPUR UP INDIA","SACHIN
RANA","SACHIN ELECTRONICS",24999);
INSERT INTO ORDERS VALUES("O03","BOOKED","2023-02-23","2023-02-15","ON WEDNESDAY","P01","CUT STAND
MADE OF IRON","This is made by strong iron ,usefull to keep cup,mug,beer glass
etc.","SHANU","[email protected]",98708383,"AZAD COLONY SAHARANPUR UP INDIA","UZAIF MIRZA","HEENA
SHOPEE",499);
INSERT INTO ORDERS VALUES("O04","BOOKED","2023-02-24","2023-02-9","ON THURSDAY","P05","HIMALYA FACE
WASH","A FACE WASH MADEUP PF NEEM,LIME WHICH WILL REMOVE DUST,DARK
CIRCLE.","Afsha","[email protected]",998877662,"H-A30,Lane no-3 Jogabai Extension ,Zakir nagar","IRFAN
MIRZA","MIRZA GENEREAL STORE",110);
SELECT * FROM orders;
create table Admin(
Admin_UserId VARCHAR(10) primary key,
Admin_Password varchar(50),
Admin_Name varchar(50),
Admin_Salary int,
Admin_Position varchar(50),
Admin_Age int,
Admin_gender varchar(1),
Admin_Address varchar(100)
);
INSERT INTO ADMIN VALUES("A01","@ANJALI","ANJALI KUMARI",40000,"BACK END ROLE",22,"F","DARRA
ALI,MUMBAI");
INSERT INTO ADMIN VALUES("A02","@RAISY","RAISY RAPHY",44000,"FRONT END ROLE",31,"F","VIKAS
GANJ,MUMBAI");
INSERT INTO ADMIN VALUES("A03","@RAJESHVARI","RAJESHVARI",39000,"FULL STACK DEVLOPER",28,"F","ZAKIR
NAGAR,NEW DELHI");
SELECT * FROM admin;
create table category(
Category_Id VARCHAR(50) primary key,
Category_Name varchar(100),
Category_Descrption varchar(250)
);
INSERT INTO CATEGORY VALUES("CATEGORY_01","IRON HANDICRAFTS","PRODUCTS MADE UP OF IRON AND WOOD");
INSERT INTO CATEGORY VALUES("CATEGORY_02","BEAUTY PRODUCTS AND GROCERIES","PRODUCTS WHICH CAN
CONTAIN BEAUTY PRODUCTS AND ALSO HROCERIES");
INSERT INTO CATEGORY VALUES("CATEGORY_03","ELECTRONICS","PRODUCTS OF ELECTRONIC WHICH CAN RUN BY
ELECTRIC");
INSERT INTO CATEGORY VALUES("CATEGORY_04","GARMENTS","CLOTHES/BOOT/BEDSHEET");
INSERT INTO CATEGORY VALUES("CATEGORY_05","HOME APPLIANCES","DECORATIVE,ELECTRONICS,KITCHEN");
INSERT INTO CATEGORY VALUES("CATEGORY_06","FOOD","VEG/NON VEG/COOCKED/UNCOOCKED");
SELECT * FROM Category;
create table SubCategory(
Subcategory_Id VARCHAR(50) primary key,
Subcategory_Name varchar(100),
Subcategory_description varchar(200),
Category_Name varchar(100) references CATEGORY(Category_Name),
Category_Id VARCHAR(50) references category(Category_Id)
);
INSERT INTO SubCategory VALUES("SUB_CATEGORY_001","IRON","PRODUCTS MADE UP OF IRON ","IRON
HANDICRAFTS","CATEGORY_01");
INSERT INTO SubCategory VALUES("SUB_CATEGORY_002","WOOD","PRODUCTS MADE UP OF WOOD ","IRON
HANDICRAFTS","CATEGORY_01");
INSERT INTO SubCategory VALUES("SUB_CATEGORY_003","ELECTRONIC KICHTEN ITEM","ELECTRONICSS
","ELECTRONICS","CATEGORY_03");
INSERT INTO SubCategory VALUES("SUB_CATEGORY_004","CLOTHES","JEANS,SHIRT,T
SHIRT,LOWER","GARMENTS","CATEGORY_04");
INSERT INTO SubCategory
VALUES("SUB_CATEGORY_005","BOOT","BOYS/GIRLS/BABIES","GARMENTS","CATEGORY_04");
INSERT INTO SubCategory VALUES("SUB_CATEGORY_006","DECORATIVE ITEMS OF WOOD","PRODUCTS MADE UP
WOOD ","HOME APPLIANCES","CATEGORY_05");
INSERT INTO SubCategory VALUES("SUB_CATEGORY_007","DECORATIVE","PRODUCTS MADE UP OF IRON ","HOME
APPLIANCES","CATEGORY_05");
SELECT * FROM subcategory;
Create Table Cart(
Order_Id VARCHAR(10) references Orders(Order_id),
Product_id VARCHAR(10) references Product (Product_id ),
Product_name varchar(200) not null references Product (Product_name ),
Cost int references Product (Cost),
Product_description varchar(100) references Product (Product_description ),
Seller_Name varchar(50) references Seller (Seller_Name),
Brands varchar(50) references Seller (Brands)
);
INSERT INTO Cart VALUES("O01","P03","REFRIGERATOR SUPERCOOL",24999,"PANASONIC PRESSENTS AN
REFRIGERATOR, USE FIRST THEN BELIEVE","SACHIN RANA","SACHIN ELECTRONICS");
INSERT INTO Cart VALUES("O02","P03","REFRIGERATOR SUPERCOOL",24999,"PANASONIC PRESSENTS AN
REFRIGERATOR, USE FIRST THEN BELIEVE","SACHIN RANA","SACHIN ELECTRONICS");
INSERT INTO Cart VALUES("O03","P01","CUT STAND MADE OF IRON",499,"This is made by strong iron ,usefull to keep
cup,mug,beer glass etc.","UZAIF MIRZA","HEENA SHOPEE");
INSERT INTO Cart VALUES("O04","P05","HIMALYA FACE WASH",110,"A FACE WASH MADEUP PF NEEM,LIME WHICH
WILL REMOVE DUST,DARK CIRCLE.","IRFAN MIRZA","MIRZA GENEREAL STORE");
SELECT * FROM Cart;
create table ShippingDetails(
Shipper_Id varchar(10) primary key,
Shipper_Name varchar(100) not null,
Shipping_Address varchar(200),
Order_Weight varchar(50),
Order_Size varchar(50),
Tracking_Number int
);
INSERT INTO SHIPPINGDETAILS VALUES("SHIPPER_01","RAHUL KUMAR","H-A30,Lane no-3 Jogabai Extension ,Zakir
nagar","300-GM","6X4",374527);
INSERT INTO SHIPPINGDETAILS VALUES("SHIPPER_02","SLAMAN NABEEL","H-A30,Lane no3,PADAPPAI,CHEENAI","1KG","12X12",725532);
INSERT INTO SHIPPINGDETAILS VALUES("SHIPPER_03","ADAM AZEES","H-A30,Lane no-3,ALI ROAD MUMBAI","5KGGM","80X23",75763);
INSERT INTO SHIPPINGDETAILS VALUES("SHIPPER_04","SULEMAN RANA","H-A30,Lane no-3,BALLU PUR CHOWK
DEHRADUN","880 KG","20X30",735382);
SELECT * FROM shippingdetails;
create table Reviews(
Cust_name varchar(50) references Customer(Cust_name),
Cust_MailId varchar(50) references Customer(Cust_MailId),
Rev_Rating float,
Product_id VARCHAR(50) references Product(Product_id),
Product_name varchar(50) references Product(Product_name),
Rev_Comment varchar(500)
);
INSERT INTO REVIEWS VALUES("SHANU","[email protected]",8.8,"P03","REFRIGERATOR SUPERCOOL","ITS AN
AMAZING REFRIDGERATOR THANKS TO SACHIN ELECTRONICS");
INSERT INTO REVIEWS VALUES("RAEES","[email protected]",9.9,"P04","REFRIGERATOR SUPERCOOL","ITS AN
AMAZING REFRIDGERATOR THANKS TO SACHIN ELECTRONICS");
INSERT INTO REVIEWS VALUES("SONU MALIK","[email protected]",8,"P02","HIMALYA FACE WASH","ITS
AN AMAZING PRODUCT THANKS TO MIRZA GENERAL STORE");
INSERT INTO REVIEWS VALUES("HITESH HIPRARA","[email protected]",9,"P01","CUP STAND","ITS AN AMAZING
CUP STAND THANKS TO SS HANDICRAFTS");
SELECT * FROM REVIEWS;
end $$
RESULT FOR PROCEDURE :
3 60 13:38:01
create procedure project2() begin create table Customer( Cust_UserId VARCHAR(50) primary key, Cust_PW varchar(50),
Cust_name varchar(50), Cust_MailId varchar(50), Cust_Gender varchar(1), Cust_Age int, Cust_Address varchar(200),
Cust_Ph_No int, Cust_Order_History varchar(50), Cust_Payment_Info varchar(100), Cust_City VARCHAR(50) ); INSERT
INTO CUSTOMER VALUES("C01","CUSTOM123","SHANU","[email protected]","M",24,"AZAD COLONY
SAHARANPUR UP NDIA",9870,"NULL","PHONEPE,GPAY,PAYTM,INTERNET BANKING","SAHARANPUR");
INSERT INTO CUSTOMER VALUES("C02","ABCDEF","RIYA","[email protected]","F",20,"HNO-246
PADAPPAI TAMBARAM CHENNAI INDIA",98700,"4","PHONEPE,GPAY,PAYTM,INTERNET
BANKING","CHENNAI"); INSERT INTO CUSTOMER
VALUES("C03","9870","AFFAN","[email protected]","M",16,"AZAD COLONY SAHARANPUR UP
NDIA",93587,"10","PHONEPE,GPAY,PAYTM,INTERNET BANKING","SAHARANPUR"); INSERT INTO CUSTOMER
VALUES("C04","@1234","Afsha","[email protected]","F",23,"H-A30,Lane no-3 Jogabai Extension ,Zakir
nagar",9988,"3","PHONEPE,GPAY,PAYTM,INTERNET BANKING","New Delhi"); INSERT INTO CUSTOMER
VALUES("C05","@HSGS","SNEHA","[email protected]","F",21,"H-A30,Lane no-3 KOLKATA
",987733,"1","PHONEPE,GPAY,PAYTM,INTERNET BANKING","KOLKATA"); SELECT * FROM CUSTOMER; create
table Seller( Seller_UserId VARCHAR(50) Primary key, Seller_Password varchar(50), Seller_Name varchar(50), Seller_Ph_No
int, Seller_MailId varchar(50), Seller_Address varchar(200), Product_Category varchar(100), Brands varchar(50) ); INSERT
INTO SELLER VALUES("S01","@1234","UZAIF MIRZA",9927113,"[email protected]","H-NO 246,LANE
NO 4 ,JOGABAI,NEW DELHI INDIA","IRON HANDICRAFTS","HEENA SHOPPEE"); INSERT INTO SELLER
VALUES("S02","@kill","SHANU HANDICRAFTS",9870788,"[email protected]","H-NO 401,LANE
NO 1 ,AZAD COLONY,SAHARANPUR,UP INDIA","IRON HANDICRAFTS","SS HANDICRAFTS"); INSERT INTO
SELLER VALUES("S03","@YESH","IRFAN MIRZA",987898,"[email protected]","H-NO
1502,LANE NO 11 ,AZAD COLONY,SAHARANPUR,UP INDIA","BEAUTY PRODUCT ND GROCERIES","MIRZA
GENEREAL STORE"); INSERT INTO SELLER VALUES("S04","@sachin","SACHIN
RANA",9870000,"[email protected]","SHOP NO 123,MANDI SAMITI ROAD,SAHARANPUR,UP
INDIA","ELECTRONICS","SACHIN ELECTRONICS"); SELECT * FROM seller; create table Product( Product_id
VARCHAR(50) primary key, Product_name varchar(200) not null, Colour varchar(50), Size varchar(50), Quantity int, Cost int,
Discount int, Rating FLOAT, Cast varchar(50), Category varchar(50), Brands varchar(50) references Seller(Brands),
Product_description varchar(100) ); ALTER TABLE PRODUCT RENAME COLUMN PRODUCT_ID TO PROD_ID;ALTER
TABLE PRODUCT RENAME COLUMN Quantity TO PROD_QUANTITY; ALTER TABLE PRODUCT RENAME
COLUMN Product_name TO PROD_NAME;ALTER TABLE PRODUCT RENAME COLUMN Discount TO
PROD_DISCOUNT; ALTER TABLE PRODUCT RENAME COLUMN Colour TO PROD_COLOUR;ALTER TABLE
PRODUCT RENAME COLUMN Rating TO PROD_RATING; ALTER TABLE PRODUCT RENAME COLUMN Size TO
PROD_SIZE;ALTER TABLE PRODUCT RENAME COLUMN Cast TO PROD_CAST; ALTER TABLE PRODUCT
RENAME COLUMN Category TO PROD_CATEGORY;ALTER TABLE PRODUCT RENAME COLUMN Brands TO
PROD_BRANDS; ALTER TABLE PRODUCT RENAME COLUMN Product_description TO PROD_DESCRIPTION;
INSERT INTO PRODUCT VALUES("P01","CUP STAND MADE OF IRON","BLACK/WHITE","L-22 : H-10 : W-12
INCHES",200,499,200,8.8,"MADE UP OF IRON","IRON HANDICRAFTS","SS HANDICRAFTS","This is made by strong
iron ,usefull to keep cup,mug,beer glass etc."); INSERT INTO PRODUCT VALUES("P02","AC PANASONIC","WHITE","L30 : H-18 : W-10 INCHES",100,14888,599,9.9,"IRON ,FIBER,PLASTIC","ELECTRONICS","SACHIN
ELECTRONICS","PANASONIC PRESSENTS AC TO KEEP COOL YOUR ROOM,THIS IS AMAZING IN SUMMERS,");
INSERT INTO PRODUCT VALUES("P03","REFRIGERATOR SUPERCOOL","WHITE/GREE/BLACK/RED/BLUE","L-36
: H-80 : W-30 INCHES",50,24999,1099,9.5,"HARD PLASTIC,IRON","ELECTRONICS","SACHIN
ELECTRONICS","PANASONIC PRESSENTS AN REFRIGERATOR, USE FIRST THEN BELIEVE"); INSERT INTO
PRODUCT VALUES("P04","DINING TABLE"," BROWN ","L-60 : H-24 : W-30 INCHES",8,8999,320,8,"SANGWAN
W00D","IRON HANDICRAFTS","SS HANDICRAFTS","This is made by SANGWAN WOOD"); INSERT INTO PRODUCT
VALUES("P05","HIMALYA FACE WASH","GREEN","L-8 : H-1 : W-1
INCHES",5000,110,40,9.5,"PLASTIC,CHEMICAL,NEEM,LIMO","BEAUTY PRODUCTS ND GROCEIES","MIRZA
GENERAL STORE","A FACE WASH MADEUP PF NEEM,LIME WHICH WILL REMOVE DUST,DARK CIRCLE.");
SELECT * FROM product; create table Orders( Order_Id VARCHAR(50) primary key, Status varchar(500), delivery_date
date, order_date date, Expected_delivery varchar(100), Product_id VARCHAR(10) references Product(Product_id ),
Product_name varchar(200) references Product (Product_name ), Product_description varchar(100) references
Product(Product_description), Cust_name varchar(50) references Customer (Cust_name), Cust_MailId varchar(50) references
Customer (Cust_MailId), Cust_Ph_No int references Customer (Cust_Ph_No), Cust_Address varchar(200) references
Customer (Cust_Address), Seller_Name varchar(50)references Seller(Seller_Name), Brands varchar(50) references
Seller(Brands ), COST INT references PRODUCT(COST) ); INSERT INTO ORDERS
VALUES("O01","DELIVERED","2023-02-17","2023-02-8","On FRIDAY","P03","REFRIGERATOR
SUPERCOOL","PANASONIC PRESSENTS AN REFRIGERATOR, USE FIRST THEN
BELIEVE","SHANU","[email protected]",98708383,"AZAD COLONY SAHARANPUR UP INDIA","SACHIN
RANA","SACHIN ELECTRONICS",24999); INSERT INTO ORDERS VALUES("O02","DELIVERED","2023-02-
16","2023-02-07","ON THURSDAY","P03","REFRIGERATOR SUPERCOOL","PANASONIC PRESSENTS AN
REFRIGERATOR, USE FIRST THEN BELIEVE","AFFAN","[email protected]",93587126,"AZAD COLONY
SAHARANPUR UP INDIA","SACHIN RANA","SACHIN ELECTRONICS",24999); INSERT INTO ORDERS
VALUES("O03","BOOKED","2023-02-23","2023-02-15","ON WEDNESDAY","P01","CUT STAND MADE OF
IRON","This is made by strong iron ,usefull to keep cup,mug,beer glass
etc.","SHANU","[email protected]",98708383,"AZAD COLONY SAHARANPUR UP INDIA","UZAIF
MIRZA","HEENA SHOPEE",499); INSERT INTO ORDERS VALUES("O04","BOOKED","2023-02-24","2023-02-9","ON
THURSDAY","P05","HIMALYA FACE WASH","A FACE WASH MADEUP PF NEEM,LIME WHICH WILL REMOVE
DUST,DARK CIRCLE.","Afsha","[email protected]",998877662,"H-A30,Lane no-3 Jogabai Extension ,Zakir
nagar","IRFAN MIRZA","MIRZA GENEREAL STORE",110); SELECT * FROM orders; create table Admin( Admin_UserId
VARCHAR(10) primary key, Admin_Password varchar(50), Admin_Name varchar(50), Admin_Salary int, Admin_Position
varchar(50), Admin_Age int, Admin_gender varchar(1), Admin_Address varchar(100) ); INSERT INTO ADMIN
VALUES("A01","@ANJALI","ANJALI KUMARI",40000,"BACK END ROLE",22,"F","DARRA ALI,MUMBAI"); INSERT
INTO ADMIN VALUES("A02","@RAISY","RAISY RAPHY",44000,"FRONT END ROLE",31,"F","VIKAS
GANJ,MUMBAI"); INSERT INTO ADMIN VALUES("A03","@RAJESHVARI","RAJESHVARI",39000,"FULL STACK
DEVLOPER",28,"F","ZAKIR NAGAR,NEW DELHI"); SELECT * FROM admin; create table category( Category_Id
VARCHAR(50) primary key, Category_Name varchar(100), Category_Descrption varchar(250) ); INSERT INTO
CATEGORY VALUES("CATEGORY_01","IRON HANDICRAFTS","PRODUCTS MADE UP OF IRON AND WOOD");
0 row(s)
affected
0.016
sec
INSERT INTO CATEGORY VALUES("CATEGORY_02","BEAUTY PRODUCTS AND GROCERIES","PRODUCTS
WHICH CAN CONTAIN BEAUTY PRODUCTS AND ALSO HROCERIES"); INSERT INTO CATEGORY
VALUES("CATEGORY_03","ELECTRONICS","PRODUCTS OF ELECTRONIC WHICH CAN RUN BY ELECTRIC");
INSERT INTO CATEGORY VALUES("CATEGORY_04","GARMENTS","CLOTHES/BOOT/BEDSHEET"); INSERT
INTO CATEGORY VALUES("CATEGORY_05","HOME APPLIANCES","DECORATIVE,ELECTRONICS,KITCHEN");
INSERT INTO CATEGORY VALUES("CATEGORY_06","FOOD","VEG/NON VEG/COOCKED/UNCOOCKED");
SELECT * FROM Category; create table SubCategory( Subcategory_Id VARCHAR(50) primary key, Subcategory_Name
varchar(100), Subcategory_description varchar(200), Category_Name varchar(100) references CATEGORY(Category_Name),
Category_Id VARCHAR(50) references category(Category_Id) ); INSERT INTO SubCategory
VALUES("SUB_CATEGORY_001","IRON","PRODUCTS MADE UP OF IRON ","IRON
HANDICRAFTS","CATEGORY_01"); INSERT INTO SubCategory
VALUES("SUB_CATEGORY_002","WOOD","PRODUCTS MADE UP OF WOOD ","IRON
HANDICRAFTS","CATEGORY_01"); INSERT INTO SubCategory VALUES("SUB_CATEGORY_003","ELECTRONIC
KICHTEN ITEM","ELECTRONICSS ","ELECTRONICS","CATEGORY_03"); INSERT INTO SubCategory
VALUES("SUB_CATEGORY_004","CLOTHES","JEANS,SHIRT,T SHIRT,LOWER","GARMENTS","CATEGORY_04");
INSERT INTO SubCategory
VALUES("SUB_CATEGORY_005","BOOT","BOYS/GIRLS/BABIES","GARMENTS","CATEGORY_04"); INSERT INTO
SubCategory VALUES("SUB_CATEGORY_006","DECORATIVE ITEMS OF WOOD","PRODUCTS MADE UP WOOD
","HOME APPLIANCES","CATEGORY_05"); INSERT INTO SubCategory
VALUES("SUB_CATEGORY_007","DECORATIVE","PRODUCTS MADE UP OF IRON ","HOME
APPLIANCES","CATEGORY_05"); SELECT * FROM subcategory; Create Table Cart( Order_Id VARCHAR(10) references
Orders(Order_id), Product_id VARCHAR(10) references Product (Product_id ), Product_name varchar(200) not null references
Product (Product_name ), Cost int references Product (Cost), Product_description varchar(100) references Product
(Product_description ), Seller_Name varchar(50) references Seller (Seller_Name), Brands varchar(50) references Seller
(Brands) ); INSERT INTO Cart VALUES("O01","P03","REFRIGERATOR SUPERCOOL",24999,"PANASONIC
PRESSENTS AN REFRIGERATOR, USE FIRST THEN BELIEVE","SACHIN RANA","SACHIN ELECTRONICS");
INSERT INTO Cart VALUES("O02","P03","REFRIGERATOR SUPERCOOL",24999,"PANASONIC PRESSENTS AN
REFRIGERATOR, USE FIRST THEN BELIEVE","SACHIN RANA","SACHIN ELECTRONICS"); INSERT INTO Cart
VALUES("O03","P01","CUT STAND MADE OF IRON",499,"This is made by strong iron ,usefull to keep cup,mug,beer glass
etc.","UZAIF MIRZA","HEENA SHOPEE"); INSERT INTO Cart VALUES("O04","P05","HIMALYA FACE WASH",110,"A
FACE WASH MADEUP PF NEEM,LIME WHICH WILL REMOVE DUST,DARK CIRCLE.","IRFAN MIRZA","MIRZA
GENEREAL STORE"); SELECT * FROM Cart; create table ShippingDetails( Shipper_Id varchar(10) primary key,
Shipper_Name varchar(100) not null, Shipping_Address varchar(200), Order_Weight varchar(50), Order_Size varchar(50),
Tracking_Number int ); INSERT INTO SHIPPINGDETAILS VALUES("SHIPPER_01","RAHUL KUMAR","H-A30,Lane
no-3 Jogabai Extension ,Zakir nagar","300-GM","6X4",374527); INSERT INTO SHIPPINGDETAILS
VALUES("SHIPPER_02","SLAMAN NABEEL","H-A30,Lane no-3,PADAPPAI,CHEENAI","1KG","12X12",725532);
INSERT INTO SHIPPINGDETAILS VALUES("SHIPPER_03","ADAM AZEES","H-A30,Lane no-3,ALI ROAD
MUMBAI","5KG-GM","80X23",75763); INSERT INTO SHIPPINGDETAILS VALUES("SHIPPER_04","SULEMAN
RANA","H-A30,Lane no-3,BALLU PUR CHOWK DEHRADUN","880 KG","20X30",735382); SELECT * FROM
shippingdetails; create table Reviews( Cust_name varchar(50) references Customer(Cust_name), Cust_MailId varchar(50)
references Customer(Cust_MailId), Rev_Rating float, Product_id VARCHAR(50) references Product(Product_id),
Product_name varchar(50) references Product(Product_name), Rev_Comment varchar(500) ); INSERT INTO REVIEWS
VALUES("SHANU","[email protected]",8.8,"P03","REFRIGERATOR SUPERCOOL","ITS AN AMAZING
REFRIDGERATOR THANKS TO SACHIN ELECTRONICS"); INSERT INTO REVIEWS
VALUES("RAEES","[email protected]",9.9,"P04","REFRIGERATOR SUPERCOOL","ITS AN AMAZING
REFRIDGERATOR THANKS TO SACHIN ELECTRONICS"); INSERT INTO REVIEWS VALUES("SONU
MALIK","[email protected]",8,"P02","HIMALYA FACE WASH","ITS AN AMAZING PRODUCT
THANKS TO MIRZA GENERAL STORE"); INSERT INTO REVIEWS VALUES("HITESH
HIPRARA","[email protected]",9,"P01","CUP STAND","ITS AN AMAZING CUP STAND THANKS TO SS
HANDICRAFTS"); SELECT * FROM REVIEWS; end
CALL PROJECT();