--RI02 - premier exercice
--création de base
CREATE DATABASE CINEMA;

--création de la table film
--DROP TABLE FILM; 
CREATE TABLE FILM (IDENT_FILM        INTEGER PRIMARY KEY, 
                   TITRE             VARCHAR(50), 
                   GENRE1            VARCHAR(20), 
                   RECETTE           DECIMAL(15,2), 
                   DATE_SORTIE       DATE, 
                   PAYS              SMALLINT, 
                   NB_ENTREE         INTEGER, 
                   RESUME            VARCHAR(2000), 
                   DATE_SAISIE       TIMESTAMP 
                   );

--création d'index				   
CREATE INDEX I2_GENREPAYS ON FILM (GENRE1, PAYS);

--Création table CASTING
--DROP TABLE CASTING;
CREATE TABLE CASTING
     (IDENT_FILM       INTEGER,
      IDENT_ACTEUR     INTEGER,
      ROLE             VARCHAR(100),
      NB_JOUR_TOURNAGE INTEGER);

--ajout commentaire
COMMENT ON TABLE CASTING IS 'liste des acteurs par film avec précision du rôle et du nombre de jours de tournage';

--deuxième exercice
--ajout de colonne
ALTER TABLE FILM ADD NUM_REAL INTEGER;

--création table REALISATEUR
--DROP TABLE REALISATEUR;
CREATE TABLE REALISATEUR 
                 (NUM_REAL    INTEGER PRIMARY KEY, 
                  NOM               VARCHAR(50)); 

-- Ajout de la contrainte d'intégrité
ALTER TABLE FILM ADD CONSTRAINT FK_REALISATEUR FOREIGN KEY 
(NUM_REAL) REFERENCES REALISATEUR;

--ajout valeur par défaut
ALTER TABLE FILM ALTER COLUMN RECETTE SET DEFAULT 0;			  

--modification colonnes
ALTER TABLE FILM ALTER COLUMN TITRE SET NOT NULL;
ALTER TABLE FILM ALTER COLUMN PAYS SET NOT NULL;

--suppression contrainte
ALTER TABLE FILM DROP CONSTRAINT FK_REALISATEUR;

--Troisième exercice
--création vue
CREATE VIEW FILM2 AS 
  SELECT IDENT_FILM, TITRE, GENRE1, RECETTE, RESUME 
  FROM FILM;	

--suppression vue
DROP VIEW FILM2;

--quatrième exercice
--création séquence
CREATE SEQUENCE S_FILM START WITH 12 INCREMENT BY 1 
MINVALUE 12 MAXVALUE 9999 CYCLE;

--renommer table
ALTER TABLE FILM RENAME TO FILMOLD;

--création table
CREATE TABLE FILM AS SELECT * FROM FILMOLD;

--suppression table
DROP TABLE FILM;
DROP TABLE IF EXISTS FILMOLD;

--RI03 - Exercices d'application
USE CINEMA
DROP TABLE IF EXISTS FILM;
CREATE TABLE FILM (IDENT_FILM        INTEGER,
                   TITRE             VARCHAR(50),
                   GENRE1            VARCHAR(20),
                   GENRE2            VARCHAR(20),
                   DATE_SORTIE       DATE,
                   PAYS              SMALLINT,
                   IDENT_REALISATEUR INTEGER,
                   DISTRIBUTEUR       VARCHAR(50),
                   RESUME            VARCHAR(2000));

INSERT INTO FILM VALUES (1,'SUBWAY','POLICIER','DRAME','10/04/1985',1,1,'GAUMONT','Conte les aventures de la population souterraine dans les couloirs du métro parisien');

INSERT INTO FILM VALUES (2,'NIKITA','DRAME','ROMANTIQUE','21/02/1990',1,1,'GAUMONT','Nikita condamnée à la prison à perpétuité est contrainte  à travailler secrètement pour le gouvernement en tant que agent hautement qualifié des services secrets.');

INSERT INTO FILM VALUES (3,'STAR WARS 6 : LE RETOUR DU JEDI','ACTION','SF','19/10/1983',2,2,'20th Century Fox ','L''Empire galactique est plus puissant que jamais : la construction de la nouvelle arme, l''Etoile de la Mort, menace l''univers tout entier.');

INSERT INTO FILM VALUES (4,'AVATAR','ACTION','SF','16/10/2009',2,3,'20th Century Fox ','Malgré sa paralysie, Jake Sully, un ancien marine immobilisé dans un fauteuil roulant, est resté un combattant au plus profond de son être');

INSERT INTO FILM VALUES (5,'BIENVENUE CHEZ LES CH''TIS','COMEDIE','','27/02/2008',1,4,'PATHE','Philippe Abrams directeur de la poste de Salon-de-Provence est muté dans le Nord.');

DROP TABLE IF EXISTS ACTEUR;			   
CREATE TABLE ACTEUR (IDENT_ACTEUR   INTEGER,
                     NOM            VARCHAR(50),
                     PRENOM         VARCHAR(20),
                     DATE_NAISSANCE DATE,
                     NB_FILM        INTEGER,
                     NATIONALITE    SMALLINT);

INSERT INTO ACTEUR VALUES (1,'ADJANI','ISABELLE','27/06/1955',67,1);
INSERT INTO ACTEUR VALUES (2,'LAMBERT','CHRISTOPHE','29/03/1957',94,1);
INSERT INTO ACTEUR VALUES (3,'BORHINGER','RICHARD','16/06/1942',84,1);
INSERT INTO ACTEUR VALUES (4,'GALABRU','MICHEL','27/10/1922',150,1);
INSERT INTO ACTEUR VALUES (5,'PARILLAUD','ANNE','06/05/1960',35,1);
INSERT INTO ACTEUR VALUES (6,'FORD','HARRISON','13/06/1942',64,2);
INSERT INTO ACTEUR VALUES (7,'FISHER','CARRIE','21/10/1956',74,2);
INSERT INTO ACTEUR VALUES (8,'SALDANA','ZOE','19/06/1978',42,2);
INSERT INTO ACTEUR VALUES (9,'WEAVER','SIGOURNEY','08/10/1949',133,2);
INSERT INTO ACTEUR VALUES (10,'RENO','JEAN','30/06/1948',95,1);
INSERT INTO ACTEUR VALUES (11,'BOON','DANY','26/06/1966',53,1);
INSERT INTO ACTEUR VALUES (12,'MERAD','KAD','27/03/1964',112,3);

DROP TABLE IF EXISTS STATISTIQUE;
CREATE TABLE STATISTIQUE
     (IDENT_FILM       INTEGER,
      DUREE            INTEGER,
      NB_ENTREE_FRANCE MONEY,
      RECETTE_USA      MONEY,
      RECETTE_MONDE    MONEY,
      BUDGET           MONEY);

INSERT INTO STATISTIQUE VALUES (1,104,2917562,390659,1272637.45,2.6);
INSERT INTO STATISTIQUE VALUES (2,118,3787845,5017971,0,7.6);
INSERT INTO STATISTIQUE VALUES (3,133,4263000,191648000,472000000,32);
INSERT INTO STATISTIQUE VALUES (4,170,12018251,760505847,2946271769,237);
INSERT INTO STATISTIQUE VALUES (5,100,21000000,0,245000000,11);
	  
DROP TABLE IF EXISTS REALISATEUR;
CREATE TABLE REALISATEUR
                    (IDENT_REALISATEUR INTEGER,
                     NOM               VARCHAR(50),
                     PRENOM            VARCHAR(20),
                     DATE_NAISSANCE    DATE,
                     NB_FILM_ECRIT     INTEGER,
                     NB_FILM_PRODUIT   INTEGER,
                     NATIONALITE       SMALLINT);

INSERT INTO REALISATEUR VALUES ('1','BESSON','LUC','18/03/1959',57,105,1);
INSERT INTO REALISATEUR VALUES ('2','LUCAS','GEORGES','14/05/1944',79,64,2);
INSERT INTO REALISATEUR VALUES ('3','CAMERON','JAMES','16/08/1954',22,23,2);
INSERT INTO REALISATEUR VALUES ('4','BOON','DANY','26/06/1966',9,10,1);

DROP TABLE IF EXISTS CASTING;
CREATE TABLE CASTING
     (IDENT_FILM       INTEGER,
      IDENT_ACTEUR     INTEGER,
      ROLE             VARCHAR(100),
      NB_JOUR_TOURNAGE INTEGER);

INSERT INTO CASTING VALUES (1,1,'HELENA',100);
INSERT INTO CASTING VALUES (1,2,'FRED',100);
INSERT INTO CASTING VALUES (1,3,'INSPECTEUR GESBERG',NULL);
INSERT INTO CASTING VALUES (1,4,'LE FLEURISTE',35);
INSERT INTO CASTING VALUES (1,10,'LE BATTEUR',20);
INSERT INTO CASTING VALUES (2,5,'NIKITA',68);
INSERT INTO CASTING VALUES (2,10,'VICTOR LE NETTOYEUR',9);
INSERT INTO CASTING VALUES (3,6,'HAN SOLO',201);
INSERT INTO CASTING VALUES (3,7,'PRINCESSE LEIA',203);
INSERT INTO CASTING VALUES (4,8,'NEYTIRI',50);
INSERT INTO CASTING VALUES (4,9,'Dr. Grace Augustine',45);
INSERT INTO CASTING VALUES (5,11,'ANTOINE BAILLEUL',125);
INSERT INTO CASTING VALUES (5,12,'PHILIPPE ABRAMS',126);

DROP TABLE IF EXISTS PAYS;
CREATE TABLE PAYS
     (IDENT_PAYS SMALLINT,
      LIBELLE    VARCHAR(100));

INSERT INTO PAYS VALUES (1,'FRANCE');
INSERT INTO PAYS VALUES (2,'ETATS-UNIS');
INSERT INTO PAYS VALUES (3,'ALGERIE'); 

--Vérification
select * from ACTEUR;
select * from CASTING;
select * from FILM;
select * from PAYS;
select * from REALISATEUR;
select * from STATISTIQUE;

select COUNT(1) from ACTEUR; --Résultat =12
select COUNT(1) from CASTING; --Résultat =13
select COUNT(1) from FILM; --Résultat =5
select COUNT(1) from PAYS; --Résultat =3
select COUNT(1) from REALISATEUR; --Résultat =4
select COUNT(1) from STATISTIQUE; --Résultat =5

-- premier exercice
-- solution 1
SELECT * FROM FILM T1, REALISATEUR T2 WHERE 
       T1.IDENT_REALISATEUR = T2.IDENT_REALISATEUR AND 
       T2.NATIONALITE = 1 
ORDER BY T1.TITRE;

-- solution 2
SELECT * FROM FILM T1, REALISATEUR T2, PAYS T3 WHERE 
       T1.IDENT_REALISATEUR = T2.IDENT_REALISATEUR AND 
       T2.NATIONALITE = T3.IDENT_PAYS AND 
       T3.LIBELLE = 'FRANCE' 
ORDER BY T1.TITRE;

-- solution 3
SELECT * FROM FILM T1
       INNER JOIN REALISATEUR T2 ON T1.IDENT_REALISATEUR = 
                                    T2.IDENT_REALISATEUR 
       INNER JOIN PAYS T3        ON T2.NATIONALITE = T3.IDENT_PAYS 
WHERE T3.LIBELLE = 'FRANCE' 
ORDER BY T1.TITRE;

-- solution 4
SELECT T1.TITRE, T1.GENRE1, T1.DATE_SORTIE, T2.NOM, T2.PRENOM 
FROM FILM T1, REALISATEUR T2, PAYS T3 WHERE  
       T1.IDENT_REALISATEUR = T2.IDENT_REALISATEUR AND 
       T2.NATIONALITE = T3.IDENT_PAYS AND 
       T3.LIBELLE = 'FRANCE'

-- deuxième exercice
-- solution 1
SELECT T1.TITRE, T1.DATE_SORTIE, T2.NOM, T4.NOM, 
T4.DATE_NAISSANCE, T5.BUDGET  
FROM FILM T1, REALISATEUR T2, CASTING T3, ACTEUR T4, STATISTIQUE 
T5 WHERE  
   T1.IDENT_REALISATEUR = T2.IDENT_REALISATEUR AND 
   T1.IDENT_FILM        = T3.IDENT_FILM AND 
   T1.IDENT_FILM        = T5.IDENT_FILM AND 
   T3.IDENT_ACTEUR      = T4.IDENT_ACTEUR 
ORDER BY T1.TITRE, T4.NOM DESC;

-- solution 2
SELECT FILM.TITRE, FILM.DATE_SORTIE, REALI.NOM, ACTEUR.NOM, 
ACTEUR.DATE_NAISSANCE, STAT.BUDGET 
FROM FILM, REALISATEUR REALI, CASTING CASTI, ACTEUR, 
STATISTIQUE STAT WHERE  
   FILM.IDENT_REALISATEUR = REALI.IDENT_REALISATEUR AND 
   FILM.IDENT_FILM        = CASTI.IDENT_FILM AND 
   FILM.IDENT_FILM        = STAT.IDENT_FILM AND 
   CASTI.IDENT_ACTEUR      = ACTEUR.IDENT_ACTEUR 
ORDER BY FILM.TITRE, ACTEUR.NOM DESC;

-- solution 3
SELECT FILM.TITRE, FILM.DATE_SORTIE, REALI.NOM REALISATEUR, 
ACTEUR.NOM ACTEUR, ACTEUR.DATE_NAISSANCE, STAT.BUDGET 
FROM FILM, REALISATEUR REALI, CASTING CASTI, ACTEUR, 
STATISTIQUE STAT WHERE  
   FILM.IDENT_REALISATEUR = REALI.IDENT_REALISATEUR AND 
   FILM.IDENT_FILM        = CASTI.IDENT_FILM AND 
   FILM.IDENT_FILM        = STAT.IDENT_FILM AND 
   CASTI.IDENT_ACTEUR      = ACTEUR.IDENT_ACTEUR 
ORDER BY FILM.TITRE, ACTEUR.NOM DESC;

-- troisième exercice
SELECT T1.TITRE, T1.DATE_SORTIE, T2.NOM, 
T1.DISTRIBUTEUR,COUNT(T3.IDENT_FILM) AS NB_ACTEUR 
FROM FILM T1 INNER JOIN REALISATEUR T2 ON T1.IDENT_REALISATEUR = T2.IDENT_REALISATEUR
INNER JOIN CASTING T3 ON T1.IDENT_FILM = T3.IDENT_FILM
INNER JOIN ACTEUR T4 ON T3.IDENT_ACTEUR = T4.IDENT_ACTEUR
INNER JOIN STATISTIQUE T5 ON T1.IDENT_FILM = T5.IDENT_FILM
GROUP BY T1.TITRE, T1.DATE_SORTIE, T2.NOM, T1.DISTRIBUTEUR 
ORDER BY T1.TITRE;

-- quatrième exercice
SELECT FILM.TITRE, FILM.DATE_SORTIE, 
       REALI.NOM ||' '||REALI.PRENOM REALISATEUR, ACTEUR.NOM NOM, 
       ACTEUR.PRENOM PRENOM, ACTEUR.DATE_NAISSANCE, 
       ACTEUR.NB_FILM,STAT.BUDGET, STAT.NB_ENTREE_FRANCE ENTREES 
FROM FILM INNER JOIN REALISATEUR REALI ON FILM.IDENT_REALISATEUR = REALI.IDENT_REALISATEUR
INNER JOIN CASTING CASTI ON FILM.IDENT_FILM = CASTI.IDENT_FILM
INNER JOIN ACTEUR ON CASTI.IDENT_ACTEUR = ACTEUR.IDENT_ACTEUR
INNER JOIN STATISTIQUE STAT ON FILM.IDENT_FILM = STAT.IDENT_FILM
INNER JOIN PAYS ON PAYS.IDENT_PAYS = ACTEUR.NATIONALITE
WHERE PAYS.LIBELLE = 'ALGERIE' 
ORDER BY FILM.TITRE;

-- cinquième exercice
SELECT FILM.TITRE, FILM.DATE_SORTIE, 
       REALI.NOM ||' '||REALI.PRENOM REALISATEUR, STAT.BUDGET, 
STAT.NB_ENTREE_FRANCE ENTREES, STAT.RECETTE_USA CA_USA, 
STAT.RECETTE_MONDE CA_MONDE  
FROM FILM INNER JOIN REALISATEUR REALI ON FILM.IDENT_REALISATEUR = REALI.IDENT_REALISATEUR
INNER JOIN STATISTIQUE STAT ON FILM.IDENT_FILM = STAT.IDENT_FILM
WHERE STAT.RECETTE_MONDE = (SELECT MAX(RECETTE_MONDE) FROM STATISTIQUE)  
ORDER BY FILM.TITRE;

-- sixième exercice
-- solution 1
SELECT ACTEUR.NOM||' '||ACTEUR.PRENOM ACTEUR, 
ACTEUR.DATE_NAISSANCE "NE LE", FILM.TITRE TITRE, FILM.DATE_SORTIE 
"SORTIE LE", REALI.NOM||' '||REALI.PRENOM REALISATEUR 
FROM FILM INNER JOIN REALISATEUR REALI ON FILM.IDENT_REALISATEUR = REALI.IDENT_REALISATEUR
INNER JOIN CASTING CASTI ON  FILM.IDENT_FILM = CASTI.IDENT_FILM
INNER JOIN ACTEUR ON CASTI.IDENT_ACTEUR = ACTEUR.IDENT_ACTEUR
WHERE  ACTEUR.IDENT_ACTEUR IN 
       (SELECT IDENT_ACTEUR FROM CASTING GROUP BY IDENT_ACTEUR 
                     HAVING COUNT(*) = 2) 
ORDER BY FILM.TITRE, ACTEUR.NOM DESC;

-- solution 2
SELECT ACTEUR.NOM||' '||ACTEUR.PRENOM ACTEUR, 
ACTEUR.DATE_NAISSANCE "NE LE", COUNT(*) NB_FILMS  
FROM CASTING CASTI INNER JOIN ACTEUR ON CASTI.IDENT_ACTEUR = ACTEUR.IDENT_ACTEUR
GROUP BY ACTEUR.NOM||' '||ACTEUR.PRENOM, ACTEUR.DATE_NAISSANCE
HAVING COUNT(*) = 2 
ORDER BY ACTEUR.NOM||' '||ACTEUR.PRENOM;

-- septième exercice
-- solution 1
SELECT ACTEUR.NOM||' '||ACTEUR.PRENOM ACTEUR, 
ACTEUR.DATE_NAISSANCE "NE LE" 
FROM FILM INNER JOIN REALISATEUR REALI ON FILM.IDENT_REALISATEUR = REALI.IDENT_REALISATEUR
INNER JOIN CASTING CASTI ON FILM.IDENT_FILM = CASTI.IDENT_FILM
INNER JOIN ACTEUR ON CASTI.IDENT_ACTEUR = ACTEUR.IDENT_ACTEUR
WHERE ACTEUR.NOM = REALI.NOM AND 
   ACTEUR.PRENOM = REALI.PRENOM AND 
   ACTEUR.DATE_NAISSANCE = REALI.DATE_NAISSANCE;

-- solution 2
SELECT ACTEUR.NOM||' '||ACTEUR.PRENOM ACTEUR, 
ACTEUR.DATE_NAISSANCE "NE LE" 
FROM REALISATEUR REALI, ACTEUR
WHERE ACTEUR.NOM = REALI.NOM AND 
   ACTEUR.PRENOM = REALI.PRENOM AND 
   ACTEUR.DATE_NAISSANCE = REALI.DATE_NAISSANCE
ORDER BY ACTEUR.NOM||' '||ACTEUR.PRENOM;

-- huitième exercice
-- solution 1
SELECT ACTEUR.NOM||' '||ACTEUR.PRENOM ACTEUR,  
ACTEUR.DATE_NAISSANCE "NE LE",
CASTI.ROLE, PAYS.LIBELLE, FILM.TITRE, FILM.DATE_SORTIE 
FROM FILM INNER JOIN CASTING CASTI ON FILM.IDENT_FILM = CASTI.IDENT_FILM
INNER JOIN ACTEUR ON CASTI.IDENT_ACTEUR = ACTEUR.IDENT_ACTEUR
INNER JOIN PAYS ON PAYS.IDENT_PAYS = ACTEUR.NATIONALITE
WHERE SUBSTR(FILM.TITRE,1,1) = 'S' 
ORDER BY ACTEUR.NOM||' '||ACTEUR.PRENOM, FILM.TITRE DESC;

-- solution 2
SELECT ACTEUR.NOM||' '||ACTEUR.PRENOM ACTEUR,  
ACTEUR.DATE_NAISSANCE "NE LE",
CASTI.ROLE, PAYS.LIBELLE, FILM.TITRE, FILM.DATE_SORTIE 
FROM FILM INNER JOIN CASTING CASTI ON FILM.IDENT_FILM = CASTI.IDENT_FILM
INNER JOIN ACTEUR ON CASTI.IDENT_ACTEUR = ACTEUR.IDENT_ACTEUR
INNER JOIN PAYS ON PAYS.IDENT_PAYS = ACTEUR.NATIONALITE
WHERE FILM.TITRE LIKE 'S%' 
ORDER BY ACTEUR.NOM||' '||ACTEUR.PRENOM, FILM.TITRE DESC;

-- neuvième exercice
-- solution 1
SELECT ACTEUR.NOM||' '||ACTEUR.PRENOM ACTEUR,  
ACTEUR.DATE_NAISSANCE "NE LE",
SUM(CASTI.NB_JOUR_TOURNAGE) NB_JOURS 
FROM CASTING CASTI INNER JOIN ACTEUR ON CASTI.IDENT_ACTEUR = ACTEUR.IDENT_ACTEUR
WHERE ACTEUR.DATE_NAISSANCE >= TO_DATE('01/01/1948','DD/MM/YYYY') AND 
 ACTEUR.DATE_NAISSANCE <= TO_DATE('31/05/1978','DD/MM/YYYY')
GROUP BY ACTEUR.NOM||' '||ACTEUR.PRENOM,ACTEUR.DATE_NAISSANCE 
ORDER BY ACTEUR.NOM||' '||ACTEUR.PRENOM ASC;

-- solution 2
SELECT ACTEUR.NOM||' '||ACTEUR.PRENOM ACTEUR,  
ACTEUR.DATE_NAISSANCE "NE LE",
SUM(CASTI.NB_JOUR_TOURNAGE) NB_JOURS 
FROM CASTING CASTI INNER JOIN ACTEUR ON CASTI.IDENT_ACTEUR = ACTEUR.IDENT_ACTEUR
WHERE ACTEUR.DATE_NAISSANCE BETWEEN TO_DATE('01/01/1948','DD/MM/YYYY') AND
TO_DATE('31/05/1978','DD/MM/YYYY') 
GROUP BY ACTEUR.NOM||' '||ACTEUR.PRENOM,ACTEUR.DATE_NAISSANCE 
ORDER BY ACTEUR.NOM||' '||ACTEUR.PRENOM ASC;

-- dixième exercice
-- solution 1
SELECT ACTEUR.IDENT_ACTEUR, NOM, PRENOM, ROLE
FROM ACTEUR INNER JOIN CASTING ON CASTING.IDENT_ACTEUR = ACTEUR.IDENT_ACTEUR;
SELECT ACTEUR.IDENT_ACTEUR, NOM, PRENOM, ROLE
FROM ACTEUR LEFT OUTER JOIN CASTING ON CASTING.IDENT_ACTEUR = ACTEUR.IDENT_ACTEUR
WHERE ROLE IS NULL;

-- solution 2
SELECT ACTEUR.IDENT_ACTEUR, NOM, PRENOM, ROLE
FROM CASTING RIGHT OUTER JOIN ACTEUR ON CASTING.IDENT_ACTEUR = ACTEUR.IDENT_ACTEUR
WHERE ROLE IS NULL;

-- onzième exercice
SELECT NOM, PRENOM FROM ACTEUR
UNION
SELECT NOM, PRENOM FROM REALISATEUR;

SELECT NOM, PRENOM FROM ACTEUR
EXCEPT
SELECT NOM, PRENOM FROM REALISATEUR;

SELECT NOM, PRENOM FROM REALISATEUR
EXCEPT
SELECT NOM, PRENOM FROM ACTEUR;

SELECT NOM, PRENOM FROM REALISATEUR
INTERSECT
SELECT NOM, PRENOM FROM ACTEUR;

--exercices sur l'insertion de données
-- premier exercice
SELECT MAX(IDENT_FILM) FROM FILM; 
INSERT INTO FILM VALUES (6,'DIE HARD 4','ACTION','', 
TO_DATE('04/07/2007','DD/MM/YYYY'),2,5,'Twentieth Century Fox Film Corporation'
,'Pour sa quatrième aventure, l''inspecteur John McClane se trouve confronté à un nouveau genre de terrorisme. Le réseau informatique national qui contrôle absolument toutes les communications, les transports et l''énergie des Etats-Unis, est détruit de façon systématique, plongeant le pays dans le chaos.'); 

-- deuxième exercice
CREATE TABLE ACTEUR_FRANCE AS SELECT * FROM ACTEUR WHERE 
NATIONALITE = (SELECT IDENT_PAYS FROM PAYS WHERE LIBELLE = 
'FRANCE');

-- troisième exercice
SELECT MAX(IDENT_ACTEUR) FROM ACTEUR;
INSERT INTO ACTEUR VALUES ((SELECT MAX(IDENT_ACTEUR)+1 FROM 
ACTEUR),'WILLIS','BRUCE',TO_DATE('19/03/1955','DD/MM/YYYY'),152,2);

--exercices sur la suppression de données
-- premier exercice
SELECT * FROM ACTEUR WHERE NOM = 'FISHER' AND PRENOM = 'CARRIE'; 
DELETE FROM ACTEUR WHERE NOM = 'FISHER' AND PRENOM = 'CARRIE'; 

-- deuxième exercice
SELECT * FROM STATISTIQUE WHERE RECETTE_MONDE = '0';
DELETE FROM STATISTIQUE WHERE RECETTE_MONDE = '0';

--exercices sur la modification de données
-- premier exercice
SELECT IDENT_FILM,TITRE,GENRE1,GENRE2 FROM FILM WHERE GENRE2 IS NULL OR GENRE2 = '';
UPDATE FILM SET GENRE2 = 'AVENTURE' WHERE GENRE2 IS NULL OR GENRE2 = '';
UPDATE FILM SET GENRE2 = 'SCIENCE FICTION' WHERE GENRE2 = 'SF';
SELECT IDENT_FILM,TITRE,GENRE1,GENRE2 FROM FILM WHERE GENRE2 IN ('AVENTURE', 'SCIENCE FICTION');

-- deuxième exercice
UPDATE STATISTIQUE SET RECETTE_MONDE = RECETTE_MONDE / 1000000;
SELECT * FROM STATISTIQUE;

--RI04 - premier exercice
SELECT TO_CHAR(CURRENT_DATE,'DD-MM-YYYY');

-- deuxième exercice
SELECT UPPER('Lors du match Lille / Brest le premier but a été marqué à la 69ème minute');
SELECT POSITION('BUT' IN UPPER('Lors du match Lille / Brest le premier but a été marqué à la 69ème minute')) AS POSITION;

-- troisième exercice
SELECT LTRIM('             Le temps ne permet pas de réaliser les travaux') AS MEF;
SELECT RPAD(LTRIM('             Le temps ne permet pas de réaliser les travaux'),50,'-') AS MEF;

-- quatrième exercice
--solution 1
SELECT * FROM FILM T1 WHERE  
   EXISTS (SELECT IDENT_REALISATEUR FROM REALISATEUR WHERE PRENOM 
= 'LUC' AND IDENT_REALISATEUR = T1.IDENT_REALISATEUR ) 
   AND DATE_SORTIE BETWEEN ('01/01/85') AND ('30/05/1995') 
ORDER BY TITRE;

--solution 2
SELECT * FROM FILM T1, REALISATEUR T2 WHERE 
       T1.IDENT_REALISATEUR = T2.IDENT_REALISATEUR AND 
       T2.PRENOM = 'LUC' AND 
       DATE_SORTIE BETWEEN ('01/01/85') AND ('30/05/1995') 
ORDER BY TITRE;

--solution 3
SELECT * FROM FILM T1 WHERE  
   T1.IDENT_REALISATEUR IN (SELECT IDENT_REALISATEUR FROM 
REALISATEUR WHERE PRENOM = 'LUC')  
   AND DATE_SORTIE BETWEEN ('01/01/85') AND ('30/05/1995') 
ORDER BY TITRE;

--solution 4
SELECT T1.* 
FROM FILM T1 INNER JOIN REALISATEUR ON T1.IDENT_REALISATEUR = REALISATEUR.IDENT_REALISATEUR
WHERE PRENOM = 'LUC'  
AND DATE_SORTIE BETWEEN ('01/01/85') AND ('30/05/1995') 
ORDER BY TITRE;

-- cinquième exercice
SELECT 'NOUS SOMMES LE '||TO_CHAR(CURRENT_DATE, 'DAY DD MONTH YYYY')||' . IL EST '||TO_CHAR(CURRENT_TIMESTAMP,'HH24')||' HEURE ET '||TO_CHAR(CURRENT_TIMESTAMP,'MI')||' MINUTES.' AS EXERCICE_5;

-- sixième exercice
--solution 1
SELECT TITRE,DATE_SORTIE,CURRENT_DATE - DATE_SORTIE AS NB_JOURS_DEPUIS_SORTIE FROM FILM;

--solution 2
SELECT TITRE, DATE_SORTIE, (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM DATE_SORTIE)) * 12 + (EXTRACT(MONTH FROM CURRENT_DATE) - EXTRACT(MONTH FROM DATE_SORTIE)) NB_MOIS_DEPUIS_SORTIE FROM FILM;  

-- septième exercice
SELECT TITRE, ROUND(MAX(RECETTE_MONDE)::numeric / SUM(NB_JOUR_TOURNAGE), 2) as "Recette par jour de tournage"
FROM CASTING INNER JOIN STATISTIQUE ON CASTING.IDENT_FILM = STATISTIQUE.IDENT_FILM
INNER JOIN FILM ON STATISTIQUE.IDENT_FILM = FILM.IDENT_FILM
GROUP BY TITRE;

-- huitième exercice
SELECT RANK() OVER (ORDER BY RECETTE_MONDE DESC) as rang, TITRE, RECETTE_MONDE
FROM FILM INNER JOIN STATISTIQUE ON FILM.IDENT_FILM = STATISTIQUE.IDENT_FILM;

--RI05 - premier exercice
CREATE USER ALFRED WITH PASSWORD 'Postgresql';
GRANT SELECT ON CASTING TO ALFRED;

--deuxième exercice
GRANT SELECT ON FILM TO PUBLIC;

--troisième exercice
GRANT UPDATE (TITRE,RESUME) ON FILM TO ALFRED;
UPDATE FILM SET GENRE1='Toto' WHERE IDENT FILM =1 ;
UPDATE TITRE SET TITRE='SUBWAY-' WHERE IDENT =1 ;

--quatrième exercice
REVOKE UPDATE ON FILM FROM ALFRED;
GRANT UPDATE (RESUME) ON FILM TO ALFRED;

--RI06 - exercice
/* BEGION TRANSACTION fonctionne aussi à la place de START TRANSACTION */
START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 

/* Vidage de la table FILM */  
DELETE FROM FILM;  

SELECT COUNT(*) FROM ACTEUR WHERE NOM = 'FISHER' AND PRENOM = 'CARRIE'; 

INSERT INTO FILM VALUES (1,'SUBWAY','POLICIER','DRAME', '19850410',1,1,'GAUMONT',  
'Conte les aventures de la population souterraine dans les couloirs du métro parisien');  

/* Positionnement du premier point */ 
SAVEPOINT ETAPE_NUMERO1;  
INSERT INTO FILM VALUES (2,'NIKITA','DRAME','ROMANTIQUE', '19900221',1,1,'GAUMONT',  
'Nikita condamnée à la prison à perpétuité est contrainte à travailler secrètement pour le gouvernement en tant que agent hautement qualifié des services secrets.');

/* Positionnement du deuxième point */ 
SAVEPOINT ETAPE_NUMERO2;
INSERT INTO FILM VALUES (3,'SUBWAY','POLICIER','DRAME', '19850410',1,1,'GAUMONT',  
'Conte les aventures de la population souterraine dans les couloirs du métro parisien');  

/* Retour au point numéro 2 afin de ne pas insérer 2 fois le film 'SUBWAY' */ 
ROLLBACK TO ETAPE_NUMERO2; 
 
COMMIT;

SELECT * FROM FILM;

--RI07 - premier exercice
CREATE OR REPLACE FUNCTION public."CALCUL_AGE_ACTEUR"(IN "dateNaissance" date)
    RETURNS integer
    LANGUAGE 'plpgsql'
    
AS $BODY$
DECLARE vAge integer;
BEGIN
vAge := (CURRENT_DATE - "dateNaissance") / 365;

RETURN vAge;
END;
$BODY$;

SELECT public."CALCUL_AGE_ACTEUR"(DATE_NAISSANCE) FROM ACTEUR;

-- deuxième exercice
CREATE OR REPLACE PROCEDURE public."NOUVEAUPAYS"()
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
iid integer;
BEGIN
SELECT MAX(IDENT_PAYS) INTO iid FROM PAYS;
INSERT INTO PAYS VALUES (iid + 1,'A COMPLETER');
END;
$BODY$;

CALL public."NOUVEAUPAYS"();

SELECT * FROM PAYS;

-- troisième exercice
CREATE OR REPLACE FUNCTION public."NOUVEAUPAYSINT"()
RETURNS TRIGGER
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE nbvaleur integer := 0;
BEGIN
SELECT COUNT(*) INTO nbvaleur FROM PAYS WHERE PAYS.IDENT_PAYS = NEW.NATIONALITE;
IF nbvaleur = 0 THEN
INSERT INTO PAYS VALUES (NEW.NATIONALITE,'A COMPLETER');
END IF;
RETURN NEW;
END;
$BODY$;

CREATE OR REPLACE TRIGGER T_INS_ACTEUR 
BEFORE INSERT   
ON ACTEUR   
FOR EACH ROW
Execute FUNCTION public."NOUVEAUPAYSINT"();

INSERT INTO ACTEUR VALUES (14, 'CRUZ', 'PENELOPE', '19740428', 76, 7);
SELECT * FROM ACTEUR;
SELECT * FROM PAYS;

--RI08 - premier exercice
SELECT TITRE, NOM, PRENOM 
FROM FILM INNER JOIN CASTING ON FILM.IDENT_FILM = CASTING.IDENT_FILM 
INNER JOIN ACTEUR ON CASTING.IDENT_ACTEUR = ACTEUR.IDENT_ACTEUR
WHERE NATIONALITE = (SELECT PAYS.IDENT_PAYS FROM PAYS WHERE PAYS.LIBELLE = 'FRANCE')   
ORDER BY FILM.TITRE, NOM;

--deuxième exercice
INSERT INTO ACTEUR VALUES (15, 'DUJARDIN', 'JEAN', '19720619',57,1);

SELECT * FROM ACTEUR A1
WHERE EXISTS( SELECT PRENOM, COUNT(1) FROM ACTEUR AS A2 WHERE A1.PRENOM = A2.PRENOM
GROUP BY PRENOM
HAVING COUNT(1) > 1);

--Troisième exercice
ALTER TABLE PAYS RENAME TO PAYSold;

CREATE TABLE PAYS
     (IDENT_PAYS SERIAL NOT NULL PRIMARY KEY,
      LIBELLE    VARCHAR(100));

COPY PAYS (LIBELLE) FROM 'E:\Extraits\curiexplore-pays.csv' DELIMITER ',' CSV HEADER;

SELECT ACTEUR.NOM, ACTEUR.PRENOM, ACTEUR.NATIONALITE, PAYSold.LIBELLE
FROM ACTEUR INNER JOIN PAYSold ON ACTEUR.NATIONALITE = PAYSold.IDENT_PAYS
WHERE (PAYSold.LIBELLE = 'A COMPLETER');

SELECT * from PAYS
WHERE LIBELLE = 'Espagne';

UPDATE ACTEUR SET NATIONALITE = 56
WHERE NATIONALITE = 7;

DO $$
DECLARE
C1 CURSOR FOR SELECT DISTINCT NATIONALITE FROM ACTEUR
WHERE NATIONALITE <> 56;

inationalite numeric(3);
ipays numeric(3);
vlibelle varchar(100);
BEGIN
OPEN C1;
LOOP
FETCH C1 into inationalite;
exit when not found;
RAISE NOTICE'nationalite %', inationalite;
SELECT LIBELLE INTO vlibelle FROM PAYSold WHERE IDENT_PAYS = inationalite;
RAISE NOTICE'libelle %', vlibelle;
 IF (vlibelle <> 'A COMPLETER') THEN
			SELECT IDENT_PAYS INTO ipays FROM PAYS WHERE LIBELLE = vlibelle;
            RAISE NOTICE'pays %',ipays;
			UPDATE ACTEUR SET NATIONALITE = ipays
			WHERE NATIONALITE = inationalite;
    END IF;
END LOOP;
CLOSE C1;
END $$;

SELECT ACTEUR.NOM, ACTEUR.PRENOM, ACTEUR.NATIONALITE, PAYS.LIBELLE
FROM ACTEUR LEFT OUTER JOIN PAYS ON ACTEUR.NATIONALITE = PAYS.IDENT_PAYS;

DROP TABLE PAYSold;
DROP TRIGGER T_INS_ACTEUR;
	  
