Exercice 2 · Bac Numérique et Sciences Informatiques Asie 2023 (jour 2)

Exercice 2 (3 points)

Cet exercice porte sur les bases de données relationnelles et le langage SQL.

Dans cet exercice, on pourra utiliser les mots clés suivants du langage SQL: SELECT, FROM, WHERE, JOIN...ON, INSERT INTO...VALUES..., UPDATE...SET, ORDER BY. La clause ORDER BY suivie d'un attribut permet de trier les résultats par ordre croissant des valeurs de l'attribut.

On veut gérer les résultats d'un groupe d'élèves au baccalauréat à l'aide d'une base de données relationnelle en langage SQL. Pour cela, on a créé deux relations: une contenant les renseignements concernant les élèves et l'autre des indications sur les établissements.

Les figures 1 et 2 donnent les commandes ayant été exécutées pour créer ces deux relations: candidats et etablissements.

Les figures 3 et 4 ci-dessous donnent les contenus de ces relations:

Figure 3: Contenu de la relation candidats

identifiant · prenom · nom · email · telephone · moyenne · id_etab

1 · Kevin · APPA · kevin.appa@gmail.com · 0102030405 · 12.2 · 1

2 · Paul · TARDI · paul.tardi@gmail.com · 1539457825 · 11.5 · 3

3 · Pierre · PASTA · pierre.pasta@gmail.com · 1535687725 · 11.5 · 3

4 · Claire · TOU · claire.tou@gmail.com · 1535712825 · 9.5 · 1

5 · Lydie · HECTOR · lydie.hector@gmail.com · 9895712825 · 18.5 · 2

6 · Mathis · PARIS · mathis.paris@gmail.com · 9895719999 · 19.5 · 1

7 · Roman · LAMI · roman.lami@gmail.com · 9894758999 · 19.5 · 2

Figure 4: Contenu de la relation etablissements

id_etab · etablissement · adresse · mail · proviseur

1 · Vinci · 2 rue de la gare trelouper · vinci@rectorat.com · Gerard LECHEF

2 · Acamus · 3 rue du bord de l'eau saturne 8066 · acamus@rectorat.com · Camille PREMIERE

3 · Gbrassens · avenue de Paris 60555 Beaupre · gbrassens@rectorat.com · Philippe STAR

1.

a.

Représenter par un schéma relationnel les relations candidats et etablissements en précisant les types pour les attributs.

b.

Indiquer, en justifiant, quelle est la clé étrangère de la relation candidats.

c.

Écrire une requête permettant d'ajouter le candidat présentant les renseignements suivants:

identifiant: 8

email: jacques.haddy@gmail.com

moyenne: 9.75

nom: HADDY

prenom: Jacques

telephone: 98 76 54 32 10

etablissement: Acamus

2.

a.

Écrire ce qu'affiche la requête suivante appliquée aux relations précédentes:

SELECT prenom, nom

FROM candidats WHERE moyenne > 12.5;

b.

Écrire une requête permettant d'afficher les noms, prénoms et moyennes des candidats venant de l'établissement dont l'id_etab vaut 1.

c.

Écrire ce qu'affiche la requête suivante:

SELECT nom, moyenne

FROM candidats WHERE moyenne < 16 ORDER BY nom;

3.

Écrire une requête donnant les noms des établissements et de leurs proviseurs classés par ordre croissant des notes.