GoupillWiki

Outils pour utilisateurs

Outils du site

Piste : • sql_requests
nsi:terminales:sql_requests

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Les deux révisions précédentesRévision précédente
Prochaine révision		Révision précédente
nsi:terminales:sql_requests [2021/08/19 15:19] – goupillwikinsi:terminales:sql_requests [2022/08/29 21:45] (Version actuelle) – goupillwiki
Ligne 15: Ligne 15:
 Afin d'illustrer notre travail, on utilisera un exemple. Afin d'illustrer notre travail, on utilisera un exemple.
  
-{{ :nsi:terminales:sql-1.png?direct&400 |}}+{{ nsi:terminales:database:sql-1.png?direct&400 |}}
  
 Pour cette base, nous besoin de deux tables FILM et ARTISTE et aussi d'une table JOUEDANS pour la relation du haut : Pour cette base, nous besoin de deux tables FILM et ARTISTE et aussi d'une table JOUEDANS pour la relation du haut :
Ligne 104: Ligne 104:
 Dans le texte de la biographie, vous pouvez voir la présence de caractères ''%%'%%'', mais ces caractères servent à délimiter les chaînes de texte... La convention est de doubler les ''%%'%%'' pour qu'ils soient compris comme une simple apostrophe. C'est pourquoi vous pouvez voir des ''%%''%%'' ici et là dans la biographie. Dans le texte de la biographie, vous pouvez voir la présence de caractères ''%%'%%'', mais ces caractères servent à délimiter les chaînes de texte... La convention est de doubler les ''%%'%%'' pour qu'ils soient compris comme une simple apostrophe. C'est pourquoi vous pouvez voir des ''%%''%%'' ici et là dans la biographie.
  
-==== On ne précise pas la clé primaire ====+<WRAP tip> 
 +==== Préciser ou non la clé primaire ? ====
  
-''idArtiste'' est la clé primaire, elle se crée toute seule, nous n'avons pas besoin de la préciser.+On a le choix : on peut décider de préciser un ''idArtiste''. Dans ce cas on pourra écrire 
 + 
 +<code sql> 
 +INSERT INTO ARTISTE(idArtiste, prénom, nom, biographie, naissance) VALUES(129, 'Jo', 'Bill', 'blablabla', '1955-03-19'); 
 +</code> 
 + 
 +et on précise l'identifiant voulu **à condition qu'il ne soit pas déjà utilisé**. 
 + 
 +Puisque dans la requête précédente figurent toutes les colonnes de la table, on peut se contenter de : 
 + 
 +<code sql> 
 +INSERT INTO ARTISTE VALUES(129, 'Jo', 'Bill', 'blablabla', '1955-03-19'); 
 +</code> 
 + 
 +Dans les sujets de bac, l'identifiant est précisé en général. Mais dans un cas d'utilisation réelle, il sera sans doute plus pratique de laisser le SGBD automatiquement identifiant : sans cela il faudrait d'abord consulter la BDD pour savoir quel identifiant est disponible... 
 +</WRAP>
  
 ==== Erreur si insertion de film sans idReal ==== ==== Erreur si insertion de film sans idReal ====
Ligne 210: Ligne 226:
 <WRAP box>Une base est construite par des milliers d'insertions individuelles. On ne va donc pas le faire et nous allons continuer à travailler avec une base déjà faite. <WRAP box>Une base est construite par des milliers d'insertions individuelles. On ne va donc pas le faire et nous allons continuer à travailler avec une base déjà faite.
  
-Vous disposez du fichier {{ :nsi:terminales:films.sql |}} qui contient toutes les commandes pour créer une base de donnée. Dans //sqlitebrowser// vous pouvez importer un fichier ''*.sql''. <wrap tip>Fichier -> Importer -> Base de données à partir du fichier SQL...</wrap>+Vous disposez du fichier {{ nsi:terminales:database:films.sql |}} qui contient toutes les commandes pour créer une base de donnée. Dans //sqlitebrowser// vous pouvez importer un fichier ''*.sql''. <wrap tip>Fichier -> Importer -> Base de données à partir du fichier SQL...</wrap> 
 +</WRAP>
  
 ==== Sélection ==== ==== Sélection ====
Ligne 259: Ligne 276:
 === tri === === tri ===
  
-Et on peut trier -- tri par naissance puis nom dans le premier cas, ordre descendant dans le second cas.+Et on peut trier : 
 +  * tri par naissance puis nom dans le premier cas, 
 +  * ordre descendant dans le second cas -- mot clé ''DESC''.
  
 <code sql linenums> <code sql linenums>
Ligne 266: Ligne 285:
 </code> </code>
  
 +<wrap important>Dans le cas du requête avec ''WHERE'' et ''ORDER BY'', il faut placer ''ORDER BY'' après.</wrap>
 +
 +=== compte ===
 +
 +On peut compter les items. Par exemple la requête suivante renvoie une liste de noms avec éventuellement des doublons :
 +
 +<code sql>
 +SELECT nom from ARTISTE;
 +</code>
 +
 +Si nous n'étions intéressé que par le nombre de lignes de cette liste -- ce qui revient à compter le nombre d'artistes -- on peut utiliser le mot clef ''COUNT'' :
 +
 +<code sql>
 +SELECT COUNT(nom) from ARTISTE;
 +</code>
 +
 +Cette requête est différente puisqu'elle ne renvoie pas une liste de valeurs, elle ne renvoie que le résultat du compte.
 +
 +<WRAP tip>On compte tous les noms apparaissant, même s'ils viennent en plusieurs exemplaires. La fonction ''COUNT'' ignorera les cas ''NULL'', c'est à dire les cases vides (en admettant que l'on ait autorisé qu'un artiste n'ait pas de nom renseigné).</WRAP>
 +
 +<WRAP info>Pour aller plus loin, on pourrait préciser que l'on ne veut pas compter plusieurs fois un même nom. On ajoute alors le mot-clé ''DISCTINCT'' comme ceci : ''SELECT COUNT(DISTINCT nom) from ARTISTE''.
 +
 +Plus loin encore : on pourrait produire une liste avec chaque nom associé avec le nombre d'artiste portant ce nom : ''SELECT nom, COUNT(idArtiste) FROM ARTISTE GROUP BY nom''.  Le mot-clé ''GROUP BY'' indique que l'on veut regrouper tous les artistes de même nom et la fonction ''COUNT'' se charge de compter les différents artistes groupés sous ce nom.</WRAP>
 ==== Jointure ==== ==== Jointure ====
  
Ligne 294: Ligne 336:
 <wrap important>ORDER BY doit être après WHERE</wrap> <wrap important>ORDER BY doit être après WHERE</wrap>
  
-===== Des requêtes de sélection pour s'exercer ===== +<WRAP tip>**Autre façon de faire :** Si on écrit
- +
-On utilise la base précédente. +
- +
-==== Applications directes, sans jointure ==== +
- +
-  - Lister tous les noms, prénoms, naissances d'artistes nés avant 1970 +
-  - Lister tous les titreVO de films dont le réalisateur à l'idArtiste 15 +
-  - Lister tous les rôles joués par l'artiste d'idArtiste 1 +
- +
-==== Avec une jointure ==== +
- +
-  - Lister tous les titreVO, année des films de Tarantino +
-  - Lister tous les rôles de Brad Pitt +
-  - (A) Lister tous les titreVO, année des films dans lequel a joué l'artiste avec l'idArtiste 1 +
-  - Lister les noms et prénoms de tous les réalisateurs -- //c'est à dire des artistes qui sont réalisateurs d'un film// +
- +
-==== Avec deux jointures ==== +
- +
-Pour lier un artiste à un film en tant qu'acteur, il faut passer par JOUEDANS ce qui suppose deux jointures sous la forme +
 <code sql linenums> <code sql linenums>
-... FROM JOUEDANS +SELECT * FROM FILM, ARTISTE;
-  JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-  JOIN ARTISTE ON ARTISTE.idArtiste = JOUEDANS.idArtiste+
 </code> </code>
- +On obtient tous les couples ''(FILM, ARTISTE)'' même s'ils n'ont rien à voir l'un avec l'autre. Si on ne veut que les paires où l'artiste est le réalisateur du film, on peut préciser :
-  - (B) Trouver tous les titreVO, année des films dans lesquels joue un artiste nommé Willis +
-  - Trouver tous les titreVO, année des films dont un acteur est né après 1970 +
- +
-<WRAP tip>La requête (B) correspond à la requête (A). mais nécessite une jointure supplémentaire. En situation réelle, on sera plutôt dans la situation du (A) car on connaîtra l'id de Bruce Willis</WRAP> +
- +
-==== Avec trois jointures... ==== +
- +
-Ça commence à devenir plus compliqué et on ne vous demande pas d'aller si loin. +
- +
-Si pour une paire acteur -- film, on veut en plus avoir le réalisateur du film, il faut en plus joindre FILM à ARTISTE selon la clé idRéal. Cette fois c'est plus difficile car ceci ne fonctionne pas : +
 <code sql linenums> <code sql linenums>
-... FROM JOUEDANS +SELECT * FROM FILM, ARTISTE WHERE FILM.idReal = ARTISTE.idArtiste;
-  JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-  JOIN ARTISTE ON ARTISTE.idArtiste = JOUEDANS.idArtiste +
-  JOIN ARTISTE ON ARTISTE.idArtiste = FILM.idReal+
 </code> </code>
 +Ce qui revient à peu près au même que la  jointure.
  
-Car ARTISTE apparaît deux fois pour des raisons différentes. Il est possible de se sortir d'embarras en donnant un alias à chaque occurrence de ARTISTE. Par exemple on utilisera ''acteur'' pour la première apparition de ARTISTE et ''réalisateur'' pour la deuxième : +//Dans tous les cas, inutile de vous remplir la tête de code SQL. On souhaite surtout que vous sachiez écrire des requêtes simples et que vous arriviez à comprendre. La requête précédente, même si elle n'est pas facile de la trouver tout seul, ne devrait pas être trop difficile à comprendre.// 
- +</WRAP>
-<code sql linenums> +
-... FROM JOUEDANS +
-  JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-  JOIN ARTISTE acteur ON acteur.idArtiste = JOUEDANS.idArtiste +
-  JOIN ARTISTE réalisateur ON réalisateur.idArtiste = FILM.idReal +
-</code> +
- +
-L'ajout de ''acteur'' et ''réalisateur'' juste à côté de ARTISTE suffit. +
- +
-Supposons que l'on veuille visualiser le nom d'acteur et le nom du réalisateur, le titreVO. On ne peut pas se contenter de +
- +
-<code sql linenums> +
-SELECT nom, nom, titreVO FROM JOUEDANS +
-  JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-  JOIN ARTISTE acteur ON acteur.idArtiste = JOUEDANS.idArtiste +
-  JOIN ARTISTE réalisateur ON réalisateur.idArtiste = FILM.idReal +
-</code> +
- +
-car les deux occurrences de ''nom'' sont ambiguës : est-ce nom pour la première occurrence de ARTISTE (acteur) ou la deuxième (réalisateur) ? Il faudra préciser : +
- +
-<code sql linenums> +
-SELECT acteur.nom, réalisateur.nom, titreVO FROM JOUEDANS +
-  JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-  JOIN ARTISTE acteur ON acteur.idArtiste = JOUEDANS.idArtiste +
-  JOIN ARTISTE réalisateur ON réalisateur.idArtiste = FILM.idReal +
-</code> +
- +
-  - Trouver tous les noms et prénoms, rôle et titreVO pour des acteurs jouant dans un film de Tarantino +
-  - Trouver tous les noms, prénoms de réalisateurs d'un film dont dont un des acteurs est né après 1970 +
- +
-===== Les réponses +
- +
-1. Lister tous les noms, prénoms, naissances d'artistes nés avant 1970 +
-   +
-   <code sql> +
-   SELECT nom, prénom, naissance FROM ARTISTE WHERE naissance < '1970-01-01'; +
-   </code> +
- +
-2. Lister tous les films dont le réalisateur à l'idArtiste 15 +
- +
-   <code sql> +
-   SELECT titreVO FROM FILM WHERE idReal = 15; +
-   </code> +
- +
-3. Lister tous les rôles joués par l'artiste d'idArtiste 1 +
- +
-   <code sql> +
-   SELECT rôle FROM JOUEDANS WHERE idArtiste = 1; +
-   </code> +
- +
-4. Lister tous les titreVO, année des films de Tarantino +
- +
-   <code sql> +
-   SELECT titreVO, année FROM ARTISTE +
-     JOIN FILM ON FILM.idReal = ARTISTE.idArtiste +
-     WHERE nom = 'Tarantino'; +
-   </code> +
- +
-5. Lister tous les rôles de Brad Pitt +
- +
-   <code sql> +
-   SELECT rôle, année FROM JOUEDANS +
-     JOIN ARTISTE ON JOUEDANS.idArtiste = ARTISTE.idArtiste +
-     WHERE nom = 'Pitt' AND prénom = 'Brad'; +
-   </code> +
- +
-6. Lister tous les titreVO, année des films dans lequel a joué l'artiste avec l'idArtiste 1 +
- +
-   <code sql> +
-   SELECT titreVO, année FROM FILM +
-     JOIN JOUEDANS ON FILM.idFilm = JOUEDANS.idFilm +
-     WHERE idArtiste = 1; +
-   </code> +
- +
-7. Lister les noms et prénoms de tous les réalisateurs (*c'est à dire des artistes qui sont réalisateurd d'un film*) +
- +
-   <code sql> +
-   SELECT DISTINCT nom, prénom FROM ARTISTE +
-     JOIN FILM ON FILM.idReal = ARTISTE.idArtiste; +
-   </code> +
-    +
-   *Seuls apparaîtront les artistes pour lesquels la jointure est possible* +
- +
-8. Trouver tous les titreVO, année des films dans lesquels joue un artiste nommé Willis +
- +
-   <code sql> +
-   SELECT titreVO, année FROM JOUEDANS +
-     JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-     JOIN ARTISTE ON ARTISTE.idArtiste = JOUEDANS.idArtiste +
-     WHERE nom = 'Willis' AND prénom = 'Bruce'; +
-   </code> +
- +
-9. Trouver tous les titreVO, année des films dont un acteur est né après 1970  +
- +
-   <code sql> +
-   SELECT titreVO, année FROM JOUEDANS +
-     JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-     JOIN ARTISTE ON ARTISTE.idArtiste = JOUEDANS.idArtiste +
-     WHERE naissance > '1970-01-01'; +
-   </code> +
- +
-10. Trouver tous les noms et prénoms, rôle et titreVO pour des acteurs jouant dans un film de Tarantino +
- +
-    <code sql> +
-    SELECT acteur.nom, acteur.prénom, rôle, titreVO FROM JOUEDANS +
-      JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm +
-      JOIN ARTISTE acteur ON acteur.idArtiste = JOUEDANS.idArtiste +
-      JOIN ARTISTE réalisateur ON réalisateur.idArtiste = FILM.idReal +
-      WHERE réalisateur.nom = 'Tarantino'; +
-    </code> +
- +
-11. Trouver tous les noms, prénoms de réalisateurs d'un film dont dont un des acteurs est né après 1970+
  
-    <code sql> 
-    SELECT DISTINCT réalisateur.nom, réalisateur.prénom FROM JOUEDANS 
-      JOIN FILM ON FILM.idFilm = JOUEDANS.idFilm 
-      JOIN ARTISTE acteur ON acteur.idArtiste = JOUEDANS.idArtiste 
-      JOIN ARTISTE réalisateur ON réalisateur.idArtiste = FILM.idReal 
-      WHERE acteur.naissance > '1970-01-01'; 
-    </code> 
  
nsi/terminales/sql_requests.1629379175.txt.gz · Dernière modification : de goupillwiki