Vous allez créer 2 tables dont le schéma relationnel est représenté ci-dessous :

• la table resultats contiendra les résultats des candidats
• la table candidats contiendra les informations des candidats

Tapez la commande SQL suivante :

 USE baccalaureat

qui permet de sélectionner la base de données baccalaureat

Tapez la requête SQL suivante, permettant de créer la table suivante :

candidats (num_candidat, num_anonymat, nom, prenom, date_naissance, num_etablissement)

CREATE TABLE candidats ( num_candidat varchar(10) NOT NULL, num_anonymat varchar(10) NOT NULL, nom varchar(25) NOT NULL, prenom varchar(25) NOT NULL, date_naissance date NOT NULL, num_etablissement varchar(10) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Tapez la requête SQL permettant de créer la table suivante :

resultats (num_resultat, #num_anonymat, avis, points, moyenne, decision, points_jury)

CREATE TABLE resultats ( num_resultat int(11) NOT NULL, num_anonymat varchar(10) NOT NULL, avis varchar(25) NOT NULL, points int(11) NOT NULL, moyenne float NOT NULL, decision varchar(65) DEFAULT NULL, points_jury smallint(6) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Chaque relation va contenir des données d'un certain type ou domaine :

• varchar(10) : Chaine de 10 caractères maxi
• int(11) : Entier compris entre - 2 147 483 648 et 2 147 483 647
• smallint(6) : Entier compris entre -32 768 à 32 767
• float : Nombre réel
• date : Date au format aaaa-mm-jj
• NOT NULL : relation qui devra obligatoirement être saisi
• DEFAULT NULL : si le relation n'est pas renseigné il prendra la valeur NULL

Si vous avez fait une erreur dans une table et vous souhaiter supprimer une table, exécuter la commande SQL :

DROP nom_de_la_table

Faire valider votre travail par le professeur (1).

ALTER TABLE candidats ADD PRIMARY KEY (num_anonymat), ADD KEY num_candidat (num_candidat); ALTER TABLE resultats ADD PRIMARY KEY (num_resultat), ADD KEY num_resultat (num_resultat), ADD KEY num_anonymat (num_anonymat);

On désire que la relation num_resultat soit auto-incrémenté pour ne pas avoir à le saisir.

ALTER TABLE resultats MODIFY num_resultat int(11) NOT NULL AUTO_INCREMENT;

Dans la table candidats, num_anonymat, qui est la clé primaire, est le parent de num_anonymat de la table resultats.

num_anonymat de la table candidat est la clé étrangère de num_anonymat de la table résultats.

ALTER TABLE resultats ADD FOREIGN KEY (num_anonymat) REFERENCES candidats (num_anonymat) ON DELETE CASCADE ON UPDATE CASCADE;

Si on modifie ou supprime le numéro d'anonymat dans l'une des tables, l'autre table sera également affectée grâce aux instructions ON DELETE CASCADE ON UPDATE CASCADE.

Vérification : cliquer sur la base de données baccalaureat, cliquez sur plus et choisir concepteur

 

On voit bien la relation qui a été mise en place entre les champs num_candidat des deux tables ainsi que les types des champs.

Faire valider votre travail par le professeur (2).

ATTENTION : Il faut commencer par garnir la table candidats à cause du champ num_anonymat qui est la clé étrangère de num_anonymat de la table resultats.

L'instruction SQL :

INSERT INTO table (champ1,champ2,...) VALUES (valeur1,valeur2,...)

En utilisant la requête SQL INSERT INTO, ajoutez les 2 candidats suivants dans la table candidats :

INSERT INTO ...

num_candidat	num_anonymat	nom	prenom	date _naissance	num_etablissement
202168001	B21A05226	ABBOU	STEEVE	2003-11-03	0681768C
202168002	B21A05072	ABED	ALDO	2002-09-16	0681768C

Pour afficher toutes les données de la table candidats classées par ordre croissant on exécute la requête SQL suivante :" SELECT * FROM candidats ORDER BY num_candidat ASC;

Garnir la table resultats avec les données suivantes :

num_anonymat	avis	points	moyenne
B21A05226	Preuves	902	9.02
B21A05072	Très Favorable	1750	17.5

INSERT INTO ...

Faire valider votre travail par le professeur en exécutant les deux requêtes permettant d'afficher les données des deux tables. (3).

On commence à nouveau par sélectionner la table candidats, puis cliquer sur l'onglet importation et importer le fichier candidats.csv

Il est important d'importer les candidats avant d'importer les resultats. De toute façon si la base de données est bien structurée un message d'erreur apparaîtra.

En faire de même pour la table resultats : resultats.csv

Faire valider votre travail par le professeur en affichant les données des tables (4) :

• candidats classées par numéro de candidat croissant,
• résultats classées par numéro de résultat croissant.

Le jury souhaite afficher tous les candidats Admis au baccalauréat : ceux ayant une moyenne de 10/20 et plus.

SELECT * FROM resultats WHERE moyenne>=10 ORDER BY moyenne DESC 

Ecrire la requête permettant d'afficher les candidats qui passent le second groupe

Faire valider par le professeur (5).

Elle se fera par la requête SQL suivante, que vous devrez compléter en remplaçant les ? par la bonne condition :

UPDATE resultats SET decision = ( CASE WHEN moyenne <8 THEN 'Refusé' WHEN moyenne ? AND moyenne >=? THEN 'Passe le Second Groupe' WHEN moyenne ? AND moyenne ? THEN 'Admis' WHEN moyenne ? AND moyenne ? THEN 'Admis Mention Assez Bien' WHEN moyenne ? AND moyenne ? THEN 'Admis Mention Bien' WHEN moyenne ? AND moyenne ? THEN 'Admis Mention Très Bien' WHEN moyenne ? THEN 'Admis Mention Très Bien avec Félicitations du jury' END )

Vérifiez le résultat :

Faire valider votre travail par le professeur (6).

Le jury souhaite attribuer des points supplémentaires aux candidats proche du seuil "Refusé" :

Trouver deux requêtes permettant d'afficher les candidats "Refusés" et les classer par moyenne décroissante

Résultat :

Faire valider votre travail par le professeur (7).

Le jury souhaite rattraper les candidats ci-dessus ayant un avis favorable en leur attribuant des points jury leur permettant d'obtenir 800 points.

La syntaxe de la requête SQL sera :

UPDATE table SET nom_champ1 = valeur1 WHERE nom_champ2 = valeur2 

Vérifier le résultat en affichant les candidats ayant des points_jury non null (IS NOT NULL)

Enregistrez vos commandes SQL ci-dessous et justifiez quels sont les seuls champs qu'on peut mettre après la condition WHERE :

Faire valider votre travail par le professeur (8).

Compléter puis exécuter la requête SQL suivante

UPDATE ? SET ? = ( CASE WHEN ? IS NOT NULL THEN (points+?)/100 ELSE moyenne END );

Réexécutez la requête SQL du paragraphe 2.7 permettant d'attribuer les décisions

Résultat :

Faire valider votre travail par le professeur (9).

Pour afficher les résultats, l'établissement devra avoir accès aux deux tables pour lever l'anonymat

Il s'agit de joindre les données des deux tables par la commande JOIN : sur le champ commun num_anonymat de la table resultats et le champ num_anonymat de la table candidats :
ON resultat.num_anonymat = candidats.num_anonymat

A lieu de sélectionner toutes les champs (*) on peut sélectionner uniquement les champs qui nous intéressent. Testez la requête SQL ci-dessous :

SELECT num_candidat, candidats.nom, candidats.prenom, resultats.moyenne, resultats.avis, resultats.decision, resultats.points_jury FROM candidats JOIN resultats ON candidats.num_anonymat=resultats.num_anonymat WHERE num_etablissement='0681505T' ORDER BY num_candidat ASC

Ecrire la requête SQL permettant d'afficher le nom, le prenom, la moyenne, les points jurry et le numéro de l'établissement, des candidats classés par nom ayant reçu des points du jury

Résultat

Faire valider votre travail par le professeur (10).

Ecrire la requête SQL permettant d'afficher le numéro du candidat, le nom, le prénom le numéro de résultat, la décision et les points jury des candidats classés par nom et prénom dans l'ordre croissant, uniquement de l'établissement 0681768C

Résultat

Faire valider votre travail par le professeur (11).

On remarque que les deux candidats ABBOU Steeve et ABED ALDO apparaissent 2 fois

Cette erreur provient de l'importation du fichier csv où ces candidats n'auraient pas dû y figurer.

Syntaxe SQL de la suppression :

DELETE FROM table WHERE condition

Trouver les 2 requêtes permettant de supprimer les 2 résultats en trop. Veillez à choisir la bonne relation lors de la condition de suppression. Avant d'exécuter la requête vous pouvez cliquer sur le bouton Simuler la requête pour vérifier qu'on ne supprimer qu'une ligne à chaque fois.

Faire valider votre travail par le professeur (12).

La fonction COUNT() permet de compter le nombre d'enregistrement dans une table ou d'une requête

SELECT COUNT(*) FROM table
SELECT COUNT(*) FROM table WHERE condition
SELECT COUNT(champ) FROM table
SELECT COUNT(DISTINCT champ) FROM table

Exemple : compter le nombre de candidat Admis :

 SELECT COUNT(*) FROM resultats WHERE decision LIKE "Admis%"

Le caractère % remplace tous les autres caractères existants. Il peut être placé avant et/ou après le mot clé recherché. %Admis% recherche le mot Admis dans n'importe quelle position

Plus de détails ici https://sql.sh/cours/where/like

Trouver deux requêtes avec des conditions différentes permettant d'afficher le nombre de candidats ayant reçu une mention. Notez le nombre de mentions

Faire valider votre travail par le professeur (13).

Trouver la requête permettant d'afficher le nombre de candidats Refusés dans l'établissement 0681505T

Faire valider votre travail par le professeur (14).

Que retourne l'instruction SQL suivante :

SELECT COUNT(DISTINCT num_etablissement) FROM candidats

Faire valider votre travail par le professeur (15).

Vous pouvez vous aider de la documentation suivante : https://sql.sh/fonctions/agregation/count

Ecrire les 2 requêtes SQL permettant d'afficher la moyenne de chaque établissement 0681768C et 0681505T avec une précision au centième, ainsi que les deux requêtes permettant d'afficher la plus petite et la plus grande des moyennes.

Vous utiliserez les fonctions ROUND(AVG(champ),nombre de chiffres après la virgule), MIN() et MAX()

Faire valider votre travail par le professeur (16).

Découvrir toutes les fonctions et requêtes sur le site Internet https://sql.sh/

Créer la table etablissements (num_etablissement, nom, adresse, cp, ville, telephone)

Domaines :

• num_etablissement : varchar(10)
• nom : varchar(50)
• adresse : varchar(50)
• cp : varchar(6)
• ville : varchar(25)
• téléphone : varchar(25)

Insérer les données suivantes :

• ('0681768C','Lycée Laurent de Lavoisier','42 rue Lavoisier','68200','MULHOUSE','03 89 42 29 95')
• ('0681505T','Lycée Lambert','73 Rue Josué Heilmann',68100,'MULHOUSE','03 89 33 16 55')

Ajouter un index sur le champ num_etablissement de la table candidats, puis créer la jointure entre num_etablissement de la table etablissement et num_etablissement de la table candidats

En cas d'erreur, vérifier que les deux champs num_etablissement des deux tables, soient identiques en tous points et que les numéros d'établissement soient bien renseignés dans la table etablissements.

Vérifier le fonctionnement de la requête suivante, réalisant une jointure entre 3 tables :

SELECT candidats.nom,candidats.prenom,etablissements.nom FROM candidats JOIN resultats ON candidats.num_anonymat=resultats.num_anonymat JOIN etablissements ON candidats.num_etablissement=etablissements.num_etablissement ORDER BY candidats.nom, candidats.prenom ASC

Faire valider votre travail par le professeur et lui indiquer où se trouve la clé primaire et étrangère (17).

Créer la table utilisateurs (num_utilisateur, nom, prenom, id, mdp, role, #num_etablissement)

Domaines:

• num_utilisateur : int(11) autoincrémenté
• nom : varchar(25)
• prenom : varchar(25)
• id : varchar(50)
• mdp : varchar(40)
• role : varchar(25)
• num_etablissement : varchar(10)

Les champs nom et prénoms et num_etablissement sont NULL par défaut

Le champ id (identifiant) doit être unique (index ou key)

Le champ mdp doit pouvoir contenir 40 caractères, car il sera crypté par un algorithme de hashage non décryptable (sha1)

Insérer les données suivantes dans les champs :

• (id,mdp,role,num_etablissement) les valeurs ('jury1',sha1('secret1'),'jury','0681768C')
• (id,mdp,role,num_etablissement) les valeurs ('jury2',sha1('secret2'),'jury','0681505T')
• (nom,prenom,id,mdp,role,num_etablissement) les valeurs ('LAMBERT','Jean-Henri','jlambert',sha1('secret3'),'proviseur','0681505T')
• (nom,prenom,id,mdp,role,num_etablissement) les valeurs ('HUGUENOT','Pierre','phuguenot',sha1('secret4'),'cpe','0681505T')
• (nom,prenom,id,mdp,role,num_etablissement) les valeurs ('LAVOISIER','Antoine Laurent','alavoisier',sha1('secret5'),'proviseur','0681768C')
• (nom,prenom,id,mdp,role,num_etablissement) les valeurs ('PAULZE','Marie-Anne Pierrette','mpaulze',sha1('secret6'),'cpe','0681768C')
• (nom,prenom,id,mdp,role) les valeurs (votre nom,votre prenom,votre identifiant,sha1(votre mot de passe),'administrateur')

Créer la jointure entre les champs communs des 2 tables

Trouver la requête permettant de sélectionner le rôle, le num_etablissement où l'identifiant est égal à 'alavoisier' et le mot de passe est égal à sha1('secret5')

Résultat attendu :

Faire valider votre travail par le professeur (18).

5.1 - Pages de connexion à la base de données SQL

5.2 - Page d'accueil et de connexion : index.php

5.3 - Page du proviseur

5.4 - Page de l'administrateur