-- 1. Quels sont les deux derniers chiffres des années de naissance
-- des peintres ?
SELECT DISTINCT annee_naissance % 100 FROM peintres;

-- 2. Quels sont les prénoms, noms et les durées de vie des peintres
-- qui sont morts avant leur 40ième anniversaire ?
SELECT prenom, nom, (annee_mort - annee_naissance) AS duree_vie
FROM peintres
WHERE duree_vie < 40;

-- 3. Pour les oeuvres dont le titre fait moins de 20 caractères et
-- qui sont exposées dans un musée dont le nom commence par 'Musée',
-- on calcule la chaîne '<oeuvre> est exposé au <MUSEE>' où <oeuvre>
-- est le titre de l'oeuvre et <MUSEE> le nom du musée, en majuscules.
SELECT o.nom || ' est exposé au ' || UPPER(m.nom)
FROM oeuvres o JOIN musees m ON o.id_musee = m.id
WHERE LENGTH(o.nom) < 20 AND m.nom LIKE 'Musée%';

-- 4. Quel est le nom complet (prénom et nom en une seule chaîne) des
-- peintres dont le prénom contient un 'a', le nom ne commence pas par
-- 'Ve' et dont l'avant dernière lettre est un 'i'. La colonne de la
-- table doit s'intituler 'nom_complet'.
SELECT prenom || ' ' || nom AS nom_complet 
FROM peintres
WHERE prenom LIKE '%a%' AND nom NOT LIKE 'Ve%' AND nom LIKE '%i_';

-- 5. Quelle est l'année de naissance d'un peintre la plus ancienne ?
SELECT MIN(annee_naissance) FROM peintres;

-- 6. Quelle est l'année de naissance moyenne des peintres français ?
SELECT AVG(annee_naissance)
FROM peintres p JOIN pays c ON p.id_pays = c.id
WHERE c.nom = 'France';

-- 7. Combien d'oeuvres sont exposées au Musée du Louvre ?
SELECT COUNT(*) 
FROM oeuvres o JOIN musees m ON o.id_musee = m.id
WHERE m.nom = 'Musée du Louvre';

-- 8. Quel est le nombre de lettres total des noms des musées qui ne sont pas en France ?
SELECT SUM(LENGTH(m.nom))
FROM musees m
  JOIN villes v JOIN pays c
  ON m.id_ville = v.id AND v.id_pays = c.id
WHERE c.nom != 'France';

-- 9. Quelle est la ville dont l'identifiant correspond au nombre de
-- peintres néerlandais ?
SELECT *
FROM villes
WHERE id =
  (SELECT COUNT(*)
   FROM peintres p JOIN pays c 
   ON p.id_pays = c.id
   WHERE c.nom = 'Pays-Bas');

-- 10. Combien de peintres sont nés après l'année moyenne de naissance
-- d'un peintre ?
SELECT COUNT(*) FROM peintres
WHERE annee_naissance >= (SELECT AVG(annee_naissance) FROM peintres);

-- 11. Quel est le nom et l'année de naissance des peintres qui sont
-- nés le plus récemment ?
SELECT nom, annee_naissance FROM peintres
WHERE annee_naissance = (SELECT MAX(annee_naissance) FROM peintres);

-- 12. Donner toutes les informations sur les peintres triés par année
--  de naissance croissante ?
SELECT * FROM peintres 
ORDER BY annee_naissance;

-- 13. Donner les noms des musées triés par nombre de lettres décroissant
SELECT * FROM musees 
ORDER BY LENGTH(nom) DESC;

-- 14. Donner les noms des oeuvres et de leurs peintres triés par date
-- de naissance des peintres dans l'ordre décroissant puis, pour une
-- même date de naissance du peintre, par ordre lexicographique
-- croissant du nom de l'oeuvre
SELECT o.nom, p.nom
FROM oeuvres o JOIN peintres p ON o.id_peintre = p.id
ORDER BY p.annee_naissance DESC, o.nom ASC;

-- 15. Quels sont les cinq derniers peintres morts ?
SELECT * FROM peintres 
ORDER BY annee_mort DESC 
LIMIT 5;

-- 16. Quels sont, suivant l'ordre lexicographique croissant de leur
-- nom, les troisième, quatrième et cinquième pays ?
SELECT * FROM pays 
ORDER BY nom 
LIMIT 3 OFFSET 2;

-- 17. Quelles sont les oeuvres peintes par un peintre né après 1500, exposées dans
-- un musée dont le nom est formé d'un seul mot ?
-- Avec jointure :
SELECT o.* FROM peintres p
  JOIN oeuvres o JOIN musees m 
  ON p.id = o.id_peintre AND o.id_musee = m.id
WHERE p.annee_naissance >= 1500 AND m.nom NOT LIKE '% %';

-- 18. Avec produit cartésien :
SELECT o.* FROM peintres p, oeuvres o, musees m
WHERE p.id = o.id_peintre AND  o.id_musee = m.id
  AND p.annee_naissance >= 1500 AND m.nom NOT LIKE '% %';
  
-- 19. Avec IN :
SELECT * FROM oeuvres o
WHERE id_peintre IN (SELECT id 
                     FROM peintres 
                     WHERE annee_naissance >= 1500)
  AND id_musee IN (SELECT id 
                   FROM musees 
                   WHERE nom NOT LIKE '% %');
  
-- 20. Avec EXISTS
SELECT * FROM oeuvres o
WHERE EXISTS (SELECT id 
              FROM peintres
              WHERE annee_naissance >= 1500 AND id = o.id_peintre)
    AND EXISTS (SELECT id 
                FROM musees
                WHERE nom NOT LIKE '% %' AND id = o.id_musee);
                
-- 21. Avec COUNT
SELECT * FROM oeuvres o
WHERE 0 != (SELECT COUNT(*) 
            FROM peintres
            WHERE annee_naissance >= 1500 AND id = o.id_peintre)
    AND 0 != (SELECT COUNT(*) 
              FROM musees
              WHERE nom NOT LIKE '% %' AND id = o.id_musee);

-- 22. Donner, pour chaque musée, son identifiant et le nombre d'oeuvres
-- qui y sont exposées.
SELECT id_musee, COUNT(id) AS nb_oeuvres
FROM oeuvres
WHERE id_musee IS NOT NULL
GROUP BY id_musee;

-- 23. Donner, pour chaque peintre, son identifiant et son nombre d'oeuvres.
SELECT p.id, COUNT(o.id) AS nb_oeuvres
FROM peintres p JOIN oeuvres o ON p.id = o.id_peintre
GROUP BY p.id;

-- 24. Donner, pour chaque peintre ayant au moins une oeuvre, son prénom, son nom
-- et son nombre d'oeuvres.
SELECT p.prenom, p.nom, COUNT(o.id) AS nb_oeuvres
FROM peintres p JOIN oeuvres o ON p.id = o.id_peintre
GROUP BY p.id;

-- 25. Donner, pour chaque pays, son nom, le nombre de peintres qui en sont
-- originaires et la durée moyenne de vie d'un peintre originaire de ce pays
SELECT c.nom, COUNT(p.id), AVG(p.annee_mort - p.annee_naissance)
FROM pays c JOIN peintres p ON c.id = p.id_pays
GROUP BY c.id;

-- 26. Donner l'identifiant des pays qui comportent au moins deux villes.
SELECT id_pays
FROM villes
GROUP BY id_pays
HAVING COUNT(id) >= 2;

-- 27. Donner le nom des peintres ayant peint au moins 3 toiles, avec ce nombre.
SELECT p.nom, COUNT(o.id) AS nb_oeuvres
FROM oeuvres o JOIN peintres p ON o.id_peintre = p.id
GROUP BY o.id_peintre
HAVING COUNT(o.id) >= 3;

-- Donner le nom des musées qui exposent au moins deux tableaux
-- dont le titre commence par 'L', avec ce nombre de tableaux.
SELECT m.nom, COUNT(o.id) AS nb_oeuvres_commence_par_L
FROM oeuvres o JOIN musees m ON o.id_musee = m.id
WHERE o.nom LIKE 'L%'
GROUP BY o.id_musee
HAVING COUNT(o.id) >= 2;

-- 28. Donner, pour chaque musée dont le nom commence par 'G' et qui
-- expose au moins 2 oeuvres de peintres nés après 1480, son nom et le
-- nombre de peintres nés après 1480 qui y sont exposés.
SELECT m.nom, COUNT(p.id)
FROM musees m
  JOIN oeuvres o JOIN peintres p 
  ON m.id = o.id_musee AND o.id_peintre = p.id
WHERE m.nom LIKE 'G%'  AND p.annee_naissance >= 1480
GROUP BY m.id
HAVING COUNT(o.id) >= 2;

-- 29. Quel est le nombre maximal d'oeuvres peintes par un même artiste ?
SELECT MAX(nb_oeuvres)
FROM 
    (SELECT COUNT(id) AS nb_oeuvres 
    FROM oeuvres 
    GROUP BY id_peintre);

-- 30. Quel est le nombre moyen d'oeuvres dans les musées qui comportent
-- au moins deux oeuvres ?
SELECT AVG(nb_oeuvres)
FROM 
    (SELECT COUNT(id) AS nb_oeuvres 
    FROM oeuvres 
    WHERE id_musee IS NOT NULL 
    GROUP BY id_musee)
WHERE nb_oeuvres >= 2;

-- 31. Quel est le nom d'un musée qui expose le moins d'oeuvres ?
SELECT m.nom, COUNT(o.id) AS nb_oeuvres
FROM oeuvres o JOIN musees m ON o.id_musee = m.id
GROUP BY id_musee
ORDER BY nb_oeuvres
LIMIT 1;

-- 32. Quels sont les noms des peintres exposés dans tous les musées parisiens ?
-- On reformule : ce sont les peintres pour lesquels il n'existe pas de musée
-- parisien qui ne les expose pas (c'est-à-dire tel qu'il n'existe pas d'oeuvre
-- de ce peintre dans ce musée)
-- Avec double NOT EXISTS
SELECT p.nom FROM peintres p
WHERE NOT EXISTS
  (SELECT * FROM musees m JOIN villes v ON m.id_ville = v.id
   WHERE v.nom = 'Paris' AND NOT EXISTS
     (SELECT * FROM oeuvres o WHERE o.id_peintre = p.id AND o.id_musee = m.id));
-- Avec NOT EXISTS et NOT IN
SELECT p.nom FROM peintres p
WHERE NOT EXISTS
  (SELECT m.id FROM musees m JOIN villes v ON m.id_ville = v.id
   WHERE v.nom = 'Paris' AND m.id NOT IN
     (SELECT id_musee FROM oeuvres WHERE id_peintre = p.id
	                                                  AND id_musee IS NOT NULL));
-- Avec COUNT
SELECT p.nom FROM peintres p
WHERE (SELECT COUNT(DISTINCT m.id)
            FROM musees m JOIN villes v ON m.id_ville = v.id
            WHERE v.nom = 'Paris' )
			=
			(SELECT COUNT(DISTINCT m.id)
			FROM oeuvres o JOIN musees m JOIN villes v 
			ON o.id_musee = m.id  AND m.id_ville = v.id
			WHERE v.nom = 'Paris' AND o.id_peintre = p.id);
-- Avec GROUP BY
SELECT p.nom FROM peintres p
  JOIN oeuvres o JOIN musees m JOIN villes v 
  ON p.id = o.id_peintre AND o.id_musee = m.id AND m.id_ville = v.id
WHERE v.nom = 'Paris'
GROUP BY p.nom
HAVING COUNT(DISTINCT m.id) =
             (SELECT COUNT(DISTINCT m.id)
            FROM musees m JOIN villes v ON m.id_ville = v.id
            WHERE v.nom = 'Paris');

-- 33. Quels sont les noms des pays dans lesquels tous les musées de ce pays
-- exposent tous les peintres de ce pays ? On reformule : ce sont les pays
-- pour lesquels il n'existe pas de musée de ce pays pour lequel il existe
-- un peintre de ce pays pour lequel il n'existe pas d'oeuvre qui n'est pas exposée
-- dans ce musée.
SELECT c.nom FROM pays c WHERE NOT EXISTS
  (SELECT * FROM musees m
     JOIN villes v JOIN pays c_bis 
     ON m.id_ville = v.id AND v.id_pays = c_bis.id
   WHERE c_bis.id = c.id AND EXISTS
     (SELECT * FROM peintres p WHERE p.id_pays = c.id AND NOT EXISTS
       (SELECT * FROM oeuvres o WHERE o.id_peintre = p.id AND o.id_musee = m.id)));
