Voici un ensemble de travaux dirigés (TD) couvrant toutes les notions abordées sur les bases
de données relationnelles avec MySQL. Chaque TD est organisé autour d'un objectif
spécifique avec des exercices progressifs pour mettre en pratique vos connaissances.
TD 1 : Création et manipulation de tables
Objectif : Comprendre comment créer, modifier et manipuler des tables.
1. Création de tables :
o Créez une base de données nommée universite.
o Dans cette base, créez deux tables :
etudiants avec les colonnes :
id (clé primaire, entier auto-incrémenté),
nom (VARCHAR(50)),
prenom (VARCHAR(50)),
email (VARCHAR(100)),
age (entier).
cours avec les colonnes :
id (clé primaire, entier auto-incrémenté),
nom_cours (VARCHAR(100)),
enseignant (VARCHAR(50)).
2. Manipulations :
o Ajoutez 5 étudiants dans la table etudiants.
o Ajoutez 3 cours dans la table cours.
o Modifiez l’âge d’un étudiant.
o Supprimez un étudiant de la table.
3. Modification de la structure :
o Ajoutez une colonne date_naissance (DATE) à la table etudiants.
o Modifiez la colonne email pour qu’elle soit obligatoire ( NOT NULL).
o Supprimez la colonne date_naissance.
TD 2 : Requêtes SQL
Objectif : Pratiquer les différentes requêtes pour manipuler les données.
1. Requêtes basiques :
o Sélectionnez tous les étudiants âgés de plus de 20 ans.
o Trouvez tous les cours qui contiennent le mot "Informatique" dans leur nom.
2. Filtres et tri :
o Affichez les étudiants par ordre alphabétique (du nom).
o Affichez les cours enseignés par un enseignant nommé "Durand".
3. Requêtes avancées :
o Comptez le nombre d’étudiants dans la table.
o Affichez les 3 étudiants les plus âgés.
TD 3 : Relations entre tables
Objectif : Comprendre et utiliser les relations entre tables.
1. Création de relations :
o Ajoutez une table inscriptions avec les colonnes :
id (clé primaire, entier auto-incrémenté),
etudiant_id (clé étrangère pointant sur [Link]),
cours_id (clé étrangère pointant sur [Link]),
date_inscription (DATE).
2. Insertion de données :
o Ajoutez 5 inscriptions reliant des étudiants à des cours.
3. Requêtes avec INNER JOIN :
o Affichez tous les étudiants inscrits à un cours.
o Affichez les cours suivis par un étudiant nommé "Dupont".
4. Requêtes avec LEFT JOIN :
o Affichez tous les étudiants, même ceux qui ne sont inscrits à aucun cours.
o Affichez tous les cours, même ceux qui n’ont aucun étudiant inscrit.
TD 4 : Fonctions SQL
Objectif : Utiliser des fonctions SQL pour effectuer des calculs ou manipuler des données.
1. Fonctions d’agrégation :
o Trouvez l’âge moyen des étudiants.
o Comptez le nombre total de cours.
2. Manipulation des chaînes de caractères :
o Affichez les noms des étudiants en majuscules.
o Affichez les 3 premières lettres de chaque nom de cours.
3. Manipulation des dates :
o Affichez les étudiants inscrits après le 1ᵉʳ janvier 2023.
o Ajoutez une colonne annee_naissance (calculée) dans la table etudiants à
partir de leur âge.
TD 5 : Vues et sous-requêtes
Objectif : Comprendre l’utilité des vues et des sous-requêtes.
1. Création de vues :
o Créez une vue vue_etudiants_inscrits qui liste tous les étudiants avec
leurs cours.
o Créez une vue vue_cours_populaires qui liste les cours avec plus de 3
inscriptions.
2. Sous-requêtes :
o Trouvez les étudiants qui suivent le cours "Base de données".
o Trouvez les cours ayant le moins d'inscriptions.
TD 6 : Administration et sécurité
Objectif : Pratiquer la gestion des utilisateurs et des droits.
1. Gestion des utilisateurs :
o Créez un utilisateur gestionnaire avec le mot de passe 12345.
o Donnez à cet utilisateur les droits en lecture et écriture sur la base universite.
2. Droits spécifiques :
o Révoquez les droits d’écriture sur la table etudiants pour l’utilisateur
gestionnaire.
o Vérifiez que l’utilisateur peut toujours lire les données.
3. Sauvegarde et restauration :
o Effectuez une sauvegarde de la base universite au format SQL.
o Supprimez la base et restaurez-la depuis la sauvegarde.
TD 7 : Projet final
Objectif : Mettre en œuvre toutes les notions dans un mini-projet.
Sujet : Gestion d’une bibliothèque universitaire
1. Création des tables :
o Créez les tables suivantes :
livres :
id (clé primaire),
titre,
auteur,
annee_publication,
genre.
etudiants (comme dans les TD précédents).
emprunts :
id (clé primaire),
etudiant_id (clé étrangère),
livre_id (clé étrangère),
date_emprunt,
date_retour.
2. Manipulation :
o Insérez des étudiants, des livres et des emprunts.
o Affichez les étudiants ayant emprunté des livres de l’année 2023.
o Affichez les livres qui n’ont pas été empruntés.
3. Avancées :
o Créez une vue vue_emprunts affichant tous les emprunts avec les noms des
étudiants et les titres des livres.
o Trouvez les étudiants ayant emprunté plus de 3 livres.
Conclusion
Ces travaux dirigés permettent de couvrir les notions de base et avancées en gestion de bases
de données relationnelles avec MySQL. Ils intègrent les aspects essentiels tels que la
manipulation des données, les relations entre tables, les fonctions SQL, et les tâches
d’administration.
