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: Count the number of students living 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: List all states with at least two cities.
SQL: SELECT state FROM City GROUP BY state HAVING count(*)  >=  2

Question: What is the average distance between Boston and all other cities.
SQL: SELECT avg(distance) FROM Direct_distance AS T1 JOIN City AS T2 ON T1.city1_code  =  T2.city_code WHERE T2.city_name  =  "Boston"

Question: What are all the distinct states?
SQL: SELECT DISTINCT state FROM City

Question: Give the city name of the city with greatest distance from 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: What is the total distance between city BAL and all other cities.
SQL: SELECT sum(distance) FROM Direct_distance WHERE city1_code  =  "BAL"

Question: What is the latitude and longitude for Baltimore?
SQL: SELECT latitude ,  longitude FROM City WHERE city_name  =  "Baltimore"

Question: What are the names of all cities in PA?
SQL: SELECT city_name FROM City WHERE state  =  "PA"

Question: Show all countries and number of cities in each .
SQL: select country ,  count(*) from city group by country

Question: What are the full names of students living in MD?
SQL: SELECT T2.Fname ,  T2.Lname FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code WHERE T1.state  =  "MD"

Question: Give the state that the student with first name Linda lives in.
SQL: SELECT state FROM Student AS T1 JOIN City AS T2 ON T1.city_code  =  T2.city_code WHERE T1.Fname  =  "Linda"

Question: Give the distance between BAL and CHI?
SQL: SELECT distance FROM Direct_distance WHERE city1_code  =  "BAL" AND city2_code  =  "CHI"

Question: What are the city codes of the cities with the maximum distance?
SQL: SELECT city1_code ,  city2_code FROM Direct_distance ORDER BY distance DESC LIMIT 1

Question: Find the number of students living in each city.
SQL: SELECT T1.city_name ,  count(*) FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code GROUP BY T1.city_code

Question: Show the number of students living 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: Count the number of coutries.
SQL: SELECT count(DISTINCT country) FROM City

Question: What is the average, minimum, maximum distance between two cities?
SQL: SELECT avg(distance) ,  min(distance) ,  max(distance) FROM Direct_distance

Question: How far do Linda (first name) Smith (last name) and Tracy (first name) Kim (last name) 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"

Question: Give the average distance between Boston and other cities.
SQL: SELECT avg(distance) FROM Direct_distance AS T1 JOIN City AS T2 ON T1.city1_code  =  T2.city_code WHERE T2.city_name  =  "Boston"

Question: Show names, codes, states, countries for all cities.
SQL: SELECT city_name ,  city_code ,  state ,  country FROM City

Question: Give the state that has the most students.
SQL: SELECT T1.state FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code GROUP BY T1.state ORDER BY count(*) DESC LIMIT 1

Question: What is the sum of distances between BAL and other cities?
SQL: SELECT sum(distance) FROM Direct_distance WHERE city1_code  =  "BAL"

Question: What is the full name of the student who lives furthest from 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: Show all states and number of cities in each state.
SQL: SELECT state ,  count(*) FROM City GROUP BY state

Question: What is the name of the city closest 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: Show me the city code of two cities with a distance greater than the average.
SQL: SELECT city1_code ,  city2_code FROM Direct_distance WHERE distance  >  (SELECT avg(distance) FROM Direct_distance)

Question: Give the country with the fewest students.
SQL: SELECT T1.country FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code GROUP BY T1.country ORDER BY count(*) LIMIT 1

Question: What are the city name and average distances from each city?
SQL: SELECT T2.city_name ,  avg(distance) FROM Direct_distance AS T1 JOIN City AS T2 ON T1.city1_code  =  T2.city_code GROUP BY T1.city1_code

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

Question: Show all city names and the average distance to all other cities.
SQL: SELECT T2.city_name ,  avg(distance) FROM Direct_distance AS T1 JOIN City AS T2 ON T1.city1_code  =  T2.city_code GROUP BY T1.city1_code

Question: What are the names, codes, states, and countries for all cities?
SQL: SELECT city_name ,  city_code ,  state ,  country FROM City

Question: What are the city codes of cities with distance greater than average?
SQL: SELECT city1_code ,  city2_code FROM Direct_distance WHERE distance  >  (SELECT avg(distance) FROM Direct_distance)

Question: Show ids for all students who don't live in USA.
SQL: SELECT StuID FROM Student EXCEPT SELECT StuID FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code WHERE country  =  "USA"

Question: Show ids for all male students living outside of 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: Which state has most number of students?
SQL: SELECT T1.state FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code GROUP BY T1.state ORDER BY count(*) DESC LIMIT 1

Question: What are the states with more than 5 students?
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: What is the distance between BAL and CHI?
SQL: SELECT distance FROM Direct_distance WHERE city1_code  =  "BAL" AND city2_code  =  "CHI"

Question: Which states have at least two cities?
SQL: SELECT state FROM City GROUP BY state HAVING count(*)  >=  2

Question: List all different states .
SQL: select distinct state from city

Question: How many cities do we have?
SQL: SELECT count(*) FROM City

Question: Which state does the student whose first name is Linda live in?
SQL: SELECT state FROM Student AS T1 JOIN City AS T2 ON T1.city_code  =  T2.city_code WHERE T1.Fname  =  "Linda"

Question: Which country has least number of students?
SQL: SELECT T1.country FROM City AS T1 JOIN Student AS T2 ON T1.city_code  =  T2.city_code GROUP BY T1.country ORDER BY count(*) LIMIT 1