SQL and Relational Theory, 2nd Edition

EXAMPLE 9: JOIN QUERIES

This time, for practice, I’ll just present the query and the SQL formulation and leave you to give the logical formulation and the derivation process. The query is “Get suppliers such that every part they supply is in the same city (as in Examples 7 and 8), together with the city in question.” Here’s the SQL formulation:

     SELECT DISTINCT SX.* , PX.CITY
     FROM   S AS SX , P AS PX
     WHERE  EXISTS
          ( SELECT *
            FROM   SP AS SPX
            WHERE  SPX.SNO = SX.SNO
            AND    NOT EXISTS
                 ( SELECT *
                   FROM   SP AS SPY
                   WHERE  SPY.SNO = SPX.SNO
                   AND    EXISTS
                        ( SELECT *
                          FROM   P AS PY
                          WHERE  PY.PNO = SPY.PNO
                          AND    PY.CITY <> PX.CITY ) ) )

Result:

SNO	SNAME	STATUS	CITY
S3	Blake	30	Paris

Exercise: Is the DISTINCT necessary in this example? And why is this section called “Join Queries”?