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
Show all storm names except for those with at least two affected regions.
assistant
SELECT name FROM storm EXCEPT SELECT T1.name FROM storm AS T1 JOIN affected_region AS T2 ON T1.storm_id = T2.storm_id GROUP BY T1.storm_id HAVING count(*) >= 2
human
Show the name of storms which don't have affected region in record.
assistant
SELECT name FROM storm WHERE storm_id NOT IN (SELECT storm_id FROM affected_region)
human
Show storm name with at least two regions and 10 cities affected.
assistant
SELECT T1.name FROM storm AS T1 JOIN affected_region AS T2 ON T1.storm_id = T2.storm_id GROUP BY T1.storm_id HAVING count(*) >= 2 INTERSECT SELECT T1.name FROM storm AS T1 JOIN affected_region AS T2 ON T1.storm_id = T2.storm_id GROUP BY T1.storm_id HAVING sum(T2.number_city_affected) >= 10
human
List the name for storms and the number of affected regions for each storm.
assistant
SELECT T1.name , count(*) FROM storm AS T1 JOIN affected_region AS T2 ON T1.storm_id = T2.storm_id GROUP BY T1.storm_id
human
Show the region name with at least two storms.
assistant
SELECT T1.region_name FROM region AS T1 JOIN affected_region AS T2 ON T1.region_id = T2.region_id GROUP BY T1.region_id HAVING count(*) >= 2
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 ;