[[SQL Débutant]] # Atelier n°3 ## Avant de commencer Ne pas oublier que pour répondre aux demandes, il est recommandé de préparer ses requêtes. Pour cela, nous vous proposons l'outil ci-dessous. Nous l'utiliserons dans nos propositions de solutions ![[Preparer_sa_requete.pdf]] ## Vos premières requêtes sur la base de données *Films* ### 00- Lancer *Visual Studio Code*. Lancer une connexion avec *Visual Studio Code* . Cette connexion doit avoir *films* en base de données. >[!Remarque] Utiliser *Visual Studio Code*. >Si besoin, revoir comment utiliser ce logiciel : [[10_Atelier 0_installer- paramétrer et lancer Visual Studio Code]]. ### 00- Schéma de base *films* ![[Pasted image 20260309225928.png|750]] ### 13- ne pas perdre d'informations **Demande :** On nous demande la liste de tous les films avec leur pays d'origine et leur type de film. En plus du titre de film, du nom du pays qui apparaitra dans le champ *Pays d'origine* et du nom du type de film qui apparaitra dans le champ *Type de film*, nous voulons voir affichées l'année de sortie et la durée. Si le pays ou le type sont manquants on affichera *Inconnu* dans le champ concerné. Les films seront affichés du plus récent au plus ancien. >[!question] Aide : Si vous obtenez le résultat ci-dessous, votre `FROM` n'est pas bon ! > ![[Pasted image 20260316141526.png|750]] > Posez-vous alors la questions suivante : Que se passe-t-il si le pays ou le type est manquant ? >[!question]- Aide : Si vous ne voyez pas **comment afficher le terme *Inconnu***. > Nous pouvons vous dire que si le pays n'est pas connu (ou le type) alors le champ est NULL et si le champ est NULL alors on affichera "inconnu" sinon on affichera la valeur du champ. Vous aurez compris qu'il faut utiliser une fonction conditionnelle. Vous en avez déjà utilisé une. Vous pouvez l'utiliser ou alors en trouver une qui permettra d'aller plus vite. Vous trouverez la solution dans la page [[07_Fonctions utiles#Les Fonctions Conditionnelles]] > >[!tip]- Voir la solution > 1- Préparation de la requête > >[!flou] schéma > > ![[Pasted image 20260316152925.png|750]] > > 2- La requête > >[!flou] Requête SQL > >```SQL > >SELECT titre, > > ifnull(nom_pays, "Inconnu") AS "pays d'origine", > > ifnull(nom_type, "Inconnu") AS "type de film", > > annee_sortie, > > duree > >FROM t_film f LEFT JOIN t_type t ON f.id_type = t.id_type > > LEFT JOIN t_pays p ON f.id_pays = p.id_pays > >ORDER BY annee_sortie DESC; > >``` ### 14 - Faire des calculs **Demande :** Afficher la liste des acteurs ayant plus de 2 films dans la base de données. Nous voulons voir le nom et le prénom des acteurs dans un seul champ nommé *Acteur* ainsi que le nombre de films dans le champ *nbFilms*. >[!tip]- Voir la solution > 1- Préparation de la requête > >[!flou] schéma > > ![[Pasted image 20260316155610.png|750]] > > 2- La requête > >[!flou] Requête SQL > >```SQL > >SELECT CONCAT(prenom_act, " ", nom_act) AS Acteur, Count(id_film) AS "Nb Films" > >FROM T_Acteur a INNER JOIN T_Film_Acteur fa ON a.id_act = fa.id_act > >GROUP BY prenom_act, nom_act > >HAVING Count(id_film)>2; > >``` > > **Remarque :** Vous pouviez faire un `COUNT()` sur `*`, `a.id_act` ou encore `fa.id_act`. > > En effet, dans les 4 cas, vous comptez le nombre d'enregistrements des groupes ayant le même nom et le même prénom. > > Si vous vous n'arrivez pas à expliquer cette remarque, il est important de revoir le cours et, si besoin, de demander de l'aide à un enseignant ou un étudiant qui pourrait vous aider. > > > ### 15 - Rester devant son écran risque de ne pas suffire... **Demande :** Afficher les titres de films les plus longs. Le champ contenant les titres sera nommé _liste des titres de films les plus longs_ >[!question]- Aide : Si vous êtes bloqués. > 1- Que faut-il pour savoir que le titre est le plus long ? > >[!flou] réponse > >connaitre le nombre de caractères du titre de film et le comparer avec le nombre le plus grand. > > 2- Toutes les informations sont-elles présentes dans un champ de la base de données ? Si non, que manque-t-il et peut-on le calculer à l'aide d'une requête ? > > >[!flou] réponse > >Dans la base, il y a ni la ***longueur des titres de films*** ni ***la longueur la plus grande***. > >1- *longueur des titres de films* : comme nous avons les titres de films, il suffit d'utiliser la bonne fonction pour avoir la longueur. Donc je peux, dans une requête, avoir cette information pour chaque film et je pourrai comparait chaque valeur obtenue à *la longueur la plus grande* (à condition de savoir la calculer). > >2- *la longueur la plus grande* : Comme je viens de voir que je pouvais calculer le longueur de chaque titre, il me faudra prendre la plus grande (opération d'agrégat) > > Le point 1- me donne : > > ```mermaid > > graph TD > > A@{ shape: text, label: "T_film" }-->C["Requête 1"] > > B@{ shape: text, label: "Longueur de titre la plus grande" }-->C > > C-->D@{ shape: text, label: "Liste des titres de films les plus longs." } > > ``` > > Le point 2- me donne : > > ```mermaid > > graph TD > > A@{ shape: text, label: "T_film" }-->B["Requête 2"] > > B-->C@{ shape: text, label: "Longueur de titre la plus grande" } > > A-->E["Requête 1"] > > C-->E > > E-->F@{ shape: text, label: "Liste des titres de films les plus longs." } > > ``` >[!tip]- Voir la solution > 1- Préparation de la requête > >[!flou] schéma > > ```mermaid > > graph TD > > A@{ shape: text, label: "T_film" }-->B["Requête 2"] > > B-->C@{ shape: text, label: "Longueur de titre la plus grande" } > > A-->E["Requête 1"] > > C-->E > > E-->F@{ shape: text, label: "Liste des titres de films les plus longs." } > > ``` > > Préparation de la ***Requête 2*** : > > FROM : > > SELECT : MAX(char_length(titre)) AS LongueurLaPlusGrande > > > > Préparation de la ***requête 1*** : > > FROM   : > > WHERE  : char_length(titre) = LongueurLaPlusGrande > > SELECT : titre AS "liste des titres les plus longs" > > > > 2- La requête > >[!flou] Requête SQL > >```SQL > >SELECT titre AS "liste des titres les plus longs" > >FROM T_Film, > > ( > > SELECT Max(char_length(titre)) AS LongueurLaPlusGrande > > FROM T_Film > > ) Requete2 > >WHERE char_length(titre) = LongueurLaPlusGrande; > >``` > ### 16- On applique les mêmes méthodes **Demande :** Afficher la liste des films dont la durée est supérieure à la moyenne des films présents dans la base de données. Il faut afficher le titre des films ainsi que leurs durées du plus long au plus court. >[!tip]- Voir la solution > 1- Préparation de la requête > >[!flou] schéma > > ```mermaid > > graph TD > > A@{ shape: text, label: "T_film" }-->B["Requête 2"] > > B-->C@{ shape: text, label: "durée moyenne des films présents dans la BDD" } > > A-->E["Requête 1"] > > C-->E > > E-->F@{ shape: text, label: "liste des films dont la durée est supérieure à la moyenne des films" } > > ``` > > > > détails de chaque requête sélection : > > ![[Pasted image 20260316222908.png|750]] > > 2- La requête > >[!flou] Requête SQL > >```SQL > >SELECT titre, duree > >FROM T_Film, > > ( > > SELECT AVG(duree) AS dureeMoyenne > > FROM T_Film > > )R2 > >WHERE duree>dureeMoyenne > >ORDER BY duree DESC; > >``` ### 17- Trouver une de information pour trouver la solution **Demande :** Nous voulons connaitre les films provenant du même pays que le film *Match Point*. Vous afficherez les titres de films dans un champ nommé _Films du même pays que "Match Point"_ et dans un ordre décroissant. **Remarques :** - *Match Point* doit être utilisé comme critère. - Le film *Match Point* ne doit pas apparaitre dans la liste finale. >[!tip]- Voir la solution > 1- Préparation de la requête > >[!flou] schéma > > ```mermaid > > graph TD > > A@{ shape: text, label: "T_film" }-->B["Requête 2"] > > B-->C@{ shape: text, label: "Pays de *Match Point*" } > > A-->E["Requête 1"] > > C-->E > > E-->F@{ shape: text, label: "liste des Films du même pays que *Match Point*" } > > ``` > > Préparation de la ***Requête 2*** : > > FROM : > > WHERE : titre LIKE "Match Point" > > SELECT : id_pays AS lePays > > > > Préparation de la ***requête 1*** : > > FROM   : > > WHERE  : id_pays = lePays  AND titre not like"match point" > > ORDER BY : titre DESC > > SELECT : titre AS "titre AS "Films du même pays que Match Point" > > > > 2- La requête > >[!flou] Requête SQL > >```SQL > >SELECT titre AS "Films du même pays que 'Match Point'" > >FROM t_film, > > ( > > SELECT id_pays AS lePays > > FROM t_film > > WHERE titre LIKE "Match Point" > > ) R1 > >WHERE id_pays = lePays AND titre <> "Match Point" > >ORDER BY titre DESC; > >``` ### 18- Je ne peux pas trouver ce qui n'existe pas **Demande :** Nous voulons la liste des acteurs qui ont tourné avec le réalisateur _Enzo Barnoni_ mais pas avec le réalisateur _Giorgio Stegani_. Vous afficherez le nom et le prénom de chaque acteur dans un même champs nommé *Liste acteurs* et la liste apparaitra dans l'ordre alphabétique. **Remarque :** 1. Dans une base de données, vous ne pouvez trouver que ce qui existe. 2. Attention aux doublons >[!question]- Aide : Si vous êtes bloqués. > Je cherche qui a joué pour _Enzo Barnoni_, qui a joué pour _Giorgio Stegani_ et j'en conclus qui à joué pour l'un mais pas pour l'autre. >[!tip]- Voir la solution > 1- Préparation de la requête > >[!flou] schéma > > ```mermaid > > graph TD > > E@{ shape: text, label: "T_acteur" }-->B["Requête 3"] > > C@{ shape: text, label: "T_film_acteur" }-->B > > D@{ shape: text, label: "T_realisateur" }-->B > > A@{ shape: text, label: "T_film" }-->B > > B-->F@{ shape: text, label: "Liste des Acteurs > > de Enzo Barnoni." } > > A-->G["Requête 2"] > > C-->G > > D-->G > > G-->H@{ shape: text, label: "Liste des Acteurs > > de Giorgio Stegani."} > > F-->I["Requête 1"] > > H-->I > > I-->J@{shape: text, label: "Liste des acteurs > > de Enzo Barnoni > > mais pas de > > Giorgio Stegani."} > > style B fill:#EEEDFE,stroke:#534AB7,color:#26215C > > style G fill:#EEEDFE,stroke:#534AB7,color:#26215C > > style I fill:#EEEDFE,stroke:#534AB7,color:#26215C > > style F color:#0C447C > > style H color:#0C447C > > style J color:#633806 > > linkStyle 0,1,2,3 stroke:#0F6E56,stroke-width:1.5px > > linkStyle 4 stroke:#185FA5,stroke-width:2px > > linkStyle 5,6,7 stroke:#F742AF,stroke-width:1.5px > > linkStyle 8 stroke:#185FA5,stroke-width:2px > > linkStyle 9,10 stroke:#534AB7,stroke-width:2px > > linkStyle 11 stroke:#854F0B,stroke-width:2.5px > > ``` > > > > > > > > Préparation de la ***Requête 3*** : > >![[Pasted image 20260318232016.png]] > > > > Préparation de la ***requête 2*** : > > ![[Pasted image 20260318232333.png]] > > > > Préparation de la ***requête 1*** : > > ![[Pasted image 20260318232827.png]] > > > > > > 2- La requête > >[!flou] Requête SQL > >```SQL > >SELECT concat (nom_act, " ", prenom_act) AS "Liste Acteurs" > >FROM ( > > SELECT a.id_act, prenom_act, nom_act > > FROM T_Realisateur r INNER JOIN T_Film f ON r.id_real = f.id_real > > INNER JOIN T_Film_Acteur fa ON f.id_film = fa.id_film > > INNER JOIN T_Acteur a ON fa.id_act = a.id_act > > WHERE nom_real="Barnoni" AND prenom_real="Enzo" > > GROUP BY a.id_act, prenom_act, nom_act > > ) R3 > > LEFT JOIN > > ( > > SELECT id_act > > FROM T_Realisateur r INNER JOIN T_Film f ON r.id_real = f.id_real > > INNER JOIN T_Film_Acteur fa ON f.id_film = fa.id_film > > WHERE nom_real="Stegani" AND prenom_real="Giorgio" > > ) R2 > > ON R3.id_act = R2.id_act > > WHERE R2.id_act Is Null; > >``` ### 19 - Avant de compter, faut compter. **Demande :** Nous voulons connaitre le nombre de films sortis entre 1968 et 2000 et ayant plus de 2 acteurs. La valeur attendue apparaitra dans un champ nommé *Nombre de films entre 68 et 2000 avec plus de 2 acteurs*. **Remarque :** A ce stade, vous avez tous les outils pour y arriver. >[!tip]- Voir la solution > 1- Préparation de la requête > >[!flou] schéma > > ```mermaid > > graph TD > > A@{ shape: text, label: "T_film" }-->B["Requête 2"] > > C@{ shape: text, label: "T_film_acteur" }-->B > > B-->D@{ shape: text, label: "Liste de films sortis entre 1968 et 2000 > > et ayant plus de 2 acteurs" } > > D-->E["Requête 1"] > > E-->F@{shape: text, label: "Nombre de films sortis entre 1968 et 2000 > > et ayant plus de 2 acteurs"} > > style D color:#0C447C > > style F color:#633806 > > ``` > > > > Préparation de la ***requête 2*** : > > ![[Pasted image 20260319110322.png|750]] > > > > Préparation de la ***requête 1*** : > > ![[Pasted image 20260319110420.png|750]] > > > > > > 2- La requête > >[!flou] Requête SQL > >```SQL > >SELECT Count(id_film) AS "Nombre de films entre 68 et 2000 avec plus de 2 acteurs" > >FROM ( > > SELECT f.id_film > > FROM T_Film f INNER JOIN T_Film_Acteur fa ON f.id_film = fa.id_film > > WHERE annee_sortie Between 1968 And 2000 > > GROUP BY id_film > > HAVING Count(id_act)>2 > > )R2; > >``` ### 20 - Attention, piège en vue ! **Demande :** Nous voulons la liste des films ayant moins de 3 acteurs et sortis entre 1968 et 2000. Pour chaque film affiché, nous voulons le titre, le résumé, le réalisateur (dans un seul champ nommé *Realisateur*), le nom du pays, le nom du type et le nombre d'acteurs. >[!tip]- Voir la solution > 1- Préparation de la requête > >[!flou] schéma > >![[Pasted image 20260319131625.png]] > >**Remarque :** Si vous ne faites pas la dissymétrie, > >- entre *t_film* et *t_film_acteur* : il vous manquera les films sans acteurs. > >- entre *t_film* et *t_pays* : les films dont on ne connait pas le pays disparaitront > >- entre *t_film* et *t_type* : les films dont on ne connait pas le type disparaitront > >- > > 2- La requête > >[!flou] Requête SQL > >```SQL > >SELECT titre, > > resume, > > concat(nom_real," ", prenom_real) AS Realisateur, > > nom_Pays, > > nom_type, > > count(id_act) AS nombreActeurs > >FROM t_film f LEFT JOIN t_pays p ON f.id_pays = p.id_pays > > LEFT JOIN t_realisateur r ON f.id_real = r.id_real > > LEFT JOIN t_type t ON f.id_type = t.id_type > > LEFT JOIN t_film_acteur fa ON f.id_film = fa.id_film > >WHERE annee_sortie BETWEEN 1968 AND 2000 > >GROUP BY titre, resume, nom_real, prenom_real, nom_pays, nom_type > >HAVING count(id_act)<3; > >``` > > > >A l'exécution de cette requête, nous pouvons voir des valeurs `NULL` dans différents champs. Si c'est une requête finale (qui ne sera pas reprise dans une autre requête) il est possible d'améliorer la visibilité en faisant disparaitre cette valeur. > >Voici une proposition de requête : > >```SQL > >SELECT titre, > > ifnull(resume, "") AS resume, > > concat(nom_real," ", prenom_real) AS Realisateur, > > ifnull(nom_Pays,"") AS pays, > > ifnull(nom_type, "") AS type, > > count(id_act) AS nombreActeurs > >FROM t_film f LEFT JOIN t_pays p ON f.id_pays = p.id_pays > > LEFT JOIN t_realisateur r ON f.id_real = r.id_real > > LEFT JOIN t_type t ON f.id_type = t.id_type > > LEFT JOIN t_film_acteur fa ON f.id_film = fa.id_film > >WHERE annee_sortie BETWEEN 1968 AND 2000 > >GROUP BY titre, resume, nom_real, prenom_real, nom_pays, nom_type > >HAVING count(id_act)<3; > >``` > > ### 21 - Vous l'avez déjà fait **demande :** Dans leur jeunesse, vos enseignants ont vu beaucoup de films avec *Bud Spencer* et *Terence Hill*. Mais ils veulent savoir si des réalisateurs ont fait tourner *Bud Spencer* sans *Terence Hill*. La requête affichera uniquement le nom et le prénom des réalisateurs dans un champ nommé *Realisateur*. **Remarque :** Attention aux doublons ! >[!tip]- Voir la solution > 1- Préparation de la requête > C'est exactement le même fonctionnement que la demande 18 : [[13_Atelier 3 - vos premières requêtes#18- Je ne peux pas trouver ce qui n'existe pas]] > > 2- La requête > >[!flou] requête SQL > >```SQL > >SELECT concat(nom_real, " ", prenom_real) AS Realisateur > >FROM ( > > SELECT r.id_real, nom_real, prenom_real > > FROM T_Realisateur r INNER JOIN T_Film f ON r.id_real = f.id_real > > INNER JOIN T_Film_Acteur fa ON f.id_film = fa.id_film > > INNER JOIN T_Acteur a ON fa.id_act = a.id_act > > WHERE prenom_act= "Bud" AND nom_act= "Spencer" > > GROUP BY id_real, nom_real, prenom_real > > )R3 > > LEFT JOIN > > ( > > SELECT id_real > > FROM T_Acteur a INNER JOIN T_Film_Acteur fa ON a.id_act = fa.id_act > > INNER JOIN T_Film f ON fa.id_film = f.id_film > > WHERE prenom_act="Terence" AND nom_act="Hill" > > )R2 > > ON R3.id_real = R2.id_real > >WHERE R2.id_real Is Null; > >```