CREATE TABLE airlines (alid NUMBER, name CLOB, iata CLOB, icao CLOB, callsign CLOB, country CLOB, active CLOB)
CREATE TABLE airports (apid NUMBER, name CLOB, city CLOB, country CLOB, x NUMBER, y NUMBER, elevation NUMBER, iata CLOB, icao CLOB)
CREATE TABLE routes (rid NUMBER, dst_apid NUMBER, dst_ap CLOB, src_apid NUMBER, src_ap CLOB, alid NUMBER, airline CLOB, codeshare CLOB)

What is the id of the routes whose source and destination airports are in the United States?
SELECT rid FROM routes WHERE dst_apid IN (SELECT apid FROM airports WHERE country = 'United States') AND src_apid IN (SELECT apid FROM airports WHERE country = 'United States')
SELECT "rid" FROM "routes" WHERE "dst_apid" IN (SELECT "apid" FROM "airports" WHERE "country" = 'United States') AND "src_apid" IN (SELECT "apid" FROM "airports" WHERE "country" = 'United States')