/******************** 
	CORRECTION des exercices du cours de SQL

		ENONCÉ : https://itsforeveryone.org/lycee/nsi/docs/bdd/TNSI-Cours-SQL.pdf 
		CODE SQL DE CRÉATION DE LA BASE DE DONNÉES : https://itsforeveryone.org/lycee/nsi/code/bdd/portables/portables.sql
		
		Copyleft M. Tonnelier 2023
********************/

/** 
	EXERCICE 2 - Page 7 
**/

/* 1. Écrire une requête qui extrait les colonnes id_modele et id_systeme de la table poste, en élimi-
	nant les doublons sur les lignes du résultat. */
SELECT DISTINCT id_modele, id_systeme
FROM poste;
	
/* 2. Écrire une requête qui extrait les colonnes id_poste, id_modele et id_systeme de la table poste.
	Est-il nécessaire d’éliminer les doublons sur les lignes du résultat ? */
SELECT id_poste, id_modele, id_systeme
FROM poste;
	
	

/** 
	EXERCICE 5 - Page 13 
**/

/* 1. Écrire une requête SQL qui permet d’obtenir le nombre de postes dans la table poste pour lesquels
	la valeur de l’attribut id_systeme est 5 (système ubuntu en version 18.04 installé). */
SELECT COUNT(*)
FROM poste
WHERE id_systeme = 5;
	
/* 2. Écrire une requête SQL qui permet d’obtenir la valeur minimale de l’attribut id_poste pour les
	postes dont le numéro de série comporte au moins un 4. */
SELECT MIN(id_poste)
FROM poste
WHERE num_serie LIKE "%4%";
	
	
	
/** 
	EXERCICE 6 - Page 15 
**/

/* 1. Écrire une requête SQL qui affiche une nouvelle table qui contient pour chaque poste son identi-
	fiant id_poste, son numéro de série, son nom, son modèle, son fabricant et l’année de livraison. */
SELECT id_poste, num_serie, poste.nom AS nom_poste, modele.nom AS nom_modele, fabricant, annee
FROM poste
JOIN modele ON poste.id_modele = modele.id_modele;

/* 2. Modifier la requête précédente pour ne sélectionner dans le résultat de la jointure que les modèles
	de fabricant ’hp’. */
SELECT id_poste, num_serie, poste.nom AS nom_poste, modele.nom AS nom_modele, fabricant, annee
FROM poste
JOIN modele ON poste.id_modele = modele.id_modele
WHERE fabricant = "hp";
	
/* 3. Modifier la requête précédente pour ordonner les résultats par ordre décroissant d’année de livraison. */
SELECT id_poste, num_serie, poste.nom AS nom_poste, modele.nom AS nom_modele, fabricant, annee
FROM poste
JOIN modele ON poste.id_modele = modele.id_modele
WHERE fabricant = "hp"
ORDER BY annee DESC;



/** 
	EXERCICE 7 - Jointures multiples - Page 16 et 17
**/

/* 1. Déterminer la clef primaire et les clefs étrangères de la table affectation. */

	/*	PRIMARY KEY(id_poste, code_cours),
		FOREIGN KEY (id_poste) REFERENCES poste(id_poste),
		FOREIGN KEY (code_cours) REFERENCES cours(code) */

	
/* 2. Que représente le résultat de la requête suivante ? */
	SELECT poste.nom, description, semestre
	FROM poste JOIN affectation ON poste.id_poste=affectation.id_poste
	JOIN cours ON affectation.code_cours = cours.code ;
	/* Cette requête affiche tous les postes affectés à un cours */

/* 3. Écrire une requête dont le résultat est une table de colonnes nom_poste, nom_modele , fabricant,
	nom_systeme, version dont on donne un extrait. On renommera certaines colonnes avec AS. */
SELECT poste.nom AS nom_poste, modele.nom AS nom_modele, fabricant, systeme.nom AS nom_systeme, version
FROM poste
JOIN modele ON poste.id_modele = modele.id_modele
JOIN systeme ON poste.id_systeme = systeme.id_systeme;

/* 4. Écrire une requête dont le résultat est une table avec pour tous les postes affectés au cours "UNIX",
	leur nom, celui de leur système d’exploitation et sa version. */
SELECT poste.nom AS nom_poste, systeme.nom AS nom_systeme, version
FROM poste
JOIN systeme ON poste.id_systeme = systeme.id_systeme
JOIN affectation ON affectation.id_poste = poste.id_poste
JOIN cours ON affectation.code_cours = cours.code
WHERE cours.description = "UNIX";

/* 5. Écrire une requête qui calcule, pour le cours "Algorithmique et programmation récursive",
	le nombre de postes, leur âge moyen, l’âge minimal et l’âge maximal. */

/** REMARQUE : strftime('%Y',DATE()) permet d'obtenir l'année actuelle 
	cf https://www.sqlite.org/lang_datefunc.html#strftm **/
SELECT COUNT(*) AS nb_postes, AVG(strftime('%Y',DATE()) - annee) AS age_moyen, MIN(strftime('%Y',DATE()) - annee) AS age_min, MAX(strftime('%Y',DATE()) - annee) AS age_max
FROM poste
JOIN modele ON poste.id_modele = modele.id_modele
JOIN affectation ON affectation.id_poste = poste.id_poste
JOIN cours ON affectation.code_cours = cours.code
WHERE cours.description = "Algorithmique et programmation récursive";

/* 6.a. Écrire d’abord une requête qui détermine l’ensemble des postes qui ont été affectés au cours
"UNIX" avec leur nom de poste et leur numéro de série. */
SELECT poste.nom AS nom_poste, num_serie
FROM poste
JOIN affectation ON affectation.id_poste = poste.id_poste
JOIN cours ON affectation.code_cours = cours.code
WHERE cours.description = "UNIX";


/* 6.b. Écrire ensuite une requête qui détermine l’ensemble des postes qui ont été affectés au cours
"Algorithmique et programmation récursive" avec leur nom de poste et leur numéro
de série. */
SELECT poste.nom AS nom_poste, num_serie
FROM poste
JOIN affectation ON affectation.id_poste = poste.id_poste
JOIN cours ON affectation.code_cours = cours.code
WHERE cours.description = "Algorithmique et programmation récursive";

/* 6.c. Déduire des questions précédentes une requête qui détermine l’ensemble des postes qui
ont été affectés à la fois au cours "UNIX" et au cours "Algorithmique et programmation
récursive", avec leur nom de poste et leur numéro de série.*/
SELECT poste.nom AS nom_poste, num_serie
FROM poste
JOIN affectation AS affectation_unix ON affectation_unix.id_poste = poste.id_poste
JOIN cours AS cours_unix ON affectation_unix.code_cours = cours_unix.code
JOIN affectation AS affectation_algo ON affectation_algo.id_poste = poste.id_poste
JOIN cours AS cours_algo ON affectation_algo.code_cours = cours_algo.code
WHERE cours_unix.description = "UNIX" AND cours_algo.description = "Algorithmique et programmation récursive";