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:
Get supplier numbers for suppliers who supply N different parts for some N > 3.
Get supplier numbers for suppliers who supply N different parts for some N < 4.
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.