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
How many storms had death records?
assistant
SELECT count(*) FROM storm WHERE Number_Deaths > 0
human
List name, dates active, and number of deaths for all storms with at least 1 death.
assistant
SELECT name , dates_active , number_deaths FROM storm WHERE number_deaths >= 1
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
List name and damage for all storms in a descending order of max speed.
assistant
SELECT name , damage_millions_USD FROM storm ORDER BY max_speed DESC
human
Show the average and maximum damage for all storms with max speed higher than 1000.
assistant
SELECT avg(damage_millions_USD) , max(damage_millions_USD) FROM storm WHERE max_speed > 1000
human
What is the total number of deaths and damage for all storms with a max speed greater than the average?
assistant
SELECT SUM( number_deaths ) , SUM( damage_millions_USD ) FROM storm WHERE max_speed > ( SELECT avg ( max_speed ) FROM storm ) ;