Beginning SQL Queries (eBook)

Clare Churcher is currently a senior lecturer in the Department of Applied Computing at Lincoln University, New Zealand. She holds a degree in physics with first class honors and completed a Ph.D in physics at the University of Canterbury, New Zealand. She has done postdoctoral research in the Cavendish Laboratory at the University of Cambridge, England. Clare s research interests are in the management and visualization of data especially for scientific research. She has a background in database design, and has taught programming, analysis and design of information systems, and database management at undergraduate level, as well as software engineering and scientific visualization at post graduate level.

Contents at a Glance 5
Contents 6
About the Author 12
About the Technical Reviewer 13
Acknowledgments 14
Introduction 15
Relational Database Overview 17
What Is a Relational Database? 17
Retrieving Information from a Database 26
Summary 31
Simple Queries on One Table 32
Retrieving a Subset of Rows 35
Retrieving a Subset of Columns 37
Using Aliases 38
Combining Subsets of Rows and Columns 39
Saving Queries 40
Specifying Conditions for Selecting Rows 40
Dealing with Nulls 44
Managing Duplicates 47
Ordering Output 50
Performing Simple Counts 50
Avoiding Common Mistakes 51
Summary 54
A First Look at Joins 56
Joins in Relational Algebra 56
Joins in Relational Calculus 60
Extending Join Queries 61
Other Types of Joins 69
Summary 73
Nested Queries 75
IN Keyword 75
Using IN with a Nested Query 76
Being Careful with NOT and < >
EXISTS Keyword 81
Different Types of Nesting 83
Using Nested Queries for Updating 87
Summary 89
Self Joins 90
Self Relationships 90
Questions Involving “ Both” 101
Summary 105
More Than One Relationship Between Tables 107
Representing Multiple Relationships Between Tables 107
Algebra Approach to Two Relationships Between Tables 109
Calculus Approach to Two Relationships Between Tables 113
Business Rules 114
Summary 117
Set Operations 118
Overview of Basic Set Operations 119
Union- Compatible Tables 120
Union 122
Intersection 128
Difference 134
Division 138
Summary 143
Aggregate Operations 144
Simple Aggregates 144
Grouping 150
Nested Queries and Aggregates 158
Summary 161
Efficiency Considerations 163
Indexes 163
Query Optimizer 171
Summary 177
How to Approach a Query 178
Understanding the Data 178
Big Picture Approach 183
Spotting Key Words in Questions 188
No Idea Where to Start? 192
Checking Queries 195
Summary 197
Common Problems 199
Poor Database Design 199
Problems with Data Values 205
Diagnosing Problems 208
Common Symptoms 210
Common Typos and Syntax Problems 215
Summary 216
Sample Database 217
Index 219