--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
EXEC sp_addextendedproperty @name = 'description', @value = 'liste des acteurs par film avec précision du rôle et du nombre de jours de tournage', @level0type='SCHEMA', @level0name='dbo', @level1type='TABLE', @level1name='CASTING'

--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 ADD CONSTRAINT [DF_Recette]  DEFAULT (0) FOR RECETTE;

--modification colonnes	
ALTER TABLE FILM ALTER COLUMN TITRE varchar(50) NOT NULL;
DROP INDEX I2_GENREPAYS ON FILM;
ALTER TABLE FILM ALTER COLUMN PAYS smallint NOT NULL;
CREATE INDEX I2_GENREPAYS ON FILM (GENRE1, PAYS);

--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
EXEC sp_rename 'FILM', 'FILMOLD';

--création table
SELECT * INTO FILM FROM FILMOLD;

--suppression table
DROP TABLE FILM; 
DROP TABLE IF EXISTS FILMOLD;
IF OBJECT_ID('FILMOLD') IS NOT NULL
DROP TABLE FILMOLD;
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FILMOLD')
DROP TABLE FILMOLD;
IF EXISTS (SELECT 1 FROM sys.TABLES WHERE name = 'FILMOLD')
DROP TABLE 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) as 'acteur 12' from ACTEUR;
select COUNT(1) as 'casting 13' from CASTING;
select COUNT(1) as 'film 5' from FILM;
select COUNT(1) as 'pays 3' from PAYS;
select COUNT(1) as 'realisateur 4' from REALISATEUR;
select COUNT(1) as 'statistique 5' from STATISTIQUE;

-- 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 SUBSTRING(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 >= '01/01/1948' AND 
   ACTEUR.DATE_NAISSANCE <= '31/05/1978'
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 '01/01/1948' AND '31/05/1978'
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','', 
'20070704',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
SELECT * INTO ACTEUR_FRANCE 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','19/03/1955',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 CONVERT(CHAR(10),GETDATE(),105);

-- deuxième exercice
SELECT UPPER('Lors du match Lille / Brest le premier but a été marqué à la 69ème minute');
SELECT CHARINDEX('BUT',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 LTRIM('             Le temps ne permet pas de réaliser les travaux') + REPLICATE('-','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 ' + DATENAME(weekday, GETDATE()) + ' ' + DATENAME(day, GETDATE()) + ' ' + DATENAME(MONTH, GETDATE()) + ' ' 
+ DATENAME(YEAR, GETDATE()) + '. Il est ' +  DATENAME(HH, GETDATE()) +' heure et '
+  DATENAME(n, GETDATE()) +' minutes.' AS EXERCICE_5;

-- sixième exercice
--solution 1
SELECT TITRE, DATE_SORTIE, DATEDIFF(DAY, DATE_SORTIE, GETDATE()) AS NB_JOURS_DEPUIS_SORTIE FROM FILM;

--solution 2
SELECT TITRE, DATE_SORTIE, DATEDIFF(MONTH, DATE_SORTIE, GETDATE()) AS NB_MOIS_DEPUIS_SORTIE FROM FILM;

-- septième exercice
SELECT TITRE, ROUND(MAX(RECETTE_MONDE) / 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
USE master; 
GO 
CREATE LOGIN ALFRED
    WITH PASSWORD = 'SqlServer.72';
	USE CINEMA
GO
CREATE USER ALFRED FROM LOGIN ALFRED;
GRANT CONNECT TO ALFRED;
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
BEGIN 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', '10/04/1985',1, 1,'GAUMONT',  
'Conte les aventures de la population souterraine dans les couloirs du métro parisien');

SAVE TRANSACTION ETAPE_NUMERO1;  
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.');

DELETE FROM ACTEUR WHERE NOM = 'FISHER' AND PRENOM = 'CARRIE';

/* Positionnement du deuxième point */ 
SAVE TRANSACTION ETAPE_NUMERO2; 
INSERT INTO FILM VALUES (3,'SUBWAY','POLICIER','DRAME', '10/04/1985',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 TRANSACTION ETAPE_NUMERO2; 
 
COMMIT;

SELECT * FROM FILM;

--RI07 - premier exercice
CREATE OR ALTER FUNCTION CALCUL_AGE_ACTEUR (@dateNaissance date)
RETURNS integer
AS
BEGIN
DECLARE @Age integer
SELECT @Age= DATEDIFF(YEAR, @dateNaissance, GETDATE());
RETURN @Age
END;

SELECT dbo.CALCUL_AGE_ACTEUR(DATE_NAISSANCE) FROM ACTEUR;

-- deuxième exercice
CREATE OR ALTER PROCEDURE NOUVEAUPAYS
AS
BEGIN
DECLARE @iid int;
SELECT @iid = MAX(IDENT_PAYS) FROM PAYS;
INSERT INTO PAYS VALUES (@iid + 1,'A COMPLETER');
END;

EXEC NOUVEAUPAYS;

SELECT * FROM PAYS;

-- troisième exercice
CREATE OR ALTER TRIGGER [dbo].[T_INS_ACTEUR] ON [dbo].[ACTEUR]
INSTEAD OF INSERT
AS
BEGIN
DECLARE @nbvaleur int, @ipays int, @erreur varchar(150);   
	SET @nbvaleur = 0;
	SELECT @ipays = NATIONALITE FROM inserted;
	SELECT @nbvaleur = COUNT(1) FROM PAYS WHERE IDENT_PAYS = @ipays;   
	
IF (@nbvaleur = 0)
	SET @erreur = 'La nationalité est inconnue : ' + CONVERT(varchar(3), @ipays) + '. Une insertion dans la table PAYS va être réalisée. Merci de compléter le libellé du pays ultérieurement.'
	BEGIN
		RAISERROR(@erreur,16, 10 );
		INSERT INTO PAYS VALUES (@ipays,'A COMPLETER');   
	END
INSERT INTO ACTEUR (IDENT_ACTEUR, NOM, PRENOM, DATE_NAISSANCE, NB_FILM, NATIONALITE)
SELECT IDENT_ACTEUR, NOM, PRENOM, DATE_NAISSANCE, NB_FILM, NATIONALITE FROM inserted;
END;

INSERT INTO ACTEUR VALUES (14, 'CRUZ', 'PENELOPE', '28/04/1974', 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', '19/06/1972',57,1);

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

--Troisième exercice
EXEC sp_rename 'PAYS', 'PAYSold';

CREATE TABLE PAYS
     (IDENT_PAYS SMALLINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
      LIBELLE    VARCHAR(100));

sp_configure 'show advanced options', 1 
GO 
RECONFIGURE 
GO 
sp_configure 'Ad Hoc Distributed Queries', 1 
GO 
RECONFIGURE

BULK INSERT PAYS FROM 'E:\Extraits\curiexplore-pays.csv' WITH (firstrow = 2, fieldterminator = ',', rowterminator='\n');

SELECT * FROM PAYS;

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;

BEGIN
DECLARE @inationalite integer, @ipays integer
DECLARE @vlibelle varchar(100)
DECLARE C1 CURSOR
FOR
SELECT DISTINCT NATIONALITE FROM ACTEUR;

OPEN C1;
FETCH C1 into @inationalite
WHILE @@FETCH_STATUS=0
BEGIN
	SELECT @vlibelle = LIBELLE FROM PAYSold WHERE IDENT_PAYS = @inationalite;
	IF @vlibelle <> 'A COMPLETER'
		BEGIN
			SELECT @ipays = IDENT_PAYS FROM PAYS WHERE UPPER(LIBELLE collate SQL_Latin1_General_CP1251_CS_AS) = @vlibelle;
			UPDATE ACTEUR SET NATIONALITE = @ipays
			WHERE NATIONALITE = @inationalite;
		END;
	FETCH C1 into @inationalite
END;
CLOSE C1;
DEALLOCATE 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;
