Corrections

Exercice 1 -(Requêtes de base)

Requêtes simples

  1. Que sait-on de Hulk ?
    SELECT * FROM personnage
    WHERE nom='Hulk';
  2. Quels sont les noms des personnage féminin ?
    SELECT nom
    FROM `personnage`
    WHERE sexe = 'F';
  3. Quelles sont les femmes qui n’ont pas de nom civil connu ?
    SELECT nom
    FROM `personnage`
    WHERE sexe = 'F' AND nom_civil IS NULL;
  4. Donner les noms des personnages féminins de l’équipe numéro 1.
    SELECT nom
    FROM `personnage`
    WHERE sexe = 'F' AND idequipe = 1;
  5. Afficher les noms des personnages des équipes 1 à 4 classés par numéro d’équipe et par sexe.
    SELECT nom FROM personnage 
    WHERE idequipe BETWEEN 1 AND 4
    ORDER BY idequipe, sexe;
  6. Afficher les noms des personnages de l’équipe 1 classés par ordre alphabétique décroissant.
    SELECT nom FROM personnage 
    WHERE idequipe="1"
    ORDER BY nom DESC;
  7. Donner les noms des personnages dont le nom termine par man.
    SELECT nom FROM personnage 
    WHERE LOWER(nom) LIKE '%man';
  8. Donner les noms des personnages qui ne sont pas d’alignement 1 ni 3.
    SELECT nom FROM personnage 
    WHERE NOT (idalignement=1 OR idalignement=3);
  9. Donner la liste des films classés par note décroissante.
    SELECT * FROM Films
    ORDER BY note DESC;

Exercice 2 – Expressions et fonctions

  1. Combien de personnages sont enregistrés ?
    SELECT COUNT(*) FROM personnage;
  2. Quelle est la plus grande puissance de personnage ?
    SELECT MAX(puissance) FROM personnage;
  3. Quelle est la plus petite puissance de personnage ?
    SELECT MIN(puissance) FROM personnage;
  4. Quelle est la puissance moyenne des personnages ?
    SELECT AVG(puissance) FROM personnage;
  5. Quelle est la puissance cumulée de tous les personnages ?
    SELECT SUM(puissance) FROM personnage;

Exercice 3 – Requêtes imbriquées

  1. Quel est le nom de l’équipe d’Hulk ?
    SELECT nom FROM equipes
    WHERE idequipe = (SELECT idequipe FROM personnage WHERE nom='Hulk');
  2. Quelle est la particularité de la race du personnage Ronan ?
    SELECT particularite FROM race
    WHERE idrace=(SELECT idrace FROM personnage WHERE nom='Ronan');
  3. Listes les noms des personnes qui ont un alignement mauvais par ordre alphabétique. (16 lignes)
    SELECT nom FROM personnage
    WHERE idalignement=(SELECT idalignement FROM alignement WHERE nom='Mauvais')
    ORDER BY nom;
  4. Qui sont les membres de l’équipe d’Hulk ? (13 lignes)
    SELECT nom FROM personnage
    WHERE idequipe=(SELECT idequipe FROM personnage WHERE nom='Hulk');
  5. Donner la liste des humains, classés par puissance. (45 lignes)
    SELECT nom FROM personnage
    WHERE idrace=(SELECT idrace FROM race WHERE nom='Humains')
    ORDER BY puissance;
  6. Dans quels films apparait Thor ? (7 lignes)
    SELECT titre FROM Films
    WHERE idFilms IN (SELECT idFilms FROM apparait_dans WHERE personnage_idpersonnage = (SELECT idpersonnage FROM personnage WHERE nom='Thor'));
  7. Donner la liste des personnages apparaissant dans le film Avengers ? (7 lignes)
    SELECT nom FROM personnage
    WHERE idpersonnage IN (SELECT personnage_idpersonnage FROM apparait_dans WHERE idFilms=(SELECT idFilms FROM Films WHERE titre='Avengers'));
  8. Quel sont les pouvoirs de Hulk ? (6 lignes)
    SELECT nom FROM pouvoirs
    WHERE idpouvoirs IN (SELECT idpouvoirs FROM pouvoir_possede WHERE idpersonnage=(SELECT idpersonnage FROM personnage WHERE nom='Hulk'));

Exercice 4 – Jointure

  1. Quels sont les membres de l’équipe de ‘The Avengers’ ? (13 lignes) (
    SELECT personnage.nom from personnage
    INNER JOIN equipes ON personnage.idequipe = equipes.idequipe
    WHERE equipes.nom='The Avengers';
  2. Noms et race des personnages ’mauvais’. (16 lignes)
    SELECT personnage.nom, race.nom, alignement.nom from personnage
    INNER JOIN race ON personnage.idrace = race.idrace
    INNER JOIN alignement ON personnage.idalignement = alignement.idalignement
    WHERE alignement.nom='Mauvais';
  3. Donner la listes des personnages étant des génies scientifiques ou intellectuels (8 lignes).
    SELECT DISTINCT personnage.nom from personnage
    INNER JOIN pouvoir_possede ON personnage.idpersonnage = pouvoir_possede.idpersonnage
    INNER JOIN pouvoirs ON pouvoir_possede.idpouvoirs = pouvoirs.idpouvoirs
    WHERE pouvoirs.nom = 'Génie intelectuel' OR pouvoirs.nom='Génie scientifique';
  4. Listes des pouvoirs des personnages de l’équipe ’The Avengers’ (8 lignes).
    SELECT DISTINCT pouvoirs.nom FROM pouvoirs
    INNER JOIN pouvoir_possede ON pouvoirs.idpouvoirs = pouvoir_possede.idpouvoirs
    INNER JOIN personnage ON personnage.idpersonnage = pouvoir_possede.idpersonnage
    INNER JOIN equipes ON equipes.idequipe = personnage.idequipe
    WHERE equipes.nom = 'The Avengers';

Exercice 4 – Agrégation

  1. Afficher pour chaque équipe, son nom et le nombre de membre. Les équipes sont triées par effectifs
    décroissants.
    SELECT equipes.nom, count(personnage.nom) FROM equipes
    INNER JOIN personnage ON personnage.idequipe = equipes.idequipe
    GROUP BY equipes.idequipe
    ORDER BY count(personnage.nom) DESC
  2. Donner pour chaque équipe, son nom et sa puissance moyenne, arrondi au dixième près.
    SELECT equipes.nom, ROUND(AVG(personnage.puissance),1) AS puissance FROM equipes
    INNER JOIN personnage ON personnage.idequipe = equipes.idequipe
    GROUP BY equipes.idequipe;

Exercice 4 – Corrélation

  1. Afficher les races qui comportent un personnage avec une puissance strictement supérieure à 150 (3 lignes)
    SELECT DISTINCT race.nom AS Race FROM race
    INNER JOIN personnage ON personnage.idrace = race.idrace
    WHERE personnage.nom IN (SELECT personnage.nom FROM personnage WHERE puissance > 150);

Exercice 5 – Mise à jour

  1. Dans la table personnage, mettez à jour le personnage ’Black Widow’ pour faire passer sa puissance à 50.
    UPDATE personnage
    SET personnage.puissance = 50
    WHERE personnage.nom = ‘Black Widow’;