/* 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 opérations ensemblistes ******/
/**************************************************/


/* union des identifiants étudiants avec plus de 18 de moyenne ou qui sont acceptés en informatique */

SELECT idE, nomE FROM Éleve 
WHERE moyenneLycée > 18 
UNION 
SELECT e.idE, nomE FROM Élève e, Candidature c 
WHERE e.idE = c.idE AND décision = 'O' AND département = 'informatique';

/* idem avec un ORDER BY */

(SELECT idE, nomE FROM Élève WHERE moyenneLycée > 18)
UNION 
(SELECT e.idE, nomE FROM Élève e, Candidature c 
WHERE e.idE = c.idE AND décision = 'O' AND département = 'informatique')
ORDER BY nomE;

/* intersection : élèves qui ont candidaté en bioinformatique et en informatique */

SELECT e.idE, nomE
FROM Élève e NATURAL JOIN Candidature
WHERE département = 'bioinformatique'
INTERSECT
SELECT e.idE, nomE
FROM Élève e NATURAL JOIN Candidature
WHERE département = 'informatique';

SELECT e.idE, nomE
FROM Élève e NATURAL JOIN Candidature
WHERE département = 'bioinformatique' AND
EXISTS(SELECT *
		FROM Candidature c
		WHERE e.idE=c.idE and c.département='informatique');

/* difference : les élèves qui ont candidaté en informatique mais pas en bioinformatique */

SELECT idE FROM Élève
WHERE département = 'informatique'
EXCEPT
SELECT idE FROM Élève
WHERE département = 'bioinformatique';

SELECT c1.idE
FROM Candidature c1 LEFT OUTER JOIN Candidature c2 ON c1.idE = c2.idE
WHERE c1.département = 'informatique' AND c2.département = 'bioinformatique' AND c2.idE IS NULL;


