/* 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 jointures **************/
/**************************************************/


/* produit cartésien / cartesian product */

SELECT nomU,nomE 
FROM Université, Élève;

/* les paires d'élèves avec le même nom */

SELECT e1.idE, e2.idE, e1.nomE
FROM Élève e1, Élève e2
WHERE e1.nomE = e2.nomE AND e1.idE <> e2.idE ;

/* jointures internes / internal joins */

SELECT DISTINCT e.idE, nomE, moyenneLycée, nomU
FROM Élève e NATURAL JOIN Candidature c
WHERE moyenneLycée > 19;

SELECT e.idE, nomE
FROM Élève e INNER JOIN Candidature c ON e.idE = c.idE 
     INNER JOIN Université u ON c.nomU = u.nomU 
WHERE u.ville = 'Grenoble';

SELECT e.idE, nomE 
FROM Élève e, Candidature c, Université u 
WHERE e.idE = c.idE AND c.nomU = u.nomU AND u.ville ='Grenoble';

/* jointures externes / external joins */

SELECT e.idE, nomE, nomU 
FROM Élève e LEFT OUTER JOIN Candidature c 
	 ON c.idE = e.idE 
WHERE moyenneLycee >= 19;

SELECT e.idE, nomE, nomU 
FROM Élève e FULL OUTER JOIN Candidature c
	 ON c.idE = e.idE
WHERE moyenneLycee >= 19;

SELECT * FROM 
Élève e RIGHT OUTER JOIN Candidature c ON c.idE = e.idE 
WHERE moyenneLycee >= 19;

/* semi-jointures */

SELECT e.*
FROM Élève e NATURAL JOIN Candidature c 
	 NATURAL JOIN Université
WHERE ville = 'Grenoble';

/* auto-jointures */

SELECT DISTINCT c1.idE, c2.idE
FROM Candidature c1 INNER JOIN Candidature c2 
		ON c1.nomU = c2.nomU 
		AND c1.département = c2.département 
		AND c1.idE < c2.idE;

/* autres exemples de jointures / more joins */

-- les universités qui ont un effectif supérieur à celui de UCB

SELECT r2.nomU, r2.effectif 
FROM Université r1, Université r2 
WHERE r1.nomU= 'UCB' AND r2.effectif > r1.effectif ;

-- jointure naturelle entre Élève et Candidature

SELECT * FROM Élève NATURAL JOIN Candidature;

-- jointure externe à gauche sur les noms des élèves, leur moyenne lycée et les universités candidatées

SELECT e.idE, nomE, e.moyenneLycee, nomU FROM Élève e LEFT OUTER JOIN Candidature c ON c.idE = e.idE;


