human
Schema:
storm (Storm_ID, Name, Dates_active, Max_speed, Damage_millions_USD, Number_Deaths)
region (Region_id, Region_code, Region_name)
affected_region (Region_id, Storm_ID, Number_city_affected)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the storm name and max speed which affected the greatest number of regions?
assistant
SELECT T1.name , T1.max_speed FROM storm AS T1 JOIN affected_region AS T2 ON T1.storm_id = T2.storm_id GROUP BY T1.storm_id ORDER BY count(*) DESC LIMIT 1
human
Show the name for regions and the number of storms for each region.
assistant
SELECT T1.region_name , count(*) FROM region AS T1 JOIN affected_region AS T2 ON T1.region_id = T2.region_id GROUP BY T1.region_id
human
Show the name for regions not affected.
assistant
SELECT region_name FROM region WHERE region_id NOT IN (SELECT region_id FROM affected_region)
human
List all region names in alphabetical order.
assistant
SELECT region_name FROM region ORDER BY region_name
human
How many regions are affected?
assistant
SELECT count(DISTINCT region_id) FROM affected_region
human
How many regions do we have?
assistant
SELECT COUNT( * ) FROM region ;