Beginning SQL Queries: From Novice to Professional

Beginning SQL Queries: From Novice to Professional......Page 1
Contents at a Glance......Page 7
Contents......Page 9
About the Author......Page 15
About the Technical Reviewer......Page 17
Acknowledgments......Page 19
Two-Pronged Approach......Page 21
Unknown......Page 0
Objective of This Book......Page 22
What Is a Relational Database?......Page 23
Introducing Data Models......Page 24
Introducing Tables......Page 26
Inserting and Updating Rows in a Table......Page 27
Designing Appropriate Tables......Page 29
Maintaining Consistency Between Tables......Page 31
Retrieving Information from a Database......Page 32
Relational Algebra: Specifying the Operations......Page 33
Relational Calculus: Specifying the Result......Page 35
Why Do We Need Both Algebra and Calculus?......Page 36
Summary......Page 37
Simple Queries on One Table......Page 39
Relational Calculus for Retrieving Rows......Page 42
SQL for Retrieving Rows......Page 43
Relational Calculus for Retrieving Columns......Page 44
Using Aliases......Page 45
Combining Subsets of Rows and Columns......Page 46
Specifying Conditions for Selecting Rows......Page 47
Comparison Operators......Page 48
Logical Operators......Page 49
Dealing with Nulls......Page 51
Comparing Null Values......Page 53
Managing Duplicates......Page 54
Performing Simple Counts......Page 57
Avoiding Common Mistakes......Page 58
Misusing Select to Answer Questions with the Word fbothf......Page 60
Summary......Page 61
Cartesian Product......Page 63
Inner Join......Page 65
SQL for Cartesian Product and Join......Page 66
Joins in Relational Calculus......Page 67
Extending Join Queries......Page 68
An Algebra Approach......Page 69
Order of Algebra Operations......Page 72
A Calculus Approach......Page 73
Expressing Joins Through Diagrammatic Interfaces......Page 75
Other Types of Joins......Page 76
Summary......Page 80
IN Keyword......Page 83
Using IN with a Nested Query......Page 84
Being Careful with NOT and <>......Page 86
EXISTS Keyword......Page 89
Different Types of Nesting......Page 91
Inner Queries Returning a Single Value......Page 92
Inner Queries Checking for Existence......Page 94
Using Nested Queries for Updating......Page 95
Summary......Page 97
Self Relationships......Page 99
Creating a Self Join......Page 101
Queries Involving a Self Join......Page 102
List the Names of All the Members and the Names of Their Coaches......Page 103
Who Coaches the Coaches, or Who Is My Grandmother?......Page 105
A Calculus Approach to Self Joins......Page 107
Questions Involving fiBthf......Page 110
A Calculus Approach to Questions Involving fiothf......Page 112
An Algebra Approach to Questions Involving fiothf......Page 113
Self Relationships......Page 114
Questions Involving the Word fBothf......Page 115
Representing Multiple Relationships Between Tables......Page 117
Algebra Approach to Two Relationships Between Tables......Page 119
Calculus Approach to Two Relationships Between Tables......Page 123
Business Rules......Page 124
Summary......Page 127
Set Operations......Page 129
Overview of Basic Set Operations......Page 130
Union-Compatible Tables......Page 131
Union......Page 133
Ensuring Union Compatibility......Page 134
Selecting the Appropriate Columns......Page 135
Uses for Union......Page 136
Uses of Intersection......Page 139
The Importance of Projecting Appropriate Columns......Page 142
Managing Without the INTERSECT Keyword......Page 144
Difference......Page 145
Uses of Difference......Page 146
Managing Without the EXCEPT Keyword......Page 148
Division......Page 149
Projecting Appropriate Columns......Page 151
SQL for Division......Page 152
Summary......Page 154
The COUNT Function......Page 155
The AVG Function......Page 158
Other Aggregate Functions......Page 160
Grouping......Page 161
Filtering the Result of an Aggregate Query......Page 165
Using Aggregates to Perform Division Operations......Page 167
Nested Queries and Aggregates......Page 169
Summary......Page 172
Indexes......Page 175
Types of Indexes......Page 176
Indexes for Efficiently Ordering Output......Page 179
Indexes and Joins......Page 180
What Should We Index?......Page 182
What Does the Query Optimizer Consider?......Page 183
Does the Way We Express the Query Matter?......Page 184
Summary......Page 189
Determine the Relationships Between Tables......Page 191
The Conceptual Model vs. the Implementation......Page 193
What Tables Are Involved?......Page 195
Big Picture Approach......Page 196
Combine the Tables......Page 197
Find the Subset of Rows......Page 198
Retain the Appropriate Columns......Page 199
Consider an Intermediate View......Page 200
And, Both, Also......Page 201
Not, Never......Page 204
Try to Answer the Question by Hand......Page 205
Write Down a Description of the Retrieved Result......Page 206
Is There Another Way?......Page 207
Checking Queries......Page 208
Check Boundary Conditions......Page 209
Summary......Page 210
Data That Is Not Normalized......Page 213
No Keys......Page 216
Similar Data in Two Tables......Page 217
Wrong Types......Page 218
Wrong or Inconsistent Spelling......Page 219
Extraneous Characters in Text Fields......Page 220
Inconsistent Case in Text Fields......Page 221
Diagnosing Problems......Page 222
Remove Extra WHERE Clauses......Page 223
No Rows Are Returned......Page 224
Have Selection Conditions Dealt with Nulls Appropriately?......Page 225
Have You Used AND Instead of OR?......Page 226
Did You Use NOT Instead of Difference?......Page 227
Have You Dealt with Duplicates Appropriately?......Page 228
Common Typos and Syntax Problems......Page 229
Summary......Page 230
Sample Database......Page 231
Index......Page 233