The #1 Easy, Common-Sense Guide to SQL Queries—Updated for Today’s Databases, Standards, and Challenges   SQL Queries for Mere Mortals® has earned worldwide praise as the clearest, simplest tutorial on writing effective SQL queries. The authors have updated this hands-on classic to reflect new SQL standards and database applications and teach valuable new techniques.   Step by step, John L. Viescas and Michael J. Hernandez guide you through creating reliable queries for virtually any modern SQL-based database. They demystify all aspects of SQL query writing, from simple data selection and filtering to joining multiple tables and modifying sets of data.   Three brand-new chapters teach you how to solve a wide range of challenging SQL problems. You’ll learn how to write queries that apply multiple complex conditions on one table, perform sophisticated logical evaluations, and think “outside the box” using unlinked tables.   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   Practice all you want with downloadable sample databases for today’s versions of Microsoft Office Access, Microsoft SQL Server, and the open source MySQL database. Whether you’re a DBA, developer, user, or student, there’s no better way to master SQL.   informit.com/aw forMereMortals.com

Table of Contents:
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