Correction TP4- REQUETES SQL

num_etape number(4),

temps_realise NUMBER(10), -- en secondes

constraint pk_tps primary key(num_dossart,num_etape),

constraint fk_tmpnd foreign key(num_dossart) references coureur(num_dossart),

constraint fk_tmpsne foreign key(num_etape) references etape(num)

);

PARTIE II : SAISIE DES DONNEES

insert into equipe values('TMT','T-MOBILE TEAM','Mario Kummer');

insert into equipe values('BLB','Brioche la Boulangere','Jean-Rene Bernaudeau');

insert into equipe values('USP','US Postal Service', 'Berry Floor' );

insert into pays values('FRA','France');

insert into pays values('EU','Etats Unis');

insert into pays values('CAN','Canada');

insert into pays values('ESP', 'Espagne');

insert into pays values('ALL','Allemagne');

insert into pays values('ITA', 'Italie');

insert into coureur values(1,'HIEKMANN Torsten','TMT','ALL');

insert into coureur values(2,'GUERINI Giuseppe','TMT','ITA');

insert into coureur values(3,'REICHL Dirk', 'TMT','ALL');

insert into coureur values(4,'CHAVANEL Sylvain','BLB','FRA');

insert into coureur values(5,'ROUS Didier','BLB','FRA');

insert into coureur values(6,'YUS QUEREJETA Unai','BLB','ESP');

insert into coureur values(7,'ARMSTRONG Lance','USP','EU');

insert into coureur values(8,'McCARTHY Patrick','USP','EU');

insert into coureur values(9,'BARRY Michael','USP','CAN');

insert into etape values(1,to_date('06/07/2008','DD/MM/YYYY'),197,'Brest','Plumelec');

insert into etape values(2,to_date('07/07/2008','DD/MM/YYYY'),164,'Plumelec','Saint-Brieuc');

Badri Sabrine LFIG

insert into etape values(3,to_date('08/07/2008','DD/MM/YYYY'),208,'Saint-Malo','Nantes');

insert into temps values(1,1,15123);

insert into temps values(3,1,14881);

insert into temps values(4,1,15123);

insert into temps values(5,1,15321);

insert into temps values(6,1,15330);

insert into temps values(7,1,15142);

insert into temps values(8,1,15493);

insert into temps values(9,1,15603);

insert into temps values(1,2,13873);

insert into temps values(2,2,13563);

insert into temps values(3,2,13703);

insert into temps values(4,2,13810);

insert into temps values(5,2,13688);

insert into temps values(6,2,13742);

insert into temps values(8,2,13793);

insert into temps values(9,2,13644);

insert into temps values(1,3,18313);

insert into temps values(2,3,18603);

insert into temps values(3,3,18203);

insert into temps values(4,3,18010);

insert into temps values(5,3,18488);

insert into temps values(7,3,18392);

insert into temps values(9,3,18444);

REQUETES SQL SUR UNE TABLE

-- 1. Le nom des coureurs

SELECT nom FROM coureur;

-- 2. Les villes de départ et les villes d’arrivée des étapes

SELECT ville_depart,ville_arrivee FROM etape;

-- 3. Les villes du tour renommées en ‘ville’

Badri Sabrine LFIG

( SELECT ville_depart AS ville

FROM etape )

UNION

( SELECT ville_arrivee AS ville

FROM etape );

-- 4. Les coureurs français, renommés en coureurs_français

SELECT nom as "coureurs_français" FROM coureur WHERE code_pays='FRA';

-- 5. L’étape du 3 juillet 2003

SELECT num FROM etape WHERE

to_date(date_etape,'DD/MM/YYYY')=to_date('03/07/2008','DD/MM/YYYY');

-- 6. Les matricules et nom des coureurs dont le nom commence par ‘A’

SELECT num_dossart, nom FROM coureur WHERE nom LIKE 'A%';

-- 7. Les noms des coureurs dont le matricule est compris entre 1 et 5.

SELECT nom FROM coureur WHERE num_dossart BETWEEN 1 AND 5;

-- 8. Les temps réalisés et les numéros de dossard pour l’étape 1, ordonnés par ordre décroissant sur le

temps réalisé.

SELECT num_dossart,temps_realise

FROM temps

WHERE num_etape=1

ORDER BY temps_realise DESC;

REQUETES SQL SUR PLUSIEURS TABLES

-- 1. Le directeur de l’équipe du coureur numéro 7

SELECT directeur

FROM coureur, equipe

WHERE coureur.code_equipe=equipe.code

AND coureur.num_dossart=7;

-- 2. Le nom des l’équipe, le nom des coureurs et le temps réalisé pour l’étape 1.

SELECT equipe.nom AS nom_equipe,

coureur.nom AS nom_coureur,

to_char(temps.temps_realise,'HH:MI:SS') as temps

FROM temps, coureur, equipe

WHERE coureur.code_equipe=equipe.code

Badri Sabrine LFIG

AND temps.num_dossart=coureur.num_dossart

AND temps.num_etape=1;

-- 3. Le nom des équipes et le nom des coureurs qui ont terminé l’étape 1 en moins de 4h15.

SELECT equipe.nom AS nom_equipe, coureur.nom AS nom_coureur,

to_char(temps.temps_realise,'HH:MI:SS') as temps

FROM temps, coureur, equipe

WHERE coureur.code_equipe=equipe.code

AND temps.num_dossart=coureur.num_dossart

AND temps.num_etape = 1

AND temps.temps_realise < to_date('04:15:00','HH:MI:SS');

-- 4. Les temps réalisés de chaque coureurs trié par numéros d'étape puis par équipe et enfin par temps

réalisés,

SELECT t.num_etape, e.code, c.num_dossart, to_char(t.temps_realise,'HH:MI:SS')

FROM equipe e, coureur c, temps t

WHERE e.code = c.code_equipe

AND t.num_dossart = c.num_dossart

AND c.num_dossart between 1 and 5

ORDER BY t.num_etape, e.code, c.num_dossart, t.temps_realise;

-- 5. Le nombre de miles de chaque étape (arrondi à l'entier inférieur)

SELECT etape.num, floor(kms/1.609344) AS miles

FROM etape;

-- 6. Les dates des étapes de plus de 200 kilomètres sous la forme "DD/MM/YYYY"

SELECT num, to_date(date_etape, 'DD/MM/YYYY') AS date_etape

FROM etape

WHERE kms >= 200;

-- 7. Les villes de départ de la semaine du 5 juillet 2008

SELECT ville_depart

FROM etape

WHERE to_char(to_date('05/07/2008','DD/MM/YYYY'),'ww') = to_char(date_etape,'ww');

-- 8. Les étapes qui démarrent de la ville d'arrivée du jour d'avant

SELECT e1.num

FROM etape e1, etape e2

Badri Sabrine LFIG

WHERE e1.ville_depart = e2.ville_arrivee

AND e1.date_etape = e2.date_etape + 1;

Badri Sabrine LFIG