USE mondiale2009_popolato;

-- Q1
SELECT P.CodPilota, P.Nome, P.Cognome, P.Nazionalita, SUM(R.Punti) AS Punti_totali
FROM Pilota AS P
	INNER JOIN Risultato AS R ON R.Pilota = P.CodPilota
GROUP BY P.CodPilota
ORDER BY Punti_totali DESC;
 

-- Q2
SELECT S.idSQ, S.NomeSQ, S.Motore, SUM(R.Punti) AS Punti_totali
FROM Squadra AS S
	INNER JOIN Risultato AS R ON R.Squadra = S.idSQ
GROUP BY S.idSQ
ORDER BY Punti_totali DESC;

-- Q3
SELECT P.Nome, P.Cognome, COUNT(*) AS Partecipazioni
FROM Risultato AS R
	INNER JOIN Pilota AS P ON R.Pilota = P.CodPilota
GROUP BY P.CodPilota
HAVING partecipazioni < 4; 

-- Q4
SELECT P.Nome, P.Cognome, COUNT(*)
FROM Risultato AS R
	INNER JOIN Pilota AS P On R.Pilota=P.CodPilota
	INNER JOIN GranPremio AS G on G.DataGP=R.GranPremio
WHERE R.GiriEffettuati > 0.5 * G.Giri
GROUP BY P.CodPilota;

-- Q5
SELECT G.Nome, G.Nazione, G.Circuito, COUNT(*)
FROM GranPremio AS G
	INNER JOIN Risultato AS R ON G.DataGP = R.GranPremio
WHERE R.Posizione IS NULL
GROUP BY G.DataGP;

-- Q6
SELECT R.MotivoRitiro, COUNT(*) AS Numero_ritiri
FROM Risultato AS R 
WHERE R.MotivoRitiro IS NOT NULL
GROUP BY R.MotivoRitiro
ORDER BY Numero_ritiri DESC;

-- Q7
SELECT DISTINCT P.Nome, P.Cognome, S1.NomeSQ, S2.NomeSQ
FROM Pilota AS P
	INNER JOIN Appartiene AS R1 ON R1.Pilota = P.CodPilota
	INNER JOIN Appartiene AS R2 ON R2.Pilota = P.CodPilota
	INNER JOIN Squadra AS S1 ON S1.idSQ = R1.Squadra
	INNER JOIN Squadra AS S2 ON S2.idSQ = R2.Squadra
WHERE R1.Squadra > R2.Squadra;

-- Q8
SELECT S.NomeSQ, COUNT(*) numero_piloti
FROM Appartiene AS A
	INNER JOIN Squadra AS S ON S.idSQ = A.Squadra
GROUP BY A.Squadra
HAVING numero_piloti > 2;