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.

1

.tables

1
2
3

pragma table_info('film');
pragma table_info('projection');
pragma table_info('categorie');

2. Afficher le contenu de chacune des relations.

1
2
3
4
5
6
7
8

SELECT *
FROM film;

SELECT *
FROM projection;

SELECT *
FROM categorie;

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

1
2

SELECT codecat, libcat
FROM categorie;

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

1
2
3

SELECT DISTINCT salle
FROM projection
ORDER BY salle;

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

1
2
3

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

1
2
3

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

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

1
2
3

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

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

1
2
3

SELECT titre, duree
FROM film
WHERE numfilm = '20';

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

1
2
3

SELECT titre, (duree/60)
FROM film
WHERE numfilm = '20';

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

1
2
3
4
5

SELECT titre
FROM film, projection
WHERE film.numfilm = projection.numfilm
AND salle = 'Nord'
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) ?

1
2

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

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

1
2
3
4
5

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) ?

1
2
3
4
5
6

SELECT libcat, COUNT(numfilm) AS 'Nbre films'
FROM film f, categorie c
WHERE f.codecat = c.codecat
AND c.libcat = (SELECT libcat 
                FROM categorie
                WHERE libcat = "documentaire");

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

1
2
3
4

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

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

1
2
3
4
5

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) ?

1
2
3
4

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 ?

1
2
3
4

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 ?

1
2
3
4
5

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

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

1
2

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 ?

1
2
3

SELECT MIN(seance)
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.

1
2
3
4
5

SELECT numfilm, titre
FROM film
WHERE duree = (SELECT duree
               FROM film
               WHERE numfilm='71');

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

1
2
3
4
5

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 ?

1
2
3
4

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

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

1
2
3
4

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

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

1
2
3
4

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>) ?

1
2
3

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 ?

27. Quels sont les trois films qui sont les plus diffusés ? cc) Quel est le film le plus diffusé ?

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

1

ALTER TABLE projection ADD tarif float;

28. 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.

29. 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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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';

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

1
2
3
4

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

31. Mettre à jour la table projection en majorant de 1 € les films dont les séances sont à 14h50 ; de 2 € pour les séances à 19h30.

1
2
3
4
5
6
7

UPDATE Projection
SET tarif = tarif + 1
WHERE seance = '14h50';

UPDATE Projection
SET tarif = tarif + 2
WHERE seance = '19h30';

32. Mettre à jour la table projection en majorant le 10% les films en projection le 18/11.

1
2
3

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

33. Mettre à jour la table projection en majorant de 25% les films n°10 et n°50.

1
2
3
4

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

34. 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.

1
2
3
4

INSERT INTO film
VALUES ('89','Les fourmis de l’espace','130','UGC','DOCU');
INSERT INTO film
VALUES ('90','Jet Set','100','Paramounth','COMD');

35. Effacer les enregistrements en date du 17-11-2005.

1
2

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