12.1 According to the BNF grammar given in the body of the chapter, which of the following are legal as “stand alone” expressions (i.e., expressions not nested inside other expressions) and which not, syntactically speaking? (A and B are table names, and you can assume the tables they denote satisfy the requirements for the operator in question in each case.)

A NATURAL JOIN B

A INTERSECT B

SELECT * FROM A NATURAL JOIN B

SELECT * FROM A INTERSECT B

SELECT * FROM ( A NATURAL JOIN B )

SELECT * FROM ( A INTERSECT B )

SELECT * FROM ( SELECT * FROM A INTERSECT SELECT * FROM B )

SELECT * FROM ( A NATURAL JOIN B ) AS C

SELECT * FROM ( A INTERSECT B ) AS C

TABLE A NATURAL JOIN TABLE B

TABLE A INTERSECT TABLE B

SELECT * FROM A INTERSECT SELECT * FROM B

( SELECT * FROM A ) INTERSECT ( SELECT * FROM B )

( SELECT * FROM A ) AS AA INTERSECT ( SELECT * FROM B ) AS BB

What do you conclude from this exercise? Perhaps I should remind you that, relationally speaking, intersection is a special case of natural join.

12.2 Take another look at the expressions in Exercise 12.1. In which of those expressions would it be syntactically legal to replace A or B or both by “table literals” (i.e., appropriate VALUES invocations)?

12.3 Let X and Y both be of the same character string type and be subject to the same collation; let PAD SPACE apply to that collation (not recommended, of course); and let X and Y have the values ’42’ and ’42 ’, respectively (note the trailing space in the second of these). Then we know from Chapter 2 that although X and Y are clearly distinct, the expression X = Y gives TRUE. But what about the expression X LIKE Y?

12.4 Given our usual sample values, what do the following expressions return?

SELECT DISTINCT STATUS
FROM   S
WHERE  STATUS BETWEEN 10 AND 30

SELECT DISTINCT CITY
FROM   S
WHERE  CITY LIKE 'L%'

SELECT DISTINCT CITY
FROM   S
WHERE  CITY BETWEEN 'Paris' AND 'Athens'

12.5 The following is intended to be an SQL expression of type BOOLEAN. Is it legal?

( SELECT CITY FROM S WHERE  STATUS < 20 )
  =
( SELECT CITY FROM P WHERE  WEIGHT = 14.0 )

12.6 In the body of the chapter I recommended circumspection in the use of asterisk notation in the SELECT clause. For brevity, however, I didn’t always follow my own advice in this respect in earlier chapters. Take a look through those chapters and see if you think any of my uses of the asterisk notation were unsafe.

12.7 Consider any SQL product available to you. Does that product support (a) the UNIQUE operator, (b) explicit tables, (c) lateral subqueries, (d) “possibly nondeterministic” expressions?

12.8 With regard to possibly nondeterministic expressions, recall that SQL prohibits the use of such expressions in integrity constraints. Take another look at the examples in Chapter 8 and/or the answers to the exercises from that chapter in Appendix F. Do any of those examples or answers involve any possibly nondeterministic expressions? If so, what can be done about it?

12.9 Throughout this book I’ve taken the term SQL to refer to the official standard version of that language specifically (though my treatment of the standard has deliberately been a very long way from exhaustive). But every product on the market departs from the standard in various ways, either by omitting some standard features or by introducing proprietary features of its own or (almost certainly in practice) both. Again, consider any SQL product available to you. Identify as many departures from the standard in that product as you can.