The #1 Easy, Common-Sense Guide to SQL Queries-Updated with More Advanced Techniques and SolutionsForeword by Keith W. Hare, Vice Chair, USA SQL Standards CommitteeSQL 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:

Foreword xxiPreface xxiiAcknowledgments xxivAbout 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 13Chapter 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 87Chapter 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 239Chapter 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 441Chapter 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 533Chapter 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 625Chapter 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 709What 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 859Appendix 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