-- 1.Selezionare il nome della regione seguito dal numero di abitanti, chiamando tale quantità “popolazione”.
SELECT r.nome, SUM(c.popolazione) AS popolazione
FROM regione AS r
	INNER JOIN provincia AS p ON p.codice_istat_regione = r.codice_istat
	INNER JOIN comune AS c ON c.codice_istat_provincia = p.codice_istat
GROUP BY r.nome
ORDER BY popolazione DESC;

-- 2. Selezionare il nome della regione seguito dai casi positivi in tutta la regione al 31 maggio, chiamando quest’ultima quantità “casi_covid_al_31_maggio”.
SELECT r.nome, SUM(cc.casi_positivi_totali) AS casi_covid_al_31_maggio
FROM regione AS r
	INNER JOIN provincia AS p ON p.codice_istat_regione = r.codice_istat
	INNER JOIN casi_covid AS cc ON cc.codice_istat_provincia = p.codice_istat
WHERE MONTH(cc.data)=5 AND DAY(cc.data)=31 
GROUP BY r.nome
ORDER BY casi_covid_al_31_maggio DESC;

-- 3. Selezionare il nome della regione e la percentuale di positivi al 31 maggio, solo per le regioni con una percentuale di positivi superiore allo 0.5%.
SELECT r.nome, SUM(cc.casi_positivi_totali)/SUM(pop.popolazione)*100 AS percentuale
FROM provincia AS p INNER JOIN
	(SELECT p1.codice_istat, SUM(popolazione) as popolazione
		FROM provincia AS p1
			INNER JOIN comune c1 on c1.codice_istat_provincia = p1.codice_istat
		GROUP BY p1.codice_istat
    ) AS pop ON p.codice_istat = pop.codice_istat
    INNER JOIN regione AS r ON p.codice_istat_regione = r.codice_istat
    INNER JOIN casi_covid AS cc ON p.codice_istat = cc.codice_istat_provincia
WHERE MONTH(cc.data)=5 and DAY(cc.data)=31
GROUP BY r.nome
HAVING percentuale > 0.5
ORDER BY percentuale DESC;

-- 4.Per ogni regione (identificata solo dal nome), contare il numero di province e di comuni, chiamando rispettivamente questi campi “numero_province” e “numero_comuni”
SELECT r.nome, num_prov.num AS numero_province, num_com.num AS numero_comuni
FROM  regione AS r
	INNER JOIN
	(
    SELECT codice_istat_regione, COUNT(*) AS num
    FROM provincia
    GROUP BY codice_istat_regione
    ) num_prov ON num_prov.codice_istat_regione = r.codice_istat
    INNER JOIN
    (
    SELECT codice_istat_regione, COUNT(*) AS num
    FROM provincia AS p
    INNER JOIN comune AS c ON c.codice_istat_provincia = p.codice_istat
    GROUP BY codice_istat_regione
    ) num_com ON num_com.codice_istat_regione = r.codice_istat;

-- 5. Selezionare, per ogni provincia (rappresentata da codice_istat, nome e abbreviazione), il numero di acquisizioni totali fatte (una acquisizione = un record in “casi_covid”), indicando questa quantità con “numero_acquisizioni”.
SELECT p.codice_istat, p.nome, p.abbreviazione, COUNT(*) as numero_acquisizioni
FROM provincia AS p
	INNER JOIN casi_covid AS cc ON p.codice_istat = cc.codice_istat_provincia
GROUP BY p.codice_istat, p.nome, p.abbreviazione;