Solution for Exercise 5.2 in Ramakrishnan and Gehrke (3rd ed.), by
Niels Hvidberg Kjeldsen, September 2004.

-----------------

1.      SELECT  DISTINCT P.pname
        FROM    Parts P, Catalog C
        WHERE   P.pid = C.pid

2.      SELECT  S.sname
        FROM    Suppliers S
        WHERE   NOT EXISTS ( (  SELECT  P.pid
                                FROM    Parts P )
                                EXCEPT
                             (  SELECT  C.pid
                                FROM    Catalog C
                                WHERE   C.sid = S.sid ) )

3.      SELECT  S.sname
        FROM    Suppliers S
        WHERE   NOT EXISTS ( (  SELECT  P.pid
                                FROM    Parts P
                                WHERE   P.color = 'Red' )
                                EXCEPT
                             (  SELECT  C.pid
                                FROM    Parts P2, Catalog C
                                WHERE   C.sid = S.sid AND C.pid = P.pid AND P.color = 'Red' ) )

4.      SELECT  P.pname
        FROM    Parts P, Catalog C, Suppliers S
        WHERE   P.pid = C.pid AND S.sid = C.sid
                AND S.sname = 'AWS' AND
                NOT EXISTS (    SELECT  *
                                FROM    Catalog C1, Suppliers S1
                                WHERE   P.pid = C1.pid AND S1.sid = C1.sid AND
                                        S1.sname <> 'AWS' )

5.      SELECT  DISTINCT C.sid
        FROM    Catalog C
        WHERE   C.cost > (      SELECT  AVG( C1.cost)
                                FROM    Catalog C1
                                WHERE   C1.pid = C.pid )

6.      SELECT  P.pid, S.sname
        FROM    Parts P, Suppliers S, Catalog C
        WHERE   C.pid = P.pid AND S.sid = C.sid AND
                C.cost = (      SELECT  MAX( C1.cost)
                                FROM    Catalog C1
                                WHERE   C1.pid = C.pid )

7.      SELECT  DISTINCT C.sid
        FROM    Catalog C
        WHERE   NOT EXISTS (    SELECT  *
                                FROM    Parts P, Catalog C1
                                WHERE   P.pid = C1.pid AND P.color = 'Red'
					AND C1.sid = C.sid )

8.      SELECT  DISTINCT C.sid
        FROM    Catalog C, Parts P
        WHERE   C.pid = P.pid AND P.color = 'Red'
        INTERSECT
        SELECT  DISTINCT C1.sid
        FROM    Catalog C1, Parts P1
        WHERE   C1.pid = P1.pid AND P1.color = 'Green'

9.      SELECT  DISTINCT C.sid
        FROM    Catalog C, Parts P
        WHERE   C.pid = P.pid AND P.color = 'Red'
        UNION
        SELECT  DISTINCT C1.sid
        FROM    Catalog C1, Parts P1
        WHERE   C1.pid = P1.pid AND P1.color = 'Green'

10.     SELECT   S.sname, COUNT * AS PartCount
        FROM     Suppliers S, Catalog C, Parts P
        WHERE    P.pid = C.pid AND C.sid = S.sid
        GROUP BY S.sname, S.sid
        HAVING   EVERY ( P.color = 'Green' )

11.     SELECT   S.sname, MAX( C.cost ) AS MaxCost
        FROM     Suppliers S, Parts P, Catalog C
        WHERE    P.pid = C.pid AND C.sid = S.sid
        GROUP BY S.sname, S.sid
        HAVING   ANY ( P.color = 'Red' ) AND ANY ( P.color = 'Green' )