\i /ens/hugel/BD/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 ((select * from temps where numerocoureur=13) as a natural join etapes);

---------- 6 ----------
select * from etapes where nbkm=(select MAX(nbkm) from etapes);

---------- 7 ----------
select nomcoureur from ((select * from temps where numeroetape=4 and tempsrealise=(select MIN(tempsrealise) from temps)) as a natural join coureurs);
select nomcoureur from coureurs where numerocoureur in (select numerocoureur from temps where numeroetape=4 and tempsrealise=(select MIN(tempsrealise) from temps));

---------- 8 ----------
select numerocoureur,3600*(select nbkm from etapes where numeroetape=4)/extract(epoch from tempsrealise) as "vitesse (km/h)" from temps where numeroetape=4 order by numerocoureur;

---------- 9 ----------
select codeequipe,count(numerocoureur) as "nb coureurs" from coureurs group by codeequipe;
select nomequipe,"nb coureurs" from ((select codeequipe,count(numerocoureur) as "nb coureurs" from coureurs group by codeequipe) as a natural join equipes);

---------- 10 ----------
select codeequipe,min(tempsrealise) as "meilleur temps" from ((select * from temps where numeroetape=2) as a natural join coureurs) 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 ((select * from temps where numeroetape=3) as a natural join coureurs) 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 (select numeroetape,numerocoureur as a from temps) as b where a=numerocoureur)) order by numerocoureur;

---------- 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;