book

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

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

by John L. Viescas, Douglas J. Steele, Ben G. Clothier

December 2016

Intermediate to advanced

352 pages

9h 37m

English

Addison-Wesley Professional

Read now

Unlock full access

About This E-Book
Title Page
Copyright Page
Dedication Page
Contents
Foreword
Acknowledgments
About the Authors
About the Technical Editors
Introduction
A Brief History of SQLDatabase Systems We ConsideredSample DatabasesWhere to Find the Samples on GitHubSummary of the Chapters

1. Data Model Design
Item 1: Verify That All Tables Have a Primary KeyThings to RememberItem 2: Eliminate Redundant Storage of Data ItemsThings to RememberReferencesItem 3: Get Rid of Repeating GroupsThings to RememberItem 4: Store Only One Property per ColumnThings to RememberItem 5: Understand Why Storing Calculated Data Is Usually a Bad IdeaThings to RememberItem 6: Define Foreign Keys to Protect Referential IntegrityThings to RememberItem 7: Be Sure Your Table Relationships Make SenseThings to RememberItem 8: When 3NF Is Not Enough, Normalize MoreThings to RememberItem 9: Use Denormalization for Information WarehousesThings to Remember
2. Programmability and Index Design
Item 10: Factor in Nulls When Creating IndexesIBM DB2Microsoft AccessMicrosoft SQL ServerMySQLOraclePostgreSQLThings to RememberItem 11: Carefully Consider Creation of Indexes to Minimize Index and Data ScanningThings to RememberItem 12: Use Indexes for More than Just FilteringThings to RememberItem 13: Don’t Go Overboard with TriggersThings to RememberItem 14: Consider Using a Filtered Index to Include or Exclude a Subset of DataThings to RememberItem 15: Use Declarative Constraints Instead of Programming ChecksThings to RememberItem 16: Know Which SQL Dialect Your Product Uses and Write AccordinglyOrdering Result SetsLimiting Result SetsThe BOOLEAN Data TypeSQL FunctionsThe UNIQUE ConstraintThings to RememberItem 17: Know When to Use Calculated Results in IndexesThings to Remember
3. When You Can’t Change the Design
Item 18: Use Views to Simplify What Cannot Be ChangedThings to RememberItem 19: Use ETL to Turn Nonrelational Data into InformationThings to RememberItem 20: Create Summary Tables and Maintain ThemThings to RememberItem 21: Use UNION Statements to “Unpivot” Non-normalized DataThings to Remember
4. Filtering and Finding Data
Item 22: Understand Relational Algebra and How It Is Implemented in SQLSelect (Restrict)ProjectJoinIntersectCartesian ProductUnionDivideDifferenceThings to RememberItem 23: Find Non-matches or Missing RecordsThings to RememberItem 24: Know When to Use CASE to Solve a ProblemThings to RememberItem 25: Know Techniques to Solve Multiple-Criteria ProblemsThings to RememberItem 26: Divide Your Data If You Need a Perfect MatchThings to RememberItem 27: Know How to Correctly Filter a Range of Dates on a Column Containing Both Date and TimeThings to RememberItem 28: Write Sargable Queries to Ensure That the Engine Will Use IndexesThings to RememberItem 29: Correctly Filter the “Right” Side of a “Left” JoinThings to Remember
5. Aggregation
Item 30: Understand How GROUP BY WorksThings to RememberItem 31: Keep the GROUP BY Clause SmallThings to RememberItem 32: Leverage GROUP BY/HAVING to Solve Complex ProblemsThings to RememberItem 33: Find Maximum or Minimum Values Without Using GROUP BYThings to RememberItem 34: Avoid Getting an Erroneous COUNT() When Using OUTER JOINThings to RememberItem 35: Include Zero-Value Rows When Testing for HAVING COUNT(x) < Some NumberThings to RememberItem 36: Use DISTINCT to Get Distinct CountsThings to RememberItem 37: Know How to Use Window FunctionsThings to RememberItem 38: Create Row Numbers and Rank a Row over Other RowsThings to RememberItem 39: Create a Moving AggregateThings to Remember
6. Subqueries
Item 40: Know Where You Can Use SubqueriesTable SubqueryTable Subquery with One ColumnScalar SubqueryThings to RememberItem 41: Know the Difference between Correlated and Non-correlated SubqueriesNon-correlated SubqueriesCorrelated SubqueriesThings to RememberItem 42: If Possible, Use Common Table Expressions Instead of SubqueriesUsing a CTE to Simplify a QueryUsing a Recursive CTEThings to RememberItem 43: Create More Efficient Queries Using Joins Rather than SubqueriesThings to Remember
7. Getting and Analyzing Metadata
Item 44: Learn to Use Your System’s Query AnalyzerIBM DB2Microsoft AccessMicrosoft SQL ServerMySQLOraclePostgreSQLThings to RememberItem 45: Learn to Get Metadata about Your DatabaseThings to RememberItem 46: Understand How the Execution Plan WorksThings to Remember
8. Cartesian Products
Item 47: Produce Combinations of Rows between Two Tables and Flag Rows in the Second That Indirectly Relate to the FirstThings to RememberItem 48: Understand How to Rank Rows by Equal QuantilesThings to RememberItem 49: Know How to Pair Rows in a Table with All Other RowsThings to RememberItem 50: Understand How to List Categories and the Count of First, Second, or Third PreferencesThings to Remember
9. Tally Tables
Item 51: Use a Tally Table to Generate Null Rows Based on a ParameterThings to RememberItem 52: Use a Tally Table and Window Functions for SequencingThings to RememberItem 53: Generate Multiple Rows Based on Range Values in a Tally TableThings to RememberItem 54: Convert a Value in One Table Based on a Range of Values in a Tally TableThings to RememberItem 55: Use a Date Table to Simplify Date CalculationThings to RememberItem 56: Create an Appointment Calendar Table with All Dates Enumerated in a RangeThings to RememberItem 57: Pivot Data Using a Tally TableThings to Remember
10. Modeling Hierarchical Data
Item 58: Use an Adjacency List Model as the Starting PointThings to RememberItem 59: Use Nested Sets for Fast Querying Performance with Infrequent UpdatesThings to RememberItem 60: Use a Materialized Path for Simple Setup and Limited SearchingThings to RememberItem 61: Use Ancestry Traversal Closure for Complex SearchingThings to Remember
Appendix. Date and Time Types, Operations, and Functions
IBM DB2Data Types SupportedArithmetic Operations SupportedFunctionsMicrosoft AccessData Types SupportedArithmetic Operations SupportedFunctionsMicrosoft SQL ServerData Types SupportedArithmetic Operations SupportedFunctionsMySQLData Types SupportedArithmetic Operations SupportedFunctionsOracleData Types SupportedArithmetic Operations SupportedFunctionsPostgreSQLData Types SupportedArithmetic Operations SupportedFunctions
Index
Code Snippets

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

9. Tally Tables

In Chapter 8, “Cartesian Products,” you read about Cartesian Products and how they can provide necessary data for SQL statements.

Another useful tool is the tally table, usually a table with a single column of sequential numbers, with values starting from 1 (or 0) to a maximum number appropriate for the situation. It can also be a single column of sequential dates that cover a range of interest, or something more complex to aid in “pivoting” a set of summaries. These allow us to solve problems that cannot be solved with Cartesian Products because Cartesian Products are dependent on actual values in the underlying tables, whereas tally tables cover all possibilities. In this chapter we show you examples of such problems and how ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780134579078Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

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

by John L. Viescas, Douglas J. Steele, Ben G. Clothier

9. Tally Tables

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.