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:
|
|
|
|
|
|
|
|
Exercise: Is the DISTINCT necessary in this example? And why is this section called “Join Queries”?
Get SQL and Relational Theory, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.