SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL (3rd Edition)

The #1 Easy, Common-Sense Guide to SQL Queries--Updated for Today's Databases, Standards, and Challenges "SQL Queries for Mere Mortals""(R)" 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.(http://informit.com/awforMereMortals.com) informit.com/awforMereMortals.com About This eBook......Page 3 Title Page......Page 4 Copyright Page......Page 5 Praise for SQL Queries for Mere Mortals®, Third Edition......Page 7 Contents......Page 10 Foreword......Page 22 Acknowledgments......Page 23 About the Authors......Page 25 Are You a Mere Mortal?......Page 27 About This Book......Page 28 What This Book Is Not......Page 29 Reading the Diagrams Used in This Book......Page 30 Sample Databases Used in This Book......Page 34 “Follow the Yellow Brick Road”......Page 36 Part I: Relational Databases and SQL......Page 38 Types of Databases......Page 39 A Brief History of the Relational Model......Page 40 Anatomy of a Relational Database......Page 43 What’s in It for You?......Page 51 Summary......Page 53 Why Is This Chapter Here?......Page 55 Fine-Tuning Fields......Page 56 Fine-Tuning Tables......Page 66 Establishing Solid Relationships......Page 78 Summary......Page 86 The Origins of SQL......Page 88 Early Vendor Implementations......Page 90 “. . . And Then There Was a Standard”......Page 91 Evolution of the ANSI/ISO Standard......Page 92 Commercial Implementations......Page 99 Why Should You Learn SQL?......Page 100 Summary......Page 101 Part II: SQL Basics......Page 103 Introducing SELECT......Page 104 The SELECT Statement......Page 105 A Quick Aside: Data versus Information......Page 107 Translating Your Request into SQL......Page 109 Eliminating Duplicate Rows......Page 116 Sorting Information......Page 118 Sample Statements......Page 124 Summary......Page 134 Problems for You to Solve......Page 135 What Is an Expression?......Page 137 What Type of Data Are You Trying to Express?......Page 138 Changing Data Types: The CAST Function......Page 141 Specifying Explicit Values......Page 143 Types of Expressions......Page 149 Using Expressions in a SELECT Clause......Page 160 That “Nothing” Value: Null......Page 168 Sample Statements......Page 171 Summary......Page 179 Problems for You to Solve......Page 180 Refining What You See Using WHERE......Page 183 Defining Search Conditions......Page 188 Using Multiple Conditions......Page 210 Nulls Revisited: A Cautionary Note......Page 225 Expressing Conditions in Different Ways......Page 229 Sample Statements......Page 230 Summary......Page 239 Problems for You to Solve......Page 240 Part III: Working with Multiple Tables......Page 243 What Is a Set, Anyway?......Page 244 Operations on Sets......Page 245 Intersection......Page 246 Difference......Page 252 Union......Page 259 SQL Set Operations......Page 263 Summary......Page 272 What Is a JOIN?......Page 274 The INNER JOIN......Page 275 Uses for INNER JOINs......Page 292 Sample Statements......Page 293 Summary......Page 314 Problems for You to Solve......Page 315 What Is an OUTER JOIN?......Page 318 The LEFT/RIGHT OUTER JOIN......Page 320 The FULL OUTER JOIN......Page 338 Uses for OUTER JOINs......Page 343 Sample Statements......Page 344 Summary......Page 358 Problems for You to Solve......Page 359 What Is a UNION?......Page 362 Writing Requests with UNION......Page 365 Uses for UNION......Page 375 Sample Statements......Page 376 Problems for You to Solve......Page 386 11. Subqueries......Page 389 What Is a Subquery?......Page 390 Subqueries as Column Expressions......Page 391 Subqueries as Filters......Page 397 Uses for Subqueries......Page 409 Sample Statements......Page 411 Summary......Page 423 Problems for You to Solve......Page 424 Part IV: Summarizing and Grouping Data......Page 427 Aggregate Functions......Page 428 Using Aggregate Functions in Filters......Page 441 Sample Statements......Page 443 Problems for You to Solve......Page 451 Why Group Data?......Page 454 The GROUP BY Clause......Page 457 “Some Restrictions Apply”......Page 467 Uses for GROUP BY......Page 470 Sample Statements......Page 471 Summary......Page 480 Problems for You to Solve......Page 481 A New Meaning of “Focus Groups”......Page 484 Where You Filter Makes a Difference......Page 489 Uses for HAVING......Page 496 Sample Statements......Page 497 Problems for You to Solve......Page 505 Part V: Modifying Sets of Data......Page 509 What Is an UPDATE?......Page 510 The UPDATE Statement......Page 511 Uses for UPDATE......Page 524 Sample Statements......Page 525 Summary......Page 541 Problems for You to Solve......Page 542 What Is an INSERT?......Page 545 The INSERT Statement......Page 547 Uses for INSERT......Page 558 Sample Statements......Page 559 Summary......Page 568 Problems for You to Solve......Page 569 What Is a DELETE?......Page 572 The DELETE Statement......Page 573 Uses for DELETE......Page 579 Sample Statements......Page 580 Problems for You to Solve......Page 588 Part VI: Introduction to Solving Tough Problems......Page 591 A Short Review of Sets......Page 592 Finding Out the “Not” Case......Page 596 Finding Multiple Matches in the Same Table......Page 604 Sample Statements......Page 613 Problems for You to Solve......Page 629 Conditional Expressions (CASE)......Page 634 Solving Problems with CASE......Page 639 Sample Statements......Page 647 Summary......Page 659 Problems for You to Solve......Page 660 What Is Unlinked Data?......Page 662 Solving Problems with Unlinked Data......Page 666 Solving Problems Using “Driver” Tables......Page 669 Sample Statements......Page 676 Summary......Page 691 Problems for You to Solve......Page 692 In Closing......Page 696 Appendices......Page 697 A. SQL Standard Diagrams......Page 698 Sales Orders Example Database......Page 709 Sales Orders Modify Database......Page 710 Entertainment Agency Example Database......Page 711 Entertainment Agency Modify Database......Page 712 School Scheduling Example Database......Page 713 School Scheduling Modify Database......Page 714 Bowling League Example Database......Page 715 Bowling League Modify Database......Page 716 Recipes Database......Page 717 IBM DB2......Page 719 Microsoft Office Access......Page 721 Microsoft SQL Server......Page 723 MySQL......Page 726 Oracle......Page 729 Books on SQL......Page 732 Index......Page 733 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 Annotation The #1 Easy, Common-Sense Guide to SQL Queries--Updated for Today's Databases, Standards, and ChallengesSQL 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 morePractice 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/awforMereMortals.com SQL Queries for Mere Mortals is the easiest, most practical guide to writing modern SQL queries. Michael J. Hernandez and John L. Viescas demystify all facets of query writing with SQL. 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. - Publisher. About the authors Introduction Part one : Relational databases and SQL. What is relational? Ensuring your database structure is sound A concise history of SQL Part two : SQL basics. Creating a simple query Getting more than simple columns Filtering your data Part three : Working with multiple tables. Thinking in sets Inner joins Outer joins Unions Subqueries Part four : Summarizing and grouping data. Simple totals Grouping data Filtering grouped data Part five : Modifying sets of data. Updating sets of data Inserting sets of data Deleting sets of data Part six : Introduction to solving tough problems. "NOT" and "AND" problems Condition testing Using unlinked data and "driver" tables In closing Appendices. SQL standard diagrams Schema for the sample databases Date and time types, operations, and functions Suggested reading John L. Viescas, Michael J. Hernandez. Includes Bibliographical References And Index.