Database schema:
CREATE TABLE Student (
  StuID NUMBER PRIMARY KEY,
  LName TEXT,
  Fname TEXT,
  Age NUMBER,
  Sex TEXT,
  Major NUMBER,
  Advisor NUMBER,
  city_code TEXT,
  FOREIGN KEY (city_code) REFERENCES City(city_code)
);
CREATE TABLE Direct_distance (
  city1_code TEXT,
  city2_code TEXT,
  distance NUMBER,
  FOREIGN KEY (city2_code) REFERENCES City(city_code),
  FOREIGN KEY (city1_code) REFERENCES City(city_code)
);
CREATE TABLE City (
  city_code TEXT PRIMARY KEY,
  city_name TEXT,
  state TEXT,
  country TEXT,
  latitude NUMBER,
  longitude NUMBER
);

Question: What are the ids for male students not in the USA?
SQL: SELECT StuID FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code WHERE T2.sex  =  'M' AND T1.country != "USA"

Question: Show all states where more than 5 students live.
SQL: SELECT T1.state FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code GROUP BY T1.state HAVING count(*)  >  5

Question: Show me the city code of two cities with maximum distance.
SQL: SELECT city1_code ,  city2_code FROM Direct_distance ORDER BY distance DESC LIMIT 1

Question: What are the city codes corresponding to cities with distances less than 1000?
SQL: SELECT city1_code ,  city2_code FROM Direct_distance WHERE distance  <  1000

Question: Show names for all cities in state PA.
SQL: SELECT city_name FROM City WHERE state  =  "PA"

Question: How many students live in each country?
SQL: SELECT T1.country ,  count(*) FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code GROUP BY T1.country

Question: What is the name of the city furthest to Boston?
SQL: SELECT T3.city_name FROM Direct_distance AS T1 JOIN City AS T2 ON T1.city1_code  =  T2.city_code JOIN City AS T3 ON T1.city2_code  =  T3.city_code WHERE T2.city_name  =  "Boston" ORDER BY distance DESC LIMIT 1

Question: How many students live in China?
SQL: SELECT count(*) FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code WHERE T1.country  =  "CHINA"

Question: What is the distance between Boston and Newark?
SQL: SELECT distance FROM Direct_distance AS T1 JOIN City AS T2 ON T1.city1_code  =  T2.city_code JOIN City AS T3 ON T1.city2_code  =  T3.city_code WHERE T2.city_name  =  "Boston" AND T3.city_name  =  "Newark"

Question: What are the first names and majors of students living in Baltimore?
SQL: SELECT T2.Fname ,  T2.Major FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code WHERE T1.city_name  =  "Baltimore"

Question: Give the state that has the most cities.
SQL: SELECT state FROM City GROUP BY state ORDER BY count(*) DESC LIMIT 1

Question: What are all the city names for cities in the USA, ordered by latitude?
SQL: SELECT city_name FROM City WHERE country  =  "USA" ORDER BY latitude

Question: Return the first name and major of students are living in Baltimore?
SQL: SELECT T2.Fname ,  T2.Major FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code WHERE T1.city_name  =  "Baltimore"

Question: What is the first name and last name of the student living furthest to Linda Smith?
SQL: SELECT T3.Fname ,  T3.Lname FROM Direct_distance AS T1 JOIN Student AS T2 ON T1.city1_code  =  T2.city_code JOIN Student AS T3 ON T1.city2_code  =  T3.city_code WHERE T2.Fname  =  "Linda" AND T2.Lname  =  "Smith" ORDER BY distance DESC LIMIT 1

Question: Give the name of the nearest city to Chicago.
SQL: SELECT T3.city_name FROM Direct_distance AS T1 JOIN City AS T2 ON T1.city1_code  =  T2.city_code JOIN City AS T3 ON T1.city2_code  =  T3.city_code WHERE T2.city_name  =  "Chicago" ORDER BY distance LIMIT 1

Question: Count the number of cities in Canada.
SQL: SELECT count(*) FROM City WHERE country  =  "CANADA"

Question: How many cities are there in each country?
SQL: SELECT country ,  count(*) FROM City GROUP BY country

Question: What are the student ids for female students in the state of PA?
SQL: SELECT StuID FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code WHERE T1.state  =  "PA"  AND T2.sex  =  'F'

Question: For each city, what is the the city code and sum of distances from each?
SQL: SELECT city1_code ,  sum(distance) FROM Direct_distance GROUP BY city1_code

Question: Show me the distance between Boston and Newark.
SQL: SELECT distance FROM Direct_distance AS T1 JOIN City AS T2 ON T1.city1_code  =  T2.city_code JOIN City AS T3 ON T1.city2_code  =  T3.city_code WHERE T2.city_name  =  "Boston" AND T3.city_name  =  "Newark"

Question: What is the distance between the cities where Linda Smith and Tracy Kim live?
SQL: SELECT distance FROM Direct_distance AS T1 JOIN Student AS T2 ON T1.city1_code  =  T2.city_code JOIN Student AS T3 ON T1.city2_code  =  T3.city_code WHERE T2.Fname  =  "Linda" AND T2.Lname  =  "Smith" AND T3.Fname  =  "Tracy" AND T3.Lname  =  "Kim"