DROP SCHEMA IF EXISTS CONFERENCE;
CREATE SCHEMA CONFERENCE;
USE CONFERENCE;

CREATE TABLE ARTICLE (
    idArticle int primary key,
    nomArticle text
);

CREATE TABLE UNIVERSITE (
    idUniversite int primary key,
    nomUniversite text
);


CREATE TABLE CHERCHEUR (
    idChercheur int primary key,
    nomChercheur text,
    idArticle int,
    idUniversite int,
    FOREIGN KEY (idArticle)
        REFERENCES ARTICLE (idArticle),
    FOREIGN KEY (idUniversite)
        REFERENCES UNIVERSITE (idUniversite)
);

INSERT INTO ARTICLE VALUES (1, 'Article1');
INSERT INTO ARTICLE VALUES (2, 'Article2');
INSERT INTO ARTICLE VALUES (3, 'Article3');
INSERT INTO ARTICLE VALUES (4, 'Article4');
INSERT INTO ARTICLE VALUES (5, 'Article5');

INSERT INTO UNIVERSITE VALUES (6, 'Paris6');
INSERT INTO UNIVERSITE VALUES (9, 'Paris9');
INSERT INTO UNIVERSITE VALUES (13, 'Paris13');

INSERT INTO CHERCHEUR VALUES (1, 'Pierre', 1, 13);
INSERT INTO CHERCHEUR VALUES (2, 'Boutaine', 2, 9);
INSERT INTO CHERCHEUR VALUES (3, 'Laura', 3, 13);
INSERT INTO CHERCHEUR VALUES (4, 'Kais', 4, 6);

-- Cr ́eer une vue Paris13 qui affiche les chercheurs de l’universit ́e 13. 

CREATE VIEW Paris13 AS
    SELECT 
        *
    FROM
        CHERCHEUR
    WHERE
        idUniversite = 13;

-- 4. Retourner le contenu de la vue.

SELECT 
    *
FROM
    Paris13;

-- 5. Modifier le nom du chercheur “Pierre” en “Ali” dans la table CHERCHEUR. 
UPDATE CHERCHEUR 
SET 
    nomChercheur = 'Ali'
WHERE
    nomChercheur = 'Pierre';

-- V ́erifier que la vue Paris13 a chang ́e.

SELECT 
    *
FROM
    Paris13;

-- 6. Ins ́erer un nouveau chercheur de l’universit ́e Paris 13 dans la table CHERCHEUR 
INSERT INTO CHERCHEUR VALUES (5, 'Sami', 5, 13);

-- et v ́erifier le contenu de Paris13 a bien chang ́e.
SELECT 
    *
FROM
    Paris13;

-- 7. Donner les noms de tous les chercheurs de Paris 13 `a partir de la vue Paris13. 
SELECT 
    nomChercheur
FROM
    Paris13;

-- 8. Modifier le nom du chercheur “Ali” en “Pierre” dans la vue. 
UPDATE Paris13 
SET 
    nomChercheur = 'Pierre'
WHERE
    nomChercheur = 'Ali';

-- 9. Lister le contenu de la table CHERCHEUR, que remarque-t-on ?
-- > La table CHERCHEUR a aussi été mise à jour

SELECT * FROM CHERCHEUR;

CREATE VIEW Universites AS
    SELECT 
        nomChercheur, nomUniversite, idArticle
    FROM
        CHERCHEUR,
        UNIVERSITE
    WHERE 
        CHERCHEUR.idUniversite = UNIVERSITE.idUniversite;

-- 11. Afficher le contenu de la vue.
SELECT 
    *
FROM
    Universites;

-- 12. Afficher les noms des chercheurs 
-- ayant contribu ́e `a l’article 01 `a partir de la vue.
SELECT 
    nomChercheur
FROM
    Universites
WHERE
    idArticle = 1;

-- 13. Afficher les noms des chercheurs travaillant `a Paris6.
SELECT 
    nomChercheur
FROM
    Universites
WHERE
    nomUniversite = 'Paris6';

-- 14. Afficher les noms des articles des chercheurs de Paris 6 
-- `a partir de la vue Universites et de la table ARTICLE.

SELECT 
    nomArticle
FROM
    ARTICLE,
    Universites
WHERE
    ARTICLE.idArticle = Universites.idArticle
        AND nomUniversite = 'Paris6';

-- 15. Cr ́eer une vue `a partir de Universites qui affiche les noms des chercheurs, 
-- les noms de leurs univer- sites, ainsi que les noms de leurs articles.
CREATE VIEW DetailsUniversites AS
    SELECT 
        nomArticle, nomChercheur, nomUniversite
    FROM
        ARTICLE,
        Universites
    WHERE
        ARTICLE.idArticle = Universites.idArticle;

-- 16. Afficher le contenu de la vue. 
SELECT 
    *
FROM
    DetailsUniversites;

-- 17. Cr ́eer une vue ArticlesChercheur qui affiche le nombre d’articles par chercheur 
CREATE VIEW ArticlesChercheur (nomChercheur , nbrArticles) AS
    SELECT 
        nomChercheur, COUNT(idArticle)
    FROM
        CHERCHEUR
    GROUP BY idChercheur;

SELECT * FROM ArticlesChercheur;

-- et une autre vue ArticlesUniversite, le nombre d’articles par universit ́e. 
CREATE VIEW ArticlesUniversite (idUniversite , nbrArticles) AS
    SELECT 
        CHERCHEUR.idUniversite, nomUniversite, COUNT(idArticle)
    FROM
        CHERCHEUR,
		UNIVERSITE
	WHERE CHERCHEUR.idUniversite = UNIVERSITE.idUniversite
    GROUP BY CHERCHEUR.idUniversite;

SELECT * FROM ArticlesUniversite;


-- 18. Afficher le nombre d’articles de l’universit ́e Paris 13 `a partir de la vue.
SELECT 
    nbrArticles
FROM
    articlesUniversite
WHERE
    idUniversite = 13;













