DROP SCHEMA IF EXISTS HOLLYWOOD;
CREATE SCHEMA HOLLYWOOD; 
USE HOLLYWOOD;

CREATE TABLE FILM (
    idFilm INT NOT NULL,
    titre TEXT NOT NULL,
    realisateur TEXT NOT NULL,
    annee TEXT NOT NULL,
    CONSTRAINT pk1 PRIMARY KEY (idFilm)
);


CREATE TABLE ACTEUR (
    idActeur INT NOT NULL,
    nom TEXT NOT NULL,
    CONSTRAINT pk1 PRIMARY KEY (idActeur)
);


CREATE TABLE FILMOGRAPHIE (
    idActeur INT NOT NULL,
    idFilm INT NOT NULL,
    role TEXT NOT NULL,
    salaire TEXT NOT NULL,
    CONSTRAINT pk PRIMARY KEY (idFilm , idActeur),
    CONSTRAINT fk1 FOREIGN KEY (idFilm)
        REFERENCES FILM (idFilm),
    CONSTRAINT fk2 FOREIGN KEY (idActeur)
        REFERENCES ACTEUR (idActeur)
);

INSERT INTO FILM VALUES (1,'Les évadés', 'Darabont', 1994);
INSERT INTO FILM VALUES (2,'Le parrain', 'Coppola', 1972);
INSERT INTO FILM VALUES (3,'Le parrain 2', 'Coppola', 1974);
INSERT INTO FILM VALUES (4,'L''odyssée de Pi', 'Ang Lee', 2013);
INSERT INTO FILM VALUES (5,'Chocolat', 'Hallstrom', 2000);
INSERT INTO FILM VALUES (6,'Scarface', 'De Palma', 1983);
INSERT INTO FILM VALUES (7,'Rango', 'Verbinski', 2011);


INSERT INTO ACTEUR VALUES (1, 'Johnny Deep');
INSERT INTO ACTEUR VALUES (2, 'Al Pacino');
INSERT INTO ACTEUR VALUES (3, 'Suraj Sharma');
INSERT INTO ACTEUR VALUES (4, 'Brad Pitt');
INSERT INTO ACTEUR VALUES (5, 'Edward Norton');

INSERT INTO FILMOGRAPHIE VALUES(1,5, 'Roux', 5000);
INSERT INTO FILMOGRAPHIE VALUES(1,7, 'Rango', 10000);
INSERT INTO FILMOGRAPHIE VALUES(2,2, 'Michael Corleone', 10000);
INSERT INTO FILMOGRAPHIE VALUES(2,3, 'Michael Corleone', 20000);
INSERT INTO FILMOGRAPHIE VALUES(2,6, 'Tony Montana', 15000);
INSERT INTO FILMOGRAPHIE VALUES(3,4, 'Pi', 20000);


-- 1. La liste de tous les films ou a joue Johnny Deep:

SELECT 
    titre
FROM
    FILM,
    FILMOGRAPHIE
WHERE
    FILM.idFilm = FILMOGRAPHIE.idFilm
        AND idActeur = (SELECT 
            idActeur
        FROM
            ACTEUR
        WHERE
            nom = 'Johnny Deep');

-- 2. La liste des annees ou Johnny Deep a joue dans un film, ainsi que son role dans ce film.

SELECT 
    annee, realisateur, role as 'Role de Johhny'
FROM
    FILM,
    FILMOGRAPHIE
WHERE
    FILM.idFilm = FILMOGRAPHIE.idFilm
        AND idActeur = (SELECT 
            idActeur
        FROM
            ACTEUR
        WHERE
            nom = 'Johnny Deep');

-- 3. La liste de tous les films realises par le realisateur du film <<Le parrain>>. 

SELECT 
    titre
FROM
    FILM
where
    realisateur = (SELECT 
            realisateur
        FROM
            FILM
        WHERE
            titre = 'Le parrain');

-- 4. La liste des films qui commencent par “Le” ou contiennent “de”:

SELECT 
    titre
FROM
    FILM
WHERE
    (titre like 'Le%')
        OR (titre like '%de%');

-- 5. La liste des films ordonnee par annee de realisation, dans l’ordre descendant:

SELECT 
    titre, annee
FROM
    FILM
ORDER BY annee desc;

-- 6. Le nombre d’acteurs ayant joue dans le film 'L’odyssee de Pi':

SELECT 
    count(idActeur)
FROM
    FILMOGRAPHIE
WHERE
    idFilm = (SELECT 
            idFilm
        FROM
            FILM
        where
            titre = 'L\'odyssée de Pi');

-- 7. La liste des noms d’acteurs qui n’ont joue dans aucun film:

SELECT 
    nom
FROM
    ACTEUR
WHERE
    idActeur NOT IN (SELECT 
            idActeur
        FROM
            FILMOGRAPHIE);

-- 8. La liste des noms d’acteurs ayant joue dans au moins dans un film avec la moyenne 
-- des salaires qu’ils ont touche sur tous leurs films. Nommez la colonne Moyenne.

SELECT 
    nom, AVG(salaire)
FROM
    ACTEUR,
    FILMOGRAPHIE
WHERE
    ACTEUR.idActeur = FILMOGRAPHIE.idActeur
GROUP BY FILMOGRAPHIE.idActeur;

-- 9. La liste des paires d’acteurs ayant le meme salaire. 
-- Une paire d’acteurs ne doit pas se trouver deux fois dans votre resultat.

SELECT 
    A1.nom, A2.nom, F1.salaire, F2.salaire
FROM
    ACTEUR A1,
    ACTEUR A2,
    FILMOGRAPHIE F1,
    FILMOGRAPHIE F2
WHERE
    A1.idActeur = F1.idActeur
        AND A2.idActeur = F2.idActeur
        AND F1.salaire = F2.salaire
        and F1.idActeur < F2.idActeur;

-- 10. Les salaires dans la base de donnees sont en dollars. Afficher ces salaires en dirhams. 
-- 1 dollar coute aux environs de 9 dirhams.

SELECT 
    *, 9 * salaire as salaireDhs
FROM
    FILMOGRAPHIE;

