Aasd Sad Feedback Final Exam Semester 2 Part 2

feedbackfinalexamsemister2part2Test:FinalExamSemester2-PartII Reviewyouranswers,feedback,andquestionscores below.Anasterisk(*)indicatesacorrectanswer.PartIIoftheSemester2FinalExamcoversSectio ns10-14ofDatabaseProgrammingwithSQL. Section10 1.TheEMP_HIST_Vviewisnolongerneeded.Whic hstatementshouldyouusetotheremovethisview?MarkforReview(1)PointsDROPemp_hist_v;DELETEemp_hist_v;REMOVEemp_hist_v;DROPVIEWemp_hist_v;(*)Correct2.Youmustcreateaviewthatwhenqueriedwill displaythename,customeridentificationnumber,newbalance,financecharge andcreditlimitofallcustomers.Youissuethisstatement:CREATEORREPLACEVIEWCUST_CREDIT_VASSELECTc.last_name,c.customer_id,a.new_balance ,a.finance_charge,a.credit_limitFROMcustomersc,accountsaWHEREc.account_id=a.account_idWITHREADONLY; WhichtypeofSQLcommandcanbeissuedontheCUST _CREDIT_Vview?MarkforReview (1)PointsUPDATEDELETEINSERTSELECT(*)Correct3.Whichstatementaboutaninlineviewistrue? MarkforReview(1)PointsAninlineviewisaschemaobject.AninlineviewisasubqueryintheFROMclause ,oftennamedwithanalias.(*)Aninlineviewisacomplexview.Page1feedbackfinalexamsemister2part2AninlineviewcanbeusedtoperformDMLopera tions. Correct4.EvaluatethisSELECTstatement:SELECTROWNUM"Rank",customer_id,new_balanceFROM(SELECTcustomer_id,new_balanceFROMcustomer _financeORDERBYnew_balanceDESC)WHEREROWNUM=45; WhichofthefollowingstatementsusingthePART_NA ME_Vviewwillexecutesuccessfully? MarkforReview(1)PointsSELECT*FROMpart_name_v;(*)UPDATEpart_name_vSETcost=cost*1.23WHEREpart_id=56990;DELETEFROMpart_name_vWHEREpart_id=56897;INSERTINTOpart_name_v(part_id,part_name,pr oduct_id,cost)VALUES(857986,'cylinder',8790,3.45);Page52feedbackfinalexamsemister2part2Correct 2.Inordertoqueryadatabaseusingaview,wh ichofthefollowingstatementsapplies?MarkforReview(1)PointsUsespecialVIEWSELECTkeywords.Youcanretrievedatafromaviewasyouwould fromanytable.(*)Youcanneverseealltherowsinthetablethr oughtheview.Thetablesyouareselectingfromcanbeempty, yettheviewstillreturnstheoriginaldatafromthosetables.Incorrect.RefertoSection103.YouadministeranOracledatabase,whichcont ainsatablenamedEMPLOYEES.Luke,adatabaseuser,mustcreateareportthatin cludesthenamesandaddressesofallemployees.YoudonotwanttograntLukeaccess totheEMPLOYEEStablebecauseitcontainssensitivedata.Whichofthefollowing actionsshouldyouperformfirst?MarkforReview(1)PointsCreatethereportforhim.Createaview.(*)Createasubquery.Createanindex.Correct4.EvaluatethisCREATEVIEWstatement:CREATEVIEWemp_viewASSELECTSUM(salary)FROMemployees; Whichstatementistrue? MarkforReview(1)PointsYoucannotupdatedataintheEMPLOYEEStableu singtheEMP_VIEWview.(*)YoucanupdateanydataintheEMPLOYEEStable usingtheEMP_VIEWview.YoucandeleterecordsfromtheEMPLOYEEStable usingtheEMP_VIEWview.YoucanupdateonlytheSALARYcolumnintheEM PLOYEEStableusingtheEMP_VIEWview.Correct5.Whichstatementwouldyouusetoalteraview ?MarkforReview(1)PointsPage53feedbackfinalexamsemister2part2 ALTERVIEWMODIFYVIEWALTERTABLECREATEORREPLACEVIEW(*)Correct6.Whichofthefollowingkeywordscannotbeuse dwhencreatingaview?MarkforReview(1)PointsHAVINGWHEREORDERBYTheyareallvalidkeywordswhencreatingviews .(*)Correct7.Whichkeyword(s)wouldyouincludeinaCREAT EVIEWstatementtocreatetheviewregardlessofwhetherornotthebasetableex ists?MarkforReview(1)PointsFORCE(*)NOFORCEORREPLACEWITHREADONLYCorrect8.WhichstatementabouttheCREATEVIEWstateme ntistrue?MarkforReview(1)PointsACREATEVIEWstatementCANcontainajoinquer y.(*)ACREATEVIEWstatementCANNOTcontainanORDER BYclause.ACREATEVIEWstatementCANNOTcontainafuncti on.ACREATEVIEWstatementCANNOTcontainaGROUP BYclause.Incorrect.RefertoSection109.WhichstatementaboutperformingDMLoperatio nsonaviewistrue?MarkforReview(1)PointsPage54feedbackfinalexamsemister2part2 Youcandeletedatainaviewiftheviewconta instheDISTINCTkeyword.Youcannotmodifydatainaviewiftheviewco ntainsaWHEREclause.Youcannotmodifydatainaviewiftheviewco ntainsagroupfunction.(*)Youcanmodifydatainaviewiftheviewconta insaGROUPBYclause.Correct10.YouadministeranOracledatabase.Jackmana gestheSalesdepartment.Heandhisemployeesoftenfinditnecessarytoquerythe databasetoidentifycustomersandtheirorders.Hehasaskedyoutocreateaview thatwillsimplifythisprocedureforhimselfandhisstaff.Theviewshoul dnotacceptINSERT,UPDATEorDELETEoperations.Whichofthefollowingstatement sshouldyouissue?MarkforReview(1)PointsCREATEVIEWsales_viewAS(SELECTcompanyname,city,orderid,orderdate ,totalFROMcustomers,ordersWHEREcustid=custid)WITHREADONLY;CREATEVIEWsales_view(SELECTc.companyname,c.city,o.orderid,o.ord erdate,o.totalFROMcustomersc,ordersoWHEREc.custid=o.custid)WITHREADONLY;CREATEVIEWsales_viewAS(SELECTc.companyname,c.city,o.orderid,o.ord erdate,o.totalFROMcustomersc,ordersoWHEREc.custid=o.custid);CREATEVIEWsales_viewAS(SELECTc.companyname,c.city,o.orderid,o. orderdate,o.totalFROMcustomersc,ordersoWHEREc.custid=o.custid)WITHREADONLY;(*)Correct Page1of5Test:FinalExamSemester2-PartII Reviewyouranswers,feedback,andquestionscores below.Anasterisk(*)indicatesacorrectanswer.Page55feedbackfinalexamsemister2part2PartIIoftheSemester2FinalExamcoversSectio ns10-14ofDatabaseProgrammingwithSQL. Section10 11.Youcancreateaviewiftheviewsubqueryc ontainsaninlineview.TrueorFalse?MarkforReview(1)PointsTrue(*)FalseIncorrect.RefertoSection1012.YoucreateaviewontheEMPLOYEESandDEPAR TMENTStablestodisplaysalaryinformationperdepartment.Whatwillhappenifyou issuethefollowingstatement:CREATEORREPLACEVIEWsal_deptASSELECTSUM(e.salary)sal,d.department_nameFROMemployeese,departmentsdWHEREe.department_id=d.department_idGROUPBYd.department_nameORDERBYd.department_name;MarkforReview(1)PointsAcomplexviewiscreatedthatreturnsthesum ofsalariesperdepartment,sortedbydepartmentname.(*)Asimpleviewiscreatedthatreturnsthesumo fsalariesperdepartment,sortedbydepartmentname.Acomplexviewiscreatedthatreturnsthesum ofsalariesperdepartment,sortedbydepartmentid.Nothing,asthestatementconstainsanerroran dwillfail.Correct13.Youcannotinsertdatathroughaviewifthe viewincludes______.MarkforReview(1)PointsAWHEREclauseAjoinAcolumnaliasAGROUPBYclause(*)Correct14.Yourmanagerhasjustaskedyoutocreatea reportthatillustratesthesalaryrangeofalltheemployeesatyourcompany. WhichofthefollowingSQLstatementswillcreateaviewcalledSALARY_VUbase dontheemployeelastnames,Page56feedbackfinalexamsemister2part2departmentnames,salaries,andsalarygradesfora llemployees?UsetheEMPLOYEES,DEPARTMENTS,andJOB_GRADEStables.Labelthecolum nsEmployee,Department,Salary,andGrade,respectively.MarkforReview (1)PointsCREATEORREPLACEVIEWsalary_vuASSELECTe.last_name"Employee",d.department_name "Department",e.salary"Salary",j.grade_level"Grade"FROMemployeese,departmentsd,job_gradesjWHEREe.department_idequalsd.department_idANDe. salaryBETWEENj.lowest_salandj.highest_sal;CREATEORREPLACEVIEWsalary_vuASSELECTe.empid"Employee",d.department_name"De partment",e.salary"Salary",j.grade_level"Grade"FROMemployeese,departmentsd,job_gradesjWHEREe.department_id=d.department_idNOTe.salar yBETWEENj.lowest_salandj.highest_sal;CREATEORREPLACEVIEWsalary_vuASSELECTe.last_name"Employee",d.department_name "Department",e.salary"Salary",j.grade_level"Grade"FROMemployeese,departmentsd,job_gradesjWHEREe.department_id=d.department_idANDe.salar yBETWEENj.lowest_salandj.highest_sal;(*)CREATEORREPLACEVIEWsalary_vuAS(SELECTe.last_name"Employee",d.department_nam e"Department",e.salary"Salary",j.grade_level"Grade"FROMemployeesemp,departmentsd,jobgradesjWHEREe.department_id=d.department_idANDe.salar yBETWEENj.lowest_salandj.highest_sal);Correct15.WhatisthepurposeofincludingtheWITHCH ECKOPTIONclausewhencreatingaview?MarkforReview(1)PointsTomakesurethattheparenttable(s)actually existTokeepviewsformbeingqueriedbyunauthorize dpersonsTomakesurethatdataisnotduplicatedinthe viewTomakesurenorowsareupdatedthroughthevi ewthatwillhinderthoserowsfrombeingreturnedbytheview.(*)Correct16.TheEMP_HIST_Vviewisnolongerneeded.Whi chstatementshouldyouusetotheremovethisview?MarkforReview(1)PointsPage57feedbackfinalexamsemister2part2DROPemp_hist_v; DELETEemp_hist_v;REMOVEemp_hist_v;DROPVIEWemp_hist_v;(*)Correct17.Youmustcreateaviewthatwhenqueriedwil ldisplaythename,customeridentificationnumber,newbalance,financecharge andcreditlimitofallcustomers.Youissuethisstatement:CREATEORREPLACEVIEWCUST_CREDIT_VASSELECTc.last_name,c.customer_id,a.new_balance ,a.finance_charge,a.credit_limitFROMcustomersc,accountsaWHEREc.account_id=a.account_idWITHREADONLY; WhichtypeofSQLcommandcanbeissuedontheCUST _CREDIT_Vview?MarkforReview (1)PointsUPDATEDELETEINSERTSELECT(*)Correct18.TheCUSTOMER_FINANCEtablecontainstheseco lumns:CUSTOMER_IDNUMBER(9)NEW_BALANCENUMBER(7,2)PREV_BALANCENUMBER(7,2)PAYMENTSNUMBER(7,2)FINANCE_CHARGENUMBER(7,2)CREDIT_LIMITNUMBER(7) Youexecutethisstatement:SELECTROWNUM"Rank",customer_id,new_balance FROM(SELECTcustomer_id,new_balanceFROMcustomer _finance)WHEREROWNUM