You are previewing SQL Queries for Mere Mortals®: A Hands-On Guide to Data Manipulation in SQL, Third Edition.
O'Reilly logo
SQL Queries for Mere Mortals®: A Hands-On Guide to Data Manipulation in SQL, Third Edition

Book Description

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

  1. About This eBook
  2. Title Page
  3. Copyright Page
  4. Praise for SQL Queries for Mere Mortals®, Third Edition
  5. Contents
  6. Foreword
  7. Preface
    1. Acknowledgments
  8. About the Authors
  9. Introduction
    1. Are You a Mere Mortal?
    2. About This Book
    3. What This Book Is Not
    4. How to Use This Book
    5. Reading the Diagrams Used in This Book
    6. Sample Databases Used in This Book
    7. “Follow the Yellow Brick Road”
  10. Part I: Relational Databases and SQL
    1. 1. What Is Relational?
      1. Types of Databases
      2. A Brief History of the Relational Model
      3. Anatomy of a Relational Database
      4. What’s in It for You?
      5. Summary
    2. 2. Ensuring Your Database Structure Is Sound
      1. Why Is This Chapter Here?
      2. Why Worry about Sound Structures?
      3. Fine-Tuning Fields
      4. Fine-Tuning Tables
      5. Establishing Solid Relationships
      6. Is That All?
      7. Summary
    3. 3. A Concise History of SQL
      1. The Origins of SQL
      2. Early Vendor Implementations
      3. “. . . And Then There Was a Standard”
      4. Evolution of the ANSI/ISO Standard
      5. Commercial Implementations
      6. What the Future Holds
      7. Why Should You Learn SQL?
      8. Which Version of SQL Does This Book Cover?
      9. Summary
  11. Part II: SQL Basics
    1. 4. Creating a Simple Query
      1. Introducing SELECT
      2. The SELECT Statement
      3. A Quick Aside: Data versus Information
      4. Translating Your Request into SQL
      5. Eliminating Duplicate Rows
      6. Sorting Information
      7. Saving Your Work
      8. Sample Statements
      9. Summary
      10. Problems for You to Solve
    2. 5. Getting More Than Simple Columns
      1. What Is an Expression?
      2. What Type of Data Are You Trying to Express?
      3. Changing Data Types: The CAST Function
      4. Specifying Explicit Values
      5. Types of Expressions
      6. Using Expressions in a SELECT Clause
      7. That “Nothing” Value: Null
      8. Sample Statements
      9. Summary
      10. Problems for You to Solve
    3. 6. Filtering Your Data
      1. Refining What You See Using WHERE
      2. Defining Search Conditions
      3. Using Multiple Conditions
      4. Nulls Revisited: A Cautionary Note
      5. Expressing Conditions in Different Ways
      6. Sample Statements
      7. Summary
      8. Problems for You to Solve
  12. Part III: Working with Multiple Tables
    1. 7. Thinking in Sets
      1. What Is a Set, Anyway?
      2. Operations on Sets
      3. Intersection
      4. Difference
      5. Union
      6. SQL Set Operations
      7. Summary
    2. 8. INNER JOINs
      1. What Is a JOIN?
      2. The INNER JOIN
      3. Uses for INNER JOINs
      4. Sample Statements
      5. Summary
      6. Problems for You to Solve
    3. 9. OUTER JOINs
      1. What Is an OUTER JOIN?
      2. The LEFT/RIGHT OUTER JOIN
      3. The FULL OUTER JOIN
      4. Uses for OUTER JOINs
      5. Sample Statements
      6. Summary
      7. Problems for You to Solve
    4. 10. UNIONs
      1. What Is a UNION?
      2. Writing Requests with UNION
      3. Uses for UNION
      4. Sample Statements
      5. Summary
      6. Problems for You to Solve
    5. 11. Subqueries
      1. What Is a Subquery?
      2. Subqueries as Column Expressions
      3. Subqueries as Filters
      4. Uses for Subqueries
      5. Sample Statements
      6. Summary
      7. Problems for You to Solve
  13. Part IV: Summarizing and Grouping Data
    1. 12. Simple Totals
      1. Aggregate Functions
      2. Using Aggregate Functions in Filters
      3. Sample Statements
      4. Summary
      5. Problems for You to Solve
    2. 13. Grouping Data
      1. Why Group Data?
      2. The GROUP BY Clause
      3. “Some Restrictions Apply”
      4. Uses for GROUP BY
      5. Sample Statements
      6. Summary
      7. Problems for You to Solve
    3. 14. Filtering Grouped Data
      1. A New Meaning of “Focus Groups”
      2. Where You Filter Makes a Difference
      3. Uses for HAVING
      4. Sample Statements
      5. Summary
      6. Problems for You to Solve
  14. Part V: Modifying Sets of Data
    1. 15. Updating Sets of Data
      1. What Is an UPDATE?
      2. The UPDATE Statement
      3. Uses for UPDATE
      4. Sample Statements
      5. Summary
      6. Problems for You to Solve
    2. 16. Inserting Sets of Data
      1. What Is an INSERT?
      2. The INSERT Statement
      3. Uses for INSERT
      4. Sample Statements
      5. Summary
      6. Problems for You to Solve
    3. 17. Deleting Sets of Data
      1. What Is a DELETE?
      2. The DELETE Statement
      3. Uses for DELETE
      4. Sample Statements
      5. Summary
      6. Problems for You to Solve
  15. Part VI: Introduction to Solving Tough Problems
    1. 18. “NOT” and “AND” Problems
      1. A Short Review of Sets
      2. Finding Out the “Not” Case
      3. Finding Multiple Matches in the Same Table
      4. Sample Statements
      5. Summary
      6. Problems for You to Solve
    2. 19. Condition Testing
      1. Conditional Expressions (CASE)
      2. Solving Problems with CASE
      3. Sample Statements
      4. Summary
      5. Problems for You to Solve
    3. 20. Using Unlinked Data and “Driver” Tables
      1. What Is Unlinked Data?
      2. Solving Problems with Unlinked Data
      3. Solving Problems Using “Driver” Tables
      4. Sample Statements
      5. Summary
      6. Problems for You to Solve
    4. In Closing
  16. Appendices
    1. A. SQL Standard Diagrams
    2. B. Schema for the Sample Databases
      1. Sales Orders Example Database
      2. Sales Orders Modify Database
      3. Entertainment Agency Example Database
      4. Entertainment Agency Modify Database
      5. School Scheduling Example Database
      6. School Scheduling Modify Database
      7. Bowling League Example Database
      8. Bowling League Modify Database
      9. Recipes Database
    3. C. Date and Time Types, Operations, and Functions
      1. IBM DB2
      2. IBM DB2
      3. Microsoft Office Access
      4. Microsoft SQL Server
      5. MySQL
      6. Oracle
    4. D. Suggested Reading
      1. Database Books
      2. Books on SQL
  17. Index