COLLECTER              STOCKER              TRAITER              DIFFUSER
    |                      |                    |                    |
Fiches d'inscription  Base de données      Calcul des notes     Bulletins de notes
Feuilles de présence  Fichiers Excel       Statistiques         Convocations examen
Résultats examens    Archives papier      Emplois du temps     Attestations
    ↓                      ↓                    ↓                    ↓
 Saisie/scan         MySQL / Cloud        Logiciel gestion      Email / Impression

Phase 1              Phase 2              Phase 3              Phase 4              Phase 5
SCHEMA              ETUDE                ETUDE                REALISATION          MISE EN
DIRECTEUR           PREALABLE            DETAILLEE                                 OEUVRE
    |                   |                    |                    |                    |
Orientation         Faisabilité         Spécifications        Développement        Déploiement
stratégique        Analyse besoins      Techniques            Tests                Formation
Budget global       Etude exist.         MCD / MLD             BDD + appli          Maintenance
Plan 3-5 ans        Proposition           Maquettes            Recette              Evolution

                    PORTÉE
                  (fonctionnalités)
                       /\
                      /  \
                     /    \
                    /  ★   \
                   / QUALITÉ \
                  /____________\
              BUDGET          DÉLAI
           (coût)            (temps)

Principe fondamental :
  Si PORTEE augmente → BUDGET ou DELAI doit augmenter
  Si DELAI est réduit  → BUDGET augmente ou PORTEE diminue
  Si BUDGET est réduit → DELAI augmente ou PORTEE diminue
  On ne peut pas optimiser les 3 simultanément

PHASE 1              PHASE 2              PHASE 3              PHASE 4
ANALYSE             MCD                  MLD                  IMPLEMENTATION
    |                   |                    |                    |
Lire le CDC         Entités              Tables SQL           CREATE TABLE
Identifier          Associations         Clés primaires       INSERT INTO
entités            Cardinalités         Clés étrangères      Requêtes
Dictionnaire        Formes norm.         Contraintes          Index
de données         Graphe DF            Types de données
    |                   |                    |                    |
Niveau              Niveau               Niveau               Niveau
conceptuel          conceptuel           logique              physique
(métier)           (indép. SGBD)        (dépend SGBD)        (MySQL 8)

CDC SkolaBase — Centre de Formation (extrait)

« Le centre gère des ETUDIANTS qui s'inscrivent à des SESSIONS
  de FORMATION. Chaque session est animée par un FORMATEUR.
  Les étudiants reçoivent des notes à chaque session.
  Chaque formation appartient à une FILIERE. »

Entités candidates identifiées :
  ■ ETUDIANT    → numCIN, nomEtu, prenomEtu, dateNaiss, ville
  ■ FORMATION   → codeForm, titreForm, dureeForm, niveauForm
  ■ SESSION     → codeSess, dateDebut, dateFin, lieuSess
  ■ FORMATEUR   → numFormateur, nomForm, specialite
  ■ FILIERE     → codeFiliere, libelleFiliere

Associations identifiées :
  ■ ETUDIANT    ---[s'inscrit]---    SESSION
  ■ SESSION     ---[est animée par]--- FORMATEUR
  ■ SESSION     ---[porte sur]---     FORMATION
  ■ FORMATION   ---[appartient à]---  FILIERE

Texte du CDC :
« Le centre gère des ETUDIANTS. Chaque étudiant possède un numéro de CIN unique.
  Les étudiants peuvent s'inscrire à plusieurs SESSIONS de FORMATION.
  Un étudiant ne peut s'inscrire qu'une seule fois à une même session.
  Chaque session est animée par un seul FORMATEUR.
  Un formateur peut animer plusieurs sessions au cours de l'année.
  Chaque session porte sur une seule FORMATION.
  Une formation peut donner lieu à plusieurs sessions différentes.
  Chaque formation appartient à une seule FILIERE.
  Une filière regroupe plusieurs formations. »

► Souligner les entités : ETUDIANT, SESSION, FORMATION, FORMATEUR, FILIERE
► Souligner les verbes : s'inscrire, animer, porter sur, appartenir à
► Extraire les quantités : « plusieurs » (N), « un seul » (1), « une seule fois » (1)

                      ┌─────── nomEtu
                      ├─────── prenomEtu
numCINEtu ─────────┼─────── dateNaissEtu
                      ├─────── villeEtu
                      └─────── telEtu

                      ┌─────── nomFormateur
numFormateur ──────┼─────── specialiteForm
                      └─────── numFormateur ──────→ codeSess (via RG2)

                      ┌─────── dateDebutSess
                      ├─────── dateFinSess
codeSess ───────────┼─────── lieuSess
                      ├─────── codeForm (via RG3)
                      └─────── numFormateur (via RG2)

                      ┌─────── titreForm
                      ├─────── dureeForm
codeForm ───────────┼─────── niveauForm
                      └─────── codeFiliere (via RG4)

codeFiliere ────────────→ libelleFiliere

DF composée :
numCINEtu + codeSess ────────→ noteInscription

Sources identifiées (futurs identifiants des entités) :
  numCINEtu    → entité ETUDIANT
  numFormateur → entité FORMATEUR
  codeSess     → entité SESSION
  codeForm     → entité FORMATION
  codeFiliere  → entité FILIERE

┌───────────────────┐
│     ETUDIANT         │  ←  Nom de l'entité (en majuscules)
├───────────────────┤
│ #numCINEtu          │  ←  Identifiant (souligné, précédé de #)
├───────────────────┤
│  nomEtu             │
│  prenomEtu          │  ←  Attributs (propriétés)
│  dateNaissEtu       │
│  villeEtu           │
└───────────────────┘

Occurrence (instance) : une ligne dans la future table
  Ex : { numCINEtu:'A123456', nomEtu:'Alaoui', prenomEtu:'Karim', ... }

Depuis le graphe DF, on identifie les sources (R2) :
  numCINEtu   → Entité ETUDIANT
     Attributs (R1) : nomEtu, prenomEtu, dateNaissEtu, villeEtu, telEtu

  numFormateur → Entité FORMATEUR
     Attributs (R1) : nomFormateur, specialiteForm

  codeSess    → Entité SESSION
     Attributs (R1) : dateDebutSess, dateFinSess, lieuSess

  codeForm    → Entité FORMATION
     Attributs (R1) : titreForm, dureeForm, niveauForm

  codeFiliere → Entité FILIERE
     Attributs (R1) : libelleFiliere

  Donnée composée (R4) :
  numCINEtu + codeSess → noteInscription
     → Association INSCRIPTION porteuse de noteInscription

Association binaire (entre 2 entités) :
  ETUDIANT ─(0,N)─◆ s'inscrit ◆─(1,N)─ SESSION

Association porteuse (avec attribut) :
  ETUDIANT ─(0,N)─◆ INSCRIPTION ◆─(1,N)─ SESSION
                     noteInscription  ← attribut de l'association

Association ternaire (entre 3 entités) :
                  SALLE
                    |
  FORMATEUR ─◆ TIENT ◆─ SESSION
  (rare — éviter si possible)

RG1 : « Un étudiant peut s'inscrire à plusieurs sessions.
       Un étudiant ne s'inscrit pas forcément à une session (nouveau inscrit). »

Question 1 (du côté ETUDIANT) :
  « Un ETUDIANT est lié à COMBIEN de SESSION ? »
  Réponse : minimum 0 (pas obligé), maximum N (plusieurs) → cardinalité ETUDIANT = (0,N)

Question 2 (du côté SESSION) :
  « Une SESSION est liée à COMBIEN d'ETUDIANT ? »
  Réponse : minimum 1 (une session a au moins 1 étudiant), maximum N → cardinalité SESSION = (1,N)

Représentation :
  ETUDIANT (0,N) ◆ INSCRIPTION ◆ (1,N) SESSION

┌──────────────┐                           ┌───────────────┐
│   ETUDIANT    │                           │   FORMATEUR   │
├──────────────┤                           ├───────────────┤
│ #numCINEtu    │                           │ #numFormateur │
│  nomEtu       │                           │  nomFormateur │
│  prenomEtu    │                           │  specialite   │
│  dateNaissEtu │                           └───────────────┘
│  villeEtu     │                                    |
└──────────────┘                               (0,N)
       |                                          |
     (0,N)                              ◆──── ANIME ────◆
       |                                          |
◆─ INSCRIPTION ─◆                             (1,1)
  noteInscription                                 |
       |                           ┌──────────────┐
     (1,N)                         │   SESSION    │
       |                           ├──────────────┤
       └─────────────────────────┤ #codeSess    │
                                   │  dateDebut   │
                                   │  dateFin     │
                                   │  lieu        │
                                   └──────────────┘
                                           |
                                         (1,N)
                                           |
                              ◆── PORTE SUR ──◆
                                           |
                                         (1,1)
                                           |
                           ┌──────────────┐
                           │  FORMATION  │
                           ├──────────────┤
                           │ #codeForm   │
                           │  titreForm  │
                           │  dureeForm  │
                           │  niveauForm │
                           └──────────────┘
                                   |
                                 (1,N)
                                   |
                      ◆── APPARTIENT A ──◆
                                   |
                                 (1,1)
                                   |
                       ┌──────────────┐
                       │   FILIERE    │
                       ├──────────────┤
                       │ #codeFiliere │
                       │  libelleFiliere│
                       └──────────────┘

Table INSCRIPTION_BRUTE (non normalisée) :
numCINEtu | nomEtu  | codeSess | titreForm        | libelleFiliere | note
----------|---------|----------|------------------|----------------|-----
A123456   | Alaoui  | SESS-01  | Dév. Web        | Informatique   | 14
A123456   | Alaoui  | SESS-02  | Comptabilité    | Gestion        | 16
B789012   | Bennani | SESS-01  | Dév. Web        | Informatique   | 12

Problèmes :
✕ nomEtu répété pour chaque session → redondance
✕ Si "Dév. Web" change en "Développement Web" → modifier 2 lignes
✕ Si Alaoui se désinscrit de SESS-01 → on perd que SESS-01 existe
✕ Si SESS-01 a 0 inscrits → impossible de stocker la session

Table INSCRIPTION (CLÉ : numCINEtu + codeSess) — non 2FN :
numCINEtu | codeSess | noteInscription | nomEtu  | dateDebutSess
A123456   | SESS-01  | 14              | Alaoui  | 2024-09-01

Problème :
  numCINEtu → nomEtu      (dépend de PARTIE de la clé → violation 2FN)
  codeSess  → dateDebutSess (dépend de PARTIE de la clé → violation 2FN)
  numCINEtu + codeSess → noteInscription (dépend de TOUTE la clé → OK)

Correction — décomposer en 3 tables :
  ETUDIANT(numCINEtu, nomEtu, ...)
  SESSION(codeSess, dateDebutSess, ...)
  INSCRIPTION(numCINEtu, codeSess, noteInscription)

Table FORMATION (non 3FN) :
codeForm | titreForm   | dureeForm | codeFiliere | libelleFiliere
DEV01    | Dév. Web   | 120       | INF         | Informatique
CPT01    | Comptabilité| 80        | GES         | Gestion

Problème :
  codeForm → codeFiliere     (OK — codeFiliere dépend de la clé)
  codeFiliere → libelleFiliere (VIOLATION — un non-clé détermine un autre non-clé)
  Donc : codeForm → codeFiliere → libelleFiliere (transitivité)

Correction — décomposer en 2 tables :
  FORMATION(codeForm, titreForm, dureeForm, codeFiliere)
  FILIERE(codeFiliere, libelleFiliere)

ETUDIANT  (#numCINEtu, nomEtu, prenomEtu, dateNaissEtu, villeEtu, telEtu)
FORMATEUR (#numFormateur, nomFormateur, specialiteForm)
SESSION   (#codeSess, dateDebutSess, dateFinSess, lieuSess)
FORMATION (#codeForm, titreForm, dureeForm, niveauForm)
FILIERE   (#codeFiliere, libelleFiliere)

Association ANIME : FORMATEUR (0,N) ◆────◆ (1,1) SESSION
  → Côté SESSION a cardinalité max = 1
  → SESSION reçoit la FK de FORMATEUR : numFormateur
  SESSION (#codeSess, dateDebutSess, dateFinSess, lieuSess, #numFormateur FK)

Association PORTE SUR : SESSION (1,N) ◆────◆ (1,1) FORMATION
  → Côté FORMATION a cardinalité max = 1 ? Non !
  Attention : la SESSION a cardinalité (1,1) du côté FORMATION
  → C'est SESSION qui a max=1 vis-à-vis de FORMATION
  → SESSION reçoit la FK de FORMATION : codeForm
  SESSION (#codeSess, dateDebutSess, dateFinSess, lieuSess, #numFormateur FK, #codeForm FK)

Association APPARTIENT A : FORMATION (1,N) ◆────◆ (1,1) FILIERE
  → FORMATION a cardinalité max = 1 vis-à-vis de FILIERE
  → FORMATION reçoit la FK de FILIERE : codeFiliere
  FORMATION (#codeForm, titreForm, dureeForm, niveauForm, #codeFiliere FK)

Association INSCRIPTION : ETUDIANT (0,N) ◆ INSCRIPTION ◆ (1,N) SESSION
  → Les deux côtés ont cardinalité max = N
  → Créer une table pivot INSCRIPTION
  → Clé composée : (numCINEtu + codeSess)
  → Attribut porté noteInscription devient colonne

  INSCRIPTION (#numCINEtu FK, #codeSess FK, noteInscription)

Notation : #champ = clé primaire (PK) | *champ = clé étrangère (FK)

FILIERE   (#codeFiliere, libelleFiliere)

FORMATION (#codeForm, titreForm, dureeForm, niveauForm,
           *codeFiliere → FILIERE.codeFiliere)

FORMATEUR (#numFormateur, nomFormateur, specialiteForm)

SESSION   (#codeSess, dateDebutSess, dateFinSess, lieuSess,
           *numFormateur → FORMATEUR.numFormateur,
           *codeForm     → FORMATION.codeForm)

ETUDIANT  (#numCINEtu, nomEtu, prenomEtu, dateNaissEtu,
           villeEtu, telEtu)

INSCRIPTION (#numCINEtu → ETUDIANT.numCINEtu,
             #codeSess  → SESSION.codeSess,
             noteInscription)
             CLÉ PRIMAIRE : (numCINEtu, codeSess)

FILIERE ───────▶ FORMATION ──────▶ SESSION ◄────── FORMATEUR
                                       ┃
                                  INSCRIPTION
                                       ┃
                                   ETUDIANT

┌──────────────────────────────────────────────────────────────────────────────┐
│ ① CDC           │ ② RG           │ ③ DD           │ ④ GRAPHE DF    │
├────────────────────┤ ├────────────────────┤ ├────────────────────┤ ├────────────────────┤
│ Périmètre        │ RG1, RG2...   │ Code/Type/   │ Sources =    │
│ Entités cand.   │ Verbes+quant.│ Taille/Obs.  │ identifiants │
│ Limites         │ Cardinalités │ objetConcept │ DF directes  │
│                 │ initiales     │ Contrôles    │ pas transitiv│
└────────────────────┘ └────────────────────┘ └────────────────────┘ └────────────────────┘
        │                     │                     │                     │
        ▼                     ▼                     ▼                     ▼
┌────────────────────────────────────────────┐
│ ⑤ MCD                          │ ⑥ MLD                          │
├────────────────────────────────────────────┤
│ Entités + attributs (R1+R2)  │ Tables + clés PK (R1)         │
│ Associations + cardinalités   │ FK migrées (R2, cardmax=1)    │
│ R3 (porteuse), R4 (non-port.) │ Tables pivot (R3, N,N)         │
│ R5 (hors graphe)              │ Normalisation vérifiée         │
└────────────────────────────────────────────┘

CDC — Bibliothèque Municipale de Rabat

« La bibliothèque gère des ADHERENTS identifiés par un numéro unique.
   Elle possède un catalogue de LIVRES, chaque livre identifié par son ISBN.
   Un livre peut exister en plusieurs EXEMPLAIRES physiques (un numéro par exemplaire).
   Un adhérent peut emprunter plusieurs exemplaires simultanément.
   Un exemplaire ne peut être emprunté que par un seul adhérent à la fois.
   Chaque emprunt a une date de début et une date de retour prévue.
   Chaque livre appartient à une seule CATEGORIE (Roman, Sciences, Histoire...).
   Un auteur peut avoir écrit plusieurs livres, et un livre peut avoir plusieurs AUTEURS. »

Travail demandé :
  1. Identifier les entités candidates
  2. Rédiger les RG (RG1 à RG5)
  3. Construire le DD (colonnes essentielles)
  4. Dessiner le graphe des DF
  5. Dessiner le MCD avec les cardinalités
  6. Déduire le MLD

ENTITES : ADHERENT, LIVRE, EXEMPLAIRE, CATEGORIE, AUTEUR

REGLES DE GESTION :
  RG1 : Un adhérent peut emprunter 0 ou plusieurs exemplaires. Un exemplaire est
        emprunté par 0 ou 1 adhérent à la fois.
  RG2 : Un livre possède 1 ou plusieurs exemplaires. Un exemplaire appartient à 1 seul livre.
  RG3 : Un livre appartient à 1 seule catégorie. Une catégorie regroupe 0 ou plusieurs livres.
  RG4 : Un livre a 1 ou plusieurs auteurs. Un auteur a écrit 0 ou plusieurs livres.

MCD :
  ADHERENT (0,N) ◆ EMPRUNT (date_debut, date_retour_prevue) ◆ (0,1) EXEMPLAIRE
  EXEMPLAIRE (1,N) ◆ POSSEDE ◆ (1,1) LIVRE
  LIVRE (1,N) ◆ APPARTIENT ◆ (1,1) CATEGORIE
  LIVRE (0,N) ◆ REDIGE ◆ (1,N) AUTEUR

MLD (6 tables) :
  CATEGORIE(#codeCateg, libelleCateg)
  AUTEUR(#idAuteur, nomAuteur, prenomAuteur)
  LIVRE(#isbn, titreL, anneePubli, *codeCateg FK)
  LIVRE_AUTEUR(#isbn FK, #idAuteur FK)           ← R3 (N,N)
  EXEMPLAIRE(#numExemp, etat, *isbn FK)
  EMPRUNT(#idAdherent FK, #numExemp FK, dateDebut, dateRetourPrevue)
  ADHERENT(#idAdherent, nomAdh, prenomAdh, emailAdh)

① Télécharger MySQL Installer
     URL : https://dev.mysql.com/downloads/installer/
     Choisir : "MySQL Installer for Windows" (mysql-installer-community-x.x.x.msi)
     Taille : ~450 Mo (community = version gratuite)

② Lancer l'installeur en tant qu'Administrateur
     Clic droit sur le .msi → "Exécuter en tant qu'administrateur"

③ Choisir le type d'installation
     Sélectionner "Custom" pour choisir les composants
     Cocher : MySQL Server 8.0 + MySQL Workbench 8.0
     (Éviter "Full" — installe des composants inutiles)

④ Configurer MySQL Server
     Port : 3306 (laisser par défaut)
     Root password : choisir un mot de passe FORT (noter le !)
     Windows Service : cocher "Start at system startup"

⑤ Exécuter l'installation
     Cliquer "Execute" → attendre la fin de l'installation des deux composants

⑥ Vérifier l'installation
     Démarrer MySQL Workbench → l'interface d'accueil doit s'ouvrir
     Dans la liste des connexions : "Local instance MySQL80" doit apparaître

┌──────────────────────────────────────────────────────────┐
│  MySQL Workbench — Home Screen                          │
├──────────────────────────────────────────────────────────┤
│  MODULE 1 : MySQL Connections                             │
│  ┌─────────────────┐  ┌─────────────┐                          │
│  │ Local MySQL80  │  │  + New conn. │                          │
│  │ localhost:3306 │  └─────────────┘                          │
│  └─────────────────┘                                         │
├──────────────────────────────────────────────────────────┤
│  MODULE 2 : Models (EER Diagrams)                        │
│  ┌────────────────┐                                       │
│  │ + New Model    │  Ouvrir un modèle .mwb existant              │
│  └────────────────┘                                       │
├──────────────────────────────────────────────────────────┤
│  MODULE 3 : MySQL Migration Wizard                       │
│  Migrer depuis SQL Server, Access, PostgreSQL vers MySQL  │
└──────────────────────────────────────────────────────────┘

① File → New Model  (ou Ctrl+N depuis le module Models)

② Configurer le schéma :
   Onglet "EER Diagram" → Double-clic sur "mydb" dans le panneau de gauche
   → Name : skolabase
   → Default Charset : utf8mb4
   → Default Collation : utf8mb4_unicode_ci

③ Ouvrir le diagramme EER :
   Double-clic sur "Add Diagram" (icône diagramme) dans le panneau gauche
   → Un canvas blanc s'ouvre — c'est ici qu'on dessine

④ Sauvegarder le modèle :
   File → Save Model  (Ctrl+S)
   → Nom du fichier : SkolaBase.mwb  (format propriétaire Workbench)
   → Sauvegarder régulièrement !

① Dans la barre d'outils du diagramme EER :
   Cliquer l'icône "Table" (rectangle avec colonnes)
   → Cliquer sur le canvas pour placer la table

② Nommer la table :
   Double-clic sur la table placée → onglet "Table"
   → Name : Etudiant
   → Engine : InnoDB  (toujours InnoDB pour les FK)
   → Charset/Collation : utf8mb4 / utf8mb4_unicode_ci

③ Ajouter les colonnes (onglet "Columns") :
   Cliquer dans la zone vide sous les en-têtes pour ajouter une ligne

   Column Name      | Datatype    | PK | NN | UQ | AI | Default
   numCINEtu        | VARCHAR(8)  | ✓  | ✓  |    |    |
   nomEtu           | VARCHAR(50) |    | ✓  |    |    |
   prenomEtu        | VARCHAR(50) |    | ✓  |    |    |
   dateNaissEtu     | DATE        |    |    |    |    | NULL
   villeEtu         | VARCHAR(50) |    | ✓  |    |    |
   telEtu           | VARCHAR(15) |    |    |    |    | NULL

TABLE : Filiere
  codeFiliere     VARCHAR(6)    PK NN
  libelleFiliere  VARCHAR(80)   NN

TABLE : Formation
  codeForm        VARCHAR(10)   PK NN
  titreForm       VARCHAR(100)  NN
  dureeForm       SMALLINT      NN
  niveauForm      VARCHAR(20)
  codeFiliere     VARCHAR(6)    NN  ← FK vers Filiere (à configurer en section suivante)

TABLE : Formateur
  numFormateur    INT           PK NN AI
  nomFormateur    VARCHAR(50)   NN
  specialiteForm  VARCHAR(80)

TABLE : Session
  codeSess        VARCHAR(10)   PK NN
  dateDebutSess   DATE          NN
  dateFinSess     DATE
  lieuSess        VARCHAR(100)
  numFormateur    INT           NN  ← FK vers Formateur
  codeForm        VARCHAR(10)   NN  ← FK vers Formation

TABLE : Etudiant
  numCINEtu       VARCHAR(8)    PK NN
  nomEtu          VARCHAR(50)   NN
  prenomEtu       VARCHAR(50)   NN
  dateNaissEtu    DATE
  villeEtu        VARCHAR(50)   NN
  telEtu          VARCHAR(15)

TABLE : Inscription
  numCINEtu       VARCHAR(8)    PK NN  ← FK vers Etudiant
  codeSess        VARCHAR(10)   PK NN  ← FK vers Session
  noteInscription DECIMAL(4,2)         ← entre 0.00 et 20.00

① Database → Forward Engineer...
② Connexion : choisir SkolaBase_Local → Next
③ Options : cocher "DROP objects before each CREATE" → Next
④ Objets : sélectionner toutes les tables → Next
⑤ Prévisualiser le SQL généré → Next → Execute
⑥ Résultat : toutes les tables créées dans MySQL !

Symboles en bout de ligne (notation Crow's Foot) :

  │   = exactement 1 (obligatoire, maximum 1)
  o   = 0 (facultatif)
  <   = N (plusieurs, patte de corbeau)

Combinaisons :
  ││ = (1,1) exactement un        Formateur ─││─ Session : 1 formateur/session
  o│ = (0,1) zéro ou un           Session ─│o── Formateur : un formateur peut avoir 0 session
  │< = (1,N) au moins un          Filiere ─<│── Formation : une formation/une filière
  o< = (0,N) zéro ou plusieurs    Etudiant ─<o─ Inscription : 0 ou N inscriptions/étudiant

① Sélectionner l'outil "1:N Non-Identifying Relationship" dans la barre d'outils

② Cliquer sur la table Formation (côté N — plusieurs formations/filière)
   puis cliquer sur la table Filiere (côté 1 — une filière par formation)

③ Workbench ajoute automatiquement dans Formation :
   → Colonne codeFiliere VARCHAR(6) NN
   → Contrainte FK : Formation_ibfk_1 REFERENCES Filiere(codeFiliere)

④ Configurer la FK (double-clic sur la ligne de relation) :
   Onglet "Foreign Key"
   → Renommer : fk_Formation_Filiere (plus lisible)
   → ON DELETE : RESTRICT  (impossible de supprimer une filière si des formations y sont rattachées)
   → ON UPDATE : CASCADE   (si codeFiliere change, répercuter automatiquement)

Toutes les relations SkolaBase :
  Filiere  (1) ─── (N) Formation   → Non-Identifying 1:N  ON DELETE RESTRICT
  Formation(1) ─── (N) Session      → Non-Identifying 1:N  ON DELETE RESTRICT
  Formateur(1) ─── (N) Session      → Non-Identifying 1:N  ON DELETE RESTRICT
  Etudiant (N) ─── (N) Session via Inscription → Identifying 1:N sur les deux côtés

① Type and Networking
   Config Type     : Development Computer (réserve moins de mémoire)
   Port            : 3306  (ne pas changer sauf conflit)
   Named Pipe      : désactiver (inutile pour notre usage)
   Open Windows Firewall port : cocher (accès réseau local)

② Authentication Method
   Choisir : "Use Strong Password Encryption" (caching_sha2_password)
   → MySQL 8.0 par défaut — plus sécurisé que l'ancien mysql_native_password
   Attention : certains vieux pilotes PHP/Python ne supportent pas encore ce mode
   → Si problèmes de connexion PHP : choisir "Use Legacy Authentication Method"

③ Accounts and Roles
   Root Password   : choisir un mot de passe FORT (minimum 12 caractères)
                     Exemple : SkolaBase_2024!  ← à noter impérativement
   Ajouter un compte  : optionnel — on créera les utilisateurs après (Partie 9)

④ Windows Service
   Service Name    : MySQL80  (défaut)
   Start at Startup: cocher  → MySQL démarre automatiquement avec Windows
   Run as System Account : garder par défaut

# Démarrer MySQL
net start MySQL80

# Arrêter MySQL
net stop MySQL80

# Vérifier le statut
sc query MySQL80
# Résultat attendu : STATE : 4 RUNNING

# Méthode graphique : services.msc
# Chercher MySQL80 → Clic droit → Démarrer/Arrêter/Redémarrer

# Démarrer MySQL
sudo systemctl start mysql

# Arrêter MySQL
sudo systemctl stop mysql

# Redémarrer (après modification my.cnf)
sudo systemctl restart mysql

# Vérifier le statut
sudo systemctl status mysql
# Résultat attendu : Active: active (running)

# Activer le démarrage automatique
sudo systemctl enable mysql

-- Vérifier la version et l'état
STATUS;
-- Affiche : Server version: 8.0.xx, Connection id, Uptime...

-- Variables de configuration importantes
SHOW VARIABLES LIKE 'version';
SHOW VARIABLES LIKE 'port';
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'max_connections';

-- Bases de données présentes par défaut
SHOW DATABASES;
-- information_schema  : métadonnées sur toutes les BDD
-- mysql               : comptes utilisateurs, privilèges
-- performance_schema  : métriques de performance
-- sys                 : vues simplifiées de performance_schema

  NIVEAU 1 — EXTERNE (Vues utilisateurs)
  ┌───────────┐  ┌───────────┐  ┌───────────┐
  │ Vue Etudiant │  │ Vue Admin   │  │ Vue Rapport │
  └───────────┘  └───────────┘  └───────────┘
          │                    │                   │
          └──────────────────────┴──────────────────────┘
                          │
  NIVEAU 2 — CONCEPTUEL (Schéma logique global)
  ┌─────────────────────────────────────────────┐
  │ Tables : Etudiant, Session, Inscription... │
  │ Relations, contraintes, types de données  │
  └─────────────────────────────────────────────┘
                          │
  NIVEAU 3 — INTERNE (Stockage physique)
  ┌─────────────────────────────────────────────┐
  │ Fichiers .ibd, index B-Tree, pages 16Ko    │
  │ Géré par MySQL InnoDB — transparent        │
  └─────────────────────────────────────────────┘

Indépendance logique : changer le schéma ne modifie pas les vues utilisateurs
Indépendance physique : changer le stockage ne modifie pas le schéma

-- Créer la base de données SkolaBase
CREATE DATABASE skolabase
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
-- utf8mb4 : supporte les caractères arabes, accents, emojis
-- utf8mb4_unicode_ci : comparaisons insensibles à la casse

-- Sélectionner la base active (toutes les commandes suivantes s'y appliquent)
USE skolabase;

-- Vérifier la base active
SELECT DATABASE();
-- Résultat : skolabase

-- Lister toutes les bases disponibles
SHOW DATABASES;

-- Lister les tables de la base active
SHOW TABLES;

-- Supprimer une base (ATTENTION : irréversible, supprime toutes les tables)
DROP DATABASE IF EXISTS skolabase_test;
-- IF EXISTS évite une erreur si la base n'existe pas

-- Afficher la structure de création d'une base
SHOW CREATE DATABASE skolabase;

CREATE TABLE [IF NOT EXISTS] nom_table (
    nom_colonne  type_donnee  [contraintes_colonne],
    nom_colonne  type_donnee  [contraintes_colonne],
    ...
    [contraintes_table]
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci
COMMENT = 'Description de la table';

-- Contraintes colonne : NOT NULL, DEFAULT valeur, UNIQUE, AUTO_INCREMENT
-- Contraintes table   : PRIMARY KEY(...), FOREIGN KEY(...) REFERENCES ...,
--                       INDEX, UNIQUE KEY, CHECK

CREATE TABLE IF NOT EXISTS Formateur (
    numFormateur    INT             NOT NULL AUTO_INCREMENT,
    nomFormateur    VARCHAR(50)     NOT NULL,
    prenomFormateur VARCHAR(50)     NOT NULL,
    specialiteForm  VARCHAR(80)     NULL,
    emailFormateur  VARCHAR(100)    NULL,
    actifFormateur  BOOLEAN         NOT NULL DEFAULT TRUE,
    PRIMARY KEY (numFormateur)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COMMENT = 'Formateurs du centre SkolaBase';

USE skolabase;

CREATE TABLE IF NOT EXISTS Filiere (
    codeFiliere     CHAR(6)         NOT NULL,
    libelleFiliere  VARCHAR(80)     NOT NULL,
    PRIMARY KEY (codeFiliere)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS Formation (
    codeForm        VARCHAR(10)     NOT NULL,
    titreForm       VARCHAR(100)    NOT NULL,
    dureeForm       SMALLINT        NOT NULL COMMENT 'Durée en heures',
    niveauForm      ENUM('Technicien','Bac','Bac+2','Bac+3') NULL,
    codeFiliere     CHAR(6)         NOT NULL,
    PRIMARY KEY (codeForm),
    CONSTRAINT fk_Form_Filiere
        FOREIGN KEY (codeFiliere) REFERENCES Filiere(codeFiliere)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS Formateur (
    numFormateur    INT             NOT NULL AUTO_INCREMENT,
    nomFormateur    VARCHAR(50)     NOT NULL,
    prenomFormateur VARCHAR(50)     NOT NULL,
    specialiteForm  VARCHAR(80)     NULL,
    PRIMARY KEY (numFormateur)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS Session (
    codeSess        VARCHAR(10)     NOT NULL,
    dateDebutSess   DATE            NOT NULL,
    dateFinSess     DATE            NULL,
    lieuSess        VARCHAR(100)    NULL,
    numFormateur    INT             NOT NULL,
    codeForm        VARCHAR(10)     NOT NULL,
    PRIMARY KEY (codeSess),
    CONSTRAINT fk_Sess_Formateur
        FOREIGN KEY (numFormateur) REFERENCES Formateur(numFormateur)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_Sess_Formation
        FOREIGN KEY (codeForm) REFERENCES Formation(codeForm)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS Etudiant (
    numCINEtu       CHAR(8)         NOT NULL COMMENT 'Lettre + 7 chiffres ex: A1234567',
    nomEtu          VARCHAR(50)     NOT NULL,
    prenomEtu       VARCHAR(50)     NOT NULL,
    dateNaissEtu    DATE            NULL,
    villeEtu        VARCHAR(50)     NOT NULL DEFAULT 'Casablanca',
    telEtu          VARCHAR(15)     NULL,
    PRIMARY KEY (numCINEtu)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS Inscription (
    numCINEtu       CHAR(8)         NOT NULL,
    codeSess        VARCHAR(10)     NOT NULL,
    noteInscription DECIMAL(4,2)    NULL COMMENT 'Note sur 20, ex: 14.50',
    PRIMARY KEY (numCINEtu, codeSess),
    CONSTRAINT fk_Insc_Etudiant
        FOREIGN KEY (numCINEtu) REFERENCES Etudiant(numCINEtu)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_Insc_Session
        FOREIGN KEY (codeSess) REFERENCES Session(codeSess)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- Façon 1 : inline (colonne unique, syntaxe courte)
CREATE TABLE Filiere (
    codeFiliere     CHAR(6)  NOT NULL  PRIMARY KEY,
    libelleFiliere  VARCHAR(80) NOT NULL
);

-- Façon 2 : en fin de table (recommandé — plus lisible)
CREATE TABLE Formation (
    codeForm    VARCHAR(10) NOT NULL,
    titreForm   VARCHAR(100) NOT NULL,
    codeFiliere CHAR(6)     NOT NULL,
    PRIMARY KEY (codeForm)                  -- PK simple
);

-- Façon 3 : avec nom de contrainte (bonne pratique)
CREATE TABLE Inscription (
    numCINEtu   CHAR(8)     NOT NULL,
    codeSess    VARCHAR(10) NOT NULL,
    note        DECIMAL(4,2),
    CONSTRAINT pk_Inscription PRIMARY KEY (numCINEtu, codeSess)  -- PK composée nommée
);

-- Vérifier les clés d'une table
SHOW KEYS FROM Inscription;
-- ou :
SHOW CREATE TABLE Inscription;

CREATE TABLE Session (
    codeSess        VARCHAR(10)  NOT NULL,
    dateDebutSess   DATE         NOT NULL,
    dateFinSess     DATE         NULL,
    lieuSess        VARCHAR(100) NULL,
    numFormateur    INT          NOT NULL,
    codeForm        VARCHAR(10)  NOT NULL,

    PRIMARY KEY (codeSess),

    -- FK nommée explicitement (bonne pratique)
    CONSTRAINT fk_Sess_Formateur
        FOREIGN KEY (numFormateur)
        REFERENCES Formateur(numFormateur)
        ON DELETE RESTRICT    -- Interdit supprimer un formateur avec des sessions
        ON UPDATE CASCADE,    -- Si numFormateur change, répercuter dans Session

    CONSTRAINT fk_Sess_Formation
        FOREIGN KEY (codeForm)
        REFERENCES Formation(codeForm)
        ON DELETE RESTRICT
        ON UPDATE CASCADE

) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- Vérifier les FK d'une table
SELECT
    CONSTRAINT_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'Session'
  AND TABLE_SCHEMA = 'skolabase'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

CREATE TABLE Etudiant (
    numCINEtu    CHAR(8)      NOT NULL,                -- Obligatoire
    nomEtu       VARCHAR(50)  NOT NULL,                -- Obligatoire
    prenomEtu    VARCHAR(50)  NOT NULL,                -- Obligatoire
    dateNaissEtu DATE         NULL,                    -- Optionnel
    villeEtu     VARCHAR(50)  NOT NULL DEFAULT 'Casablanca', -- Valeur par défaut
    telEtu       VARCHAR(15)  NULL,
    emailEtu     VARCHAR(100) NULL UNIQUE,             -- Unique (index automatique)

    PRIMARY KEY (numCINEtu),

    -- CHECK : vérifier le format CIN marocain (lettre + 7 chiffres)
    CONSTRAINT chk_CIN
        CHECK (numCINEtu REGEXP '^[A-Z][0-9]{7}$') ENFORCED,

    -- CHECK : date de naissance réaliste
    CONSTRAINT chk_DateNaiss
        CHECK (dateNaissEtu IS NULL OR dateNaissEtu < CURDATE()) ENFORCED

) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- CHECK sur la note dans Inscription
ALTER TABLE Inscription
    ADD CONSTRAINT chk_Note
        CHECK (noteInscription IS NULL
               OR (noteInscription >= 0 AND noteInscription <= 20)) ENFORCED;

-- UNIQUE sur plusieurs colonnes (unicité composite)
ALTER TABLE Session
    ADD CONSTRAINT uq_Sess_FormDate
        UNIQUE (codeForm, dateDebutSess);
-- Un même formation ne peut pas avoir deux sessions au même jour

-- ADD COLUMN : ajouter une colonne
ALTER TABLE Etudiant
    ADD COLUMN emailEtu VARCHAR(100) NULL
    AFTER telEtu;                          -- Positionner après telEtu
-- AFTER col  : insérer après une colonne précise
-- FIRST      : insérer en première position
-- (sans AFTER/FIRST : ajoutée en dernière position par défaut)

-- Ajouter plusieurs colonnes en une commande
ALTER TABLE Formateur
    ADD COLUMN emailFormateur  VARCHAR(100) NULL AFTER nomFormateur,
    ADD COLUMN telFormateur    VARCHAR(15)  NULL AFTER emailFormateur,
    ADD COLUMN actif           BOOLEAN      NOT NULL DEFAULT TRUE;

-- MODIFY COLUMN : changer le type ou les contraintes (garde le nom)
ALTER TABLE Formation
    MODIFY COLUMN titreForm VARCHAR(150) NOT NULL;  -- Agrandir la taille
-- Attention : peut échouer si les données existantes ne sont pas compatibles

ALTER TABLE Inscription
    MODIFY COLUMN noteInscription DECIMAL(4,2) NOT NULL DEFAULT 0.00;
-- Ajouter NOT NULL sur une colonne existante (toutes les lignes doivent avoir une valeur)

-- CHANGE COLUMN : renommer ET modifier (deux noms requis)
ALTER TABLE Etudiant
    CHANGE COLUMN villeEtu communeEtu VARCHAR(80) NOT NULL DEFAULT 'Casablanca';
-- Ancien nom : villeEtu | Nouveau nom : communeEtu | Nouveau type

-- DROP COLUMN : supprimer une colonne (irréversible !)
ALTER TABLE Etudiant
    DROP COLUMN emailEtu;

-- Supprimer plusieurs colonnes
ALTER TABLE Formateur
    DROP COLUMN telFormateur,
    DROP COLUMN actif;

-- DROP INDEX : supprimer un index (sauf PK)
ALTER TABLE Etudiant
    DROP INDEX emailEtu;     -- Supprime l'index UNIQUE sur emailEtu
-- ou : DROP INDEX nom_index ON table;

-- DROP FOREIGN KEY : supprimer une contrainte FK
-- D'abord trouver le nom de la contrainte :
SHOW CREATE TABLE Session;
-- Puis supprimer :
ALTER TABLE Session
    DROP FOREIGN KEY fk_Sess_Formateur;
-- Note : supprimer la FK ne supprime pas la colonne numFormateur

-- RENAME TABLE / RENAME TO
ALTER TABLE Session RENAME TO SessionFormation;
-- ou : RENAME TABLE Session TO SessionFormation;

-- DROP TABLE : supprimer une table et toutes ses données (irréversible)
DROP TABLE IF EXISTS SessionFormation;
-- IF EXISTS : pas d'erreur si la table n'existe pas

-- Supprimer plusieurs tables
DROP TABLE IF EXISTS Inscription, Session, Formation, Filiere;
-- Attention : respecter l'ordre inverse des FK (enfants avant parents)

-- DROP DATABASE : supprimer toute la base (EXTRÊMEMENT dangereux)
DROP DATABASE IF EXISTS skolabase_test;

-- Créer un index simple (recherches fréquentes sur villeEtu)
CREATE INDEX idx_Etu_Ville ON Etudiant(villeEtu);

-- Créer un index unique (valeurs distinctes obligatoires)
CREATE UNIQUE INDEX idx_Etu_Email ON Etudiant(emailEtu);

-- Ajouter un index via ALTER TABLE
ALTER TABLE Formation
    ADD INDEX idx_Form_Filiere (codeFiliere),    -- Accélérer les JOIN avec Filiere
    ADD INDEX idx_Form_Niveau (niveauForm);      -- Filtrer par niveau fréquemment

-- Index composite (colonne 1 + colonne 2)
CREATE INDEX idx_Sess_FormDate ON Session(codeForm, dateDebutSess);
-- Utilisé quand WHERE codeForm = ? AND dateDebutSess > ?

-- Lister les index d'une table
SHOW INDEX FROM Session;

-- Lister les index avec leur utilisation (MySQL 8+)
SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE, SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_NAME = 'Session' AND TABLE_SCHEMA = 'skolabase';

-- Syntaxe 1 : lister les colonnes (recommandée)
INSERT INTO Filiere (codeFiliere, libelleFiliere)
VALUES ('INF', 'Informatique');

-- Syntaxe 2 : sans liste de colonnes (ordre strict !)
-- RISQUÉ : si la structure change, la requête peut échouer silencieusement
INSERT INTO Filiere
VALUES ('GES', 'Gestion'), ('ELE', 'Électronique');

-- Syntaxe 3 : SET (style objet, lisible)
INSERT INTO Formateur SET
    nomFormateur    = 'Alaoui',
    prenomFormateur = 'Karim',
    specialiteForm  = 'Développement Web';

-- Colonnes avec DEFAULT : omettre la colonne ou utiliser DEFAULT
INSERT INTO Etudiant (numCINEtu, nomEtu, prenomEtu, dateNaissEtu)
VALUES ('A1234567', 'Bennani', 'Sara', '2001-03-15');
-- villeEtu prend la valeur DEFAULT 'Casablanca'
-- telEtu prend NULL (accepte NULL)

-- Utiliser explicitement DEFAULT et NULL
INSERT INTO Etudiant
VALUES ('B7654321', 'Idrissi', 'Youssef', '1999-07-22', DEFAULT, NULL);
--                                                        ^ville   ^tel

USE skolabase;

-- 1. Filières (pas de FK, insérer en premier)
INSERT INTO Filiere (codeFiliere, libelleFiliere) VALUES
    ('INF', 'Informatique'),
    ('GES', 'Gestion et Commerce'),
    ('ELE', 'Électronique et Électrotechnique'),
    ('BTP', 'Bâtiment et Travaux Publics');

-- 2. Formations
INSERT INTO Formation (codeForm, titreForm, dureeForm, niveauForm, codeFiliere) VALUES
    ('DEV-WEB', 'Développement Web Full Stack',    120, 'Bac+2', 'INF'),
    ('DEV-MOB', 'Développement Mobile Android',    80,  'Bac+2', 'INF'),
    ('BDD-ADM', 'Administration des Bases de Données', 100, 'Bac+3', 'INF'),
    ('CPT-GEN', 'Comptabilité Générale',           90,  'Bac',   'GES'),
    ('MKT-DIG', 'Marketing Digital',               60,  'Bac+2', 'GES');

-- 3. Formateurs (numFormateur = AUTO_INCREMENT, ne pas le spécifier)
INSERT INTO Formateur (nomFormateur, prenomFormateur, specialiteForm) VALUES
    ('Alaoui',   'Karim',   'Développement Web, PHP, Laravel'),
    ('Bennani',  'Nadia',   'Bases de données, MySQL, Oracle'),
    ('Chraibi',  'Hassan',  'Réseaux, Administration système'),
    ('Idrissi',  'Leila',   'Marketing digital, SEO');

-- Récupérer le dernier ID généré
SELECT LAST_INSERT_ID();
-- Résultat : 4 (dernier numFormateur inséré)

-- 4. Sessions
INSERT INTO Session (codeSess, dateDebutSess, dateFinSess, lieuSess, numFormateur, codeForm) VALUES
    ('SESS-2024-01', '2024-09-02', '2024-12-20', 'Salle A101 - Casablanca', 1, 'DEV-WEB'),
    ('SESS-2024-02', '2024-09-09', '2024-11-29', 'Salle B205 - Rabat',      2, 'BDD-ADM'),
    ('SESS-2024-03', '2024-10-07', '2024-12-13', 'Salle A102 - Casablanca', 1, 'DEV-MOB'),
    ('SESS-2025-01', '2025-01-13', '2025-04-04', 'Salle A101 - Casablanca', 1, 'DEV-WEB');

-- 5. Étudiants (données marocaines)
INSERT INTO Etudiant (numCINEtu, nomEtu, prenomEtu, dateNaissEtu, villeEtu, telEtu) VALUES
    ('A1234567', 'Alaoui',   'Karim',    '2002-05-15', 'Casablanca', '0612345678'),
    ('B7654321', 'Bennani',  'Sara',     '2001-11-22', 'Rabat',      '0698765432'),
    ('C2468135', 'Chraibi',  'Youssef',  '2003-01-08', 'Marrakech',  '0635791246'),
    ('D1357924', 'Idrissi',  'Fatima',   '2002-08-30', 'Fès',        '0674513892'),
    ('E9876543', 'Mansouri', 'Amine',    '2001-03-17', 'Casablanca', '0656789012'),
    ('F3692581', 'Tazi',     'Zineb',    '2003-06-25', 'Tanger',     '0678901234');

-- 6. Inscriptions (FK vers Etudiant et Session)
INSERT INTO Inscription (numCINEtu, codeSess, noteInscription) VALUES
    ('A1234567', 'SESS-2024-01', 14.50),
    ('B7654321', 'SESS-2024-01', 16.00),
    ('C2468135', 'SESS-2024-01', 11.75),
    ('A1234567', 'SESS-2024-02', 13.25),
    ('D1357924', 'SESS-2024-02', 17.50),
    ('E9876543', 'SESS-2024-01', NULL),   -- Note pas encore saisie
    ('F3692581', 'SESS-2024-03', 15.00);

-- Fonctions de date utiles dans INSERT
INSERT INTO Session (codeSess, dateDebutSess, dateFinSess, lieuSess, numFormateur, codeForm)
VALUES (
    'SESS-2025-02',
    CURDATE(),                              -- Date du jour
    DATE_ADD(CURDATE(), INTERVAL 3 MONTH), -- Dans 3 mois
    'Salle C301 - Casablanca',
    2,
    'CPT-GEN'
);

-- Littéraux de date (format ISO 8601 recommandé)
INSERT INTO Etudiant (numCINEtu, nomEtu, prenomEtu, dateNaissEtu, villeEtu)
VALUES ('G1593570', 'Guessous', 'Imane', '2004-02-14', 'Agadir');
-- Format : 'AAAA-MM-JJ' pour DATE
-- Format : 'AAAA-MM-JJ HH:MM:SS' pour DATETIME

-- INSERT ... SELECT : copier des données depuis une autre table
INSERT INTO Formation (codeForm, titreForm, dureeForm, niveauForm, codeFiliere)
SELECT
    CONCAT('CPY-', codeForm),   -- Nouveau code préfixé
    CONCAT('[Archivé] ', titreForm),
    dureeForm,
    niveauForm,
    codeFiliere
FROM Formation
WHERE codeFiliere = 'INF';     -- Copier toutes les formations informatique

-- INSERT ... ON DUPLICATE KEY UPDATE : insérer ou mettre à jour
INSERT INTO Inscription (numCINEtu, codeSess, noteInscription)
VALUES ('A1234567', 'SESS-2024-01', 15.00)
ON DUPLICATE KEY UPDATE noteInscription = 15.00;
-- Si la PK (A1234567, SESS-2024-01) existe déjà : met à jour la note
-- Si elle n'existe pas : insère la nouvelle ligne

-- Compter les lignes affectées
-- INSERT normal    : 1 (insertion)
-- ON DUPLICATE KEY : 2 (mise à jour) ou 1 (insertion)
SELECT ROW_COUNT();

-- Vérifier d'abord ce qui sera modifié
SELECT * FROM Etudiant WHERE numCINEtu = 'A1234567';

-- UPDATE simple : corriger le téléphone d'un étudiant
UPDATE Etudiant
SET telEtu = '0612000001'
WHERE numCINEtu = 'A1234567';

-- UPDATE multi-colonnes : corriger plusieurs champs
UPDATE Etudiant
SET
    villeEtu  = 'Rabat',
    telEtu    = '0698001122'
WHERE numCINEtu = 'B7654321';

-- UPDATE avec expression : ajouter 1 point bonus à toutes les notes > 10
UPDATE Inscription
SET noteInscription = LEAST(noteInscription + 1, 20)
-- LEAST(x, 20) garantit que la note ne dépasse pas 20
WHERE noteInscription > 10 AND noteInscription IS NOT NULL;

-- UPDATE toutes les lignes (intentionnel : sans WHERE)
-- Désactiver tous les formateurs (mise en maintenance)
UPDATE Formateur SET actif = FALSE;
-- ROW_COUNT() donne le nombre de lignes modifiées
SELECT ROW_COUNT();

-- UPDATE avec JOIN : mettre à jour en se basant sur une autre table
UPDATE Inscription i
    JOIN Session s ON i.codeSess = s.codeSess
SET i.noteInscription = 0
WHERE s.dateFinSess < CURDATE()
  AND i.noteInscription IS NULL;
-- Mettre 0 pour les inscriptions sans note des sessions terminées

-- Utiliser une transaction pour annuler si erreur
START TRANSACTION;

UPDATE Inscription
SET noteInscription = 18.00
WHERE numCINEtu = 'D1357924' AND codeSess = 'SESS-2024-02';

-- Vérifier le résultat avant de valider
SELECT * FROM Inscription WHERE numCINEtu = 'D1357924';

-- Si correct : valider
COMMIT;
-- Si erreur : annuler
-- ROLLBACK;

-- Tester d'abord avec SELECT
SELECT * FROM Inscription WHERE codeSess = 'SESS-2024-03';

-- DELETE ciblé : supprimer les inscriptions d'une session
DELETE FROM Inscription
WHERE codeSess = 'SESS-2024-03';

-- DELETE avec LIMIT : supprimer au maximum N lignes (sécurité)
DELETE FROM Inscription
WHERE noteInscription IS NULL
LIMIT 10;   -- Ne supprime pas plus de 10 lignes même si plus correspondent

-- DELETE avec condition sur date
DELETE FROM Session
WHERE dateFinSess < '2023-01-01';  -- Sessions terminées avant 2023

-- Vérifier l'effet ON DELETE CASCADE
-- Si ON DELETE CASCADE est défini sur Inscription → Etudiant :
DELETE FROM Etudiant WHERE numCINEtu = 'A1234567';
-- → Supprime automatiquement toutes les inscriptions de A1234567

-- DELETE avec JOIN : supprimer en se basant sur une autre table
DELETE i
FROM Inscription i
    JOIN Session s ON i.codeSess = s.codeSess
WHERE s.codeForm = 'DEV-WEB'
  AND s.dateFinSess < '2024-01-01';
-- Supprimer toutes les inscriptions aux sessions DEV-WEB terminées avant 2024

-- Confirmer le nombre de lignes supprimées
SELECT ROW_COUNT();

-- DELETE tout (lent, annulable, garde AUTO_INCREMENT)
START TRANSACTION;
DELETE FROM Inscription;   -- Supprime toutes les lignes ligne par ligne
ROLLBACK;                  -- Annulation possible !
-- La table Inscription retrouve toutes ses lignes

-- TRUNCATE (rapide, irréversible, remet AUTO_INCREMENT à 1)
TRUNCATE TABLE Formateur;
-- Équivalent à DROP TABLE + CREATE TABLE mais plus rapide
-- ROLLBACK impossible après TRUNCATE
-- Échoue si une autre table a une FK vers Formateur

-- Stratégie pour vider toutes les tables (développement)
SET FOREIGN_KEY_CHECKS = 0;   -- Désactiver temporairement les FK
TRUNCATE TABLE Inscription;
TRUNCATE TABLE Session;
TRUNCATE TABLE Etudiant;
TRUNCATE TABLE Formation;
TRUNCATE TABLE Formateur;
TRUNCATE TABLE Filiere;
SET FOREIGN_KEY_CHECKS = 1;   -- Réactiver les FK
-- ⚠ UNIQUEMENT en développement pour réinitialiser les données de test

-- Structure complète (l'ordre d'écriture est OBLIGATOIRE)
SELECT   [DISTINCT] colonnes | expressions | *      -- 5. Quoi retourner
FROM     table                                       -- 1. Depuis quelle table
[WHERE   condition]                                  -- 2. Filtrer les lignes
[GROUP BY colonnes]                                  -- 3. Regrouper
[HAVING  condition_groupe]                           -- 4. Filtrer les groupes
[ORDER BY colonnes [ASC|DESC]]                       -- 6. Trier
[LIMIT   n [OFFSET m]];                             -- 7. Limiter

-- Ordre d'ÉVALUATION MySQL (différent de l'ordre d'écriture !)
-- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
-- Important : on ne peut pas utiliser un alias défini dans SELECT dans WHERE
-- (WHERE est évalué avant SELECT)

USE skolabase;

-- Toutes les colonnes (déconseillé en production)
SELECT * FROM Etudiant;

-- Colonnes spécifiques
SELECT numCINEtu, nomEtu, prenomEtu FROM Etudiant;

-- Alias avec AS (renommer le résultat)
SELECT
    numCINEtu           AS cin,
    nomEtu              AS nom,
    prenomEtu           AS prenom,
    villeEtu            AS ville
FROM Etudiant;

-- Alias sans AS (syntaxe courte)
SELECT nomEtu nom, prenomEtu prenom FROM Etudiant;

-- Colonne calculée avec alias
SELECT
    numCINEtu,
    CONCAT(prenomEtu, ' ', nomEtu) AS nom_complet,
    YEAR(CURDATE()) - YEAR(dateNaissEtu) AS age_approx
FROM Etudiant;

-- DISTINCT : éliminer les doublons
SELECT DISTINCT villeEtu FROM Etudiant;
-- Retourne chaque ville une seule fois

SELECT DISTINCT codeFiliere FROM Formation;
-- Toutes les filières utilisées

-- DISTINCT sur plusieurs colonnes (combinaison unique)
SELECT DISTINCT villeEtu, nomEtu FROM Etudiant;
-- Lignes uniques (ville + nom)

-- Comparaison simple
SELECT * FROM Etudiant WHERE villeEtu = 'Casablanca';
SELECT * FROM Inscription WHERE noteInscription >= 14;
SELECT * FROM Formation WHERE dureeForm != 120;   -- != ou <>

-- AND / OR (AND a priorité sur OR)
SELECT * FROM Etudiant
WHERE villeEtu = 'Casablanca' AND nomEtu = 'Alaoui';

SELECT * FROM Etudiant
WHERE villeEtu = 'Casablanca' OR villeEtu = 'Rabat';

-- Parenthèses pour forcer la priorité
SELECT * FROM Etudiant
WHERE (villeEtu = 'Casablanca' OR villeEtu = 'Rabat')
  AND nomEtu LIKE 'A%';

-- NOT : inverser une condition
SELECT * FROM Inscription WHERE NOT noteInscription >= 14;
-- Équivalent à : WHERE noteInscription < 14

-- BETWEEN : plage inclusive (inclut les bornes)
SELECT * FROM Inscription
WHERE noteInscription BETWEEN 10 AND 15;
-- Équivalent à : noteInscription >= 10 AND noteInscription <= 15

SELECT * FROM Session
WHERE dateDebutSess BETWEEN '2024-01-01' AND '2024-12-31';

-- IN : liste de valeurs
SELECT * FROM Etudiant
WHERE villeEtu IN ('Casablanca', 'Rabat', 'Marrakech');
-- Plus lisible que OR multiples

SELECT * FROM Formation
WHERE niveauForm IN ('Bac+2', 'Bac+3');

-- NOT IN : exclure une liste
SELECT * FROM Formation
WHERE codeFiliere NOT IN ('GES', 'ELE');

-- LIKE : recherche par motif
-- % = n'importe quelle suite de caractères (0 ou plus)
-- _ = exactement 1 caractère
SELECT * FROM Etudiant WHERE nomEtu LIKE 'A%';       -- Commence par A
SELECT * FROM Etudiant WHERE nomEtu LIKE '%ani';     -- Finit par ani
SELECT * FROM Etudiant WHERE nomEtu LIKE '%ala%';    -- Contient ala
SELECT * FROM Etudiant WHERE numCINEtu LIKE 'A______'; -- A + 6 chars
SELECT * FROM Formation WHERE titreForm LIKE '%Web%';

-- IS NULL / IS NOT NULL (JAMAIS = NULL)
SELECT * FROM Inscription WHERE noteInscription IS NULL;
SELECT * FROM Etudiant WHERE telEtu IS NOT NULL;
-- Erreur classique : WHERE noteInscription = NULL (ne retourne RIEN)

-- ORDER BY simple (ASC par défaut)
SELECT * FROM Etudiant ORDER BY nomEtu ASC;
SELECT * FROM Etudiant ORDER BY nomEtu;       -- ASC implicite

-- ORDER BY décroissant
SELECT * FROM Inscription ORDER BY noteInscription DESC;

-- ORDER BY multi-colonnes (1ère colonne prioritaire)
SELECT * FROM Etudiant
ORDER BY villeEtu ASC, nomEtu ASC;
-- Trier par ville, puis par nom dans chaque ville

SELECT * FROM Inscription
ORDER BY noteInscription DESC, numCINEtu ASC;

-- ORDER BY sur alias (ORDER BY est évalué après SELECT)
SELECT nomEtu, prenomEtu,
       CONCAT(prenomEtu, ' ', nomEtu) AS nom_complet
FROM Etudiant
ORDER BY nom_complet ASC;   -- L'alias est utilisable dans ORDER BY

-- LIMIT : retourner seulement N lignes
SELECT * FROM Etudiant LIMIT 3;            -- Les 3 premières lignes
SELECT * FROM Inscription
ORDER BY noteInscription DESC LIMIT 5;     -- Top 5 notes

-- LIMIT + OFFSET : pagination
-- Page 1 : LIMIT 3 OFFSET 0  (lignes 1-3)
-- Page 2 : LIMIT 3 OFFSET 3  (lignes 4-6)
-- Page 3 : LIMIT 3 OFFSET 6  (lignes 7-9)
SELECT * FROM Etudiant
ORDER BY nomEtu
LIMIT 3 OFFSET 0;    -- Page 1

SELECT * FROM Etudiant
ORDER BY nomEtu
LIMIT 3 OFFSET 3;    -- Page 2

-- Syntaxe alternative : LIMIT offset, count
SELECT * FROM Etudiant ORDER BY nomEtu LIMIT 3, 3;  -- Offset 3, count 3

-- ORDER BY personnalisé avec FIELD()
SELECT * FROM Formation
ORDER BY FIELD(niveauForm, 'Technicien', 'Bac', 'Bac+2', 'Bac+3');
-- Trie dans l'ordre spécifié (pas alphabétique)

-- Nombre d'étudiants par ville
SELECT villeEtu, COUNT(*) AS nb_etudiants
FROM Etudiant
GROUP BY villeEtu;
-- Résultat :
-- villeEtu    | nb_etudiants
-- Casablanca  | 2
-- Rabat       | 1
-- Marrakech   | 1

-- Moyenne des notes par session
SELECT
    codeSess,
    COUNT(*)                                AS nb_inscrits,
    COUNT(noteInscription)                  AS nb_notes_saisies,
    AVG(noteInscription)                    AS moyenne,
    MIN(noteInscription)                    AS note_min,
    MAX(noteInscription)                    AS note_max
FROM Inscription
GROUP BY codeSess;

-- Nombre de formations par filière
SELECT
    f.libelleFiliere,
    COUNT(fm.codeForm)  AS nb_formations,
    SUM(fm.dureeForm)   AS heures_totales
FROM Filiere f
    LEFT JOIN Formation fm ON f.codeFiliere = fm.codeFiliere
GROUP BY f.codeFiliere, f.libelleFiliere
ORDER BY nb_formations DESC;

-- GROUP BY avec expression (regrouper par année de début)
SELECT
    YEAR(dateDebutSess)  AS annee,
    COUNT(*)             AS nb_sessions
FROM Session
GROUP BY YEAR(dateDebutSess)
ORDER BY annee;

-- Sessions avec plus de 2 inscrits
SELECT codeSess, COUNT(*) AS nb_inscrits
FROM Inscription
GROUP BY codeSess
HAVING COUNT(*) > 2;

-- Étudiants inscrits à plus d'une session
SELECT numCINEtu, COUNT(*) AS nb_sessions
FROM Inscription
GROUP BY numCINEtu
HAVING COUNT(*) > 1;

-- Combinaison WHERE + GROUP BY + HAVING
-- Sessions de 2024 avec moyenne > 13
SELECT
    codeSess,
    AVG(noteInscription) AS moyenne
FROM Inscription
WHERE noteInscription IS NOT NULL      -- WHERE : exclure les notes NULL d'abord
GROUP BY codeSess                      -- GROUP BY : regrouper par session
HAVING AVG(noteInscription) > 13       -- HAVING : garder seulement moyenne > 13
ORDER BY moyenne DESC;

-- WRONG : ne pas mettre une fonction d'agrégation dans WHERE
-- SELECT codeSess FROM Inscription WHERE AVG(noteInscription) > 13;  -- ERREUR
-- La bonne version : utiliser HAVING

-- Filières avec plus de 2 formations de niveau Bac+2
SELECT
    codeFiliere,
    COUNT(*)    AS nb_formations_bac2
FROM Formation
WHERE niveauForm = 'Bac+2'             -- WHERE filtre les lignes individuelles
GROUP BY codeFiliere
HAVING COUNT(*) >= 2;                  -- HAVING filtre les groupes résultants

-- WITH ROLLUP : sous-totaux et total général
SELECT
    COALESCE(villeEtu, 'TOTAL')  AS ville,
    COUNT(*)                      AS nb_etudiants
FROM Etudiant
GROUP BY villeEtu WITH ROLLUP;
-- Ajoute une ligne TOTAL à la fin

-- ABS : valeur absolue
SELECT ABS(-14.5);          -- 14.5
SELECT ABS(noteInscription - 10) AS ecart_au_seuil
FROM Inscription;

-- CEIL / CEILING : arrondir vers le haut (entier supérieur)
SELECT CEIL(13.1);          -- 14
SELECT CEIL(13.0);          -- 13

-- FLOOR : arrondir vers le bas (entier inférieur)
SELECT FLOOR(13.9);         -- 13

-- ROUND : arrondir au plus proche (n décimales)
SELECT ROUND(13.456, 2);    -- 13.46
SELECT ROUND(13.454, 2);    -- 13.45
SELECT ROUND(13.5);         -- 14  (0 décimales)

-- TRUNCATE : tronquer sans arrondir
SELECT TRUNCATE(13.999, 2); -- 13.99  (pas d'arrondi)
SELECT TRUNCATE(13.999, 0); -- 13

-- MOD : modulo (reste de la division)
SELECT MOD(17, 5);          -- 2
SELECT MOD(numFormateur, 2) AS parite FROM Formateur;
-- 0 = pair, 1 = impair

-- POWER / POW : puissance
SELECT POWER(2, 10);        -- 1024
-- SQRT : racine carrée
SELECT SQRT(144);           -- 12

-- Exemple concret : arrondir les moyennes à 2 décimales
SELECT
    codeSess,
    ROUND(AVG(noteInscription), 2) AS moyenne_arrondie
FROM Inscription
WHERE noteInscription IS NOT NULL
GROUP BY codeSess;

-- CONCAT : concaténer des chaînes
SELECT CONCAT(prenomEtu, ' ', nomEtu) AS nom_complet FROM Etudiant;
SELECT CONCAT('ETU-', numCINEtu) AS code_etudiant FROM Etudiant;

-- CONCAT_WS : concaténer avec séparateur (WS = With Separator)
SELECT CONCAT_WS(' - ', nomEtu, prenomEtu, villeEtu) AS fiche FROM Etudiant;
-- Alaoui - Karim - Casablanca

-- LENGTH : longueur en octets (CHAR_LENGTH pour les caractères)
SELECT nomEtu, LENGTH(nomEtu) AS longueur FROM Etudiant;
SELECT nomEtu, CHAR_LENGTH(nomEtu) AS nb_chars FROM Etudiant;
-- LENGTH != CHAR_LENGTH pour les caractères UTF-8 multi-octets (ç, é, ب...)

-- UPPER / LOWER : majuscules / minuscules
SELECT UPPER(nomEtu) AS nom_maj FROM Etudiant;   -- ALAOUI
SELECT LOWER(nomEtu) AS nom_min FROM Etudiant;   -- alaoui

-- TRIM / LTRIM / RTRIM : supprimer les espaces
SELECT TRIM('  Karim  ');        -- 'Karim'
SELECT LTRIM('  Karim');         -- 'Karim' (espaces gauche)
SELECT RTRIM('Karim  ');         -- 'Karim' (espaces droite)

-- LEFT / RIGHT : extraire N caractères à gauche/droite
SELECT LEFT(numCINEtu, 1)  AS lettre_cin FROM Etudiant;    -- 'A', 'B', 'C'...
SELECT RIGHT(numCINEtu, 7) AS chiffres_cin FROM Etudiant;  -- '1234567'

-- SUBSTRING / SUBSTR : extraire une sous-chaîne
SELECT SUBSTRING(numCINEtu, 2, 7) AS chiffres FROM Etudiant;
-- Depuis position 2, 7 caractères

SELECT SUBSTRING(codeSess, 6, 4) AS annee_sess FROM Session;
-- 'SESS-2024-01' → '2024'

-- REPLACE : remplacer une sous-chaîne
SELECT REPLACE(titreForm, 'Développement', 'Dev') FROM Formation;
SELECT REPLACE(nomEtu, 'El ', '') AS nom_court FROM Etudiant;

-- LPAD / RPAD : compléter avec un caractère
SELECT LPAD(numFormateur, 4, '0') AS code_form FROM Formateur;
-- 1 → '0001',  12 → '0012'

-- LOCATE / POSITION : trouver la position d'une sous-chaîne
SELECT LOCATE('Web', titreForm) AS pos_web FROM Formation;
-- 0 = non trouvé, > 0 = position

-- INSTR : similaire à LOCATE
SELECT titreForm FROM Formation WHERE INSTR(titreForm, 'Web') > 0;

-- Date et heure actuelles
SELECT CURDATE();               -- '2024-11-15'  (date seule)
SELECT CURTIME();               -- '14:23:05'    (heure seule)
SELECT NOW();                   -- '2024-11-15 14:23:05'
SELECT SYSDATE();               -- Similaire à NOW()

-- Extraire des parties de date
SELECT
    dateNaissEtu,
    YEAR(dateNaissEtu)   AS annee,
    MONTH(dateNaissEtu)  AS mois,
    DAY(dateNaissEtu)    AS jour,
    DAYNAME(dateNaissEtu) AS jour_semaine,   -- 'Monday', 'Tuesday'...
    MONTHNAME(dateNaissEtu) AS nom_mois      -- 'January', 'February'...
FROM Etudiant;

-- DATEDIFF : différence en jours
SELECT
    codeSess,
    dateDebutSess,
    dateFinSess,
    DATEDIFF(dateFinSess, dateDebutSess) AS duree_jours
FROM Session;

-- Âge en années avec TIMESTAMPDIFF
SELECT
    numCINEtu,
    CONCAT(prenomEtu, ' ', nomEtu) AS nom_complet,
    TIMESTAMPDIFF(YEAR, dateNaissEtu, CURDATE()) AS age
FROM Etudiant
ORDER BY age;

-- DATE_ADD / DATE_SUB : ajouter/soustraire un intervalle
SELECT DATE_ADD(CURDATE(), INTERVAL 30 DAY)  AS dans_30_jours;
SELECT DATE_ADD(CURDATE(), INTERVAL 3 MONTH) AS dans_3_mois;
SELECT DATE_SUB(CURDATE(), INTERVAL 1 YEAR)  AS il_y_a_1_an;

-- Intervalles disponibles : SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR

-- DATE_FORMAT : formater une date
SELECT DATE_FORMAT(dateDebutSess, '%d/%m/%Y') AS date_fr
FROM Session;
-- '02/09/2024'

SELECT DATE_FORMAT(dateDebutSess, '%W %d %M %Y') AS date_longue
FROM Session;
-- 'Monday 02 September 2024'

SELECT DATE_FORMAT(dateDebutSess, '%Y-%m') AS annee_mois
FROM Session
GROUP BY DATE_FORMAT(dateDebutSess, '%Y-%m');
-- Grouper par mois : '2024-09', '2024-10'...

-- WEEK / WEEKOFYEAR : numéro de semaine
SELECT codeSess, WEEK(dateDebutSess) AS semaine FROM Session;

-- Sessions dont la date de fin est dépassée
SELECT codeSess, dateFinSess
FROM Session
WHERE dateFinSess < CURDATE();

-- Sessions en cours aujourd'hui
SELECT codeSess, dateDebutSess, dateFinSess
FROM Session
WHERE CURDATE() BETWEEN dateDebutSess AND dateFinSess;

-- COUNT(*) : toutes les lignes (y compris NULL)
SELECT COUNT(*) AS total_inscriptions FROM Inscription;   -- 7

-- COUNT(colonne) : ignorer les NULL
SELECT COUNT(noteInscription) AS notes_saisies FROM Inscription;  -- 6

-- Voir la différence
SELECT
    COUNT(*)                AS total_inscrits,
    COUNT(noteInscription)  AS notes_saisies,
    COUNT(*) - COUNT(noteInscription) AS notes_manquantes
FROM Inscription;

-- COUNT(DISTINCT) : compter les valeurs uniques
SELECT COUNT(DISTINCT numCINEtu) AS nb_etudiants_inscrits
FROM Inscription;
-- Nombre d'étudiants différents qui ont au moins une inscription

SELECT COUNT(DISTINCT villeEtu) AS nb_villes FROM Etudiant;

-- Statistiques complètes par session
SELECT
    codeSess,
    COUNT(*)                            AS nb_inscrits,
    COUNT(noteInscription)              AS notes_saisies,
    COUNT(*) - COUNT(noteInscription)   AS notes_manquantes,
    ROUND(AVG(noteInscription), 2)      AS moyenne,
    MIN(noteInscription)                AS note_min,
    MAX(noteInscription)                AS note_max,
    SUM(noteInscription)                AS total_notes
FROM Inscription
GROUP BY codeSess
ORDER BY codeSess;

-- Toutes les fonctions ignorent automatiquement les NULL
SELECT
    SUM(noteInscription)    AS somme,     -- Somme des notes non-NULL
    AVG(noteInscription)    AS moyenne,   -- Moyenne des notes non-NULL
    MIN(noteInscription)    AS note_min,
    MAX(noteInscription)    AS note_max
FROM Inscription;

-- MIN / MAX sur des chaînes (ordre alphabétique)
SELECT MIN(nomEtu) AS premier_alpha, MAX(nomEtu) AS dernier_alpha
FROM Etudiant;

-- MIN / MAX sur des dates
SELECT
    MIN(dateDebutSess) AS premiere_session,
    MAX(dateFinSess)   AS derniere_fin
FROM Session;

-- Total des heures de formation par filière
SELECT
    fi.libelleFiliere,
    SUM(fo.dureeForm)   AS total_heures,
    AVG(fo.dureeForm)   AS duree_moyenne,
    COUNT(fo.codeForm)  AS nb_formations
FROM Filiere fi
    JOIN Formation fo ON fi.codeFiliere = fo.codeFiliere
GROUP BY fi.codeFiliere, fi.libelleFiliere
ORDER BY total_heures DESC;

-- Taux de réussite par session (note >= 10)
SELECT
    codeSess,
    COUNT(*) AS total,
    SUM(CASE WHEN noteInscription >= 10 THEN 1 ELSE 0 END) AS admis,
    ROUND(100 * SUM(CASE WHEN noteInscription >= 10 THEN 1 ELSE 0 END)
          / COUNT(noteInscription), 1) AS taux_reussite_pct
FROM Inscription
WHERE noteInscription IS NOT NULL
GROUP BY codeSess;

-- Opérateurs de base
SELECT 10 + 3;    -- 13   addition
SELECT 10 - 3;    -- 7    soustraction
SELECT 10 * 3;    -- 30   multiplication
SELECT 10 / 3;    -- 3.33 division réelle (DECIMAL)
SELECT 10 DIV 3;  -- 3    division entière
SELECT 10 MOD 3;  -- 1    modulo (reste)
SELECT 10 % 3;    -- 1    alias de MOD

-- Priorité : * / avant + - (comme en mathématiques)
SELECT 2 + 3 * 4;     -- 14  (pas 20)
SELECT (2 + 3) * 4;   -- 20

-- Applications SkolaBase
SELECT
    codeSess,
    dureeForm,
    dureeForm * 60        AS duree_minutes,
    dureeForm / 8         AS duree_jours,
    ROUND(dureeForm / 8)  AS duree_jours_arrondis
FROM Session
    JOIN Formation USING (codeForm);

-- Bonus de 5% sur les notes < 14, plafonné à 20
SELECT
    numCINEtu,
    noteInscription AS note_initiale,
    LEAST(noteInscription * 1.05, 20) AS note_avec_bonus
FROM Inscription
WHERE noteInscription < 14 AND noteInscription IS NOT NULL;

-- CASE recherché (condition booléenne — le plus flexible)
SELECT
    numCINEtu,
    codeSess,
    noteInscription,
    CASE
        WHEN noteInscription IS NULL    THEN 'Non évalué'
        WHEN noteInscription >= 18      THEN 'Excellent'
        WHEN noteInscription >= 14      THEN 'Bien'
        WHEN noteInscription >= 12      THEN 'Assez bien'
        WHEN noteInscription >= 10      THEN 'Passable'
        ELSE                                 'Insuffisant'
    END AS mention
FROM Inscription;

-- CASE simple (comparer une colonne à des valeurs)
SELECT
    codeFiliere,
    CASE codeFiliere
        WHEN 'INF' THEN 'Informatique'
        WHEN 'GES' THEN 'Gestion'
        WHEN 'ELE' THEN 'Électronique'
        ELSE             'Autre filière'
    END AS libelle_court
FROM Formation;

-- CASE dans GROUP BY / HAVING (classifier puis compter)
SELECT
    CASE
        WHEN noteInscription >= 10 THEN 'Admis'
        ELSE 'Refusé'
    END AS statut,
    COUNT(*) AS nb
FROM Inscription
WHERE noteInscription IS NOT NULL
GROUP BY statut;

-- IF() : version courte pour 2 cas seulement
SELECT
    numCINEtu,
    noteInscription,
    IF(noteInscription >= 10, 'Admis', 'Refusé') AS decision
FROM Inscription;

-- IF() dans SUM pour compter conditionnellement
SELECT
    SUM(IF(noteInscription >= 10, 1, 0))    AS nb_admis,
    SUM(IF(noteInscription < 10, 1, 0))     AS nb_refusés,
    COUNT(noteInscription)                   AS total_notes
FROM Inscription;

-- COALESCE(a, b, c) : retourne la première valeur non-NULL
SELECT
    numCINEtu,
    COALESCE(telEtu, 'Téléphone non renseigné') AS contact
FROM Etudiant;
-- Si telEtu = NULL → 'Téléphone non renseigné'
-- Si telEtu = '0612345678' → '0612345678'

-- COALESCE avec plusieurs alternatives
SELECT
    COALESCE(telEtu, emailEtu, numCINEtu) AS contact_prioritaire
FROM Etudiant;
-- Essaie tel, puis email, puis CIN (toujours non-NULL)

-- Afficher les notes avec valeur par défaut
SELECT
    numCINEtu,
    codeSess,
    COALESCE(noteInscription, 0)  AS note_affichee   -- 0 si NULL
FROM Inscription;

-- IFNULL(col, valeur_si_null) : version MySQL de COALESCE à 2 args
SELECT
    numCINEtu,
    IFNULL(noteInscription, 'À évaluer') AS note
FROM Inscription;

-- NULLIF(a, b) : retourne NULL si a = b, sinon retourne a
-- Utilité : éviter la division par zéro
SELECT
    codeSess,
    SUM(noteInscription) / NULLIF(COUNT(noteInscription), 0) AS moyenne_securisee
FROM Inscription
GROUP BY codeSess;
-- Si COUNT = 0 (aucune note) → NULLIF retourne NULL → division évitée

-- Exemple : transformer 0 en NULL (ignorer les 0 dans une moyenne)
SELECT AVG(NULLIF(noteInscription, 0)) AS moyenne_sans_zeros
FROM Inscription;

-- Position 1 : dans le SELECT (sous-requête scalaire)
SELECT
    nomEtu,
    (SELECT COUNT(*) FROM Inscription WHERE numCINEtu = e.numCINEtu) AS nb_inscriptions
FROM Etudiant e;

-- Position 2 : dans le FROM (table dérivée — doit avoir un alias)
SELECT *
FROM (
    SELECT codeSess, AVG(noteInscription) AS moyenne
    FROM Inscription
    WHERE noteInscription IS NOT NULL
    GROUP BY codeSess
) AS stats_sessions
WHERE moyenne > 13;

-- Position 3 : dans le WHERE (la plus courante)
SELECT * FROM Etudiant
WHERE numCINEtu IN (
    SELECT numCINEtu FROM Inscription WHERE noteInscription >= 14
);

-- Position 4 : dans le HAVING
SELECT codeSess, AVG(noteInscription) AS moy
FROM Inscription
GROUP BY codeSess
HAVING AVG(noteInscription) > (
    SELECT AVG(noteInscription) FROM Inscription
);

-- Étudiants dont la note > moyenne générale
SELECT numCINEtu, noteInscription
FROM Inscription
WHERE noteInscription > (
    SELECT AVG(noteInscription)
    FROM Inscription
    WHERE noteInscription IS NOT NULL
);

-- Session avec la durée de formation la plus longue
SELECT codeSess, dateDebutSess
FROM Session
WHERE codeForm = (
    SELECT codeForm
    FROM Formation
    WHERE dureeForm = (SELECT MAX(dureeForm) FROM Formation)
);

-- Étudiant avec la meilleure note (unique)
SELECT e.numCINEtu, e.nomEtu, e.prenomEtu, i.noteInscription
FROM Etudiant e
    JOIN Inscription i ON e.numCINEtu = i.numCINEtu
WHERE i.noteInscription = (
    SELECT MAX(noteInscription) FROM Inscription
);

-- Formateur qui anime la session la plus récente
SELECT nomFormateur, prenomFormateur
FROM Formateur
WHERE numFormateur = (
    SELECT numFormateur
    FROM Session
    ORDER BY dateDebutSess DESC
    LIMIT 1
);

-- IN : étudiants qui ont au moins une inscription
SELECT numCINEtu, nomEtu, prenomEtu
FROM Etudiant
WHERE numCINEtu IN (
    SELECT DISTINCT numCINEtu FROM Inscription
);

-- NOT IN : étudiants sans aucune inscription
SELECT numCINEtu, nomEtu, prenomEtu
FROM Etudiant
WHERE numCINEtu NOT IN (
    SELECT DISTINCT numCINEtu FROM Inscription
);

-- Formations qui ont au moins une session
SELECT codeForm, titreForm
FROM Formation
WHERE codeForm IN (
    SELECT DISTINCT codeForm FROM Session
);

-- Sessions avec note moyenne > 13
SELECT codeSess, dateDebutSess
FROM Session
WHERE codeSess IN (
    SELECT codeSess
    FROM Inscription
    WHERE noteInscription IS NOT NULL
    GROUP BY codeSess
    HAVING AVG(noteInscription) > 13
);

-- Table dérivée : les 3 meilleures sessions par moyenne
SELECT s.codeSess, s.dateDebutSess, stats.moyenne
FROM Session s
    JOIN (
        SELECT codeSess, ROUND(AVG(noteInscription), 2) AS moyenne
        FROM Inscription
        WHERE noteInscription IS NOT NULL
        GROUP BY codeSess
    ) AS stats ON s.codeSess = stats.codeSess
ORDER BY stats.moyenne DESC
LIMIT 3;

-- ALL : vrai si la comparaison est vraie pour TOUTES les valeurs
-- Étudiants dont la note est > TOUTES les notes de la session SESS-2024-02
SELECT numCINEtu, noteInscription
FROM Inscription
WHERE codeSess = 'SESS-2024-01'
  AND noteInscription > ALL (
      SELECT noteInscription
      FROM Inscription
      WHERE codeSess = 'SESS-2024-02'
        AND noteInscription IS NOT NULL
  );
-- Revient à : note > MAX(notes de SESS-2024-02)

-- Formations dont la durée est > TOUTES les formations de filière GES
SELECT codeForm, titreForm, dureeForm
FROM Formation
WHERE dureeForm > ALL (
    SELECT dureeForm FROM Formation WHERE codeFiliere = 'GES'
);
-- Équivalent à : dureeForm > MAX(durée des formations GES)

-- ANY : vrai si la comparaison est vraie pour AU MOINS UNE valeur
-- Étudiants dont la note est > n'importe quelle note de SESS-2024-02
SELECT numCINEtu, noteInscription
FROM Inscription
WHERE codeSess = 'SESS-2024-01'
  AND noteInscription > ANY (
      SELECT noteInscription
      FROM Inscription
      WHERE codeSess = 'SESS-2024-02'
        AND noteInscription IS NOT NULL
  );
-- Revient à : note > MIN(notes de SESS-2024-02)

-- EXISTS : étudiants qui ont AU MOINS UNE inscription
SELECT numCINEtu, nomEtu, prenomEtu
FROM Etudiant e
WHERE EXISTS (
    SELECT 1
    FROM Inscription i
    WHERE i.numCINEtu = e.numCINEtu   -- référence à la requête externe (corrélée)
);
-- MySQL arrête dès la 1ère inscription trouvée → très rapide

-- NOT EXISTS : étudiants SANS aucune inscription
SELECT numCINEtu, nomEtu, prenomEtu
FROM Etudiant e
WHERE NOT EXISTS (
    SELECT 1
    FROM Inscription i
    WHERE i.numCINEtu = e.numCINEtu
);
-- Avantage sur NOT IN : pas de piège NULL

-- Formateurs qui n'ont animé AUCUNE session
SELECT numFormateur, nomFormateur
FROM Formateur f
WHERE NOT EXISTS (
    SELECT 1 FROM Session s
    WHERE s.numFormateur = f.numFormateur
);

-- Sessions qui n'ont aucun inscrit
SELECT codeSess, dateDebutSess
FROM Session s
WHERE NOT EXISTS (
    SELECT 1 FROM Inscription i
    WHERE i.codeSess = s.codeSess
);

-- Étudiants inscrits à TOUTES les sessions de 2024 (division relationnelle)
-- Formulation : étudiants pour lesquels il n'existe PAS de session 2024
-- à laquelle ils ne sont PAS inscrits
SELECT numCINEtu, nomEtu
FROM Etudiant e
WHERE NOT EXISTS (
    SELECT codeSess
    FROM Session
    WHERE YEAR(dateDebutSess) = 2024
    AND codeSess NOT IN (
        SELECT codeSess FROM Inscription
        WHERE numCINEtu = e.numCINEtu
    )
);

-- Pour chaque étudiant, compter ses inscriptions (sous-requête corrélée)
SELECT
    e.numCINEtu,
    e.nomEtu,
    (SELECT COUNT(*)
     FROM Inscription i
     WHERE i.numCINEtu = e.numCINEtu) AS nb_inscriptions
FROM Etudiant e;
-- La sous-requête est exécutée une fois par étudiant

-- Pour chaque étudiant, afficher sa meilleure note
SELECT
    e.numCINEtu,
    e.nomEtu,
    (SELECT MAX(noteInscription)
     FROM Inscription i
     WHERE i.numCINEtu = e.numCINEtu) AS meilleure_note
FROM Etudiant e;

-- Étudiants dont la note dans SESS-2024-01 est supérieure à leur propre moyenne
SELECT numCINEtu, noteInscription AS note_sess01
FROM Inscription i1
WHERE codeSess = 'SESS-2024-01'
  AND noteInscription > (
      SELECT AVG(noteInscription)
      FROM Inscription i2
      WHERE i2.numCINEtu = i1.numCINEtu   -- corrélation : même étudiant
        AND noteInscription IS NOT NULL
  );

-- UNION : combiner noms de toutes les personnes (sans doublons)
SELECT nomFormateur AS nom, prenomFormateur AS prenom, 'Formateur' AS role
FROM Formateur
UNION
SELECT nomEtu, prenomEtu, 'Etudiant'
FROM Etudiant;
-- Les noms des colonnes viennent du 1er SELECT
-- Les doublons (même nom+prénom) sont supprimés

-- UNION ALL : garder tous (avec doublons potentiels, plus rapide)
SELECT nomFormateur AS nom, 'Formateur' AS type FROM Formateur
UNION ALL
SELECT nomEtu, 'Etudiant' FROM Etudiant;
-- Plus rapide car pas de déduplication

-- ORDER BY sur le résultat global (placer à la fin)
SELECT nomEtu AS nom, villeEtu AS ville FROM Etudiant
UNION ALL
SELECT nomFormateur, 'Casablanca' FROM Formateur
ORDER BY nom ASC;    -- Trie TOUT le résultat

-- Rapport unifié : toutes les activités de 2024
SELECT 'Inscription' AS type_activite, numCINEtu AS reference,
       codeSess AS detail, NULL AS note
FROM Inscription
WHERE codeSess LIKE '%2024%'
UNION ALL
SELECT 'Session', codeSess, codeForm, NULL
FROM Session
WHERE YEAR(dateDebutSess) = 2024
ORDER BY type_activite, reference;

-- INTERSECT natif (MySQL 8.0.31+)
-- Étudiants inscrits à SESS-2024-01 ET à SESS-2024-02
SELECT numCINEtu FROM Inscription WHERE codeSess = 'SESS-2024-01'
INTERSECT
SELECT numCINEtu FROM Inscription WHERE codeSess = 'SESS-2024-02';

-- Émulation INTERSECT avec IN (compatible toutes versions MySQL)
SELECT DISTINCT numCINEtu
FROM Inscription
WHERE codeSess = 'SESS-2024-01'
  AND numCINEtu IN (
      SELECT numCINEtu FROM Inscription WHERE codeSess = 'SESS-2024-02'
  );

-- EXCEPT natif (MySQL 8.0.31+)
-- Étudiants inscrits à SESS-2024-01 MAIS PAS à SESS-2024-02
SELECT numCINEtu FROM Inscription WHERE codeSess = 'SESS-2024-01'
EXCEPT
SELECT numCINEtu FROM Inscription WHERE codeSess = 'SESS-2024-02';

-- Émulation EXCEPT avec NOT IN
SELECT DISTINCT numCINEtu
FROM Inscription
WHERE codeSess = 'SESS-2024-01'
  AND numCINEtu NOT IN (
      SELECT numCINEtu
      FROM Inscription
      WHERE codeSess = 'SESS-2024-02'
  );

-- Émulation EXCEPT avec LEFT JOIN (plus performant sur grandes tables)
SELECT DISTINCT i1.numCINEtu
FROM Inscription i1
    LEFT JOIN Inscription i2
        ON i1.numCINEtu = i2.numCINEtu AND i2.codeSess = 'SESS-2024-02'
WHERE i1.codeSess = 'SESS-2024-01'
  AND i2.numCINEtu IS NULL;   -- IS NULL = pas de correspondance à droite

   UNION                  INTERSECT              EXCEPT (A - B)
  A ┌───┐B            A ┌───┐B            A ┌───┐B
  ████████             ░░░██░░             ███░░░░
Tout A + tout B       Seulement            Seulement dans A,
(sans doublons)       l'intersection       pas dans B

-- INNER JOIN basique : étudiants avec leurs notes
SELECT
    e.numCINEtu,
    e.nomEtu,
    e.prenomEtu,
    i.codeSess,
    i.noteInscription
FROM Etudiant e
    INNER JOIN Inscription i ON e.numCINEtu = i.numCINEtu;
-- Seuls les étudiants AVEC au moins une inscription apparaissent
-- Les étudiants sans inscription sont exclus

-- Syntaxe USING (quand les colonnes ont le même nom)
SELECT e.nomEtu, i.noteInscription
FROM Etudiant e
    INNER JOIN Inscription i USING (numCINEtu);
-- Equivalent à ON e.numCINEtu = i.numCINEtu

-- JOIN sans INNER (INNER est implicite)
SELECT e.nomEtu, i.noteInscription
FROM Etudiant e
    JOIN Inscription i ON e.numCINEtu = i.numCINEtu;

-- Jointure sur 3 tables : étudiant + inscription + session
SELECT
    e.nomEtu,
    e.prenomEtu,
    s.codeSess,
    s.dateDebutSess,
    i.noteInscription
FROM Etudiant e
    JOIN Inscription i ON e.numCINEtu = i.numCINEtu
    JOIN Session s     ON i.codeSess  = s.codeSess;

-- Jointure sur 5 tables : parcours complet
SELECT
    e.nomEtu,
    e.prenomEtu,
    fo.titreForm,
    fi.libelleFiliere,
    CONCAT(fmt.prenomFormateur, ' ', fmt.nomFormateur) AS formateur,
    i.noteInscription
FROM Etudiant e
    JOIN Inscription i ON e.numCINEtu  = i.numCINEtu
    JOIN Session s     ON i.codeSess   = s.codeSess
    JOIN Formation fo  ON s.codeForm   = fo.codeForm
    JOIN Filiere fi    ON fo.codeFiliere = fi.codeFiliere
    JOIN Formateur fmt ON s.numFormateur = fmt.numFormateur
ORDER BY e.nomEtu, fo.titreForm;

-- LEFT JOIN : TOUS les étudiants, avec ou sans inscription
SELECT
    e.numCINEtu,
    e.nomEtu,
    e.prenomEtu,
    i.codeSess,           -- NULL si pas d'inscription
    i.noteInscription     -- NULL si pas d'inscription
FROM Etudiant e
    LEFT JOIN Inscription i ON e.numCINEtu = i.numCINEtu;
-- TOUS les étudiants apparaissent, y compris ceux sans inscription

-- Compter les inscriptions par étudiant (y compris 0)
SELECT
    e.numCINEtu,
    e.nomEtu,
    COUNT(i.codeSess) AS nb_inscriptions   -- COUNT ignore les NULL
FROM Etudiant e
    LEFT JOIN Inscription i ON e.numCINEtu = i.numCINEtu
GROUP BY e.numCINEtu, e.nomEtu
ORDER BY nb_inscriptions DESC;

-- Anti-jointure : étudiants SANS aucune inscription
SELECT e.numCINEtu, e.nomEtu
FROM Etudiant e
    LEFT JOIN Inscription i ON e.numCINEtu = i.numCINEtu
WHERE i.numCINEtu IS NULL;   -- IS NULL = pas de correspondance à droite

-- LEFT JOIN sur 3 tables : toutes les formations avec ou sans session
SELECT
    fi.libelleFiliere,
    fo.titreForm,
    COUNT(s.codeSess) AS nb_sessions
FROM Filiere fi
    LEFT JOIN Formation fo ON fi.codeFiliere = fo.codeFiliere
    LEFT JOIN Session s    ON fo.codeForm    = s.codeForm
GROUP BY fi.codeFiliere, fi.libelleFiliere, fo.codeForm, fo.titreForm
ORDER BY fi.libelleFiliere, fo.titreForm;

-- RIGHT JOIN : équivalent au LEFT JOIN inversé
-- Toutes les sessions avec ou sans formateur assigné
SELECT
    s.codeSess,
    f.nomFormateur      -- NULL si aucun formateur
FROM Formateur f
    RIGHT JOIN Session s ON f.numFormateur = s.numFormateur;
-- Équivalent à : Session LEFT JOIN Formateur

INNER JOIN              LEFT JOIN              RIGHT JOIN
  A ┌───┐B            A ┌───┐B            A ┌───┐B
  ░░░██░░             █████░░             ░░░████
  Intersection         Tout A +             Tout B +
  seulement            intersection         intersection

LEFT JOIN + WHERE B.col IS NULL   →  Uniquement dans A (anti-jointure)
  A ┌───┐B
  ███░░░░  Tout A sauf l'intersection

-- CROSS JOIN : chaque ligne de A combinée avec chaque ligne de B
SELECT
    e.nomEtu,
    s.codeSess
FROM Etudiant e
    CROSS JOIN Session s;
-- 6 étudiants x 4 sessions = 24 lignes
-- Utile pour générer toutes les combinaisons possibles

-- Cas d'usage : générer une grille de disponibilité
-- (toutes les paires formateur-formation possibles)
SELECT
    f.nomFormateur,
    fm.titreForm
FROM Formateur f
    CROSS JOIN Formation fm
ORDER BY f.nomFormateur, fm.titreForm;
-- 4 formateurs x 5 formations = 20 lignes

-- Attention : CROSS JOIN implicite (vieille syntaxe à éviter)
SELECT e.nomEtu, s.codeSess FROM Etudiant e, Session s;
-- Equivalent à CROSS JOIN mais moins lisible

-- Trouver les paires d'étudiants de la même ville
SELECT
    e1.nomEtu AS etudiant1,
    e2.nomEtu AS etudiant2,
    e1.villeEtu
FROM Etudiant e1
    INNER JOIN Etudiant e2
        ON e1.villeEtu = e2.villeEtu
        AND e1.numCINEtu < e2.numCINEtu  -- éviter les doublons et les auto-paires
ORDER BY e1.villeEtu;

-- Étudiants inscrits dans les mêmes sessions
SELECT
    i1.numCINEtu AS etudiant1,
    i2.numCINEtu AS etudiant2,
    i1.codeSess
FROM Inscription i1
    INNER JOIN Inscription i2
        ON i1.codeSess = i2.codeSess
        AND i1.numCINEtu < i2.numCINEtu;

-- Exemple hiérarchie : table Organisation (manager référence un autre employé)
-- CREATE TABLE Organisation (id INT PK, nom VARCHAR, manager_id INT FK→id)
-- SELECT e.nom AS employe, m.nom AS manager
-- FROM Organisation e
--     LEFT JOIN Organisation m ON e.manager_id = m.id;

-- MySQL ne supporte pas FULL OUTER JOIN nativement
-- Émulation : LEFT JOIN UNION RIGHT JOIN

-- Toutes les formations et toutes les filières, avec ou sans correspondance
SELECT
    fi.libelleFiliere,
    fo.titreForm
FROM Filiere fi
    LEFT JOIN Formation fo ON fi.codeFiliere = fo.codeFiliere
UNION
SELECT
    fi.libelleFiliere,
    fo.titreForm
FROM Filiere fi
    RIGHT JOIN Formation fo ON fi.codeFiliere = fo.codeFiliere;

-- FULL OUTER ANTI-JOIN (lignes sans correspondance des deux côtés)
SELECT fi.libelleFiliere, fo.titreForm
FROM Filiere fi
    LEFT JOIN Formation fo ON fi.codeFiliere = fo.codeFiliere
WHERE fo.codeFiliere IS NULL  -- Filières sans formation
UNION
SELECT fi.libelleFiliere, fo.titreForm
FROM Filiere fi
    RIGHT JOIN Formation fo ON fi.codeFiliere = fo.codeFiliere
WHERE fi.codeFiliere IS NULL; -- Formations sans filière (ne devrait pas exister)

# Sauvegarder TOUT (structure + données) — la plus courante
mysqldump -u root -p skolabase > skolabase_backup_2024.sql
# -u root : utilisateur MySQL
# -p : demander le mot de passe
# > skolabase_backup_2024.sql : redirection vers le fichier

# Structure SEULEMENT (sans les données — pour recréer le schéma)
mysqldump -u root -p --no-data skolabase > skolabase_structure.sql

# Données SEULEMENT (sans CREATE TABLE — pour recharger des données)
mysqldump -u root -p --no-create-info skolabase > skolabase_data.sql

# Une seule table
mysqldump -u root -p skolabase Inscription > inscription_backup.sql

# Plusieurs tables spécifiques
mysqldump -u root -p skolabase Etudiant Inscription > etudiants_inscriptions.sql

# Plusieurs bases de données
mysqldump -u root -p --databases skolabase skolabase_test > multi_backup.sql

# TOUTES les bases (y compris mysql, information_schema)
mysqldump -u root -p --all-databases > all_databases.sql

# Options recommandées pour la production
mysqldump -u root -p \
    --single-transaction \  # Dump cohérent sans verrouiller les tables (InnoDB)
    --routines \            # Inclure les procédures stockées et fonctions
    --triggers \            # Inclure les triggers
    --events \              # Inclure les événements planifiés
    --hex-blob \            # Données binaires en hexadécimal
    skolabase > skolabase_prod_$(date +%Y%m%d).sql
# Nommage avec date : skolabase_prod_20241115.sql

-- MySQL dump 8.0.36
-- Host: localhost    Database: skolabase
-- ------------------------------------------------------

CREATE DATABASE IF NOT EXISTS `skolabase`;
USE `skolabase`;

DROP TABLE IF EXISTS `Filiere`;
CREATE TABLE `Filiere` (
    `codeFiliere` CHAR(6) NOT NULL,
    `libelleFiliere` VARCHAR(80) NOT NULL,
    PRIMARY KEY (`codeFiliere`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOCK TABLES `Filiere` WRITE;
INSERT INTO `Filiere` VALUES
    ('INF','Informatique'),
    ('GES','Gestion et Commerce');
UNLOCK TABLES;

# Méthode 1 : redirection en ligne de commande
mysql -u root -p skolabase < skolabase_backup_2024.sql
# Restaure toutes les tables et données dans la base skolabase

# Si la base n'existe pas encore
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS skolabase CHARACTER SET utf8mb4;"
mysql -u root -p skolabase < skolabase_backup_2024.sql

# Méthode 2 : à l'intérieur de MySQL Shell
mysql -u root -p
-- Dans MySQL :
SOURCE /chemin/vers/skolabase_backup_2024.sql;
-- ou sur Windows :
SOURCE C:/backup/skolabase_backup_2024.sql;

# Vérifier que la restauration a fonctionné
mysql -u root -p -e "USE skolabase; SHOW TABLES; SELECT COUNT(*) FROM Etudiant;"

① Ouvrir Workbench et se connecter à SkolaBase_Local
② Server → Data Import
③ Choisir "Import from Self-Contained File"
④ Cliquer ... et sélectionner le fichier .sql de sauvegarde
⑤ Default Target Schema : choisir skolabase
    (ou "New..." pour créer une nouvelle base)
⑥ Cliquer "Start Import" → attendre la fin
⑦ Vérifier dans l'onglet "Object Browser" que les tables sont bien présentes

numCINEtu,nomEtu,prenomEtu,dateNaissEtu,villeEtu,telEtu
A1234567,Alaoui,Karim,2002-05-15,Casablanca,0612345678
B7654321,Bennani,Sara,2001-11-22,Rabat,0698765432
C2468135,Chraibi,Youssef,2003-01-08,Marrakech,

-- Import basique
LOAD DATA INFILE '/var/lib/mysql-files/etudiants_import.csv'
INTO TABLE Etudiant
FIELDS TERMINATED BY ','           -- Séparateur de colonnes
       ENCLOSED BY '"'              -- Qualificateur de texte (guillemets)
LINES TERMINATED BY '\n'           -- Fin de ligne (Unix) ou '\r\n' (Windows)
IGNORE 1 LINES                     -- Ignorer la ligne d'en-tête
(numCINEtu, nomEtu, prenomEtu, dateNaissEtu, villeEtu, telEtu);

-- Import avec transformation : majuscules sur le nom
LOAD DATA INFILE '/var/lib/mysql-files/etudiants_import.csv'
INTO TABLE Etudiant
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@cin, @nom, @prenom, @naissance, @ville, @tel)
SET
    numCINEtu    = @cin,
    nomEtu       = UPPER(@nom),         -- Forcer les majuscules
    prenomEtu    = @prenom,
    dateNaissEtu = STR_TO_DATE(@naissance, '%Y-%m-%d'),
    villeEtu     = COALESCE(NULLIF(@ville, ''), 'Casablanca'),
    telEtu       = NULLIF(@tel, '');    -- '' → NULL

-- Vérifier le résultat
SELECT * FROM Etudiant LIMIT 5;

-- Exporter la table Etudiant en CSV
SELECT *
INTO OUTFILE '/var/lib/mysql-files/etudiants_export.csv'
FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM Etudiant;

-- Exporter avec en-tête (astuce : UNION ALL avec les noms de colonnes)
SELECT 'numCINEtu', 'nomEtu', 'prenomEtu', 'dateNaissEtu', 'villeEtu'
UNION ALL
SELECT numCINEtu, nomEtu, prenomEtu,
       DATE_FORMAT(dateNaissEtu, '%d/%m/%Y'), villeEtu
INTO OUTFILE '/var/lib/mysql-files/etudiants_avec_entete.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM Etudiant;

-- Exporter un résultat de jointure
SELECT
    e.numCINEtu,
    CONCAT(e.prenomEtu, ' ', e.nomEtu) AS nom_complet,
    fo.titreForm,
    i.noteInscription
INTO OUTFILE '/var/lib/mysql-files/resultats_etudiants.csv'
FIELDS TERMINATED BY ';' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM Etudiant e
    JOIN Inscription i  ON e.numCINEtu = i.numCINEtu
    JOIN Session s      ON i.codeSess  = s.codeSess
    JOIN Formation fo   ON s.codeForm  = fo.codeForm
ORDER BY e.nomEtu;

① Clic droit sur la table Etudiant dans l'Object Browser
② Choisir "Table Data Import Wizard"
③ Cliquer "Browse" et sélectionner le fichier CSV
④ Choisir l'encodage (UTF-8 pour les accents et caractères arabes)
⑤ Configurer le séparateur (virgule, point-virgule...)
⑥ Mapper les colonnes du CSV aux colonnes de la table
⑦ Cliquer "Next" puis "Execute Import"
⑧ Vérifier le rapport : nb lignes importées, erreurs éventuelles

-- Utilisateur administrateur SkolaBase (connexion locale seulement)
CREATE USER IF NOT EXISTS 'skola_admin'@'localhost'
    IDENTIFIED BY 'SkolaAdmin_2024!';

-- Utilisateur application (lecture + écriture, depuis l'application web)
CREATE USER IF NOT EXISTS 'skola_app'@'localhost'
    IDENTIFIED BY 'SkolaApp_2024!';

-- Utilisateur lecture seule (pour les rapports, depuis n'importe où)
CREATE USER IF NOT EXISTS 'skola_reader'@'%'
    IDENTIFIED BY 'SkolaReader_2024!';

-- Vérifier la création
SELECT User, Host, plugin, password_expired
FROM mysql.user
WHERE User LIKE 'skola%';

-- Résultat attendu :
-- User          | Host      | plugin                | password_expired
-- skola_admin   | localhost | caching_sha2_password | N
-- skola_app     | localhost | caching_sha2_password | N
-- skola_reader  | %         | caching_sha2_password | N

-- Changer le mot de passe
ALTER USER 'skola_app'@'localhost'
    IDENTIFIED BY 'NouveauMdp_2024!';

-- Forcer le changement de mot de passe à la prochaine connexion
ALTER USER 'skola_reader'@'%'
    PASSWORD EXPIRE;

-- Expiration automatique tous les 90 jours
ALTER USER 'skola_app'@'localhost'
    PASSWORD EXPIRE INTERVAL 90 DAY;

-- Désactiver le compte (sans le supprimer)
ALTER USER 'skola_reader'@'%' ACCOUNT LOCK;

-- Réactiver le compte
ALTER USER 'skola_reader'@'%' ACCOUNT UNLOCK;

-- Changer son propre mot de passe (connecté comme skola_app)
ALTER USER CURRENT_USER() IDENTIFIED BY 'MonNouveauMdp!';

-- Supprimer un compte (et tous ses privilèges)
DROP USER IF EXISTS 'skola_reader'@'%';

-- Tester la connexion depuis le terminal
-- mysql -u skola_app -p skolabase
-- Entrer le mot de passe → doit se connecter

-- Voir le compte courant
SELECT CURRENT_USER();

-- skola_admin : tous les droits sur SkolaBase uniquement
GRANT ALL PRIVILEGES ON skolabase.* TO 'skola_admin'@'localhost';

-- skola_app : droits CRUD (pas DROP, CREATE, ALTER)
GRANT SELECT, INSERT, UPDATE, DELETE
    ON skolabase.*
    TO 'skola_app'@'localhost';

-- skola_reader : lecture seule sur toute la base
GRANT SELECT ON skolabase.* TO 'skola_reader'@'%';

-- Niveau table : secrétaire gère uniquement les étudiants
GRANT SELECT, INSERT, UPDATE ON skolabase.Etudiant
    TO 'secretaire'@'localhost';
GRANT SELECT ON skolabase.Inscription
    TO 'secretaire'@'localhost';

-- Niveau colonne : cacher les notes à certains utilisateurs
GRANT SELECT (numCINEtu, codeSess) ON skolabase.Inscription
    TO 'skola_view'@'localhost';
-- skola_view peut voir numCINEtu et codeSess mais PAS noteInscription

-- Appliquer immédiatement (MySQL 8 recharge automatiquement)
FLUSH PRIVILEGES;
-- Note : FLUSH PRIVILEGES est nécessaire seulement si on modifie
-- directement les tables mysql.user (à éviter)

-- Vérifier les droits accordés
SHOW GRANTS FOR 'skola_app'@'localhost';
-- Résultat :
-- GRANT SELECT, INSERT, UPDATE, DELETE ON `skolabase`.* TO 'skola_app'@'localhost'

-- Retirer le droit DELETE à skola_app
REVOKE DELETE ON skolabase.* FROM 'skola_app'@'localhost';

-- Retirer TOUS les droits
REVOKE ALL PRIVILEGES ON skolabase.* FROM 'skola_reader'@'%';

-- Vérifier les droits de tous les utilisateurs SkolaBase
SELECT
    User,
    Host,
    Select_priv,
    Insert_priv,
    Update_priv,
    Delete_priv,
    Create_priv,
    Drop_priv
FROM mysql.user
WHERE User LIKE 'skola%';

-- Lister tous les GRANT sur une base spécifique
SELECT *
FROM information_schema.SCHEMA_PRIVILEGES
WHERE TABLE_SCHEMA = 'skolabase';

-- Résumé de la stratégie de sécurité SkolaBase :
-- root         → NE JAMAIS utiliser pour l'application
-- skola_admin  → Admins techniques (migrations, sauvegardes)
-- skola_app    → Application web (SELECT/INSERT/UPDATE/DELETE)
-- skola_reader → Rapports, analyses (SELECT uniquement)
-- secretaire   → Interface secrétariat (Etudiant + Inscription SELECT/INSERT/UPDATE)

CAHIER DES CHARGES — SkolaBase
Centre de Formation Professionnelle — Maroc
==============================================

1. CONTEXTE
   Le centre SkolaBase dispense des formations professionnelles continues
   pour des techniciens et cadres marocains. Il gère actuellement ses
   données sur registres papier et souhaite une base de données MySQL.

2. ENTITES IDENTIFIEES
   ● ETUDIANT : toute personne inscrite à une session de formation.
     Chaque étudiant possède un numéro CIN unique (lettre + 7 chiffres).
   ● FORMATEUR : intervenant qui anime les sessions.
   ● FORMATION : programme pédagogique avec titre, durée et niveau.
   ● SESSION : occurrence décalée d'une formation dans le temps et l'espace.
   ● FILIERE : domaine regroupant plusieurs formations (INF, GES, ELE, BTP).

3. REGLES DE GESTION
   RG1 : Un étudiant peut s'inscrire à plusieurs sessions. Une session
         peut accueillir plusieurs étudiants. (N:N)
   RG2 : Chaque inscription d'un étudiant à une session donne lieu à
         une note (0 à 20, peut être absente si non encore saisie).
   RG3 : Chaque session est animée par exactement un formateur.
         Un formateur peut animer plusieurs sessions. (1:N)
   RG4 : Chaque session porte sur exactement une formation.
         Une formation peut donner lieu à plusieurs sessions. (1:N)
   RG5 : Chaque formation appartient à exactement une filière.
         Une filière regroupe une ou plusieurs formations. (1:N)

4. PERIMETRE
   Inclus   : gestion inscriptions, sessions, notes, formateurs, formations, filières
   Exclus   : paie des formateurs, comptabilité générale, app mobile

5. CONTRAINTES TECHNIQUES
   SGBD : MySQL 8.0 — moteur InnoDB — charset utf8mb4
   Serveur : Windows Server local ou Linux
   Accès simultanés : 10 utilisateurs maximum

numCINEtu    ──► nomEtu, prenomEtu, dateNaissEtu, villeEtu, telEtu
numFormateur ──► nomFormateur, prenomFormateur, specialiteForm
codeSess     ──► dateDebutSess, dateFinSess, lieuSess, numFormateur (FK), codeForm (FK)
codeForm     ──► titreForm, dureeForm, niveauForm, codeFiliere (FK)
codeFiliere  ──► libelleFiliere

DF composée :
numCINEtu + codeSess ──► noteInscription   (association INSCRIPTION porteuse)

ETUDIANT  (0,N) ◆─ INSCRIPTION (porteuse: noteInscription) ─◆ (1,N) SESSION
                        R3 → table pivot INSCRIPTION
SESSION   (1,N) ◆─ ANIME (RG3) ─◆ (1,1) FORMATEUR
                        R2 → FK numFormateur dans SESSION
SESSION   (1,N) ◆─ PORTE SUR (RG4) ─◆ (1,1) FORMATION
                        R2 → FK codeForm dans SESSION
FORMATION (1,N) ◆─ APPARTIENT A (RG5) ─◆ (1,1) FILIERE
                        R2 → FK codeFiliere dans FORMATION

FILIERE    (#codeFiliere, libelleFiliere)

FORMATION  (#codeForm, titreForm, dureeForm, niveauForm,
            *codeFiliere → FILIERE.codeFiliere)

FORMATEUR  (#numFormateur AI, nomFormateur, prenomFormateur, specialiteForm)

SESSION    (#codeSess, dateDebutSess, dateFinSess, lieuSess,
            *numFormateur → FORMATEUR.numFormateur,
            *codeForm     → FORMATION.codeForm)

ETUDIANT   (#numCINEtu, nomEtu, prenomEtu, dateNaissEtu, villeEtu, telEtu)

INSCRIPTION(#numCINEtu → ETUDIANT.numCINEtu,
            #codeSess  → SESSION.codeSess,
            noteInscription)
            CLE COMPOSEE : (numCINEtu, codeSess)

-- ============================================================
--  SkolaBase — Script DDL complet
--  Centre de formation marocain — MySQL 8.0
-- ============================================================

DROP DATABASE IF EXISTS skolabase;
CREATE DATABASE skolabase
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci
    COMMENT 'Base de données centre de formation SkolaBase';

USE skolabase;

-- ── Table 1 : FILIERE (aucune FK — créer en premier) ────────
CREATE TABLE IF NOT EXISTS Filiere (
    codeFiliere     CHAR(6)      NOT NULL,
    libelleFiliere  VARCHAR(80)  NOT NULL,
    CONSTRAINT pk_Filiere PRIMARY KEY (codeFiliere)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
  COMMENT = 'Filières de formation (INF, GES, ELE, BTP)';

-- ── Table 2 : FORMATION ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS Formation (
    codeForm    VARCHAR(10)                             NOT NULL,
    titreForm   VARCHAR(100)                            NOT NULL,
    dureeForm   SMALLINT UNSIGNED                       NOT NULL COMMENT 'Durée en heures',
    niveauForm  ENUM('Technicien','Bac','Bac+2','Bac+3') NULL,
    codeFiliere CHAR(6)                                 NOT NULL,
    CONSTRAINT pk_Formation  PRIMARY KEY (codeForm),
    CONSTRAINT fk_Form_Fil   FOREIGN KEY (codeFiliere)
        REFERENCES Filiere(codeFiliere)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT chk_Duree     CHECK (dureeForm > 0)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- ── Table 3 : FORMATEUR ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS Formateur (
    numFormateur    INT          NOT NULL AUTO_INCREMENT,
    nomFormateur    VARCHAR(50)  NOT NULL,
    prenomFormateur VARCHAR(50)  NOT NULL,
    specialiteForm  VARCHAR(80)  NULL,
    CONSTRAINT pk_Formateur PRIMARY KEY (numFormateur)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- ── Table 4 : SESSION ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS Session (
    codeSess      VARCHAR(10)  NOT NULL,
    dateDebutSess DATE         NOT NULL,
    dateFinSess   DATE         NULL,
    lieuSess      VARCHAR(100) NULL,
    numFormateur  INT          NOT NULL,
    codeForm      VARCHAR(10)  NOT NULL,
    CONSTRAINT pk_Session       PRIMARY KEY (codeSess),
    CONSTRAINT fk_Sess_Format   FOREIGN KEY (numFormateur)
        REFERENCES Formateur(numFormateur)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_Sess_Form     FOREIGN KEY (codeForm)
        REFERENCES Formation(codeForm)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT chk_DatesSess    CHECK (dateFinSess IS NULL OR dateFinSess >= dateDebutSess),
    INDEX idx_Sess_Form (codeForm),
    INDEX idx_Sess_Debut (dateDebutSess)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- ── Table 5 : ETUDIANT ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS Etudiant (
    numCINEtu    CHAR(8)      NOT NULL COMMENT 'Format : lettre + 7 chiffres',
    nomEtu       VARCHAR(50)  NOT NULL,
    prenomEtu    VARCHAR(50)  NOT NULL,
    dateNaissEtu DATE         NULL,
    villeEtu     VARCHAR(50)  NOT NULL DEFAULT 'Casablanca',
    telEtu       VARCHAR(15)  NULL,
    CONSTRAINT pk_Etudiant PRIMARY KEY (numCINEtu),
    CONSTRAINT chk_CIN
        CHECK (numCINEtu REGEXP '^[A-Z][0-9]{7}$') ENFORCED,
    CONSTRAINT chk_DateNaiss
        CHECK (dateNaissEtu IS NULL OR dateNaissEtu < CURDATE()) ENFORCED,
    INDEX idx_Etu_Ville (villeEtu)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- ── Table 6 : INSCRIPTION (créer en dernier — FK vers Etudiant + Session) ─
CREATE TABLE IF NOT EXISTS Inscription (
    numCINEtu       CHAR(8)      NOT NULL,
    codeSess        VARCHAR(10)  NOT NULL,
    noteInscription DECIMAL(4,2) NULL COMMENT 'Note sur 20',
    CONSTRAINT pk_Inscription   PRIMARY KEY (numCINEtu, codeSess),
    CONSTRAINT fk_Insc_Etu      FOREIGN KEY (numCINEtu)
        REFERENCES Etudiant(numCINEtu)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_Insc_Sess     FOREIGN KEY (codeSess)
        REFERENCES Session(codeSess)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT chk_Note
        CHECK (noteInscription IS NULL OR
              (noteInscription >= 0 AND noteInscription <= 20)) ENFORCED
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- Vérification
SHOW TABLES;
SELECT TABLE_NAME, ENGINE, TABLE_ROWS, TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'skolabase';

USE skolabase;

-- ── 1. Filières ────────────────────────────────────────────
INSERT INTO Filiere (codeFiliere, libelleFiliere) VALUES
    ('INF', 'Informatique'),
    ('GES', 'Gestion et Commerce'),
    ('ELE', 'Electronique et Electrotechnique'),
    ('BTP', 'Batiment et Travaux Publics');

-- ── 2. Formations ───────────────────────────────────────────
INSERT INTO Formation (codeForm, titreForm, dureeForm, niveauForm, codeFiliere) VALUES
    ('DEV-WEB', 'Developpement Web Full Stack',        120, 'Bac+2', 'INF'),
    ('DEV-MOB', 'Developpement Mobile Android',         80, 'Bac+2', 'INF'),
    ('BDD-ADM', 'Administration des Bases de Donnees', 100, 'Bac+3', 'INF'),
    ('CPT-GEN', 'Comptabilite Generale',                90, 'Bac',   'GES'),
    ('MKT-DIG', 'Marketing Digital',                    60, 'Bac+2', 'GES');

-- ── 3. Formateurs ───────────────────────────────────────────
INSERT INTO Formateur (nomFormateur, prenomFormateur, specialiteForm) VALUES
    ('Alaoui',   'Karim',  'Developpement Web, PHP, Laravel'),
    ('Bennani',  'Nadia',  'Bases de donnees, MySQL, Oracle'),
    ('Chraibi',  'Hassan', 'Reseaux, Administration systeme'),
    ('Idrissi',  'Leila',  'Marketing digital, SEO');

-- ── 4. Sessions ─────────────────────────────────────────────
INSERT INTO Session (codeSess, dateDebutSess, dateFinSess, lieuSess, numFormateur, codeForm) VALUES
    ('SESS-2024-01', '2024-09-02', '2024-12-20', 'Salle A101 - Casablanca', 1, 'DEV-WEB'),
    ('SESS-2024-02', '2024-09-09', '2024-11-29', 'Salle B205 - Rabat',      2, 'BDD-ADM'),
    ('SESS-2024-03', '2024-10-07', '2024-12-13', 'Salle A102 - Casablanca', 1, 'DEV-MOB'),
    ('SESS-2025-01', '2025-01-13', '2025-04-04', 'Salle A101 - Casablanca', 1, 'DEV-WEB');

-- ── 5. Etudiants ────────────────────────────────────────────
INSERT INTO Etudiant (numCINEtu, nomEtu, prenomEtu, dateNaissEtu, villeEtu, telEtu) VALUES
    ('A1234567', 'Alaoui',   'Karim',   '2002-05-15', 'Casablanca', '0612345678'),
    ('B7654321', 'Bennani',  'Sara',    '2001-11-22', 'Rabat',      '0698765432'),
    ('C2468135', 'Chraibi',  'Youssef', '2003-01-08', 'Marrakech',  '0635791246'),
    ('D1357924', 'Idrissi',  'Fatima',  '2002-08-30', 'Fes',        '0674513892'),
    ('E9876543', 'Mansouri', 'Amine',   '2001-03-17', 'Casablanca', '0656789012'),
    ('F3692581', 'Tazi',     'Zineb',   '2003-06-25', 'Tanger',     '0678901234');

-- ── 6. Inscriptions ─────────────────────────────────────────
INSERT INTO Inscription (numCINEtu, codeSess, noteInscription) VALUES
    ('A1234567', 'SESS-2024-01', 14.50),
    ('B7654321', 'SESS-2024-01', 16.00),
    ('C2468135', 'SESS-2024-01', 11.75),
    ('A1234567', 'SESS-2024-02', 13.25),
    ('D1357924', 'SESS-2024-02', 17.50),
    ('E9876543', 'SESS-2024-01',  NULL),   -- Note pas encore saisie
    ('F3692581', 'SESS-2024-03', 15.00);

-- ── Vérification du peuplement ────────────────────────────────
SELECT 'Filiere'     AS table_name, COUNT(*) AS nb FROM Filiere    UNION ALL
SELECT 'Formation',                 COUNT(*) FROM Formation         UNION ALL
SELECT 'Formateur',                 COUNT(*) FROM Formateur         UNION ALL
SELECT 'Session',                   COUNT(*) FROM Session           UNION ALL
SELECT 'Etudiant',                  COUNT(*) FROM Etudiant          UNION ALL
SELECT 'Inscription',               COUNT(*) FROM Inscription;

-- Quelques corrections via UPDATE pour illustrer
UPDATE Etudiant
SET telEtu = '0612000099'
WHERE numCINEtu = 'A1234567';

-- Saisir la note manquante d'E9876543
UPDATE Inscription
SET noteInscription = 12.50
WHERE numCINEtu = 'E9876543' AND codeSess = 'SESS-2024-01';

USE skolabase;

-- Q1 : Tous les étudiants triés par nom
SELECT numCINEtu, nomEtu, prenomEtu, villeEtu FROM Etudiant ORDER BY nomEtu;

-- Q2 : Etudiants de Casablanca uniquement
SELECT * FROM Etudiant WHERE villeEtu = 'Casablanca';

-- Q3 : Etudiants dont le nom commence par 'A' ou 'B'
SELECT nomEtu, prenomEtu FROM Etudiant WHERE nomEtu LIKE 'A%' OR nomEtu LIKE 'B%';

-- Q4 : Formations de plus de 80 heures
SELECT titreForm, dureeForm, niveauForm FROM Formation WHERE dureeForm > 80 ORDER BY dureeForm DESC;

-- Q5 : Sessions en cours en 2024
SELECT codeSess, dateDebutSess, dateFinSess FROM Session
WHERE YEAR(dateDebutSess) = 2024 ORDER BY dateDebutSess;

-- Q6 : Notes entre 12 et 16 (inclus)
SELECT numCINEtu, codeSess, noteInscription FROM Inscription
WHERE noteInscription BETWEEN 12 AND 16 ORDER BY noteInscription DESC;

-- Q7 : Inscriptions sans note
SELECT numCINEtu, codeSess FROM Inscription WHERE noteInscription IS NULL;

-- Q8 : Nombre d'étudiants par ville
SELECT villeEtu, COUNT(*) AS nb_etudiants FROM Etudiant GROUP BY villeEtu ORDER BY nb_etudiants DESC;

-- Q9 : Moyenne, min et max des notes par session
SELECT
    codeSess,
    COUNT(*)                            AS nb_inscrits,
    COUNT(noteInscription)              AS notes_saisies,
    ROUND(AVG(noteInscription), 2)      AS moyenne,
    MIN(noteInscription)                AS note_min,
    MAX(noteInscription)                AS note_max
FROM Inscription GROUP BY codeSess;

-- Q10 : Sessions avec moyenne supérieure à 13
SELECT codeSess, ROUND(AVG(noteInscription), 2) AS moyenne
FROM Inscription WHERE noteInscription IS NOT NULL
GROUP BY codeSess HAVING AVG(noteInscription) > 13;

-- Q11 : Âge de chaque étudiant (calculé depuis dateNaissEtu)
SELECT
    CONCAT(prenomEtu, ' ', nomEtu) AS nom_complet,
    dateNaissEtu,
    TIMESTAMPDIFF(YEAR, dateNaissEtu, CURDATE()) AS age
FROM Etudiant WHERE dateNaissEtu IS NOT NULL ORDER BY age;

-- Q12 : Durée de chaque session en jours
SELECT codeSess, dateDebutSess, dateFinSess,
       DATEDIFF(dateFinSess, dateDebutSess) AS duree_jours
FROM Session WHERE dateFinSess IS NOT NULL;

-- Q13 : Mention selon la note (CASE WHEN)
SELECT numCINEtu, codeSess, noteInscription,
    CASE
        WHEN noteInscription IS NULL  THEN 'Non evalue'
        WHEN noteInscription >= 16   THEN 'Tres bien'
        WHEN noteInscription >= 14   THEN 'Bien'
        WHEN noteInscription >= 12   THEN 'Assez bien'
        WHEN noteInscription >= 10   THEN 'Passable'
        ELSE                              'Insuffisant'
    END AS mention
FROM Inscription ORDER BY noteInscription DESC;

-- Q14 : Taux de réussite par session (note >= 10)
SELECT codeSess,
    COUNT(noteInscription) AS evaluations,
    SUM(IF(noteInscription >= 10, 1, 0)) AS admis,
    ROUND(100 * SUM(IF(noteInscription >= 10, 1, 0)) / COUNT(noteInscription), 1) AS taux_pct
FROM Inscription WHERE noteInscription IS NOT NULL GROUP BY codeSess;

-- Q15 : Formations par filière (nombre et total heures)
SELECT codeFiliere, COUNT(*) AS nb_formations, SUM(dureeForm) AS heures_totales
FROM Formation GROUP BY codeFiliere HAVING COUNT(*) >= 2;

-- Q16 : Formateur le plus actif (plus de sessions)
SELECT numFormateur, COUNT(*) AS nb_sessions FROM Session
GROUP BY numFormateur ORDER BY nb_sessions DESC LIMIT 1;

-- Q17 : Etudiants inscrits à plus d'une session
SELECT numCINEtu, COUNT(*) AS nb_sessions FROM Inscription
GROUP BY numCINEtu HAVING COUNT(*) > 1;

-- Q18 : Note de chaque étudiant avec son écart à la moyenne générale
SELECT numCINEtu, codeSess, noteInscription,
    ROUND(noteInscription - (SELECT AVG(noteInscription) FROM Inscription
                              WHERE noteInscription IS NOT NULL), 2) AS ecart_moyenne
FROM Inscription WHERE noteInscription IS NOT NULL;

-- Q19 : Formations sans session planifiée
SELECT codeForm, titreForm FROM Formation
WHERE codeForm NOT IN (SELECT DISTINCT codeForm FROM Session);

-- Q20 : Statistiques globales SkolaBase
SELECT
    (SELECT COUNT(*) FROM Etudiant)    AS nb_etudiants,
    (SELECT COUNT(*) FROM Session)     AS nb_sessions,
    (SELECT COUNT(*) FROM Inscription) AS nb_inscriptions,
    (SELECT ROUND(AVG(noteInscription), 2)
     FROM Inscription
     WHERE noteInscription IS NOT NULL)  AS moyenne_generale;

USE skolabase;

-- ── JOINTURES ────────────────────────────────────────────

-- J1 : Parcours complet (étudiant + formation + formateur)
SELECT
    CONCAT(e.prenomEtu, ' ', e.nomEtu) AS etudiant,
    fo.titreForm                        AS formation,
    fi.libelleFiliere                   AS filiere,
    CONCAT(fmt.prenomFormateur, ' ', fmt.nomFormateur) AS formateur,
    s.dateDebutSess,
    i.noteInscription
FROM Etudiant e
    JOIN Inscription i  ON e.numCINEtu   = i.numCINEtu
    JOIN Session s      ON i.codeSess    = s.codeSess
    JOIN Formation fo   ON s.codeForm    = fo.codeForm
    JOIN Filiere fi     ON fo.codeFiliere = fi.codeFiliere
    JOIN Formateur fmt  ON s.numFormateur = fmt.numFormateur
ORDER BY e.nomEtu, s.dateDebutSess;

-- J2 : Toutes les formations avec nombre de sessions (LEFT JOIN)
SELECT fo.codeForm, fo.titreForm, fo.dureeForm,
       COUNT(s.codeSess) AS nb_sessions
FROM Formation fo
    LEFT JOIN Session s ON fo.codeForm = s.codeForm
GROUP BY fo.codeForm, fo.titreForm, fo.dureeForm
ORDER BY nb_sessions DESC;

-- J3 : Filières avec nombre de formations et heures totales
SELECT fi.libelleFiliere,
       COUNT(fo.codeForm)  AS nb_formations,
       COALESCE(SUM(fo.dureeForm), 0) AS heures_totales
FROM Filiere fi
    LEFT JOIN Formation fo ON fi.codeFiliere = fo.codeFiliere
GROUP BY fi.codeFiliere, fi.libelleFiliere
ORDER BY heures_totales DESC;

-- J4 : Etudiants avec leurs meilleures notes
SELECT e.numCINEtu,
       CONCAT(e.prenomEtu, ' ', e.nomEtu) AS etudiant,
       MAX(i.noteInscription) AS meilleure_note,
       COUNT(i.codeSess)      AS nb_inscriptions
FROM Etudiant e
    LEFT JOIN Inscription i ON e.numCINEtu = i.numCINEtu
GROUP BY e.numCINEtu, e.nomEtu, e.prenomEtu
ORDER BY meilleure_note DESC;

-- J5 : Formateurs avec leurs sessions et formations
SELECT fmt.nomFormateur, fmt.prenomFormateur,
       s.codeSess, fo.titreForm, s.dateDebutSess
FROM Formateur fmt
    LEFT JOIN Session s   ON fmt.numFormateur = s.numFormateur
    LEFT JOIN Formation fo ON s.codeForm       = fo.codeForm
ORDER BY fmt.nomFormateur, s.dateDebutSess;

-- J6 : Pairs d'étudiants dans la même session (SELF JOIN)
SELECT i1.numCINEtu AS etudiant1, i2.numCINEtu AS etudiant2, i1.codeSess
FROM Inscription i1
    JOIN Inscription i2 ON i1.codeSess = i2.codeSess
                        AND i1.numCINEtu < i2.numCINEtu
ORDER BY i1.codeSess;

-- ── SOUS-REQUETES ────────────────────────────────────────

-- S1 : Etudiants avec note > moyenne générale
SELECT numCINEtu, codeSess, noteInscription
FROM Inscription
WHERE noteInscription > (SELECT AVG(noteInscription) FROM Inscription
                          WHERE noteInscription IS NOT NULL);

-- S2 : Meilleur étudiant de la session SESS-2024-01
SELECT e.numCINEtu, CONCAT(e.prenomEtu, ' ', e.nomEtu) AS etudiant, i.noteInscription
FROM Etudiant e JOIN Inscription i ON e.numCINEtu = i.numCINEtu
WHERE i.codeSess = 'SESS-2024-01'
  AND i.noteInscription = (SELECT MAX(noteInscription) FROM Inscription
                            WHERE codeSess = 'SESS-2024-01');

-- S3 : Etudiants inscrits à au moins une session (EXISTS)
SELECT numCINEtu, nomEtu, prenomEtu FROM Etudiant e
WHERE EXISTS (SELECT 1 FROM Inscription i WHERE i.numCINEtu = e.numCINEtu);

-- S4 : Etudiants sans aucune inscription (NOT EXISTS)
SELECT numCINEtu, nomEtu, prenomEtu FROM Etudiant e
WHERE NOT EXISTS (SELECT 1 FROM Inscription i WHERE i.numCINEtu = e.numCINEtu);

-- S5 : Formations qui ont eu au moins une session en 2024
SELECT codeForm, titreForm FROM Formation
WHERE codeForm IN (SELECT codeForm FROM Session WHERE YEAR(dateDebutSess) = 2024);

-- S6 : Formateur avec le plus de sessions animées
SELECT numFormateur, nomFormateur, prenomFormateur FROM Formateur
WHERE numFormateur = (SELECT numFormateur FROM Session
                      GROUP BY numFormateur ORDER BY COUNT(*) DESC LIMIT 1);

-- S7 : Etudiants dont la note dans SESS-2024-01 est > leur propre moyenne
SELECT i1.numCINEtu, i1.noteInscription AS note_sess01
FROM Inscription i1
WHERE i1.codeSess = 'SESS-2024-01'
  AND i1.noteInscription > (SELECT AVG(i2.noteInscription)
                             FROM Inscription i2
                             WHERE i2.numCINEtu = i1.numCINEtu
                               AND i2.noteInscription IS NOT NULL);

-- S8 : Sessions de la formation la plus longue
SELECT codeSess, dateDebutSess FROM Session
WHERE codeForm = (SELECT codeForm FROM Formation ORDER BY dureeForm DESC LIMIT 1);

-- S9 : Table dérivée : top 3 sessions par moyenne
SELECT s.codeSess, s.dateDebutSess, stats.moyenne
FROM Session s
    JOIN (SELECT codeSess, ROUND(AVG(noteInscription), 2) AS moyenne
          FROM Inscription WHERE noteInscription IS NOT NULL
          GROUP BY codeSess) AS stats ON s.codeSess = stats.codeSess
ORDER BY stats.moyenne DESC LIMIT 3;

# Sauvegarde complète (structure + données)
mysqldump -u root -p \
    --single-transaction \
    --routines \
    --triggers \
    skolabase > skolabase_backup_$(date +%Y%m%d).sql

# Vérifier le fichier généré
ls -lh skolabase_backup_*.sql
# skolabase_backup_20241115.sql  12K

# Sauvegarde structure seulement (partager le schéma)
mysqldump -u root -p --no-data skolabase > skolabase_schema.sql

# Restauration complète
mysql -u root -p -e "DROP DATABASE IF EXISTS skolabase;"
mysql -u root -p -e "CREATE DATABASE skolabase CHARACTER SET utf8mb4;"
mysql -u root -p skolabase < skolabase_backup_20241115.sql

# Vérification après restauration
mysql -u root -p skolabase -e "SHOW TABLES; SELECT COUNT(*) FROM Etudiant;"

-- ── Création des 4 comptes ──────────────────────────────────

-- Compte administrateur (droits complets sur skolabase)
CREATE USER IF NOT EXISTS 'skola_admin'@'localhost'
    IDENTIFIED BY 'SkolaAdmin_2024!';
GRANT ALL PRIVILEGES ON skolabase.* TO 'skola_admin'@'localhost';

-- Compte application web (CRUD sans DROP/CREATE/ALTER)
CREATE USER IF NOT EXISTS 'skola_app'@'localhost'
    IDENTIFIED BY 'SkolaApp_2024!';
GRANT SELECT, INSERT, UPDATE, DELETE ON skolabase.*
    TO 'skola_app'@'localhost';

-- Compte secrétariat (Etudiant + Inscription uniquement)
CREATE USER IF NOT EXISTS 'secretariat'@'localhost'
    IDENTIFIED BY 'Secretariat_2024!';
GRANT SELECT, INSERT, UPDATE ON skolabase.Etudiant
    TO 'secretariat'@'localhost';
GRANT SELECT, INSERT ON skolabase.Inscription
    TO 'secretariat'@'localhost';
GRANT SELECT ON skolabase.Session
    TO 'secretariat'@'localhost';

-- Compte rapports (lecture seule depuis n'importe où)
CREATE USER IF NOT EXISTS 'skola_reader'@'%'
    IDENTIFIED BY 'SkolaReader_2024!';
GRANT SELECT ON skolabase.* TO 'skola_reader'@'%';

-- ── Vérification ───────────────────────────────────────────
SHOW GRANTS FOR 'skola_app'@'localhost';
SHOW GRANTS FOR 'secretariat'@'localhost';

SELECT User, Host, plugin
FROM mysql.user WHERE User LIKE 'skola%' OR User = 'secretariat';

-- ── Tests de connexion ───────────────────────────────────────
-- (exécuter depuis le terminal)
-- mysql -u skola_app -p skolabase -e "SELECT COUNT(*) FROM Etudiant;"
-- mysql -u skola_reader -p skolabase -e "SELECT titreForm FROM Formation;"
-- mysql -u secretariat -p skolabase -e "DROP TABLE Etudiant;" -- doit échouer !

-- ── Maintenance ─────────────────────────────────────────────
-- Changer le mot de passe d'un compte
ALTER USER 'skola_app'@'localhost'
    IDENTIFIED BY 'NouveauMdp_SkolaApp_2025!';

-- Révoquer le DELETE pour skola_app (restriction de sécurité)
REVOKE DELETE ON skolabase.* FROM 'skola_app'@'localhost';
SHOW GRANTS FOR 'skola_app'@'localhost';

-- Supprimer un compte obsolète
DROP USER IF EXISTS 'skola_reader'@'%';

-- ── Bilan de sécurité SkolaBase ──────────────────────────────
SELECT
    TABLE_SCHEMA    AS base,
    GRANTEE         AS utilisateur,
    PRIVILEGE_TYPE  AS privilege,
    IS_GRANTABLE    AS peut_transmettre
FROM information_schema.SCHEMA_PRIVILEGES
WHERE TABLE_SCHEMA = 'skolabase'
ORDER BY GRANTEE, PRIVILEGE_TYPE;