corrigé TP 1:
Révision: Requêtes SQL
participant(idParticipant, nomParticipant, ville, age)
randonnee(idRando, nomRando, region, distance, denivele, suiteRando)
sortie(idRando, idParticipant, dateSortie, dureeSortie)
Exprimez en SQL les requêtes suivantes:
1. Les informations sur les randonnées (12 tuples)
SELECT * FROM Randonnee;
2. L'identi ant et le nom des randonnées de plus de 20 kms. (4 tuples)
SELECT * FROM Randonnee WHERE distance > 20;
3. Les participants âgés entre 35 et 50 ans. (2 tuple)
SELECT * FROM participant WHERE age BETWEEN 35 AND 50;
4. Les randonnées qui ont une suite. (3 tuples)
SELECT * FROM Randonnee WHERE suiteRando IS NOT NULL;
5. Les informations sur les randonnées ainsi que sur la randonnée qui suit. (3 tuples)
SELECT * FROM Randonnee r1, Randonnee r2 WHERE [Link] = [Link];
6. Les participants de moins de 50 ans et leurs éventuelles sorties (18 tuples)
SELECT * FROM participant LEFT OUTER JOIN Sortie ON [Link] = [Link]
WHERE age < 50;
7. Les participants qui ont fait une randonnée de plus de 1000 mètres de dénivelé (avec jointure). (6 tuples)
SELECT DISTINCT p.* FROM participant p, Randonnee r, Sortie s WHERE [Link] = [Link]
AND [Link] = [Link] AND [Link] > 1000;
8. Les participants qui ont fait une randonnée de plus de 1000 mètres de dénivelé (avec sous-requête). (6
tuples)
SELECT DISTINCT p.* FROM participant p, Sortie s WHERE [Link] = [Link] AND
[Link] = ANY(SELECT idRando FROM Randonnee WHERE denivele > 1000);
9. Les participants qui n'ont jamais fait de sorties (avec sous-requête). (2 tuples)
SELECT p.* FROM participant p WHERE idparticipant NOT IN (SELECT idparticipant FROM Sortie);
SELECT p.* FROM participant p WHERE [Link] != ALL (SELECT idparticipant FROM Sortie);
SELECT p.* FROM participant p WHERE NOT EXISTS (SELECT * FROM Sortie s WHERE [Link]
= [Link]);
10. Les participants qui vivent dans une ville contenant 'an' et qui font des randonnées (avec sous-requêtes).(2
tuples)
SELECT * FROM participant p WHERE ville LIKE '%an%' AND EXISTS (SELECT * FROM Sortie
WHERE [Link] = [Link]);
11. randonnées qui n'ont jamais été faite par un participant (avec sous requête )
SELECT [Link], nomRando FROM Randonnee r WHERE [Link] NOT IN (SELECT DISTINCT
idRando FROM Sortie);
SELECT [Link], nomRando FROM Randonnee r WHERE NOT EXISTS (SELECT * FROM Sortie
WHERE [Link] = [Link]);
1
12. Le participant le plus âgé (avec sous-requête).
SELECT p1.* FROM participant p1 WHERE [Link] >= ALL (SELECT [Link] FROM participant p2);
13. La sortie la plus longue (avec sous-requête).
SELECT * FROM Sortie WHERE dureeSortie >= ALL (SELECT dureeSortie FROM Sortie);
14. les participants qui ont fait des sorties en 2011 et 2012. (3 tuples)
SELECT DISTINCT p1.* FROM participant p1, Sortie s1 WHERE [Link] = [Link]
AND EXTRACT(YEAR FROM dateSortie) = 2011
AND [Link] IN (SELECT [Link] FROM participant p2, Sortie s2 WHERE [Link]
= [Link] AND EXTRACT(YEAR FROM dateSortie) = 2012);
15. les participants qui ont fait des sorties en 2011 ou 2012. (7 tuples)
(SELECT DISTINCT p1.* FROM participant p1, Sortie s1 WHERE [Link] = [Link]
AND EXTRACT(YEAR FROM dateSortie) = 2011)
UNION (SELECT p2.* FROM participant p2, Sortie s2 WHERE [Link] = [Link] AND
EXTRACT(YEAR FROM dateSortie) = 2012);
16. le nombre total des participants.
SELECT count(*) FROM participant ;
17. l'age moyen des participants.
SELECT avg(age) FROM participant; (réponse= 38,6)
18. la distance maximale des randonnées.
SELECT max(distance) FROM randonnee; (réponse= 35)
19. a cher le nombre de randonnées de la région de Blida.
SELECT count(*) FROM randonnee WHERE region='Blida' (reponse=2)
20. a cher le nombre de randonées eectuées par région.
SELECT region, count(*) FROM Randonnee GROUP BY region ;
21. la sortie la plus récente.
SELECT max(datesortie) FROM sortie
22. le nombre de sorties eectuées par jour
SELECT count(*) FROM sortie GROUP BY datesortie
23. le nombre de sorties eectuées par an
SELECT extract (year FROM datesortie) as an,count(*) as nb_sortie FROM sortie GROUP BY extract
(year FROM datesortie)
24. le nombre de randonnées eectuées par les participants
SELECT nomparticipant,count([Link]) FROM participant p, Randonnee r, Sortie s WHERE
[Link] = [Link] AND [Link] = [Link] GROUP BY [Link]
25. les participants qui ont fait une seule randonnée (1 tuple)
SELECT p.* FROM Participant p, Sortie s WHERE [Link] = [Link] GROUP BY id-
Participant HAVING COUNT(*) = 1;
26. La distance totale parcourue par chacun des participants ordonnée par distance décroissante
SELECT p.*, SUM(distance) AS distanceParcourue
FROM Participant p LEFT OUTER JOIN Sortie s ON [Link] = [Link] LEFT OUTER
JOIN Randonnee r ON [Link] = [Link]
GROUP BY idParticipant ORDER BY distanceParcourue DESC;
2
27. Le pourcentage de sortie en fonction des regions, trié par pourcentage
SELECT region, round(count(*)/(SELECT count(*) FROM sortie)*100) || '%' as pourcentage FROM
sortie, randonnee WHERE [Link] = [Link]
GROUP BY region ORDER BY count(*)/(SELECT count(*) FROM sortie)*100
28. Les randonnées (dé nies par leurs identi ants et leurs noms) ainsi que le nombre de randonnée qui sont à
l'origine
SELECT idrando, nomrando, level-1 "Nombre de suite" FROM randonnee WHERE suiterando is not null
start WITH suiterando is null connect by suiterando = prior idrando ORDER BY idrando ;
Mise à jour des tables
1. Mettre en majuscule tous les noms des participants
UPDATE participant SET nomparticipant=upper(nomparticipant)
2. Modi er la ville du participant GHAZALI par Tlemcen
UPDATE participant SET ville='Tlemcen' WHERE nomparticipant='GHAZALI' ou bien(UPDATE par-
ticipant SET ville='Tlemcen' WHERE idparticipant='9')
3. Mettre à jour les randonnee qui n'ont pas de suite par la valeur 0
UPDATE randonnee SET suiterando='0' WHERE suiterando is null
4. Ajouter un nouveau champ pays à la table randonnee et mettre à jour la colonne par la valeur ALGERIE
ALTER TABLE randonnee add pays varchar(20)UPDATE randonnee SET pays='ALGERIE'
5. Ajouter deux contraintes d'intégrité permettant de garantir que la distance des sorties ainsi que l'age des
participants soient strictement positifs.
ALTER TABLE randonnee ADD CONSTRAINT c_distance check (distance >0)
ALTER TABLE participant ADD CONSTRAINT c_age check (age >0)
Création des vues
1. Créer une vue contenant les participants de moins de 50 ans.
CREATE VIEW part50 as SELECT * FROM participant WHERE age<50;
2. Créer une vue contenant les noms des participants de moins de 50 qui ont fait une randonnée à Tizi Ouzou
(utiliser la vue précédemment créé)
CREATE VIEW part50tizi as SELECT nomparticipant FROM part50,sortie,randonnee WHERE [Link]
= [Link] AND [Link] = [Link] AND [Link]= 'Tizi Ouzou'
