Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
  Phone
  800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

    Privacy Policy

  

 

ISBN
0-9776715-4-2
 
ISBN 13
978-0977671540
Library of Congress Number: 2006934082
264 pages
 Perfect bind - 9x7
PD 1007
Shelving: Database IT In-focus Series # 4

  SQL Design Patterns
The professional Guide to SQL Programming

Vadim Tropashko      

Retail Price $49.95 /  £37.95 

Key Features About the Author Table of Contents
Index Reader Comments Errata

Note: This book is exclusively for professional SQL programmers and it IS NOT appropriate for novices and non-professionals.

 Order now and save 30%.  Only $34.95


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.

 
Key Features

* Learn the SQL best practices used by successful professionals.

* Improve productivity and maintainability with SQL coding standards.

* Learn relational division, set joins, user-defined aggregates, pivot, and many other advanced patterns.

* Learn how SQL Design Patterns create common solutions.

* See proven best practices for SQL coding styles.

* Learn (literally!) countless ways to implement integer generators in SQL.

* Understand how to perform complex data manipulation with SQL advanced predicates.

* Leverage materialized views to implement ANSI SQL assertions.

* Explore hierarchical SQL queries.

SQL is the de-facto query language for all databases and SQL is the topic of thousands of books. However, never before has anyone taken the principle of “Design Patterns” and applied them to SQL programming. Unlike programming languages, SQL is non-procedural and declarative, and this book melds two wildly popular technologies into a cohesive whole.

On internet SQL forums it is not uncommon for people to ask the same question over and over again. Patterns introduce colorful names where developers otherwise have to refer to the answer by URL. “Look, that’s a singleton!” is a common reply in procedural programming community.

Historically, SQL is a very successful language, with more usage than any procedural programming language. This might be surprising to a newcomer who generally find SQL a little bit old fashioned compared to "modern" programming languages. It’s almost as old as C (which spawned at least 3 newer generation languages already) and it looks like COBOL, why isn't it obsolete yet? Let assure the reader that this appearance is misleading. Under the cover of sloppy and archaic syntax we find a high abstraction language.

SQL is many things to many people.  To the beginner, the sloppy and archaic declarative syntax is nothing more than a data query language.  But to the professional, SQL offers a robust abstraction level that can be used to solve a host of complex data manipulation problems.

Understanding SQL design patterns is critical to the success of every professional SQL developer.  For development best practices it's critical that SQL developers employ common design patterns with SQL so that they have a framework for common SQL solutions.

Predicates, joins, subqueries and aggregation are SQL basics. The advanced part of SQL learning curve demands a SQL programmer to develop the ability to recognize complex patterns. Whether you are working on a small project or a multi-million dollar system, it's critical that developers understand the common design patterns with SQL so that they have a framework for common SQL solutions. Beyond a certain point, the skill of piling up subqueries doesn’t pay off, and one has to study some rudimentary theory, which classifies known SQL solutions into common design patterns.

Programming books in general are poorly illustrated, or illustrations have little to relate to the book topics. "SQL Design Patterns" is an exception. Most of the material has accompanying figures that help visualizing the problem.

SQL Design Patterns Book Preface

SQL is a very successful language. Yet, is there a place for an advanced SQL book in the era of “Learn Technology X in 10 Minutes”? Indeed, the tech industry today emphasizes a shallow knowledge of a huge number of technical skills, rather than deep knowledge of a more specialized skill. That’s how workers have been getting jobs, by knowing a tiny bit of many skills employers might need. Someone who knows how to use SQL in an advanced way does not have a proportionate advantage in getting a job over someone who knows only the basics. If this second person, however, also knows J2EE, XSLT, Ajax, Flash, or any other flash in the pan to a similarly basic level, then he or she has a greater employment advantage.

The major flaw of this line of reason is equating the sophistication of SQL to these rather unsound technologies. This might be surprising to a newcomer who generally finds SQL a little bit old fashioned compared to the “modern” programming languages. It is almost as old as C, which spawned at least 3 newer generation languages already, and it looks like COBOL, so why isn’t it obsolete yet? Let me assure you that this appearance is misleading. Under the cover of sloppy and archaic syntax, a high abstraction language is found.

SQL programming is very unusual from the procedural perspective: there is no explicit flow control, no loops and no variables either to apply operations to or to store intermediate results into. SQL heavily leverages predicates instead, which elevates it to Logic Programming. Then, the grouping and aggregation syntax blends naturally into this already formidable logic foundation. Anybody who rediscovers that matrix or polynomial multiplication can be written in just three lines of code has a long lasting impression.

These ingredients that make SQL unique partially explain why advanced SQL programming does not revolve around syntax features, but demands a SQL programmer to develop an ability to recognize complex patterns. Yes, beyond a certain point a skill of piling up subqueries does not give much of a return and one has to study some rudimentary theory, which classifies known SQL solutions into patterns.

Patterns in procedural programming became popular a decade ago, originated with a landmark book by Gamma et. al. Each pattern has its name so that developers could quickly refer to it by just a name. “Oh, that’s a singleton!” instead of a lengthy description and often accompanied with a code snippet.

Patterns received a sour reception in a high abstraction language community. The prevailing perception was that patterns are a signature of low level programming. When a programmer sees patterns in her programs, it is a sign of trouble. The shape of a program should reflect only the problem it needs to solve. Any other regularity in the code indicates that abstractions are not powerful enough.

In reality, however, any language is quite limited in its abstraction power, declarative languages notwithstanding. Sooner or later we have to find workarounds for those limitations. This is how SQL patterns were born!

Patterns greatly improve our communication capabilities. On internet SQL forums it is not uncommon for people to ask the same question over and over again. Pattern names such as Interval coalesce or Relational division, for example, rarely surface in the discussion thread, giving way to numerous reply messages pointlessly competing to see who can find a query that does not look intimidating. Patterns allow succinct replies like this: “Lookup the Interval coalesce method in the … textbook”.

Establishing common pattern names is the first goal of this book. Most of the patterns have standard names: Skyline query, Pivot, or Nested Intervals, for example. Few do not; we have to work out a name, like the fancy sounding Discrete interval sampling, for example.

When presenting SQL patterns in this book I decided to dismiss the standard template form. Template is perfect for reference material, but is a nuisance for a textbook. More important than this stylistic comment, however, is the fact that fairly soon you might stumble into patterns that require little familiarity with undergraduate level math. Don’t be discouraged, however: as John Garmany suggested, many topics start making sense on second reading. In the Indicator Functions section, for example, you may want to skip the theory, first, rewind to the sample problem and SQL solutions, then, rollback to the theory.

List of SQL Design patterns:


 Counting
 Conditional summation
 Integer generator
 String/Collection decomposition
 List Aggregate
 Enumerating pairs
 Enumerating sets
 Interval coalesce
 Discrete interval sampling
 User-defined aggregate
 Pivot
 Symmetric difference
 Histogram
 Skyline query
 Relational division
 Outer union
 Complex constraint
 Nested intervals
 Transitive closure
 Hierarchical total
.

About the Author:



Vadim Tropashko

 

Vadim Tropashko graduated from Moscow Institute of Physics and Technology in 1984. Tropashko researched Petri Nets for five years following graduation. In the early 90s, his interests switched to OOP. Tropashko translated "The C++ Programming Language" by B.Stroustrup into Russian. He was a C++ instructor at University of Radio and Electronics in Belarus. In the mid 90s, Tropashko's interest changed from OOP to databases. Vadim Tropashko has now worked for Oracle since 1998.    

Table of Contents:
 
Chapter 1 - Counting in SQL

List of patterns
Introduction to SQL Counting
Counting Ordered Rows
Conditional Summation with CASE Operator
Indicator and Step Functions
A Case for the CASE Operator
Summarizing by more than one Relation
Interval Coalesce

Chapter 2 - Integer Generators in SQL

Integers Relation
Recursive With
Big Table
Table Function
Cube
Hierarchical Query
String Decomposition
Enumerating Pairs
Enumerating Sets of Integers
Discrete Interval Sampling

Chapter 3 - Exotic Operators in SQL

Introduction to SQL exotic operators
List Aggregate
Product
Factorial
Interpolation
Pivot
Symmetric Difference
Histograms in SQL
Equal-Width Histogram
Equal-Height Histogram
Logarithmic Buckets
Skyline Query
Relational Division
Outer Union

Chapter 4 - SQL Constraints

Function Based Constraints
Symmetric Functions
Materialized View Constraints
Disjoint Sets
Disjoint Intervals
Temporal Foreign Key Constraint
Cardinality Constraint
 

Chapter 5 - Trees in SQL

Materialized Path
Nested Sets
Interval Halving
From Binary to N-ary Trees
Matrix Encoding
Parent and Children Query
Nested Intervals
Descendants Query
Ancestor Criteria
Ancestors Query
Converting Matrix to Path
Inserting Nodes
Relocating Tree Branches
Ordering
Exotic Labeling Schemas
Dietz Encoding
Pre-order – Depth Encoding
Reversed Nesting
Ordered Partitions

Chapter 6 - Graphs in SQL

Schema Design
Tree Constraint
Transitive Closure
Recursive SQL
Connect By
Incremental Evaluation
Hierarchical Weighted Total
Generating Baskets
Comparing Hierarchies

Index Topics:

Conditional Summation
Integer Generators
Enumerating Sets and Lists
Matrix Multiplication
Matrix Pivot
Factorial
List Aggregate
Units
 
Interpolation
Length-Height-Width Constraint
Step and Characteristic Function
Minimal Cover
SQL Competition: Interval Coalesce (+finding continuous range)
 
(Disjoint) Interval set covering a point
Convex Closure
Transitive Closure
Transitive Closure for Undirected Graph
Hierarchical Total
Hierarchical Weighted Total
Comparing Trees
adjacency encoding
adjacency matrix
aggregate functions
aggregation
Ajax
analytic SQL
Ancestor Criteria
Ancestors Query
ANSI SQL
anti-join
associativity property
asymmetric join condition
audit trail
Bags
Baskets
BITAND
bitmapped indexes
Boolean
B-Tree
C. J. Date
Cartesian product
case operator
CASE operator
Chantziantoniou
Complex constraint
concurrency semantics
conditional summation
Conditional summation
Conditional Summation idiom
connect by
constraints
correlated scalar subquery
count(*)
Counting
CRC
Cyclic Redundancy Check
Damianos Chantziantoniou
DB2
declarative language
descendant query
Descendants Query
Dietz Encoding
discrete interval sampling
Discrete interval sampling
Disjoint Intervals
Disjoint Sets
Donald Kossmann
E.F.Codd
EAV
Ellipsis
Entity-Attribute-Value
enumerating
Enumerating pairs
Enumerating sets
Equal-height histogram
equivalence class
Equivalence Relation
Euclidean algorithm
factorial calculation
Farey fractions
Flash
foreign key constraint
frequency histogram
Function Argument
G. Dong
Gabe Romanescu
Gamma
GCD
graph
Greatest Common Divisor
GROUP BY
GUI
hash function
hash values
hierarchical query
Hierarchical total
Hierarchical Weighted Total
Histogram
histograms
Hugh Darwen
Incremental Evaluation
indicator function
instr
Integer generator
Integers relation
Interpolation
interval coalesce
Interval coalesce
Interval Halving
interval packing
Interval tree
J2EE
Joe Celko
Kenneth Ross
Konrad Stocker
L. Libkin
Lagrange Interpolating Polynomial
lexicographical order comparison
 
List Aggregate
logarithm function
logarithmic partitioning
loop iteration
Materialized Path
materialized path encoding
materialized paths concatenation
materialized view constraint
materialized views
matrix encoding
Matrix encoding
Matrix Encoding
matrix evaluation
Matrix multiplication
max
mediant
Michael Hammer
Mikito Harakiri
Minkovski question mark function
minus operator
multiplicative property
multivalued function
naïve nested sets
NATURAL JOIN
Nested intervals
Nested Intervals
nested sets
Nested Sets
Nikos A. Lorentzos
non-linear interpolation
NOT EXISTS
NOT IN
not NULL
NULL handling
NULL value
optimizer
Oracle
order by
ordering
OTN
outer join
Outer union
Outer Union
partition by
partitioning
patterns
Peter Buneman
Pipelined operators
pipelining
Pivot
POWER SQL
pre-order index
procedural programming
 
puzzles
R. Snodgrass
reachability
recursive SQL
Relation Equality
Relational Algebra
Relational Closure
Relational division
Relational Division
relational division operator
Reversed Nesting
Rod West
ROWID pseudo column
rownum
R-Tree
scalar subqueries
scalar values
Schema Design
select clause
semi-join
singleton
Skyline
Skyline query
SQL Server
start with clause
Stephan Börzsönyi
Stern-Brocot tree
Stern-Brokot tree
Steve Kaas
String Decomposition
String/Collection decomposition
Sunil Sarin
Symmetric difference
symmetric polynomials
Transitive closure
transitive closure graph
transitive closure query
Tree Branches
trees
trigger
Tropical arithmetic
unary increment operator
unaugmented node weights
unique dimensional constraint
universal quantifiers
UNIX directory structure
Unpivot
where clause
with operator
XSLT
Zero counts
 

Reviews:

by Laurent Schneider:

A mathematical approach to sql programming

The book starts by comparing sum(1) and count(*) and reveals the power of group by. The tree and graph theory requires some scientific background (or at least an attraction for numbers), and understanding matrix calculation will help.

The author invented many techniques, the most famous is probably the row generator CONNECT BY LEVEL<10. The wise approach to constraint is to use a on-commit refreshable materialized view and a constraint on the materialized view to check the view is empty rather than using a trigger. This technique however requires COMMIT (like a deferred constraint).

This book is a must have if you are doing some research on sql and it is perhaps a good book to learn how the language works if you are studying. However, it is not a sql reference book.

I particularly appreciated the constraint part of the book, especially how to use binoms to uniquely identify a box ! It reveals the "infinite" loop artifices, in both Oracle and non-Oracle databases. The connect by without prior infinite loop is finally explained!

 

by Kevin Mead

A Fresh Look and What we do

First let me point out, there are several problems with this book.

1) The author's english is a bit broken so in some places you will find yourself frustrated by having to read things twice or more to understand it.

2) The author has a great mathematics background, but again in a few places, this actually gets in the way as most of us do not have such a deep mathematics grounding and so won't understand what the math is trying to tell us. Knowing there is a sound mathematical set of principles behind things is nice but the details and symbols are often too deep for me.

3) For those of us who are actually ADVANCED developers, there may be little here in the way of new tips or tricks because you will already know and practice many of the techniques discussed.

INPSITE OF THESE DETRACTORS, I give the book five stars and suggest that anyone looking to go to the next level with their SQL skills should read it for a mind set, because it has the potential to actually change the way we work. Language issue aside, it clearly articulates the idea that it is possible to make a mental leap that will take you from average sql development to great sql development. I liken it to what my typing teacher in High School once told me: "when you stop thinking about the letters and start thinking about the words, your typing will improve dramatically with fewer errors and faster results". In the same way, this author shows us there is a mental leap to be made with sql. The number of lines of code does not matter. What matters is ideas. When we stop thinking about number of tables in the from clause or number of joins in the where clause, and instead start thinking about DESIGN PATTERNS, the quality of our sql code will improve dramtically just like our ability to type did when we made the simple mental leap from letters on a page to words in a sentence.

This is a refreshing new look at what we do. I have thought for some time what the author is saying, but I could not express it in words till I read this book.

Kevin

 

By Jason

Great book for improving SQL skills

I really liked this book. Great examples and explanations of complex ideas. It has definitely helped me to better understand Design Patterns. I plan to come back to this one.

 

Errata:

 

   

 Copyright © 1996 -2011 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks