O'Reilly logo

SQL and Relational Theory, 2nd Edition by C.J. Date

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

EXERCISES

7.1 For each of the following Tutorial D expressions on the suppliers-and-parts database, give both (a) an SQL analog and (b) an informal interpretation of the expression (i.e., a corresponding predicate) in natural language. Also show the result of evaluating the expressions, given our usual sample values for relvars S, P, and SP.

  1. S MATCHING ( SP WHERE PNO = ‘P2’ )

  2. S NOT MATCHING ( SP WHERE PNO = ‘P2’ )

  3. P WHERE ( !!SP ) { SNO } = S { SNO }

  4. P WHERE SUM ( !!SP , QTY ) < 500

  5. P WHERE TUPLE { CITY CITY } ∈ S { CITY }

  6. EXTEND S : { TAG := ‘Supplier’ }

  7. EXTEND ( S MATCHING ( SP WHERE PNO = 'P2' ) ) :
                                { TRIPLE_STATUS := 3 * STATUS }
  8. EXTEND ( P JOIN SP ) : { SHIPWT := WEIGHT * QTY }

  9. EXTEND P : { GMWT := WEIGHT * 454 , OZWT := WEIGHT * 16 }

  10. EXTEND P : { SCT := COUNT ( !!SP ) }

  11. EXTEND S :
        { NP := COUNT ( ( SP RENAME { SNO AS X } ) WHERE X = SNO ) }
  12. SUMMARIZE S BY { CITY } : { SUM_STATUS := SUM ( STATUS ) }

  13. SUMMARIZE ( S WHERE CITY = 'London' ) PER ( TABLE_DEE ) :
                                                  { N := COUNT ( SNO ) }

    Note: Tutorial D allows the PER clause to be omitted, in which case PER (TABLE_DEE) is assumed by default. The foregoing SUMMARIZE could therefore be simplified slightly, thus:

    SUMMARIZE ( S WHERE CITY = 'London' ) : { N := COUNT ( SNO ) }

  14. EXTEND SP WHERE SNO = ‘S1’ : { SNO := ‘S7’ , QTY = 0.5 * QTY }

7.2 In what circumstances (if any) are r1 MATCHING r2 and r2 MATCHING r1 equivalent?

7.3 Show that RENAME isn’t primitive.

7.4 Give an expression involving EXTEND instead of SUMMARIZE that’s logically equivalent to the following: ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required