TME 7: Requêtes d'agrégation et Division

Exercice 1:

On considère le schéma de la base Jeux Olympiques 2014 :

• Pays (codePays, nomP)
• Sport (sid, nomSp)
• Epreuve (epid, sid*, nomEp, catégorie, dateDebut, dateFin)
• Athlete (aid, nomAth, prenomAth, dateNaissance, codePays*)
• Equipe (eqid, codePays*)
• AthletesEquipe (eqid*, aid*)
• RangIndividuel (epid*, aid*, rang)
• RangEquipe (epid*, eqid*, rang)

Travail à effectuer: - Connectez-vous au serveur H2 et recharger la base JO_v2

Rappel : pour détruire le schéma de la base courante sous H2

 DROP ALL objects

- Connectez-vous au serveur Oracle (rappel: Connexion oracle) - Charger la base de données JO_v2 en exécutant: <code sql>@vider @jo_v2</code>

Écrivez et évaluez les expressions SQL pour répondre aux requêtes suivantes.

Fonctions d’agrégation « COUNT, SUM, AVG, MIN, MAX »

• 1. Le nombre d’athlètes.

select count(*)
from Athlete;

Résultat attendu (1 ligne) : 2431

• 2. Le nombre d’athlètes ayant participé à au moins une épreuve en individuel.

Résultat attendu (1 ligne) : 1558

• 3. L'âge moyen des sportifs dont le code pays est 'FRA' (France) au 06/02/2014. Aide : sous Oracle, utilisez:

1. to_date('06/02/2014','dd/mm/YYYY')
2. round(valeur,nb) pour garder seulement nb décimales à valeur

Résultat attendu (1 ligne) : 26,8

• 4. La durée moyenne, minimale et maximale des épreuves. Aide : sous Oracle, utilisez l’opérateur de concaténation || . Attention : entre le 10/01/2014 et le 13/01/2014, il y a une durée de 4 jours (et non pas 3).

Résultat attendu(1 ligne) : « Durée moyenne = 1,98 min = 1 max = 16 »

• 5. Le nombre moyen d'athlètes par pays, c'est-à-dire le nombre d'athlètes divisé par le nombre de pays (ayant au moins un athlète).

Résultat attendu(1 ligne) : 27,625

Partitionnement « group by »

• 6. Pour chaque pays, le nom du pays et le nombre d’athlètes, ordonner par nombre d’athlètes croissant.

Résultat attendu (88 lignes) : (PAK,1) ; (HKG, 1) ; … ; (USA, 196) ; (CAN,221)

• 7. Le nombre moyen d'athlètes par pays (avec group by). Aide : compter le nombre d’athlètes dans chaque pays (ayant au moins un athlète), puis faire la moyenne.

Résultat attendu (1 ligne) : 27,625

• 8. Pour chaque équipe, l’eqid de l'équipe et le nombre d'athlètes, ordonner par nombre d’athlètes décroissant.

Résultat attendu (296 lignes) : (164,25) ; (165,25) ; (166,25) ; … ; (180,2) ; (181, 2) ; (182, 2)

• 9. Pour chaque catégorie, la catégorie et le nombre d'épreuves.

Résultat attendu (3 lignes) : (Femmes,43) ; (Mixte,6) ; (Hommes,49)

• 10. Pour chaque sport, le nom du sport et le nombre d'épreuves, ordonner par nombre d'épreuves décroissant.

Résultat attendu (15 lignes) : (Patinage de vitesse,12) ; (Ski de fond,12) ; … ;(Hockey sur glace,2)

• 11. Pour chaque pays, le code du pays, le nombre de médailles en épreuve individuelle gagnées et le nombre d'athlètes ayant gagnés au moins une médaille. Ordonner par nombre de médailles décroissant. Aide : 2 tables seulement sont nécessaires.

Résultat attendu (24 lignes) : (NOR, 24,19) ; (NED,22,15) ; …

• 12. Pour chaque pays et sport, le code du pays, le sid du sport, le nombre de médailles en épreuve individuelle gagnées, le nombre d'athlètes ayant gagnés au moins une médaille, ordonner d'abord par code pays, puis par nombre de médailles décroissant.

Résultat attendu (84 lignes) : (AUS,12,2,2); (AUS,15,1,1);(AUT,13,9,7);(AUT,15,2,2);…

Partitionnement avec « group by / having »

• 13. L’eqid de la ou des équipes qui sont composées :

1. d'exactement 10 athlètes. Résultat attendu (1 ligne) : 226
2. du plus d’athlètes pour ces JO. Résultat attendu (3 lignes) : 164 ; 165 ; 166

• 14. Le nombre d'épreuves en individuel où il y a eu au moins 100 participants.

Résultat attendu (1 ligne ) : 2

• 15. Le nom des pays qui ont gagné au moins 20 médailles aux épreuves individuelles.

Résultat attendu (3 lignes) : Pays-Bas ; États-Unis ; Norvège

Division en SQL

• 16. Le sid des sports qui ont des épreuves dans toutes les catégories existantes.

Résultat attendu (3 lignes) : 1 ; 6 ; 7

• 17. Le nom des pays qui ont participé aux épreuves en individuel de tous les sports en individuel. Résultat attendu (3 lignes) : (Russie,12) ; (États-Unis,12) ; (Italie,12)

Exercice 2:

On considère le schéma « Foofle » (la table des distances s'appelle Dist, la table des équipes s'appelle ÉquipeF):

• Sponsorise(NSp, NJo, Somme)
• Joueur(NJo,  Eq, Taille, Age)
• EquipeF(NEq, Ville, Couleur, StP)
• Match(Eq1, Eq2, DateM, St)
• Dist(St1, St2, NbKm)

Charger la base de données Foofle.

Rappel : pour détruire le schéma de la base courante sous H2

 DROP ALL objects

<code sql> @vider @foofle </code>

Écrivez les expressions SQL pour répondre aux requêtes suivantes :

• 18. Pour chaque sponsor, le nom du sponsor, le nombre de joueurs sponsorisés, le montant total des sommes versées, ordonner par sommes versées décroissantes.

Résultat attendu (7 lignes) : (Adadis,6,2340) ; (Robek,5,1426) ;…

• 19. Quelles équipes ont joué au moins dans 3 stades différents ?

Résultat attendu (2 lignes) : Fortiches ; Direkt

• 20. Quels sponsors sponsorisent exactement un joueur pour chaque équipe qu'il sponsorise ?

Résultat attendu (2 lignes) : Air Monaco ; Palasse

• 21. Quel est le nombre total de kilomètres parcourus par chaque équipe. On suppose qu’après chaque match, chaque équipe se rend directement au stade où aura lieu son prochain match (d’après la date du match). Aide : il existe 2 matchs ordonnés par leur date pour la même équipe, mais il n’existe pas un 3ième match entre les dates des 2 matchs pour cette équipe.

Résultat attendu (4 lignes) : (Fortiches,516) ; (Direkt,671) ; (Piepla,124) ; (Sabar,360)