11.1 First of all, you were asked several times in the body of the chapter whether it was necessary to worry about the possibility that the tables involved might include duplicate rows or nulls or both. But I categorically refuse—and so, I would like to suggest politely, should you—to waste any more time worrying about such matters. Avoid duplicates, avoid nulls, and then the transformations will all work just fine (and so will many other things, too).

That said, let me now give solutions to a couple of the more significant inline exercises:

(From the end of the section on Example 7.) Here’s an SQL formulation of the query “Get suppliers SX such that for all parts PX and PY, if PX.CITY ≠ PY.CITY, then SX doesn’t supply both of them.” (How does this formulation differ from the one shown in the body of the chapter?)

     SELECT SX.*
     FROM   S AS SX
     WHERE  NOT EXISTS
          ( SELECT *
            FROM   P AS PX
            WHERE  EXISTS
                 ( SELECT *
                   FROM   P AS PY
                   WHERE  PX.CITY <> PY.CITY
                   AND    EXISTS
                        ( SELECT *
                          FROM   SP AS SPX
                          WHERE  SPX.SNO = SX.SNO
                          AND    SPX.PNO = PX.PNO )
                   AND    EXISTS
                        ( SELECT *
                          FROM   SP AS SPX
                          WHERE  SPX.SNO = SX.SNO
                          AND    SPX.PNO = PY.PNO ) ) )

(From the end of the section on Example 12.) You were asked to give SQL formulations (a) using GROUP BY and HAVING, (b) not using GROUP BY and HAVING, for the following queries:

Here are GROUP BY and HAVING formulations:

SELECT SNO
FROM   SP
GROUP  BY SNO
HAVING COUNT ( * ) > 3

SELECT SNO
FROM   SP
GROUP  BY SNO
HAVING COUNT ( * ) < 4
UNION  CORRESPONDING
SELECT SNO
FROM   S
WHERE  SNO NOT IN
     ( SELECT SNO
       FROM   SP )

And here are non GROUP BY, non HAVING formulations:

SELECT SNO
FROM   S
WHERE  ( SELECT COUNT ( * )
         FROM   SP
         WHERE  SP.SNO = S.SNO ) > 3

SELECT SNO
FROM   S
WHERE  ( SELECT COUNT ( * )
         FROM   SP
         WHERE  SP.SNO = S.SNO ) < 4

You were also asked: What do you conclude from this exercise? Well, one thing I conclude is that we need to be very circumspect in our use of GROUP BY and HAVING. Observe in particular that the natural language queries were symmetric, which the GROUP BY / HAVING formulations aren’t. By contrast, the non GROUP BY, non HAVING formulations are symmetric.

11.2 No answer provided.

11.3 No answer provided (obviously).

11.4 First of all, the exercise asked if you think the GROUP BY / HAVING expressions are easier to understand than the relational calculus expression (or the direct SQL transliteration of that expression). Only you can answer this question, of course, but I’m pretty sure the answer for most people would have to be no. Second, the exercise also asked if those GROUP BY / HAVING expressions accurately represent the desired query. Answer: The third one does; by contrast, the first returns all employee numbers in EMP and the second returns no employee numbers at all. Third, the exercise also asked what happens in each case if there aren’t exactly three shortest employees. I’ll leave this one to you!

11.5 I’m certainly not going to give anything like a complete answer to this exercise, but I will at least observe that the following equivalences allow certain algebraic expressions to be converted into calculus ones and vice versa:

Other transformations were discussed in passing throughout the body of the book (from Chapter 6 on).

11.6 Well, I certainly don’t see why not.