Menu

[solved]-Need Help Questions 4 5 Create View Contains Products Supplier S2029929 Sure Provide Produ Q39085789

I need help with questions 4 and 5

  1. Create a view that contains products from the supplier #S2029929. Be sure to provide all “Product” columns in yourview.
  2. Provide a list of products that are greater than $250. You mustuse the view created in problem #1.
  3. Write a statement that will insert a new row into the producttable. You must use the view created in problem #1.
  4. Write a statement that will modify one of the columns you justcreated in problem #3. You must use the view created in problem#1.
  5. Delete the row created in problem #3. You must use the viewcreated in problem #1.

Here is the database:

CREATE DATABASE OrderEntryBMIS325 GO USE OrderEntryBMIS325 GOCREATE TABLE Customer ( CustNo CHAR(8), CustFirstName VARCHAR(20)CONSTRAINT CustFirstNameRequired NOT NULL, CustLastName VARCHAR(30)CONSTRAINT CustLastNameRequired NOT NULL, CustStreet VARCHAR(50),CustCity VARCHAR(30), CustState CHAR(2), CustZip CHAR(10), CustBalDecimal(12,2) DEFAULT 0, CONSTRAINT PKCustomer PRIMARY KEY (CustNo)) INSERT INTO Customer (CustNo, CustFirstName, CustLastName,CustStreet, CustCity, CustState, CustZip, CustBal) VALUES (‘C0954327′,’Sheri’,’Gordon’,’336 HillSt.’,’Littleton’,’CO’,’80129-5543′,$230.00 ), (‘C1010398′,’Jim’,’Glussman’,’1432 E.Ravenna’,’Denver’,’CO’,’80111-0033′,$200.00 ), (‘C2388597′,’Beth’,’Taylor’,’2396 RafterRd’,’Seattle’,’WA’,’98103-1121′,$500.00 ), (‘C3340959′,’Betty’,’Wise’,’4334 153rdNW’,’Seattle’,’WA’,’98178-3311′,$200.00 ), (‘C3499503′,’Bob’,’Mann’,’1190 LorraineCir.’,’Monroe’,’WA’,’98013-1095′,$0.00 ), (‘C8543321′,’Ron’,’Thompson’,’789 122ndSt.’,’Renton’,’WA’,’98666-1289′,$85.00 ), (‘C8574932′,’Wally’,’Jones’,’411 WebberAve.’,’Seattle’,’WA’,’98105-1093′,$1500.00 ), (‘C8654390′,’Candy’,’Kendall’,’456 PineSt.’,’Seattle’,’WA’,’98105-3345′,$50.00 ), (‘C9128574′,’Jerry’,’Wyatt’,’16212 123rdCt.’,’Denver’,’CO’,’80222-0022′,$100.00 ), (‘C9403348′,’Mike’,’Boren’,’642 CrestAve.’,’Englewood’,’CO’,’80113-5431′,$0.00 ), (‘C9432910′,’Larry’,’Styles’,’9825 S. CrestLane’,’Bellevue’,’WA’,’98104-2211′,$250.00 ), (‘C9543029′,’Sharon’,’Johnson’,’1223 MeyerWay’,’Fife’,’WA’,’98222-1123′,$856.00 ), (‘C9549302′,’Todd’,’Hayes’,’1400 NW88th’,’Lynnwood’,’WA’,’98036-2244′,$0.00 ), (‘C9857432′,’Homer’,’Wells’,’123 MainSt.’,’Seattle’,’WA’,’98105-4322′,$500.00 ), (‘C9865874′,’Mary’,’Hill’,’206McCaffrey’,’Littleton’,’CO’,’80129-5543′,$150.00 ), (‘C9943201′,’Harry’,’Sanders’,’1280 S. HillRd.’,’Fife’,’WA’,’98222-2258′,$1000.00 ); CREATE TABLE Employee (EmpNo CHAR(8), EmpFirstName VARCHAR(20) CONSTRAINTEmpFirstNameRequired NOT NULL, EmpLastName VARCHAR(30) CONSTRAINTEmpLastNameRequired NOT NULL, EmpPhone CHAR(15), EmpEMailVARCHAR(50) CONSTRAINT EmpEMailRequired NOT NULL, SupEmpNo CHAR(8),EmpCommRate DECIMAL(3,3), CONSTRAINT PKEmployee PRIMARY KEY(EmpNo), CONSTRAINT UNIQUEEMail UNIQUE (EmpEMail), CONSTRAINTFKSupEmpNo FOREIGN KEY (SupEmpNo) REFERENCES Employee ) INSERT INTOEmployee (EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEMail,SupEmpNo, EmpCommRate) VALUES ( ‘E1329594′,’Landi’,’Santos’,'(303)789-1234′,’LSantos@bigco.com’,’E8843211′,0.02 ), (‘E8544399′,’Joe’,’Jenkins’,'(303)221-9875′,’JJenkins@bigco.com’,’E8843211′,0.02 ), (‘E8843211′,’Amy’,’Tang’,'(303)556-4321′,’ATang@bigco.com’,’E9884325′,0.04 ), (‘E9345771′,’Colin’,’White’,'(303)221-4453′,’CWhite@bigco.com’,’E9884325′,0.04 ), (‘E9884325′,’Thomas’,’Johnson’,'(303)556-9987′,’TJohnson@bigco.com’,NULL,0.05 ), (‘E9954302′,’Mary’,’Hill’,'(303)556-9871′,’MHill@bigco.com’,’E8843211′,0.02 ), (‘E9973110′,’Theresa’,’Beck’,'(720)320-2234′,’TBeck@bigco.com’,’E9884325′,NULL ); CREATE TABLEOrderTbl ( OrdNo CHAR(8), OrdDate DATE CONSTRAINT OrdDateRequiredNOT NULL, CustNo CHAR(8) CONSTRAINT CustNoRequired NOT NULL, EmpNoCHAR(8), OrdName VARCHAR(50), OrdStreet VARCHAR(50), OrdCityVARCHAR(30), OrdState CHAR(2), OrdZip CHAR(10), CONSTRAINTPKOrderTbl PRIMARY KEY (OrdNo), CONSTRAINT FKCustNo FOREIGN KEY(CustNo) REFERENCES Customer, CONSTRAINT FKEmpNo FOREIGN KEY(EmpNo) REFERENCES Employee ) INSERT INTO OrderTbl (OrdNo, OrdDate,CustNo, EmpNo, OrdName, OrdStreet, OrdCity, OrdState, OrdZip)VALUES ( ‘O1116324′,’1-23-2013′,’C0954327′,’E8544399′,’SheriGordon’,’336 Hill St.’,’Littleton’,’CO’,’80129-5543′ ), (‘O1231231′,’1-23-2013′,’C9432910′,’E9954302′,’Larry Styles’,’9825S. Crest Lane’,’Bellevue’,’WA’,’98104-2211′ ), (‘O1241518′,’2-10-2013′,’C9549302′,NULL,’Todd Hayes’,’1400 NW88th’,’Lynnwood’,’WA’,’98036-2244′ ), (‘O1455122′,’1-9-2013′,’C8574932′,’E9345771′,’Wally Jones’,’411Webber Ave.’,’Seattle’,’WA’,’98105-1093′ ), (‘O1579999′,’1-5-2013′,’C9543029′,’E8544399′,’Tom Johnson’,’1632Ocean Dr.’,’Des Moines’,’WA’,’98222-1123′ ), (‘O1615141′,’1-23-2013′,’C8654390′,’E8544399′,’Candy Kendall’,’456Pine St.’,’Seattle’,’WA’,’98105-3345′ ), (‘O1656777′,’2-11-2013′,’C8543321′,NULL,’Ron Thompson’,’789 122ndSt.’,’Renton’,’WA’,’98666-1289′ ), (‘O2233457′,’1-12-2013′,’C2388597′,’E9884325′,’Beth Taylor’,’2396Rafter Rd’,’Seattle’,’WA’,’98103-1121′ ), (‘O2334661′,’1-14-2013′,’C0954327′,’E1329594′,’Mrs. RuthGordon’,’233 S. 166th’,’Seattle’,’WA’,’98011′ ), (‘O3252629′,’1-23-2013′,’C9403348′,’E9954302′,’Mike Boren’,’642Crest Ave.’,’Englewood’,’CO’,’80113-5431′ ), (‘O3331222′,’1-13-2013′,’C1010398′,NULL,’Jim Glussman’,’1432 E.Ravenna’,’Denver’,’CO’,’80111-0033′ ), (‘O3377543′,’1-15-2013′,’C9128574′,’E8843211′,’Jerry Wyatt’,’16212123rd Ct.’,’Denver’,’CO’,’80222-0022′ ), (‘O4714645′,’1-11-2013′,’C2388597′,’E1329594′,’Beth Taylor’,’2396Rafter Rd’,’Seattle’,’WA’,’98103-1121′ ), (‘O5511365′,’1-22-2013′,’C3340959′,’E9884325′,’Betty White’,’4334153rd NW’,’Seattle’,’WA’,’98178-3311′ ), (‘O6565656′,’1-20-2013′,’C9865874′,’E8843211′,’Mr. Jack Sibley’,’166E. 344th’,’Renton’,’WA’,’98006-5543′ ), (‘O7847172′,’1-23-2013′,’C9943201′,NULL,’Harry Sanders’,’1280 S.Hill Rd.’,’Fife’,’WA’,’98222-2258′ ), (‘O7959898′,’2-19-2013′,’C8543321′,’E8544399′,’Ron Thompson’,’789122nd St.’,’Renton’,’WA’,’98666-1289′ ), (‘O7989497′,’1-16-2013′,’C3499503′,’E9345771′,’Bob Mann’,’1190Lorraine Cir.’,’Monroe’,’WA’,’98013-1095′ ), (‘O8979495′,’1-23-2013′,’C9865874′,NULL,’HelenSibley’,’206McCaffrey’,’Renton’,’WA’,’98006-5543′ ), (‘O9919699′,’2-11-2013′,’C9857432′,’E9954302′,’Homer Wells’,’123Main St.’,’Seattle’,’WA’,’98105-4322′ ); CREATE TABLE Product (ProdNo CHAR(8), ProdName VARCHAR(50) CONSTRAINT ProdNameRequiredNOT NULL, ProdMfg VARCHAR(20) CONSTRAINT ProdMfgRequired NOT NULL,SuppNo CHAR(8), ProdQOH INTEGER DEFAULT 0, ProdPrice DECIMAL(12,2)DEFAULT 0, ProdNextShipDate DATE, CONSTRAINT PKProduct PRIMARY KEY(ProdNo) ) INSERT INTO Product (ProdNo, ProdName, ProdMfg, SuppNo,ProdQOH, ProdPrice, ProdNextShipDate) VALUES ( ‘P0036566′,’17 inchColor Monitor’,’ColorMeg Inc.’,’S2029929′,12,$169.00,’2-20-2013′ ),( ‘P0036577′,’19 inch Color Monitor’,’ColorMegInc.’,’S2029929′,10,$319.00,’2-20-2013′ ), ( ‘P1114590′,’R3000Color Laser Printer’,’Connex’,’S3399214′,5,$699.00,’1-22-2013′ ), (‘P1412138′,’10 Foot Printer Cable’,’Ethlite’,’S4290202′,100,$12.00,NULL ), ( ‘P1445671′,’8-Outlet SurgeProtector’,’Intersafe’,’S4298800′,33,$14.99, NULL ), (‘P1556678′,’CVP Ink Jet ColorPrinter’,’Connex’,’S3399214′,8,$99.00,’1-22-2013′ ), (‘P3455443′,’Color Ink JetCartridge’,’Connex’,’S3399214′,24,$38.00,’1-22-2013′ ), (‘P4200344′,’36-Bit Color Scanner’,’UVComponents’,’S4420948′,16,$199.99,’1-29-2013′ ), (‘P6677900′,’Black Ink Jet Cartridge’,’Connex’,’S3399214′,44,$25.69,NULL ), ( ‘P9995676′,’Battery Back-upSystem’,’Cybercx’,’S5095332′,12,$89.00,’2-1-2013′ ); CREATE TABLEOrdLine ( OrdNo CHAR(8), ProdNo CHAR(8), Qty INTEGER DEFAULT 1,CONSTRAINT PKOrdLine PRIMARY KEY (OrdNo, ProdNo), CONSTRAINTFKOrdNo FOREIGN KEY (OrdNo) REFERENCES OrderTbl ON DELETE CASCADE,CONSTRAINT FKProdNo FOREIGN KEY (ProdNo) REFERENCES Product )INSERT INTO OrdLine (OrdNo, ProdNo, Qty) VALUES (‘O1116324′,’P1445671’,1 ), ( ‘O1231231′,’P0036566’,1 ), (‘O1231231′,’P1445671’,1 ), ( ‘O1241518′,’P0036577’,1 ), (‘O1455122′,’P4200344’,1 ), ( ‘O1579999′,’P1556678’,1 ), (‘O1579999′,’P6677900’,1 ), ( ‘O1579999′,’P9995676’,1 ), (‘O1615141′,’P0036566’,1 ), ( ‘O1615141′,’P1445671’,1 ), (‘O1615141′,’P4200344’,1 ), ( ‘O1656777′,’P1445671’,1 ), (‘O1656777′,’P1556678’,1 ), ( ‘O2233457′,’P0036577’,1 ), (‘O2233457′,’P1445671’,1 ), ( ‘O2334661′,’P0036566’,1 ), (‘O2334661′,’P1412138’,1 ), ( ‘O2334661′,’P1556678’,1 ), (‘O3252629′,’P4200344’,1 ), ( ‘O3252629′,’P9995676’,1 ), (‘O3331222′,’P1412138’,1 ), ( ‘O3331222′,’P1556678’,1 ), (‘O3331222′,’P3455443’,1 ), ( ‘O3377543′,’P1445671’,1 ), (‘O3377543′,’P9995676’,1 ), ( ‘O4714645′,’P0036566’,1 ), (‘O4714645′,’P9995676’,1 ), ( ‘O5511365′,’P1412138’,1 ), (‘O5511365′,’P1445671’,1 ), ( ‘O5511365′,’P1556678’,1 ), (‘O5511365′,’P3455443’,1 ), ( ‘O5511365′,’P6677900’,1 ), (‘O6565656′,’P0036566’,10 ), ( ‘O7847172′,’P1556678’,1 ), (‘O7847172′,’P6677900’,1 ), ( ‘O7959898′,’P1412138’,5 ), (‘O7959898′,’P1556678’,5 ), ( ‘O7959898′,’P3455443’,5 ), (‘O7959898′,’P6677900’,5 ), ( ‘O7989497′,’P1114590’,2 ), (‘O7989497′,’P1412138’,2 ), ( ‘O7989497′,’P1445671’,3 ), (‘O8979495′,’P1114590’,1 ), ( ‘O8979495′,’P1412138’,1 ), (‘O8979495′,’P1445671’,1 ), ( ‘O9919699′,’P0036577’,1 ), (‘O9919699′,’P1114590’,1 ), ( ‘O9919699′,’P4200344’,1 ); CREATETABLE Supplier ( SuppNo CHAR(8), SuppName VARCHAR(30) CONSTRAINTSuppNameRequired NOT NULL, SuppEMail VARCHAR(50), SuppPhoneCHAR(14), SuppURL VARCHAR(100), SuppDiscount DECIMAL(3,3),CONSTRAINT PKSupplier PRIMARY KEY (SuppNo) ); INSERT INTO supplier(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)VALUES(‘S2029929′,’ColorMeg,Inc.’,’custrel@colormeg.com’,'(720)444-1231′,’www.colormeg.com’,0.10);INSERT INTO supplier(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)VALUES(‘S3399214′,’Connex’,’help@connex.com’,'(206)432-1142′,’www.connex.com’,0.12);INSERT INTO supplier(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)VALUES(‘S4290202′,’Ethlite’,’ordering@ethlite.com’,'(303)213-2234′,’www.ethlite.com’,0.05);INSERT INTO supplier(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)VALUES(‘S4298800′,’Intersafe’,’orderdesk@intersafe.com’,'(512)443-2215′,’www.intersafe.com’,0.10);INSERT INTO supplier(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)VALUES(‘S4420948′,’UVComponents’,’custserv@uvcomponents.com’,'(303)321-0432′,’www.uvcomponents.com’,0.08);INSERT INTO supplier(SuppNo,SuppName,SuppEmail,SuppPhone,SuppURL,SuppDiscount)VALUES(‘S5095332′,’Cybercx’,’orderhelp@cybercx.com’,'(212)324-5683′,’www.cybercx.com’,0.00);CREATE TABLE Purchase ( PurchNo CHAR(8), PurchDate DATE CONSTRAINTPurchDateRequired NOT NULL, SuppNo CHAR(8) CONSTRAINTSuppNo2Required NOT NULL, PurchPayMethod CHAR(6) DEFAULT ‘PO’,PurchDelDate DATE, CONSTRAINT PKPurchase PRIMARY KEY (PurchNo),CONSTRAINT SuppNoFK2 FOREIGN KEY (SuppNo) REFERENCES Supplier );INSERT INTO purchase(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)VALUES(‘P2224040′,’3-Feb-2013′,’S2029929′,’Credit’,’8-Feb-2013′);INSERT INTO purchase(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)VALUES(‘P2345877′,’3-Feb-2013′,’S5095332′,’PO’,’11-Feb-2013′);INSERT INTO purchase(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)VALUES(‘P3249952′,’4-Feb-2013′,’S3399214′,’PO’,’9-Feb-2013′);INSERT INTO purchase(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)VALUES(‘P3854432′,’3-Feb-2013′,’S4290202′,’PO’,’8-Feb-2013′);INSERT INTO purchase(PurchNo,PurchDate,SuppNo,PurchPayMethod,PurchDelDate)VALUES(‘P9855443′,’7-Feb-2013′,’S4420948′,’PO’,’15-Feb-2013′);CREATE TABLE PurchLine ( PurchNo CHAR(8), ProdNo CHAR(8), PurchQtyINTEGER DEFAULT 1 CONSTRAINT PurchQtyRequired NOT NULL,PurchUnitCost DECIMAL(12,2), CONSTRAINT PKPurchLine PRIMARY KEY(PurchNo, ProdNo), CONSTRAINT FKPurchNo FOREIGN KEY (PurchNo)REFERENCES Purchase ON DELETE CASCADE, CONSTRAINT FKProdNo2 FOREIGNKEY (ProdNo) REFERENCES Product ); INSERT INTO purchline (PurchNo,ProdNo, PurchQty, PurchUnitCost)VALUES(‘P2224040′,’P0036566’,10,100.00); INSERT INTO purchline(PurchNo, ProdNo, PurchQty, PurchUnitCost)VALUES(‘P2224040′,’P0036577’,10,200.00); INSERT INTO purchline(PurchNo, ProdNo, PurchQty, PurchUnitCost)VALUES(‘P2345877′,’P9995676’,10,45.00); INSERT INTO purchline(PurchNo, ProdNo, PurchQty, PurchUnitCost)VALUES(‘P3249952′,’P1114590’,15,450.00); INSERT INTO purchline(PurchNo, ProdNo, PurchQty, PurchUnitCost)VALUES(‘P9855443′,’P4200344’,15,99.00);

Expert Answer

Answer to I need help with questions 4 and 5 Create a view that contains products from the supplier # S2029929. Be sure to provide… . . .

OR


Leave a Reply

Your email address will not be published. Required fields are marked *

See what happens after you have accessed solutions.

We are an established writing agency offering a variety of homework related solutions.

 

Place New Order Now

Once your purchase clears after clicking the purchase button, you will get the download link on the confirmation page and its mirror on your email for convenience.