TD6 - Le retour du Select

Inès de Courchelle

Durée : 4h30

Consignes :

Durant ce TD l’utilisation d’un papier et d’un crayon est fortement conseillé !

Objectifs :

  • Réaliser des tripples requêtes
  • Maitriser les jointures (LEFT,RIGHT,INNER)
  • Réfléchir encore un peu plus qu’avant !

Attention :

  • L’ensemble des exercices ci-dessous ne seront pas tous corrigés en cours !
  • Les éléments de correction seront donnés en TD, EN AUCUN CAS, des corrections toutes faites vous serons données ou distribuées. Vous devez prendre des notes !

Exo 1 - Zelda

Nous RE-considèrons le MLD suivant :

Joueur(idJoueur, pseudo, niveauEndurance, nbCoeurs)

Plat(idPlat, nom, famille,puissance)

Manger(#idJoueur, #idPlat,horaire)

Ingredient(idIngredient,nom);

Composer(#idIngredient,#idPlat,quantité);

Monde(idMonde, nom)

Sanctuaire(idSanctuaire, nom, niveau, #idMonde)

Visiter(#idJoueur,#idSanctuaire,horaire, vainqueur)

CreatureDivine(idCreatureDivine,nom,#idMonde)

Combattre(#idJoueur,#idCreatureDivine,horairevainqueur)

Attention : Si vous avez déjà utilisé la base de données Zelda dans le TP précédent, il n’est pas nécessaire de ré-installer la BDD. Vous pouvez directement lancer mySQL et lancer la commande use Zelda. Cependant, il vous faudra créer un nouveau script pour stocker les nouvelles instructions.

  1. Télécharger le script zeldaLDD.sql
  2. Lancer mySQL
  3. Exécuter le script zeldaLDD.sql
  4. Créer le nouveau script zeldaLMDTD6.sql
  5. Ajouter les instructions au script zeldaLMDTD6.sql permettant d’afficher :
    1. Quels sont les joueurs qui n’ont jamais mangé de plat ?
    2. Quelle est la moyenne de plat consommé par joueur ?
    3. Pour chaque joueur, donner le nombre total de plats consommés. Afficher les joueurs dans l’ordre décroissant du nombre de plats consommés, y compris ceux qui n’ont rien consommé.
    4. Quel est le joueur qui a consommé le plus de plat ?
    5. Quel plat contient le plus d’ingrédients différents ?

Exo 2 - Harry Potter

Nous RE-considèrons le MLD suivant :

Maison(idMaison, nom, couleur)

Eleve(idEleve, nom, prenom,#idMaison)

Professeur(idProfesseur,nom, prenom);

Matiere(idMatiere,intitule,#idProfesseur);

EleveSuitMatiere(#idEleve,#idMatiere)

Points(#idProfesseur,#idEleve,horaire,motif,nbPoints);

Attention : Si vous avez déjà utilisé la base de données HarryPotter dans le TP précédent, il n’est pas nécessaire de ré-installer la BDD. Vous pouvez directement lancer mySQL et lancer la commande use HarryPotter. Cependant, il vous faudra créer un nouveau script pour stocker les nouvelles instructions.

  1. Télécharger le script harryPotterLDD.sql
  2. Lancer mySQL
  3. Exécuter le script harryPotterLDD.sql
  4. Créer le script harryPotterLMDTD6.sql
  5. Ajouter les instructions au script harryPotterLMDTD6.sql permettant d’afficher :
    1. On veut obtenir le nom, le prénom et le nombre de points total obtenu par chaque étudiant, y compris ceux qui n’ont gagné aucun points. Trier le résultat.
    2. Quels sont les élèves dont le nombre de points remporté est supérieur au nombre de points moyen remporté ? Vous prendrez en compte dans la moyenne les points positifs et négatifs
    3. Donnez la liste des élèves de “Severus Rogue”. Dans le résultat, vous afficherez le nom et le prénom des élèves


Exo 3 : Mario Kart

Nous considérons le MLD suivant :

Personnage(id,nom, dateNaissance)

Circuit(id, nom, nbTour)

RéaliserUneCourse(#idPerso,#idCircuit,date, durée)

Voiture(id, nom, vitesse, poids,#idPerso)

  1. Télécharger le script marioKartLDD.sql
  2. Lancer mySQL
  3. Exécuter le script marioKartLDD.sql
  4. Créer le script marioKartLMD.sql
  5. Ajouter les instructions au script marioKartLMD.sql permettant d’afficher :
    1. Quels personnages ont leur nom commençant par un M ? Dans le résultat, vous affichez le nom, et la date de naissance.
    2. Quelles sont les voitures appartenant au personnage appelé “Mario” ? Dans le résultat, vous affichez le nom du personnage, le nom de la voiture et sa vitesse.
    3. Quel est le circuit qui a été réalisé le plus récemment ? Dans le résultat, vous affichez le nom du circuit, la date, et le nom du personnage ayant réalisé le circuit.
    4. Quelles sont les voitures dont le nom commence par la lettre B, M ou W ? Dans le résultat, vous affichez le nom des voitures.
    5. Quels sont les personnages qui n’ont pas de voiture dépassant le 5 km/h ? Dans le résultat, vous affichez le nom des personnages.
    6. Classez les personnages en fonction du nombre de courses réalisées (même les personnages qui n’ont réalisés aucunes courses). Dans le résultat, vous affichez le nom des personnages, leur nombre de courses.
    7. Quels sont les personnages qui ont réalisés tous les circuits ? Dans le résultat, vous affichez le nom des personnages.
    8. Quelles sont les voitures ayant un poids supérieur au poids moyen de toutes les voitures ? Dans le résultat, vous affichez le nom des voitures.