SQL:Queries,Programming, Triggers Chapter5 - - PDF document

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 1

SQL:Queries,Programming, Triggers

Chapter5

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 2

ExampleInstances

sid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0 sid sname rating age 28 yuppy 9 35.0 31 lubber 8 55.5 44 guppy 5 35.0 58 rusty 10 35.0

sid bid day 22 101 10/10/96 58 103 11/12/96

R1 S1 S2

• Wewillusethese

instancesofthe Sailorsand Reservesrelations inourexamples.

• Ifthekeyforthe

Reservesrelation containedonlythe attributes sid and bid,howwouldthe semanticsdiffer?

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 3

BasicSQLQuery

relation-list Alistofrelationnames(possiblywitha range-variable aftereachname).

target-list Alistofattributesofrelationsinrelation-list

qualification Comparisons(Attr op constorAttr1op Attr2,whereop isoneof) combinedusingAND,ORandNOT.

• DISTINCT isanoptionalkeywordindicatingthatthe

answershouldnotcontainduplicates.Defaultisthat duplicatesarenot eliminated!

SELECT[DISTINCT]target-list FROM

relation-list

WHEREqualification

< > = ≤ ≥ ≠ , , , , ,

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 4

ConceptualEvaluationStrategy

SemanticsofanSQLquerydefinedintermsofthe followingconceptualevaluationstrategy:

• Computethecross-productofrelation-list.
• Discardresulting tuples iftheyfailqualifications.
• Deleteattributesthatarenotintarget-list.
• IfDISTINCT isspecified,eliminateduplicaterows.

Thisstrategyisprobablytheleastefficientwayto computeaquery!Anoptimizerwillfindmore efficientstrategiestocomputethesameanswers.

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 5

ExampleofConceptualEvaluation

SELECT S.sname FROMSailorsS,ReservesR WHERE S.sid=R.sid AND R.bid=103

(sid) sname rating age (sid) bid day 22 dustin 7 45.0 22 101 10/10/96 22 dustin 7 45.0 58 103 11/12/96 31 lubber 8 55.5 22 101 10/10/96 31 lubber 8 55.5 58 103 11/12/96 58 rusty 10 35.0 22 101 10/10/96 58 rusty 10 35.0 58 103 11/12/96

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 6

ANoteonRangeVariables

Reallyneededonlyifthesamerelation appearstwiceintheFROM clause.The previousquerycanalsobewrittenas:

SELECT S.sname FROMSailorsS,ReservesR WHERE S.sid=R.sid AND bid=103 SELECT sname FROMSailors,Reserves WHERE Sailors.sid=Reserves.sid AND bid=103

Itisgoodstyle, however,touse rangevariables always! OR

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 7

Findsailorswho’vereservedatleastoneboat

WouldaddingDISTINCTtothisquerymakea difference?

WhatistheeffectofreplacingS.sid byS.sname in theSELECT clause?WouldaddingDISTINCT to thisvariantofthequerymakeadifference?

SELECT S.sid FROM SailorsS,ReservesR WHERE S.sid=R.sid

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 8

ExpressionsandStrings

Illustratesuseofarithmeticexpressionsandstring patternmatching:Findtriples(ofagesofsailorsand twofieldsdefinedbyexpressions)forsailorswhosenames beginandendwithBandcontainatleastthreecharacters.

• AS and= aretwowaystonamefieldsinresult.
• LIKE isusedforstringmatching.`_’standsforany
• necharacterand`%’standsfor0ormorearbitrary

characters.

SELECT S.age,age1=S.age-5,2*S.ageAS age2 FROM SailorsS WHERE S.sname LIKE‘B_%B’

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 9

Find sid’s ofsailorswho’vereservedaredor agreenboat

• UNION:Canbeusedto

computetheunionofany twounion-compatible setsof tuples (whichare themselvestheresultof SQLqueries).

• IfwereplaceOR byAND in

thefirstversion,whatdo weget?

• Alsoavailable:EXCEPT

(Whatdowegetifwe replaceUNION byEXCEPT?)

SELECT S.sid FROM SailorsS,BoatsB,ReservesR WHERE S.sid=R.sid AND R.bid=B.bid AND (B.color=‘red’OR B.color=‘green’) SELECT S.sid FROMSailorsS,BoatsB,ReservesR WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ UNION SELECT S.sid FROMSailorsS,BoatsB,ReservesR WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 10

Find sid’s ofsailorswho’vereservedaredand agreenboat

• INTERSECT:Canbeusedto

computetheintersection

• fanytwounion-

compatible setsof tuples.

• IncludedintheSQL/92

standard,butsome systemsdon’tsupportit.

• Contrastsymmetryofthe

UNION andINTERSECT

querieswithhowmuch theotherversionsdiffer.

SELECT S.sid FROM SailorsS,BoatsB1,ReservesR1, BoatsB2,ReservesR2 WHERE S.sid=R1.sid AND R1.bid=B1.bid ANDS.sid=R2.sid AND R2.bid=B2.bid AND (B1.color=‘red’AND B2.color=‘green’) SELECT S.sid FROMSailorsS,BoatsB,ReservesR WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ INTERSECT SELECT S.sid FROMSailorsS,BoatsB,ReservesR WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’

Keyfield!

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 11

NestedQueries

AverypowerfulfeatureofSQL:aWHERE clausecan itselfcontainanSQLquery!(Actually,socanFROM andHAVING clauses.)

Tofindsailorswho’venot reserved#103,useNOTIN.

Tounderstandsemanticsofnestedqueries,thinkofa nestedloops evaluation:ForeachSailors tuple,checkthe qualificationbycomputingthe subquery.

SELECT S.sname FROM SailorsS WHERE S.sid IN (SELECT R.sid FROM ReservesR WHERE R.bid=103)

Findnamesofsailorswho’vereservedboat#103:

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 12

NestedQuerieswithCorrelation

• EXISTS isanothersetcomparisonoperator,likeIN.

IfUNIQUE isused,and*isreplacedbyR.bid,finds sailorswithatmostonereservationforboat#103. (UNIQUE checksforduplicate tuples;*denotesall attributes.Whydowehavetoreplace*byR.bid?)

Illustrateswhy,ingeneral, subquery mustbere- computedforeachSailors tuple.

SELECT S.sname FROM SailorsS WHEREEXISTS (SELECT * FROM ReservesR WHERE R.bid=103AND S.sid=R.sid)

Findnamesofsailorswho’vereservedboat#103:

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 13

MoreonSet-ComparisonOperators

We’vealreadyseenIN,EXISTSandUNIQUE.Canalso useNOTIN,NOTEXISTSandNOTUNIQUE.

Alsoavailable:op ANY,op ALL,op IN

Findsailorswhoseratingisgreaterthanthatofsome sailorcalledHoratio:

> < = ≥ ≤ ≠ , , , , ,

SELECT * FROM SailorsS WHERE S.rating>ANY (SELECT S2.rating FROM SailorsS2 WHERE S2.sname=‘Horatio’)

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 14

RewritingINTERSECT QueriesUsingIN

Similarly,EXCEPT queriesre-writtenusingNOTIN.

Tofindnames (not sid’s)ofSailorswho’vereserved bothredandgreenboats,justreplace S.sid byS.sname inSELECT clause.(WhataboutINTERSECT query?)

Find sid’s ofsailorswho’vereservedbotharedandagreenboat:

SELECT S.sid FROM SailorsS,BoatsB,ReservesR WHERE S.sid=R.sid AND R.bid=B.bidAND B.color=‘red’ AND S.sid IN (SELECT S2.sid FROM SailorsS2,BoatsB2,ReservesR2 WHERE S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color=‘green’)

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 15

DivisioninSQL

Let’sdoitthehard way,withoutEXCEPT:

SELECT S.sname FROM SailorsS WHERENOTEXISTS ((SELECT B.bid FROMBoatsB) EXCEPT (SELECT R.bid FROM ReservesR WHERE R.sid=S.sid)) SELECT S.sname FROM SailorsS WHERENOTEXISTS(SELECT B.bid FROM BoatsB WHERENOTEXISTS(SELECT R.bid FROM ReservesR WHERE R.bid=B.bid ANDR.sid=S.sid))

SailorsSsuchthat... thereisnoboatBwithout... aReserves tuple showingSreservedB Findsailorswho’vereservedallboats. (1) (2)

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 16

AggregateOperators

Significantextensionof relationalalgebra.

COUNT (*) COUNT ([DISTINCT]A) SUM ([DISTINCT]A) AVG ([DISTINCT]A) MAX (A) MIN (A) SELECTAVG(S.age) FROM SailorsS WHERE S.rating=10 SELECTCOUNT(*) FROM SailorsS SELECTAVG(DISTINCTS.age) FROM SailorsS WHERE S.rating=10 SELECT S.sname FROM SailorsS WHERE S.rating=(SELECTMAX(S2.rating) FROM SailorsS2)

singlecolumn

SELECTCOUNT(DISTINCT S.rating) FROM SailorsS WHERES.sname=‘Bob’

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 17

Findnameandageoftheoldestsailor(s)

Thefirstqueryisillegal! (We’lllookintothe reasonabitlater,when wediscussGROUPBY.)

Thethirdqueryis equivalenttothesecond query,andisallowedin theSQL/92standard, butisnotsupportedin somesystems.

SELECT S.sname,MAX (S.age) FROM SailorsS SELECT S.sname,S.age FROM SailorsS WHERE S.age=

(SELECTMAX(S2.age)

FROM SailorsS2) SELECT S.sname,S.age FROM SailorsS WHERE (SELECTMAX(S2.age) FROM SailorsS2)

=S.age

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 18

GROUPBYandHAVING

Sofar,we’veappliedaggregateoperatorstoall (qualifying) tuples.Sometimes,wewanttoapply themtoeachofseveralgroups of tuples.

Consider:Findtheageoftheyoungestsailorforeach ratinglevel.

• Ingeneral,wedon’tknowhowmanyratinglevels

exist,andwhattheratingvaluesfortheselevelsare!

• Supposeweknowthatratingvaluesgofrom1to10;

wecanwrite10queriesthatlooklikethis(!): SELECTMIN(S.age) FROMSailorsS WHERES.rating=i Fori =1,2,...,10:

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 19

QueriesWithGROUPBYandHAVING

The target-listcontains(i)attributenames (ii)terms withaggregateoperations(e.g.,MIN(S.age)).

• Theattributelist(i) mustbeasubsetofgrouping-list.

Intuitively,eachanswer tuple correspondstoagroup,and theseattributesmusthaveasinglevaluepergroup.(A group isasetof tuples thathavethesamevalueforall attributesingrouping-list.)

SELECT[DISTINCT]target-list FROM

relation-list

WHEREqualification GROUPBY grouping-list HAVINGgroup-qualification

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 20

ConceptualEvaluation

Thecross-productofrelation-list iscomputed, tuples thatfailqualification arediscarded,`unnecessary’ fields aredeleted,andtheremaining tuples arepartitioned intogroupsbythevalueofattributesingrouping-list.

Thegroup-qualification isthenappliedtoeliminate somegroups.Expressionsingroup-qualification must haveasinglevaluepergroup!

• Ineffect,anattributeingroup-qualification thatisnotan

argumentofanaggregateopalsoappearsingrouping-list. (SQLdoesnotexploitprimarykeysemanticshere!)

Oneanswer tuple isgeneratedperqualifyinggroup.

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 21

Findtheageoftheyoungestsailorwithage18, foreachratingwithatleast2such sailors

• OnlyS.ratingandS.ageare

mentionedintheSELECT,

GROUPBYorHAVING clauses;

• therattributes`unnecessary’.
• 2ndcolumnofresultis

unnamed.(UseAS tonameit.)

SELECT S.rating,MIN (S.age) FROM SailorsS WHERE S.age>=18 GROUPBYS.rating HAVING COUNT (*)>1

sid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 71 zorba 10 16.0 64 horatio 7 35.0 29 brutus 1 33.0 58 rusty 10 35.0 rating age 1 33.0 7 45.0 7 35.0 8 55.5 10 35.0 rating 7 35.0

Answerrelation

≥

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 22

Foreachredboat,findthenumberof reservationsforthisboat

Groupingoverajoinofthreerelations.

WhatdowegetifweremoveB.color=‘red’ fromtheWHEREclauseandaddaHAVING clausewiththiscondition?

WhatifwedropSailorsandthecondition involvingS.sid?

SELECT B.bid,COUNT (*)AS scount FROM SailorsS,BoatsB,ReservesR WHERE S.sid=R.sid AND R.bid=B.bidAND B.color=‘red’ GROUPBYB.bid

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 23

Findtheageoftheyoungestsailorwithage>18, foreachratingwithatleast2sailors(ofanyage)

ShowsHAVING clausecanalsocontaina subquery.

Comparethiswiththequerywhereweconsidered

• nlyratingswith2sailorsover18!

WhatifHAVING clauseisreplacedby:

• HAVINGCOUNT(*)>1

SELECT S.rating,MIN (S.age) FROM SailorsS WHERE S.age>18 GROUPBYS.rating HAVING 1<(SELECTCOUNT(*) FROM SailorsS2 WHERE S.rating=S2.rating)

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 24

Findthoseratingsforwhichtheaverage ageistheminimumoverallratings

Aggregateoperationscannotbenested!WRONG:

SELECTS.rating FROM SailorsS WHERE S.age=(SELECTMIN(AVG (S2.age))FROM SailorsS2) SELECT Temp.rating,Temp.avgage FROM (SELECT S.rating,AVG (S.age)AS avgage FROM SailorsS GROUPBYS.rating)AS Temp WHERE Temp.avgage =(SELECTMIN(Temp.avgage) FROM Temp) v Correctsolution(inSQL/92):

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 25

NullValues

Fieldvaluesina tuple aresometimesunknown (e.g.,a ratinghasnotbeenassigned)orinapplicable (e.g.,no spouse’sname).

• SQLprovidesaspecialvaluenull forsuchsituations.

Thepresenceofnull complicatesmanyissues.E.g.:

• Specialoperatorsneededtocheckifvalueis/isnotnull.
• Israting>8 trueorfalsewhenrating isequaltonull?What

aboutAND,ORandNOT connectives?

• Weneeda3-valuedlogic (true,falseandunknown).
• Meaningofconstructsmustbedefinedcarefully.(e.g.,

WHEREclauseeliminatesrowsthatdon’tevaluatetotrue.)

• Newoperators(inparticular,outerjoins)possible/needed.

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 26

IntegrityConstraints(Review)

AnICdescribesconditionsthateverylegalinstance

• farelationmustsatisfy.
• Inserts/deletes/updatesthatviolateIC’saredisallowed.
• Canbeusedtoensureapplicationsemantics(e.g., sid isa

key),orpreventinconsistencies(e.g., sname hastobea string,age mustbe<200)

TypesofIC’s:Domainconstraints,primarykey constraints,foreignkeyconstraints,general constraints.

• Domainconstraints:Fieldvaluesmustbeofrighttype.

Alwaysenforced.

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 27

GeneralConstraints

Usefulwhen moregeneral ICsthankeys areinvolved.

Canusequeries toexpress constraint.

Constraintscan benamed.

CREATETABLESailors

( sid INTEGER, sname CHAR(10), rating INTEGER, age REAL,

PRIMARYKEY(sid), CHECK (rating>=1 AND rating<=10) CREATETABLEReserves

( sname CHAR(10), bidINTEGER, dayDATE,

PRIMARYKEY(bid,day), CONSTRAINT noInterlakeRes CHECK (`Interlake’<>

(SELECT B.bname

FROM BoatsB WHERE B.bid=bid)))

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 28

ConstraintsOverMultipleRelations

CREATETABLESailors

( sid INTEGER, sname CHAR(10), rating INTEGER, age REAL,

PRIMARYKEY(sid), CHECK

((SELECTCOUNT(S.sid)FROM SailorsS) +(SELECTCOUNT(B.bid)FROM BoatsB)<100)

Awkwardand wrong!

IfSailorsis empty,the numberofBoats tuples canbe anything!

• ASSERTION isthe

rightsolution; notassociated witheithertable.

CREATEASSERTION smallClub CHECK

((SELECTCOUNT(S.sid)FROM SailorsS) +(SELECTCOUNT(B.bid)FROM BoatsB)<100) Numberofboats plusnumberof sailorsis<100

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 29

Triggers

Trigger:procedurethatstartsautomaticallyif specifiedchangesoccurtotheDBMS

Threeparts:

• Event(activatesthetrigger)
• Condition(testswhetherthetriggersshouldrun)
• Action(whathappensifthetriggerruns)

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 30

Triggers:Example(SQL:1999)

CREATETRIGGER youngSailorUpdate AFTERINSERTONSAILORS REFERENCINGNEWTABLE NewSailors FOREACHSTATEMENT INSERT INTO YoungSailors(sid,name,age,rating) SELECT sid,name,age,rating FROM NewSailors N WHEREN.age<=18

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 31

Summary

SQLwasanimportantfactorintheearlyacceptance

• ftherelationalmodel;morenaturalthanearlier,

proceduralquerylanguages.

Relationallycomplete;infact,significantlymore expressivepowerthanrelationalalgebra.

EvenqueriesthatcanbeexpressedinRAcanoften beexpressedmorenaturallyinSQL.

Manyalternativewaystowriteaquery;optimizer shouldlookformostefficientevaluationplan.

• Inpractice,usersneedtobeawareofhowqueriesare
• ptimizedandevaluatedforbestresults.

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 32

Summary(Contd.)

NULLforunknownfieldvaluesbringsmany complications

SQLallowsspecificationofrichintegrity constraints

Triggersrespondtochangesinthedatabase