Exercice langage SQL : BD Cinéma (Partie 3)

Enoncé de l'Exercice (BD Cinéma Suite):
Les attributs NUM, NUM, NUMA, NUMC, NUMS sont des identifiants uniques (clés primaires) pour respectivement : FILM, PERSONNE, ACTEUR, CINÉMA, SALLE.
Un de ces attributs utilisé comme attribut d’une autre relation est une clé étrangère qui renvoie à la clé primaire de la relation correspondante, par exemple dans GÉNÉRIQUE, NUMF renvoie au NUMF de FILM et est défini sur le même domaine.
De plus, les attributs RÉALISATEUR dans FILM et NUMA dans ACTEUR sont définis sur le domaine des NUMP, et renvoient au NUMP de la personne correspondante.

SQ1

Réaliser les Requêtes suivantes:
Requête 20 : Trouver les couples acteur-réalisateur (noms et prénoms) tels que l’un a dirigé l’autre sur un film et vice-versa sur un autre.
Requête 21 : Trouver le nom, le prénom, le numéro des acteurs qui ont joué dans tous les films de Lelouch, s'il y en a.
Requête 22 : Pour chaque film de Bergman, trouver le nom et le prénom de l'acteur qui a eu le plus gros salaire.
Requête 23 : Donner le nom et le prénom des réalisateurs qui ont eu le plus gros salaire sur un de leurs films (par comparaison avec ceux des acteurs).

Requête 20 : Trouver les couples acteur-réalisateur (noms et prénoms) tels que l“’“un a dirigé l’autre sur un film et vice-versa sur un autre.
 
Forme plate :
 
SELECT DISTINCT P1.PRENOM, P1.NOM, P2.PRENOM, P2.NOM
FROM PERSONNE P1, PERSONNE P2, FILM F1, FILM F2,
DISTRIBUTION D1, DISTRIBUTION D2
WHERE P1.NUMP > P2.NUMP
AND P1.NUMP = F1.REALISATEUR
AND P2.NUMP = F2.REALISATEUR
AND F1.NUMF = D1.NUMF
AND D1.NUMA = F2.RÉALISATEUR
AND F2.NUMF = D2.NUMF
AND D2.NUMA = F1.REALISATEUR
 
Forme imbriquée:
 
 SELECT DISTINCT P1.PRÉNOM, P1.NOM, P2.PRÉNOM, P2.NOM
 FROM PERSONNE P1, PERSONNE P2
 WHERE (P1.NUMP, P2.NUMP) IN (
      SELECT F1.RÉALISATEUR, F2.RÉALISATEUR
      FROM FILM F1, FILM F2, DISTRIBUTION D1,
                DISTRIBUTION D2
      WHERE F1.RÉALISATEUR > F2.RÉALISATEUR
AND F1.NUMF = D1.NUMF
AND D1.NUMA = F2.RÉALISATEUR
AND F2.NUMF = D2.NUMF
AND D2.NUMA = F1.RÉALISATEUR)
 
Requête 21 : Trouver le nom, le prénom, le numéro des acteurs qui ont joué dans tous les films de Lelouch, s“’“il y en a.
 
Forme imbriquée – prédicat EXISTS : « dans un des films »
 
 SELECT NOM, PRÉNOM FROM PERSONNE P
 WHERE EXISTS (SELECT * FROM FILM F
      WHERE RÉALISATEUR IN (SELECT NUMP FROM PERSONNE
           WHERE NOM = ‘Lelouch’)
      ANDEXISTS (SELECT * FROM DISTRIBUTION D
WHERE D.NUMF = F.NUMF
AND D.NUMA = P.NUMP)
                                                        )
 
Requête 22 : Pour chaque film de Bergman, trouver le nom et le prénom de L“’ “acteur qui a eu le plus gros salaire.
 
Forme imbriquée – prédicat NOT EXISTS : un seul rôle par acteur
 
 SELECT F.TITRE, PA.PRÉNOM, PA.NOM
 FROM FILM F, DISTRIBUTION D1, PERSONNE PA
 WHERE F.NUMF = D1.NUMF
 ANDD1.NUMA = PA.NUMP
 ANDRÉALISATEUR IN (
      SELECT NUMP
      FROM PERSONNE
      WHERE NOM = “Bergman”)
 ANDNOT EXISTS (
      SELECT * FROM DISTRIBUTION D2
      WHERE D2.NUMF = D1.NUMF
      AND D2.SALAIRE > D1.SALAIRE )
 
Forme imbriquée + – prédicat > ALL : possibilité de plusieurs rôles pour un même acteur
 
 SELECT F.TITRE, PA.PRÉNOM, PA.NOM
 FROM FILM F, DISTRIBUTION D1, PERSONNE PA
 WHERE F.NUMF = D1.NUMF
 ANDD1.NUMA = PA.NUMP
 ANDRÉALISATEUR IN (
      SELECT NUMP
      FROM PERSONNE
      WHERE NOM = ‘Bergman’)
 GROUP BY D1.NUMF, D1.NUMA, F.TITRE, PA.PRÉNOM, PA.NOM
 HAVING SUM (SALAIRE) > ALL (
      SELECT SUM (SALAIRE)
      FROM DISTRIBUTION D2
      WHERE D2.NUMF = D1.NUMF
AND D2.NUMA   D1.NUMA
GROUP BY D2.NUMA)
 
Forme imbriquée: possibilité de plusieurs rôles pour un même acteur
 
 SELECT F.TITRE, PA.PRÉNOM, PA.NOM
 FROM FILM F, PERSONNE PA
WHERE (F.NUMF, PA.NUMP) IN (SELECT D1.NUMF, D1.NUMA
   FROM DISTRIBUTION D1 WHERE D1.NUMF IN (
       SELECT NUMF FROM FILM
       WHERE RÉALISATEUR IN (SELECT NUMP FROM PERSONNE
           WHERE NOM = ‘Bergman’ ) )
   GROUP BY D1.NUMF, D1.NUMA
   HAVING SUM (D1.SALAIRE) = (
       SELECT MAX (
           SELECT SUM (D2.SALAIRE)
           FROM DISTRIBUTION D2
           WHERE D2.NUMF = D1.NUMF
           GROUP BY D2.NUMA))
 
Utilisation d’une vue GROUPée:
 
 CREATE VIEW SALAIRE_TOTAL_ACTEUR_FILM
        (NUMA, NUMF, SALAIRE_TOTAL)
 AS SELECT NUMA, NUMF, SUM (SALAIRE)
        FROM FILM GROUP BY NUMA, NUMF
SELECT F.TITRE, PA.PRÉNOM, PA.NOM
FROM FILM F, SALAIRE_TOTAL_ACTEUR_FILM D1, PERSONNE PA
WHERE F.NUMF = D1.NUMF
AND D1.NUMA = PA.NUMP
AND RÉALISATEUR IN (SELECT NUMP FROM PERSONNE
    WHERE NOM = ‘Bergman’)
ANDNOT EXISTS ( SELECT * FROM SALAIRE_TOTAL_ACTEUR_FILM D2
    WHERE D2.NUMF = D1.NUMF
    AND D2.SALAIRE_TOTAL > D1.SALAIRE_TOTAL )
 
Requête 23 : Donner le nom et le prénom des réalisateurs qui ont eu le plus gros salaire sur un de leurs films (par comparaison avec ceux des acteurs).
 
Forme imbriquée :
 SELECT PRÉNOM, NOM
 FROM PERSONNE
 WHERE NUMP IN (
      SELECT RÉALISATEUR
      FROM FILM F
      WHERE SALAIRE_RÉAL > (
           SELECT MAX (SALAIRE)
           FROM DISTRIBUTION D
           WHERE D.NUMF = F.NUMF ) )
 
Forme imbriquée 2:
 SELECT PRÉNOM, NOM
 FROM PERSONNE
 WHERE NUMP IN ( SELECT RÉALISATEUR
FROM FILM F
WHERE SALAIRE_RÉAL > ALL (SELECT SUM (SALAIRE)
   FROM DISTRIBUTION D
   WHERE D.NUMF = F.NUMF
   GROUP BY NUMA ) )
 
Forme imbriquée 3 :
 SELECT PRÉNOM, NOM
 FROM PERSONNE
 WHERE NUMP IN (
      SELECT RÉALISATEUR
      FROM FILM F
      WHERE SALAIRE_RÉAL + (SELECT SUM (SALAIRE)
           FROM DISTRIBUTION D1
           WHERE D1.NUMF = F.NUMF
           AND D1.NUMA = F.RÉALISATEUR ) >(SELECT MAX (
           SELECT SUM (SALAIRE)
           FROM DISTRIBUTION D2
           WHERE D2.NUMF = F.NUMF
           GROUP BY D2.NUMA ) ) )

Exercice langage SQL : BD Cinéma (Partie 3)

0 commentaires:

Enregistrer un commentaire