This book introduces a relatively new approach to mastering one's Oracle SQL skills. This book will teach you how to leverage your existing Oracle SQL knowledge as well as how you can benefit from a variety of SQL tricks and techniques we present thereafter. This is a text book rather than a reference, and it aims to teach you how to become a better SQL specialist. Even though the recommendations found in this book may be applied to a variety of SQL flavors, Oracle SQL is the main subject of this book. Our goal was not to impress you with clever tricks and sophisticated techniques, but rather give you a roadmap to excellence in writing Oracle SQL queries. No doubt, this book presents tricks and classy approaches, which still serve the main goal - to let you master your Oracle SQL skills.

Oracle SQL Tricks and Workarounds

By Zahar Hilkevich

AuthorHouse

Copyright © 2011 Zahar Hilkevich
All right reserved.
ISBN: 978-1-4634-2137-3

Contents

Chapter 1: Terms and Definitions..........................................1Chapter 2: First approach to the first problem............................13Chapter 3: Method Substitution............................................23Chapter 4: Introducing Problem/Technique Matrix (PTM).....................50Chapter 5: Extending the PTM..............................................77Chapter 6: Problem Patterns...............................................98Chapter 7: Benefit with the vendor........................................115Chapter 8: Handicap Challenge.............................................154Chapter 9: Solution patterns..............................................172Chapter 10: Substitution Drill............................................189Chapter 11: Writing DELETE and UPDATE statements..........................210Chapter 12: Mastering your SQL skills.....................................230

Chapter One

Terms and Definitions.

This book introduces a relatively new approach to mastering one's Oracle SQL skills. This book will teach you how to leverage your existing Oracle SQL knowledge as well as how you can benefit from a variety of SQL tricks and techniques we present thereafter. This is a text book rather than a reference, and it aims to teach you how to become a better SQL specialist. Even though the recommendations found in this book may be applied to a variety of SQL flavors, Oracle SQL is the main subject of this book. Our goal was not to impress you with clever tricks and sophisticated techniques, but rather give you a roadmap to excellence in writing Oracle SQL queries. No doubt, this book presents tricks and classy approaches, which still serve the main goal – to let you master your Oracle SQL skills.

Traditionally, when someone explains a trick or technique, a specific category of problems is targeted; it usually demonstrates how the trick or technique can solve the problem and the goal is considered accomplished. When we try solving a set of problems we often operate with the following concepts: a problem, a solution, and a method (employed by solutions). A method can represent a trick or technique, which we will define in a few paragraphs below. Usually, we are satisfied when a problem gets solved one way or another and there is little need to introduce a new concept within the domain we described. It is easy to understand that the value of a trick or technique in any technology can be measured by the number of different problems the method helps to solve. Throughout their career, a software developer acquires a number of such techniques that signifies his or her professional experience. Our idea is to show a different angle in the way we can acquire professional skills. Instead of only focusing on the number of different techniques we know, we emphasize a number of different problems or types of problems we are familiar with, and also a number of different ways in which we can solve all those problems. In other words, we encourage researching different types of problems and finding numerous solutions for each of them.

One of the main goals of this book is to explore and reveal the potential of many well known and lesser known techniques. Any technique is better understood if it can be widely applied. This book will demonstrate numerous applications of traditional SQL features in situations where those features were not expected to be applicable.

During the work on this book we faced a small challenge with respect to how to refer to a set of alternative solutions to a specific problem. This is how we came across the term WORKAROUND which we defined as follows:

We call the family of distinct solutions for a given problem the workarounds for that problem.

Since the terminology we use in this book may differ from what you have used before we will take a moment to define a few more terms.

We define an SQL technique as a general problem solving strategy that can usually be applied to a fairly broad set of problems.

An SQL trick is a method that's more specific than a technique – it may not apply to as many types of problems but it is usually more clever and obscure than a technique, and can sometimes be used to solve more stubborn problems.

Classifying solutions as tricks vs. techniques is a subjective process (the line can be blurry), but for us this distinction has been proven useful.

Roadmap of the book

The first chapter makes is an introduction into the methodology of producing multiple solutions for a given SQL problem. Generally speaking, any set of numerous solutions begins with a first solution. Some useful tactics on finding the very first solution are presented in Chapter 2. When you found your very first solution, in reality, you already "booked" a whole set of workarounds that can be derived with a help of Method Substitution, described in Chapter 3. Each chapter will present more and more SQL nuances that will help you derive more and more workarounds. With the growing number of found solutions, it becomes challenging to keep your knowledge well organized; Chapter 4 introduces a Problem Technique Matrix (PTM) – a neat model to manage your problems, techniques, and actual workarounds. Chapter 5 extends the PTM concept and explains how it can be best used. Simple statistical analysis of the PTM content leads us to a concept of Problem Patterns that provides a great practical payoff; Chapter 6 describes Problem Patterns in details. Every new Oracle release comes with a handful of very useful new features which equates to more new techniques one should try for finding new solutions – see Chapter 7 for more details on that. New features often offer new and simpler approaches to variety of problems. When developers get used to new techniques, they often forget that in previous versions they survived without them. Chapter 8 presents several handicap challenges, including the one we have just pointed to – "How did we do this before"? When developers start working on a new query, they are supposed to know all related business entities, relationships, and other rules. This is not the case when they have to optimize somebody else's request. Chapter 9 demonstrates possible approaches for such circumstances. Nothing can speed up one's professional progress better than extensive practice. Chapter 10 offers a comprehensive drill based on Method Substitution and other techniques reviewed earlier in the book. Data Manipulation commands are not limited to SELECT statement only. Chapter 11 shows how to apply the knowledge accumulated from Chapters 1 through 10 to DELETE and UPDATE commands. The last chapter of the book suggests further ways of improving SQL practical skills.

Why bother looking for workarounds?

SQL is a declarative language (unlike procedural languages, such as C, Java, Visual Basic, etc) hence it allows multiple equivalent ways to write database requests. Sometimes, two logically identical SQL statements look completely different and only SQL experts can recognize a common pattern. We will not be discussing correct and incorrect ways of writing SQL statements, but rather teaching you how to find different ways to write such statements.

Our goal is to enable our readers to become SQL gurus quickly, by teaching specific sample...