The Language of SQL: How to Access Data in Relational Databases

256 Pages·2010·1.68 MB·English

by  Larry Rockoff

The Language
of SQL
Larry Rockoff
CourseTechnologyPTR
ApartofCengageLearning
Australia•Brazil•Japan•Korea•Mexico•Singapore•Spain•UnitedKingdom•UnitedStates
TheLanguageofSQL †2011CourseTechnology,apartofCengageLearning.
LarryRockoff
ALLRIGHTSRESERVED.Nopartofthisworkcoveredbythecopyrighthereinmay
bereproduced,transmitted,stored,orusedinanyformorbyanymeansgraphic,
PublisherandGeneralManager,
electronic,ormechanical,includingbutnotlimitedtophotocopying,recording,
CourseTechnologyPTR:
scanning,digitizing,taping,Webdistribution,informationnetworks,or
StacyL.Hiquet
informationstorageandretrievalsystems,exceptaspermittedunderSection107
AssociateDirectorofMarketing: or108ofthe1976UnitedStatesCopyrightAct,withoutthepriorwritten
SarahPanella permissionofthepublisher.
ManagerofEditorialServices:
HeatherTalbot
Forproductinformationandtechnologyassistance,contactusat
MarketingManager:MarkHughes CengageLearningCustomer&SalesSupport,1-800-354-9706
AcquisitionsEditor:MitziKoontz Forpermissiontousematerialfromthistextorproduct,
submitallrequestsonlineatcengage.com/permissions
ProjectandCopyEditor:
Furtherpermissionsquestionscanbeemailedto
MartaJustak [email protected]
TechnicalReviewer:Keith
Davenport
DB2,Informix,andIBMareregisteredtrademarksofIBMCorporation.
InteriorLayoutTech:MPSLimited, OracleisaregisteredtrademarkofOracleCorp.MySQLisaregistered
AMacmillanCompany trademarkofMySQLAB.SybaseandSQLAnywhereareregistered
CoverDesigner:LukeFletcher trademarksofSybaseInc.Access,Excel,Microsoft,SQLServer,and
Indexer:ValerieHaynesPerry WindowsareregisteredtrademarksofMicrosoftCorporation.MacOS
isaregisteredtrademarkofAppleInc.PostgreSQLisatrademarkof
Proofreader:ChrisSmall
PostgreSQLIncandtheRegentsoftheUniversityofCalifornia.
Allothertrademarksarethepropertyoftheirrespectiveowners.
Allimages†CengageLearningunlessotherwisenoted.
LibraryofCongressControlNumber:2010925128
ISBN-13:978-1-4354-5751-5
ISBN-10:1-4354-5751-X
eISBN-10: 1-4354-5752-8
CourseTechnology,apartofCengageLearning
20ChannelCenterStreet
Boston,MA 02210
USA
CengageLearningisaleadingproviderofcustomizedlearningsolutions
withofficelocationsaroundtheglobe,includingSingapore,theUnited
Kingdom,Australia,Mexico,Brazil,andJapan.Locateyourlocalofficeat:
international.cengage.com/region
CengageLearningproductsarerepresentedinCanadabyNelson
Education,Ltd.
Foryourlifelonglearningsolutions,visitcourseptr.com
Visitourcorporatewebsiteatcengage.com
Printed in the United States of America
1 2 3 4 5 6 7 12 11 10
For Lisa
Acknowledgments
As noted in the 2002 hit movie About a Boy, no man is an island. While that
sentimentiscertainlyageneraltruth,itisonethatIhavespecificallyexperienced
as I’ve written this book. As such, I would like to acknowledge the help received
from those who assisted, either directly or indirectly, with the words found
betweenthese covers.
First, I would like to thank the many editors at Cengage Learning who skillfully
enhancedandimproved manyaspectsofthis bookasit wasbrought to fruition.
Without Mitzi Koontz, my acquisitions editor, this book literally would not
exist.KeithDavenport,mytechnicaleditor,didanoutstandingjobinhisreview.
I thank him for the numerous suggestions and corrections that he provided.
Finally, Marta Justak, my project editor was superb in pulling it all together,
while adding a professional touch and coherencyto the entire project.
I would also like to thank a former colleague, Mary Anne Schneider, who con-
tributed to my understanding of SQL, and other associates at ASAP Software
who gave me the freedom to explore things on my own.
Finally, and most especially, I would like to thank everyone in my immediate
family for their encouragement and support as I’ve dedicated myself to this
project...this is for Lisa, Steve, Dan, Emily, and Kyle.
iv
About the Author
Larry Rockoff has been involved with SQL and Business Intelligence (BI) de-
velopment for many years. His main area of expertise is with data warehouse
systems and reporting tools. He recently developed a suite of BI tools for ASAP
Software, a subsidiary of Dell Inc. He holds an MBA from the University of
Chicago, with a specialization in Management Science.
For more information on his current activities or to contact the author, please
visit LarryRockoff.com.
v
Contents
Introduction ..................................................xii
Chapter1 RelationalDatabasesandSQL........................... 1
LanguageandLogic............................................ 1
SQLDefined................................................... 3
MicrosoftSQLServer,Oracle,andMySQL.......................... 3
OtherDatabases............................................... 5
RelationalDatabases ........................................... 6
PrimaryandForeignKeys ....................................... 7
Datatypes..................................................... 8
NULLValues.................................................. 10
TheSignificanceofSQL ........................................ 10
LookingAhead ............................................... 11
Chapter2 BasicDataRetrieval................................... 13
ASimpleSELECT .............................................. 13
SyntaxNotes ................................................. 15
SpecifyingColumns ........................................... 16
ColumnNameswithEmbeddedSpaces........................... 17
LookingAhead ............................................... 18
Chapter3 CalculationsandAliases ............................... 19
CalculatedFields.............................................. 19
LiteralValues................................................. 20
vi
Contents vii
ArithmeticCalculations ........................................ 22
ConcatenatingFields .......................................... 23
ColumnAliases ............................................... 24
TableAliases ................................................. 26
LookingAhead ............................................... 27
Chapter4 UsingFunctions ...................................... 29
TheFunctionofFunctions...................................... 29
CharacterFunctions ........................................... 30
CompositeFunctions .......................................... 36
Date/TimeFunctions........................................... 37
NumericFunctions ............................................ 40
ConversionFunctions.......................................... 41
LookingAhead ............................................... 44
Chapter5 SortingData ......................................... 47
AddingaSort ................................................ 47
SortinginAscendingOrder..................................... 48
SortinginDescendingOrder.................................... 49
SortingbyMultipleColumns.................................... 50
SortingbyaCalculatedField.................................... 51
MoreonSortSequences ....................................... 52
LookingAhead ............................................... 55
Chapter6 Column-BasedLogic .................................. 57
IF-THEN-ELSELogic............................................ 57
TheSimpleFormat ............................................ 58
TheSearchedFormat .......................................... 60
LookingAhead ............................................... 62
Chapter7 Row-BasedLogic ..................................... 63
ApplyingSelectionCriteria ..................................... 63
WHEREClauseOperators....................................... 65
LimitingRows ................................................ 66
LimitingRowswithaSort ...................................... 68
LookingAhead ............................................... 70
Chapter8 BooleanLogic........................................ 71
ComplexLogicalConditions .................................... 71
viii Contents
TheANDOperator ............................................ 72
TheOROperator.............................................. 73
UsingParentheses............................................. 73
MultipleSetsofParentheses.................................... 75
TheNOTOperator ............................................ 76
TheBETWEENOperator........................................ 79
TheINOperator .............................................. 80
BooleanLogicandNULLValues................................. 82
LookingAhead ............................................... 84
Chapter9 InexactMatches...................................... 85
PatternMatching ............................................. 85
Wildcards.................................................... 88
MatchingbySound ........................................... 91
LookingAhead ............................................... 93
Chapter10 SummarizingData.................................... 95
EliminatingDuplicates......................................... 95
AggregateFunctions .......................................... 97
TheCOUNTFunction .......................................... 99
GroupingData .............................................. 101
MultipleColumnsandSorting ................................. 103
SelectionCriteriaonAggregates ............................... 105
LookingAhead .............................................. 107
Chapter11 Combining TableswithanInnerJoin ................... 109
JoiningTwoTables........................................... 110
TheInnerJoin ............................................... 112
TableOrderinInnerJoins ..................................... 114
AlternateSpecificationofInnerJoins ........................... 114
TableAliasesRevisited........................................ 115
LookingAhead .............................................. 116
Chapter12 Combining TableswithanOuterJoin................... 119
TheOuterJoin............................................... 119
LeftJoins ................................................... 122
TestingforNULLValues....................................... 124
RightJoins.................................................. 125
TableOrderinOuterJoins..................................... 125
Contents ix
FullJoins.................................................... 126
LookingAhead .............................................. 128
Chapter13 SelfJoinsandViews ................................. 131
SelfJoins ................................................... 131
CreatingViews .............................................. 134
ReferencingViews ........................................... 136
BenefitsofViews ............................................ 137
ModifyingandDeletingViews................................. 138
LookingAhead .............................................. 139
Chapter14 Subqueries ......................................... 141
TypesofSubqueries .......................................... 141
UsingaSubqueryasaDataSource.............................. 142
UsingaSubqueryinSelectionCriteria........................... 145
CorrelatedSubqueries........................................ 147
TheEXISTSOperator.......................................... 149
UsingaSubqueryasaCalculatedColumn........................ 150
LookingAhead .............................................. 151
Chapter15 SetLogic ........................................... 153
UsingtheUNIONOperator .................................... 154
DistinctandNon-DistinctUnions ............................... 157
IntersectingQueries.......................................... 158
LookingAhead .............................................. 160
Chapter16 StoredProceduresandParameters..................... 163
CreatingStoredProcedures.................................... 164
ParametersinStoredProcedures ............................... 166
ExecutingStoredProcedures................................... 168
ModifyingandDeletingStoredProcedures ...................... 169
FunctionsRevisited........................................... 170
LookingAhead .............................................. 171
Chapter17 ModifyingData ..................................... 173
ModificationStrategies....................................... 173
InsertingData............................................... 174
DeletingData ............................................... 178
UpdatingData............................................... 180