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

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')