Précédent Suivant Index

3   Requêtes SQL

Quand vous vous connectez à la base, vous avez automatiquement accès à la base ``Officiel des spectacles'' dont le schéma a été présenté précédemment. Cette base contient un petit jeu de données plus ou moins réaliste. A vous de jouer : il faut concevoir, saisir et exécuter les ordres SQL correspondant aux requêtes suivantes.

3.1   Sélections simples

  1. Les titres de films triés.


    Solution :

    
SELECT titre 
FROM film
ORDER BY titre;



  2. Nom et année de naissance des artistes nés avant 1950.




    Solution :

    
SELECT nom, annee_naissance
FROM   artiste
WHERE annee_naissance <= 1950;



  3. Les cinémas du 12ème arrondissement.




    Solution :

    
SELECT *
FROM   cinema
WHERE arrondissement = 12;



  4. Les artistes dont le nom commence par 'H' (commande LIKE).




    Solution :

    
SELECT *
FROM   artiste
WHERE  nom LIKE 'H\%';



  5. Quels sont les acteurs dont on ignore la date de naissance ? (Attention : cela signifie que la valeur n'existe pas).




    Solution :

    
SELECT nom, prenom
FROM artiste
WHERE annee_naissance IS NULL;



  6. Combien de fois Bruce Willis a-t-il joué le role de McLane ?




    Solution :

    
SELECT count(*) 
FROM role
WHERE nom_acteur = 'Willis'
AND   nom_role = 'McLane';

3.2   Jointures



  1. Qui a joué Tarzan (nom et prénom) ?




    Solution :

    
SELECT prenom, nom_acteur 
FROM   role, artiste
WHERE nom_role = 'Tarzan'
AND  nom_acteur = nom;



  2. Nom des acteurs de Vertigo.




    Solution :

    
SELECT nom_acteur
FROM   film, role
WHERE  film.titre = 'Vertigo'
AND    film.id_film = role.id_film;



  3. Films dont le réalisateur est Tim Burton, et un des acteurs avec Jonnhy Depp.




    Solution :

    
SELECT titre
FROM   film f, role r
WHERE  f.id_film = r.id_film
AND    nom_realisateur='Burton'
AND    nom_acteur='Depp';



  4. Quels films peut-on voir au Rex, et à quelle heure ?




    Solution :

    
SELECT titre, heure_debut
FROM   seance s, film f
WHERE  s.id_film = f.id_film
AND    s.nom_cinema='Rex';






    Solution :

    
SELECT titre, heure_debut
FROM   seance s, film f
WHERE  s.id_film = f.id_film
AND    s.nom_cinema='Rex';



  5. Titre des films dans lesquels a joué Woody Allen. Donner aussi le rôle.




    Solution :

    
SELECT titre, nom_role
FROM   role r, film f
WHERE  nom_acteur='Allen'
AND    r.id_film = f.id_film;

  6. Quel metteur en scène a tourné dans ses propres films ? Donner le nom, le rôle et le titre des films.




    Solution :

    
SELECT titre, nom_role, nom_acteur
FROM  role r, film f
WHERE r.nom_acteur  =  f.nom_realisateur
AND   r.id_film = f.id_film;

  7. Quel metteur en scène a tourné en tant qu'acteur ? Donner le nom, le rôle et le titre des films où le metteur en scène a joué.




    Solution :

    
SELECT f2.titre, nom_role, nom_acteur
FROM  role r, film f1, film f2
WHERE r.nom_acteur  =  f1.nom_realisateur
AND   r.id_film = f2.id_film;



  8. Où peut-on voir Shining ? (Nom et adresse du cinéma, horaire).




    Solution :

    
SELECT titre, c.nom_cinema, heure_debut, heure_fin
FROM  film f, seance s, cinema c
WHERE f.titre='Shining'
AND   s.id_film = f.id_film
AND   s.nom_cinema = c.nom_cinema;



  9. Dans quels films le metteur-en-scène a-t-il le même prénom que l'un des interprètes ? (titre, nom du metteur-en-scne, nom de l'inteprète). Le metteur-en-scène et l'interprète ne doivent pas être la même personne.




    Solution :

    
SELECT titre, a1.nom ``Metteur en scene'', a2.nom ``Interprete''
FROM  film f, role r, artiste a1, artiste a2
WHERE f.id_film = r.id_film
AND   r.nom_acteur = a2.nom
AND   f.nom_realisateur = a1.nom
AND   a1.prenom = a2.prenom;
AND   a1.nom != a2.nom



  10. Où peut-on voir un film avec Clint Eastwood ? (Nom et adresse du cinéma, horaire).


    Solution :

    
SELECT titre, c.nom_cinema, heure_debut, heure_fin
FROM  film f, seance s, cinema c, role r
WHERE r.nom_acteur = 'Eastwood'
AND   s.id_film = f.id_film
AND   s.nom_cinema = c.nom_cinema
AND   s.id_film = r.id_film;



  11. Quel film peut-on voir dans le 12e arrondissement, dans une salle climatisée ? (Nom du cinéma, No de la salle, horaire, titre du film).




    Solution :

    
SELECT  f.titre, c.nom_cinema, salle.no_salle,  heure_debut, heure_fin
FROM  film f, seance s, cinema c, salle salle
WHERE salle.climatise = 'O'
AND   c.arrondissement = 12
AND   s.id_film = f.id_film
AND   s.nom_cinema = c.nom_cinema
AND   salle.nom_cinema = c.nom_cinema
AND   s.no_salle     =   salle.no_salle;



  12. Liste des cinémas (Adresse, Arrondissement) ayant une salle de plus de 150 places et passant un film avec Bruce Willis.




    Solution :

    
SELECT c.nom_cinema, adresse, arrondissement
FROM  seance s, cinema c, salle, role r
WHERE r.nom_acteur = 'Willis'
AND   s.id_film = r.id_film
AND   s.nom_cinema = c.nom_cinema
AND   salle.nom_cinema = c.nom_cinema
AND   salle.capacite > 150
AND   salle.no_salle = s.no_salle;



  13. Liste des cinémas (Nom, Adresse) dont TOUTES les salles ont plus de 100 places.




    Solution :

    
SELECT c.nom_cinema, c.adresse
FROM   cinema c
WHERE  100 < ALL (SELECT capacite 
                 FROM salle s
            WHERE s.nom_cinema = c.nom_cinema);

3.3   Négation

  1. Quels acteurs n'ont jamais mis en scène de film ?




    Solution :

    
SELECT nom
FROM artiste
MINUS
SELECT nom_realisateur
FROM  film;



  2. Les cinémas (nom, adresse) qui ne passent pas un film de Tarantino.




    Solution :

    
SELECT c.nom_cinema, c.adresse
FROM  cinema c
WHERE NOT EXISTS (SELECT 'x'
            FROM seance s, film f
           WHERE nom_realisateur = 'Tarantino'
            AND   f.id_film = s.id_film
           AND   s.nom_cinema = c.nom_cinema);

3.4   Fonctions de groupe

  1. Total des places dans les salles du Rex.




    Solution :

    
SELECT sum (capacite)
FROM   salle
WHERE  nom_cinema = 'Rex';
    Resultat: 
    
SUM(CAPACITE)
-------------
          410



  2. Année du film le plus ancien et du film le plus récent.




    Solution :

    
SELECT MIN (annee), MAX (annee)
FROM   film;
    Resultat: 
    
MIN(ANNEE) MAX(ANNEE)
---------- ----------
      1926       1995



  3. Total des places offertes par cinéma.




    Solution :

    
SELECT nom_cinema, sum (capacite)
FROM   salle
GROUP BY  nom_cinema;
    Resultat: 
    
NOM_CINEMA SUM(CAPACITE)
---------- -------------
Halles               195
Kino                 400
Nations              280
Rex                  410



  4. Nom et prénom des réalisateurs, et nombre de films qu'ils ont tournés.




    Solution :

    
SELECT nom, prenom, count (titre)
FROM   artiste a, film f
WHERE   f.nom_realisateur = a.nom
GROUP BY  nom, prenom;



  5. (trèfle)Nom des cinémas ayant plus de 1 salle climatisée.




    Solution :

    
SELECT nom_cinema, count(*)
FROM   salle
WHERE  climatise = 'O'
GROUP BY nom_cinema
HAVING  count (*) > 1;
    Résultat : le Rex




  6. (trèfle)Les artistes (nom, prénom) ayant joué au moins dans trois films depuis 1985, dont au moins un passe a l'affiche a Paris (donner ausssi le nombre de films).




    Solution :

    
SELECT nom, prenom , count(*)
FROM   artiste, role, film
WHERE  nom = role.nom_acteur
AND    EXISTS  (SELECT 'x' FROM seance s , film f, role r
                WHERE  s.id_film = f.id_film
         AND    r.id_film = f.id_film
                AND    r.nom_acteur = artiste.nom)
AND     role.id_film = film.id_film
AND     film.annee > 1985
GROUP BY nom, prenom
HAVING  count (*) >= 3;
    Résultat : Bruce Willis !


Précédent Suivant Index