Devoir pratique STI n°8 : les absences en SQL, la connexion d'un élève et son espace en PHP

Exercice 1 : les absences en SQL (8 pts)

Le lycée garde ses élèves et leurs absences dans deux tables : eleve (num, nom, classe, login, mdp) et absence (id, #num_eleve, date_abs, justifiee), où justifiee vaut 1 pour une absence justifiée et 0 sinon. Les tables sont créées par ce script :

CREATE TABLE eleve (
  num INT PRIMARY KEY,
  nom VARCHAR(30) NOT NULL,
  classe VARCHAR(10) NOT NULL,
  login VARCHAR(20) NOT NULL UNIQUE,
  mdp VARCHAR(20) NOT NULL
);
CREATE TABLE absence (
  id INT AUTO_INCREMENT PRIMARY KEY,
  num_eleve INT NOT NULL,
  date_abs DATE NOT NULL,
  justifiee INT NOT NULL,
  FOREIGN KEY (num_eleve) REFERENCES eleve(num)
);
INSERT INTO eleve VALUES
  (1, 'Sami Ben Ali', '4SI1', 'sami', 's4mi2026'),
  (2, 'Rim Trabelsi', '4SI1', 'rim', 'r1m.tn'),
  (3, 'Omar Jaziri', '4SI2', 'omar', '0mar99'),
  (4, 'Lina Mansour', '4SI2', 'lina', 'lina2008');
INSERT INTO absence (num_eleve, date_abs, justifiee) VALUES
  (1, '2026-10-05', 0),
  (1, '2026-10-12', 1),
  (3, '2026-10-06', 0),
  (1, '2026-10-20', 0),
  (3, '2026-10-21', 0),
  (2, '2026-10-22', 1);

• 1) Écrire la requête qui affiche, pour chaque élève qui a au moins une absence non justifiée, son nom et son nombre d'absences non justifiées, du plus grand nombre au plus petit (à égalité, par ordre alphabétique du nom) (4 pts).
• 2) Écrire la requête qui affiche le nom des élèves qui n'ont aucune absence, par ordre alphabétique (4 pts).

Exercice 2 : la connexion et l'espace de l'élève (12 pts)

Les tables de l'exercice 1 sont dans la base MySQL lycee, créée par le fichier base.sql ci-dessous. La connexion à la base est dans connexion.php, que chaque page inclut avec require. La page connexion.html envoie le login et le mot de passe à login.php avec la méthode POST.

CREATE DATABASE lycee;
USE lycee;
CREATE TABLE eleve (
  num INT PRIMARY KEY,
  nom VARCHAR(30) NOT NULL,
  classe VARCHAR(10) NOT NULL,
  login VARCHAR(20) NOT NULL UNIQUE,
  mdp VARCHAR(20) NOT NULL
);
CREATE TABLE absence (
  id INT AUTO_INCREMENT PRIMARY KEY,
  num_eleve INT NOT NULL,
  date_abs DATE NOT NULL,
  justifiee INT NOT NULL,
  FOREIGN KEY (num_eleve) REFERENCES eleve(num)
);
INSERT INTO eleve VALUES
  (1, 'Sami Ben Ali', '4SI1', 'sami', 's4mi2026'),
  (2, 'Rim Trabelsi', '4SI1', 'rim', 'r1m.tn'),
  (3, 'Omar Jaziri', '4SI2', 'omar', '0mar99'),
  (4, 'Lina Mansour', '4SI2', 'lina', 'lina2008');
INSERT INTO absence (num_eleve, date_abs, justifiee) VALUES
  (1, '2026-10-05', 0),
  (1, '2026-10-12', 1),
  (3, '2026-10-06', 0),
  (1, '2026-10-20', 0),
  (3, '2026-10-21', 0),
  (2, '2026-10-22', 1);

<?php
$cnx = mysqli_connect("localhost", "root", "", "lycee");
?>

<form action="login.php" method="post">
  <input type="text" name="login" id="login">
  <input type="password" name="mdp" id="mdp">
  <input type="submit" value="Se connecter">
</form>

• 1) Écrire la page login.php : si un élève a ce login et ce mot de passe, elle affiche « Connexion réussie » puis un lien « Mon espace (élève n°1) » vers espace.php?num=1, avec le numéro de cet élève ; sinon elle affiche « Login ou mot de passe incorrect » (6 pts).
• 2) Écrire la page espace.php : elle reçoit le numéro de l'élève dans l'adresse (num) ; s'il manque, elle s'arrête avec le message « Connectez-vous d'abord » (die) ; sinon elle affiche le titre « Bienvenue Sami Ben Ali (4SI1) » (avec le nom et la classe de cet élève), puis son nombre d'absences non justifiées sous la forme « 2 absence(s) non justifiée(s) » (6 pts).