Executing SQL Query on Normalized DB

Executing SQL Queries to Answer Questions Relating to Milestone 1

The third question I outlined in milestone 1 was the following: Which locations (specified by city and country) have experienced earthquakes of magnitude greater than 6 that triggered tsunamis?

A revised and more complex question that I will seek to answer here is the following: Which cities have not experienced a tsunami-triggering earthquake in the past 3 years, but have in the last 5 years?

The query that helps in answering this question is the following

SELECT DISTINCT city_name, country_name
FROM EarthquakeDetails AS ed
JOIN Location AS l ON ed.location_id = l.location_id
JOIN City AS C ON l.city_id = c.city_id
JOIN Country ON c.country_id = Country.country_id
WHERE year > 2018 AND tsunami = 1
AND city_name NOT IN (
	SELECT DISTINCT city_name
    FROM EarthquakeDetails AS ed
	JOIN Location AS l ON ed.location_id = l.location_id
	JOIN City AS C ON l.city_id = c.city_id
	WHERE year > 2020 AND tsunami = 1
); 

Query Description: The query uses a subquery. The subquery returns the cities that have experienced an earthquake in the last 3 years. The outer query returns the city names that had an earthquake with tsunami in the last 5 years, but filters out those cities that were impacted by the same in the last 3 years.

Below is a screenshot of some of the results

Brief Description of the Findings

I noticed that the top 3 places where the above event occurred was in Chile, Indonesia, and the Philippines. I found this using an adjustment of the previous query.

SELECT DISTINCT country_name, COUNT(country_name)
FROM EarthquakeDetails AS ed
JOIN Location AS l ON ed.location_id = l.location_id
JOIN City AS C ON l.city_id = c.city_id
JOIN Country ON c.country_id = Country.country_id
WHERE year > 2018 AND tsunami = 1
AND city_name NOT IN (
	SELECT DISTINCT city_name
    FROM EarthquakeDetails AS ed
	JOIN Location AS l ON ed.location_id = l.location_id
	JOIN City AS C ON l.city_id = c.city_id
	WHERE year > 2020 AND tsunami = 1
)
GROUP BY country_name
ORDER BY COUNT(country_name) DESC
LIMIT 3; 

Furthermore, the places that were impacted in Chile were San Antonio, Corral, Vallenar, San Pedro de Atacama, Coquimbo, and Consitucion.