/* Listes de requêtes */

/* A. Requêtes simples */

/* 1. Liste des élèves par ordre croissant de dates de naissance */

SELECT *
FROM Eleve
ORDER BY datenaiss ASC;

/* 2. L'élève le plus vieux */

SELECT *
FROM Eleve
WHERE datenaiss = (
	SELECT min(datenaiss)
	FROM Eleve
);

/* 3. Nombre de questionnaires catégorisation */

SELECT count(id)
FROM Questionnaire
WHERE id IN (
	SELECT id
	FROM Categorisation
);

/* 4. Enoncé de la question 14 */

SELECT qu.libelle as question, r.libelle as reponse
FROM Question qu, Reponse r
WHERE r.qu_id = qu.id
AND qu.id = 14;

/* 5. Listes des modules, activités et programmes correspondants */

SELECT P.libelle AS programme, M.libelle AS module, A.libelle AS activite
FROM Activite A, Module M, Programme P
WHERE A.mod_id = M.id
AND M.prg_id = P.id
ORDER BY P.id ASC;

/* 6. Réponses enregistrées dans la session 2 avec les questions correspondantes */

SELECT e.nom as eleve, q.libelle as question, r.libelle as reponse
FROM Eleve e, Question q, Reponse r, Session_ s, rep_valider v
WHERE e.id = s.elv_id
AND v.ses_id = s.id
AND v.rep_id = r.id
AND r.qu_id = q.id
AND s.id = 2;

/* 7. Nombre de questionnaires répondus par chaque élève */

SELECT e.nom, e.prenom, count(s.id) AS nombre
FROM Eleve e LEFT JOIN Session_ s
ON s.elv_id = e.id
GROUP BY e.nom, e.prenom;

/* 8. Listes des remarques en question ouverte itérées pour l'évaluation n°1 */

SELECT libelle
FROM Reponse
WHERE qu_id = (
	SELECT id
	FROM Question
	WHERE qu_type = 'ouverte'
	AND qa_id = (
		SELECT id
		FROM Questionnaire
		WHERE id = (
			SELECT qa_id
			FROM Evaluation
			WHERE id = 1
		)
	)
);

/* 9. Nombre de réponses (avec les questions) proposées pour chaque question dans le QCM n°1 */

SELECT qu.libelle, count(r.id)
FROM Question qu, Reponse r
WHERE r.qu_id = qu.id
AND qu.qa_id = (
	SELECT id
	FROM Questionnaire
	WHERE id = (
		SELECT qa_id
		FROM QCM
		WHERE id = 1
	)
)
GROUP BY qu.libelle;

/* B. Requêtes avancées/traitement de questionnaires */

/* 10. Visualisation des statistiques de l'évaluation n°1 */

SELECT qu.libelle AS question, r.libelle AS reponse, count(rv.rep_id) AS voix
FROM Question qu, Reponse r LEFT JOIN rep_valider rv
ON r.id = rv.rep_id
WHERE r.qu_id = qu.id
AND qu.qa_id = (
	SELECT qa_id
	FROM Evaluation
	WHERE id = 1
)
GROUP BY qu.libelle, r.libelle
ORDER BY qu.libelle;

/* 11. Les bonnes réponses du QCM n°1 (la session avec elv_id = NULL) */

SELECT qu.libelle as question, r.libelle as reponse
FROM Question qu, reponse r
WHERE r.qu_id = qu.id
AND r.id IN (
	SELECT rep_id
	FROM rep_valider
	WHERE ses_id = (
		SELECT id
		FROM Session_
		WHERE elv_id IS NULL
		AND qa_id = (
			SELECT id
			FROM Questionnaire
			WHERE id = (
				SELECT qa_id
				FROM QCM
				WHERE id = 1
			)
		)
	)
)
ORDER BY qu.id ASC;

/* 12. Récupérer les réponses (avec questions) données par TYNDAL Pierre-Alexandre dans les évaluations du programme ING 1 */

SELECT qu.libelle AS question, r.libelle AS reponse
FROM Question qu, Reponse r
WHERE r.qu_id = qu.id
AND qu.qa_id IN (
	SELECT id
	FROM Questionnaire
	WHERE id IN (
		SELECT qa_id
		FROM Evaluation
		WHERE mod_id IN (
			SELECT id
			FROM Module
			WHERE prg_id = (
				SELECT id
				FROM Programme
				WHERE libelle = 'ING 1'
			)
		)
	)
)
AND r.id IN (
	SELECT rep_id
	FROM rep_valider
	WHERE ses_id IN (
		SELECT id
		FROM Session_
		WHERE elv_id = (
			SELECT id
			FROM Eleve
			WHERE lower(nom) = 'tyndal'
			AND lower(prenom) = 'pierre-alexandre'
		)
	)
);

/* 13. Obtenir la catégorie à laquelle appartient DOS Hugo selon la catégorisation n°1 */

SELECT categ, score FROM (
	SELECT c.libelle AS categ, count(rc.rep_id) AS score
	FROM Categorie c LEFT JOIN rep_categoriser rc
	ON c.id = rc.ctg_id
	AND rc.rep_id IN (
		SELECT rep_id
		FROM rep_valider
		WHERE ses_id = (
			SELECT id
			FROM Session_
			WHERE elv_id = (
				SELECT id
				FROM Eleve
				WHERE lower(nom) = 'dos'
				AND lower(prenom) = 'hugo'
			)
			AND qa_id = (
				SELECT qa_id
				FROM Categorisation
				WHERE id = 1
			)
		)
	)
	GROUP BY c.libelle
	ORDER BY score DESC
)
WHERE ROWNUM = 1;

/* 14. Obtenir les bonnes réponses de Jean-Claude Van Damme au QCM se rapportant à la Transverse des ING 1 (utilisation d'intersect) */

/* Les bonnes réponses (de tous les QCM) */
SELECT rep_id
FROM rep_valider
/* NB: En remplaçant IN par NOT IN, on obtient les mauvaises réponses de JCVD pour cette requête */
WHERE ses_id IN (
	SELECT id
	FROM Session_
	WHERE elv_id IS NULL
)
INTERSECT
/* INTER : Les réponses de Jean-Claude au QCM Transverse ING1 */
SELECT rep_id
FROM rep_valider
WHERE ses_id IN (
	SELECT id
	FROM Session_
	WHERE elv_id = (
		SELECT id
		FROM Eleve
		WHERE lower(nom) = 'van damme'
		AND lower(prenom) = 'jean-claude'
	)
	AND qa_id = (
		SELECT qa_id
		FROM QCM
		WHERE act_id IN (
			SELECT id
			FROM Activite
			WHERE mod_id = (
				SELECT id
				FROM Module
				WHERE libelle = 'Transverse'
				AND prg_id = (
					SELECT id
					FROM Programme
					WHERE libelle = 'ING 1'
				)
			)
		)
	)
);

/* 15. Ceux qui ont davantage de bonnes réponses que la moyenne des répondants au QCM n°1 */

SELECT e.nom, e.prenom, count(rv.rep_id) AS score
FROM Eleve e, Session_ s, rep_valider rv
WHERE e.id = s.elv_id
AND s.id = rv.ses_id
AND rv.rep_id IN (
	SELECT rep_id
	FROM rep_valider
	WHERE ses_id = (
		SELECT id
		FROM Session_
		WHERE elv_id IS NULL
		AND qa_id = (
			SELECT qa_id
			FROM QCM
			WHERE id = 1
		)
	)
)
GROUP BY e.nom, e.prenom
HAVING count(rv.rep_id) >= (
	SELECT avg(score) FROM (
		SELECT s.elv_id, count(rv.rep_id) AS score
		FROM Session_ s, rep_valider rv
		WHERE s.id = rv.ses_id
		AND rv.rep_id IN (
			SELECT rep_id
			FROM rep_valider
			WHERE ses_id = (
				SELECT id
				FROM Session_
				WHERE elv_id IS NULL
				AND qa_id = (
					SELECT qa_id
					FROM QCM
					WHERE id = 1
				)
			)
		)
		GROUP BY s.elv_id
	)
);