Exercice SQL corrigé base de donnée cinéma (Partie III)

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