Database: storm_record

CREATE TABLE "storm" (
"Storm_ID" int,
"Name" text,
"Dates_active" text,
"Max_speed" int,
"Damage_millions_USD" real,
"Number_Deaths" int,
PRIMARY KEY ("Storm_ID")
);
CREATE TABLE "region" (
`Region_id` int,
`Region_code` text,
`Region_name` text,
PRIMARY KEY ("Region_id")
);
CREATE TABLE `affected_region` (
`Region_id` int,
`Storm_ID` int,
`Number_city_affected` real,
PRIMARY KEY (`Region_id`,`Storm_ID`),
FOREIGN KEY (`Region_id`) REFERENCES `region`(`Region_id`),
FOREIGN KEY (`Storm_ID`) REFERENCES `storm`(`Storm_ID`)
);

Question: How many regions do we have?
SQL: SELECT count(*) FROM region

Question: Count the number of regions.
SQL: SELECT count(*) FROM region

Question: Show all region code and region name sorted by the codes.
SQL: SELECT region_code ,  region_name FROM region ORDER BY region_code

Question: What are the codes and names for all regions, sorted by codes?
SQL: SELECT region_code ,  region_name FROM region ORDER BY region_code

Question: List all region names in alphabetical order.
SQL: SELECT region_name FROM region ORDER BY region_name

Question: What are the names of the regions in alphabetical order?
SQL: SELECT region_name FROM region ORDER BY region_name

Question: Show names for all regions except for Denmark.
SQL: SELECT region_name FROM region WHERE region_name != 'Denmark'

Question: Return the names of all regions other than Denmark.
SQL: SELECT region_name FROM region WHERE region_name != 'Denmark'

Question: How many storms had death records?
SQL: SELECT count(*) FROM storm WHERE Number_Deaths  >  0

Question: Count the number of storms in which at least 1 person died.
SQL: SELECT count(*) FROM storm WHERE Number_Deaths  >  0

Question: List name, dates active, and number of deaths for all storms with at least 1 death.
SQL: SELECT name ,  dates_active ,  number_deaths FROM storm WHERE number_deaths  >=  1

Question: What are the names, dates active, and number of deaths for storms that had 1 or more death?
SQL: SELECT name ,  dates_active ,  number_deaths FROM storm WHERE number_deaths  >=  1

Question: Show the average and maximum damage for all storms with max speed higher than 1000.
SQL: SELECT avg(damage_millions_USD) ,  max(damage_millions_USD) FROM storm WHERE max_speed  >  1000

Question: What is the average and maximum damage in millions for storms that had a max speed over 1000?
SQL: SELECT avg(damage_millions_USD) ,  max(damage_millions_USD) FROM storm WHERE max_speed  >  1000

Question: What is the total number of deaths and damage for all storms with a max speed greater than the average?
SQL: SELECT sum(number_deaths) ,  sum(damage_millions_USD) FROM storm WHERE max_speed  >  (SELECT avg(max_speed) FROM storm)

Question: Return the total number of deaths and total damange in millions for storms that had a max speed greater than the average.
SQL: SELECT sum(number_deaths) ,  sum(damage_millions_USD) FROM storm WHERE max_speed  >  (SELECT avg(max_speed) FROM storm)

Question: List name and damage for all storms in a descending order of max speed.
SQL: SELECT name ,  damage_millions_USD FROM storm ORDER BY max_speed DESC

Question: What are the names and damage in millions for storms, ordered by their max speeds descending?
SQL: SELECT name ,  damage_millions_USD FROM storm ORDER BY max_speed DESC

Question: How many regions are affected?
SQL: SELECT count(DISTINCT region_id) FROM affected_region

Question: Count the number of different affected regions.
SQL: SELECT count(DISTINCT region_id) FROM affected_region

Question: Show the name for regions not affected.
SQL: SELECT region_name FROM region WHERE region_id NOT IN (SELECT region_id FROM affected_region)

Question: What are the names of regions that were not affected?
SQL: SELECT region_name FROM region WHERE region_id NOT IN (SELECT region_id FROM affected_region)

Question: Show the name for regions and the number of storms for each region.
SQL: 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

Question: How many storms occured in each region?
SQL: 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

Question: List the name for storms and the number of affected regions for each storm.
SQL: 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

Question: How many regions were affected by each storm?
SQL: 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

Question: What is the storm name and max speed which affected the greatest number of regions?
SQL: 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

Question: Return the name and max speed of the storm that affected the most regions.
SQL: 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

Question: Show the name of storms which don't have affected region in record.
SQL: SELECT name FROM storm WHERE storm_id NOT IN (SELECT storm_id FROM affected_region)

Question: What are the names of storms that did not affect any regions?
SQL: SELECT name FROM storm WHERE storm_id NOT IN (SELECT storm_id FROM affected_region)

Question: Show storm name with at least two regions and 10 cities affected.
SQL: 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

Question: What are the names of storms that both affected two or more regions and affected a total of 10 or more cities?
SQL: 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

Question: Show all storm names except for those with at least two affected regions.
SQL: 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

Question: What are the names of storms that did not affect two or more regions?
SQL: 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

Question: What are the region names affected by the storm with a number of deaths of least 10?
SQL: SELECT T2.region_name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id WHERE T3.number_deaths  >=  10

Question: Return the names of the regions affected by storms that had a death count of at least 10.
SQL: SELECT T2.region_name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id WHERE T3.number_deaths  >=  10

Question: Show all storm names affecting region "Denmark".
SQL: SELECT T3.name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id WHERE T2.region_name  =  'Denmark'

Question: What are the names of the storms that affected Denmark?
SQL: SELECT T3.name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id WHERE T2.region_name  =  'Denmark'

Question: Show the region name with at least two storms.
SQL: 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

Question: What are the names of regions with two or more storms?
SQL: 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

Question: Find the names of the regions which were affected by the storm that killed the greatest number of people.
SQL: SELECT T2.region_name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id ORDER BY T3.Number_Deaths DESC LIMIT 1

Question: What are the names of regions that were affected by the storm in which the most people died?
SQL: SELECT T2.region_name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id ORDER BY T3.Number_Deaths DESC LIMIT 1

Question: Find the name of the storm that affected both Afghanistan and Albania regions.
SQL: SELECT T3.Name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id WHERE T2.Region_name  =  'Afghanistan' INTERSECT SELECT T3.Name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id WHERE T2.Region_name  =  'Albania'

Question: What are the names of the storms that affected both the regions of Afghanistan and Albania?
SQL: SELECT T3.Name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id WHERE T2.Region_name  =  'Afghanistan' INTERSECT SELECT T3.Name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id WHERE T2.Region_name  =  'Albania'