TD5 - Le Select contre attaque

Inès de Courchelle

Durée : 1h30

Consignes :

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

Objectifs :

  • Utiliser des requêtes imbiquées
  • Réaliser les premières jointures
  • Réfléchir 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 - VRAI ou FAUX

Nous considérons le MCD suivant :

drawing

Ce qui correspond au MLD suivant :

Joueur(idJoueur, nom,#idQuete)

Quete(idQuete, appelation)

Demarrer(#idEtage, #idQuete,date)

Etage(idEtage,appelation,#idEmplacement);

Emplacement(idEmplacement,longitude,latitude);

Gemme(idGemme, nom,taille)

Apparaitre(#idGemme, #idEmplacement,date)

JoueurPosseder(#idJoueur,#idGemme)

VRAI ou FAUX :

Dire si les requêtes suivantes sont vraies ou fausses. Dans le cas où la requête est fausse, justifier et corriger la, afin d’obtenir le bon résultat.

  1. Afficher les gemmes et les joueurs dont le nom dépassent 7 caractères
SELECT nom
FROM Joueur,Gemme
WHERE LENGTH(nom) > 7
  1. Compter pour chaque gemme le nombre de fois où elle est apparue
SELECT idGemme, count(idGemme) as total
FROM Apparaitre
group by idGemme
  1. Selectionner toutes les quêtes qui n’ont jamais démarré
SELECT idQuete
FROM Quete
Minus
SELECT idQuete
FROM Demarrer
  1. Selectionner toutes les quêtes qui ont démarré.
SELECT idQuete
FROM Quete
intersect
SELECT idQuete
FROM Demarrer
  1. Afficher les joueurs qui ont ramassé au moins 6 Gemmes
SELECT idJoueur
FROM Gemme
WHERE count(idJoueur) > 6

Exo 2 - 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 zeldaLMDTD5.sql
  5. Ajouter les instructions au script zeldaLMDTD5.sql permettant d’afficher :
    1. Quel est le nom du monde pour la créature divine “Vah Medoh” ?
    2. Quelle est la moyenne de plat consommé par joueur ?
    3. Quels sont les joueurs qui ont mangé plus de 3 plats ?

Exo 3 - 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 harryPotterLMDTD5.sql
  5. Ajouter les instructions au script harryPotterLMDTD5.sql permettant d’afficher :
    1. Quels sont les élèves qui appartiennent à la maison “Gryffondor” ? Dans le résultat, affichez le nom, et le prénom de l’élève.
    2. Quelle matière enseigne le professeur dont le prénom est “Minerva” et son nom est “McGonagall” ?
    3. Combien d’élèves n’ont jamais obtenu ou perdu de points pour leur maison ? Afficher dans le résultat le nom et le prénom de l’élève.
    4. Quels sont les élèves qui suivent toutes les matières ?

Exo 4 - Euro 2022

Equipe(idEquipe, Pays, classementFifa)

Joueuse(idjoueuse, nom, prenom, dateDeNaissance,positionnement, #idEquipe)

Stade(idStade,nomStade, ville, capacite);

Rencontre(idRencontre,dateM,phase,scoreDomicile,scoreExterieur,#idEquipeDomicile,#idEquipeExterieure,#idStade);

  1. Télécharger le script euro2022LDD.sql
  2. Lancer mySQL
  3. Exécuter le script euro2022LDD.sql
  4. Créer le script euro2022LMD.sql
  5. Ajouter les instructions au script euro2022LMD.sql permettant d’afficher :
    1. Afficher toutes les joueuses jouant pour le danemark ?
    2. Afficher toutes les joueuses jouant pour la norvège ?
    3. Quelle équipe a joué la finale en tant qu’équipe à Domicile ? Dans le résultat de la requête, vous afficherez le nom du pays et le score à domicile.
    4. Quelle équipe a joué la finale en tant qu’équipe à l’Extérieur ? Dans le résultat de la requête, vous afficherez le nom du pays et le score à l’extérieur.
    5. Combien de but a marqué l’angleterre ?
    6. Quelle équipe à le plus de joueuses ?
    7. Compter le nombre de fois où chaque stade a été utilisé.
    8. Quel stade a été le plus utilisé ?
    9. Compter le nombre de fois que chaque équipe a joué à l’extérieur ?
    10. Quelle équipe à jouer le plus en tant qu’équipe “extérieur” ?