/* 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 sous-requetes ***********/
/**************************************************/

/* les universités qui sont dans la même ville que UCB */

SELECT * 
FROM Université 
WHERE ville = (SELECT ville 
				FROM Université 
				WHERE nomU = 'UCB');

/* idem sans la ligne UCB */
SELECT * 
FROM Université 
WHERE nomU <> 'UCB' AND ville = 
				(SELECT ville 
				FROM Université 
				WHERE nomU = 'UCB');

/* idem avec requête dans le from */

SELECT *
FROM Université u, (SELECT ville 
					FROM Université
					WHERE nomU = 'UCB') villeUCB
WHERE u.ville = villeUCB.ville;

/* pour chaque université de Lyon, la différence d'effectif avec l'université UJM */

SELECT nomU, effectif - (SELECT effectif 
							FROM Université
							WHERE nomU = 'UJM') AS diff
FROM Université
WHERE ville = 'Lyon';

/* les élèves qui ont candidaté (IN) */

SELECT nomE 
FROM Élève 
WHERE idE IN (SELECT idE FROM Candidature);

/* les élèves qui ont candidaté (EXISTS) */

SELECT * 
FROM Élève e 
WHERE EXISTS (SELECT * FROM Candidature c WHERE c.idE = e.idE);

/* les élèves qui ont candidaté à Lyon */

SELECT c.idE, nomE
FROM Candidature c NATURAL JOIN Élève e 
WHERE nomU IN (SELECT nomU 
					FROM Université 
					WHERE ville = 'Lyon');

/* la ou les universités avec le plus grand effectif */

SELECT * 
FROM Université
WHERE effectif >= ALL (SELECT effectif 
						FROM Université);

/* les universités avec même effectif et même ville que UCB */

SELECT *
FROM Université 
WHERE (ville, effectif) =
							(SELECT ville, effectif 
							FROM Université 
							WHERE nomU = 'UCB');

/* les universités qui ont reçu des candidatures (EXISTS) */

SELECT nomU
FROM Université u 
WHERE EXISTS (SELECT * 
				FROM Candidature c 
				WHERE c.nomU = u.nomU);

/* les étudiants qui ont fait des candidatures similaires (nomU, departement) à celle de Ana (idE=123) */

SELECT e.idE, e.nomE, c.nomU, c.departement 
FROM Élève e, Candidature c 
WHERE e.idE=c.idE AND (nomU, departement) = ANY(SELECT nomU, departement FROM Candidature WHERE idE = 123);

/* les universités qui sont dans la même ville qu'une université qui a plus d'élèves que UCB */

SELECT * 
FROM Université 
WHERE ville IN (
				SELECT DISTINCT ville 
				FROM Université 
				WHERE effectif > (
									SELECT effectif 
									FROM Université 
									WHERE nomU = 'UCB'));



