SQL Queries for Mere Mortals Pearson UCertify Course Access Code Card, Fourth Edition : A Hands-On Guide to Data Manipulation in SQL

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: • 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 Software-Independent Approach! 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! Cover 1 Half Title 2 Title Page 6 Copyright Page 7 Contents 10 Foreword 23 Preface 24 Acknowledgments 25 About the Author 26 Introduction 28 Are You a Mere Mortal? 28 About This Book 29 What This Book Is Not 31 How to Use This Book 31 Reading the Diagrams Used in This Book 32 Sample Databases Used in This Book 36 “Follow the Yellow Brick Road” 39 PART I: RELATIONAL DATABASES AND SQL 40 Chapter 1: What Is Relational? 42 Topics Covered in This Chapter 42 Types of Databases 42 A Brief History of the Relational Model 43 In the Beginning 43 Relational Database Systems 44 Anatomy of a Relational Database 46 Tables 47 Columns 48 Rows 48 Keys 49 Views 50 Relationships 52 What’s in It for You? 56 Where Do You Go from Here? 57 Summary 58 Chapter 2: Ensuring Your Database Structure Is Sound 60 Topics Covered in This Chapter 60 Why Is this Chapter Here? 61 Why Worry about Sound Structures? 61 Fine-Tuning Columns 62 What’s in a Name? (Part One) 62 Smoothing Out the Rough Edges 65 Resolving Multipart Columns 67 Resolving Multivalued Columns 70 Fine-Tuning Tables 73 What’s in a Name? (Part Two) 73 Ensuring a Sound Structure 75 Resolving Unnecessary Duplicate Columns 77 Identification Is the Key 83 Establishing Solid Relationships 87 Establishing a Deletion Rule 90 Setting the Type of Participation 91 Setting the Degree of Participation 93 Is That All? 96 Summary 96 Chapter 3: A Concise History of SQL 98 Topics Covered in This Chapter 98 The Origins of SQL 99 Early Vendor Implementations 100 “. . . And Then There Was a Standard” 102 Evolution of the ANSI/ISO Standard 103 Other SQL Standards 106 Commercial Implementations 110 What the Future Holds 110 Why Should You Learn SQL? 111 Which Version of SQL Does this Book Cover? 111 Summary 112 PART II: SQL BASICS 114 Chapter 4: Creating a Simple Query 116 Topics Covered in This Chapter 116 Introducing SELECT 117 The SELECT Statement 118 A Quick Aside: Data versus Information 120 Translating Your Request into SQL 122 Expanding the Field of Vision 127 Using a Shortcut to Request All Columns 128 Eliminating Duplicate Rows 130 Sorting Information 132 First Things First: Collating Sequences 134 Let’s Now Come to Order 135 Saving Your Work 138 Sample Statements 140 Summary 149 Problems for You to Solve 150 Chapter 5: Getting More Than Simple Columns 152 Topics Covered in This Chapter 152 What Is an Expression? 153 What Type of Data Are You Trying to Express? 154 Changing Data Types: The CAST Function 157 Specifying Explicit Values 159 Character String Literals 160 Numeric Literals 162 Datetime Literals 162 Types of Expressions 165 Concatenation 165 Mathematical Expressions 169 Date and Time Arithmetic 173 Using Expressions in a SELECT Clause 177 Working with a Concatenation Expression 178 Naming the Expression 179 Working with a Mathematical Expression 181 Working with a Date Expression 183 A Brief Digression: Value Expressions 184 That “Nothing” Value: Null 186 Introducing Null 187 The Problem with Nulls 189 Sample Statements 190 Summary 199 Problems for You to Solve 200 Chapter 6: Filtering Your Data 202 Topics Covered in This Chapter 202 Refining What You See Using WHERE 203 The WHERE Clause 203 Using a WHERE Clause 206 Defining Search Conditions 208 Comparison 208 Range 216 Set Membership 219 Pattern Match 221 Null 226 Excluding Rows with NOT 228 Using Multiple Conditions 231 Introducing AND and OR 232 Excluding Rows: Take Two 238 Order of Precedence 241 Checking for Overlapping Ranges 246 Nulls Revisited: A Cautionary Note 248 Expressing Conditions in Different Ways 252 Sample Statements 253 Summary 261 Problems for You to Solve 262 PART III: WORKING WITH MULTIPLE TABLES 266 Chapter 7: Thinking in Sets 268 Topics Covered in This Chapter 268 What Is a Set, Anyway? 269 Operations on Sets 270 Intersection 271 Intersection in Set Theory 271 Intersection between Result Sets 273 Problems You Can Solve with an Intersection 276 Difference 277 Difference in Set Theory 277 Difference between Result Sets 279 Problems You Can Solve with Difference 283 Union 284 Union in Set Theory 284 Combining Result Sets Using a Union 286 Problems You Can Solve with Union 288 SQL Set Operations 289 Classic Set Operations versus SQL 289 Finding Common Values: INTERSECT 289 Finding Missing Values: EXCEPT (DIFFERENCE) 292 Combining Sets: UNION 295 Summary 298 Chapter 8: INNER JOINs 300 Topics Covered in This Chapter 300 What Is a JOIN? 300 The INNER JOIN 301 What’s “Legal” to JOIN? 302 Column References 302 Syntax 303 Check Those Relationships! 318 Uses for INNER JOINs 320 Find Related Rows 320 Find Matching Values 320 Sample Statements 321 Two Tables 322 More Than Two Tables 327 Looking for Matching Values 333 Summary 343 Problems for You to Solve 343 Chapter 9: OUTER JOINs 348 Topics Covered in This Chapter 348 What Is an OUTER JOIN? 348 The LEFT/RIGHT OUTER JOIN 350 Syntax 351 The FULL OUTER JOIN 371 Syntax 371 FULL OUTER JOIN on Non-Key Values 374 UNION JOIN 375 Uses for OUTER JOINs 376 Find Missing Values 376 Find Partially Matched Information 376 Sample Statements 377 Summary 392 Problems for You to Solve 393 Chapter 10: UNIONs 396 Topics Covered in This Chapter 396 What Is a UNION? 396 Writing Requests with UNION 399 Using Simple SELECT Statements 399 Combining Complex SELECT Statements 402 Using UNION More Than Once 406 Sorting a UNION 408 Uses for UNION 410 Sample Statements 412 Summary 422 Problems for You to Solve 423 Chapter 11: Subqueries 426 Topics Covered in This Chapter 426 What Is a Subquery? 427 Row Subqueries 427 Table Subqueries 429 Scalar Subqueries 429 Subqueries as Column Expressions 429 Syntax 429 An Introduction to Aggregate Functions: COUNT and MAX 433 Subqueries as Filters 435 Syntax 435 Special Predicate Keywords for Subqueries 438 Uses for Subqueries 449 Build Subqueries as Column Expressions 449 Use Subqueries as Filters 450 Sample Statements 451 Subqueries in Expressions 452 Subqueries in Filters 457 Summary 464 Problems for You to Solve 465 PART IV: SUMMARIZING AND GROUPING DATA 468 Chapter 12: Simple Totals 470 Topics Covered in This Chapter 470 Aggregate Functions 471 Counting Rows and Values with COUNT 473 Computing a Total with SUM 477 Calculating a Mean Value with AVG 478 Finding the Largest Value with MAX 479 Finding the Smallest Value with MIN 481 Using More Than One Function 482 Using Aggregate Functions in Filters 484 Sample Statements 486 Summary 493 Problems for You to Solve 494 Chapter 13: Grouping Data 498 Topics Covered in This Chapter 498 Why Group Data? 499 The GROUP BY Clause 502 Syntax 502 Mixing Columns and Expressions 508 Using GROUP BY in a Subquery in a WHERE Clause 510 Simulating a SELECT DISTINCT Statement 511 “Some Restrictions Apply” 512 Column Restrictions 513 Grouping on Expressions 515 Uses for GROUP BY 517 Sample Statements 518 Summary 528 Problems for You to Solve 528 Chapter 14: Filtering Grouped Data 532 Topics Covered in This Chapter 532 A New Meaning for “Focus Groups” 533 Where You Filter Makes a Difference 537 Should You Filter in WHERE or in HAVING? 537 Avoiding the HAVING COUNT Trap 540 Uses for HAVING 545 Sample Statements 546 Summary 554 Problems for You to Solve 555 PART V: MODIFYING SETS OF DATA 560 Chapter 15: Updating Sets of Data 562 Topics Covered in This Chapter 562 What Is an UPDATE? 563 The UPDATE Statement 563 Using a Simple UPDATE Expression 564 A Brief Aside: Transactions 567 Updating Multiple Columns 568 Using a Subquery to Filter Rows 570 Some Database Systems Allow a JOIN in the UPDATE Clause 573 Using a Subquery UPDATE Expression 575 Uses for UPDATE 578 Sample Statements 579 Summary 596 Problems for You to Solve 596 Chapter 16: Inserting Sets of Data 600 Topics Covered in This Chapter 600 What Is an INSERT? 600 The INSERT Statement 602 Inserting Values 602 Generating the Next Primary Key Value 605 Inserting Data by Using SELECT 608 Uses for INSERT 614 Sample Statements 615 Summary 625 Problems for You to Solve 625 Chapter 17: Deleting Sets of Data 630 Topics Covered in This Chapter 630 What Is a DELETE? 630 The DELETE Statement 631 Deleting All Rows 632 Deleting Some Rows 634 Uses for DELETE 638 Sample Statements 639 Summary 647 Problems for You to Solve 648 PART VI: INTRODUCTION TO SOLVING TOUGH PROBLEMS 652 Chapter 18: “NOT” and “AND” Problems 654 Topics Covered in This Chapter 654 A Short Review of Sets 655 Sets with Multiple AND Criteria 655 Sets with Multiple NOT Criteria 656 Sets Including Some Criteria but Excluding Others 657 Finding Out the “Not” Case 659 Using OUTER JOIN 659 Using NOT IN 662 Using NOT EXISTS 664 Using GROUP BY/HAVING 665 Finding Multiple Matches in the Same Table 668 Using INNER JOIN 669 Using IN 671 Using EXISTS 673 Using GROUP BY/HAVING 675 Sample Statements 679 Summary 698 Problems for You to Solve 699 Chapter 19: Condition Testing 704 Topics Covered in This Chapter 704 Conditional Expressions (CASE) 705 Why Use CASE? 705 Syntax 705 Solving Problems with CASE 710 Solving Problems with Simple CASE 710 Solving Problems with Searched CASE 715 Using CASE in a WHERE Clause 718 Sample Statements 719 Summary 732 Problems for You to Solve 733 Chapter 20: Using Unlinked Data and “Driver” Tables 736 Topics Covered in This Chapter 736 What Is Unlinked Data? 737 Deciding When to Use a CROSS JOIN 740 Solving Problems with Unlinked Data 741 Solving Problems Using “Driver” Tables 744 Setting Up a Driver Table 744 Using a Driver Table 747 Sample Statements 752 Examples Using Unlinked Tables 753 Examples Using Driver Tables 763 Summary 770 Problems for You to Solve 771 Chapter 21: Performing Complex Calculations on Groups 776 Topics in this Chapter 776 Grouping in Sub-Groups 777 Extending the GROUP BY Clause 780 Syntax 780 Getting Totals in a Hierarchy Using Rollup 781 Calculating Totals on Combinations Using CUBE 792 Creating a Union of Totals with GROUPING SETS 798 Variations on Grouping Techniques 802 Sample Statements 807 Examples using ROLLUP 808 Examples using CUBE 810 Examples using GROUPING SETS 813 Summary 815 Problems for You to Solve 816 Chapter 22: Partitioning Data into Windows 820 Topics in this Chapter 820 What You Can Do With a “Window” into Your Data 821 Syntax 825 Calculating a Row Number 841 Ranking Data 845 Splitting Data into Quintiles 851 Using Windows with Aggregate Functions 854 Sample Statements 861 Examples Using ROW_NUMBER 862 Examples Using RANK, DENSE_RANK, and PERCENT_RANK 865 Examples Using NTILE 869 Examples Using Aggregate Functions 871 Summary 879 Problems for You to Solve 880 In Closing 884 PART VII: APPENDICES 886 Appendix A: SQL Standard Diagrams 888 Appendix B: Schema for the Sample Databases 904 Sales Orders Example Database 905 Sales Orders Modify Database 906 Entertainment Agency Example Database 907 Entertainment Agency Modify Database 908 School Scheduling Example Database 909 School Scheduling Modify Database 910 Bowling League Example Database 911 Bowling League Modify Database 912 Recipes Database 913 “Driver” Tables 914 Appendix C: Date and Time Types, Operations, and Functions 916 IBM DB2 916 Microsoft Access 920 Microsoft SQL Server 922 MySQL 924 Oracle 928 PostgreSQL 931 Appendix D: Suggested Reading 934 Database Books 934 Books on SQL 935 Index 936 A 936 B 937 C 937 D 939 E 940 F 941 G 942 H 943 I 943 J 944 K 944 L 944 M 945 N 945 O 946 P 947 Q 947 R 948 S 949 T 954 U 955 V 956 W 956 X-Y 957 "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."--Page 4 of cover