L1: TPDragons

/* INSTRUCTIONS
psql -h dbserver
\H
\o output.html
\i TPscript.sql
*/

/* 1: Dragons qui crachent du feu */
SELECT "Dragon"
FROM "Dragons"."Table Dragon"
WHERE "CracheFeu" = true;

/* 2: Dragons males qui crachent du feu */
SELECT "Dragon"
FROM "Dragons"."Table Dragon"
WHERE "CracheFeu" = true
AND "Sexe" = 'M';

/* 3: Liste des dragons femelles dans l'ordre decroissant de leur longueur */
SELECT *
FROM "Dragons"."Table Dragon"
WHERE "Sexe" = 'F'
ORDER BY "Longueur" DESC;

/* 4: Rapport Moyen Longueur-Ecailles male puis femelle */
SELECT "Dragon","Ecailles"/"Longueur"
FROM "Dragons"."Table Dragon"
WHERE "Sexe" = 'M';

SELECT "Dragon","Ecailles"/"Longueur"
FROM "Dragons"."Table Dragon"
WHERE "Sexe" = 'F';

/* 5: Dragons amoureux */
SELECT "DragonAimant"
FROM "Dragons"."Table Amours";

/* 6: Dragons qui ne sont pas amoureux */
SELECT "Dragon"
FROM "Dragons"."Table Dragon"
WHERE "Dragon"
NOT IN  (
    SELECT "DragonAimant" FROM "Dragons"."Table Amours"
    );

/* 7: Qui aime qui passionnement */
SELECT *
FROM "Dragons"."Table Amours"
WHERE "Force" = 'passionnement';

/* 8: Qui aime qui au moins passionnement */
SELECT *
FROM "Dragons"."Table Amours"
WHERE "Force" = 'passionnement'
OR "Force" = 'a la folie';

/* 9: Liste des couples qui s'aiment mutuellement */
SELECT "DragonAimant","DragonAime"
FROM "Dragons"."Table Amours"
WHERE "DragonAimant"
IN  (
    SELECT "DragonAime" FROM "Dragons"."Table Amours"
    )
AND "DragonAime"
IN  (
    SELECT "DragonAimant" FROM "Dragons"."Table Amours"
    );

/* 10: Q9 mais en supprimant les faux doublons */
SELECT "DragonAime"
FROM "Dragons"."Table Amours"
WHERE "DragonAimant"
IN  (
    SELECT "DragonAime" FROM "Dragons"."Table Amours"
    )
AND "DragonAime"
IN  (
    SELECT "DragonAimant" FROM "Dragons"."Table Amours"
    );

/* 11: Noms des Dragonnes aimees par tous les dragons machos */
SELECT "DragonAime"
FROM "Dragons"."Table Amours", "Dragons"."Table Dragon"
WHERE "Dragon" = "DragonAimant"
AND "Sexe" = 'M'
AND "EnAmour" = 'macho'

INTERSECT SELECT "DragonAimant"
FROM "Dragons"."Table Amours", "Dragons"."Table Dragon"
WHERE "Dragon" = "DragonAime";

/* 12: Noms des Dragonnes aimees que par des dragons timides  */
SELECT "DragonAime"
FROM "Dragons"."Table Amours", "Dragons"."Table Dragon"
WHERE "Dragon" = "DragonAimant"
AND "Sexe" = 'M'
AND "EnAmour" = 'timide'

INTERSECT SELECT "DragonAimant"
FROM "Dragons"."Table Amours", "Dragons"."Table Dragon"
WHERE "Dragon" = "DragonAime";

/* 13: Produits hypo-calories (< 10 calories) */
SELECT *
FROM "Dragons"."Table nourriture"
WHERE "Calories" < 10;

/* 14: Dragons qui mange des oeufs */
SELECT "Dragon"
FROM "Dragons"."Table Repas"
WHERE "Produit" = 'oeuf';

/* 15: Dragonnes qui mangent des oeufs */
SELECT "Dragon"
FROM "Dragons"."Table Dragon"
WHERE "Sexe" = 'F'

INTERSECT SELECT "Dragon"
FROM "Dragons"."Table Repas"
WHERE "Produit" = 'oeuf';

/* 16: Dragons qui mangent pas */
SELECT "Dragon"
FROM "Dragons"."Table Dragon"

EXCEPT SELECT DISTINCT "Dragon"
FROM "Dragons"."Table Repas";

/* 17: Produits non hypo-calories consommer par dragons males crachant du feu */
SELECT DISTINCT "Dragons"."Table nourriture"."Produit"
FROM "Dragons"."Table nourriture","Dragons"."Table Repas","Dragons"."Table Dragon"
WHERE "Dragons"."Table nourriture"."Calories" >= 10
AND "Dragons"."Table nourriture"."Produit" = "Dragons"."Table Repas"."Produit"
AND "Dragons"."Table Repas"."Dragon" = "Dragons"."Table Dragon"."Dragon"
AND "Dragons"."Table Dragon"."Sexe" = 'M'
AND "Dragons"."Table Dragon"."CracheFeu" = true;

/* 18: Noms des Dragons qui mangent de tout */
SELECT "Dragon"
FROM "Dragons"."Table Repas", "Dragons"."Table nourriture"
WHERE "Dragons"."Table Repas"."Produit" = "Dragons"."Table nourriture"."Produit"
GROUP BY "Dragon"
HAVING COUNT("Dragons"."Table Repas"."Produit") = (
    SELECT COUNT(DISTINCT "Produit")
    FROM "Dragons"."Table nourriture"
    );

/* 19: Nombre de Calories par chaque dragon lors d'un repas, classer dans l'ordre croissant du total des calories */
SELECT "Dragon", SUM("Quantite" * "Calories")
FROM "Dragons"."Table Repas", "Dragons"."Table nourriture"
WHERE "Dragons"."Table Repas"."Produit" = "Dragons"."Table nourriture"."Produit"
GROUP BY "Dragon"
ORDER BY (SUM("Quantite" * "Calories")) DESC;

/* 20: --- */
SELECT "Dragons"."Table Dragon"."Dragon", SUM(1 * "Calories" * "Quantite" / "Longueur")
FROM "Dragons"."Table Repas", "Dragons"."Table Dragon", "Dragons"."Table nourriture"
WHERE "Dragons"."Table Dragon"."Dragon" = "Dragons"."Table Repas"."Dragon"
AND "Dragons"."Table nourriture"."Produit" = "Dragons"."Table Repas"."Produit"
AND "CracheFeu" = true
GROUP BY "Dragons"."Table Dragon"."Dragon"
ORDER BY (SUM(1 * "Calories" * "Quantite" / "Longueur")) DESC;

/* 21: Dragons qui mangent pas et qui aiment personne */
SELECT "Dragon"
FROM "Dragons"."Table Dragon"

EXCEPT SELECT DISTINCT "Dragon"
FROM "Dragons"."Table Repas"

EXCEPT SELECT DISTINCT "DragonAimant"
FROM "Dragons"."Table Amours";

/* 22: Produits consommes par les dragons aimant a la folie */
SELECT DISTINCT "Produit"
FROM "Dragons"."Table Repas", "Dragons"."Table Amours"
WHERE "Dragons"."Table Amours"."DragonAimant" = "Dragons"."Table Repas"."Dragon"
AND "Force" = 'a la folie';

/* 23: Dragonnes dont au moins un pretendant fais la greve de la faim */
SELECT DISTINCT "DragonAime"
FROM "Dragons"."Table Amours", "Dragons"."Table Dragon"
WHERE "Dragons"."Table Amours"."DragonAime" = "Dragons"."Table Dragon"."Dragon"
AND "Sexe" = 'F'
AND "DragonAimant" IN (
    SELECT "Dragon"
    FROM "Dragons"."Table Dragon"

    EXCEPT SELECT DISTINCT "Dragon"
    FROM "Dragons"."Table Repas"
    );

/* 24: Dragonnes dont tous les pretendants font la greve de la faim */
SELECT "DragonAime"
FROM "Dragons"."Table Amours", "Dragons"."Table Dragon"
WHERE "Dragons"."Table Amours"."DragonAime" = "Dragons"."Table Dragon"."Dragon"
GROUP BY "DragonAime"
HAVING COUNT("Dragons"."Table Amours"."DragonAime") = (
    SELECT COUNT(DISTINCT "Dragon")
    FROM "Dragons"."Table Repas"
    );

/* 25: Les couples dont le nombre d'ecailles est paire et dont le comportement amoureux est le meme */
SELECT A1."DragonAimant", A1."DragonAime"
FROM "Dragons"."Table Amours" A1
WHERE A1."DragonAimant" = 'Miloch';