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