TP 02 : GESTION DES ETUDIANTS
-----------------------------
CREATE DATABASE GEST_ETUDIANTS;
CREATE TABLE Etudiant(
Num_Etud INT Not null,
Nom VARCHAR(30),
Prenom VARCHAR(30),
PRIMARY KEY(Num_Etud)
);
CREATE TABLE Matiere(
Code_Mat INT NOT NULL,
Lib_Mat VARCHAR(50),
PRIMARY KEY(Code_Mat)
);
CREATE TABLE Evaluer(
Num_Etud INT NOT NULL,
Code_Mat INT NOT NULL,
Date_Eval DATE,
Note DECIMAL(4,2),
PRIMARY KEY(Num_Etud, Code_Mat),
FOREIGN KEY(Num_Etud) REFERENCES Etudiant(Num_Etud),
FOREIGN KEY(Code_Mat) REFERENCES Matiere(Code_Mat)
);
------------
Insert into Etudiant(Num_Etud,Nom,Prenom )
Values(1,'Nom1','Prénom1');
-----------
Insert into Matiere(Code_Mat,Lib_Mat)
Values(1,'Techniques d''expression et méthodologie');
-----------------
Insert into Evaluer( Num_Etud,Code_Mat,Date_Eval,Note)
Values(1,1,'12/05/2021',14);
---------------
SELECT ETUDIANT.Num_Etud, Nom, Prenom,AVG(Note) AS MoyEtuMat FROM EVALUER, MATIERE, ETUDIANT
WHERE EVALUER.Code_Mat = MATIERE.Code_Mat AND EVALUER.Num_Etud = ETUDIANT.Num_Etud
GROUP BY ETUDIANT.Num_Etud, Nom, Prenom, Lib_Mat;
