SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th edition

Foreword     xxi
Preface     xxii
Acknowledgments     xxiv
About the Author     xxv
Introduction     1
Are You a Mere Mortal?     1
About This Book     2
What This Book Is Not     4
How to Use This Book     4
Reading the Diagrams Used in This Book     5
Sample Databases Used in This Book     9
    “Follow the Yellow Brick Road”     12

PART I:  RELATIONAL DATABASES AND SQL     13
Chapter 1:  What Is Relational?     15
Types of Databases     15
A Brief History of the Relational Model     16
    In the Beginning . . .     16
    Relational Database Systems     17
Anatomy of a Relational Database     19
    Tables     20
    Columns     21
    Rows     21
    Keys     22
    Views     23
    Relationships     25
What’s in It for You?     29
    Where Do You Go from Here?     30
Summary     31
Chapter  2:  Ensuring Your Database Structure Is Sound     33
Why Is this Chapter Here?     34
Why Worry about Sound Structures?     34
Fine-Tuning Columns     35
    What’s in a Name? (Part One)     35
    Smoothing Out the Rough Edges     38
    Resolving Multipart Columns     40
    Resolving Multivalued Columns     43
Fine-Tuning Tables     46
    What’s in a Name? (Part Two)     46
    Ensuring a Sound Structure     48
    Resolving Unnecessary Duplicate Columns     50
    Identification Is the Key     56
Establishing Solid Relationships     60
    Establishing a Deletion Rule     63
    Setting the Type of Participation     64
    Setting the Degree of Participation     66
Is That All?     69
Summary     69
Chapter 3:  A Concise History of SQL     71
The Origins of SQL     72
Early Vendor Implementations     73
“. . . And Then There Was a Standard”     75
Evolution of the ANSI/ISO Standard     76
    Other SQL Standards     79
Commercial Implementations     83
What the Future Holds     83
Why Should You Learn SQL?     84
Which Version of SQL Does this Book Cover?     84
Summary     85

PART II:  SQL BASICS     87
Chapter 4:  Creating a Simple Query     89
Introducing SELECT     90
The SELECT Statement     91
A Quick Aside: Data versus Information     93
Translating Your Request into SQL     95
    Expanding the Field of Vision     100
    Using a Shortcut to Request All Columns     101
Eliminating Duplicate Rows     103
Sorting Information     105
    First Things First: Collating Sequences     107
    Let’s Now Come to Order     108
Saving Your Work     111
Sample Statements     113
Summary     122
Problems for You to Solve     123
Chapter 5:  Getting More Than Simple Columns     125
What Is an Expression?     126
What Type of Data Are You Trying to Express?     127
Changing Data Types: The CAST Function     130
Specifying Explicit Values     132
    Character String Literals     133
    Numeric Literals     135
    Datetime Literals     135
Types of Expressions     138
    Concatenation     138
    Mathematical Expressions     142
    Date and Time Arithmetic     146
Using Expressions in a SELECT Clause     150
    Working with a Concatenation Expression     151
    Naming the Expression     152
    Working with a Mathematical Expression     154
    Working with a Date Expression     156
    A Brief Digression: Value Expressions     157
That “Nothing” Value: Null     159
    Introducing Null     160
    The Problem with Nulls     162
Sample Statements     163
Summary     172
Problems for You to Solve     173
Chapter 6:  Filtering Your Data     175
Refining What You See Using WHERE     176
    The WHERE Clause     176
    Using a WHERE Clause     179
Defining Search Conditions     181
    Comparison     181
    Range     189
    Set Membership     192
    Pattern Match     194
    Null     199
    Excluding Rows with NOT     201
Using Multiple Conditions     204
    Introducing AND and OR     205
    Excluding Rows: Take Two     211
    Order of Precedence     214
    Checking for Overlapping Ranges     219
Nulls Revisited: A Cautionary Note     221
Expressing Conditions in Different Ways     225
Sample Statements     226
Summary     234
Problems for You to Solve     235

PART III:  WORKING WITH MULTIPLE TABLES     239
Chapter 7:  Thinking in Sets     241
What Is a Set, Anyway?     242
Operations on Sets     243
Intersection     244
    Intersection in Set Theory     244
    Intersection between Result Sets     246
    Problems You Can Solve with an Intersection     249
Difference     250
    Difference in Set Theory     250
    Difference between Result Sets     252
    Problems You Can Solve with Difference     256
Union     257
    Union in Set Theory     257
    Combining Result Sets Using a Union     259
    Problems You Can Solve with Union     261
SQL Set Operations     262
    Classic Set Operations versus SQL     262
    Finding Common Values: INTERSECT     262
    Finding Missing Values: EXCEPT (DIFFERENCE)     265
    Combining Sets: UNION     268
Summary     271
Chapter 8:  INNER JOINs     273
What Is a JOIN?     273
The INNER JOIN     274
    What’s “Legal” to JOIN?     275
    Column References     275
    Syntax     276
    Check Those Relationships!     291
Uses for INNER JOINs     293
    Find Related Rows     293
    Find Matching Values     293
Sample Statements     294
    Two Tables     295
    More Than Two Tables     300
    Looking for Matching Values     306
Summary     316
Problems for You to Solve     316
Chapter 9:  OUTER JOINs     321
What Is an OUTER JOIN?     321
The LEFT/RIGHT OUTER JOIN     323
    Syntax     324
The FULL OUTER JOIN     344
    Syntax     344
    FULL OUTER JOIN on Non-Key Values     347
    UNION JOIN     348
Uses for OUTER JOINs     349
    Find Missing Values     349
    Find Partially Matched Information     349
Sample Statements     350
Summary     365
Problems for You to Solve     366
Chapter 10:  UNIONs     369
What Is a UNION?     369
Writing Requests with UNION     372
    Using Simple SELECT Statements     372
    Combining Complex SELECT Statements     375
    Using UNION More Than Once     379
    Sorting a UNION     381
Uses for UNION     383
Sample Statements     385
Summary     395
Problems for You to Solve     396
Chapter 11:  Subqueries     399
What Is a Subquery?     400
    Row Subqueries     400
    Table Subqueries     402
    Scalar Subqueries     402
Subqueries as Column Expressions     402
    Syntax     402
    An Introduction to Aggregate Functions: COUNT and MAX     406
Subqueries as Filters     408
    Syntax     408
    Special Predicate Keywords for Subqueries     411
Uses for Subqueries     422
    Build Subqueries as Column Expressions     422
    Use Subqueries as Filters     423
Sample Statements     424
    Subqueries in Expressions     425
    Subqueries in Filters     430
Summary     437
Problems for You to Solve     438

PART IV:  SUMMARIZING AND GROUPING DATA     441
Chapter 12:  Simple Totals     443
Aggregate Functions     444
    Counting Rows and Values with COUNT     446
    Computing a Total with SUM     450
    Calculating a Mean Value with AVG     451
    Finding the Largest Value with MAX     452
    Finding the Smallest Value with MIN     454
    Using More Than One Function     455
Using Aggregate Functions in Filters     457
Sample Statements     459
Summary     466
Problems for You to Solve     467
Chapter 13:  Grouping Data     471
Why Group Data?     472
The GROUP BY Clause     475
    Syntax     475
    Mixing Columns and Expressions     481
    Using GROUP BY in a Subquery in a WHERE Clause     483
    Simulating a SELECT DISTINCT Statement     484
“Some Restrictions Apply”     485
    Column Restrictions     486
    Grouping on Expressions     488
Uses for GROUP BY     490
Sample Statements     491
Summary     501
Problems for You to Solve     501
Chapter 14:  Filtering Grouped Data     505
A New Meaning for “Focus Groups”     506
Where You Filter Makes a Difference     510
    Should You Filter in WHERE or in HAVING?     510
    Avoiding the HAVING COUNT Trap     513
Uses for HAVING     518
Sample Statements     519
Summary     527
Problems for You to Solve     528

PART V:  MODIFYING SETS OF DATA     533
Chapter 15:  Updating Sets of Data     535
What Is an UPDATE?     536
The UPDATE Statement     536
    Using a Simple UPDATE Expression     537
    A Brief Aside: Transactions     540
    Updating Multiple Columns     541
    Using a Subquery to Filter Rows     543
Some Database Systems Allow a JOIN in the UPDATE Clause     546
    Using a Subquery UPDATE Expression     548
Uses for UPDATE     551
Sample Statements     552
Summary     569
Problems for You to Solve     569
Chapter 16:  Inserting Sets of Data     573
What Is an INSERT?     573
The INSERT Statement     575
    Inserting Values     575
    Generating the Next Primary Key Value     578
    Inserting Data by Using SELECT     581
Uses for INSERT     587
Sample Statements     588
Summary     598
Problems for You to Solve     598
Chapter 17:  Deleting Sets of Data     603
What Is a DELETE?     603
The DELETE Statement     604
    Deleting All Rows     605
    Deleting Some Rows     607
Uses for DELETE     611
Sample Statements     612
Summary     620
Problems for You to Solve     621

PART VI:  INTRODUCTION TO SOLVING TOUGH PROBLEMS     625
Chapter 18:  “NOT” and “AND” Problems     627
A Short Review of Sets     628
    Sets with Multiple AND Criteria     628
    Sets with Multiple NOT Criteria     629
    Sets Including Some Criteria but Excluding Others     630
Finding Out the “Not” Case     632
    Using OUTER JOIN     632
    Using NOT IN     635
    Using NOT EXISTS     637
    Using GROUP BY/HAVING     638
Finding Multiple Matches in the Same Table     641
    Using INNER JOIN     642
    Using IN     644
    Using EXISTS     646
    Using GROUP BY/HAVING     648
Sample Statements     652
Summary     671
Problems for You to Solve     672
Chapter 19:  Condition Testing     677
Conditional Expressions (CASE)     678
    Why Use CASE?     678
    Syntax     678
Solving Problems with CASE     683
    Solving Problems with Simple CASE     683
    Solving Problems with Searched CASE     688
    Using CASE in a WHERE Clause     691
Sample Statements     692
Summary     705
Problems for You to Solve     706
Chapter 20:  Using Unlinked Data and “Driver” Tables     709
What Is Unlinked Data?     710
    Deciding When to Use a CROSS JOIN     713
Solving Problems with Unlinked Data     714
Solving Problems Using “Driver” Tables     717
    Setting Up a Driver Table     717
    Using a Driver Table     720
Sample Statements     725
    Examples Using Unlinked Tables     726
    Examples Using Driver Tables     736
Summary     743
Problems for You to Solve     744
Chapter 21:  Performing Complex Calculations on Groups     749
Grouping in Sub-Groups     750
Extending the GROUP BY Clause     753
    Syntax     753
Getting Totals in a Hierarchy Using Rollup     754
Calculating Totals on Combinations Using CUBE     765
Creating a Union of Totals with GROUPING SETS     771
Variations on Grouping Techniques     775
Sample Statements     780
    Examples using ROLLUP     781
    Examples using CUBE     783
    Examples using GROUPING SETS     786
Summary     788
Problems for You to Solve     789
Chapter 22:  Partitioning Data into Windows     793
What You Can Do With a “Window” into Your Data     794
    Syntax     798
Calculating a Row Number     814
Ranking Data     818
Splitting Data into Quintiles     824
Using Windows with Aggregate Functions     827
Sample Statements     834
    Examples Using ROW_NUMBER     835
    Examples Using RANK, DENSE_RANK, and PERCENT_RANK     838
    Examples Using NTILE     842
    Examples Using Aggregate Functions     844
Summary     852
Problems for You to Solve     853
In Closing     857

PART VII:  APPENDICES     859
Appendix A:  SQL Standard Diagrams     861
Appendix B:  Schema for the Sample Databases     877
Sales Orders Example Database     878
Sales Orders Modify Database     879
Entertainment Agency Example Database     880
Entertainment Agency Modify Database     881
School Scheduling Example Database     882
School Scheduling Modify Database     883
Bowling League Example Database     884
Bowling League Modify Database     885
Recipes Database     886
“Driver” Tables     887
Appendix C:  Date and Time Types, Operations, and Functions     889
IBM DB2     889
Microsoft Access     893
Microsoft SQL Server     895
MySQL     897
Oracle     901
PostgreSQL     904
Appendix D:  Suggested Reading     907
Database Books     907
Books on SQL     908
Index     909