Consider a wholesale database with the following data: address (Not Null) email
ID: 3754070 • Letter: C
Question
Consider a wholesale database with the following data: address (Not Null) email (Unique) vendor idname (PK) (Unique) Not Null) (Not Null) WalmartSupermarket 9191 W Flagler St service@ walmart.com (786) 801-5704 Target Superarket 10101 W Flagler St servicel@ target.com(305) 894-2938 CVS Macy'sDepartment 1205 NW 107th Ave sales@ macys.com JCPenney Department 1603 NW 107th Ave service@ macys.com305) 477-1786 Walgreens Pharmacy 10700 W Flagler St contact@walgreens.com 305) 424-1140 Pharmacy 1549 SW 107th Ave contact@evs.com (305) 220-0147 (305) 594-6300 brand idname (PK) contact phone contact email (Unique) contract year Not Null (Not Null (786) 801-1234 george@greatval.com 2008 2010 2010 2015 Worthington non-exclusie (955) 812-7462 jke@ worthington.com 2016 non-exclusive 305) 315-3700 cindy@botanics.com2008 Gold Emblem non-exclusive (265) 213-7132 mindygoldembl.com 2005 Great Value exclusive non-exclusive 360) 516-9897 jcequate.com non-exclusive 776) 636-964 eric@radiance.com 953) 474-8995 andy@alfani.com product idname (Not Null)category (Not Null) brand (FK) (PK) Toothpaste Multivitamin Supermarket Jacket T-shirt Supplements Aloe PharmacyExplanation / Answer
Task1 -: Create database wholesale;
Query 1-:
i) create table vendor(venor_id INT primary key not null,name TEXT not null,category TEXT not null,address CHAR(50) not null,email text unique,phone_number text not null);
ii) create table brand(brand_id INT primary key not null,name TEXT not null,license TEXT not null, contact_phone text unique,contact_email text,contact_year text not null);
iii) create table product(product_id INT primary key not null,name TEXT not null,category TEXT not null,brand int REFERENCES brand(brand_id));
iv) create table supply(product_id int REFERENCES product(product_id),venor_id INT REFERENCES vendor(venor_id),selling_price int, primary_key(product_id,venor_id));
Query 2:-
insert into vendor(venor_id,name,category,address,email,phone_number)values(1,'Walmart','Supermarket','9191 W Flagler St','service@walmart.com','(786)801-5704'),
(2,'Target','Supermarket','10101 W Flagler St','service@target.com','(305)894-2938')
,(3,'CVS','Pharmacy','1549 SW 107th Ave','contact@cvs.com','(305)220-0147')
,(4,'Macy's','Department','1205 NW 107th Ave','sales@macys.com','(305)594-6300')
,(5,'JCPenney','Department','1603 NW 107th Ave','service@macys.com','(305)477-1786')
,(6,'Walgreens','Pharmacy','10700 W Flagler St','contact@walgreens.com','(305)424-1140');
insert into brand(brand_id,name,license,contact_phone,contact_email,contact_year)values
(1,'Great Value','exclusive','(786)801-1234','george@greatval.com''2008'),
(2,'Equte','non-exclusive','(360)516-9897','jace@equate.com','2010'),
(3,'Radiance','non-exclusive','(776)636-9641','eric@radiance.com,'2010'),
(4,'Alfani','exclusive','(953)474-8995','sandy@alfani.com','2015'),
(5,'Worthington','non-exclusive','(955)812-7462','jake@worthington.com','2016'),
(6,'Botanics','non_exclusive','(305)315-3700','cindy@botanics.com','2008'),
(7,'Gold Emblem','non-exclusive','(265)213-7132','mindy@goldembl.com','2005');
insert into product(product_id,name,category,brand)values(1,'Toothpaste','Supermarket',1),
(2,'Multivitamin','Supermarket',2),
(3,'Shampoo','Supermarket',2),
(4,'Jacket','Department',4),
(5,'T-shirt','Department',5),
(6,'Supplements','Pharmacy',6),
(7,'Aloe','Pharmacy',6)
,(8,'Popcorn','Pharmacy',7);
insert into supply(product_id,vendor_id,selling_price)values
(1,1,4),
(2,2,5),
(3,2,12),
(3,1,5),
(4,5,30),
(5,4,12),
(5,5,10),
(6,3,0),
(6,6,11),
(7,3,16);
Task 2:-
Query 1:-
insert into supply(product_id,vendor_id,selling_price)values
(2,1,10),
(8,3,4),
(8,6,0),
(7,6,15);
Query 2:-
delete from supply where selling_price=0;
----------------------------------------------------The End-------------------------------------------------------