Requêtes SQL

A partir du modèle de données crée par les commandes SQL :

CREATE TABLE personnes ( personne_id INTEGER PRIMARY KEY AUTOINCREMENT,
                         nom         VARCHAR(20),
                         prenom      VARCHAR(20),
                         pays        VARCHAR(20) DEFAULT  'France' );


CREATE TABLE films ( film_id   INTEGER PRIMARY KEY AUTOINCREMENT,
                     realisateur_id INTEGER NOT NULL,
                     titre       VARCHAR(50),
                     genre       VARCHAR(10),
                     annee       INTEGER,
                     FOREIGN KEY (realisateur_id)  REFERENCES personnes(personne_id) );


CREATE TABLE roles  ( film_id   INTEGER NOT NULL,
                      personne_id INTEGER NOT NULL,
                      role      VARCHAR(20),
                      PRIMARY KEY (film_id,personne_id),
                      FOREIGN KEY (personne_id)  REFERENCES personnes,
                      FOREIGN KEY (film_id) REFERENCES films);

1. Rechercher le titre des films de la base de données
   • Question
   • Solution

   Ecrire la requête SQL correspondante.

   Votre réponse :

   sql : films.sql

   DROP TABLE IF EXISTS personnes; DROP TABLE IF EXISTS films; DROP TABLE IF EXISTS roles; DROP TABLE IF EXISTS cinemas; DROP TABLE IF EXISTS projections; CREATE TABLE personnes ( personne_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), prenom VARCHAR(20), pays VARCHAR(20) DEFAULT 'France' ); CREATE TABLE films ( film_id INTEGER PRIMARY KEY AUTOINCREMENT, realisateur_id INTEGER NOT NULL, titre VARCHAR(50), genre VARCHAR(10), annee INTEGER, FOREIGN KEY (realisateur_id) REFERENCES personnes(personne_id) ); CREATE TABLE roles ( film_id INTEGER NOT NULL, acteur_id INTEGER NOT NULL, role VARCHAR(20), PRIMARY KEY (film_id,acteur_id), FOREIGN KEY (acteur_id) REFERENCES personnes, FOREIGN KEY (film_id) REFERENCES films); /* CREATE TABLE cinemas ( cine_id INTEGER NOT NULL, nom VARCHAR(20), adresse VARCHAR(100), PRIMARY KEY (cine_id) ); CREATE TABLE projections ( id_cinema INTEGER NOT NULL, id_film INTEGER NOT NULL, jour DATE DEFAULT CURRENT_DATE, PRIMARY KEY (id_cinema, id_film), FOREIGN KEY (id_cinema) REFERENCES cinemas, FOREIGN KEY (id_film) REFERENCES films ); */ INSERT INTO personnes(nom,prenom) VALUES ('De Niro', 'Robert'); INSERT INTO personnes(nom,prenom) VALUES ('Scorsese', 'Martin'); INSERT INTO personnes(nom,prenom) VALUES ('Eastwood', 'Clint'); INSERT INTO personnes(nom,prenom) VALUES ('Leone', 'Sergio'); INSERT INTO personnes(nom,prenom) VALUES ('Freeman', 'Morgan'); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (2, 'Taxi Driver', 'Drame',1976); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (3, 'Impitoyable', 'Western',1992); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (4, 'Pour une poignée de dollars', 'Western',1964); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,1, 'Travis'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,2, 'client'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,3, 'Munny'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (3,3, 'Logan'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,5, 'Logan');

   >

   Output

   Interpréteur

   Menu	Action
   	Ré-initialiser les sorties
   	Faire apparaître le menu d'aide
   	Interpréter le programme

   Editeur

   Menu	Raccouci	Action
   	Ctrl+N	Initialiser l'éditeur
   	Ctrl+O	Charger le contenu d'un fichier dans l'éditeur
   	Ctrl+S	Sauvegarder le contenu de l'éditeur dans un fichier
   	Ctrl+P	Imprimer le contenu de l'éditeur
   	Ctrl+Z	Annuler la dernière modification
   	Maj+Ctrl+Z	Rétablir la modification précedente
   	Ctrl+F	Chercher une expression dans l'éditeur
   	Maj+Ctrl+F	Chercher et remplacer une expression par une autre
   	F10	Ouvrir une documentation du langage

   
   

   Raccourci	Action
   F1	Afficher cette aide
   Tab	Indenter la sélection
   Maj+Tab	Désindenter la sélection
   Ctrl+A	Sélectionner le contenu de l'éditeur
   Ctrl+C	Copier la sélection dans le presse-papier
   Ctrl+V	Remplacer la sélection par le contenu du presse-papier
   Ctrl+X	Supprimer la sélection et la copier dans le presse-papier
   Maj+Ctrl+R	Chercher et remplacer une expression par une autre dans tout l'éditeur

   Une solution possible :

   SELECT titre FROM films;
   
2. Rechercher les films de genre 'Drame' réalisés après 1975
   • Question
   • Solution

   Ecrire la requête SQL correspondante.

   Votre réponse :

   sql : films.sql

   DROP TABLE IF EXISTS personnes; DROP TABLE IF EXISTS films; DROP TABLE IF EXISTS roles; DROP TABLE IF EXISTS cinemas; DROP TABLE IF EXISTS projections; CREATE TABLE personnes ( personne_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), prenom VARCHAR(20), pays VARCHAR(20) DEFAULT 'France' ); CREATE TABLE films ( film_id INTEGER PRIMARY KEY AUTOINCREMENT, realisateur_id INTEGER NOT NULL, titre VARCHAR(50), genre VARCHAR(10), annee INTEGER, FOREIGN KEY (realisateur_id) REFERENCES personnes(personne_id) ); CREATE TABLE roles ( film_id INTEGER NOT NULL, acteur_id INTEGER NOT NULL, role VARCHAR(20), PRIMARY KEY (film_id,acteur_id), FOREIGN KEY (acteur_id) REFERENCES personnes, FOREIGN KEY (film_id) REFERENCES films); /* CREATE TABLE cinemas ( cine_id INTEGER NOT NULL, nom VARCHAR(20), adresse VARCHAR(100), PRIMARY KEY (cine_id) ); CREATE TABLE projections ( id_cinema INTEGER NOT NULL, id_film INTEGER NOT NULL, jour DATE DEFAULT CURRENT_DATE, PRIMARY KEY (id_cinema, id_film), FOREIGN KEY (id_cinema) REFERENCES cinemas, FOREIGN KEY (id_film) REFERENCES films ); */ INSERT INTO personnes(nom,prenom) VALUES ('De Niro', 'Robert'); INSERT INTO personnes(nom,prenom) VALUES ('Scorsese', 'Martin'); INSERT INTO personnes(nom,prenom) VALUES ('Eastwood', 'Clint'); INSERT INTO personnes(nom,prenom) VALUES ('Leone', 'Sergio'); INSERT INTO personnes(nom,prenom) VALUES ('Freeman', 'Morgan'); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (2, 'Taxi Driver', 'Drame',1976); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (3, 'Impitoyable', 'Western',1992); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (4, 'Pour une poignée de dollars', 'Western',1964); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,1, 'Travis'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,2, 'client'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,3, 'Munny'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (3,3, 'Logan'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,5, 'Logan');

   >

   Output

   Interpréteur

   Menu	Action
   	Ré-initialiser les sorties
   	Faire apparaître le menu d'aide
   	Interpréter le programme

   Editeur

   Menu	Raccouci	Action
   	Ctrl+N	Initialiser l'éditeur
   	Ctrl+O	Charger le contenu d'un fichier dans l'éditeur
   	Ctrl+S	Sauvegarder le contenu de l'éditeur dans un fichier
   	Ctrl+P	Imprimer le contenu de l'éditeur
   	Ctrl+Z	Annuler la dernière modification
   	Maj+Ctrl+Z	Rétablir la modification précedente
   	Ctrl+F	Chercher une expression dans l'éditeur
   	Maj+Ctrl+F	Chercher et remplacer une expression par une autre
   	F10	Ouvrir une documentation du langage

   
   

   Raccourci	Action
   F1	Afficher cette aide
   Tab	Indenter la sélection
   Maj+Tab	Désindenter la sélection
   Ctrl+A	Sélectionner le contenu de l'éditeur
   Ctrl+C	Copier la sélection dans le presse-papier
   Ctrl+V	Remplacer la sélection par le contenu du presse-papier
   Ctrl+X	Supprimer la sélection et la copier dans le presse-papier
   Maj+Ctrl+R	Chercher et remplacer une expression par une autre dans tout l'éditeur

   Une solution possible :

   SELECT *
   FROM films
   WHERE annee>=1975  AND genre='Drame';
   
3. Rechercher les nom,prénom des acteurs qui ont joué dans 'Taxi Driver'
   • Question
   • Solution

   Ecrire la requête SQL correspondante.

   Votre réponse :

   sql : films.sql

   DROP TABLE IF EXISTS personnes; DROP TABLE IF EXISTS films; DROP TABLE IF EXISTS roles; DROP TABLE IF EXISTS cinemas; DROP TABLE IF EXISTS projections; CREATE TABLE personnes ( personne_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), prenom VARCHAR(20), pays VARCHAR(20) DEFAULT 'France' ); CREATE TABLE films ( film_id INTEGER PRIMARY KEY AUTOINCREMENT, realisateur_id INTEGER NOT NULL, titre VARCHAR(50), genre VARCHAR(10), annee INTEGER, FOREIGN KEY (realisateur_id) REFERENCES personnes(personne_id) ); CREATE TABLE roles ( film_id INTEGER NOT NULL, acteur_id INTEGER NOT NULL, role VARCHAR(20), PRIMARY KEY (film_id,acteur_id), FOREIGN KEY (acteur_id) REFERENCES personnes, FOREIGN KEY (film_id) REFERENCES films); /* CREATE TABLE cinemas ( cine_id INTEGER NOT NULL, nom VARCHAR(20), adresse VARCHAR(100), PRIMARY KEY (cine_id) ); CREATE TABLE projections ( id_cinema INTEGER NOT NULL, id_film INTEGER NOT NULL, jour DATE DEFAULT CURRENT_DATE, PRIMARY KEY (id_cinema, id_film), FOREIGN KEY (id_cinema) REFERENCES cinemas, FOREIGN KEY (id_film) REFERENCES films ); */ INSERT INTO personnes(nom,prenom) VALUES ('De Niro', 'Robert'); INSERT INTO personnes(nom,prenom) VALUES ('Scorsese', 'Martin'); INSERT INTO personnes(nom,prenom) VALUES ('Eastwood', 'Clint'); INSERT INTO personnes(nom,prenom) VALUES ('Leone', 'Sergio'); INSERT INTO personnes(nom,prenom) VALUES ('Freeman', 'Morgan'); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (2, 'Taxi Driver', 'Drame',1976); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (3, 'Impitoyable', 'Western',1992); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (4, 'Pour une poignée de dollars', 'Western',1964); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,1, 'Travis'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,2, 'client'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,3, 'Munny'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (3,3, 'Logan'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,5, 'Logan');

   >

   Output

   Interpréteur

   Menu	Action
   	Ré-initialiser les sorties
   	Faire apparaître le menu d'aide
   	Interpréter le programme

   Editeur

   Menu	Raccouci	Action
   	Ctrl+N	Initialiser l'éditeur
   	Ctrl+O	Charger le contenu d'un fichier dans l'éditeur
   	Ctrl+S	Sauvegarder le contenu de l'éditeur dans un fichier
   	Ctrl+P	Imprimer le contenu de l'éditeur
   	Ctrl+Z	Annuler la dernière modification
   	Maj+Ctrl+Z	Rétablir la modification précedente
   	Ctrl+F	Chercher une expression dans l'éditeur
   	Maj+Ctrl+F	Chercher et remplacer une expression par une autre
   	F10	Ouvrir une documentation du langage

   
   

   Raccourci	Action
   F1	Afficher cette aide
   Tab	Indenter la sélection
   Maj+Tab	Désindenter la sélection
   Ctrl+A	Sélectionner le contenu de l'éditeur
   Ctrl+C	Copier la sélection dans le presse-papier
   Ctrl+V	Remplacer la sélection par le contenu du presse-papier
   Ctrl+X	Supprimer la sélection et la copier dans le presse-papier
   Maj+Ctrl+R	Chercher et remplacer une expression par une autre dans tout l'éditeur

   Une solution possible :

   SELECT nom, prenom
   FROM films NATURAL JOIN roles, personnes
   WHERE personne_id=acteur_id AND films.titre='Taxi Driver';
   
4. Quels sont les noms et prénoms des acteurs qui sont également réalisateurs ?
   • Question
   • Solution

   Ecrire la requête SQL correspondante.

   Votre réponse :

   sql : films.sql

   DROP TABLE IF EXISTS personnes; DROP TABLE IF EXISTS films; DROP TABLE IF EXISTS roles; DROP TABLE IF EXISTS cinemas; DROP TABLE IF EXISTS projections; CREATE TABLE personnes ( personne_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), prenom VARCHAR(20), pays VARCHAR(20) DEFAULT 'France' ); CREATE TABLE films ( film_id INTEGER PRIMARY KEY AUTOINCREMENT, realisateur_id INTEGER NOT NULL, titre VARCHAR(50), genre VARCHAR(10), annee INTEGER, FOREIGN KEY (realisateur_id) REFERENCES personnes(personne_id) ); CREATE TABLE roles ( film_id INTEGER NOT NULL, acteur_id INTEGER NOT NULL, role VARCHAR(20), PRIMARY KEY (film_id,acteur_id), FOREIGN KEY (acteur_id) REFERENCES personnes, FOREIGN KEY (film_id) REFERENCES films); /* CREATE TABLE cinemas ( cine_id INTEGER NOT NULL, nom VARCHAR(20), adresse VARCHAR(100), PRIMARY KEY (cine_id) ); CREATE TABLE projections ( id_cinema INTEGER NOT NULL, id_film INTEGER NOT NULL, jour DATE DEFAULT CURRENT_DATE, PRIMARY KEY (id_cinema, id_film), FOREIGN KEY (id_cinema) REFERENCES cinemas, FOREIGN KEY (id_film) REFERENCES films ); */ INSERT INTO personnes(nom,prenom) VALUES ('De Niro', 'Robert'); INSERT INTO personnes(nom,prenom) VALUES ('Scorsese', 'Martin'); INSERT INTO personnes(nom,prenom) VALUES ('Eastwood', 'Clint'); INSERT INTO personnes(nom,prenom) VALUES ('Leone', 'Sergio'); INSERT INTO personnes(nom,prenom) VALUES ('Freeman', 'Morgan'); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (2, 'Taxi Driver', 'Drame',1976); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (3, 'Impitoyable', 'Western',1992); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (4, 'Pour une poignée de dollars', 'Western',1964); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,1, 'Travis'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,2, 'client'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,3, 'Munny'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (3,3, 'Logan'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,5, 'Logan');

   >

   Output

   Interpréteur

   Menu	Action
   	Ré-initialiser les sorties
   	Faire apparaître le menu d'aide
   	Interpréter le programme

   Editeur

   Menu	Raccouci	Action
   	Ctrl+N	Initialiser l'éditeur
   	Ctrl+O	Charger le contenu d'un fichier dans l'éditeur
   	Ctrl+S	Sauvegarder le contenu de l'éditeur dans un fichier
   	Ctrl+P	Imprimer le contenu de l'éditeur
   	Ctrl+Z	Annuler la dernière modification
   	Maj+Ctrl+Z	Rétablir la modification précedente
   	Ctrl+F	Chercher une expression dans l'éditeur
   	Maj+Ctrl+F	Chercher et remplacer une expression par une autre
   	F10	Ouvrir une documentation du langage

   
   

   Raccourci	Action
   F1	Afficher cette aide
   Tab	Indenter la sélection
   Maj+Tab	Désindenter la sélection
   Ctrl+A	Sélectionner le contenu de l'éditeur
   Ctrl+C	Copier la sélection dans le presse-papier
   Ctrl+V	Remplacer la sélection par le contenu du presse-papier
   Ctrl+X	Supprimer la sélection et la copier dans le presse-papier
   Maj+Ctrl+R	Chercher et remplacer une expression par une autre dans tout l'éditeur

   Une solution possible :

   SELECT DISTINCT nom, prenom
   FROM personnes, roles
   WHERE personne_id=acteur_id
     AND acteur_id IN (
                       SELECT  realisateur_id
                       FROM  films
                      );
   
5. Donner par ordre alphabétique les noms d'acteur, leur rôle, le titre du film correspondant pour les films de 'Martin' 'Scorsese'
   • Question
   • Solution

   Ecrire la requête SQL correspondante.

   Votre réponse :

   sql : films.sql

   DROP TABLE IF EXISTS personnes; DROP TABLE IF EXISTS films; DROP TABLE IF EXISTS roles; DROP TABLE IF EXISTS cinemas; DROP TABLE IF EXISTS projections; CREATE TABLE personnes ( personne_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), prenom VARCHAR(20), pays VARCHAR(20) DEFAULT 'France' ); CREATE TABLE films ( film_id INTEGER PRIMARY KEY AUTOINCREMENT, realisateur_id INTEGER NOT NULL, titre VARCHAR(50), genre VARCHAR(10), annee INTEGER, FOREIGN KEY (realisateur_id) REFERENCES personnes(personne_id) ); CREATE TABLE roles ( film_id INTEGER NOT NULL, acteur_id INTEGER NOT NULL, role VARCHAR(20), PRIMARY KEY (film_id,acteur_id), FOREIGN KEY (acteur_id) REFERENCES personnes, FOREIGN KEY (film_id) REFERENCES films); /* CREATE TABLE cinemas ( cine_id INTEGER NOT NULL, nom VARCHAR(20), adresse VARCHAR(100), PRIMARY KEY (cine_id) ); CREATE TABLE projections ( id_cinema INTEGER NOT NULL, id_film INTEGER NOT NULL, jour DATE DEFAULT CURRENT_DATE, PRIMARY KEY (id_cinema, id_film), FOREIGN KEY (id_cinema) REFERENCES cinemas, FOREIGN KEY (id_film) REFERENCES films ); */ INSERT INTO personnes(nom,prenom) VALUES ('De Niro', 'Robert'); INSERT INTO personnes(nom,prenom) VALUES ('Scorsese', 'Martin'); INSERT INTO personnes(nom,prenom) VALUES ('Eastwood', 'Clint'); INSERT INTO personnes(nom,prenom) VALUES ('Leone', 'Sergio'); INSERT INTO personnes(nom,prenom) VALUES ('Freeman', 'Morgan'); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (2, 'Taxi Driver', 'Drame',1976); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (3, 'Impitoyable', 'Western',1992); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (4, 'Pour une poignée de dollars', 'Western',1964); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,1, 'Travis'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,2, 'client'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,3, 'Munny'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (3,3, 'Logan'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,5, 'Logan');

   >

   Output

   Interpréteur

   Menu	Action
   	Ré-initialiser les sorties
   	Faire apparaître le menu d'aide
   	Interpréter le programme

   Editeur

   Menu	Raccouci	Action
   	Ctrl+N	Initialiser l'éditeur
   	Ctrl+O	Charger le contenu d'un fichier dans l'éditeur
   	Ctrl+S	Sauvegarder le contenu de l'éditeur dans un fichier
   	Ctrl+P	Imprimer le contenu de l'éditeur
   	Ctrl+Z	Annuler la dernière modification
   	Maj+Ctrl+Z	Rétablir la modification précedente
   	Ctrl+F	Chercher une expression dans l'éditeur
   	Maj+Ctrl+F	Chercher et remplacer une expression par une autre
   	F10	Ouvrir une documentation du langage

   
   

   Raccourci	Action
   F1	Afficher cette aide
   Tab	Indenter la sélection
   Maj+Tab	Désindenter la sélection
   Ctrl+A	Sélectionner le contenu de l'éditeur
   Ctrl+C	Copier la sélection dans le presse-papier
   Ctrl+V	Remplacer la sélection par le contenu du presse-papier
   Ctrl+X	Supprimer la sélection et la copier dans le presse-papier
   Maj+Ctrl+R	Chercher et remplacer une expression par une autre dans tout l'éditeur

   Une solution possible :

   SELECT nom, role, titre
   FROM personnes, roles NATURAL JOIN films
   WHERE personne_id=acteur_id
     AND realisateur_id IN (
                            SELECT personne_id
                            FROM personnes
                            WHERE nom='Scorsese' AND prenom='Martin'
                           )
   ORDER BY nom;
   
6. Rechercher les nom,prenom des réalisateurs ayant réalisé au moins un film de même genre que l'un des films de Sergio Leone. On ne fera pas apparaître Sergio Leone dans le résultat.
   • Question
   • Solution

   Ecrire la requête SQL correspondante.

   Votre réponse :

   sql : films.sql

   DROP TABLE IF EXISTS personnes; DROP TABLE IF EXISTS films; DROP TABLE IF EXISTS roles; DROP TABLE IF EXISTS cinemas; DROP TABLE IF EXISTS projections; CREATE TABLE personnes ( personne_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), prenom VARCHAR(20), pays VARCHAR(20) DEFAULT 'France' ); CREATE TABLE films ( film_id INTEGER PRIMARY KEY AUTOINCREMENT, realisateur_id INTEGER NOT NULL, titre VARCHAR(50), genre VARCHAR(10), annee INTEGER, FOREIGN KEY (realisateur_id) REFERENCES personnes(personne_id) ); CREATE TABLE roles ( film_id INTEGER NOT NULL, acteur_id INTEGER NOT NULL, role VARCHAR(20), PRIMARY KEY (film_id,acteur_id), FOREIGN KEY (acteur_id) REFERENCES personnes, FOREIGN KEY (film_id) REFERENCES films); /* CREATE TABLE cinemas ( cine_id INTEGER NOT NULL, nom VARCHAR(20), adresse VARCHAR(100), PRIMARY KEY (cine_id) ); CREATE TABLE projections ( id_cinema INTEGER NOT NULL, id_film INTEGER NOT NULL, jour DATE DEFAULT CURRENT_DATE, PRIMARY KEY (id_cinema, id_film), FOREIGN KEY (id_cinema) REFERENCES cinemas, FOREIGN KEY (id_film) REFERENCES films ); */ INSERT INTO personnes(nom,prenom) VALUES ('De Niro', 'Robert'); INSERT INTO personnes(nom,prenom) VALUES ('Scorsese', 'Martin'); INSERT INTO personnes(nom,prenom) VALUES ('Eastwood', 'Clint'); INSERT INTO personnes(nom,prenom) VALUES ('Leone', 'Sergio'); INSERT INTO personnes(nom,prenom) VALUES ('Freeman', 'Morgan'); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (2, 'Taxi Driver', 'Drame',1976); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (3, 'Impitoyable', 'Western',1992); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (4, 'Pour une poignée de dollars', 'Western',1964); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,1, 'Travis'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,2, 'client'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,3, 'Munny'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (3,3, 'Logan'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,5, 'Logan');

   >

   Output

   Interpréteur

   Menu	Action
   	Ré-initialiser les sorties
   	Faire apparaître le menu d'aide
   	Interpréter le programme

   Editeur

   Menu	Raccouci	Action
   	Ctrl+N	Initialiser l'éditeur
   	Ctrl+O	Charger le contenu d'un fichier dans l'éditeur
   	Ctrl+S	Sauvegarder le contenu de l'éditeur dans un fichier
   	Ctrl+P	Imprimer le contenu de l'éditeur
   	Ctrl+Z	Annuler la dernière modification
   	Maj+Ctrl+Z	Rétablir la modification précedente
   	Ctrl+F	Chercher une expression dans l'éditeur
   	Maj+Ctrl+F	Chercher et remplacer une expression par une autre
   	F10	Ouvrir une documentation du langage

   
   

   Raccourci	Action
   F1	Afficher cette aide
   Tab	Indenter la sélection
   Maj+Tab	Désindenter la sélection
   Ctrl+A	Sélectionner le contenu de l'éditeur
   Ctrl+C	Copier la sélection dans le presse-papier
   Ctrl+V	Remplacer la sélection par le contenu du presse-papier
   Ctrl+X	Supprimer la sélection et la copier dans le presse-papier
   Maj+Ctrl+R	Chercher et remplacer une expression par une autre dans tout l'éditeur

   Une solution possible :

   SELECT nom,prenom
   FROM films, personnes
   WHERE personne_id=realisateur_id
     AND nom <> 'Leone'
     AND genre IN (SELECT genre
                   FROM films, personnes
                   WHERE  personne_id=realisateur_id
                       AND nom='Leone' AND prenom='Sergio');
   
7. Donner le nombre de films par genre à partir des annees 60
   • Question
   • Solution

   Ecrire la requête SQL correspondante.

   Votre réponse :

   sql : films.sql

   DROP TABLE IF EXISTS personnes; DROP TABLE IF EXISTS films; DROP TABLE IF EXISTS roles; DROP TABLE IF EXISTS cinemas; DROP TABLE IF EXISTS projections; CREATE TABLE personnes ( personne_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), prenom VARCHAR(20), pays VARCHAR(20) DEFAULT 'France' ); CREATE TABLE films ( film_id INTEGER PRIMARY KEY AUTOINCREMENT, realisateur_id INTEGER NOT NULL, titre VARCHAR(50), genre VARCHAR(10), annee INTEGER, FOREIGN KEY (realisateur_id) REFERENCES personnes(personne_id) ); CREATE TABLE roles ( film_id INTEGER NOT NULL, acteur_id INTEGER NOT NULL, role VARCHAR(20), PRIMARY KEY (film_id,acteur_id), FOREIGN KEY (acteur_id) REFERENCES personnes, FOREIGN KEY (film_id) REFERENCES films); /* CREATE TABLE cinemas ( cine_id INTEGER NOT NULL, nom VARCHAR(20), adresse VARCHAR(100), PRIMARY KEY (cine_id) ); CREATE TABLE projections ( id_cinema INTEGER NOT NULL, id_film INTEGER NOT NULL, jour DATE DEFAULT CURRENT_DATE, PRIMARY KEY (id_cinema, id_film), FOREIGN KEY (id_cinema) REFERENCES cinemas, FOREIGN KEY (id_film) REFERENCES films ); */ INSERT INTO personnes(nom,prenom) VALUES ('De Niro', 'Robert'); INSERT INTO personnes(nom,prenom) VALUES ('Scorsese', 'Martin'); INSERT INTO personnes(nom,prenom) VALUES ('Eastwood', 'Clint'); INSERT INTO personnes(nom,prenom) VALUES ('Leone', 'Sergio'); INSERT INTO personnes(nom,prenom) VALUES ('Freeman', 'Morgan'); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (2, 'Taxi Driver', 'Drame',1976); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (3, 'Impitoyable', 'Western',1992); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (4, 'Pour une poignée de dollars', 'Western',1964); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,1, 'Travis'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,2, 'client'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,3, 'Munny'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (3,3, 'Logan'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,5, 'Logan');

   >

   Output

   Interpréteur

   Menu	Action
   	Ré-initialiser les sorties
   	Faire apparaître le menu d'aide
   	Interpréter le programme

   Editeur

   Menu	Raccouci	Action
   	Ctrl+N	Initialiser l'éditeur
   	Ctrl+O	Charger le contenu d'un fichier dans l'éditeur
   	Ctrl+S	Sauvegarder le contenu de l'éditeur dans un fichier
   	Ctrl+P	Imprimer le contenu de l'éditeur
   	Ctrl+Z	Annuler la dernière modification
   	Maj+Ctrl+Z	Rétablir la modification précedente
   	Ctrl+F	Chercher une expression dans l'éditeur
   	Maj+Ctrl+F	Chercher et remplacer une expression par une autre
   	F10	Ouvrir une documentation du langage

   
   

   Raccourci	Action
   F1	Afficher cette aide
   Tab	Indenter la sélection
   Maj+Tab	Désindenter la sélection
   Ctrl+A	Sélectionner le contenu de l'éditeur
   Ctrl+C	Copier la sélection dans le presse-papier
   Ctrl+V	Remplacer la sélection par le contenu du presse-papier
   Ctrl+X	Supprimer la sélection et la copier dans le presse-papier
   Maj+Ctrl+R	Chercher et remplacer une expression par une autre dans tout l'éditeur

   Une solution possible :

   SELECT genre, COUNT (*)
   FROM films
   WHERE annee >= 1960
   GROUP BY genre;
   
8. Titre des films et nombre d'acteurs pour les films pour les films de Martin Scorsese qui ont au moins 2 acteurs
   • Question
   • Solution

   Ecrire la requête SQL correspondante.

   Votre réponse :

   sql : films.sql

   DROP TABLE IF EXISTS personnes; DROP TABLE IF EXISTS films; DROP TABLE IF EXISTS roles; DROP TABLE IF EXISTS cinemas; DROP TABLE IF EXISTS projections; CREATE TABLE personnes ( personne_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), prenom VARCHAR(20), pays VARCHAR(20) DEFAULT 'France' ); CREATE TABLE films ( film_id INTEGER PRIMARY KEY AUTOINCREMENT, realisateur_id INTEGER NOT NULL, titre VARCHAR(50), genre VARCHAR(10), annee INTEGER, FOREIGN KEY (realisateur_id) REFERENCES personnes(personne_id) ); CREATE TABLE roles ( film_id INTEGER NOT NULL, acteur_id INTEGER NOT NULL, role VARCHAR(20), PRIMARY KEY (film_id,acteur_id), FOREIGN KEY (acteur_id) REFERENCES personnes, FOREIGN KEY (film_id) REFERENCES films); /* CREATE TABLE cinemas ( cine_id INTEGER NOT NULL, nom VARCHAR(20), adresse VARCHAR(100), PRIMARY KEY (cine_id) ); CREATE TABLE projections ( id_cinema INTEGER NOT NULL, id_film INTEGER NOT NULL, jour DATE DEFAULT CURRENT_DATE, PRIMARY KEY (id_cinema, id_film), FOREIGN KEY (id_cinema) REFERENCES cinemas, FOREIGN KEY (id_film) REFERENCES films ); */ INSERT INTO personnes(nom,prenom) VALUES ('De Niro', 'Robert'); INSERT INTO personnes(nom,prenom) VALUES ('Scorsese', 'Martin'); INSERT INTO personnes(nom,prenom) VALUES ('Eastwood', 'Clint'); INSERT INTO personnes(nom,prenom) VALUES ('Leone', 'Sergio'); INSERT INTO personnes(nom,prenom) VALUES ('Freeman', 'Morgan'); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (2, 'Taxi Driver', 'Drame',1976); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (3, 'Impitoyable', 'Western',1992); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (4, 'Pour une poignée de dollars', 'Western',1964); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,1, 'Travis'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,2, 'client'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,3, 'Munny'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (3,3, 'Logan'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,5, 'Logan');

   >

   Output

   Interpréteur

   Menu	Action
   	Ré-initialiser les sorties
   	Faire apparaître le menu d'aide
   	Interpréter le programme

   Editeur

   Menu	Raccouci	Action
   	Ctrl+N	Initialiser l'éditeur
   	Ctrl+O	Charger le contenu d'un fichier dans l'éditeur
   	Ctrl+S	Sauvegarder le contenu de l'éditeur dans un fichier
   	Ctrl+P	Imprimer le contenu de l'éditeur
   	Ctrl+Z	Annuler la dernière modification
   	Maj+Ctrl+Z	Rétablir la modification précedente
   	Ctrl+F	Chercher une expression dans l'éditeur
   	Maj+Ctrl+F	Chercher et remplacer une expression par une autre
   	F10	Ouvrir une documentation du langage

   
   

   Raccourci	Action
   F1	Afficher cette aide
   Tab	Indenter la sélection
   Maj+Tab	Désindenter la sélection
   Ctrl+A	Sélectionner le contenu de l'éditeur
   Ctrl+C	Copier la sélection dans le presse-papier
   Ctrl+V	Remplacer la sélection par le contenu du presse-papier
   Ctrl+X	Supprimer la sélection et la copier dans le presse-papier
   Maj+Ctrl+R	Chercher et remplacer une expression par une autre dans tout l'éditeur

   Une solution possible :

   SELECT titre, COUNT(acteur_id)
   FROM films NATURAL JOIN roles,personnes
   WHERE personne_id=realisateur_id AND nom='Scorsese' AND prenom='Martin'
   GROUP BY film_id, titre HAVING COUNT(*)>1;
   
9. Quels sont les acteurs qui n'ont pas joué dans des 'Western' ?
   • Question
   • Solution

   Ecrire la requête SQL correspondante.

   Votre réponse :

   sql : films.sql

   DROP TABLE IF EXISTS personnes; DROP TABLE IF EXISTS films; DROP TABLE IF EXISTS roles; DROP TABLE IF EXISTS cinemas; DROP TABLE IF EXISTS projections; CREATE TABLE personnes ( personne_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), prenom VARCHAR(20), pays VARCHAR(20) DEFAULT 'France' ); CREATE TABLE films ( film_id INTEGER PRIMARY KEY AUTOINCREMENT, realisateur_id INTEGER NOT NULL, titre VARCHAR(50), genre VARCHAR(10), annee INTEGER, FOREIGN KEY (realisateur_id) REFERENCES personnes(personne_id) ); CREATE TABLE roles ( film_id INTEGER NOT NULL, acteur_id INTEGER NOT NULL, role VARCHAR(20), PRIMARY KEY (film_id,acteur_id), FOREIGN KEY (acteur_id) REFERENCES personnes, FOREIGN KEY (film_id) REFERENCES films); /* CREATE TABLE cinemas ( cine_id INTEGER NOT NULL, nom VARCHAR(20), adresse VARCHAR(100), PRIMARY KEY (cine_id) ); CREATE TABLE projections ( id_cinema INTEGER NOT NULL, id_film INTEGER NOT NULL, jour DATE DEFAULT CURRENT_DATE, PRIMARY KEY (id_cinema, id_film), FOREIGN KEY (id_cinema) REFERENCES cinemas, FOREIGN KEY (id_film) REFERENCES films ); */ INSERT INTO personnes(nom,prenom) VALUES ('De Niro', 'Robert'); INSERT INTO personnes(nom,prenom) VALUES ('Scorsese', 'Martin'); INSERT INTO personnes(nom,prenom) VALUES ('Eastwood', 'Clint'); INSERT INTO personnes(nom,prenom) VALUES ('Leone', 'Sergio'); INSERT INTO personnes(nom,prenom) VALUES ('Freeman', 'Morgan'); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (2, 'Taxi Driver', 'Drame',1976); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (3, 'Impitoyable', 'Western',1992); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (4, 'Pour une poignée de dollars', 'Western',1964); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,1, 'Travis'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,2, 'client'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,3, 'Munny'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (3,3, 'Logan'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,5, 'Logan');

   >

   Output

   Interpréteur

   Menu	Action
   	Ré-initialiser les sorties
   	Faire apparaître le menu d'aide
   	Interpréter le programme

   Editeur

   Menu	Raccouci	Action
   	Ctrl+N	Initialiser l'éditeur
   	Ctrl+O	Charger le contenu d'un fichier dans l'éditeur
   	Ctrl+S	Sauvegarder le contenu de l'éditeur dans un fichier
   	Ctrl+P	Imprimer le contenu de l'éditeur
   	Ctrl+Z	Annuler la dernière modification
   	Maj+Ctrl+Z	Rétablir la modification précedente
   	Ctrl+F	Chercher une expression dans l'éditeur
   	Maj+Ctrl+F	Chercher et remplacer une expression par une autre
   	F10	Ouvrir une documentation du langage

   
   

   Raccourci	Action
   F1	Afficher cette aide
   Tab	Indenter la sélection
   Maj+Tab	Désindenter la sélection
   Ctrl+A	Sélectionner le contenu de l'éditeur
   Ctrl+C	Copier la sélection dans le presse-papier
   Ctrl+V	Remplacer la sélection par le contenu du presse-papier
   Ctrl+X	Supprimer la sélection et la copier dans le presse-papier
   Maj+Ctrl+R	Chercher et remplacer une expression par une autre dans tout l'éditeur

   Une solution possible :

   SELECT nom, prenom
   FROM personnes,roles
   WHERE  personne_id=acteur_id
   EXCEPT
   SELECT nom, prenom
   FROM personnes,roles NATURAL JOIN  films
   WHERE personne_id=acteur_id
     AND genre='Western';
   
10. Quel est le nom,prenom et nombre de films des réalisateurs ayant réalisé au moins un film du même genre que l'un des films réalisés par 'Sergio' 'Leone' ?
    • Question
    • Solution

    Ecrire la requête SQL correspondante.

    Votre réponse :

    sql : films.sql

    DROP TABLE IF EXISTS personnes; DROP TABLE IF EXISTS films; DROP TABLE IF EXISTS roles; DROP TABLE IF EXISTS cinemas; DROP TABLE IF EXISTS projections; CREATE TABLE personnes ( personne_id INTEGER PRIMARY KEY AUTOINCREMENT, nom VARCHAR(20), prenom VARCHAR(20), pays VARCHAR(20) DEFAULT 'France' ); CREATE TABLE films ( film_id INTEGER PRIMARY KEY AUTOINCREMENT, realisateur_id INTEGER NOT NULL, titre VARCHAR(50), genre VARCHAR(10), annee INTEGER, FOREIGN KEY (realisateur_id) REFERENCES personnes(personne_id) ); CREATE TABLE roles ( film_id INTEGER NOT NULL, acteur_id INTEGER NOT NULL, role VARCHAR(20), PRIMARY KEY (film_id,acteur_id), FOREIGN KEY (acteur_id) REFERENCES personnes, FOREIGN KEY (film_id) REFERENCES films); /* CREATE TABLE cinemas ( cine_id INTEGER NOT NULL, nom VARCHAR(20), adresse VARCHAR(100), PRIMARY KEY (cine_id) ); CREATE TABLE projections ( id_cinema INTEGER NOT NULL, id_film INTEGER NOT NULL, jour DATE DEFAULT CURRENT_DATE, PRIMARY KEY (id_cinema, id_film), FOREIGN KEY (id_cinema) REFERENCES cinemas, FOREIGN KEY (id_film) REFERENCES films ); */ INSERT INTO personnes(nom,prenom) VALUES ('De Niro', 'Robert'); INSERT INTO personnes(nom,prenom) VALUES ('Scorsese', 'Martin'); INSERT INTO personnes(nom,prenom) VALUES ('Eastwood', 'Clint'); INSERT INTO personnes(nom,prenom) VALUES ('Leone', 'Sergio'); INSERT INTO personnes(nom,prenom) VALUES ('Freeman', 'Morgan'); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (2, 'Taxi Driver', 'Drame',1976); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (3, 'Impitoyable', 'Western',1992); INSERT INTO films(realisateur_id,titre,genre,annee) VALUES (4, 'Pour une poignée de dollars', 'Western',1964); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,1, 'Travis'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (1,2, 'client'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,3, 'Munny'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (3,3, 'Logan'); INSERT INTO roles(film_id,acteur_id,role ) VALUES (2,5, 'Logan');

    >

    Output

    Interpréteur

    Menu	Action
    	Ré-initialiser les sorties
    	Faire apparaître le menu d'aide
    	Interpréter le programme

    Editeur

    Menu	Raccouci	Action
    	Ctrl+N	Initialiser l'éditeur
    	Ctrl+O	Charger le contenu d'un fichier dans l'éditeur
    	Ctrl+S	Sauvegarder le contenu de l'éditeur dans un fichier
    	Ctrl+P	Imprimer le contenu de l'éditeur
    	Ctrl+Z	Annuler la dernière modification
    	Maj+Ctrl+Z	Rétablir la modification précedente
    	Ctrl+F	Chercher une expression dans l'éditeur
    	Maj+Ctrl+F	Chercher et remplacer une expression par une autre
    	F10	Ouvrir une documentation du langage

    
    

    Raccourci	Action
    F1	Afficher cette aide
    Tab	Indenter la sélection
    Maj+Tab	Désindenter la sélection
    Ctrl+A	Sélectionner le contenu de l'éditeur
    Ctrl+C	Copier la sélection dans le presse-papier
    Ctrl+V	Remplacer la sélection par le contenu du presse-papier
    Ctrl+X	Supprimer la sélection et la copier dans le presse-papier
    Maj+Ctrl+R	Chercher et remplacer une expression par une autre dans tout l'éditeur

    Une solution possible :

    SELECT nom, prenom, count(distinct film_id) AS "Nombre de films"
    FROM personnes, films
    WHERE personne_id=realisateur_id AND nom<>'Leone' AND prenom<>'Sergio'
      AND realisateur_id IN ( SELECT realisateur_id
                              FROM films
                              WHERE genre IN (SELECT genre
                                              FROM personnes, films
                                              WHERE personne_id=realisateur_id
                                                   AND nom='Leone' AND prenom='Sergio'))
    GROUP BY nom, prenom;