The syntax of SQL is quite easy to learn. A few basic ideas and a handful of keywords allow you to tackle a
huge range of queries. However, many users often find themselves completely stumped when faced with a
particular problem. It isn’t really a great deal of help for someone to say “this is how I would do it.” What you
need is a variety of ways to get started on a tricky problem. Once you have made a start on a query, you need
to be able to check, amend, and refine your solution until you have what you need.
Two-Pronged Approach
Throughout the book I have approached different types of queries from two directions. The two approaches
have their roots in the formal relational algebra and calculus. In the body of the book I have kept the
descriptions non-mathematical, however, Appendix 2 provides an introduction to the formal notation for
those keen to understand the underlying theory. The first approach, which I’ve called the process approach ,
looks at how tables need to be manipulated in order to retrieve the subset of data required. You will find
explanations of the different types of operations that can be performed on tables; e.g., joins, intersections,
selections. Explanations are provided to help you decide which of these might be useful in particular
situations. Once you understand what operations are needed, translating them into SQL is relatively
straightforward.
The second approach is what I use when I just can’t figure out what operations will give me the required
results. This approach, which I’ve called the outcome approach , lets you describe what an expected row in
your result might be like — i.e., what conditions it must obey. By looking at the data, it is surprisingly easy to
develop a semi-formal description of what a “correct” retrieved row would be like (and by implication, how
you would recognize an “incorrect” row). Translating this semi-formal description into a working query is
straightforward.
I am always surprised at which approach my students take when confronting a new problem. Some will
instantly see the operations that are needed, and others will find the outcome approach more intuitive. The
choice of approach changes from query to query, from person to person, and (I suspect) from day to day.
Having more than one way to get started means you are less likely to get completely baffled by a new problem.

0 comments:

 
Top