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:
|
|
|
|
|
|
|
|
Exercise: Is the DISTINCT necessary in this example? And why is this section called “Join Queries”?