资源描述：
1,数据库管理系统DatabaseManagementSystems,Chapter5AdvancedQueries第五章高级查询和子查询,2,Tables,3,Organization,HarderQuestionsSubqueriesNotIn,LEFTJOINUNION,MultipleJOINcolumns,RecursiveJOINOtherSQLCommandsDDLDataDefinitionLanguageDMLDataManipulationLanguageOLAPMicrosoftSQLServerOracleMicrosoftAccessCrosstab,4,HarderQuestions,Howmanycatsare“in-stock”on10/1/04WhichcatssoldorethantheaveragepriceWhichanimalssoldorethantheaveragepriceofanimalsintheircategoryWhichanimalshavenotbeensold,Whichcustomerswhoboughtsomethingatleastoncedidnotbuyanythingbetween11/1/04and12/31/04WhichcustomerswhoboughtDogsalsoboughtproductsforCatsatanytime,5,Sub-queryforCalculation,WhichcatssoldorethantheaveragesalepriceofcatsAssumeweknowtheaveragepriceis170.Usuallyweneedtocomputeitfirst.,SELECTSaleAnimal.AnimalID,Animal.Category,SaleAnimal.SalePriceFROMAnimalINNERJOINSaleAnimalONAnimal.AnimalIDSaleAnimal.AnimalIDWHEREAnimal.Category‘Cat’ANDSaleAnimal.SalePrice170;,SELECTSaleAnimal.AnimalID,Animal.Category,SaleAnimal.SalePriceFROMAnimalINNERJOINSaleAnimalONAnimal.AnimalIDSaleAnimal.AnimalIDWHEREAnimal.Category‘Cat’ANDSaleAnimal.SalePriceSELECTAVGSalePriceFROMAnimalINNERJOINSaleAnimalONAnimal.AnimalIDSaleAnimal.AnimalIDWHEREAnimal.Category‘Cat’;,,6,QuerySetsIN,ListallcustomersNamewhopurchasedoneofthefollowingitems1,2,30,32,33.,SELECTCustomer.LastName,Customer.FirstName,SaleItem.ItemIDFROMCustomerINNERJOINSaleONCustomer.CustomerIDSale.CustomerIDINNERJOINSaleItemONSale.SaleIDSaleItem.SaleIDWHERESaleItem.ItemIDIn1,2,30,32,33ORDERBYCustomer.LastName,Customer.FirstName;,Query04_13,SaleIDSaleDateEmployeeIDCustomerID,CustomerIDPhoneFirstNameLastName,Sale,Customer,SaleIDItemIDQuantitySalePrice,SaleItem,,,7,UsingINwithaSub-query,Listallcustomerswhoboughtitemsforcats.,SELECTCustomer.LastName,Customer.FirstName,SaleItem.ItemIDFROMCustomerINNERJOINSaleONCustomer.CustomerIDSale.CustomerIDINNERJOINSaleItemONSale.SaleIDSaleItem.SaleIDWHERESaleItem.ItemIDInSELECTItemIDFROMMerchandiseWHERECategory‘Cat’;,8,SubQueryINLookupaSet,ListallofthecustomerswhoboughtsomethinginMarchandwhoboughtsomethinginMay.Twotestsonthesamedata,LastNameFirstAdkinsIngaMcCainSamGrimesEarl,SELECTCustomer.LastName,Customer.FirstNameFROMCustomerINNERJOINSaleONCustomer.CustomerIDSale.CustomerIDWHEREMonth[SaleDate]3AndCustomer.CustomerIDInSELECTCustomerIDFROMSaleWHEREMonth[SaleDate]5;,Query04_14,SaleIDSaleDateEmployeeIDCustomerID,CustomerIDPhoneFirstNameLastName,Sale,Customer,,9,SubQueryANY,ALL,Anyvalueiscomparedtoeachiteminthelist.IfitisTrueforanyoftheitems,thestatementisuatedtoTrue.Allvalueiscomparedtoeachiteminthelist.IfitisTrueforeveryiteminthelist,thestatementisuatedtoTruemuchmorerestrictivethanany.,SELECTAnimal.AnimalID,Name,SalePrice,ListPriceFROMAnimalINNERJOINSaleAnimalONAnimal.AnimalIDSaleAnimal.AnimalIDWHERESalePriceAnySELECT0.80*ListPriceFROMAnimalINNERJOINSaleAnimalONAnimal.AnimalIDSaleAnimal.AnimalIDWHERECategory‘Cat’ANDCategory‘Cat’;,Query04_15,10,SubQueryNOTINSubtract,WhichanimalshavenotbeensoldStartwithlistofallanimals.Subtractoutlistofthosewhoweresold.,AnimalIDNameCategory12LeishaDog19GeneDog25VivianDog34RhondaDog88BrandyDog181Fish,SELECTAnimal.AnimalID,Animal.Name,Animal.CategoryFROMAnimalWHEREAnimal.AnimalIDNotInSELECTAnimalIDFromSaleAnimal;,Query04_16,AnimalIDNameCategoryBreed,Animal,11,SubQueryNOTINData,IDNameCategoryBreed2FishAngel4GaryDogDalmation5FishShark6RosieCatOrientalShorthair7EugeneCatBombay8MirandaDogNorfolkTerrier9FishGuppy10SherriDogSiberianHuskie11SusanDogDalmation12LeishaDogRottweiler,IDSaleIDSalePrice23510.80480156.66627173.99725251.5984183.381018150.111117148.47,Animal,SaleAnimal,Whichanimalshavenotbeensold,,,,,,,,12,LeftOuterJoin,WhichanimalshavenotbeensoldLEFTJOINincludesallrowsfromlefttableAnimalButonlythosefromrighttableSaleAnimalthatmatcharowinAnimal.RowsinAnimalwithoutmatchingdatainSaleAnimalwillhaveNull.,SELECTAnimal.AnimalID,Animal.Name,Animal.CategoryFROMAnimalLEFTJOINSaleAnimalONAnimal.AnimalIDSaleAnimal.AnimalIDWHERESaleAnimal.SaleIDIsNull;,AnimalIDNameCategory12LeishaDog19GeneDog25VivianDog34RhondaDog88BrandyDog181Fish,Query04_17,SaleIDAnimalIDSalePrice,AnimalIDNameCategoryBreed,SaleAnimal,Animal,,13,LeftOuterJoinExample,IDNameCategoryBreed2FishAngel4GaryDogDalmation5FishShark6RosieCatOrientalShorthair7EugeneCatBombay8MirandaDogNorfolkTerrier9FishGuppy10SherriDogSiberianHuskie11SusanDogDalmation12LeishaDogRottweiler,IDSaleIDSalePrice23510.80480156.66NullNullNull627173.99725251.5984183.38NullNullNull1018150.111117148.47NullNullNull,14,OlderSyntaxforLeftJoin,Whichanimalshavenotbeensold,SELECTALLFROMAnimal,SaleAnimalWHEREAnimal.AnimalID*SaleAnimal.AnimalIDAndSaleAnimal.SaleIDIsNull;,SELECTALLFROMAnimal,SaleAnimalWHEREAnimal.AnimalIDSaleAnimal.AnimalIDAndSaleAnimal.SaleIDIsNull;,OldOraclesyntaxnotethatthesymbolisonthereversedside.,15,SubQueryforComputation,Don’tknowtheaverage,souseasubquerytolookitup.Watchparentheses.,Query04_18,SELECTSaleAnimal.AnimalID,Animal.Category,SaleAnimal.SalePriceFROMAnimalINNERJOINSaleAnimalONAnimal.AnimalIDSaleAnimal.AnimalIDWHEREAnimal.Category‘Cat’ANDSaleAnimal.SalePriceSELECTAVGSalePriceFROMAnimalINNERJOINSaleAnimalONAnimal.AnimalIDSaleAnimal.AnimalIDWHEREAnimal.Category‘Cat’;,,SaleIDAnimalIDSalePrice,AnimalIDNameCategoryBreed,SaleAnimal,Animal,,16,CorrelatedSubquery,SELECTAnimalID,Name,Category,SalePriceFROMAnimalINNERJOINSaleAnimalONAnimal.AnimalIDSaleAnimal.AnimalIDWHERESaleAnimal.SalePriceSELECTAvgSaleAnimal.SalePriceFROMAnimalINNERJOINSaleAnimalONAnimal.AnimalIDSaleAnimal.AnimalIDWHEREAnimal.CategoryAnimal.CategoryORDERBYSaleAnimal.SalePriceDESC;,ListtheAnimalsthathavesoldforapricehigherthantheaverageforanimalsinthatCategory.,Thesubqueryneedstocomputetheaverageforagivencategory.ProblemWhichcategoryAnswerthecategorythatmatchesthecategoryfromthemainpartofthequery.ProblemHowdowerefertoitBothtablesarecalledAnimal.Thisquerywillnotworkyet.,,17,CorrelatedSubQueryAvoid,ListtheAnimalsthathavesoldforapricehigherthantheaverageforanimalsinthatCategory.,MatchcategoryinsubquerywithtoplevelRenametablesAsCorrelatedSubqueryRecomputesubqueryforeveryrowintoplevel--slowBettertocomputeandsaveSubquery,thenuseinjoin.,SELECTA1.AnimalID,A1.Name,A1.Category,SaleAnimal.SalePriceFROMAnimalAsA1INNERJOINSaleAnimalONA1.AnimalIDSaleAnimal.AnimalIDWHERESaleAnimal.SalePriceSELECTAvgSaleAnimal.SalePriceFROMAnimalAsA2INNERJOINSaleAnimalONA2.AnimalIDSaleAnimal.AnimalIDWHEREA2.CategoryA1.CategoryORDERBYSaleAnimal.SalePriceDESC;,Query04_19,18,CorrelatedSubqueryProblem,Fish10.80Dog156.66Fish19.80Cat173.99Cat251.59Dog183.38Fish1.80Dog150.11Dog148.47,CategorySalePrice,AnimalSaleAnimal,ComputeAvg37.78,ComputeAvg174.20,ComputeAvg37.78,ComputeAvg169.73,ComputeAvg169.73,,,,,,Recomputeaverageforeveryrowinthemainquery,Assumesmallquery100,000rows5categoriesof20,000rows100,000*20,0001billionrowstoread,19,MoreEfficientSolution2queries,ComputetheaveragesonceandsavequeryJOINsavedquerytomainqueryTwopassesthroughtable1billion/200,00010,000,Fish10.80Dog156.66Fish19.80Cat173.99Cat251.59Dog183.38Fish1.80Dog150.11Dog148.47,CategorySalePrice,AnimalSaleAnimal,Bird176.57Cat169.73Dog174.20Fish37.78Mammal80.72Reptile181.83Spider118.16,CategoryAvgOfSalePrice,SavedQuery,JOIN,,Animal.CategoryQuery1.Category,20,UNIONOperator,OfficesinLosAngelesandNewYork.EachhasanEmployeetableEastandWest.Needtosearchdatafrombothtables.ColumnsinthetwoSELECTlinesmustmatch.,SELECTEID,Name,Phone,Salary,‘East’ASOfficeFROMEmployeeEastUNIONSELECTEID,Name,Phone,Salary,‘West’ASOfficeFROMEmployeeWest,EIDNamePhoneSalaryOffice352Jones335245,000East876Inez873647,000East372Stoiko763238,000East890Smythe980362,000West361Kim773673,000West,21,UNION,INTERSECT,EXCEPT,,,,,T1,T2,A,B,C,SELECTEID,NameFROMEmployeeEastINTERSECTSELECTEID,NameFROMEmployeeWest,ListthenameofanyemployeewhohasworkedforboththeEastandWestregions.,22,MultipleJOINColumns,SometimesneedtoJOINtablesonmorethanonecolumn.PetStoreCategoryandBreed.,AnimalIDNameCategoryBreedDateBornGender...,CategoryBreed,Breed,Animal,,,SELECT*FROMBreedINNERJOINAnimalONBreed.CategoryAnimal.CategoryANDBreed.BreedAnimal.Breed,23,ReflexiveJoin,Needtoconnectatabletoitself.CommonexampleEmployeeEID,Name,...,ManagerAmanagerisalsoanemployee.Useasecondcopyofthetableandanalias.,SELECTEmployee.EID,Employee.Name,Employee.Manager,E2.NameFROMEmployeeINNERJOINEmployeeASE2ONEmployee.ManagerE2.EID,EIDName...Manager115Sanchez765462Miller115523Hawk115765Munoz886,Employee,,,,EIDNameManagerName115Sanchez765Munoz462Miller115Sanchez523Hawk115Sanchez,SQL,Result,24,RecursiveJoinsSQL99and200 x,WITHRECURSIVEEmployeeListEmployeeID,Title,SalaryASSELECTEmployeeID,Title,0.00FROMManagesWHERETitle“CEO”--startinglevelUNIONALLSELECTManages.EmployeeID,Manages.Title,Manages.SalaryFROMEmployeeListINNERJOINManagesONEmployeeList.EmployeeIDManages.ManagerIDSELECTEmployeeID,CountTitle,SumSalaryFROMEmployeeListGROUPBYEmployeEID;,Listalloftheemployeesandlisteveryonewhoreportstothem.Notyetsupportedbyvendors.Itprovidestreespanningcapabilities.,25,CASEFunction,Usedtochangedatatoadifferentcontext.ExampleDefineagecategoriesfortheanimals.Lessthan3monthsBetween3monthsand9monthsBetween9monthsand1yearOver1year,SelectAnimalID,CASEWHENDate-DateBorn90ANDDate-DateBorn270ANDDate-DateBornLateCategory.MinDaysANDDate-AR.DateDueLateCategory.MaxDays,27,SQLSELECT,SELECTDISTINCTTable.Column{ASalias},...FROMTable/QueryINNERJOINTable/QueryONT1.ColAT2.ColBWHEREconditionGROUPBYColumnHAVINGgroupconditionORDERBYTable.Column{Unionsecondselect},28,SQLMnemonic,SomeoneFromIrelandWillGrowHorseradishandOnions,SELECTFROMINNERJOINWHEREGROUPBYHAVINGORDERBY,SQLispickyaboutputtingthecommandsinthepropersequence.Ifyouhavetomemorizethesequence,thismnemonicmaybehelpful.,29,SQLDataDefinition,CreateSchemaAuthorizationdbNamepasswordCreateTableTableNameColumnType,...AlterTableTable{Add,Column,Constraint,Drop}Drop{TableTable|IndexIndexOntable}CreateIndexIndexNameONTableColumn{ASC|DESC},30,SyntaxExamples,CREATETABLECustomerCustomerIDINTEGERNOTNULL,LastNameCHAR10,morecolumns;,ALTERTABLECustomerDROPCOLUMNZipCode;,ALTERTABLECustomerADDCOLUMNCellPhoneCHAR15;,31,Querieswith“Every”NeedEXISTS,Listtheemployeeswhohavesoldanimalsfromeverycategory.,ByhandListtheemployeesandthecategories.GothroughtheSaleAnimallistandcheckofftheanimalstheyhavesold.,32,QueryWithEXISTS,ListtheAnimalcategoriesthathavenotbeensoldbyanemployee5.,SELECTCategoryFROMCategoryWHERECategory“Other“AndCategoryNOTINSELECTAnimal.CategoryFROMAnimalINNERJOINSaleINNERJOINSaleAnimalONSale.SaleIDSaleAnimal.SaleIDONAnimal.AnimalIDSaleAnimal.AnimalIDWHERESale.EmployeeID5,Ifthisqueryreturnsanyrows,thentheemployeehasnotsoldeveryanimal.Solistalltheemployeesforwhomtheabovequeryreturnsnorows,SELECTEmployeeID,LastNameFROMEmployeeWHERENOTEXISTSabovequeryslightlymodified.,33,QueryforEvery,SELECTEmployee.EmployeeID,Employee.LastNameFROMEmployeeWHERENotExistsSELECTCategoryFROMCategoryWHERECategory“Other“AndCategoryNOTINSELECTAnimal.CategoryFROMAnimalINNERJOINSaleINNERJOINSaleAnimalONSale.SaleIDSaleAnimal.SaleIDONAnimal.AnimalIDSaleAnimal.AnimalIDWHERESale.EmployeeIDEmployee.EmployeeID;,Result3Reasoner,34,SimplerQueryforEvery,SometimesitiseasiertouseCrosstabandtheCountfunction.,,ButsomesystemsdonothaveCrosstab,andsometimesthelistswouldbetoolong.Soyouneedtoknowbothtechniques.,35,SQLForeignKey,CREATETABLEOrderOrderIDINTEGERNOTNULL,OrderDateDATE,CustomerIDINTEGERCONSTRAINTpkorderPRIMARYKEYOrderID,CONSTRAINTfkorderFOREIGNKEYCustomerIDREFERENCESCustomerCustomerID;,OrderIDOrderDateCustomerID,CustomerIDLastNameFirstNameAddress,Order,Customer,,*,36,SQLDataManipulationCommands,InsertIntotargetcolumn1...VALUESvalue1...InsertIntotargetcolumn1...SELECT...FROM...DeleteFromtableWHEREconditionUpdatetableSETColumnValue,...WhereconditionNotetheuseoftheSelectandWhereconditions.Synatxisthesame--onlylearnitonce.Youcanalsousesubqueries.,37,CopyOldAnimalData,INSERTINTOOldAnimalsSELECT*FROMAnimalWHEREAnimalIDINSELECTAnimalOrderItem.AnimalIDFROMAnimalOrderINNERJOINAnimalOrderItemONAnimalOrder.OrderIDAnimalOrderItem.OrderIDWHEREAnimalOrder.OrderDate10‘Statementsxx1Loop,InitializueStatementsChangueTestcondition,DoWhilex10,Forx1to10‘StatementsNextx,55,ProgrammingLoopsAgain,DoDo{While|Until}ExitDooptionalLoopDoLoop{While|Until}For/NextForcounterstartToendStepincrementExitForoptionalNextcounter,For/EachobjectsForEachelementIngroup[ExitFor]optionalNextelementWithobjectsWithobjectEndWith,56,ProgrammingSubroutinesandFunctions,Subnamevar1As...,var2,...EndSubFunctionfnamevar1As...Asdatatypefname‘returnsaspecificvalueEndFunctionVariablesarepassedbyreferenceChangesmadetotheparametersinthesubroutinearepassedbacktothecaller.UnlessyouuseByValChangesaremadetoacopyoftheparameter,butarenotreturnedtothecallingprogram.,57,ProgrammingExampleSubroutine,MainprogramStatusMessage“Tryingtoconnect.”StatusMessage“Verifyingaccess.”EndmainprogramSubStatusMessageMsgAsString‘DisplayMsg,location,colorEndSub,,,58,ProgrammingParameterTypes,Mainj3DoSumj‘jisnowequalto8SubroutineDoSumj2AsIntegerj28EndSub,,,ByReferenceChangestodatainthesubroutinearepassedback.,Mainj3DoSumj‘jisstillequalto3SubroutineDoSumByValj2AsIntegerj28EndSub,ByValueCreatesacopyofthevariable,sochangesarenotreturned.,59,ProgrammingArraysandUserTypes,ArraysDimarraysub,...AstypeDimiSorts10AsIntegerSpecifyingboundslowerToupper,...ReDim[Preserve]array...OptionBase0|1v2.0arrayslessthan64KB,UserdefinedtypesTypeTnameename1Astypeename2AstypeEndTypeDimvar1AsTnamevar1.ename1...var1.ename2...,60,ProgrammingFinancialFunctions,FixedpaymentsPVrate,nper,pmt,fv,dueFVrate,nper,pmt,pv,dueIPmtrate,per,nper,pv,fv,dueNPerrate,pmt,pv,fv,duePmtrate,nper,pv,fv,duePPmtrate,per,nper,pv,fv,dueRatenper,pmt,pv,fv,due,guessrateinterestrateperperiodperspecificperiodnumbernperofperiodspvpresentvaluefvfuturuedue0dueatend,1dueatstart,ArraysNPVrate,arrayIRRarray,guessMIRRarray,finrate,re_rateDepreciationDDBcost,salv,life,periodSLNcost,salvage,lifeSYDcost,salv.,life,period,61,ProgrammingTe
展开阅读全文
