Tp 2
Base De Données Avancées
Réalisé par:
Imane Rachid
Encadré par:
Prof. Driss Khebbou
Préparation de l’environnement
Se connecter à votre schéma du TP 1 ou le créer de nouveau s’il n’existe plus :
1- Créer la base de données dont la structure est ci-dessous (avant de créer les tables, visualiser le type
de données de chaque attribut à partir du fichier « gestion_de_cours_velo.sql » joint à ce TP.
2- alimenter la base de données, à partir de fichier « gestion_de_cours_velo.sql ». Je vous conseille
également de visualiser ce fichier, il contient les commande les ordres insert into.
Structure de Schéma importé
- les Gras sont des clés primaires
- les * sont des clés étrangères
- les Gras et* sont à la fois clés primaires et estrangères
Le schéma de la base que vous venez d’importer est :
EQUIPE(code_equipe,nom,directeur)(contrainte :Nom est Unique)
PAYS(code_pays,nom)(contrainte :Nom par défaut est ‘Pays_2020’)
COUREUR(num_dossart,nom,code_equipe*,code_pays*)
ETAPE(num_etape,date_etape,kms,ville_depart,ville_arrivee)
TEMPS(num_dossart*,num_etape*,temps_realise)
Remarque : la table TEMPS ne stocke que les temps des joueurs qui ont participé à l’étape. Si un coureur
déclare forfait pour une étape, son temps n’apparait pas.
I. Fonctions d’agrégation (count, sum, max, min, avg)
3. Donnez le meilleur et le pire temps de l’étape 1.
4. Donnez le nombre de coureurs de l’équipe 'TMT'.
5. Donnez nombre d’étapes et le temps total effectués par 'CHAVANEL Sylvain'.
6. Donnez la moyenne de temps mis pour chaque étape.
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 ?
Une clause HAVING est similaire à une clause WHERE si ce n'est
qu'elle s'applique uniquement aux groupes dans leur ensemble (c'est-à-dire aux
lignes du jeu de résultats qui représentent des groupes), tandis que
la clause WHERE s'applique aux lignes individuelles.
b) Nombre d'étapes effectuées avec un temps supérieur à 2 heures (en secondes, soit 7200
secondes)
c) Ne garder que les coureurs ayant effectué au moins une étape
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.
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.
III. Requêtes imbriquées
10. Donnez le nom des joueurs qui n'ont pas couru l'étape 2.
11. Donnez le nom et le temps du dernier coureur arrivé pour chaque étape
12. Donnez les coureurs qui n'ont pas gagné (autrement dit tous les coureurs sauf le
premier) pour chaque étape.
13. Donnez le 2e meilleur temps pour l'étape 1.
14. Donnez le top 3 des coureurs pour chaque étape.
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))
Conclusion
Ce TP vous a permis de développer vos compétences en SQL, en abordant
des concepts clés comme les fonctions d'agrégation, le regroupement de
données avec GROUP BY, le filtrage avec HAVING, et l'utilisation de
requêtes imbriquées. Ces techniques sont essentielles pour effectuer des
analyses approfondies de données complexes, comme celles liées aux
performances des coureurs dans une compétition. Vous avez ainsi acquis
une meilleure maîtrise des outils SQL pour interroger et manipuler
efficacement une base de données.
