Yurttas/PL/DBL/postgres/F/01/Company-B/q08.sql

 1/*
 2REM
 3REM q08.sql
 4REM
 5REM Assume the companies may be located in several cities.
 6REM Find all companies located in every city in which
 7REM 'Small Bank Corporation' is located.
 8REM
 9*/
10
11SELECT current_schema();
12SET search_path to "companyb";
13
14SELECT DISTINCT C1.companyname 
15FROM Company C1
16WHERE NOT EXISTS
17            (SELECT city 
18             FROM Company 
19             WHERE companyname='Small Bank Corporation' 
20               AND city NOT IN (SELECT C2.city 
21                                FROM Company C2 
22                                WHERE C1.companyname = C2.companyname)) 
23  AND C1.companyname <> 'Small Bank Corporation';