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's the average max speed for all storms?
assistant
SELECT AVG ( max_speed ) FROM storm ;
human
What are the number of deaths and damage for all storms with a max speed greater than the average?
assistant
SELECT number_deaths , damage_millions_USD FROM storm WHERE max_speed > ( SELECT AVG ( max_speed ) FROM storm ) ;
human
How about the total death and total damage?
assistant
SELECT SUM( number_deaths ) , SUM( damage_millions_USD ) FROM storm WHERE max_speed > ( SELECT AVG ( max_speed ) FROM storm ) ;