1 2 3 4 5 6 7 8 9 10
|
select prenom || ' ' || nom AS Etudiant,DECODE(MAX(DECODE (NOTE,'A+', 4.3,'A',4,'A-',3.7,'B+', 3.4,'B',3.1,'B-',2.8,'C+',2.5,'C',2.2,'C-',1.9,'D+',1.6,'D',1.3,'D-',1.0,'E',0,NULL)),4.3,'A+
',4,'A',3.7,'A-',3.4,'B+',3.1,'B',2.8,'B-',2.5,'C+',2.2,'C',1.9,'C-',1.6,'D+',1.3,'D',1.0,'D-',0,'E',NULL) as "plus haute note",
DECODE(MIN(DECODE (NOTE,'A+', 4.3,'A',4,'A-',3.7,'B+', 3.4,'B',3.1,'B-',2.8,'C+',2.5,'C',2.2,'C-',1.9,'D+',1.6,'D',1.3,'D-',1.0,'E',0,NULL)),4.3,'A+ ',4,'A',3.7,'A-',3.4,'B+',3.1,'B',2.8,'B-',2.5,'C+',2.2,'C',1.9,'C-',1.6,'D+',1.3,'D',
1.0,'D-',0,'E',NULL) as "Plus basse note"
from TP1_A05_INDIVIDUS,TP1_A05_ETUDIANTS,TP1_A05_INSCRIPTIONS
WHERE TP1_A05_INDIVIDUS.idindividu = TP1_A05_ETUDIANTS.idindividu AND
TP1_A05_INSCRIPTIONS.codepermanent = TP1_A05_ETUDIANTS.codepermanent
GROUP BY prenom || ' ' || nom
ORDER BY prenom || ' ' || nom; |
Partager