Untitled
unknown
plain_text
2 years ago
1.8 kB
8
Indexable
Never
#1 SELECT DATE FROM Observation WHERE PRCP>0 ORDER BY date asc LIMIT 1 ; #2 SELECT s.name AS station FROM Station AS s,Observation AS o WHERE PRCP>0 AND s.id = o.station ORDER BY date ASC LIMIT 1 ; #3 SELECT c.name AS country, COUNT(*) AS count FROM Station AS s, Country AS p, Continent AS c WHERE s.country = p.fips AND p.continent = c.cc GROUP BY c.name; #4 SELECT YEAR(date) AS year, AVG(TMAX) AS avg_max_temp FROM Observation AS o, Station AS s WHERE o.station = s.id AND s.country = 'PO' GROUP BY YEAR(date) ORDER BY YEAR(date) ASC; #5 SELECT MONTH(date) AS month, AVG(PRCP) AS precipitation, MAX(TMAX) AS max_temp, MIN(TMIN) AS min_temp, MAX(TMAX) - MIN(TMIN) AS temp_amplitude FROM Observation AS o, Station AS s WHERE o.date >= 2013-01-01 AND o.station = s.id AND s.country = 'UK' GROUP BY MONTH(date) ORDER BY MONTH ASC; #6 - nao sei se vale a pena printar isto tudo SELECT AVG(CASE WHEN YEAR(date) = 2006 THEN PRCP END) AS avg_precip_2006, MAX(CASE WHEN YEAR(date) = 2006 THEN TMAX END) AS max_temp_2006, MIN(CASE WHEN YEAR(date) = 2006 THEN TMIN END) AS min_temp_2006, AVG(CASE WHEN YEAR(date) = 2021 THEN PRCP END) AS avg_precip_2021, MAX(CASE WHEN YEAR(date) = 2021 THEN TMAX END) AS max_temp_2021, MIN(CASE WHEN YEAR(date) = 2021 THEN TMIN END) AS min_temp_2021, AVG(CASE WHEN YEAR(date) = 2021 THEN PRCP END) - AVG(CASE WHEN YEAR(date) = 2006 THEN PRCP END) AS diff_avg_precip, MAX(CASE WHEN YEAR(date) = 2021 THEN TMAX END) - MAX(CASE WHEN YEAR(date) = 2006 THEN TMAX END) AS diff_max_temp, MIN(CASE WHEN YEAR(date) = 2021 THEN TMIN END) - MIN(CASE WHEN YEAR(date) = 2006 THEN TMIN END) AS diff_min_temp FROM Observation AS o, Station AS s WHERE YEAR(date) IN (2006,2021) AND o.station = s.id AND s.country = 'UK';