human
Schema:
state (state_name, population, area, country_name, capital, density)
city (city_name, population, country_name, state_name)
border_info (state_name, border)
highlow (state_name, highest_elevation, lowest_point, highest_point, lowest_elevation)
lake (lake_name, area, country_name, state_name)
mountain (mountain_name, mountain_altitude, country_name, state_name)
river (river_name, length, country_name, traverse)
Please answer with SQL.
assistant
Sure, I understand.
human
what are the capitals of states that border texas
assistant
SELECT t2.capital FROM state AS t2 JOIN border_info AS t1 ON t2.state_name = t1.border WHERE t1.state_name = 'texas';
human
what states border states that border states that border states that border texas
assistant
SELECT t1.border FROM border_info AS t2 JOIN border_info AS t1 ON t2.border = t1.state_name JOIN border_info AS t3 ON t3.border = t2.state_name JOIN border_info AS t4 ON t4.border = t3.state_name WHERE t4.state_name = 'texas';
human
what are the populations of states which border texas
assistant
SELECT t2.population FROM state AS t2 JOIN border_info AS t1 ON t2.state_name = t1.border WHERE t1.state_name = 'texas';
human
which states does not border texas
assistant
SELECT state_name FROM state WHERE state_name NOT IN ( SELECT border FROM border_info WHERE state_name = 'texas' );
human
what states border texas and have a major river
assistant
SELECT state_name FROM border_info WHERE border = 'texas' AND state_name IN ( SELECT traverse FROM river WHERE LENGTH > 750 );
human
what are the lakes in states bordering texas
assistant
SELECT lake_name FROM lake WHERE state_name IN ( SELECT border FROM border_info WHERE state_name = "texas" ) ;