Here first are answers to certain exercises that were stated inline in the body of the chapter. In one, we were given relvars as follows—

S { SNO }SP { SNO , PNO }`/* suppliers */`

PJ { PNO , JNO }`/* supplier supplies part */`

J { JNO }`/* part is used in project */`

`/* projects */`

—and we were asked for a SQL formulation of the query “Get all (*sno,jno*) pairs such that *sno* appears in S, *jno* appears in J, and supplier *sno* supplies all parts used in project *jno*.” A possible formulation is as follows:

SELECT SX.SNO , JX.JNO FROM S AS SX , J AS JX WHERE NOT EXISTS ( SELECT * FROM P AS PX WHERE EXISTS ( SELECT * FROM PJ AS PJX WHERE PJX.PNO = PX.PNO AND PJX.JNO = JX.JNO ) AND NOT EXISTS ( SELECT * FROM SP AS SPX WHERE SPX.PNO = PX.PNO AND SPX.SNO = SX.SNO ) )

*Note:* For a detailed discussion of how to tackle complicated queries like this one in SQL, see Chapter 11.

Another inline exercise asked what happens if (a) *r1* and *r2* are relations with no attribute names in common, (b) *r2* is empty, (c) we form the product *r1* TIMES *r2*, and finally (d) we divide that product by *r2*. *Answer:* It should be clear that the product is empty, and hence the final result is empty too (it has the same heading as *r1*, but of course it isn’t equal to *r1*, in general). Do note, however, that dividing by an empty relation isn’t an error (it’s not like dividing by zero in arithmetic).

Another inline exercise asked why the following **Tutorial D** and SQL expressions weren’t quite equivalent:

S WHERE SUM ( !!SP , ...

Start Free Trial

No credit card required