SQL Design Patterns: The Expert Guide to SQL Programming

by Vadim Tropashko

Overview

This indispensable SQL reference book is the first-of- its-kind to leverage the benefits of design patterns to relational database SQL queries. Leveraging on the success of programming design patterns books, SQL guru Vadim Tropashko categorizes and describes all common SQL structures and design patterns. This is an important book for programmers and managers alike. Because SQL is a declarative language there are many ways to write any SQL query and convoluted and clumsy SQL syntax has become a maintenance nightmare. Professional database programmers must understand the correct way to write SQL for complicated database queries, and managers must institute formal SQL coding standards to improve productivity and maintainability. As relational databases structures become more complex, so do their SQL queries. Complex SQL becomes difficult to understand and maintain, especially with novices who pile-on nested subqueries, and all SQL programmer must learn rudimentary SQL theory, which classifies known SQL solutions into common patterns. SQL design patterns greatly improve the quality and productivity of systems development projects because SQL design patterns form a "best practices" foundation for all relational database queries. The SQL programmers benefit from using SQL design patterns since all query structures are standardized with common approaches, and management benefits by having standardized and maintainable SQL syntax. This book is both pragmatic and academic, a worthy academic book that emphasizes theoretical foundation for almost every type of SQL query problem. Most of the material has accompanying figures that help visualizing the problem. This is not a book for casual programmers or dilettantes. It is specifically created for professional SQL developers who need to solve complex problems using common SQL idioms.

Product Details

ISBN-13:	9780977671540
Publisher:	Rampant Techpress
Publication date:	04/10/2014
Series:	IT In-Focus , #4
Pages:	256
Product dimensions:	7.08(w) x 8.84(h) x 0.80(d)

About the Author

Vadim Tropashko is the translator for The C++ Programming Language into Russian. He is a former C++ instructor at University of Radio and Electronics in Belarus and currently works for Oracle.

Table of Contents

Preface     1
List of patterns     3
Introduction to SQL Counting     5
Counting Ordered Rows     5
Conditional Summation with CASE Operator     12
Indicator and Step Functions     18
A Case for the CASE Operator     24
Summarizing by more than one Relation     31
Interval Coalesce     34
Summary     41
Exercises     42
Integer Generators     47
Integers Relation     47
Recursive With     48
Big Table     49
Table Function     51
Cube     54
Hierarchical Query     55
String Decomposition     57
Enumerating Pairs     62
Enumerating Sets of Integers     67
Discrete Interval Sampling     69
Summary     72
Exercises     72
Introduction to SQL exotic operators     76
List Aggregate     76
Product     81
Factorial     81
Interpolation     83
Pivot     89
Symmetric Difference     94
Histograms in SQL     99
Equal-Width Histogram     100
Equal-Height Histogram     105
Logarithmic Buckets     106
Skyline Query     108
Relational Division     111
Outer Union     120
Summary     123
Exercises     124
Introduction to Constraints     127
Function Based Constraints     128
Symmetric Functions     130
Materialized View Constraints     134
Disjoint Sets     140
Disjoint Intervals     142
Temporal Foreign Key Constraint     145
Cardinality Constraint     147
Summary     148
Exercises     148
Introduction to Trees     150
Materialized Path     151
Nested Sets     155
Interval Halving     158
From Binary to N-ary Trees     161
Matrix Encoding     163
Parent and Children Query     165
Nested Intervals     171
Descendants Query     173
Ancestor Criteria     174
Ancestors Query     177
Converting Matrix to Path     180
Inserting Nodes     180
Relocating Tree Branches     181
Ordering     183
Exotic Labeling Schemas     185
Dietz Encoding     186
Pre-order - Depth Encoding     189
Reversed Nesting     191
Ordered Partitions     193
Case Study: Flomegrown C Function Call Profiler     195
Summary     201
Exercises     201
Introduction to Graphs     204
Schema Design     204
Tree Constraint     205
Transitive Closure     209
Recursive SQL     214
Connect By     221
Incremental Evaluation     222
Hierarchical Weighted Total     232
Generating Baskets     239
Comparing Hierarchies     243
Summary     249
Exercises     250
About Vadim Tropashko     255