Devoir SQL

Durée: 55 minutes

L'usage de la calculatrice n'est pas autorisé

Structure

1. Compléter le diagramme suivant en soulignant les clés primaires et en plaçant un # devant les clés étrangères.

   

   Correction

   

2. Donner le schéma relationnel de cette base de données.

   Correction

   ecole(nom text,ville text)

   admission(#nom text,#concours_id text,places int,dernier_admis int)

   concours(id int, nom text, filiere text, inscrit int)

   epreuve(id int, nom text, duree float, coefficient float, oral bool,#concours_id text)

   epreuve_matiere(#epreuve_id int,matiere text)

Requêtes d'interrogation - LVL 1

Écrire en sql les requêtes qui permettent de trouver les informations suivantes:

1. Les noms des concours dans la base de données.

   Correction

   SELECT nom
   FROM concours;
   

2. Les noms des épreuves dont les coefficients sont supérieurs à 10.

   Correction

   SELECT nom
   FROM epreuve
   WHERE coefficient>=10;
   

3. Les noms des écoles qui proposent un concours dans l'ordre alphabétique.

   Correction

   SELECT ecole_nom
   FROM admission
   ORDER BY ecole_nom ASC;
   

Requêtes d'interrogation - LVL 2

Écrire en sql les requêtes qui permettent de trouver les informations suivantes:

1. Les épreuves écrites de cmp (identifiant du Concours Mines-Ponts) : filière, matière, durée et coefficient.

   Correction

   SELECT concours.filiere, epreuve_matiere.matiere, epreuve.duree, epreuve.coefficient
   FROM epreuve
   JOIN epreuve_matiere ON epreuve.id=epreuve_matiere.epreuve_id
   JOIN concours ON epreuve.concours_id=concours.id
   WHERE concours.nom='cmp' and oral='False';
   

2. Le coefficient total des mathématiques du concours ccinp.

   Correction

   SELECT sum(coefficient)
   FROM epreuve
   JOIN epreuve_matiere ON epreuve.id=epreuve_matiere.epreuve.id
   JOIN concours ON epreuve.concours_id=concours.id
   WHERE matiere='mathematiques' and concours.nom='ccinp';
   

3. Pour chaque concours, afficher le nom, la filière, le nombre d'épreuves écrites et leur durée totale.

   Correction

   SELECT concours.nom, concours.filiere, count(*), sum(duree)
   FROM epreuve
   JOIN concours ON concours.id=epreuve.concours_id
   WHERE oral='False'
   GROUP BY epreuve.concours_id;
   

Requêtes de modification

Écrire en sql des requêtes afin de modifier la base de données comme suit:

1. L'ESCP a quitté Paris et a déménagé dans ses nouveaux locaux de Strasbourg.

   Correction

   UPDATE ecole
   SET ville = 'Strasbourg'
   WHERE nom='ESCP';
   

2. Une nouvelle école vient d'ouvrir dans la ville de Saclay, elle s'appelle École Supérieure des Sciences Numériques.

   Correction

   INSERT INTO ecole 
   VALUES ('ESSN','Saclay') 
   

3. L'école supérieure EIDG ferme ses portes.

   Correction

   Attention, on doit commencer par supprimer toute référence à l'école dans la relation admission.

   DELETE FROM admission 
   WHERE ecole_nom='EIDG';
   
   DELETE FROM ecole 
   WHERE nom='EIDG';