/* Jeu de données des cours de Stanford francisé / Dataset from Stanford classes translated in French
** Fabien Duchateau, 2020
** Version francisée en ligne : https://perso.liris.cnrs.fr/fabien.duchateau/BDW1/
** Sources : https://lagunita.stanford.edu/courses/Home/Databases/Engineering/about
*/

/* Avant d'utiliser ces requêtes, il faut créer les tables avec le script /
** Before using these queries, you need to create the tables using the script :
** 		MySQL / MariaDB : "dataset-candidatures.sql"
*/

/**************************************************/
/********* Requêtes cours regroupements ***********/
/**************************************************/

/* Clause GROUP BY et opérateurs d'agrégation */

SELECT nomE 
FROM Élève 
WHERE idE < 700;

SELECT nomE 
FROM Élève
WHERE idE < 700
GROUP BY nomE;

/* compte le nombre d'élèves */
SELECT COUNT(*) 
FROM Élève;

/* idem */
SELECT COUNT(idE) 
FROM Élève;

/* idem */
SELECT COUNT(nomE)
FROM Élève;

/* compte le nombre d'élèves avec un nom différent */
SELECT COUNT(DISTINCT nomE)
FROM Élève;

/* compte le nombre d'élèves avec une moyenne supérieure à 18 */
SELECT COUNT(*) FROM Élève
WHERE moyenneLycée > 18;

/* nombre de candidatures par université */
SELECT COUNT(*) AS nbCandidatures, nomU
FROM Élève e NATURAL JOIN Candidature c
GROUP BY nomU
ORDER BY nomU;
/* ligne de commentaire inutile pour maintenir la numérotation dees lignes dans le fichier .tex */

/* nombre de candidatures par université puis par département */
SELECT nomU, COUNT(*) AS nbCandidatures
FROM Candidature c
GROUP BY nomU
ORDER BY nomU;

SELECT nomU, département, COUNT(*) AS nbC
FROM Candidature c
WHERE nomU IN ('INSA', 'UCB')
GROUP BY nomU, département;

/* le nombre de candidatures pour chaque étudiant-e (mauvais attribut de regroupement nomE, qui regroupe les 2 Chloé) */
SELECT nomE, COUNT(*)
FROM Élève e INNER JOIN Candidature c
ON e.idE = c.idE
GROUP BY nomE;

/* le nombre de candidatures pour chaque étudiant-e (bon attribut de regroupement idE) */
SELECT e.idE, nomE, COUNT(*)
FROM Élève e INNER JOIN Candidature c
ON e.idE = c.idE
GROUP BY e.idE, nomE;

/* moyenne la plus élevée */
SELECT MAX(moyenneLycée) AS moyenneMax
FROM Élève;

/* les universités où ont candidaté les étudiants avec la moyenne la plus faible */
SELECT e.idE, nomE, nomU
FROM Candidature c INNER JOIN Élève e ON c.idE = e.idE
WHERE moyenneLycée = 
			(SELECT MIN(moyenneLycée) FROM Élève);

/* idem mais on veut toustes les étudiantes avec la moyenne la plus faible (et leurs universités candidatées) */
SELECT e.idE, nomE, nomU
FROM Candidature c RIGHT OUTER JOIN Élève e ON c.idE = e.idE
WHERE moyenneLycée = (SELECT MIN(moyenneLycée) FROM Élève);

/* moyenne des candidats par université */
SELECT nomU, round(AVG(moyenneLycée))
FROM Élève NATURAL JOIN Candidature
GROUP BY nomU;

/* l'université avec la plus faible moyenne de ses candidats */
SELECT tableMoy.univ, tableMoy.moy
FROM (SELECT nomU AS univ, round(AVG(moyenneLycée)) AS moy 
		FROM Élève E NATURAL JOIN Candidature C
		GROUP BY nomU)  tableMoy
WHERE tableMoy.moy=(SELECT min(tableMoy2.moy2) 
					FROM (SELECT round(AVG(moyenneLycée)) AS moy2 FROM Élève E NATURAL JOIN Candidature C
					GROUP BY nomU) tableMoy2);


/* Clause HAVING */

/* les départements d'une université qui ont au moins 2 candidatures */
SELECT COUNT(*) AS nbC, nomU, département
FROM Élève e NATURAL JOIN Candidature c
GROUP BY nomU, département
HAVING COUNT(*) >= 2
ORDER BY nomU, département;

/* le nombre de candidatures des élèves qui ont candidaté dans des universités qui proposent au moins 5 disciplines */
SELECT e.idE, nomE, COUNT(*) AS nbC
FROM Élève e NATURAL JOIN Candidature c 
WHERE nomU IN(SELECT nomU FROM Candidature GROUP BY nomU
			HAVING COUNT(DISTINCT département) >= 5) 
GROUP BY e.idE, nomE;

/* disciplines dont le meilleur candidat a une moyenne inférieure à la moyenne des moyennes générales des candidatures */
SELECT MAX(moyenneLycée) AS meilleureMoyenne, département, (SELECT AVG(moyenneLycée) FROM Élève) AS moyenneCandidatures
FROM Élève e NATURAL JOIN Candidature c
GROUP BY département
HAVING MAX(moyenneLycée) < (SELECT AVG(moyenneLycée) FROM Élève);

SELECT MAX(moyenneLycée) AS meilleureMoyenne, département 
FROM Élève e NATURAL JOIN Candidature c
GROUP BY département
HAVING MAX(moyenneLycée) < (SELECT AVG(moyenneLycée) FROM Élève e2, Candidature c2 WHERE c2.idE = e2.idE AND c2.département = c.département);

/* disciplines dont le meilleur candidat a une moyenne inférieure ou égale à la moyenne des moyennes générales des candidatures de la discipline */
SELECT MAX(moyenneLycée) AS meilleureMoyenne, département, (SELECT AVG(moyenneLycée) 
												FROM Élève e2 NATURAL JOIN Candidature c2 
												WHERE c2.département = c.département) AS moyenneGeneraledépartement 
FROM Élève e NATURAL JOIN Candidature c
GROUP BY département
HAVING MAX(moyenneLycée) <= (SELECT AVG(moyenneLycée) FROM Élève e2 NATURAL JOIN Candidature c2 WHERE c2.département = c.département);


/* Divisions */

/* division avec not exists (division avec reste) : les universités qui proposent toutes les disciplines de la vie (bio-) */
SELECT DISTINCT nomU
FROM Candidature c1
WHERE c1.département LIKE 'bio%' AND NOT EXISTS
   (SELECT *
    FROM Candidature c2
    WHERE c2.département LIKE 'bio%' AND NOT EXISTS
       (SELECT *
        FROM Candidature c3
        WHERE c1.nomU = c3.nomU
        AND c3.département = c2.département));

/* division avec not exists (division exacte) : les universités qui proposent toutes les disciplines de la vie (bio-) */
SELECT DISTINCT nomU,count(*)
FROM Candidature c1
WHERE c1.département LIKE 'bio%' AND NOT EXISTS
   (SELECT *
    FROM Candidature c2
    WHERE c2.département LIKE 'bio%' AND NOT EXISTS
       (SELECT *
        FROM Candidature c3
        WHERE c1.nomU = c3.nomU
        AND c3.département = c2.département AND c3.département LIKE 'bio%'))
GROUP BY nomU
HAVING COUNT(*) =
   (SELECT COUNT(DISTINCT département)
    FROM Candidature WHERE département LIKE 'bio%');

/* division avec reste : les élèves qui ont candidaté dans toutes les universités de Lyon (et éventuellement d'autres) */
SELECT DISTINCT c1.idE
FROM Candidature c1
WHERE NOT EXISTS(
			SELECT *
			FROM Université u
			WHERE u.ville = 'Lyon' AND NOT EXISTS(
								SELECT *
								FROM Candidature c2
								WHERE c2.idE = c1.idE AND c2.nomU = u.nomU
));

/* division exacte : les élèves qui ont candidaté dans toutes les universités de Lyon et uniquement celles de Lyon */
SELECT DISTINCT c1.idE
FROM Candidature c1
WHERE NOT EXISTS(
			SELECT *
			FROM Université u
			WHERE u.ville = 'Lyon' AND NOT EXISTS(
								SELECT *
								FROM Candidature c2
								WHERE c2.idE = c1.idE AND c2.nomU = u.nomU))
GROUP BY c1.idE
HAVING COUNT(*) = (SELECT COUNT(DISTINCT nomU)
								FROM Université 
								WHERE ville = 'Lyon');

/* requête HAVING simple (sans sous-requête) : Nombre de candidatures par décision, avec un minimum de 5 candidatures */

SELECT décision, COUNT(*) AS nb
FROM Candidature c
GROUP BY décision
HAVING COUNT(*) > 5;


