\i '/ens/jurski/Public/BD/TP3 - Sous requetes etc/TourDeFrance.sql'
---------- 1 ----------
select * from etapes where nbkm>=100;
---------- 2 ----------
select * from etapes where nbkm<=60;
---------- 3 ----------
select numeroetape, numerocoureur from temps order by numeroetape,numerocoureur;
---------- 4 ----------
select count(*) as nbcoureurs from coureurs;
---------- 5 ----------
select AVG(tempsrealise) as "temps moyen", SUM(nbkm) as "distance totale" from temps natural join etapes where numerocoureur=31;
---------- 6 ----------
select * from etapes where nbkm=(select MAX(nbkm) from etapes);
---------- 7 ----------
select nomcoureur from temps natural join coureurs where numeroetape=4 and tempsrealise=(select MIN(tempsrealise) from temps where numeroetape=4);
---------- 8 ----------
select numerocoureur, 3600*nbkm/extract(epoch from tempsrealise) as "vitesse (km/h)" from temps natural join etapes where numeroetape=4 order by numerocoureur;
---------- 9 ----------
select codeequipe, count(numerocoureur) as "nb coureurs" from coureurs group by codeequipe;
---------- 10 ----------
select codeequipe, min(tempsrealise) as "meilleur temps" from temps natural join coureurs where numeroetape=2 group by codeequipe;
---------- 11 ----------
select codeequipe, numeroetape, min(tempsrealise) as "meilleur temps" from temps natural join coureurs group by numeroetape, codeequipe order by codeequipe, numeroetape;
---------- 12 ----------
select codeequipe, avg(tempsrealise) as "temps moyen" from temps natural join coureurs where numeroetape=3 group by codeequipe;
---------- 13 ----------
select codeequipe, count(numerocoureur) as "nb coureurs" from coureurs group by codeequipe having count(numerocoureur)>=2;
---------- 14 ----------
select numerocoureur from coureurs where not exists ((select numeroetape from etapes) except (select numeroetape from temps where numerocoureur=coureurs.numerocoureur)) order by numerocoureur;
-- Seconde solution
select numerocoureur from temps group by numerocoureur having count(numeroetape)=(select count(numeroetape) from etapes);
---------- 15 ----------
select numerocoureur, 3600*sum(nbkm)/sum(extract(epoch from tempsrealise)) as "vitesse moy" from (temps natural join etapes) where numerocoureur in (select numerocoureur from temps group by numerocoureur having count(numeroetape)>=3) group by numerocoureur order by numerocoureur;
---------- 16 ----------
-- On crée d'abord une vue (sorte de table intermediaire) "cumul" contenant le temps cumulé (depuis le départ) de chaque coureur pour chaque étape
create view cumul as select b.numeroetape, a.numerocoureur, sum(a.tempsrealise) as somme from temps a join temps b on a.numeroetape <= b.numeroetape and a.numerocoureur=b.numerocoureur group by b.numeroetape, a.numerocoureur;
-- On sélectionne le min pour chaque étape
select numeroetape, numerocoureur as "maillot jaune" from cumul a where somme=(select min(somme) from cumul where numeroetape=a.numeroetape);
---------- 17 ----------
-- utilise la vue "cumul" creee a la question 16
select nomcoureur as vainqueur from (coureurs natural join cumul) where numeroetape=(select max(numeroetape) from etapes) and somme=(select min(somme) from cumul where numeroetape=(select max(numeroetape) from etapes));
---------- 18 ----------
select distinct numerocoureur, nomcoureur from (coureurs natural join temps) where (numeroetape,tempsrealise) in (select numeroetape,min(tempsrealise) from temps group by numeroetape);
---------- 19 ----------
select distinct numerocoureur, nomcoureur from (coureurs natural join temps) where (numeroetape,tempsrealise) in (select numeroetape,min(tempsrealise) from temps group by numeroetape) and numerocoureur in (select numerocoureur from temps group by numerocoureur having count(numeroetape)=(select count(numeroetape) from etapes));
---------- 20 ----------
-- utilise la vue "cumul" creee a la question 16
select codeequipe, numerocoureur from (cumul natural join coureurs) as a where numeroetape=(select max(numeroetape) from etapes) and somme=(select min(somme) from cumul natural join coureurs where codeequipe=a.codeequipe and numeroetape=a.numeroetape);
---------- 21 ----------
select numerocoureur, 3600*sum(nbkm)/extract(epoch from sum(tempsrealise)) as moyenne from temps natural join etapes where nbkm<=60 group by numerocoureur;
---------- 22 ----------
select numerocoureur, 3600*sum(nbkm)/extract(epoch from sum(tempsrealise)) as moyenne from temps natural join etapes where nbkm>=100 group by numerocoureur;
---------- 23 ----------
select codeequipe from temps natural join coureurs where numeroetape=(select max(numeroetape) from etapes) group by codeequipe having count(numerocoureur)>=2;
---------- 24 ----------
-- utilise la vue "cumul" creee a la question 16
select nomcoureur from cumul natural join coureurs where somme=(select max(somme) from cumul where numeroetape=(select count(*) from etapes)) and numeroetape=(select count(*) from etapes);
---------- 25 ----------
select nomcoureur from (coureurs natural join temps) where codepays='FRA' group by nomcoureur having count(numeroetape)=(select count(*) from etapes);