Untitled

###3
#1
SELECT date_id
FROM Observation
WHERE PRCP>0
ORDER BY date_id 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_id 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_id) 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_id)
ORDER BY YEAR(date_id) ASC;

#5
SELECT
   MONTH(date_id) 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_id >= 2013-01-01 AND o.station = s.id AND s.country = 'UK'
GROUP BY MONTH(date_id)
ORDER BY MONTH ASC;

#6 -
SELECT
   AVG(CASE WHEN YEAR(date_id) = 2006 THEN PRCP END) AS avg_precip_2006,
   MAX(CASE WHEN YEAR(date_id) = 2006 THEN TMAX END) AS max_temp_2006,
   MIN(CASE WHEN YEAR(date_id) = 2006 THEN TMIN END) AS min_temp_2006,
   AVG(CASE WHEN YEAR(date_id) = 2021 THEN PRCP END) AS avg_precip_2021,
   MAX(CASE WHEN YEAR(date_id) = 2021 THEN TMAX END) AS max_temp_2021,
   MIN(CASE WHEN YEAR(date_id) = 2021 THEN TMIN END) AS min_temp_2021,
   AVG(CASE WHEN YEAR(date_id) = 2021 THEN PRCP END)
     - AVG(CASE WHEN YEAR(date_id) = 2006 THEN PRCP END) AS diff_avg_precip,
   MAX(CASE WHEN YEAR(date_id) = 2021 THEN TMAX END)
     - MAX(CASE WHEN YEAR(date_id) = 2006 THEN TMAX END) AS diff_max_temp,
   MIN(CASE WHEN YEAR(date_id) = 2021 THEN TMIN END)
     - MIN(CASE WHEN YEAR(date_id) = 2006 THEN TMIN END) AS diff_min_temp
FROM Observation AS o, Station AS s
WHERE YEAR(date_id) IN (2006,2021) AND o.station = s.id AND s.country = 'UK';


###5
#1
SELECT t.year AS year, AVG(o.value) AS avg_prcp
FROM factobservation AS o
JOIN dimtime AS t ON o.time_id = t.date_id
JOIN dimelement AS e ON o.element_id = e.id AND e.name = 'PRCP'
JOIN dimstation AS s ON o.station_id = s.id AND s.country_name = 'Portugal'
GROUP BY year
ORDER BY year ASC;

#2 
SELECT s.country_name AS country, t.year AS year, AVG(o.value) AS avg_max_temp
FROM factobservation AS o
JOIN dimstation AS s ON o.station_id = s.id
JOIN dimelement AS e ON o.element_id = e.id AND e.name = 'TMAX'
JOIN dimtime AS t ON o.time_id = t.date_id
GROUP BY country, year
ORDER BY avg_max_temp DESC LIMIT 1;

#3
SELECT t.day_week AS weekday, t.year AS year, AVG(o.value) AS avg_prcp
FROM factobservation AS o
JOIN dimtime AS t ON o.time_id = t.date_id
JOIN dimelement AS e ON o.element_id = e.id AND e.name = 'PRCP'
GROUP BY weekday, year
ORDER BY avg_prcp DESC LIMIT 1;

#4



#5

SELECT t.month AS month, s.country_name AS country, AVG(o_tmax.value - o_tmin.value) AS avg_range
FROM factobservation AS o
JOIN factobservation AS o_tmax ON o.time_id = o_tmax.time_id AND o.station_id = o_tmax.station_id
JOIN factobservation AS o_tmin ON o.time_id = o_tmin.time_id AND o.station_id = o_tmin.station_id
JOIN dimtime AS t ON o.time_id = t.date_id
JOIN dimelement AS e_tmax ON o_tmax.element_id = e_tmax.id AND e_tmax.name='TMAX'
JOIN dimelement AS e_tmin ON o_tmin.element_id = e_tmin.id AND e_tmin.name='TMIN'
JOIN dimstation AS s ON o.station_id = s.id
GROUP BY month, country, o.time_id, o.station_id
ORDER BY avg_range DESC LIMIT;

#6