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 Oracle (rappel: Connexion oracle)

1. Charger la base de données JO_v2 en exécutant:

@vider
@jo_v2

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

Exercice 1:

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

• 1. Le nombre d’athlètes.

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