Project 1
Customer Table :
CREATE TABLE c u s → m e r
)
ALTER TABLE c u s → m e r
ADD PRIMARY KEY (C u s → m e r i d
ADD KEY C a r t i d C a r t i d
ALTER TABLE c u s → m e r
ADD CONSTRAINT c u s → m e r i b f k 1 C a r t i d c a r t C a r t i d
INSERT INTO c u s → m e r C u s → m e r i d N a m e e m a i l ≥ n d e r d o b c i t y ∂ i v e r y A d d r e s s c o n t a c t C u r r e n t or d e r P r e v O r d e r C a r t i d
('0023pkr', 'Aishwarya', '[email protected]', 'female', '2003-11-14 15:23:33', 'mumbai', 'dadar', 888859944, 'Dress', 'lehanga', 'c_105'),
('B12kr', 'Abhi', '[email protected]', 'male', '1995-11-02 15:19:36', 'Bangalore', 'bangalore', 988849445, 'Headphone', 'Shirts', 'c_103'),
('G_235', 'Ganesh', '[email protected]', 'male', '2022-11-05 08:11:53', 'Pune', 'Hadapsar Pune', 456777653, 'T_Shirt', 'Cap', 'c_102'),
('Tj402', 'Vankatesh', 'vanki@[email protected]', 'male', '2002-11-21 15:19:36', 'Chennai', 'chennai', 78906483, 'Homethreater', 'sounds', 'c_104'),
('V_Patil', 'Vaibhav', '[email protected]', 'Male', '2000-11-12 12:41:53', 'Nanded', 'Latur', 845905668, 'Macbook', 'HP Laptop', 'c_101');
Cart Table :
CREATE TABLE c a r t
);
ALTER TABLE c a r t
ADD PRIMARY KEY (C a r t i d
INSERT INTO c a r t C a r t i d I t e m q u a n t i t y T o t a l cos t
('c_101', 20, 3000),
('c_102', 10, 1200),
('c_103', 2, 200),
('c_104', 5, 2000),
('c_105', 10, 70000);
Cart_Item Table :
CREATE TABLE c a r t i t e m
);
ALTER TABLE c a r t i t e m
ADD PRIMARY KEY (C a r t i d P r o d u c t i d
ADD KEY P r o d u c t i d P r o d u c t i d
ALTER TABLE c a r t i t e m
ADD CONSTRAINT c a r t i t e m i b f k 1 C a r t i d c a r t C a r t i d
ADD CONSTRAINT c a r t i t e m i b f k 2 P r o d u c t i d ∏ u c t P r o d u c t i d
ADD CONSTRAINT c a r t i t e m i b f k 3 C a r t i d c a r t C a r t i d
INSERT INTO c a r t i t e m C a r t i d P r o d u c t i d D a t e A d d e d p u r c h a s e d i t e m q u a n t i t y
('c_101', 'P_3', '2022-11-07', 'NO', 7),
('c_102', 'P_1', '2022-11-22', 'y', 3),
('c_103', 'P_1', '2022-10-19', 'Y', 5),
('c_104', 'P_3', '2022-11-03', 'Y', 6),
('c_105', 'P_1', '2022-11-01', 'NO', 3);
Category Table :
CREATE TABLE c a t e g or y
);
ALTER TABLE c a t e g or y
ADD PRIMARY KEY (c a t e g or y i d
ADD KEY P r o d u c t i d P r o d u c t i d
ALTER TABLE c a t e g or y
ADD CONSTRAINT c a t e g or y i b f k 1 P r o d u c t i d ∏ u c t P r o d u c t i d
INSERT INTO c a t e g or y c a t e g or y i d c a t e g or y n a m e P r o d u c t i d ⊂ C a t e g or y
('cat_285', 'Accessories', 'P_1', 'Headphones'),
('cat_288', 'Laptops', 'P_1', 'Apple'),
('cat_291', 'Accessories', 'P_3', 'Phones'),
('cat_299', 'Phones', 'P_3', 'Vivo'),
('cat_301', 'Fashion', 'P_3', 'Mens');
Orders Table :
CREATE TABLE or d e r s
);
ALTER TABLE or d e r s
ADD PRIMARY KEY (or d e r i d
ADD KEY c u s → m e r i d c u s → m e r i d
ALTER TABLE or d e r s
ADD CONSTRAINT or d e r s i b f k 1 c u s → m e r i d c u s → m e r C u s → m e r i d
INSERT INTO or d e r s or d e r i d s t a t u s or d e r d a t e ∂ i v e r y d a t e c u s → m e r i d
('02341mrk', 'shipping', '2022-11-05', '2022-11-08', 'V_Patil'),
('09hjwe3', 'delivered', '2022-11-01', '2022-11-05', '0023pkr'),
('1567o3d', 'delivered', '2022-11-02', '2022-11-04', 'G_235'),
('klr892', 'dileverd soon', '2022-11-04', '2022-11-06', 'B12kr'),
('ormh26', 'shiiping', '2022-11-02', '2022-11-07', 'Tj402');
Payment Table :
CREATE TABLE p a y m e n t
);
ALTER TABLE p a y m e n t
ADD PRIMARY KEY (p a y m e n t i d
ADD KEY C u s → m e r i d C u s → m e r i d
ADD KEY C a r t i d C a r t i d
ALTER TABLE p a y m e n t
ADD CONSTRAINT p a y m e n t i b f k 1 C u s → m e r i d c u s → m e r C u s → m e r i d
ADD CONSTRAINT p a y m e n t i b f k 2 C a r t i d c a r t C a r t i d
INSERT INTO p a y m e n t p a y m e n t i d p a y m e n t d a t e P a y m e n t t y p e C u s → m e r i d C a r t i d
('0023449', '2022-11-03', 'reference-0394j34', 'B12kr', 'c_104'),
('0294738484', '2022-11-01', 'UPI', '0023pkr', 'c_102'),
('488443565', '2022-11-02', 'phonepay', 'G_235', 'c_103'),
('4930980', '2022-11-04', 'bharatPay', 'V_Patil', 'c_101'),
('9849344', '2022-11-04', 'upi', 'Tj402', 'c_104');
Product Table :
CREATE TABLE ∏ u c t
);
ALTER TABLE ∏ u c t
ADD PRIMARY KEY (P r o d u c t i d
ADD KEY s e l ≤ r i d s e l ≤ r i d
ALTER TABLE ∏ u c t
ADD CONSTRAINT ∏ u c t i b f k 1 s e l ≤ r i d s e l ≤ r s e l ≤ r i d
INSERT INTO ∏ u c t P r o d u c t i d N a m e s e l ≤ r i d s i z e q u a n t i t y cos t d i s c o u n t p l a c e f or ∂ i v e r y i s F e a t u r e d r a t ∈ g r e v i e w s i s O n S c a ≤ C a t e g or y i d b r and d e s c r i p t i o n t a g s r e l a t e d ∏ u c t
('Hag3943r', 'Rebion Goggals', 'red-black', 'S123_KR', 0, 1, 2000, 300, 'Latur', 'yes', 5, 4, 'yes', 'cat_291', 'Rebion', 'sunlight glasses', 'goggles', 'yellow glasses'),
('krt455', 'T_Shirt', 'Yellow', 'S111_MH', 42, 2, 3000, 200, 'Nanded', '', 4, 8, 'yes', 'Fashion', 'being human', 'T_shirt with cap', '', 'being humans jeans'),
('P_1', 'Macbook', 'black', 'S123_KR', 14, 1, 120000, 7000, 'pune', 'yes', 5, 40, 'very good product', 'C_301', 'Apple', 'Product is very branded and good', 'Laptop', 'IPad'),
('P_3', 'Vivo_30', 'silver', 'S111_MH', 20, 2, 40000, 2000, 'Latur', 'Welll feat', 7, 20, 'well developed', 'Phones', 'Vivo', 'Very good phone camera 64 ram 8 gb', 'vivo', 'vivo-28'),
('P_490g', 'headphone', 'white', 'S111_MH', 0, 3, 700, 110, 'pune', 'yes', 5, 5, 'yes', 'cat_285', 'realme', 'white headphones', 'realme', 'realme ipods');
Seller Table :
CREATE TABLE s e l ≤ r
);
ALTER TABLE s e l ≤ r
ADD PRIMARY KEY (s e l ≤ r i d
INSERT INTO s e l ≤ r s e l ≤ r i d N a m e M o b i ≤ n o p a s s w or d P r o d u c t C a t e g or y
('er455k', 'Jully', 59545554, 'li9345n', 'Accessories'),
('kjhkj54', 'Danni', 345665656, 'ggj54t02', 'Laptop'),
('S-66jd', 'Javed', 483595847, 'ghd3435j', 'fashion'),
('S111_MH', 'Abhi', 8549505, 'V4590nd', 'Accessories'),
('S123_KR', 'Vinay', 84564865, '90hf3e', 'Phones');
Some Queries :
If customer wants to see details of present in cart.
select * from product where product_id in( select product_id from Cart_item where (Cart_id in ( select Cart_id from Customer where Customer_id='cid100' )) and purchased='NO');
If customer want to see history of orders.
select product_id,item_quantity from Cart_item where (purchased='Y' and Cart_id in (select Cart_id from customer where Customer_id='V_Patil'));
If customer want to see total price fir product in cart.
select sum(item_quantity * cost) total_payable from product p join cart_item c on p.product_id=c.product_id where c.product_id in (select product_id from cart_item
where cart_id in(select Cart_id from customer where customer_id='V_Patil') );