武漢理工大學(xué)-數(shù)據(jù)庫系統(tǒng)原理總復(fù)習(xí)題(完整版含答案)

武漢理工大學(xué)數(shù)據(jù)庫系統(tǒng)原理總復(fù)習(xí)題〔完整版含答案〕1.QuestionsWhatisthepurposeofadatabase?ANSWER：Thepurposeofadatabaseistohelppeopletrackofthings.Whatisthemostcommonlyusedtypeofdatabase?ANSWER:themostcommonlyusedtypeofdatabaseistherelationaldatabase.1.7 Definethetermsdataandinformation.Explainhowthetwotermsdiffer.ANSWER:Dataarerecordedfactsandnumbers. wecannowdefineinformationas:Knowledgederivedfromdata.Datapresentedinameaningfulcontext.Dataprocessedbysumming,ordering,averaging,grouping,comparingorothersimilaroperations.1.10 Whatproblemcanoccurwhenadatabaseisprocessedbymorethanoneuser?ANSWER:Whenmorethanoneuseremploysadatabaseapplication,theseisalwaysthechancethatoneuser'sworkmayinterferewithother's.1.12 Whatisthepurposeofthelargestdatabasesate-commercecompaniessuchasAmazon?ANSWER:Thelargestdatabasesarethosethattrackcustomerbrowserbehavior.〔用來記錄用戶的瀏覽行為的?！?.13 Howdothee-commercecompaniesusethesedatabases?.ANSWER:E-commercecompaniesuseWebactivitydatabasestodeterminewhichitemsonaWebpagearepopularandsuccessfulandwhicharenot.1.14 Howdodigitaldashboardanddataminingapplicationsdifferfromtransactionprocessingapplications?ANSWER:Digitaldashboardsandotherreportingsystemsassesspastandcurrentperformance.Dataminingapplicationspredictfutureperformance.1.15 Explainwhyasmalldatabaseisnotnecessarilysimplerthanalargeone.Whatarethefunctionsofapplicationprograms?ANSWER:Supposedwehave2companywhicharedifferentinsalesbuthavesimilardatabase.Thoughthedifferenceinsale,bothhavethesamekindsofdata,aboutthesamenumberoftablesofdata,andthesamelevelofcomplexityindatarelationships.Onlytheamountofdatavariesfromonetotheother.Thus,althoughadatabaseforasmallbusinessmaybesmall,itisnotnecessarilysimple.1.18 WhatisStructuredQueryLanguage(SQL),andwhyisitimportant?ANSWER:StructuredQueryLanguage(SQL)isaninternationallyrecognizedstandardlanguage.BecauseitcanbeunderstoodbyallcommercialDBMSproducts,indatabaseprocessingandthefactthatdatabaseapplicationstypicallysendSQLstatementstotheDBMSforprocessing.1.19 WhatdoesDBMSstandfor?ANSWER:Thedatabasemanagementsystem.1.20 WhatarethefunctionsoftheDBMS?ANSWER:Itcanbeusedtocreate,process,andadministerthedatabase.1.21 NamethreevendorsofDBMSproducts.ANSWER:IBM,Microsoft,Oracle.1.22 Definethetermdatabase.ANSWER:Adatabaseisaself-describingcollectionofintegratedtables.1.23 Whyisadatabaseconsideredtobeself-describing?ANSWER:Adatabaseisself-describingbecauseitcontainsadescriptionofitself.Thus,databasescontainnotonlytablesofuserdata,butalsotablesofdatathatdescribethatuserdata.1.24 Whatismetadata?Howdoesthistermpertaintoadatabase?ANSWER:databasescontainnotonlytablesofuserdata,butalsotablesofdatathatdescribethatuserdata.Suchdescriptivedataiscalledmetadatabecauseitisdataaboutdata.1.25 Whatadvantageisthereinstoringmetadataintables?ANSWER:Becausemetadataisstoredintables,youcanuseSQLtoqueryit.Thus,bylearninghowtowriteSQLtoqueryusertables,youwillalsolearnhowtowriteSQLtoquerymetadata.1.26 Listthecomponentsofadatabaseotherthanusertablesandmetadata.?Tablesofuserdata?Metadata?Indexes?Storedprocedures?Triggers?Securitydata?Backup/recoverydata1.27 IsMicrosoftAccessaDBMS?Whyorwhynot?ANSWER:No,MicrosoftAccessisnotjustaDBMS.Rather,itisapersonaldatabasesystem:aDBMSplusanapplicationgenerator.BecausealthoughMicrosoftAccesscontainsaDBMSenginethatcreates,processes,andadministersthedatabase,italsocontainsform,report,andquerycomponentsthataretheMicrosoftAccessapplicationgenerator1.37 Listseveralconsequencesofapoorlydesigneddatabase.ANSWER:-TheymayrequireapplicationdeveloperstowriteoverlycomplexandcontrivedSQLtoget–wanteddata.-theymaybedifficulttoadapttonewandchangingrequirements.-theymayfailinsomeotherway.1.38 Explaintwowaysthatadatabasecanbedesignedfromexistingdata.ANSWER:ThefirsttypeofdatabasedesigninvolvesdatabasesthatareconstructedfromexistingData.Asecondwaythatdatabasesaredesignedisforthedevelopmentofnewinformationsystems.1.39 Whatisadatawarehouse?Whatisadatamart?ANSWER:Thedatawarehouseanddatamartdatabasesstoredataspecificallyorganizedforresearchandreportingpurposes,andthesedataoftenareexportedtootheranalyticaltools,suchasSAS’sEnterpriseMiner,IBM'sSPSSDataModeler,orTIBCO'sSpotfireMetrics.1.40 Describethegeneralprocessofdesigningadatabaseforanewinformationsystem.ANSWER:First,theteamcreatesadatamodelfromtherequirementsstatementsandthentransformsthatdatamodelintoadatabasedesign.1.41 Explaintwowaysthatdatabasescanberedesigned.ANSWER:Inthefirst,adatabaseisadaptedtoneworchangingrequirements.Thisprocesssometimesiscalleddatabasemigration.Inthemigrationprocess,tablesmaybecreated,modified,orremoved;relationshipsmaybealtered;dataconstraintsmaybechanged;andsoforth.Thesecondtypeofdatabaseredesigninvolvestheintegrationoftwoormoredatabases.Thistypeofredesigniscommonwhenadaptingorremovinglegacysystems.Itisalsocommonforenterpriseapplicationintegration,whentwoormorepreviouslyseparateinformationsystemsareadaptedtoworkwitheachother.1.42 Whatdoesthetermdatabasemigrationmean?ANSWER:Theprocessofadatabaseisadaptedtoneworchangingrequirements.1.43 Summarizethevariouswaysthatyoumightworkwithdatabasetechnology.ANSWER:Inourcareer,wemayworkwithdatabasetechnologyaseitherauserorasadatabaseadministrator.Asauser,youmaybeaknowledgeworkerwhopreparesreports,minesdata,anddoesothertypesofdataanalysisoryoumaybeaprogrammerwhowritesapplicationsthatprocessthedatabase.Alternatively,youmightbeadatabaseadministratorwhodesigns,constructs,andmanagesthedatabaseitself.UsersareprimarilyconcernedwithconstructingSQLstatementstogetandputthedatatheywant.Databaseadministratorsareprimarilyconcernedwiththemanagementofthedatabase.1.44 Whatjobfunctionsdoesaknowledgeworkerperform?ANSWER:preparingreports,miningdata,anddoingothertypesofdataanalysis.1.45 Whatjobfunctionsdoesadatabaseadministratorperform?ANSWER:designing,constructing,andmanagingthedatabaseitself.1.47 Whatneeddrovethedevelopmentofthefirstdatabasetechnology?ANSWER:Theneedfordataintegrationdrovethedevelopmentofthefirstdatabasetechnology.1.48 WhatareDataLanguage/IandCODASYLDBTG?ANSWER:DataLanguage/I(DL/I)usedhierarchiesortrees(seeAppendixG)torepresentrelationships.Thissubcommitteedevelopedastandarddatamodelthatcametobearitsname—theCODASYLDBTGmodel.Itwasanunnecessarilycomplicatedmodel.Thisdatarelationshipuseddatastructurescallednetworks.1.49 WhowasE.F.Codd?ANSWER:E.F.Coddwasalittle-knownIBMengineerpublishedapaperintheCommunicationsoftheACM3inwhichheappliedtheconceptsofabranchofmathematicscalledrelationalalgebratotheproblemof“shareddatabanks,”asdatabaseswerethenknown.Theresultsofthisworkarenowtherelationalmodelfordatabases,andallrelationaldatabaseDBMSproductsarebuiltonthismodel.1.50 Whatweretheearlyobjectionstotherelationalmodel?1.51 NametwoearlyrelationalDBMSproducts.ANSWER:OracleDatabase,DB2.1.52 WhataresomeofthereasonsforthesuccessofOracleDatabase?ANSWER:1,itwouldrunonjustaboutanycomputerandjustaboutanyoperatingsystem.2,OracleDatabasehad,andcontinuestohave,anelegantandefficientinternaldesign.1.53 NamethreeearlypersonalcomputerDBMSproducts.ANSWER:dBase,R:base,Paradox.1.55 WhatwasthepurposeofOODBMSproducts?StatetworeasonsthatOODBMSproductswerenotsuccessful.ANSWER:TheyweredesignedtomakeiteasytostorethedataencapsulatedinOOPobjects.Thereweretworeasonsfortheirlackofacceptance.First,usinganOODBMSrequiredthattherelationaldatabeconvertedfromrelationalformattoobject-orientedformat.BythetimeOODBMSemerged,billionsuponbillionsofbytesofdatawerestoredinrelationalformatinorganizationaldatabases.NocompanywaswillingtoundergotheexpensivetravailofconvertingthosedatabasestobeabletousethenewOODBMS.Second,object-orienteddatabaseshadnosubstantialadvantageoverrelationaldatabasesformostcommercialdatabaseprocessing.Asyouwillseeinthenextchapter,SQLisnotobjectoriented.Butitworks,andthousandsofdevelopershavecreatedprogramsthatuseit.Withoutademonstrableadvantageoverrelationaldatabases,noorganizationwaswillingtotakeonthetaskofconvertingtheirdatatoOODBMSformat.1.56 WhatcharacteristicofHTTPwasaproblemfordatabaseprocessingapplications?ANSWER:HTTPisastatelessprotocol;aserverreceivesarequestfromauser,processestherequest,andthenforgetsabouttheuserandtherequest.Manydatabaseinteractionsaremultistage.Acustomerviewsproducts,addsoneormoretoashoppingcart,viewsmoreproducts,addsmoretotheshoppingcart,andeventuallychecksout.Astatelessprotocolcannotbeusedforsuchapplications.1.57 WhatisanopensourceDBMSproduct?WhichofthefiveDBMSproductsthatyounamedinansweringReviewQuestion1.36ishistoricallyanopensourceDBMSproduct?ANSWER:theMySQLDBMS.這套試卷沒有1.36題啊。1.58 WhathasbeentheresponseofcompaniesthatsellproprietaryDBMSproductstotheopensourceDBMSproducts?Includetwoexamplesinyouranswer.ANSWER:OneinterestingoutcomeoftheemergenceofopensourceDBMSproductsisthatcompaniesthattypicallysellproprietary(closedsource)DBMSproductsnowofferfreeversionsoftheirproducts.Forexample,MicrosoftnowoffersSQLServer2008R2Express(microsoft.com/express/Database),andOracleCorporationmakesitsOracleDatabase10gExpressEditionavailableforfree.1.59 WhatisXML?WhatcommentdidBillGatesmakeregardingXML?ANSWER:XMLmeansExtensibleMarkupLanguage.BillGatessaidthat“XMListhelingua-francaoftheInternetAge.”1.60 WhatistheNoSQLmovement?NametwoapplicationsthatrelyonNoSQLdatabases.ANSWER:It’stheworkisreallyondatabasesareoftenbasedonXML.TwoapplicationsthatrelyonNoSQLdatabases:FacebookandTwitter.2.1 Whatisabusinessintelligence(BI)system?ANSWER:Applicationreferstothecollectionofcommercialinformation,theintegration,analysisandthetechnicalreports.2.2 Whatisanad-hocquery?ANSWER:ad-hocSQLqueriesishowSQLisusedto“askquestions”aboutthedatainthedatabase.2.3 WhatdoesSQLstandfor,andwhatisSQL?ANSWER:SQLstandforStructuredQuqeryLanguage.SQLisnotacompleteprogramminglanguage,likeJavaorC#.Instead,itiscalledadatasublanguage,becauseithasonlythosestatementsneededforcreatingandprocessingdatabasedataandmetadata.2.4 WhatdoesSKUstandfor?WhatisanSKU?SKUstandforstock-keepingunit.SKUisauniqueidentifierforeachparticularitemthatCapeCoddsells.2.5 SummarizehowdatawerealteredandfilteredincreatingtheCapeCodddataextraction.ANSWER:theORDER_ITEMtablestoresanextractoftheitemspurchasedineachorder.Thereisonerowinthetableforeachiteminanorder,andthisitemisidentifiedbyitsSKU.TheOrderNumberColumninORDER_ITEMrelateseachrowinORDER_ITEMtothecorrespondingOrderNumberintheRETAIL_ORDERtable.SKUidentifiestheactualitempurchasedbyitsstock-keepingunitnumber.Further,theSKUcolumninORDER_ITEMrelateseachrowinORDER_ITEMtoitscorrespondingSKUintheSKU_DATAtable2.6 Explain,ingeneralterms,therelationshipsamongtheRETAIL_ORDER,ORDER_ITEM,andSKU_DATAtables.ANSWER:Ingeneral,RETAIL_ORDER,ORDER_ITEM,andSKU_DATA.TheRETAIL_ORDERtablehasdataabouteachretailsalesorder,theORDER_ITEMtablehasdataabouteachiteminanorder,andtheSKU_DATAtablehasdataabouteachstock-keepingunit(SKU)2.7 SummarizethebackgroundofSQL.ANSWER:SQLwasdevelopedbytheIBMCorporationinthelate1970s.ItwasendorsedasanationalstandardbytheAmericanNationalStandardsInstitute(ANSI)in1986andbytheInternationalOrganizationforStandardization(ISO).2.8 WhatisSQL-92?HowdoesitrelatetotheSQLstatementsinthischapter? ANSWER:SubsequentversionsofSQLwereadoptedin1989and1992.The1992versionissometimesreferredtoasSQL-92,orsometimesasANSI-92SQL.2.9 WhatfeatureshavebeenaddedtoSQLinversionssubsequenttotheSQL-92? ANSWER:AllthenewversionsofSQLsupportforExtensibleMarkupLanguage(XML)andtherearemanyothercommonlanguagefeatures.2.10 WhyisSQLdescribedasadatasublanguage?ANSWER:Becauseithasonlythosestatementsneededforcreatingandprocessingdatabasedataandmetadata.YoucanuseSQLstatementsinmanydifferentways.YoucansubmitthemdirectlytotheDBMSforprocessing.YoucanembedSQLstatementsintoclient/serverapplicationprograms.YoucanembedthemintoWebpages,andyoucanusetheminreportinganddataextractionprograms.YoualsocanexecuteSQLstatementsdirectlyfromVisualStudio.NETandotherdevelopmenttools.2.11 WhatdoesDMLstandfor?WhatareDMLstatements? ANSWER:DMLstandforDataManipulationLanguage,whichisacategoryofSQL.Datamanipulationlanguage(DML)statements,whichareusedforquerying,inserting,modifying,anddeletingdata.2.12 WhatdoesDDLstandfor?WhatareDDLstatements? ANSWER:DDLstandforDatadefinitionlanguage,whichisacategoryofSQL.Datadefinitionlanguage(DDL)statements,whichareusedforcreatingtables,relationships,andotherstructures2.13 WhatistheSQLSELECT/FROM/WHEREframework?ANSWER:ThebasicformofSQLqueriesusestheSQLSELECT/FROM/WHEREframework.Inthisframework:?TheSQLSELECTclausespecifieswhichcolumnsaretobelistedinthequeryresults.?TheSQLFROMclausespecifieswhichtablesaretobeusedinthequery.?TheSQLWHEREclausespecifieswhichrowsaretobelistedinthequeryresults.2.14 ExplainhowMicrosoftAccessusesSQL. ANSWER:Everytimeyouprocessaform,createareport,orrunaqueryMicrosoftAccessgeneratesSQLandsendsthatSQLtoMicrosoftAccess’internalADEDBMSengine.Todomorethanelementarydatabaseprocessing,youneedtouncovertheSQLhiddenbyMicrosoftAccess.Further,onceyouknowSQL,youwillfinditeasiertowriteaquerystatementinSQLratherthanfightwiththegraphicalforms,buttons.2.15 Explainhowenterprise-classDBMSproductsuseSQL ANSWER:Enterprise-classDBMSssuchasMicrosoftSQLServer2008R2,OracleDatabase11g,OracleMySQL5.5,andIBMDB2requirethatyouknowSQL.Withtheseproducts,alldatamanipulationisexpressedusingSQL.2.16TheCapeCoddOutdoorSportssaleextractiondatabasehasbeenmodifiedtoincludetwoadditionaltables,theINVENTORYtableandtheWAREHOUSEtable.Thetableschemasforthesetables,togetherwiththeSKUtable,areasfollows:RETAIL_ORDER(OrderNumber,StoreNumber,StoreZip,OrderMonth,OrderYear,OrderTotal)ORDER_ITEM(OrderNumber,SKU,Quantity,Price,ExtendedPrice)SKU_DATA(SKU,SKU_Description,Department,Buyer)WAREHOUSE(WarehouseID,WarehouseCity,WarehouseState,Manager,Squarefeet)INVENTORY(WarehouseID,SKU,SKU_Description,QuantityOnHand,QuantityOnOrder)ThefivetablesintherevisedCapeCodddatabaseschemaareshowninFigure2-23.ThecolumncharacteristicsfortheWAREHOUSEtableareshowninFigure2-24,andthecolumncharacteristicsfortheINVENTORYtableareshowninFigure2-25.ThedatafortheWAREHOUSEtableareshowninFigure2-26,andthedatafortheINVENTORYtableareshowninFigure2-27.Ifatallpossible,youshouldrunyourSQLsolutionstothefollowingquestionsagainstanactualdatabase.AMicrosoftAccessdatabasenamedCape-Codd.accdbisavailableonourWebsite(pearsonhighered/kroenke)thatcontainsallthetablesanddatafortheCapeCoddOutdoorSportssalesdataextractdatabase.AlsoavailableonourWebsiteareSQLscriptsforcreatingandpopulatingthetablesfortheCapeCodddatabaseinSQLServer,OracleDatabase,andMySQL.2.16 ThereisanintentionalflawinthedesignoftheINVENTORYtableusedintheseexer-cises.ThisflawwaspurposelyincludedintheINVENTORYtablessothatyoucananswersomeofthefollowingquestionsusingonlythattable.ComparetheSKUandINVENTORYtables,anddeterminewhatdesignflawisincludedinINVENTORY.Specifically,whydidweincludeit?Figure2-23TheCapeCoddDatabaseColumnNameType KeyRequiredRemarksWarehouseIDIntegerPrimaryKeyYesSurrogateKeyManagerNONONoNOFigure2-24ColumnCharacteristicsfortheWAREHOUSETableFigure2-25ColumnCharacteristicsfortheINVENTORYTableManagerFigure2-26CapeCoddOutdoorSportsWAREHOUSEDataUseonlytheINVENTORYtabletoanswerReviewQuestions2.17through2.40:2.17 WriteanSQLstatementtodisplaySKUandSKU_Description. SELECT* FROMINVENTORY WHEREColumnname=”SKU”O(jiān)RColumnname=”SKU_Description”;2.18 WriteanSQLstatementtodisplaySKU_DescriptionandSKU. SELECT* FROMINVENTORY WHEREColumnname=”SKU_Description”O(jiān)RColumnname=”SKU”;2.19 WriteanSQLstatementtodisplayWarehouseID. SELECTWarehouseID FROMWAREHOUSE;2.20 WriteanSQLstatementtodisplayuniqueWarehouseIDs. SELECTDISTINCTWarehouseID FROMWAREHOUSE;2.21 WriteanSQLstatementtodisplayallofthecolumnswithoutusingtheSQLasterisk(*)wildcardcharacter.ANSWER:Let’stakethetable“WAREHOUSE”asanexample. SELECTWarehouseID,WarehouseCity,WarehouseState,Manager,SquareFeet FROMWAREHOUSE.2.22 WriteanSQLstatementtodisplayallofthecolumnsusingtheSQLasterisk(*)wildcardcharacter.Let’stakethetable“WAREHOUSE”asanexample. SELECT* FROMWAREHOUSE.2.23 WriteanSQLstatementtodisplayalldataonproductshavingaQuantityOnHandgreaterthan0.SELECT*FROMINVENTORYWHEREQuantityOnHand>0;2.24 WriteanSQLstatementtodisplaytheSKUandSKU_DescriptionforproductshavingQuantityOnHandequalto0.SELECTSKU,SKU_DescriptionFROMINVENTORYWHEREQuantityOnHand=0;2.25 WriteanSQLstatementtodisplaytheSKU,SKU_Description,andWarehouseIDforproductshavingQuantityOnHandequalto0.SorttheresultsinascendingorderbyWarehouseID.SELECTSKU,SKU_Description,WarehouseIDFROMINVENTORYWHEREQuantityOnHand=0ORDERBYWarehouseID;2.26 WriteanSQLstatementtodisplaytheSKU,SKU_Description,andWarehouseIDforproductsthathaveaQuantityOnHandgreaterthan0.SorttheresultsindescendingorderbyWarehouseIDandinascendingorderbySKU.SELECTSKU,SKU_Description,WarehouseIDFROMINVENTORYWHEREQuantityOnHand>0ORDERBYWarehouseIDDESC,SKUASC;2.27 WriteanSQLstatementtodisplaySKU,SKU_Description,andWarehouseIDforallproductsthathaveaQuantityOnHandequalto0andaQuantityOnOrdergreaterthan0.SorttheresultsindescendingorderbyWarehouseIDandinascendingorderbySKU.SELECTSKU,SKU_Description,WarehouseIDFROMINVENTORYWHEREQuantityOnHand=0ANDQuantityOnOrder>0ORDERBYWarehouseIDDESC,SKUASC;2.28 WriteanSQLstatementtodisplaySKU,SKU_Description,andWarehouseIDforallproductsthathaveaQuantityOnHandequalto0oraQuantityOnOrderequalto0.SorttheresultsindescendingorderbyWarehouseIDandinascendingorderbySKU.SELECTSKU,SKU_Description,WarehouseIDFROMINVENTORYWHEREQuantityOnHand=0ORQuantityOnOrder=0ORDERBYWarehouseIDDESC,SKUASC;2.29WriteanSQLstatementtodisplaytheSKU,SKU_Description,WarehouseID,andQuantityOnHandforallproductshavingaQuantityOnHandgreaterthan1andlessthan10.DonotusetheBETWEENkeyword.SELECTSKU,SKU_Description,WarehouseID,QuantityOnHandFROMINVENTORYWHEREQuantityOnHand>1ANDQuantityOnOrder<102.30 WriteanSQLstatementtodisplaytheSKU,SKU_Description,WarehouseID,andQuantityOnHandforallproductshavingaQuantityOnHandgreaterthan1andlessthan10.UsetheBETWEENkeyword.SELECTSKU,SKU_Description,WarehouseID,QuantityOnHandFROMINVENTORYWHEREQuantityOnHandBETWEEN1AND10;2.31WriteanSQLstatementtoshowauniqueSKUandSKU_DescriptionforallproductshavinganSKUdescriptionstartingwith‘Half-dome’.SELECTDISTINCTSKU,SKU_DescriptionFROMINVENTORYWHERESKU_DescriptionLIKE’Half-dome%’;ehouseIDSKUSKU_DescriptionQuantityOnHandQuantityOnOrder100100100Std.ScubaTank,Yellow2500200100100Std.ScubaTank,Yellow10050300100100Std.ScubaTank,Yellow1000400100100Std.ScubaTank,Yellow2000100100200Std.ScubaTank,Magenta20030200100200Std.ScubaTank,Magenta7575300100200Std.ScubaTank,Magenta100100400100200Std.ScubaTank,Magenta2500100101100DiveMask,SmallClear0500200101100DiveMask,SmallClear0500300101100DiveMask,SmallClear300200400101100DiveMask,SmallClear4500100101200DiveMask,MedClear100500200101200DiveMask,MedClear50500300101200DiveMask,MedClear4750400101200DiveMask,MedClear250250100201000Half-DomeTent2100200201000Half-DomeTent10250300201000Half-DomeTent2500400201000Half-DomeTent0250100202000Half-DomeTentVestibule10250200202000Half-DomeTentVestibule1250300202000Half-DomeTentVestibule1000400202000Half-DomeTentVestibule0200100301000LightFlyClimbingHarness300250200301000LightFlyClimbingHarness250250300301000LightFlyClimbingHarness0250400301000LightFlyClimbingHarness0250100302000LockingCarabiner,Oval10000200302000LockingCarabiner,Oval12500300302000LockingCarabiner,Oval500500400302000LockingCarabiner,Oval01000Figure 2-27CapeCoddOutdoorSportsINVENTORYData2.32WriteanSQLstatementtoshowauniqueSKUandSKU_Descriptionforallproductshavingadescriptionthatincludestheword'Climb'.SELECTDISTINCTSKU,SKU_DescriptionFROMINVENTORYWHERESKU_DescriptionLIKE’%Climb%’;2.33 WriteanSQLstatementtoshowauniqueSKUandSKU_Descriptionforallproductshavinga‘d’inthethirdpositionfromtheleftinSKU_Description.SELECTDISTINCTSKU,SKU_DescriptionFROMINVENTORYWHERESKU_DescriptionLIKE’__d%’;2.34 WriteanSQLstatementthatusesalloftheSQLbuilt-infunctionsontheQuantityOn-Handcolumn.Includemeaningfulcolumnnamesintheresult. SELECTSUM(QuantityOnHand)ASQuantitySumAVG(QuantityOnHand)ASQuantityAvgMIN(QuantityOnHand)ASQuantityMinMAX(QuantityOnHand)ASQuantityMaxCOUNT(QuantityOnHand)ASQuantityCountFROMINVENTORY;2.35 ExplainthedifferencebetweentheSQLbuilt-infunctionsCOUNTandSUM.ANSWER:Thebuild-infunctionCOUNTmeansthatcalculatinghowmanynumberarethereinthetable.ButthefunctionSUMmeansthataddingallthenumberinthetablewhichmatchtheconditiontogetthesum.2.36 WriteanSQLstatementtodisplaytheWarehouseIDandthesumofQuantityOnHand,groupedbyWarehouseID.NamethesumTotalItemsOnHandanddisplaytheresultsindescendingorderofTotalItemsOnHand. SELECTWarehouseID,SUM(QuantityOnHand)ASTotalItamsOnHand FROMINVENTORY GROUPBYWarehouseID ORDERBYTotalItemsOnHandDESC;2.37WriteanSQLstatementtodisplaytheWarehouseIDandthesumofQuantityOnHand,groupedbyWarehouseID.OmitallSKUitemsthathave3ormoreitemsonhandfromthesum,andnamethesumTotalItemsOnHandLT3anddisplaytheresultsindescendingorderofTotalItemsOnHandLT3. SELECTWarehouseID,SUM(QuantityOnHand)ASTotalItamsOnHandLT3 FROMINVENTORY GROUPBYWarehouseID ORDERBYTotalItemsOnHandLT3DESC HAVINGSUM(Quantity)<3;2.38WriteanSQLstatementtodisplaytheWarehouseIDandthesumofQuantityOn-HandgroupedbyWarehouseID.OmitallSKUitemsthathave3ormoreitemsonhandfromthesum,andnamethesumTotalItemsOnHandLT3.ShowWarehouseIDonlyforwarehouseshavingfewerthan2SKUsintheirTotalItemesOnHandLT3anddisplaytheresultsindescendingorderofTotalItemsOnHandLT3. SELECTWarehouseID,SUM(QuantityOnHand)ASTotalItamsOnHandLT3,COUNT(SKU) FROMINVENTORY GROUPBYWarehouseID ORDERBYTotalItemsOnHandLT3DESC HAVINGSUM(Quantity)<3ANDCOUNT(SKU)<2;2.39 InyouranswertoReviewQuestion2.39,wastheWHEREclauseortheHAVINGclauseappliedfirst?Why? ANSWER:WHEREclauseappliedfirst.BecauseWHEREclausefilterstherecordsbeforeGROUPBYclause,butHAVINGclausefilterstherecordsafterGROUPBYclause.UseboththeINVENTORYandWAREHOUSEtablestoanswerReviewQuestions2.40through2.52:2.40WriteanSQLstatementtodisplaytheSKU,SKU_Description,andWarehouseID,WarehouseCity,andWarehouseStateforallitemsstoredintheAtlanta,Bangor,orChicagowarehouse.DonotusetheINkeyword.ANSWER: SELECTSKU,SKU_Description,WarehouseID,WarehouseCity,WarehouseStateFROMINVENTORY,WAREHOUSE WHEREWarehouseCity=’Atlanta’ORWarehouseCity=’Chicago’ORWarehouseCity=’Bangor’;2.41WriteanSQLstatementtodisplaytheSKU,SKU_Description,andWarehouseID,WarehouseCity,andWarehouseStateforallitemsstoredintheAtlanta,Bangor,orChicagowarehouse.UsetheINkeyword.SELECTSKU,SKU_Description,WarehouseID,WarehouseCity,WarehouseStateFROMINVENTORY,WAREHOUSE WHEREWarehouseCityIN(‘Atlanta’,’Chicago’,’Bangor’);2.42WriteanSQLstatementtodisplaytheSKU,SKU_Description,WarehouseID,Ware-houseCity,andWarehouseStateofallitemsnotstoredintheAtlanta,Bangor,orChicagowarehouse.DonotusetheNOTINkeyword.SELECTSKU,SKU_Description,WarehouseID,WarehouseCity,WarehouseStateFROMINVENTORY,WAREHOUSE WHEREWarehouseCity=’’;2.43WriteanSQLstatementtodisplaytheSKU,SKU_Description,WarehouseID,Ware-houseCity,andWarehouseStateofallitemsnotstoredintheAtlanta,Bangor,orChicagowarehouse.UsetheNOTINkeyword.SELECTSKU,SKU_Description,WarehouseID,WarehouseCity,WarehouseStateFROMINVENTORY,WAREHOUSE WHEREWarehouseCityNOTIN(‘Atlanta’,’Chicago’,’Bangor’);2.44WriteanSQLstatementtoproduceasinglecolumncalledItemLocationthatcombinestheSKU_Description,thephrase“isinawarehousein”,andWarehouseCity.Donotbeconcernedwithremovingleadingortrailingblanks. SELECTDISTINCTRTRIM(SKU_Description)+’isinawarehouse’+RTRIM(WarehouseCity) FROMINVENTORY,WAREHOUSE;2.45WriteanSQLstatementtoshowtheSKU,SKU_Description,WarehouseIDforallitemsstoredinawarehousemanagedby‘LucilleSmith’.Useasubquery. SELECTSKU,SKU_Descrip