SQL and Relational Theory, 2nd Edition

EXERCISES

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 ...