11.1 If you haven’t already done so, complete the exercises included inline in the body of the chapter.

11.2 Take another look at the various SQL expressions in the body of the chapter. From those SQL formulations alone (i.e., without looking at the problem statements), see if you can come up with a natural language interpretation of what the SQL expressions mean. Then compare your interpretations with the problem statements as given in the chapter.

11.3 Try applying the techniques described in this chapter to some genuine SQL problems from your own work environment. Note: This exercise is important. The techniques described in this chapter can seem a little daunting or hard to follow at first; in order to become familiar and comfortable with them, therefore, there’s really no substitute for “getting your hands dirty” and applying them for yourself.

11.4 Let relvar EMP have attributes ENO and HEIGHT and predicate Employee ENO has height HEIGHT. Here’s a relational calculus formulation of the quota query (see Exercise 7.14) “Get the employee number for the three shortest employees”:

     { EX.ENO } WHERE COUNT ( EY WHERE EY.HEIGHT < EX.HEIGHT ) < 3

And here’s a fairly direct transliteration of this expression into SQL:

     SELECT EX.ENO
     FROM   EMP AS EX
     WHERE ( SELECT COUNT ( * )
             FROM   EMP AS EY
             WHERE  EY.HEIGHT < EX.HEIGHT ) < 3

Here by contrast are three GROUP BY / HAVING expressions:

     SELECT EX.ENO
     FROM   EMP AS EX , EMP AS EY
     WHERE  EX.HEIGHT >= EY.HEIGHT
     GROUP  BY EX.ENO
     HAVING 3 <= COUNT ( * )

     SELECT EX.ENO
     FROM   EMP AS EX , EMP AS EY
     WHERE  EX.HEIGHT > EY.HEIGHT
     GROUP  BY EX.ENO
     HAVING 3 > COUNT ( * )

     SELECT EX.ENO
     FROM   EMP AS EX , EMP AS EY
     WHERE  EX.HEIGHT > EY.HEIGHT
     OR     EX.ENO = EY.ENO
     GROUP  BY EX.ENO
     HAVING 3 >= COUNT ( * )

Do you think these expressions are easier to understand than the relational calculus expression? More to the point, do they accurately represent the desired query? Also, what happens in each case if there aren’t exactly three shortest employees?

11.5 Some of the examples discussed in the present chapter—or others very much like them—were also discussed in earlier chapters, but the SQL formulations I gave in those chapters were often more “algebra like” than “calculus like.” Can you come up with any transformation laws that would allow the calculus formulations to be mapped into algebraic ones or vice versa?

11.6 In this chapter, I’ve discussed techniques for mapping relational calculus expressions into SQL equivalents. However, the mapping process was always carried out “by hand,” as it were. Do you think it could be mechanized?