\i cinema.sql

#PREMIÈRE SERIE DE REQUÊTES

SELECT * FROM cinema;

SELECT titre FROM film;

SELECT titre FROM film ORDER BY titre;

SELECT titre FROM film WHERE datesortie >= TO_DATE('1995', 'YYYY');

SELECT titre FROM film WHERE datesortie >= TO_DATE('1980', 'YYYY') AND datesortie <= TO_DATE('1995', 'YYYY') ORDER BY titre DESC;

SELECT titre, datesortie AS Sortie FROM film WHERE datesortie < TO_DATE('1999', 'YYYY') AND titre LIKE '%in%';

SELECT nom, prenom FROM acteur;

SELECT nom FROM acteur ; SELECT nom FROM cinema ;
#ok, c'est pas du tout subtil, mais le résultat est celui demandé...

SELECT nom, prenom FROM acteur WHERE nom LIKE 'D%';

SELECT CONCAT(UPPER(nom), ' ', SUBSTR(prenom, 1, 1), '.') AS Nom FROM acteur;

SELECT CONCAT(EXTRACT(HOUR FROM heuredbt), '-', EXTRACT(MINUTE FROM heuredbt)), CONCAT(EXTRACT(HOUR FROM heurefin), '-', EXTRACT(MINUTE FROM heurefin)) FROM seance;

SELECT CONCAT(EXTRACT(HOUR FROM heuredbt), '-', EXTRACT(MINUTE FROM heuredbt)), CONCAT(EXTRACT(HOUR FROM heurefin), '-', EXTRACT(MINUTE FROM heurefin)) FROM seance WHERE EXTRACT(HOUR FROM heuredbt) >= 19;

SELECT CONCAT(EXTRACT(HOUR FROM heuredbt), '-', EXTRACT(MINUTE FROM heuredbt)) AS Debut, CONCAT(EXTRACT(HOUR FROM heurefin), '-', EXTRACT(MINUTE FROM heurefin)) AS Fin FROM seance WHERE EXTRACT(HOUR FROM heuredbt) >= 19;



#QUESTION 4

BEGIN;

CREATE TABLE categorie(
idcat INT PRIMARY KEY,
agemini INT,
intitule VARCHAR(50) );

SAVEPOINT tuplecat; 

INSERT INTO categorie VALUES (01, 12, 'première catégorie');
INSERT INTO categorie VALUES (02, 18, 'deuxième catégorie');

SELECT * FROM categorie;

ROLLBACK WORK TO SAVEPOINT tuplecat;

SELECT * FROM categorie;

COMMIT;



#DEUXIÈME SERIE DE REQUÊTES

SELECT CONCAT(EXTRACT(HOUR FROM heuredbt), '-', EXTRACT(MINUTE FROM heuredbt)) AS Debut, CONCAT(EXTRACT(HOUR FROM heurefin), '-', EXTRACT(MINUTE FROM heurefin)) AS Fin, EXTRACT(MINUTE FROM heurefin - heuredbt) + (EXTRACT(HOUR FROM heurefin - heuredbt)*60) AS DureeMn FROM seance;

SELECT heuredbt FROM seance WHERE numsalle = 1 AND cine = 1;

SELECT AVG(prix) FROM seance;

SELECT personnage, COUNT(personnage) AS nb_apparition FROM casting GROUP BY personnage;

SELECT personnage, COUNT(personnage) AS nb_apparition FROM casting GROUP BY personnage HAVING COUNT(personnage) >= 2;

SELECT prenom, COUNT(prenom) FROM acteur GROUP BY prenom;

SELECT MAX(heurefin) FROM seance;

SELECT heuredbt, heurefin FROM seance WHERE heuredbt = (SELECT MIN(heuredbt) FROM seance);

SELECT idseance, heuredbt, heurefin FROM seance WHERE film = (SELECT idfilm FROM film WHERE titre = 'Impitoyable');

SELECT DISTINCT titre FROM film, seance WHERE film.idfilm = seance.film;

SELECT DISTINCT titre, heuredbt, heurefin FROM film, seance WHERE film.idfilm = seance.film ORDER BY titre;

SELECT DISTINCT titre FROM film, seance WHERE film.idfilm = seance.film ORDER BY titre;

SELECT titre FROM film EXCEPT SELECT DISTINCT titre FROM film, seance WHERE film.idfilm = seance.film;

SELECT DISTINCT cinema.nom, film.titre, seance.heuredbt FROM cinema, film, seance WHERE cinema.idcine = seance.cine AND film.idfilm = seance.film;

SELECT nom, prenom FROM acteur WHERE prenom IN (SELECT prenom FROM acteur GROUP BY prenom HAVING COUNT(prenom) >= 2);

SELECT cinema.nom, seance.heuredbt, film.titre, FROM cinema, seance, film WHERE cinema.idcine = seance.cine AND film.idfilm = seance.film;

SELECT cinema.nom, TO_CHAR(seance.heuredbt, 'HH24:MI') AS debut, TO_CHAR(seance.heurefin, 'HH24:MI') AS fin FROM cinema, seance, film WHERE cinema.idcine = seance.cine AND film.idfilm = seance.film AND LOWER(film.titre) = 'impitoyable';

SELECT cinema.nom, TO_CHAR(seance.heuredbt, 'HH24:MI') AS debut, TO_CHAR(seance.heurefin, 'HH24:MI') AS fin FROM cinema, seance, film WHERE cinema.idcine = seance.cine AND film.idfilm = seance.film AND LOWER(film.titre) = 'impitoyable' ORDER BY debut;

SELECT DISTINCT film.titre, DATE_PART('hour', heurefin)*60 + DATE_PART('minute', heurefin) - DATE_PART('hour', heuredbt)*60 - DATE_PART('minute', heuredbt) AS dureemn FROM film, seance WHERE film.idfilm = seance.film AND DATE_PART('hour', heurefin)*60 + DATE_PART('minute', heurefin) - DATE_PART('hour', heuredbt)*60 - DATE_PART('minute', heuredbt) < 120;

SELECT nom, prenom, titre FROM acteur, film, casting WHERE acteur.idacteur = casting.idacteur AND film.idfilm = casting.idfilm;



#QUESTION 4

ALTER TABLE seance ADD CHECK(numsalle > 0 AND prix > 0);

ALTER TABLE film ADD duree INTEGER;

UPDATE film SET duree = DATE_PART('hour', seance.heurefin)*60 + DATE_PART('minute', seance.heurefin) - DATE_PART('hour', seance.heuredbt) - DATE_PART('minute', seance.heuredbt) FROM seance WHERE film.idfilm = seance.film;

