Effective SQL: 61 Specific Ways to Write Better SQL, First Edition

Effective SQL: 61 Specific Ways to Write Better SQL, First Edition

by John L. Viescas, Douglas J. Steele, Ben G. Clothier
Released December 2016
Publisher(s): Addison-Wesley Professional
ISBN: 9780134579078

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

“Given the authors’ reputations, I expected to be impressed. I was blown away! . . . Most SQL books sit on my shelf. This one will live on my desk.”
–Roger Carlson, Microsoft Access MVP (2006-2015)

“Rather than stumble around reinventing wheels or catching glimpses of the proper approaches, do yourself a favor: Buy this book.”
—Dave Stokes, MySQL Community Manager, Oracle Corporation

Effective SQL brings together practical solutions and insights so you can solve complex problems with SQL and design databases that simplify data management in the future. It’s the only modern book that brings together advanced best practices and realistic example code for all of these versions of SQL: IBM DB2, Microsoft Access, Microsoft SQL Server, MySQL, Oracle Database, and PostgreSQL.

Drawing on their immense experience as world-class database consultants and instructors, the authors identify 61 proven approaches to writing better SQL. Wherever SQL versions vary, the authors illuminate the key nuances, so you can get the most out of whatever version you prefer. This full-color guide provides clear, practical explanations; expert tips; and plenty of usable code. Going far beyond mere syntax, it addresses issues ranging from optimizing database designs to managing hierarchies and metadata. If you already know SQL’s basics, this guide will help you become a world-class SQL problem-solver.

  • Craft better logical data models, and fix flawed models

  • Implement indexes that improve query performance

  • Handle external data from sources you don’t control

  • Extract and aggregate the information you need, as efficiently as possible

  • Write more flexible subqueries

  • Analyze and retrieve metadata using your database platform of choice

  • Use Cartesian Products and Tally Tables to solve problems you can’t address with conventional JOINs

  • Model hierarchical data: managing SQL’s tradeoffs and shortcomings

    •

    Table of contents

    1. About This E-Book
    2. Title Page
    3. Copyright Page
    4. Dedication Page
    5. Contents
    6. Foreword
    7. Acknowledgments
    8. About the Authors
    9. About the Technical Editors
    10. Introduction
      1. A Brief History of SQL
      2. Database Systems We Considered
      3. Sample Databases
      4. Where to Find the Samples on GitHub
      5. Summary of the Chapters
    11. 1. Data Model Design
      1. Item 1: Verify That All Tables Have a Primary Key
        1. Things to Remember
      2. Item 2: Eliminate Redundant Storage of Data Items
        1. Things to Remember
        2. References
      3. Item 3: Get Rid of Repeating Groups
        1. Things to Remember
      4. Item 4: Store Only One Property per Column
        1. Things to Remember
      5. Item 5: Understand Why Storing Calculated Data Is Usually a Bad Idea
        1. Things to Remember
      6. Item 6: Define Foreign Keys to Protect Referential Integrity
        1. Things to Remember
      7. Item 7: Be Sure Your Table Relationships Make Sense
        1. Things to Remember
      8. Item 8: When 3NF Is Not Enough, Normalize More
        1. Things to Remember
      9. Item 9: Use Denormalization for Information Warehouses
        1. Things to Remember
    12. 2. Programmability and Index Design
      1. Item 10: Factor in Nulls When Creating Indexes
        1. IBM DB2
        2. Microsoft Access
        3. Microsoft SQL Server
        4. MySQL
        5. Oracle
        6. PostgreSQL
        7. Things to Remember
      2. Item 11: Carefully Consider Creation of Indexes to Minimize Index and Data Scanning
        1. Things to Remember
      3. Item 12: Use Indexes for More than Just Filtering
        1. Things to Remember
      4. Item 13: Don’t Go Overboard with Triggers
        1. Things to Remember
      5. Item 14: Consider Using a Filtered Index to Include or Exclude a Subset of Data
        1. Things to Remember
      6. Item 15: Use Declarative Constraints Instead of Programming Checks
        1. Things to Remember
      7. Item 16: Know Which SQL Dialect Your Product Uses and Write Accordingly
        1. Ordering Result Sets
        2. Limiting Result Sets
        3. The BOOLEAN Data Type
        4. SQL Functions
        5. The UNIQUE Constraint
        6. Things to Remember
      8. Item 17: Know When to Use Calculated Results in Indexes
        1. Things to Remember
    13. 3. When You Can’t Change the Design
      1. Item 18: Use Views to Simplify What Cannot Be Changed
        1. Things to Remember
      2. Item 19: Use ETL to Turn Nonrelational Data into Information
        1. Things to Remember
      3. Item 20: Create Summary Tables and Maintain Them
        1. Things to Remember
      4. Item 21: Use UNION Statements to “Unpivot” Non-normalized Data
        1. Things to Remember
    14. 4. Filtering and Finding Data
      1. Item 22: Understand Relational Algebra and How It Is Implemented in SQL
        1. Select (Restrict)
        2. Project
        3. Join
        4. Intersect
        5. Cartesian Product
        6. Union
        7. Divide
        8. Difference
        9. Things to Remember
      2. Item 23: Find Non-matches or Missing Records
        1. Things to Remember
      3. Item 24: Know When to Use CASE to Solve a Problem
        1. Things to Remember
      4. Item 25: Know Techniques to Solve Multiple-Criteria Problems
        1. Things to Remember
      5. Item 26: Divide Your Data If You Need a Perfect Match
        1. Things to Remember
      6. Item 27: Know How to Correctly Filter a Range of Dates on a Column Containing Both Date and Time
        1. Things to Remember
      7. Item 28: Write Sargable Queries to Ensure That the Engine Will Use Indexes
        1. Things to Remember
      8. Item 29: Correctly Filter the “Right” Side of a “Left” Join
        1. Things to Remember
    15. 5. Aggregation
      1. Item 30: Understand How GROUP BY Works
        1. Things to Remember
      2. Item 31: Keep the GROUP BY Clause Small
        1. Things to Remember
      3. Item 32: Leverage GROUP BY/HAVING to Solve Complex Problems
        1. Things to Remember
      4. Item 33: Find Maximum or Minimum Values Without Using GROUP BY
        1. Things to Remember
      5. Item 34: Avoid Getting an Erroneous COUNT() When Using OUTER JOIN
        1. Things to Remember
      6. Item 35: Include Zero-Value Rows When Testing for HAVING COUNT(x) < Some Number
        1. Things to Remember
      7. Item 36: Use DISTINCT to Get Distinct Counts
        1. Things to Remember
      8. Item 37: Know How to Use Window Functions
        1. Things to Remember
      9. Item 38: Create Row Numbers and Rank a Row over Other Rows
        1. Things to Remember
      10. Item 39: Create a Moving Aggregate
        1. Things to Remember
    16. 6. Subqueries
      1. Item 40: Know Where You Can Use Subqueries
        1. Table Subquery
        2. Table Subquery with One Column
        3. Scalar Subquery
        4. Things to Remember
      2. Item 41: Know the Difference between Correlated and Non-correlated Subqueries
        1. Non-correlated Subqueries
        2. Correlated Subqueries
        3. Things to Remember
      3. Item 42: If Possible, Use Common Table Expressions Instead of Subqueries
        1. Using a CTE to Simplify a Query
        2. Using a Recursive CTE
        3. Things to Remember
      4. Item 43: Create More Efficient Queries Using Joins Rather than Subqueries
        1. Things to Remember
    17. 7. Getting and Analyzing Metadata
      1. Item 44: Learn to Use Your System’s Query Analyzer
        1. IBM DB2
        2. Microsoft Access
        3. Microsoft SQL Server
        4. MySQL
        5. Oracle
        6. PostgreSQL
        7. Things to Remember
      2. Item 45: Learn to Get Metadata about Your Database
        1. Things to Remember
      3. Item 46: Understand How the Execution Plan Works
        1. Things to Remember
    18. 8. Cartesian Products
      1. Item 47: Produce Combinations of Rows between Two Tables and Flag Rows in the Second That Indirectly Relate to the First
        1. Things to Remember
      2. Item 48: Understand How to Rank Rows by Equal Quantiles
        1. Things to Remember
      3. Item 49: Know How to Pair Rows in a Table with All Other Rows
        1. Things to Remember
      4. Item 50: Understand How to List Categories and the Count of First, Second, or Third Preferences
        1. Things to Remember
    19. 9. Tally Tables
      1. Item 51: Use a Tally Table to Generate Null Rows Based on a Parameter
        1. Things to Remember
      2. Item 52: Use a Tally Table and Window Functions for Sequencing
        1. Things to Remember
      3. Item 53: Generate Multiple Rows Based on Range Values in a Tally Table
        1. Things to Remember
      4. Item 54: Convert a Value in One Table Based on a Range of Values in a Tally Table
        1. Things to Remember
      5. Item 55: Use a Date Table to Simplify Date Calculation
        1. Things to Remember
      6. Item 56: Create an Appointment Calendar Table with All Dates Enumerated in a Range
        1. Things to Remember
      7. Item 57: Pivot Data Using a Tally Table
        1. Things to Remember
    20. 10. Modeling Hierarchical Data
      1. Item 58: Use an Adjacency List Model as the Starting Point
        1. Things to Remember
      2. Item 59: Use Nested Sets for Fast Querying Performance with Infrequent Updates
        1. Things to Remember
      3. Item 60: Use a Materialized Path for Simple Setup and Limited Searching
        1. Things to Remember
      4. Item 61: Use Ancestry Traversal Closure for Complex Searching
        1. Things to Remember
    21. Appendix. Date and Time Types, Operations, and Functions
      1. IBM DB2
        1. Data Types Supported
        2. Arithmetic Operations Supported
        3. Functions
      2. Microsoft Access
        1. Data Types Supported
        2. Arithmetic Operations Supported
        3. Functions
      3. Microsoft SQL Server
        1. Data Types Supported
        2. Arithmetic Operations Supported
        3. Functions
      4. MySQL
        1. Data Types Supported
        2. Arithmetic Operations Supported
        3. Functions
      5. Oracle
        1. Data Types Supported
        2. Arithmetic Operations Supported
        3. Functions
      6. PostgreSQL
        1. Data Types Supported
        2. Arithmetic Operations Supported
        3. Functions
    22. Index
    23. Code Snippets

    Product information

    • Title: Effective SQL: 61 Specific Ways to Write Better SQL, First Edition
    • Author(s): John L. Viescas, Douglas J. Steele, Ben G. Clothier
    • Release date: December 2016
    • Publisher(s): Addison-Wesley Professional
    • ISBN: 9780134579078