Aller au contenu principal
Physique, Chimie et Informatique > Terminales Spé. NSI > Bases de données

Gestion d'une salle de cinéma

L’objectif de cette séance est de mettre en œuvre tous les concepts du chapitre.

La base de donnée accessible à cette adresse simule de façon simplifiée la gestion d’un cinéma.

  1. Étudier la structure de chacune des relations de la base et établir son schéma relationnel.
    En particulier, faire apparaître les clés primaires et les clés étrangères.

    Réponse
    • Quelles sont les tables de la base ?
    .tables
    • Quelle est la structure de chacune de ces relations ?
    pragma table_info('film');
    pragma table_info('projection');
    pragma table_info('categorie');

    Rappel : ces commandes sont spécifiques à SQLite et ne sont pas au programme. Il est cependant utile de les connaître.

  2. Afficher le contenu de chacune des relations.

    Réponse
    SELECT *
    FROM film;
    
    SELECT *
    FROM projection;
    
    SELECT *
    FROM categorie;

  3. Quelles sont les différentes catégories de film ?

    Réponse
    SELECT codecat, libcat
    FROM categorie;

  4. Quelles sont les noms des salles du cinéma triés dans l’ordre alphabétique ?

    Réponse
    SELECT DISTINCT salle
    FROM projection
    ORDER BY salle;

  5. Quels sont les films (numéro, titre, durée) projetés au cinéma ?

    Réponse
    SELECT DISTINCT film.numfilm, titre, duree
    FROM film, projection
    WHERE film.numfilm = projection.numfilm;

    ou

    SELECT DISTINCT f.numfilm, titre, duree
    FROM film f, projection p
    WHERE f.numfilm = p.numfilm;

    ou

  6. Quels sont les salles qui ont des séances à 11h00 ?

    Réponse
    SELECT DISTINCT salle
    FROM projection
    WHERE seance = '11h00';

  7. Quelle est le titre et la durée du film numéro 20 ?

    Réponse
    SELECT titre, duree
    FROM film
    WHERE numfilm = 20;

  8. Quelle est le titre et la durée (en heure) du film numéro 20 ?

    Réponse
    SELECT titre, (duree/60)
    FROM film
    WHERE numfilm = 20;

  9. Quels sont les noms des films diffusés en salle Sud qui durent entre 60 et 120 minutes ?

    Réponse
    SELECT DISTINCT titre
    FROM film, projection
    WHERE film.numfilm = projection.numfilm
    AND salle = 'Sud'
    AND duree BETWEEN 60 AND 120;

  10. Combien il y a-t-il de films dans la base (dans une colonne nommée : Total films) ?

    Réponse
    SELECT COUNT(*) AS 'Total films'
    FROM film;

  11. Combien il y a-t-il de films produits par Universal dans la base ?

    Réponse
    SELECT production, COUNT(numfilm) AS 'Nbre films'
    FROM film
    WHERE production = (SELECT production 
                        FROM film 
                        WHERE production = "Universal");

  12. Combien il y a-t-il de films dans la catégorie documentaire (libellé catégorie, nombre de films) ?

    Réponse
    SELECT COUNT(*) AS 'Nbre films'
    FROM film f
    JOIN categorie c ON f.codecat = c.codecat
    WHERE libcat = "documentaire";

  13. Quels sont les titres des films de 140 minutes et produits par UGC ?

    Réponse
    SELECT titre
    FROM film
    WHERE duree = 140
    AND production = 'UGC';

  14. Quels sont les titres des films commençant par C ou L (triés par ordre alphabétique) ?

    Réponse
    SELECT titre
    FROM film
    WHERE titre LIKE 'C%'
    OR titre LIKE 'L%'
    ORDER BY titre;

  15. Quels sont les titres des films qui ne commencent pas par la lettre U (triés alphabétiquement) ?

    Réponse
    SELECT titre
    FROM film
    WHERE titre NOT LIKE 'U%'
    ORDER BY titre;

  16. Quels sont les titres et la durée des films qui ne sont pas diffusés en salle nord ?

    Réponse
    SELECT DISTINCT titre, duree
    FROM film, projection
    WHERE film.numfilm = projection.numfilm
    AND salle != 'Nord';

  17. Quels sont les titres des films diffusés à 11h dans les salles Nord ou Centrale ?

    Réponse
    SELECT DISTINCT titre
    FROM film, projection
    WHERE film.numfilm=projection.numfilm
    AND seance LIKE '11h%'
    AND salle IN ('Nord','Centrale');

    ou

    SELECT DISTINCT titre
    FROM film f
    JOIN projection p ON f.numfilm = p.numfilm 
    WHERE seance = '11h00' 
        AND (salle = 'Nord' OR salle = 'Centrale');

  18. Quelle est la durée moyenne des films de la base ?

    Réponse
    SELECT AVG(duree)
    FROM film ;

  19. Quelle est la séance du film diffusé le plus tôt dans la journée du 18/11 ?

    Réponse
    SELECT MIN(seance) AS 'Première séance'
    FROM projection
    WHERE date = '2005/11/18';

  20. Quel est le numéro et le titre des films qui ont la même durée que le film numéro 71 ?
    Utiliser deux requêtes imbriquées.

    Réponse
    SELECT numfilm, titre
    FROM film
    WHERE duree = (SELECT duree
                   FROM film
                   WHERE numfilm = 71);

  21. Quel est le numéro et le titre des films de la même catégorie que Super Mondet XII ?
    Utiliser deux requêtes imbriquées.

    Réponse
    SELECT numfilm, titre
    FROM film
    WHERE codecat = (SELECT codecat
                     FROM film
                     WHERE titre = 'Super Mondet XII');

  22. Quels sont les titres des films diffusés aux horaires suivants : 11h00, 13h10 et 14h50 ?

    Réponse
    SELECT DISTINCT titre
    FROM film f, projection p
    WHERE f.numfilm = p.numfilm
    AND seance IN ('11h00','13h10','14h50');

    ou

    SELECT DISTINCT titre
    FROM film f 
    JOIN projection p ON f.numfilm = p.numfilm
    WHERE seance IN ('11h00', '13h10', '14h50');

  23. Quels sont les films (numéro et titre) plus longs que la moyenne de tous les films ?
    Utiliser deux requêtes imbriquées.

    Réponse
    SELECT numfilm, titre
    FROM film
    WHERE duree > (SELECT AVG(duree)
                   FROM film);

  24. Quels films (numéro, salle et séance) sont programmés aux mêmes horaires ?
    Utiliser une autojointure.

    Réponse
    SELECT DISTINCT p1.numfilm, p1.salle, p1.seance
    FROM projection p1, projection p2
    WHERE p1.seance = p2.seance
    AND p1.numfilm != p2.numfilm;

  25. Quelle est le titre, la salle, la séance et la durée (en heure) des films projeté. En utilisant la fonction ROUND(,<nb décimales>) ?

    Réponse
    SELECT titre, salle, seance, ROUND(duree/60.,1) AS 'duree'
    FROM film f, projection p
    WHERE f.numfilm = p.numfilm;

  26. Quels sont les films (numéro et titre) qui n’ont pas été diffusés ?

    Réponse
    SELECT DISTINCT titre, f.numfilm
    FROM film f, projection p
    WHERE f.numfilm NOT IN (
        SELECT f.numfilm
        FROM film f, projection p
        WHERE f.numfilm = p.numfilm
    );

  27. Quel est le film le plus diffusé ?

    Réponse

On souhaite maintenant ajouter l’attribut tarif à la relation projection. Exécuter la commande suivante :

ALTER TABLE projection ADD tarif float;
  1. Vérifier que la structure de la relation projection a bien été modifiée.

On fait la supposition que le tarif des films dépend du film, de la salle, de la séance et de la date de projection. Le tarif exprime le tarif de base.

  1. Mettre à jour la table projection en fixant un tarif de base à 5 € pour la salle nord, 8 € pour la salle centrale, 6.5 € pour la salle sud.

    Réponse
    UPDATE projection
    SET tarif = 5.00
    WHERE salle = 'Nord';
    
    UPDATE projection
    SET tarif = 8.00
    WHERE salle = 'Centrale';
    
    UPDATE projection
    SET tarif = 6.50
    WHERE salle = 'Sud';

  2. Une erreur s’est glissée dans la table projection : l’horaire de 13h10 est à remplacer par 19h30 pour la salle sud.

    Réponse
    UPDATE Projection
    SET seance = '19h30'
    WHERE seance = '13h10'
        AND salle = "Sud";

  3. Mettre à jour la table projection en ajoutant 1 € au prix des films dont les séances sont à 14h50 et 2 € au prix des films dont les séances sont à 19h30.

    Réponse
    UPDATE Projection
    SET tarif = tarif + 1
    WHERE seance = '14h50';
    
    UPDATE Projection
    SET tarif = tarif + 2
    WHERE seance = '19h30';

  4. Mettre à jour la table projection en augmentant de 10% le tarif des films projectés le 18/11.

    Réponse
    UPDATE projection
    SET tarif = tarif * 1.1
    WHERE date = '2005/11/18';

  5. Mettre à jour la table projection en augmentant de 25% le tarif des films n°10 et n°50.

    Réponse
    UPDATE projection
    SET tarif = tarif * 1.25
    WHERE numfilm = '10'
    OR numfilm = '50';

  6. Mettre à jour la table film en ajoutant les films suivants :

    • 89, Les fourmis de l’espace (documentaire), produit par UGC, 130 minutes.
    • 90, Jet Set (comédie), produit par Paramounth, 100 minutes.
      Réponse
      INSERT INTO film
      VALUES ('89','Les fourmis de l’espace','130','UGC','DOCU');
      INSERT INTO film
      VALUES ('90','Jet Set','100','Paramounth','COMD');
  7. Effacer les enregistrements en date du 17-11-2005.

    Réponse
    DELETE FROM projection
    WHERE date = '2005/11/17';