Compte rendu TP2
TP 2.1
1- Créer la base de données :
Table equipe :
create table equipe(
code varchar(3),
nom varchar(30),
directeur varchar(30),
constraint pk_eq primary key(code)
Table pays :
create table pays(
code varchar(3),
nom varchar(20),
constraint pk_pa primary key(code)
);
Table coureur :
create table coureur(
num_dossart number(4),
nom varchar(20),
code_equipe varchar(3),
code_pays varchar(3),
constraint pk_co primary key(num_dossart),
constraint fk_coce foreign key(code_equipe) references equipe(code),
constraint fk_cocp foreign key(code_pays) references pays(code)
);
Table etape :
create table etape(
num number(4),
date_etape date,
kms number(4),
ville_depart varchar(20),
ville_arrivee varchar(20),
constraint pk_et primary key(num)
);
Table temps :
create table temps(
num_dossart number(4),
num_etape number(4),
temps_realise NUMBER(10),
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)
);
2- alimenter la base de données :
Table equipe :
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' );
Table pays :
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');
Table coureur :
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');
Table etape :
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');
insert into etape values(3,to_date('08/07/2008','DD/MM/YYYY'),208,'Saint-Malo','Nantes');
Table temps :
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);
commit;
I. Fonctions d’agrégation (count, sum, max, min, avg)
3. Donnez le meilleur et le pire temps de l’étape 1.
SELECT MAX(TEMPS_REALISE) PIRE_TEMPS, MIN(TEMPS_REALISE) MEILLEUR_TEMPS
FROM TEMPS
WHERE NUM_ETAPE = 1;
4. Donnez le nombre de coureurs de l’équipe 'TMT'.
SELECT COUNT(*) NBR_COUREUR FROM COUREUR
WHERE CODE_EQUIPE = 'TMT';
5. Donnez nombre d’étapes et le temps total effectués par 'CHAVANEL Sylvain'.
SELECT COUNT(*) NBR_ETAPES, SUM(TEMPS_REALISE) TEMPS_TOTAL
FROM TEMPS T, COUREUR C
WHERE T.NUM_DOSSART = C.NUM_DOSSART
AND NOM = 'CHAVANEL Sylvain';
6. Donnez la moyenne de temps mis pour chaque étape.
SELECT NUM_ETAPE, AVG(TEMPS_REALISE) TEMPS_MOYENNE
FROM TEMPS
GROUP BY NUM_ETAPE;
II. Group by, having
7. Donnez le nombre d’étapes effectuées pour chaque coureur. Compléter la requête en
ordonnant les résultats par ordre croissant du nom des coureurs. Modifier la requête de
sorte de ne considérer que les temps supérieurs à 2h. Compléter la requête en ne
gardant que les coureurs qui ont effectués au moins une étape. Quelle est la différence
entre la clause WHERE et la clause HAVING ?
SELECT NOM, COUNT(*) NBR_ETAPE
FROM TEMPS, COUREUR
WHERE TEMPS.NUM_DOSSART = COUREUR.NUM_DOSSART AND TEMPS_REALISE > (2 *
3600)
GROUP BY NOM
HAVING COUNT(*) > 0
ORDER BY NOM ASC;
8. Donnez le code et le nom des pays ayant plus d'un coureur, ainsi que le nombre de
coureurs par pays, classé par ordre alphabétique croissant des noms de pays.
SELECT P.CODE, P.NOM, COUNT(*) NBR_COUREUR
FROM PAYS P, COUREUR C
WHERE P.CODE = C.CODE_PAYS
GROUP BY P.CODE, P.NOM
HAVING COUNT(*) > 1
ORDER BY P.NOM ASC
9. Donnez le nom des coureurs dont le temps total (somme du temps mis pour chaque
étape) est inférieur à 9h00, classé par temps total croissant.
SELECT C.NOM, SUM(TEMPS_REALISE) TEMPS_TOTAL
FROM COUREUR C, TEMPS T
WHERE T.NUM_DOSSART = C.NUM_DOSSART
GROUP BY C.NOM
HAVING SUM(TEMPS_REALISE) < (9 * 3600)
ORDER BY TEMPS_TOTAL ASC;
III. Requêtes imbriquées
10. Donnez le nom des joueurs qui n'ont pas couru l'étape 2.
SELECT NOM
FROM COUREUR
WHERE NUM_DOSSART NOT IN (SELECT NUM_DOSSART FROM TEMPS WHERE
NUM_ETAPE = 2);
11. Donnez le nom et le temps du dernier coureur arrivé pour chaque étape
SELECT T.NUM_ETAPE, T.NUM_DOSSART
FROM TEMPS T
WHERE t.TEMPS_REALISE = (SELECT MAX(T1.TEMPS_REALISE) FROM TEMPS T1 WHERE
T1.NUM_ETAPE = T.NUM_ETAPE);
12. Donnez les coureurs qui n'ont pas gagné (autrement dit tous les coureurs sauf le
premier) pour chaque étape.
SELECT T.NUM_ETAPE, T.NUM_DOSSART
FROM TEMPS T
WHERE T.TEMPS_REALISE != (SELECT MIN(T1.TEMPS_REALISE) FROM TEMPS T1 WHERE
T1.NUM_ETAPE = T.NUM_ETAPE);
13. Donnez le 2e meilleur temps pour l'étape 1.
SELECT MIN(TEMPS_REALISE) LE_2E_MEILLEUR_TEMPS
FROM TEMPS
WHERE NUM_ETAPE = 1
AND TEMPS_REALISE > (
SELECT MIN(TEMPS_REALISE)
FROM TEMPS
WHERE NUM_ETAPE = 1
);
14. Donnez le top 3 des coureurs pour chaque étape.
SELECT T1.NUM_ETAPE, T1.NUM_DOSSART
FROM TEMPS T1 WHERE (
SELECT COUNT(*)
FROM TEMPS T2
WHERE T2.NUM_ETAPE = T1.NUM_ETAPE AND T2.TEMPS_REALISE <=
T1.TEMPS_REALISE AND T2.NUM_DOSSART != T1.NUM_DOSSART) = 2;
15. Donner les Noms des coureurs dont la première lettre est identique à celle d'un
autre joueur (utiliser la fonction substr(nom,1,1))
SELECT C1.NOM
FROM COUREUR C1
WHERE EXISTS (SELECT * FROM COUREUR C2 WHERE SUBSTR(C1.NOM, 1, 1) =
SUBSTR(C2.NOM, 1, 1) AND C1.NUM_DOSSART != C2.NUM_DOSSART);
TP 2.2 bis
-- I. Fonctions arithm�tiques
SELECT SIN(3/2) FROM DUAL;
SELECT COS(3) FROM DUAL;
SELECT TAN(3/4) FROM DUAL;
SELECT EXP(1) FROM DUAL;
SELECT LN(10) FROM DUAL;
SELECT LOG(10, 100) FROM DUAL;
SELECT POWER(2, 3) FROM DUAL;
SELECT SQRT(16) FROM DUAL;
SELECT CEIL(4.2) FROM DUAL;
SELECT ROUND(4.567, 2) FROM DUAL;
SELECT ABS(-10) FROM DUAL;
SELECT SIGN(-5) FROM DUAL;
SELECT TRUNC(4.567, 2) FROM DUAL;
-- II. Expressions et fonctions sur les cha�nes de caract�res
SELECT 'Hello' || ' ' || 'World' FROM DUAL;
SELECT CONCAT('Hello', ' World') FROM DUAL;
SELECT INITCAP('hello world') FROM DUAL;
SELECT LOWER('HELLO WORLD') FROM DUAL;
SELECT UPPER('hello world') FROM DUAL;
SELECT LPAD('123', 5, '0') FROM DUAL;
SELECT RPAD('123', 5, '0') FROM DUAL;
SELECT LTRIM(' Hello', ' ') FROM DUAL;
SELECT RTRIM('Hello ', ' ') FROM DUAL;
SELECT REPLACE('hello world', 'world', 'Bilal') FROM DUAL;
SELECT SUBSTR('Oracle SQL', 8, 3) FROM DUAL;
SELECT TRANSLATE('oracle', 'aeo', '123') FROM DUAL;
SELECT INSTR('Oracle SQL Developer', 'SQL', 1, 1) FROM DUAL;
SELECT LENGTH('Oracle Developer') FROM DUAL;
-- Application 1
SELECT LOWER(FIRST_NAME) || ' ' || UPPER(SUBSTR(LAST_NAME, 1, 3)) ||
SUBSTR(LAST_NAME, 4) FROM EMPLOYEES;
-- Application 2
SELECT DECODE(e.DEPARTMENT_ID, 90, '---', e.LAST_NAME) LAST_NAME,department_id
FROM EMPLOYEES e;
