as ate a database usin Consider a wholesale database with the following data end
ID: 3750936 • Letter: A
Question
as ate a database usin Consider a wholesale database with the following data endor: vendor id name (PK) address (Not Null) email (Unique) category (Not Null) phone_number (Unique) (Not Null) Walmart TagetSupermarket 10101 W Flagler St service@target.com Supermarket 9191 W Flagler St VS Pharmacy 1549 SW 107th Ave contact@cvs.com Macy's Departmen 1205 NW 107th Ave sales@macys.com JCPenney Walgreens service@walmart.com (786) 801-5704 (305) 894-2938 (305) 220-0147 (305) 594-6300 (305) 477-1786 @walgreens.com (305) 424-1140 4 ney Department 1603 N W 107th Ave service@ macys.com 6 10700 W Flagler St contact rand brand idname (PK) license contact _phone (Unique) contact email contract_year (Not Null) (Not Null) Great Value Equate Radiance Alfani Worthington non-exclusive 955) 812-7462 ake@worthington.com 2016 Botanics Gold Emblem non-exclusive (265) 213-7132 exclusive non-exclusive (360) 516-9897 jace@equate.com non-exclusive (776) 636-9641eric@radiance.com exclusive (786) 801-1234 george@greatval.com 2008 2010 2010 2015 4 (953) 474-8995sandy@alfani.com 6 non-exclusive (305) 315-3700 cindy@botanics.com 2008 mindy@goldembl. com 2005Explanation / 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;
Query 3-:
alter table product add column production_cost int;
Query 4-:
update supply set selling_price=20 where product_id=4 and vendor_id=5;
Query 5-:
alter table products add foreign key(brand) REFERENCES brand(brand_id) on delete restrict;
Query 6-:
alter table supply alter column selling_price Not null;
Qury 7-:
Copy (Select * From Supply) To 'Supply.csv';
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.