SELECT[DISTINCT]

SQL(DML)IIReview:FormalSemanticsofSQL:RA*SELECTA1,A2,…,AnFROMr1,r2,…,rmWHEREPQuery:Semantics:p*A1,A2,...,An(s*P(r1x*r2x*...rm))Query:SELECTDISTINCTA1,A2,...,AnFROMr1,r2,...,rmWHEREPSemantics:(Remove*onPi)pA1,A2,...,An(s*P(r1x*r2x*...rm))Semantics:AssignresulttoanewrelationSSpA1,A2,...,An(s*P(r1x*r2x*...rm))Query:CREATETABLESASSELECTDISTINCTA1,A2,...,AnFROMr1,r2,...,rmWHEREPReviewofSQLClause----------SELECT[DISTINCT]FROMWHERECREATETABLEASSELECTGROUPBYHAVINGORDERBYSemantics(RA/RA*)----------p*(orp)X*s*Can’texpresss*Can’texpressAggregatesandGroupByUsually,aggregatesusedwith“GroupBy”E.g.SELECTbname,COUNT(DISTINCTcname)FROMdepositord,accountaWHEREd.acct_no=a.acct_noGROUPBYbnamedepositor(customer-name,account-number)account(account-number,branch-name,balance)Result:bnameCOUNT--------------------------Downtown2Mianus5Perry1Brighton5Kenmore7AggregatesandGroupByIntuitionbehind“GroupBy”SELECTbname,COUNT(DISTINCTcname)FROMdepositord,accountaWHEREd.acct_no=a.acct_noGROUPBYbnameStep1:“Group“resultsofjoinStep2:aggregateongroupsandprojectonresultbnameCOUNT--------------------------Downtown1Mianus1Perry1Brighton2Kenmore2bnamea.acct_nobalancecnamed.acct_noDowntownA-101500JohnsonA-101MianusA-215700SmithA-215PerryA-102400HayesA-102BrightonBrightonA-202A-217900800JohnsonJonesA-202A-217KenmoreKenmoreA-305A-232700600SmithLindsayA-305A-232GroupByAnotherexample:SELECTbname,SUM(assets)astotalFROMbranchGROUPBYbcityResult??bnametotal--------------------------Redwood2.1MPownal0.3MN.Town3.7M????2M?????10.1MAbovequeryisNOTallowedNon-aggregatedvaluesinSELECTclause(e.g.,bname)mustalsoappearinGROUPBYclauseSELECTA1,A2,...,Ak,Agg1(),....,Aggi()FROM..........WHERE............GROUPBYA1,A2,...,Ak,Ak+1,...,Anbranch(bname,bcity,assets)HAVINGWHERE::FROMasHAVING::GROUPBYHAVINGP:selectsrowsfromresultofGROUPBYOptional(missingHAVINGmeansTRUE)Example:Findnamesofbranchesandtheaverageaccountbalanceforthosebrancheshavingaverageaccountbalance$1200SELECTbname,AVG(balance)ASavgFROMaccountGROUPBYbnameHAVINGavg1200Resultsameas:CREATETABLETempASSELECTbname,AVG(balance)ASavgFROMaccountGROUPBYbnameSELECT*FROMTempWHEREavg1200Review-SummaryThusFarKitchensinkquery:SELECTbcity,sum(balance)AStotalbalanceFROMbranchb,accountaWHEREb.bname=a.bnameANDassets=1MGROUPBYbcityHAVINGtotalbalance700ORDERBYbcityDESCSteps1,2:FROM,WHEREaccount(acct_no,bname,balance)branch(bname,bcity,assets)b.bnamebcityassetsa.bnameacct_nobalanceDowntownBkln9MDowntownA-101500RedwoodPaloAlto2.1MRedwoodA-215700PerryHorse1.7MPerryA-102400RHHorse8MRHA-202350BrightonBkln7.1MBrightonA-305900BrightonBkln7.1MBrightonA-217750SummarythusfarSteps3,4:GROUPBY,SELECTbcitytotalbalanceBkln2150PaloAlto700Horse750Steps5:HAVINGbcitytotalbalanceBkln2150Horse750Steps6:ORDERBYbcitytotalbalanceHorse750Bkln2150SummarythusfarClause----------SELECT[DISTINCT]FROMWHEREGROUPBYHAVINGORDERBYEvaluationOrder----------412356Semantics(RA/RA*)----------p*(orp)X*s*Can’texpresss*Can’texpressbranch(bname,bcity,assets)customer(cname,cstreet,customer-only)account(acct_no,bname,balance)loan(lno,bname,amt)depositor(cname,acct_no)borrower(cname,lno)MoreSQLNULLvaluesandSQLNestedQueriesViewsUpdatesJoinsPseudoColumnsConnect-ByHierarchicalQueriesNULLsThe“dirtylitlesecret”ofSQL(majorheadacheforqueryoptimization)canbeavalueforanyattributee.g.:bnamebcityassetsDowntownBoston9MPerryHorse1.7MMianusHorse.4MKenmoreBostonNULLbranch2=Whatdoesthismean?•Wedon’tknowKenmore’sassets?•Kenmorehasnoassets?•....................EffectonQueries:SELECT*FROMbranch2WHEREassets=NULLSELECT*FROMbranch2WHEREassetsISNULLbnamebcityassetsbnamebcityassetsKenmoreBostonNULLNULLsArithmeticwithnulls:nopnull=nullop:+,-,*,/,mod,...Booleanswithnulls:Onecanwrite:3-valuedlogic(true,false,unknown)SELECT...........FROM.............WHEREboolexprISUNKNOWNWhatexpressionsevaluatetoUNKNOWN?1.ComparisonswithNULL(e.g.assets=NULL)2.FALSEORUNKNOWN(but:TRUEORUNKNOWN=TRUE)3.TRUEANDUNKNOWN4.UNKNOWNAND/ORUNKNOWNNULLsGiven:bnamebcityassetsDowntownBoston9MPerryHorse1.7MMianusHorse.4MKenmoreBostonNULLbranch2=Aggregateoperations:SELECTSUM(assets)FROMbranch2returnsSUM--------11.1MNULLisignoredSameforAVG,MIN,MAXBut....COUNT(assets)returns4!Letbranch3anemptyrelationThen:SELECTSUM(assets)FROMbranch3returnsNULLbutCOUNT(emptyrel)=0NestedQueriesinSQLQueriescontainingotherqueriesInnerquery:CanappearinFROMorWHEREclause“outerquery”“innerquery”Example:SELECTcnameFROMborrowerWHEREcnameIN(SELECTcnameFROMdepositor)cnameJohnsonSmithJonesSmithThinkofthisasafunctionthatreturnstheresultoftheinnerqueryNestedQueriesinSQLAnotherexample:SELECTDISTINCTcnameFROMborrowerasb,loanaslWHEREb.lno=l.lnoANDbname=“Perry”AND(bname,cname)IN(SELECTbname,cnameFROMdepositorasd,accountasaWHEREd.acct_no=a.acct_no)Q:DescribewhatthisqueryreturnsAns:NamesofborrowersatthePerrybranchwhoalsohavesavingsaccountatthesamebranchNestedQueriesinSQLWhatcanwedowithqueriesintheWHEREclause?LetA=SELECT...FROM...WHERE....1.WHEREexprINAWHEREexprNOTINA2.WHEREexprSOME(A)()WHEREexprALL(A)()Also(‘’,‘=‘,‘=‘,‘=‘,...)3.WHER