SQL TUTORIALS

Answer Key

1. The top 5 places with the highest average magnitude of earthquakes are: (1) 101km SSW of Tres Picos, Mexico, (2) 76km W of Lata, Solomon Islands, (3) 19km SE of Little Sitkin Island, Alaska, (4) 54km E of Taron, Papua New Guinea, (5) 35km WNW of Panguna, Papua New Guinea. Query used:
   SELECT place, AVG(magnitude)
FROM earthquake
GROUP BY place
ORDER BY AVG(magnitude) DESC
LIMIT 5;
2. The magnitude of the largest earthquake to take place in the 21st century was 9.1. Query used:
   SELECT MAX(magnitude)
FROM earthquake
WHERE occurred_on >= '1/1/2000'
ORDER BY MAX(magnitude) DESC;
3. The average depths for each cause are: explosion: 0.00 (decimal overflow error, explained later), nuclear explosion: 0.28, earthquake: 71.71. Query used:
   SELECT cause, AVG(depth)
FROM earthquake
GROUP BY 1;
4. The total depth for all earthquakes caused by a nuclear explosion is 48.7 meters. Query used:
   SELECT SUM(depth)
FROM earthquake
WHERE cause = 'nuclear explosion';
5. Query used:
   SELECT place, COUNT(*)
FROM earthquake
GROUP BY place
HAVING COUNT(*) >= 3;