Database for school discipline management system.
- About
- Presentation and tutorial
This database run with mysql version 5.7.31 on windows 10.
Charset: CP850(DOS West European)
mysql -u <user_name> - p
mysql> \. setup.sql
mysql> \. data.sql
Just do a pull request, it'll be a pleasure to receive your contribution.
This Database is shared with a Common Creative Licence : BY - NC - SA.
These queries are located to testqueries.sql
Show precisely all the student absences resume during a week (2021-02-01 to 2021-02-06)
SELECT
affectation.`NUMERO` AS NUMERO,
etudiant.NOM AS NOMS,
etudiant.PRENOM AS PRENOMS,
etudiant.MATRICULE AS `MATR.`,
SUM(HOUR(seance_cours.DUREE)) AS `TOTAL H.A`,
SUM(IF(absence.ID_ABSENCE = absence_justifiee.ID_ABSENCE,0,HOUR(seance_cours.DUREE))) AS `TOTAL N.J`
FROM `absence`
INNER JOIN seance_cours on absence.ID_SEANCE = seance_cours.ID_SEANCE
INNER JOIN etudiant ON absence.MATRICULE = etudiant.MATRICULE
INNER JOIN affectation ON etudiant.MATRICULE = affectation.MATRICULE
LEFT JOIN absence_justifiee ON absence.ID_ABSENCE = absence_justifiee.ID_ABSENCE
WHERE
affectation.CODE_ANNEE = '2020/2021' AND
affectation.CODE_CLASSE = 'L2C' AND
absence.DEBUT_SEMAINE = '2021-02-01'
GROUP BY
`NUMERO`
ORDER BY
`NUMERO`
ASC;
List in descending order of gravity the students going to the disciplinary council with thier sanctions in the school year
SELECT
affectation.`NUMERO` AS NUMERO,
etudiant.NOM AS NOMS,
etudiant.PRENOM AS PRENOMS,
etudiant.MATRICULE AS `MATR.`,
SUM(IF(absence.ID_ABSENCE = absence_justifiee.ID_ABSENCE,0,HOUR(seance_cours.DUREE))) AS `TOTAL N.J`,
SANCTION.LIBELLE_SANCTION AS SANCTION,
recevoir.MOTIF_SANCTION
FROM `absence`
INNER JOIN seance_cours on absence.ID_SEANCE = seance_cours.ID_SEANCE
INNER JOIN etudiant ON absence.MATRICULE = etudiant.MATRICULE
INNER JOIN recevoir ON etudiant.MATRICULE = recevoir.MATRICULE
INNER JOIN sanction ON recevoir.ID_SANCTION = SANCTION.ID_SANCTION
INNER JOIN affectation ON etudiant.MATRICULE = affectation.MATRICULE
LEFT JOIN absence_justifiee ON absence.ID_ABSENCE = absence_justifiee.ID_ABSENCE
WHERE
affectation.CODE_ANNEE = '2020/2021' AND
affectation.CODE_CLASSE = 'L2C' AND
(MONTH(seance_cours.DATE_COURS) BETWEEN 1 AND 3) AND
SANCTION.ID_SANCTION = (SELECT DISTINCT MAX(ID_SANCTION) FROM RECEVOIR WHERE RECEVOIR.MATRICULE = ETUDIANT.MATRICULE)
GROUP BY
etudiant.NOM
ORDER BY
`TOTAL N.J`
DESC;