Published by Addison-Wesley Professional (January 31, 2018) © 2018
John Viescas
The #1 Easy, Common-Sense Guide to SQL Queries—Updated with More Advanced Techniques and Solutions
Foreword by Keith W. Hare, Vice Chair, USA SQL Standards Committee
SQL Queries for Mere Mortals has earned worldwide praise as the clearest, simplest tutorial on writing effective queries with the latest SQL standards and database applications. Now, author John L. Viescas has updated this hands-on classic with even more advanced and valuable techniques.
Step by step, Viescas guides you through creating reliable queries for virtually any current SQL-based database. He demystifies all aspects of SQL query writing, from simple data selection and filtering to joining multiple tables and modifying sets of data.
Building on the basics, Viescas shows how to solve challenging real-world problems, including applying multiple complex conditions on one table, performing sophisticated logical evaluations, and using unlinked tables to think “outside the box.”
In two brand-new chapters, you learn how to perform complex calculations on groups for sophisticated reporting, and how to partition data into windows for more flexible aggregation.
Practice all you want with downloadable sample databases for today’s versions of Microsoft Office Access, Microsoft SQL Server, and the open source MySQL and PostgreSQL databases. Whether you’re a DBA, developer, user, or student, there’s no better way to master SQL.
Coverage includes:
If you work with database software such as Access, MS SQL Server, Oracle, DB2, MySQL, Ingres, or any other SQL-based program, this book could save you hours of time and aggravation—before you write a single query!
Foreword by Keith W. Hare, Vice Chair, USA SQL Standards Committee
SQL Queries for Mere Mortals has earned worldwide praise as the clearest, simplest tutorial on writing effective queries with the latest SQL standards and database applications. Now, author John L. Viescas has updated this hands-on classic with even more advanced and valuable techniques.
Step by step, Viescas guides you through creating reliable queries for virtually any current SQL-based database. He demystifies all aspects of SQL query writing, from simple data selection and filtering to joining multiple tables and modifying sets of data.
Building on the basics, Viescas shows how to solve challenging real-world problems, including applying multiple complex conditions on one table, performing sophisticated logical evaluations, and using unlinked tables to think “outside the box.”
In two brand-new chapters, you learn how to perform complex calculations on groups for sophisticated reporting, and how to partition data into windows for more flexible aggregation.
Practice all you want with downloadable sample databases for today’s versions of Microsoft Office Access, Microsoft SQL Server, and the open source MySQL and PostgreSQL databases. Whether you’re a DBA, developer, user, or student, there’s no better way to master SQL.
Coverage includes:
- Getting started: understanding what relational databases are, and ensuring that your database structures are sound
- SQL basics: using SELECT statements, creating expressions, sorting information with ORDER BY, and filtering data using WHERE
- Summarizing and grouping data with GROUP BY and HAVING clauses
- Drawing data from multiple tables: using INNER JOIN, OUTER JOIN, and UNION operators, and working with subqueries
- Modifying data sets with UPDATE, INSERT, and DELETE statements
- Advanced queries: complex NOT and AND, conditions, if-then-else using CASE, unlinked tables, driver tables, and more
- NEW! Using advanced GROUP BY keywords to create subtotals, roll-ups, and more
- NEW! Applying window functions to answer more sophisticated questions, and gain deeper insight into your data
If you work with database software such as Access, MS SQL Server, Oracle, DB2, MySQL, Ingres, or any other SQL-based program, this book could save you hours of time and aggravation—before you write a single query!
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
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