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 are the names of all the storms that affected at least two regions?
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;
human
What are the names of all the storms that affected at least 10 cities?
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 SUM( T2.number_city_affected ) > = 10;
human
What are the names of all the storms that affected at least 10 cities and two regions?
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 ;