SQL Cookbook

Appendix B. Rozenshtein Revisited

This appendix is a tribute to David Rozenshtein. As I mentioned in the introduction, I feel his book The Essence of SQL is (even today) the best book ever written on SQL. Although only 119 pages long, the book covers what I consider to be crucial topics for any SQL programmer. In particular, David shows how to think through a problem and arrive at an answer. The solutions provided by Rozenshtein are very set oriented. Even if the size of your tables do not permit you to use his solutions in a practical environment, his approach is excellent as it forces you to stop searching for a procedural solution to a problem and start thinking in sets.

The Essence of SQL was published long before window functions and MODEL clauses. In this appendix I provide alternative solutions to some of the questions in Rozenshtein’s book using some of the newer functions available in standard SQL. (Whether these new solutions are “better” than Rozenshtein’s depends on the circumstances.) At the end of each discussion, I present a solution based on the original solution from Rozenshtein’s book. For the examples in which I present a variation of a problem found in Rozenshtein’s text, I will also present a variation of a solution (a solution that may not necessarily exist in Rozenshtein’s book, but that uses a similar technique).

B.1. Rozenshtein’s Example Tables

The following tables are based on Rozenshtein’s book and will be used in this chapter:

 /* table of students */ create ...