SQL and Relational Theory, 2nd Edition

CHAPTER 11

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