CREATE TABLE routes (rid NUMBER, dst_apid NUMBER, dst_ap CLOB, src_apid NUMBER, src_ap CLOB, alid NUMBER, airline CLOB, codeshare CLOB)
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)

Find the number of routes from the United States to Canada.
SELECT COUNT(*) FROM routes WHERE dst_apid IN (SELECT apid FROM airports WHERE country = 'Canada') AND src_apid IN (SELECT apid FROM airports WHERE country = 'United States')
SELECT COUNT(*) FROM "routes" WHERE "dst_apid" IN (SELECT "apid" FROM "airports" WHERE "country" = 'Canada') AND "src_apid" IN (SELECT "apid" FROM "airports" WHERE "country" = 'United States')