ModernDatabaseManagement11thEditionJeffreyA.Hoffer,V....ppt

2013pearsoneducation,inc.publishingasprenticehall,1,chapter7:advancedsql,moderndatabasemanagement11theditionjeffreya.hoffer,v.ramesh,heikkitopi,definetermswritesingleandmultipletablesqlqueriesdefineandusethreetypesofjoinswritenoncorrelatedandcorrelatedsubqueriesunderstandandusesqlinprocedurallanguages(e.g.php,pl/sql)understandtriggersandstoredproceduresdiscusssql:2008standardanditsenhancementsandextensions,2,objectives,processingmultipletables,joinarelationaloperationthatcausestwoormoretableswithacommondomaintobecombinedintoasingletableorviewequi-joinajoininwhichthejoiningconditionisbasedonequalitybetweenvaluesinthecommoncolumns;commoncolumnsappearredundantlyintheresulttablenaturaljoinanequi-joininwhichoneoftheduplicatecolumnsiseliminatedintheresulttable,3,thecommoncolumnsinjoinedtablesareusuallytheprimarykeyofthedominanttableandtheforeignkeyofthedependenttablein1:mrelationships.,processingmultipletables,outerjoinajoininwhichrowsthatdonothavematchingvaluesincommoncolumnsarenonethelessincludedintheresulttable(asopposedtoinnerjoin,inwhichrowsmusthavematchingvaluesinordertoappearintheresulttable)unionjoinincludesallcolumnsfromeachtableinthejoin,andaninstanceforeachrowofeachtable,4,5,figure7-2visualizationofdifferentjointypeswithresultsreturnedinshadedarea,thefollowingslidescreatetablesforthisenterprisedatamodel,6,(fromchapter1,figure1-3),7,thesetablesareusedinqueriesthatfollow,figure7-1pinevalleyfurniturecompanycustomer_tandorder_ttableswithpointersfromcustomerstotheirorders,7,chapter7,2013pearsoneducation,inc.publishingasprenticehall,equi-joinexample,foreachcustomerwhoplacedanorder,whatisthecustomersnameandordernumber?,8,customeridappearstwiceintheresult,equi-joinexamplealternativesyntax,9,innerjoinclauseisanalternativetowhereclause,andisusedtomatchprimaryandforeignkeys.aninnerjoinwillonlyreturnrowsfromeachtablethathavematchingrowsintheother.thisqueryproducessameresultsaspreviousequi-joinexample.,naturaljoinexample,foreachcustomerwhoplacedanorder,whatisthecustomersnameandordernumber?,10,note:fromfig.7-1,youseethatonly10customershavelinkswithorders.only10rowswillbereturnedfromthisinnerjoin,outerjoinexample,listthecustomername,idnumber,andordernumberforallcustomers.includecustomerinformationevenforcustomersthatdohaveanorder.,11,unlikeinnerjoin,thiswillincludecustomerrowswithnomatchingorderrows,12,outerjoinresults,unlikeinnerjoin,thiswillincludecustomerrowswithnomatchingorderrows,12,chapter7,2013pearsoneducation,inc.publishingasprenticehall,multipletablejoinexample,assembleallinformationnecessarytocreateaninvoiceforordernumber1006,13,14,figure7-4resultsfromafour-tablejoin(editedforreadability),self-joinexample,15,thesametableisusedonbothsidesofthejoin;distinguishedusingtablealiases,self-joinsareusuallyusedontableswithunaryrelationships.,16,figure7-5exampleofaself-join,processingmultipletablesusingsubqueries,subqueryplacinganinnerquery(selectstatement)insideanouterqueryoptions:inaconditionofthewhereclauseasa“table”ofthefromclausewithinthehavingclausesubqueriescanbe:noncorrelatedexecutedoncefortheentireouterquerycorrelatedexecutedonceforeachrowreturnedbytheouterquery,17,subqueryexample,showallcustomerswhohaveplacedanorder,18,theinoperatorwilltesttoseeifthecustomer_idvalueofarowisincludedinthelistreturnedfromthesubquery,joinvs.subquery,somequeriescouldbeaccomplishedbyeitherajoinorasubquery,19,joinversion,subqueryversion,20,figure7-6graphicaldepictionoftwowaystoansweraquerywithdifferenttypesofjoins,21,figure7-6graphicaldepictionoftwowaystoansweraquerywithdifferenttypesofjoins,correlatedvs.noncorrelatedsubqueries,noncorrelatedsubqueries:donotdependondatafromtheouterqueryexecuteoncefortheentireouterquerycorrelatedsubqueries:makeuseofdatafromtheouterqueryexecuteonceforeachrowoftheouterquerycanusetheexistsoperator,22,23,figure7-8aprocessinganoncorrelatedsubquery,anoncorrelatedsubqueryprocessescompletelybeforetheouterquerybegins.,23,chapter7,2013pearsoneducation,inc.publishingasprenticehall,correlatedsubqueryexample,showallordersthatincludefurniturefinishedinnaturalash.,24,acorrelatedsubqueryalwaysreferstoanattributefromatablereferencedintheouterquery,25,figure7-8bprocessingacorrelatedsubquery,subqueryreferstoouter-querydata,soexecutesonceforeachrowofouterquery,note:onlytheordersthatinvolveproductswithnaturalashwillbeincludedinthefinalresults.,25,chapter7,2013pearsoneducation,inc.publishingasprenticehall,anothersubqueryexample,showallproductswhosestandardpriceishigherthantheaverageprice,26,unionqueries,combinetheoutput(unionofmultiplequeries)togetherintoasingleresulttable,27,28,figure7-9combiningqueriesusingunion,note:withunionqueries,thequantityanddatatypesoftheattributesintheselectclausesofbothqueriesmustbeidentical.,28,chapter7,2013pearsoneducation,inc.publishingasprenticehall,conditionalexpressionsusingcasesyntax,thisisavailablewithnewerversionsofsql,previouslynotpartofthestandardfigure7-10,29,tipsfordevelopingqueries,befamiliarwiththedatamodel(entitiesandrelationships)understandthedesiredresultsknowtheattributesdesiredinresultsidentifytheentitiesthatcontaindesiredattributesreviewerdconstructawhereequalityforeachlinkfinetunewithgroupbyandhavingclausesifneededconsidertheeffectonunusualdata,30,queryefficiencyconsiderations,insteadofselect*,identifythespecificattributesintheselectclause;thishelpsreducenetworktrafficofresultsetlimitthenumberofsubqueries;trytomakeeverythingdoneinasinglequeryifpossibleifdataistobeusedmanytimes,makeaseparatequeryandstoreitasaview,31,guidelinesforbetterquerydesign,understandhowindexesareusedinqueryprocessingkeepoptimizerstatisticsup-to-dateusecompatibledatatypesforfieldsandliteralswritesimplequeriesbreakcomplexqueriesintomultiplesimplepartsdontnestonequeryinsideanotherquerydontcombineaquerywithitself(ifpossibleavoidself-joins),32,guidelinesforbetterquerydesign(cont.),createtemporarytablesforgroupsofqueriescombineupdateoperationsretrieveonlythedatayouneeddonthavethedbmssortwithoutanindexlearn!considerthetotalqueryprocessingtimeforadhocqueries,33,ensuringtransactionintegrity,transaction=adiscreteunitofworkthatmustbecompletelyprocessedornotprocessedatallmayinvolvemultipleupdatesifanyupdatefails,thenallotherupdatesmustbecancelledsqlcommandsfortransactionsbegintransaction/endtransactionmarksboundariesofatransactioncommitmakesallupdatespermanentrollbackcancelsupdatessincethelastcommit,34,35,figure7-12ansqltransactionsequence(inpseudocode),35,chapter7,2013pearsoneducation,inc.publishingasprenticehall,datadictionaryfacilities,systemtablesthatstoremetadatausersusuallycanviewsomeofthesetablesusersarerestrictedfromupdatingthemsomeexamplesinoracle11gdba_tablesdescriptionsoftablesdba_constraintsdescriptionofconstraintsdba_usersinformationabouttheusersofthesystemexamplesinmicrosoftsqlserver2008sys.columnstableandcolumndefinitionssys.indexestableindexinformationsys.foreign_key_columnsdetailsaboutcolumnsinforeignkeyconstraints,36,sql:2008enhancements/extensions,user-defineddatatypes(udt)subclassesofstandardtypesoranobjecttypeanalyticalfunctions(forolap)ceiling,floor,sqrt,rank,dense_rank,rollup,cube,sample,windowimprovednumericalanalysiscapabilitiesnewdatatypesbigint,multiset(collection),xmlcreatetablelikecreateanewtablesimilartoanexistingonemerge,37,programmingextensionspersistentstoredmodules(sql/psm)capabilitytocreateanddropcodemodulesnewstatements:case,if,loop,for,while,etc.makessqlintoaprocedurallanguageoraclehasproprietyversioncalledpl/sql,andmicrosoftsqlserverhastransact/sql,38,sql:2008enhancements(cont),routinesandtriggers,routinesprogrammodulesthatexecuteondemandfunctionsroutinesthatreturnvaluesandtakeinputparametersproceduresroutinesthatdonotreturnvaluesandcantakeinputoroutputparameterstriggersroutinesthatexecuteinresponsetoadatabaseevent(insert,update,ordelete),39,40,figure7-13triggerscontras