TP2 : BASE DE DONNÉES AVANCÉES (S3 )
Requêtes SQL avancées
- Langage de Définition de données (LDD) PROF IDRISS
- Fonctions d’agrégation (count, sum, max, min, avg) CHANA
- Requêtes imbriquées.
- La clause having
Introduction
Durant ce TP, vous allez écrire des requêtes SQL d’interrogation en utilisant
des constructions plus compliquées telles que les fonctions d’agrégation, group by,
having, et les requêtes imbriquées.
Préparation de l’environnement
Se connecter à votre schéma (ORA_votre_nom) 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 » à demander du
prof. Je vous conseil également de visualiser ce fichier, il contient les commande les ordres
insert into.
Structure de Schéma importé
Le schéma de la base que vous venez d’importer est : - - les Gras sont des
EQUIPE(code_equipe,nom,directeur) clés primaires
(contrainte :Nom est Unique)
- - les * sont des clés
PAYS(code_pays,nom)(contrainte :Nom par
étrangères
défaut est ‘Pays_2020’)
- - les Gras et* sont à
COUREUR(num_dossart,nom,code_equipe*,code_pa
la fois clés primaires
ys*) /.
et estrangères
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 la moyenne de temps mis pour chaque étape.
II. Group by, having
6. 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 ?
7. 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.
8. 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
9. Donnez le nom des joueurs qui n'ont pas couru l'étape 2.
10. Donnez le nom et le temps du dernier coureur arrivé pour chaque étape
11. Donnez les coureurs qui n'ont pas gagné (autrement dit tous les coureurs sauf le premier)
pour chaque étape.
12. Donnez le 2e meilleur temps pour l'étape 1.
13. Donnez le top 3 des coureurs pour chaque étape.
14. Donner les Noms des coureurs dont la première lettre est identique à celle d'un autre joueur
(utiliser la fonction substr(nom,1,1))
