How many routes have a destination airport in Canada, please?
select count ( * ) from airports as t1 join routes as t2 on t1.apid = t2.dst_apid where country = 'Canada'
How many routes have a destination airport in the United States, please?
select count ( * ) from airports as t1 join routes as t2 on t1.apid = t2.dst_apid where country = 'United States'
How many routes have a source airport in Canada and a destination airport in the United States, please?
select count ( * ) from ( select rid from airports as t1 join routes as t2 on t1.apid = t2.dst_apid where t1.country = 'United States' intersect select rid from airports as t3 join routes as t4 on t3.apid = t4.src_apid where t3.country = 'Canada' )
What are the names of those source airports that begin with the letter A?
select t1.name from airports as t1 join routes as t2 on t1.apid = t2.src_apid where t1.name like 'A%'
What are the ones that are in Canada only?
select distinct ( t1.name ) from airports as t1 join routes as t2 on t1.apid = t2.src_apid where t1.name like 'A%' and t1.country = "Canada"