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 highest points of states surrounding mississippi
assistant
SELECT highest_point FROM highlow WHERE state_name IN ( SELECT border FROM border_info WHERE state_name = 'mississippi' );
human
what rivers are in states that border alabama
assistant
SELECT river_name FROM river WHERE traverse IN ( SELECT border FROM border_info WHERE state_name = 'alabama' );
human
what is the highest point in each state whose lowest point is sea level
assistant
SELECT highest_point , state_name FROM highlow WHERE lowest_elevation = 0;
human
which state has the highest elevation
assistant
SELECT state_name FROM highlow WHERE highest_elevation = ( SELECT MAX ( highest_elevation ) FROM highlow );
human
which state has the lowest elevation
assistant
SELECT state_name FROM highlow WHERE lowest_elevation = ( SELECT MIN ( lowest_elevation ) FROM highlow );
human
count the states which have elevations lower than what alabama has
assistant
SELECT COUNT( state_name ) FROM highlow WHERE lowest_elevation < ( SELECT lowest_elevation FROM highlow WHERE state_name = "alabama" ) ;