====== Exercice - Requêtes SQL ======

<WRAP info>Exercice tiré du {{ .:sujet_0.pdf |sujet 0}} de l'épreuve terminale de NSI -- Exercice 4</WRAP>

L’énoncé de cet exercice utilise les mots du langage SQL suivants : ''SELECT'', ''FROM'', ''WHERE'', ''JOIN'', ''INSERT INTO'', ''VALUES'', ''COUNT'', ''ORDER BY''.

Dans un lycée imaginaire, les données relatives aux élèves de secondes sont regroupées dans un fichier nommé ''seconde_lyc.csv''. Un extrait de son contenu est ci-dessous.

<csv>
num_eleve,nom,prenom,datenaissance,langue1,langue2,option,classe
133310FE,ACHIR,Mussa,01/01/2005,anglais,espagnol,,2A
156929JJ,ALTMEYER,Yohan,05/05/2005,allemand,anglais,théâtre,2D
500633KH,BELEY,Thibaut,05/05/2005,anglais,espagnol,,2A
911887GA,BELEY,Marie,05/05/2005,anglais,espagnol,,2A
906089JJ,BELEY,Manon,10/01/2005,anglais,allemand,,2E
488697GA,CAILLE,Marie,30/03/2004,italien,anglais,,2D
193514FB,CHARPENTIER,Jules,26/12/2005,espagnol,anglais,,2C
321188FA,CLAUDEL,Benjamin,09/09/2005,espagnol,anglais,,2E
081282GF,EISEN,Carla,23/06/2004,anglais,allemand,,2A
026946KB,ELAYAR,Amir,11/09/2005,anglais,arabe,cinéma,2D
108303KG,GEHIN,Arthur,26/02/2005,allemand,anglais,,2D
057934BK,GROSJEAN,Alexandre,09/11/2005,anglais,espagnol,,2C
571113KE,HENRY,Paul,12/03/2005,allemand,anglais,,2E
488820DE,JAQUEY,Marc,13/11/2005,anglais,italien,,2D
024810CE,JULIANO,Alberto,21/04/2005,anglais,espagnol,,2C
249992EJ,KLEIBER,Gusti,20/02/2005,anglais,espagnol,cinéma,2E
422698AF,LACOUR,Julie,06/04/2005,italien,anglais,,2D
026454FA,LARBI,Nourdine,14/07/2005,espagnol,anglais,,2C
309341GD,LEFZA,Yasmina,26/11/2005,espagnol,anglais,,2E
076725HD,MARTIN,Victor,13/03/2005,anglais,espagnol,,2A
815183CB,NGUYEN,Ngong,16/03/2005,anglais,espagnol,,2D
094002FC,PELTIER,Romane,14/06/2005,allemand,anglais,,2D
321262HD,RENAULT,Zoé,06/08/2005,anglais,espagnol,latin,2E
075421AK,ROTH,Ursule,03/01/2005,anglais,allemand,,2A
121001CK,SERHANI,Sabrina,01/09/2005,italien,anglais,,2D
538965DJ,TLIDJANE,Yourk,31/01/2005,espagnol,anglais,,2D
389873GC,VIALET,Priscille,28/02/2005,espagnol,anglais,,2C
980306CA,WADE,Marcelin,03/05/2005,allemand,anglais,,2E
807158DH,WENGER,Alexandre,20/08/2005,allemand,anglais,,2A
666702FA,YAMAN,Elamine,23/04/2005,anglais,arabe,,2D
</csv>

Pour les besoins de l'organisation du lycée, le chef d’établissement exploite la base de données par des requêtes en langage SQL. Il a pour cela créé une table (ou relation) SQL dénommée ''seconde'' dans son système de gestion de bases de données dont la structure est la suivante :

^ seconde                       ^
| num_eleve (//clef primaire//) |
| langue1                       |
| langue2                       |
| option                        |
| classe                        |

L’attribut ''num_eleve'' est un entier, les autres sont des chaînes de caractère (le type ''CHAR'').

=== Question 1 ===

  - Dans le modèle relationnel, quel est l'intérêt de l'attribut ''num_eleve''.
  - Écrire une requête SQL d'insertion permettant d’enregistrer l'élève **ACHIR Mussa** dans la table seconde -- Les informations relatives à cet élève sont données dans la ligne 1 du fichier ''seconde_lyc.csv''.
  - Lors de l'insertion de l'élève **ALTMEYER Yohan** -- ligne 2 du fichier ''seconde_lyc.csv'' -- une erreur de saisie a été commise sur la première langue, qui devrait être allemand. Écrire une requête SQL de mise à jour corrigeant les données de cet élève.

=== Question 2 ===

On suppose maintenant que la table seconde contient les informations issues de l'extrait de fichier csv donné précédemment -- //Même si on a dit que ce n'était qu'un extrait, faire comme si c'était la totalité du fichier seconde_lyc.csv//.

  - Quel est le résultat de la requête ''SELECT num_eleve FROM seconde;'' ?
  - On rappelle qu'en SQL, la fonction d’agrégation ''COUNT()'' permet de compter le nombre d'enregistrements dans une table.\\ Quel est le résultat de la requête\\ ''SELECT COUNT(num_eleve) FROM seconde ;'' ?
  - Écrire la requête permettant de connaître le nombre d'élèves qui font allemand en //langue1// ou //langue2//.

=== Question 3 ===

Le chef d'établissement souhaite faire évoluer la structure de sa base de données. Pour ce faire, il créé une nouvelle table ''eleve'' dont la structure est la suivante :

^ eleve                                                             ^
| num_eleve (//clef primaire, clef étrangère de la table seconde//) |
| nom             |
| prenom          |
| datedenaissance |

Là encore, l'attribut ''num_eleve'' est un entier, les autres sont des chaînes de caractère (le type ''CHAR'').

  - Expliquer ce qu'apporte l’information clef étrangère pour l'attribue ''num_eleve'' de cette table en termes d'intégrité et de cohérence.
  - On suppose la table ''eleve'' correctement créée et complétée. Le chef d'établissement aimerait lister les élèves -- //nom, prénom, date de naissance// -- de la classe 2A.\\ Écrire la commande qui permet d'établir cette liste à l’aide d’une **jointure** entre ''eleve'' et ''seconde''.

=== Question 4 ===

Proposer la structure d'une table ''coordonnees'' dans laquelle on pourra indiquer, pour chaque élève, son adresse, son code postal, sa ville, son adresse mail. Préciser la clef primaire et/ou la clé étrangère en vue de la mise en relation avec les autres tables.