SQL: Practical Guide for Developers

266 Pages·2005·4.146 MB·English

by  Michael J. Donahoo, Gregory D. Speegle

Critical Acclaim for
SQL: Practical Guide for Developers
Thebooklivesuptoitstitle:itisaverypracticalguide.TheauthorsclearlyknowtheirSQL
and manage to write about the language in a step-by-step style that progresses smoothly
fromthesimpletothemoredifficultaspectsofthelanguage.Theprofuseuseofexamples,
eachwithanaccompanyingmotivationandexplanationoftheresults,letsthereaderfollow
evencomplextopicswithoutaconstantstruggle.Theauthorshaveevenincludedexamples
thatillustratecommonerrorsprogrammersmake,explainingtherightwaytoperformthe
task.Itdoesn’thurtthatthesampleapplicationisbasedonsomethingeverybodyusesevery
day:food.
I especially enjoyed the manner in which the authors explained the rationale behind most
oftheSQLfacilities.Eventhoughtheyhavenotbeenanactivepartofthestandard’sdevel-
opment efforts, their insight into the underlying reasons for a feature’s inclusion is spot
on. They also do an excellent job of explaining complex sets of rules in easily understood
language—I learned new ways of understanding some of the very rules that I personally
wroteintothestandard!
Eachchapterendswithasummaryoftheprinciplepoints,andmostofthemincludeprac-
ticaladviceforprogrammerswhointendtousetheSQLlanguagefeaturesdescribedinthe
chapter.Theinclusionateverychapter’sendofasetofquestionsandexercises—alldirectly
relevant to the chapter’s material—makes this book an excellent candidate for use in the
classroom.
–Jim Melton,OracleCorporation,EditorofISO/IEC9075-*(SQL)andCo-Chair,W3CXML
QueryWorkingGroup
This book provides a succinct yet complete introduction to the fundamental aspects of the
SQLlanguage.ItisnotjustanotherSQLtext.Theauthors’useofsimpleyetclearexamples
to illustrate difficult concepts throughout the text makes this a perfect book for use in an
introductory database systems class as a supplement or as an introductory reference for
thenovicepractitioner.
–Paul Fortier,UniversityofMassachusetts,Dartmouth

SQL: Practical Guide
for Developers
The Morgan Kaufmann Practical Guides Series
Series Editor, Michael J. Donahoo
SQL:PracticalGuideforDevelopers
MichaelJ.DonahooandGregoryD.Speegle
C#2.0:PracticalGuideforProgrammers
MicheldeChamplainandBrianG.Patrick
Multi-TierApplicationProgrammingwithPHP:PracticalGuideforArchitects
andProgrammers
DavidWall
TCP/IPSocketsinC#:PracticalGuideforProgrammers
DavidB.Makofske,MichaelJ.Donahoo,andKennethL.Calvert
JavaCryptographyExtensions:PracticalGuideforProgrammers
JasonWeiss
JSP:PracticalGuideforJavaProgrammers
RobertJ.Brunner
JSTL:PracticalGuideforJSPProgrammers
SueSpielman
Java:PracticalGuideforProgrammers
ZbigniewM.Sikora
TheStrutsFramework:PracticalGuideforJavaProgrammers
SueSpielman
MulticastSockets:PracticalGuideforProgrammers
DavidMakofskeandKevinAlmeroth
TCP/IPSocketsinJava:PracticalGuideforProgrammers
KennethL.CalvertandMichaelJ.Donahoo
TCP/IPSocketsinC:PracticalGuideforProgrammers
MichaelJ.DonahooandKennethL.Calvert
JDBC:PracticalGuideforJavaProgrammers
GregoryD.Speegle
For further information on these books and for a list of forthcoming titles, please visit our
Websiteathttp://www.mkp.com/practical
SQL: Practical Guide
for Developers
Michael J. Donahoo
Gregory D. Speegle
AMSTERDAM•BOSTON•HEIDELBERG•LONDON
NEWYORK•OXFORD•PARIS•SANDIEGO
SANFRANCISCO•SINGAPORE•SYDNEY•TOKYO
MorganKaufmannPublishersisanimprintofElsevier
Publisher DianeCerra
PublishingServicesManager SimonCrump
ProjectManager BrandyLilly
EditorialAssistant AsmaStephan
CoverDesign YvoRiezebos
CoverImage GettyImages
Composition CephaImagingPvt.Ltd.
TechnicalIllustration DartmouthPublishing,Inc.
Copyeditor GraphicWorldInc.
Proofreader GraphicWorldInc.
Interiorprinter TheMaple-VailBookManufacturingGroup
Coverprinter PhoenixColorCorp.
MorganKaufmannPublishersisanimprintofElsevier.
500SansomeStreet,Suite400,SanFrancisco,CA94111
Thisbookisprintedonacid-freepaper.
©2005byElsevierInc.Allrightsreserved.
Designationsusedbycompaniestodistinguishtheirproductsareoftenclaimedastrademarksor
registeredtrademarks.InallinstancesinwhichMorganKaufmannPublishersisawareofaclaim,
theproductnamesappearininitialcapitalorallcapitalletters.Readers,however,shouldcontact
theappropriatecompaniesformorecompleteinformationregardingtrademarksandregistration.
Nopartofthispublicationmaybereproduced,storedinaretrievalsystem,ortransmittedinany
formorbyanymeans—electronic,mechanical,photocopying,scanning,orotherwise—withoutprior
writtenpermissionofthepublisher.
PermissionsmaybesoughtdirectlyfromElsevier’sScience&TechnologyRightsDepartmentin
Oxford,UK:phone:[phone],fax:[phone],e-mail:[email protected].
Youmayalsocompleteyourrequeston-lineviatheElsevierhomepage(http://elsevier.com)by
selecting“CustomerSupport”andthen“ObtainingPermissions.”
LibraryofCongressCataloging-in-PublicationData
Applicationsubmitted
ISBN-13:978-0-1222-0531-6
ISBN-10:0-12-220531-6
ForinformationonallMorganKaufmannpublications,
visitourWebsiteatwww.mkp.comorwww.books.elsevier.com
PrintedintheUnitedStatesofAmerica
05 06 07 08 09 5 4 3 2 1
Contents
1 Databasics 1
1.1 Tables 3
1.2 DataTypes 4
1.2.1 CharacterString 4
1.2.2 Numeric 5
1.2.3 Temporal 6
1.2.4 Binary 7
1.2.5 Boolean 8
1.2.6 OtherDataTypes 8
1.3 NULL 8
1.4 PrimaryKey 9
1.5 TableRelationships 9
1.5.1 ForeignKey 10
1.5.2 RelationshipTypes 11
1.5.3 Schemas 14
1.6 RestaurantDatabase 15
1.7 WrapUp 21
2 Retrieval: Basic SELECTion 27
2.1 SimpleSELECT 27
2.2 SelectingRows 30
2.3 StandardComparisonOperators 30
2.4 StringComparisons 31
2.5 MatchingStringPatternswithLIKE 33
2.6 GettingWhatWeHaven’tGotwithNOT 34
vii
viii Contents ■
2.7 CombiningConditionswithANDandOR 35
2.8 SelectingaRangeofValueswithBETWEEN 37
2.9 SelectingaSetofValuesUsingIN 38
2.10 ISNULL:ExploringtheUnknown 38
2.11 ANDs,ORs,NOTswithNULLs:Three-ValuedLogic 41
2.12 Three-ValuedLogicandtheINOperator 42
2.13 HowWHEREDeterminesWhat’sInandOut 43
2.14 WrapUp 43
3 Reshaping Results 47
3.1 AS:NamingResultTableColumns 47
3.2 DISTINCTandALL:DealingwithDuplicates 48
3.3 DerivedAttributes 50
3.3.1 Numeric 50
3.3.2 CharacterString 52
3.3.3 Temporal 57
3.3.4 Binary 59
3.4 ComputationintheWHEREClause 60
3.5 ORDERBY:OrderingResultTableTraversal 61
3.6 CAST:DataTypeConversion 64
3.7 CASE,COALESCE,andNULLIF:ConditionalExpressions 66
3.7.1 CASE:ValueList 66
3.7.2 CASE:ConditionalList 67
3.7.3 NULLIF 69
3.7.4 COALESCE 70
3.8 WrapUp 70
4 Aggregating Results 75
4.1 AggregationFunctions 75
4.1.1 AVGandSUM 75
4.1.2 MINandMAX 76
4.1.3 COUNT 77
4.2 RemovingRowsbeforeAggregationwithWHERE 78
4.3 RemovingRepeatingDatawithDISTINCTbeforeAggregation 79
4.4 MixingAttributes,Aggregates,andLiterals 79
4.5 GroupAggregationUsingGROUPBY 80
4.6 RemovingRowsbeforeGroupingwithWHERE 82
4.7 SortingGroupswithORDERBY 83
4.8 RemovingGroupswithHAVING 83
4.9 AggregatesoverExpressions 85
4.10 WrapUp 87
■ Contents ix
5 Joins 91
5.1 TwoTableJoinswithWHERE 91
5.2 TableAliases 94
5.3 JoinsNeedingMoreThanTwoTables 95
5.4 Self-Join:JoiningaTablewithItself 97
5.5 ExampleJoins 97
5.6 HowDoesaJoinReallyWork? 99
5.7 ThetaJoins:GeneralizingJoinPredicates 101
5.8 JOINOperator 104
5.8.1 INNERJOIN 104
5.8.2 OUTERJOIN 106
5.8.3 NATURALJOIN 109
5.8.4 CROSSJOIN 111
5.9 JoinStrategies 111
5.10 WrapUp 112
6 Set Queries: UNION, INTERSECT, and EXCEPT 117
6.1 UNION 117
6.2 INTERSECT 120
6.3 EXCEPT 121
6.4 WrapUp 124
7 Subqueries 127
7.1 WhatAreSubqueries? 127
7.2 MultilevelSubqueryNesting 132
7.3 SubqueriesUsingNOTIN 134
7.4 SubquerieswithEmptyResults 136
7.5 CombiningJOINandSubqueries 137
7.6 StandardComparisonOperatorswithListsUsingANY,SOME,
orALL 138
7.6.1 ANYorSOME 139
7.6.2 ALL 142
7.7 CorrelatedSubqueries 144
7.8 EXISTS 146
7.9 DerivedRelations—SubqueriesintheFROMClause 148
7.10 SubqueriesintheHAVINGClause 150
7.11 SubsetQueries 151
7.11.1 SubsetUsingEXISTSandEXCEPT 152
7.11.2 SubsetUsingSetCardinality 155
7.11.3 ComparingSetCardinalityandSubsets 156
7.12 SubqueriesintheSELECTClause 157
7.13 WrapUp 159