|Library of Congress Number:
Perfect bind - 9x7
||IT In-focus Series # 4
The professional Guide to SQL Programming
Retail Price $49.95
Note: This book is exclusively for professional SQL
programmers and it IS NOT appropriate for novices and
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
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
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.
* Learn the SQL best practices
used by successful professionals.
* Improve productivity and maintainability with SQL coding
* 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
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.
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
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:
Discrete interval sampling
About the Author:
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
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
Chapter 2 - Integer Generators in SQL
Enumerating Sets of Integers
Discrete Interval Sampling
Chapter 3 - Exotic Operators in SQL
Introduction to SQL exotic operators
Histograms in SQL
Chapter 4 - SQL Constraints
Function Based Constraints
Materialized View Constraints
Temporal Foreign Key Constraint
Chapter 5 - Trees in SQL
From Binary to N-ary Trees
Parent and Children Query
Converting Matrix to Path
Relocating Tree Branches
Exotic Labeling Schemas
Pre-order – Depth Encoding
Chapter 6 - Graphs in SQL
Hierarchical Weighted Total
Enumerating Sets and Lists
Step and Characteristic Function
SQL Competition: Interval Coalesce (+finding continuous
|(Disjoint) Interval set
covering a point
Transitive Closure for Undirected Graph
Hierarchical Weighted Total
asymmetric join condition
C. J. Date
Conditional Summation idiom
correlated scalar subquery
Cyclic Redundancy Check
discrete interval sampling
Discrete interval sampling
foreign key constraint
Greatest Common Divisor
Hierarchical Weighted Total
Lagrange Interpolating Polynomial
lexicographical order comparison
materialized path encoding
materialized paths concatenation
materialized view constraint
Minkovski question mark function
naïve nested sets
Nikos A. Lorentzos
relational division operator
ROWID pseudo column
start with clause
transitive closure graph
transitive closure query
unary increment operator
unaugmented node weights
unique dimensional constraint
UNIX directory structure
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
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
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
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.
Great book for improving SQL skills
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.