/*TP PostgreSQL n°3
- Requêtes avancées et transactions -
Clément Saintier*/

/*************************/
/**2°) Requêtes partie 1**/
/*************************/

/*1) Afficher toutes les informations propres aux cinémas.*/
SELECT * FROM Cinema;

/*2) Afficher le titre de tous les films.*/
SELECT titre FROM Film;

/*3) Afficher le titre de tous les films par ordre alphabétique.*/
SELECT titre FROM Film ORDER BY titre;

/*4) Afficher le titre des films sortis en 1995 ou après.*/
SELECT titre FROM Film WHERE datesortie >= TO_DATE('1995', 'YYYY');

/*5) Afficher le titre des films sortis entre 1980 et 1995 par ordre alphabétique inverse.*/
SELECT titre FROM Film WHERE datesortie BETWEEN TO_DATE('1980', 'YYYY') AND TO_DATE('1995', 'YYYY') ORDER BY titre DESC;

/*6) Affiche le titre des films, et leur date de sortie, pour les films antérieur à 1999 et dont le titre
contient la chaine 'in'.*/
SELECT titre, datesortie AS sortie FROM Film WHERE datesortie<TO_DATE('1999','YYYY') AND titre LIKE '%in%';

/*7) Afficher le nom et le prénom de tous les acteurs.*/
SELECT nom FROM acteur;

/*8) Afficher le nom des acteurs et des cinémas.*/
SELECT DISTINCT 
    cinema.nom, 
    acteur.nom 
FROM
    acteur,
    casting,
    film,
    seance,
    cinema 
WHERE 
    acteur.idacteur = casting.idacteur 
    and casting.idfilm = film.idfilm 
    and seance.film = film.idfilm 
    and cinema.idcine = seance.cine 
        ORDER BY 1;
              
/*9) Afficher le nom et le prénom des acteurs dont le nom commence par D.*/
SELECT nom, prenom FROM Acteur WHERE nom LIKE 'D%';

/*10) Afficher le nom (en majuscule) des acteurs et l'initiale de leur prénom suivie d'un point, c'est à dire sous le format NOM P. (opérateurs UPPER et CONCAT). Renommer le nom de la colonne en Nom.*/
SELECT CONCAT(UPPER(nom), ' ', LEFT(prenom, 1), '.') AS nom FROM acteur;

/*11) Afficher les heures de début et de fin de chaque séance sous le format hh-mm.*/
SELECT
    TO_CHAR(heuredbt, 'HH24-MI') AS debut,
    TO_CHAR(heurefin, 'HH24-MI') AS fin
FROM seance;

/*12 et 13) Afficher les heures de début et de fin de chaque séance commençant à ou après 19h00 sous le format hh-mm, en renommant les colonnes Debut et Fin.*/
SELECT
    TO_CHAR(heuredbt, 'HH24-MI') AS debut,
    TO_CHAR(heurefin, 'HH24-MI') AS fin
FROM seance
WHERE TO_NUMBER(TO_CHAR(heuredbt, 'HH24'), '99') >= 19;

/*14) Rien*/





/*************************/
/**2°) Requêtes partie 2**/
/*************************/

/*15) Afficher les heures de début et de fin de chaque séance ainsi que leur durée en minutes, pour les séances commençant à ou après 21h00. Les heures doivent etre affichées sous le format hh-mm, en renommant les colonnes en Debut,Fin et DureeMn*/
SELECT
    TO_CHAR(heuredbt, 'HH24-MI') AS debut,
    TO_CHAR(heurefin, 'HH24-MI') AS fin,
    DATE_PART('hour', heurefin)*60+DATE_PART('minute', heurefin)
     - DATE_PART('hour', heuredbt)*60+DATE_PART('minute', heuredbt)
        AS dureeMn
FROM seance
WHERE TO_NUMBER(TO_CHAR(heuredbt, 'HH24'), '99') >= 21;

/*16) Afficher les horaires des séances de la première salle du premier cinéma*/
SELECT
    idseance, 
    TO_CHAR(heuredbt, 'HH24-MI') AS debut,
    TO_CHAR(heurefin, 'HH24-MI') AS fin
FROM
    Seance
WHERE
    cine = 1
    and numsalle = 1;
    
/*17) Afficher le prix moyen d'une séance de cinéma*/
SELECT avg(prix) FROM Seance;

/*18) Afficher, pour chaque personnage, le nombre de fois qu'il est joué et son titre*/
SELECT
    casting.personnage, count(casting.personnage) AS nb_de_fois_apparait_a_lecran
FROM
    Casting, seance, film
WHERE casting.idfilm = film.idfilm
    and film.idfilm = seance.film
GROUP BY casting.personnage;

/*19) Afficher le personnage et le nombre de fois qu'il est joué si ce nombre est au moins égal à deux*/
SELECT
    casting.personnage, count(casting.personnage) AS nb_de_fois_apparait_a_lecran
FROM
    Casting, seance, film
WHERE casting.idfilm = film.film
    and film.idfilm = seance.film
GROUP BY casting.personnage
HAVING count(casting.personnage) > 2;

/*20) Afficher, pour chaque prénom, le nombre d'acteurs portant ce prénom*/
SELECT prenom, count(prenom)
FROM Acteur
GROUP BY prenom;

/*21) Afficher l'heure de fin de la séance la plus tardive*/
SELECT max(heurefin) FROM Seance;

/*22) Afficher l'heure de début et de fin des séances commençant le plus tôt*/
SELECT idseance, heuredbt, heurefin
FROM Seance
WHERE heuredbt = (SELECT MIN(heuredbt) FROM Seance);

/*23) Afficher les horaires des séances qui projettent le film Impitoyable*/
SELECT idseance, heuredbt, heurefin
FROM seance, film
WHERE
    seance.film = film.idfilm
    and film.titre = 'Impitoyable';
    
/*24) Afficher le titre des films programmés*/
SELECT DISTINCT titre
FROM film, seance
WHERE seance.film = film.idfilm;

/*25) Afficher le titre des films programmés avec les horaires des séances prévues*/
SELECT DISTINCT titre, heuredbt, heurefin
FROM film, seance
WHERE seance.film = film.idfilm;

/*26) Afficher le titre des films programmés (sans doublons) par ordre alphabétique sur le titre*/
SELECT DISTINCT titre
FROM film, seance
WHERE seance.film = film.idfilm
ORDER BY titre;

/*27) Afficher le titre des films non programmés (opérateur minus)*/
SELECT titre FROM film
    EXCEPT
SELECT DISTINCT titre
FROM film, seance
WHERE seance.film = film.idfilm;

/*28) Afficher le nom des cinémas et les séances qu'ils proposent*/
SELECT cinema.nom, film.titre, seance.heuredbt, seance.heurefin
FROM cinema, seance, film
WHERE seance.cine = cinema.idcine
    and seance.film = film.idfilm;
    
/*29) Afficher pour chaque prénom présent au moins deux fois chez les acteurs, le nom et le prénom de ces acteurs*/
INSERT INTO acteur VALUES(12, 'qsdf', 'Roy');
INSERT INTO acteur VALUES(13, 'qsdf', 'Sandra');
SELECT nom, prenom
FROM acteur
WHERE prenom IN(
    SELECT prenom
    FROM acteur
    GROUP BY prenom
    HAVING count(prenom) >= 2
);

/*30) Afficher le nom des cinémas, les horaires des séances ainsi que le titre du film proposé à cette séance*/
SELECT cinema.nom, film.titre, seance.heuredbt, seance.heurefin
FROM cinema, seance, film
WHERE seance.cine = cinema.idcine
    and seance.film = film.idfilm;

/*31) Afficher le nom des cinémas, les horaires des séances sous le bon format, où passe le film intitulé 'Impitoyable', quelle que soit la casse du titre du film*/
SELECT
    cinema.nom,
    film.titre,
    TO_CHAR(seance.heuredbt, 'HH24:MI') AS debut,
    TO_CHAR(seance.heurefin, 'HH24:MI') AS fin
FROM cinema, seance, film
WHERE seance.cine = cinema.idcine
    and seance.film = film.idfilm
    and lower(film.titre) = 'impitoyable';
     
/*32) Même chose que précédemment en ordonnant la sélection sur la date de début des séances.*/
SELECT
    cinema.nom,
    film.titre,
    TO_CHAR(seance.heuredbt, 'HH24:MI') AS debut,
    TO_CHAR(seance.heurefin, 'HH24:MI') AS fin
FROM cinema, seance, film
WHERE seance.cine = cinema.idcine
    and seance.film = film.idfilm
    and lower(film.titre) = 'impitoyable'
ORDER BY seance.heuredbt;

/*33) Afficher les titres des films dont une séance programmée dure moins de 2h00 et afficher aussi cette durée en minutes*/
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 seance, film
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;
        
/*34) Afficher le nom et prénom des acteurs jouant dans chaque film ainsi que le titre du film*/
SELECT acteur.nom, acteur.prenom, film.titre
FROM acteur, casting, film
WHERE acteur.idacteur = casting.idacteur
    and casting.idfilm = film.idfilm;

