-- Cr ́eer une vue qui affiche, pour chaque article, 
-- le titre de l’article, et la difference entre le meilleur et le pire vote 
-- que l’article a recu. 
-- Trier la liste en utilisant cette difference, puis par titre.

SELECT idArticle, max(vote), min(vote), max(vote) - min(vote) as difference FROM DECISION
GROUP BY idArticle
ORDER BY difference;

DROP VIEW IF EXISTS difference_votes;
CREATE VIEW difference_votes AS
    SELECT 
        titre, max(vote) - min(vote) as difference
    FROM
        ARTICLE,
        DECISION
    WHERE
        DECISION.idArticle = ARTICLE.idArticle
    GROUP BY DECISION.idArticle
    ORDER BY difference , titre;

SELECT 
    *
FROM
    difference_votes;

-- Cr ́eer une vue qui affiche la diff ́erence entre la moyenne des votes des 
-- articles publi ́es entre 1980 et 1990 et ceux publi ́es apr`es 1990.
-- Il faut d’abord calculer la moyenne des votes pour chaque article, 
-- puis la moyenne de ces moyennes avant et apr`es 1990.

SELECT idArticle, avg(vote) FROM DECISION GROUP BY idArticle;

DROP VIEW IF EXISTS Moyenne;
CREATE VIEW Moyenne AS
    SELECT 
       AVG(vote) as moyenne, annee
    FROM
        DECISION,
        ARTICLE
    WHERE
        DECISION.idArticle = ARTICLE.idArticle
    GROUP BY DECISION.idArticle;



DROP VIEW IF EXISTS MoyenneAvant;
CREATE VIEW MoyenneAvant AS
SELECT 
    avg(moyenne) as MoyenneAvant
FROM
    Moyenne
WHERE
    annee > 1980 AND annee < 1990;

DROP VIEW IF EXISTS MoyenneApres;
CREATE VIEW MoyenneApres AS
SELECT 
    avg(moyenne) as MoyenneApres
FROM
    Moyenne
WHERE
    annee >= 1990;

SELECT MoyenneApres - MoyenneAvant FROM MoyenneAvant, MoyenneApres;



/*
    SELECT 
        MoyenneApres - MoyenneAvant as difference
    FROM
        (SELECT 
            AVG(moyenne) as MoyenneAvant
        FROM
            (SELECT 
            AVG(vote) as moyenne, annee
        FROM
            DECISION, ARTICLE
        WHERE
            DECISION.idArticle = ARTICLE.idArticle
        GROUP BY DECISION.idArticle) as T
        WHERE
            annee > 1980 AND annee < 1990) as T1,
        (SELECT 
            AVG(moyenne) as MoyenneApres
        FROM
            (SELECT 
            AVG(vote) as moyenne, annee
        FROM
            DECISION, ARTICLE
        WHERE
            DECISION.idArticle = ARTICLE.idArticle
        GROUP BY DECISION.idArticle) as T
        WHERE
            annee >= 1990) as T2;*/


-- 4. Pour les auteurs ayant publi ́e plus d’un article, 
-- cr ́eer une vue qui affiche les titres de leurs articles et leur nom. 
-- La liste doit ˆetre tri ́ee par nom, puis par titre.
-- Cr ́eer la vue en utilisant COUNT dans un premier temps, 
-- puis sans utiliser de COUNT.



SELECT 
    auteur, titre
FROM
    ARTICLE
WHERE
    auteur in (SELECT 
            auteur
        FROM
            ARTICLE
        GROUP BY auteur
        HAVING COUNT(idArticle) > 1)
ORDER BY auteur , titre;


-- Sans utiliser le COUNT

SELECT 
    auteur, titre
FROM
    ARTICLE
WHERE
    auteur IN (SELECT 
            A1.auteur
        FROM
            ARTICLE A1,
            ARTICLE A2
        WHERE
            A1.auteur = A2.auteur
                AND A1.titre <> A2.titre);


-- 6. Cr ́eer une vue qui affiche l’article avec la plus petite 
-- moyenne de votes. Afficher cette moyenne.
 
CREATE VIEW faible_moyenne AS
    SELECT 
        idArticle, avg(vote)
    FROM
        DECISION
    GROUP BY idArticle
    HAVING avg(vote) = (SELECT 
            min(moyenne)
        FROM
            (SELECT 
                idArticle, avg(vote) as moyenne
            FROM
                DECISION
            GROUP BY idArticle) as T);


-- 7 
-- 7. Cr ́eer une vue qui affiche, pour chaque auteur, 
-- le nom de l’auteur avec les titres de 
-- ses articles qui ont recu le meilleur vote, et la valeur de ce vote.


CREATE VIEW meilleurs_votes AS
    SELECT distinct
        auteur, titre, vote
    FROM
        ARTICLE A,
        DECISION
    WHERE
        A.idArticle = DECISION.idArticle
    AND vote = (SELECT 
            max(vote)
        FROM
            DECISION
        WHERE
            idArticle = A.idArticle);


