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

/* attention : fonctionne sous PostgreSQL, aucune garantie pour les autres SGBD */

SELECT idE, moyenneLycée * 5 AS moyenne100
FROM Élève;

SELECT nomU
FROM Université
WHERE nomU != 'UCB';

SELECT CONCAT(nomU, '-', ville) AS nom-ville
FROM Université;

SELECT nomE 
FROM Élève 
WHERE nomE LIKE '%e%';

/* les élèves qui ont candiadté dans une université de 3 lettres */
SELECT DISTINCT e.idE, nomE
FROM Élève e NATURAL JOIN Candidature
WHERE nomU IN (SELECT nomU
					FROM Université
					WHERE nomU LIKE '___');

/* moyenne des éléves sur 10 */
SELECT idE, ROUND(moyenneLycée / 2, 0) AS moyenne10
FROM Élève;

/* la date courante formatée */
SELECT DATE_FORMAT(NOW(),'%d%M%Y')
AS dateFormatée;



/* autres exemples de fonctions SGBD (non utilisées dans le cm) */

/* Comparaison approximative : les élèves dont le nom commence par un a */
SELECT * FROM Élève WHERE nomE LIKE 'a%';
SELECT * FROM Élève WHERE nomE LIKE 'A%';
SELECT * FROM Élève WHERE nomE LIKE 'a%';

/* les étudiants dont le nom contient un a */
SELECT * FROM Élève WHERE nomE LIKE '%a%';

/* les élèves qui ont candiaté dans une université de 3 lettres */
SELECT DISTINCT e.idE, e.nomE
FROM Élève e , Candidature c
WHERE e.idE = c.idE AND c.nomU IN (SELECT nomU
				   FROM Université
				   WHERE nomU LIKE '___');

/* fonctions numériques (pas besoin de clause FROM avec Postgres ou MariaDB) */
SELECT 6*3 AS resultat;
SELECT ABS(-5);
SELECT ROUND(155.7965);
SELECT ROUND(155.7965, 2);
SELECT ROUND(155.7965, -2);

/* le nom des élèves avec une moyenne pondérée par l'effectif du lycée */
SELECT idE, nomE, moyenneLycee, Round(cast(moyenneLycee*(effectifLycee/1000.0) AS decimal),2) AS moyennePonderee FROM Élève;

/* fonctions chaînes de caractères (concaténation avec || sous Postgres, CONCAT(...) sous MariaDB) */
SELECT 'ab' || 'cd';
SELECT CONCAT('ab', 'cd');
SELECT CONCAT('valeur = ', 42);
SELECT lower('abCDEfG');

/* date et heure du jour */
SELECT current_date AS aujourdhui;
SELECT current_time;
SELECT now();
	
/* date de demain */
SELECT current_date+1 AS demain;

/* ajout de deux mois à la date du jour (Postgres seulement) */
-- SELECT current_date + interval '2 month';

/* différence en nombre de jours entre la date du jour et le 1er septembre 2012 */
SELECT (current_date) - date '2012-09-01';

/* fonction ascii */
SELECT ascii('abCDEfG');

/* conversions de type (Postgres seulement) */
-- SELECT to_number('abCDEfG');
-- SELECT to_number('12,454.8-', '99G999D9S')
-- SELECT to_date('01 Sep 2012', 'DD Mon YYYY');


