7. TD SQL

7.1. La base Cinema

_images/cinema.png

7.2. Requêtes mono-table

  1. Titre, année de sortie et réalisateur des films français. Attention: contrairement à Access, en sql il y a une distinction entre minuscules et majuscules dans les comparaisons:

    titre | annee | realisateur
    ------------------------+-------+-------------
     La baie des anges | 1962 | Demy
     Mon cher sujet | 1989 | Mieville
     Baxter | 1989 | Boivin
     Nikita | 1989 | Besson
     L'amant | 1991 | Annaud
     La belle histoire | 1991 | Lelouch
     Cyrano de Bergerac | 1989 | Rappeneau
     Jules et Jim | 1961 | Truffaut
     Merci la vie | 1990 | Blier
     Mauvais sang | 1990 | Carax
     Hotel du nord | 1938 | Carne
     Les enfants du paradis | 1943 | Carne
     Le grand bleu | 1987 | Besson
     Les visiteurs | 1993 | C. Clavier
    (14 lignes)
  2. Titres des films avec leur réalisateur, dans l’ordre alphabétique des titres de film:

     titre | realisateur
    --------------------------+-------------
     Annee du soleil calme | Krzysztof
     Appocalypse now | Coppola
     Baxter | Boivin
     Birdy | Parker
     Cop | Harris
     Cyrano de Bergerac | Rappeneau
     Danse avec les loups | Costner
     Easy Rider | Hopper
     Fisher King | Gilliam
     Hotel du nord | Carne
     JFK | Stone
     Jules et Jim | Truffaut
     La baie des anges | Demy
     La belle histoire | Lelouch
     La boca del lobo | Lombardi
     La derniere cible | Eastwood
     L'amant | Annaud
     Le grand bleu | Besson
     Les enfants du paradis | Carne
     Les liaisons dangereuses | Frears
     Les visiteurs | C. Clavier
     Mauvais sang | Carax
     Merci la vie | Blier
     Midnight express | Parker
     Mon cher sujet | Mieville
     Nikita | Besson
     Pink Floyd, the wall | Parker
     Rusty James | Coppola
     Sauve qui peut, la vie | Godard
     Shining | Kubrick
     Taxi driver | Scorsese
     Un poisson nomme wanda | Crichton
     Veuve mais pas trop | Truffaut
    (33 lignes)
  3. Nom des réalisateurs qui commencent par un T:

     realisateur
    -------------
     Truffaut
    (1 ligne)

7.3. Jointures simples

  1. Noms et numéros des acteurs du film “Cop”:

     nomacteur | idacteur
    -----------+----------
     Deneuve   |        7
     Eastwood  |        8
     Haid      |       12
     Warren    |       32
     Woods     |       34
    (5 lignes)
  2. Années de sortie des films tournés avec Gabin:

     annee
    -------
      1989
      1962
    (2 lignes)
  3. Films à l’affiche de la quatrième semaine dans chaque salle du cinéma gaumont:

     salle |         titre
    -------+------------------------
     1     | Mon cher sujet
     2     | Annee du soleil calme
     3     | Un poisson nomme wanda
     4     | Veuve mais pas trop
     5     | La derniere cible
     6     | La boca del lobo
    (6 lignes)
  4. Acteurs qui ont tourné avec Truffaut comme réalisateur:

     idacteur | nomacteur
    ----------+-----------
           19 | Modine
           23 | Pfeiffer
           24 | Piccoli
           29 | Stockwell
           30 | Truffaut
    (5 lignes)
  5. Adresses des cinémas où passe un film réalisé par Crichton:

        nomcine     |       rue
    ----------------+-----------------
     gaumont        | alsace lorraine
     la nef         | edouard rey
     les 6 rex      | saint jacques
     pathe grenette | grenette
     ugc royal      | clot bey
    (5 lignes)
  6. Rues, salles et nombre de places des cinémas où sont joués des films sortis en 1984 et dans lesquels joue Delon:

           rue       | salle | nbplaces
    -----------------+-------+----------
     alsace lorraine | 5     |      100
     grenette        | 6     |      100
     saint jacques   | 1     |      400
     strasbourg      | 1     |      200
    (4 lignes)
  7. Titre et semaine de programmation des films dans lesquels joue Deneuve ou Eastwood:

           titre       | semaine
    -------------------+---------
     Cop               |       1
     Cop               |       2
     Cop               |       3
     Cop               |       4
     La derniere cible |       1
     La derniere cible |       2
     La derniere cible |       3
     La derniere cible |       4
    (8 lignes)
  8. Pour chaque réalisateur, nom des acteurs qui ont tourné sous sa direction en 1989, trié par ordre alphabétique de nom de réalisateur puis par ordre alphabétique inverse de nom d’acteur:

     realisateur | idacteur | nomacteur
    -------------+----------+------------
     Boivin      |       28 | Spiesser
     Boivin      |       24 | Piccoli
     Boivin      |       18 | Mercure
     Boivin      |       10 | Gastaldi
     Boivin      |        6 | Delon
     Boivin      |        5 | Delamare
     Crichton    |       22 | Palin
     Crichton    |       14 | Kline
     Crichton    |        4 | Curtis
     Crichton    |        3 | Cleese
     Harris      |       34 | Woods
     Harris      |       32 | Warren
     Harris      |       12 | Haid
     Harris      |        8 | Eastwood
     Harris      |        7 | Deneuve
     Krzysztof   |       33 | Wilson
     Krzysztof   |       27 | Skanzanka
     Krzysztof   |       15 | Komorowska
     Krzysztof   |        9 | Gabin
     Lombardi    |       31 | Vega
     Lombardi    |        1 | Bueno
     Mieville    |       30 | Truffaut
     Mieville    |       26 | Roussel
     Mieville    |       25 | Romand
     Mieville    |       16 | Le roi
     Truffaut    |       30 | Truffaut
     Truffaut    |       29 | Stockwell
     Truffaut    |       24 | Piccoli
     Truffaut    |       23 | Pfeiffer
     Truffaut    |       19 | Modine
    (30 lignes)
  9. Films où le réalisateur est aussi acteur:

     idfilm |        titre
    --------+---------------------
          4 | La derniere cible
          9 | Veuve mais pas trop
    (2 lignes)

7.4. Regroupements

  1. Afficher pour le réalisateur Mieville, le nombre de films, en nommant nbreFilms le titre de la colonne:

     nbrefilms
    -----------
             1
    (1 ligne)
  2. Afficher pour chaque réalisateur, le nombre de films, en commençant par celui qui a réalisé le plus de films:

     realisateur | count
    -------------+-------
     Parker      |     3
     Besson      |     2
     Carne       |     2
     Coppola     |     2
     Truffaut    |     2
     Boivin      |     1
     Godard      |     1
     Gilliam     |     1
     Annaud      |     1
     Blier       |     1
     Costner     |     1
     C. Clavier  |     1
     Hopper      |     1
     Crichton    |     1
     Scorsese    |     1
     Frears      |     1
     Krzysztof   |     1
     Kubrick     |     1
     Harris      |     1
     Carax       |     1
     Lombardi    |     1
     Lelouch     |     1
     Mieville    |     1
     Rappeneau   |     1
     Eastwood    |     1
     Demy        |     1
     Stone       |     1
    (27 lignes)
  3. Afficher pour chaque réalisateur, le nombre de films, pour les réalisateurs qui ont fait plus d’un film:

     realisateur | count
    -------------+-------
     Besson      |     2
     Carne       |     2
     Parker      |     3
     Coppola     |     2
     Truffaut    |     2
    (5 lignes
  4. Nombre de semaines de programmation de chaque film:

             titre          | semaines
    ------------------------+----------
     Annee du soleil calme  |        4
     Baxter                 |        4
     Cop                    |        4
     La baie des anges      |        4
     La boca del lobo       |        4
     La derniere cible      |        4
     Mon cher sujet         |        4
     Un poisson nomme wanda |        5
     Veuve mais pas trop    |        5
    (9 lignes)
  5. Titre des films qui passaient la semaine 4, avec le nombre de cinémas dans lesquels ils étaient programmés, limités à ceux programmés dans au moins 4 cinémas, triés dans l’ordre du nombre de cinémas dans lesquels ils étaient programmés:

             titre          | cinemas
    ------------------------+---------
     Cop                    |       4
     La boca del lobo       |       4
     La derniere cible      |       4
     Mon cher sujet         |       4
     Annee du soleil calme  |       5
     La baie des anges      |       5
     Un poisson nomme wanda |       5
     Veuve mais pas trop    |       5
    (8 lignes)
  6. Box office (=les films dans l’ordre décroissant du nombre d’entrées) de la semaine 4 (titre, réalisateur, nombre d’entrées):

             titre          | realisateur | entrees
    ------------------------+-------------+---------
     Un poisson nomme wanda | Crichton    |    3013
     Annee du soleil calme  | Krzysztof   |    2679
     Veuve mais pas trop    | Truffaut    |    2364
     La baie des anges      | Demy        |    2001
     Mon cher sujet         | Mieville    |    1763
     La derniere cible      | Eastwood    |    1317
     Cop                    | Harris      |    1194
     La boca del lobo       | Lombardi    |     974
     Baxter                 | Boivin      |     101
    (9 lignes)

7.5. Auto-jointures et sous-requêtes

  1. Avec une sous-requête, afficher les films qui ont été tournés la même année que Nikita:

             titre
    ------------------------
     Annee du soleil calme
     Cop
     La boca del lobo
     Un poisson nomme wanda
     Mon cher sujet
     Baxter
     Veuve mais pas trop
     Nikita
     Cyrano de Bergerac
    (9 lignes)
  2. Même question avec auto-jointure:

             titre
    ------------------------
     Annee du soleil calme
     Cop
     La boca del lobo
     Un poisson nomme wanda
     Mon cher sujet
     Baxter
     Veuve mais pas trop
     Nikita
     Cyrano de Bergerac
    (9 lignes)
  3. Acteurs partenaires de Deneuve:

     idacteur | nomacteur
    ----------+-----------
            2 | Clarkson
            6 | Delon
            8 | Eastwood
           12 | Haid
           13 | Hunt
           32 | Warren
           34 | Woods
    (7 lignes)
  4. Réalisateurs ayant joué dans des films qu’ils n’ont pas réalisés eux-même:

     realisateur
    -------------
     Eastwood
     Truffaut
    (2 lignes)
  5. Réalisateurs et noms des films précédés dans la programmation par un film dans lequel joue Pfeiffer:

     realisateur |         titre
    -------------+------------------------
     Boivin      | Baxter
     Crichton    | Un poisson nomme wanda
     Demy        | La baie des anges
     Eastwood    | La derniere cible
     Harris      | Cop
     Krzysztof   | Annee du soleil calme
     Lombardi    | La boca del lobo
     Mieville    | Mon cher sujet
    (8 lignes)
  6. Liste des acteurs avec le nombre de leurs partenaires (c’est à dire le nombre des acteurs avec lesquels ils ont joué dans au moins un film), dans l’ordre décroissant du nombre de partenaires:

     nomacteur  | partenaires
    ------------+-------------
     Delon      |           9
     Piccoli    |           9
     Deneuve    |           7
     Eastwood   |           7
     Gabin      |           7
     Truffaut   |           7
     . . .
     Skanzanka  |           3
     Wilson     |           3
     Bueno      |           1
     Vega       |           1
    (34 lignes)
  7. Titre, année et pays des films pour lesquels il existe au moins un autre film réalisé la même année dans le même pays:

             titre          | annee |  pays
    ------------------------+-------+--------
     Birdy                  |  1984 | USA
     La derniere cible      |  1984 | USA
     Baxter                 |  1989 | France
     Cyrano de Bergerac     |  1989 | France
     Mon cher sujet         |  1989 | France
     Nikita                 |  1989 | France
     Cop                    |  1989 | USA
     Un poisson nomme wanda |  1989 | USA
     Veuve mais pas trop    |  1989 | USA
     Mauvais sang           |  1990 | France
     Merci la vie           |  1990 | France
     La belle histoire      |  1991 | France
     L'amant                |  1991 | France
     Fisher King            |  1991 | USA
     JFK                    |  1991 | USA
    (15 lignes)
  8. Analyse de la concurrence: pour chaque paire de cinémas (C1, C2), nombre de films identiques programmés simultanément. La liste doit être triée par C1 puis par nombre de films simultanés (dans l’ordre décroissant), puis par C2:

        nomcine     | communs |    nomcine
    ----------------+---------+----------------
     gaumont        |       7 | gaumont
     gaumont        |       5 | la nef
     gaumont        |       5 | le club
     gaumont        |       5 | les 6 rex
     gaumont        |       5 | pathe grenette
     gaumont        |       4 | ugc royal
     gaumont        |       3 | le melies
     gaumont        |       2 | les dauphins
     la nef         |       6 | la nef
     la nef         |       5 | gaumont
     la nef         |       5 | le club
     la nef         |       5 | les 6 rex
     la nef         |       4 | pathe grenette
     . . .
     ugc royal      |       2 | les dauphins
    (64 lignes)
  9. Noms des cinémas ayant programmé un même film dans des salles différentes:

     idcine |  nomcine
    --------+-----------
          1 | le club
          7 | ugc royal
    (2 lignes)
  10. Pour chaque pays, donnez le nombre moyen d’acteurs par film:

      pays   | x
    ---------+---
     Espagne | 2
     Pologne | 4
     USA     | 4
     France  | 5
    (4 lignes)
  11. Pour chaque année, noms des cinémas ayant eu à l’affiche tous les films sortis cette année:

     annee |    nomcine
    -------+----------------
      1962 | le club
      1962 | les dauphins
      1962 | les 6 rex
      1962 | ugc royal
      1962 | pathe grenette
    (5 lignes)

7.6. Champs calculés, Jointures gauches, CASE WHEN (SI) et requêtes difficiles

  1. films qui ont au moins 6 acteurs:

    titre
    -------------------
    Baxter
  2. nombre de films par décennie:

    decennie |      nbFilms
    -------------------
    30      | 1
    40      | 1
    60      | 3
    70      | 4
    80      | 16
    90      | 8
  3. les acteurs sont payes 200000 euros et 0.5 euro par entrée, calculer le salaire total des acteurs:

    nomacteur | salaire
    -------------------
    Bueno | 201360.0
    Clarkson | 201758.5
    Cleese | 208952.5
    Curtis | 208952.5
    Delamare | 201860.0
    Delon | 403618.5
    Deneuve | 405512.5
    . . .
  4. pourcentage de remplissage des salles du cinéma gaumont la semaine 4 (le nombre d’entrées de la table programme correspond au nombre d’entrées par semaine):

    salle | pourcentage
    -------------------
    1 | 9
    2 | 51
    3 | 47
    4 | 37
    5 | 33
    6 | 41
  5. films qui n’ont pas été programmes:

    titre
    -------------------
    Nikita
    L'amant
    La belle histoire
    Cyrano de Bergerac
    Danse avec les loups
    Easy Rider
    Fisher King
    JFK
    Les liaisons dangereuses
    Appocalypse now
    Birdy
    Taxi driver
    Shining
    Sauve qui peut, la vie
    Jules et Jim
    Pink Floyd, the wall
    Rusty James
    Midnight express
    Merci la vie
    Mauvais sang
    Hotel du nord
    Les enfants du paradis
    Le grand bleu
    Les visiteurs
  6. cinéma qui n’ont rien programme la semaine 4:

    nomcine
    -------------------
    lux
    vox
  7. film qui a fait le plus grand nombre d’entrées:

    titre
    -------------------
    Un poisson nomme wanda
  8. films dont le nombre d’entrées est supérieur a la moyenne des entrées par semaine:

    titre
    -------------------
    Annee du soleil calme
    Cop
    Un poisson nomme wanda
    La baie des anges
    Mon cher sujet
    Baxter
    Veuve mais pas trop
  9. films dont le nombre d’entrées est égal à +/- 5 % de la moyenne des entrées par semaine:

    titre
    -------------------
    Cop
    Un poisson nomme wanda
    Mon cher sujet
    Veuve mais pas trop
  10. les réalisateurs sont payes 100000 euros avec une subvention de 50000 euros s’ils réalisent un film en France, calculer le salaire total des réalisateurs:

    realisateur | salaire
    -------------------
    Annaud | 150000
    Besson | 300000
    Blier | 150000
    Boivin | 150000
    C. Clavier | 150000
    . . .
  11. nombre de films par mois et par cinéma, on notera Jamais si un cinéma ne programme aucun film. Pour simplifier il n’y a que 2 mois : janvier et fevrier, on supposera qu’un mois dure 4 semaines !!!!:

    nomcine | mois | nbFilms
    -------------------
    gaumont | Janvier | 7
    la nef | Janvier | 6
    le club | Janvier | 7
    le melies | Fevrier | 1
    le melies | Janvier | 4
    les 6 rex | Janvier | 8
    les dauphins | Janvier | 4
    lux | Jamais | 0
    pathe grenette | Fevrier | 1
    pathe grenette | Janvier | 7
    ugc royal | Janvier | 6
    vox | Jamais | 0
  12. realisateur qui a fait le + grand nombre de films

  13. acteur le plus rentable (+ grand nombre d’entrées)

  14. realisateurs qui ne sont pas acteurs ss jointure gauche.

  15. idem jointure gauche

  16. par cinema liste des films qui ont un nb d’entrées inférieur à la moitié du nb de place tt salles confondues la semaine 4

  17. nb de films par première lettre (A:2) : substr(champ,debut,nb)

  18. realisateur qui a tourné avec le plus grand nombre d’acteurs

  19. les acteurs ont un intéressement de 0.5 euro par entrée d’un film si le nombre d’entrées de ce film dépasse 10000, pour chaque acteur ayant obtenu des intéressements afficher leur montant total

  20. le tarif des entrées des cinémas est de 6€ sauf “le melies” : 5€ et “le club”: 3€. Quelle est la recette totale des cinemas

  21. pour “le club”, par salle et par film, afficher “abandonner” si le taux d’occupation de la semaine 4 est inférieur à 20% des places de la salle, “ouvrir” s’il est supérieur à 80% sinon “continuer”

  22. par cinema et par semaine, liste des salles sans programmation la semaine 4

  23. acteurs qui n’ont pas tourné de films au USA mais qui en ont tourné en Espagne