SQL Queries for Mere Mortals

John L. Viescas is an independent database consultant with more than 45 years of experience. He began his career as a systems analyst, designing large database applications for IBM mainframe systems. He spent 6 years at Applied Data Research in Dallas, Texas, where he directed a staff of more than 30 people and was responsible for research, product development, and customer support of database products for IBM mainframe computers. While working at Applied Data Research, John completed a degree in business finance at the University of Texas at Dallas, graduating cum laude.   John joined Tandem Computers, Inc., in 1988, where he was responsible for the development and implementation of database marketing programs in Tandem’s U.S. Western Sales region. He developed and delivered technical seminars on Tandem’s relational database management system, NonStop SQL. John wrote his first book, A Quick Reference Guide to SQL (Microsoft Press, 1989), as a research project to document the similarities in the syntax among the ANSI-86 SQL standard, IBM’s DB2, Microsoft’s SQL Server, Oracle Corporation’s Oracle, and Tandem’s NonStop SQL. He wrote the first edition of Running Microsoft Access (Microsoft Press, 1992) while on sabbatical from Tandem. He has since written four editions of Running, three editions of Microsoft Office Access Inside Out (Microsoft Press, 2003, 2007, and 2010–the successor to the Running series), and Building Microsoft Access Applications (Microsoft Press, 2005).   John formed his own company in 1993. He provides information systems management consulting for a variety of small to large businesses around the world, with a specialty in the Microsoft Access and SQL Server database management products. He maintains offices in Nashua, New Hampshire, and Paris, France. He has been recognized as a “Most Valuable Professional” (MVP) since 1993 by Microsoft Product Support Services for his assistance with technical questions on public support forums. He set a landmark 20 consecutive years as an MVP in 2013.   You can visit John’s Web site at www.viescas.com or contact him by e-mail at john@viescas.com.   Michael J. Hernandez has been an independent relational database consultant specializing in relational database design. He has more than 20 years of experience in the technology industry, developing database applications for a wide variety of clients. He’s been a contributing author to a wide variety of magazine columns, white papers, books, and periodicals, and is coauthor of the best-selling SQL Queries for Mere Mortals.   Mike has been a top-rated and noted technical trainer for the government,the military, the private sector, and companies throughout the United States. He has spoken at numerous national and international conferences, and has consistently been a top-rated speaker and presenter.   Aside from his technical background,Mike has a diverse set of skills and interests that he also pursues, ranging from the artistic to the metaphysical. His greatest interest is still the guitar, as he’s been a practicing guitarist for more than 40 years and played professionally for 15 years. He’s also a working actor, a great cook, loves to teach (writing,public speaking,music), has a gift for bad puns, and even reads Tarot cards.   He says he’s never going to retire, per se, but rather just change whatever it is he’s doing whenever he finally gets tired of it and move on to something else that interests him.

Foreword xvii

Preface xix

About the Authors xxi

Introduction xxiii

Are You a Mere Mortal? xxiii

About This Book xxiv

What This Book Is Not xxvi

How to Use This Book xxvi

Reading the Diagrams Used in This Book xxvii

Sample Databases Used in This Book xxxi

“Follow the Yellow Brick Road” xxxiii

Part I Relational Databases and SQL 1

Chapter 1 What Is Relational? 3

Types of Databases 3

A Brief History of the Relational Model 4

In the Beginning . . . 4

Relational Database Systems 5

Anatomy of a Relational Database 7

Tables 7

Fields 9

Records 9

Keys 9

Views 11

Relationships 12

What’s in It for You? 17

Where Do You Go from Here? 18

Summary 19

Chapter 2 Ensuring Your Database Structure Is Sound 21

Why Is This Chapter Here? 21

Why Worry about Sound Structures? 22

Fine-Tuning Fields 23

What’s in a Name? (Part One) 23

Smoothing Out the Rough Edges 25

Resolving Multipart Fields 27

Resolving Multivalued Fields 30

Fine-Tuning Tables 32

What’s in a Name? (Part Two) 33

Ensuring a Sound Structure 35

Resolving Unnecessary Duplicate Fields 36

Identification Is the Key 42

Establishing Solid Relationships 45

Establishing a Deletion Rule 48

Setting the Type of Participation 49

Setting the Degree of Participation 52

Is That All? 54

Summary 55

Chapter 3 A Concise History of SQL 57

The Origins of SQL 58

Early Vendor Implementations 59

“. . . And Then There Was a Standard” 60

Evolution of the ANSI/ISO Standard 62

Other SQL Standards 65

Commercial Implementations 68

What the Future Holds 69

Why Should You Learn SQL? 69

Which Version of SQL Does This Book Cover? 70

Summary 70

Part II SQL Basics 73

Chapter 4 Creating a Simple Query 75

Introducing SELECT 76

The SELECT Statement 77

A Quick Aside: Data versus Information 79

Translating Your Request into SQL 81

Expanding the Field of Vision 85

Using a Shortcut to Request All Columns 87

Eliminating Duplicate Rows 88

Sorting Information 91

First Things First: Collating Sequences 92

Let’s Now Come to Order 93

Saving Your Work 96

Sample Statements 97

Summary 106

Problems for You to Solve 107

Chapter 5 Getting More Than Simple Columns 109

What Is an Expression? 110

What Type of Data Are You Trying to Express? 111

Changing Data Types: The CAST Function 114

Specifying Explicit Values 116

Character String Literals 116

Numeric Literals 118

Datetime Literals 119

Types of Expressions 121

Concatenation 122

Mathematical Expressions 125

Date and Time Arithmetic 129

Using Expressions in a SELECT Clause 133

Working with a Concatenation Expression 134

Naming the Expression 135

Working with a Mathematical Expression 137

Working with a Date Expression 138

A Brief Digression: Value Expressions 139

That “Nothing” Value: Null 141

Introducing Null 142

The Problem with Nulls 143

Sample Statements 144

Summary 153

Problems for You to Solve 154

Chapter 6 Filtering Your Data 157

Refining What You See Using WHERE 157

The WHERE Clause 158

Using a WHERE Clause 160

Defining Search Conditions 162

Comparison 163

Range 170

Set Membership 173

Pattern Match 175

Null 179

Excluding Rows with NOT 181

Using Multiple Conditions 184

Introducing AND and OR 185

Excluding Rows: Take Two 191

Order of Precedence 193

Checking for Overlapping Ranges 197

Nulls Revisited: A Cautionary Note 199

Expressing Conditions in Different Ways 203

Sample Statements 204

Summary 212

Problems for You to Solve 213

Part III Working with Multiple Tables 217

Chapter 7 Thinking in Sets 219

What Is a Set, Anyway? 220

Operations on Sets 221

Intersection 222

Intersection in Set Theory 222

Intersection between Result Sets 224

Problems You Can Solve with an Intersection 227

Difference 228

Difference in Set Theory 228

Difference between Result Sets 230

Problems You Can Solve with Difference 233

Union 234

Union in Set Theory 234

Combining Result Sets Using a Union 236

Problems You Can Solve with Union 238

SQL Set Operations 239

Classic Set Operations versus SQL 239

Finding Common Values: INTERSECT 240

Finding Missing Values: EXCEPT (DIFFERENCE) 243

Combining Sets: UNION 245

Summary 248

Chapter 8 INNER JOINs 249

What Is a JOIN? 249

The INNER JOIN 250

What’s “Legal” to JOIN? 250

Column References 251

Syntax 252

Check Those Relationships! 267

Uses for INNER JOINs 268

Find Related Rows 268

Find Matching Values 269

Sample Statements 269

Two Tables 270

More Than Two Tables 276

Looking for Matching Values 283

Summary 294

Problems for You to Solve 295

Chapter 9 OUTER JOINs 299

What Is an OUTER JOIN? 299

The LEFT/RIGHT OUTER JOIN 301

Syntax 302

The FULL OUTER JOIN 320

Syntax 320

FULL OUTER JOIN on Non-Key Values 323

UNION JOIN 323

Uses for OUTER JOINs 324

Find Missing Values 324

Find Partially Matched Information 325

Sample Statements 325

Summary 341

Problems for You to Solve 341

Chapter 10 UNIONs 345

What Is a UNION? 345

Writing Requests with UNION 348

Using Simple SELECT Statements 348

Combining Complex SELECT Statements 351

Using UNION More Than Once 355

Sorting a UNION 357

Uses for UNION 358

Sample Statements 359

Summary 371

Problems for You to Solve 372

Chapter 11 Subqueries 375

What Is a Subquery? 376

Row Subqueries 376

Table Subqueries 377

Scalar Subqueries 378

Subqueries as Column Expressions 378

Syntax 378

An Introduction to Aggregate Functions: COUNT and MAX 381

Subqueries as Filters 384

Syntax 384

Special Predicate Keywords for Subqueries 386

Uses for Subqueries 397

Build Subqueries as Column Expressions 397

Use Subqueries as Filters 398

Sample Statements 399

Subqueries in Expressions 399

Subqueries in Filters 405

Summary 413

Problems for You to Solve 414

Part IV Summarizing and Grouping Data 417

Chapter 12 Simple Totals 419

Aggregate Functions 420

Counting Rows and Values with COUNT 422

Computing a Total with SUM 425

Calculating a Mean Value with AVG 427

Finding the Largest Value with MAX 428

Finding the Smallest Value with MIN 430

Using More Than One Function 431

Using Aggregate Functions in Filters 432

Sample Statements 435

Summary 442

Problems for You to Solve 443

Chapter 13 Grouping Data 445

Why Group Data? 446

The GROUP BY Clause 448

Syntax 449

Mixing Columns and Expressions 454

Using GROUP BY in a Subquery in a WHERE Clause 456

Simulating a SELECT DISTINCT Statement 457

“Some Restrictions Apply” 458

Column Restrictions 459

Grouping on Expressions 461

Uses for GROUP BY 462

Sample Statements 463

Summary 474

Problems for You to Solve 475

Chapter 14 Filtering Grouped Data 477

A New Meaning of “Focus Groups” 478

Where You Filter Makes a Difference 482

Should You Filter in WHERE or in HAVING? 482

Avoiding the HAVING COUNT Trap 485

Uses for HAVING 490

Sample Statements 491

Summary 499

Problems for You to Solve 500

Part V Modifying Sets of Data 503

Chapter 15 Updating Sets of Data 505

What Is an UPDATE? 505

The UPDATE Statement 506

Using a Simple UPDATE Expression 507

A Brief Aside: Transactions 510

Updating Multiple Columns 511

Using a Subquery to Filter Rows 512

Using a Subquery UPDATE Expression 518

Uses for UPDATE 520

Sample Statements 521

Summary 538

Problems for You to Solve 538

Chapter 16 Inserting Sets of Data 541

What Is an INSERT? 541

The INSERT Statement 543

Inserting Values 543

Generating the Next Primary Key Value 547

Inserting Data by Using SELECT 548

Uses for INSERT 555

Sample Statements 556

Summary 568

Problems for You to Solve 568

Chapter 17 Deleting Sets of Data 571

What Is a DELETE? 571

The DELETE Statement 572

Deleting All Rows 573

Deleting Some Rows 575

Uses for DELETE 579

Sample Statements 580

Summary 588

Problems for You to Solve 589

Part VI Introduction to Solving Tough Problems 591

Chapter 18 “NOT” and “AND” Problems 593

A Short Review of Sets 593

Sets with Multiple AND Criteria 594

Sets with Multiple NOT Criteria 595

Sets Including Some Criteria but Excluding Others 596

Finding Out the “Not” Case 597

Using OUTER JOIN 598

Using NOT IN 601

Using NOT EXISTS 603

Using GROUP BY/HAVING 604

Finding Multiple Matches in the Same Table 607

Using INNER JOIN 608

Using IN 610

Using EXISTS 612

Using GROUP BY/HAVING 614

Sample Statements 618

Summary 636

Problems for You to Solve 637

Chapter 19 Condition Testing 641

Conditional Expressions (CASE) 641

Why Use CASE? 642

Syntax 642

Solving Problems with CASE 647

Solving Problems with Simple CASE 647

Solving Problems with Searched CASE 652

Using CASE in a WHERE Clause 655

Sample Statements 655

Summary 669

Problems for You to Solve 669

Chapter 20 Using Unlinked Data and “Driver” Tables 671

What Is Unlinked Data? 672

Deciding When to Use a CROSS JOIN 675

Solving Problems with Unlinked Data 676

Solving Problems Using “Driver” Tables 679

Setting Up a Driver Table 679

Using a Driver Table 682

Sample Statements 686

Examples Using Unlinked Tables 687

Examples Using Driver Tables 697

Summary 705

Problems for You to Solve 705

In Closing 709

Appendices 711

Appendix A SQL Standard Diagrams 713

Appendix B Schema for the Sample Databases 723

Sales Orders Example Database 724

Sales Orders Modify Database 725

Entertainment Agency Example Database 726

Entertainment Agency Modify Database 727

School Scheduling Example Database 728

School Scheduling Modify Database 729

Bowling League Example Database 730

Bowling League Modify Database 731

Recipes Database 732

Appendix C Date and Time Types, Operations, and Functions 733

IBM DB2 733

Microsoft Office Access 736

Microsoft SQL Server 738

MySQL 740

Oracle 743

Appendix D Suggested Reading 745

Database Books 745

Books on SQL 745

 

 

9780321992475 TOC 5/20/2014