Exercice SQL corrigé base de données Cinéma (Partie II)

Requête 12 : Quel est le total des salaires des acteurs du film « Nuits blanches à Seattle ».

Forme plate:

SELECT SUM (D.SALAIRE)
FROM DISTRIBUTION D, FILM F
WHERE DISTRIBUTION.NUMF = F.NUMF
AND F.TITRE = ‘Nuits blanches à Seattle’

Forme imbriquée :

SELECT SUM (SALAIRE)
FROM DISTRIBUTION
WHERE NUMF IN (SELECT NUMF
FROM FILM WHERE TITRE = ‘Nuits blanches à Seattle’ )

Requête 13 : Donner la moyenne des salaires des acteurs par film, avec le titre et l’année correspondants.

SELECT F.TITRE, F.ANNÉE, AVG (D.SALAIRE)
FROM FILM F, DISTRIBUTION D
WHERE F.NUMF = D.NUMF
GROUP BY F.TITRE, F.ANNÉE

Requête 14 : Trouver le genre des films des années 80 dont le budget moyen dépasse 200.000 $.

SELECT GENRE FROM FILM
WHERE ANNÉE BETWEEN 1980 AND 1989
GROUP BY GENRE
HAVING AVG (BUDGET) > 200000

Requête 15 : Pour chaque film de Spielberg (titre, année), donner le total des salaires des acteurs.

Forme plate :

SELECT F.TITRE, F.ANNÉE, SUM (D.SALAIRE)
FROM FILM F, DISTRIBUTION D, PERSONNE P
WHERE F.NUMF = D.NUMF
AND F.RÉALISATEUR = P.NUMP
AND P.NOM = ‘Spielberg’
GROUP BY F.TITRE, F.ANNÉE

Forme imbriquée :

SELECT F.TITRE, F.ANNÉE, SUM (D.SALAIRE)
FROM FILM F, DISTRIBUTION D
WHERE F.NUMF = D.NUMF
AND F.RÉALISATEUR IN (SELECT NUMP FROM PERSONNE
WHERE NOM = ‘Spielberg’ )
GROUP BY F.TITRE, F.ANNÉE
Forme imbriquée SQL-92 :
SELECT F.TITRE, F.ANNÉE, X.SUMSAL
FROM FILM F, (SELECT NUMF, SUM (SALAIRE) AS SUMSAL
FROM DISTRIBUTION
GROUP BY NUMF ) AS X
WHERE F.NUMF = X.NUMF
AND F.RÉALISATEUR IN (SELECT NUMP FROM PERSONNE
WHERE NOM = ‘Spielberg’ )

Requête 16 : Lister les cinémas dont la taille moyenne d »‘ »écran est supérieure à 40 mètres carrés.

Forme plate :

SELECT C.NOM, C.VILLE
FROM CINÉMA C, SALLE S
WHERE C.NUMC = S.NUMC
GROUP BY C.NUMC, C.NOM, C.VILLE
HAVING AVG (S.TAILLE_ÉCRAN) > 40 )

Forme imbriquée SQL-92 :

SELECT NOM, VILLE FROM CINÉMA
WHERE NUMC IN (SELECT NUMC FROM SALLE
GROUP BY NUMC
HAVING AVG (TAILLE_ÉCRAN) > 40 )

Requête 17 : Quels sont les cinémas Parisiens de la Fox, avec le film correspondant, qui passent un film d »‘ »Elia Kazan avant 22 heures dans une salle d’au moins 200 places et d’écran de taille supérieure à 30 m carrés.

Forme plate :

SELECT DISTINCT C.NOM, F.TITRE
FROM CINÉMA C, SALLE S, PASSE P, FILM F, PERSONNE P
WHERE C.COMPAGNIE = ‘Fox’
ANDC.VILLE = ‘Paris’ AND C.NUMC = S.NUMC
AND S.NBPLACES >= 200 AND S.TAILLE_ÉCRAN > 30
AND S.NUMC = P.NUMC AND S.NUMS = P.NUMS
AND P.HORAIRE < ’22 :00’ AND P.NUMF = F.NUMF
AND F.RÉALISATEUR = P.NUMP AND P.PRÉNOM = ‘Elia’
AND P.NOM = ‘Kazan’

Forme imbriquée:

SELECT DISTINCT C.NOM, F.TITRE
FROM CINÉMA C, FILM F
WHERE C.COMPAGNIE = ‘Fox’ ANDC.VILLE = ‘Paris’
AND (C.NUMC, F.NUMF) IN (
SELECT S.NUMC, P.NUMF FROM SALLE S, PASSE P
WHERE S.NBPLACES >= 200
AND S.TAILLE_ÉCRAN > 30 AND S.NUMC = P.NUMC
AND S.NUMS = P.NUMS
AND P.HORAIRE < ’22 :00’ )
ANDF.RÉALISATEUR IN (
SELECT NUMP FROM PERSONNE
WHERE PRÉNOM = ‘Elia’ AND NOM = ‘Kazan’ )

Requête 18 : Trouver le titre des films qui ne passent à aucun cinéma de la Compagnie FOX.

Forme plate : pour trouver ceux qui passent dans un cinéma de la Fox

SELECT DISTINCT F.NUMF, F.TITRE
FROM FILM F, PASSE P, CINÉMA C
WHERE F.NUMF = P.NUMF
AND P.NUMC = C.NUMC
AND C.COMPAGNIE = ‘Fox’

Forme imbriquée 1 – prédicat IN : pour trouver ceux qui passent dans un cinéma de la Fox

SELECT DISTINCT NUMF, TITRE
FROM FILM WHERE NUMF IN (
SELECT NUMF FROM PASSE
WHERE NUMC IN (SELECT NUMC FROM CINÉMA
WHERE COMPAGNIE = ‘Fox’ ) )

Forme imbriquée 2 – prédicat EXISTS : toujours pour trouver ceux qui passent dans un cinéma de la Fox

SELECT DISTINCT NUMF, TITRE
FROM FILM F WHERE EXISTS (
SELECT * FROM PASSE P
WHERE P.NUMF = F.NUMF
AND EXISTS ( SELECT * FROM
CINÉMA C WHERE C.NUMC = P.NUMC
AND COMPAGNIE = ‘Fox’ ) )

La négation de ces deux dernières formes permet d’exprimer la requête initiale : les films qui ne passent à aucun des cinémas de la Fox.

Forme imbriquée 1 – prédicat NOT IN :

SELECT DISTINCT NUMF, TITRE
FROM FILM WHERE NUMF NOT IN (
SELECT NUMF FROM PASSE WHERE NUMC IN (
SELECT NUMC FROMCINÉMA
WHERE COMPAGNIE = ‘Fox’ ) )

Forme imbriquée 2 – prédicat NOT EXISTS : pour trouver ceux qui ne passent dans aucun cinéma de la Fox

SELECT DISTINCT NUMF, TITRE FROM FILM F
WHERE NOT EXISTS (
SELECT * FROM PASSE P
WHERE P.NUMF = F.NUMF
ANDEXISTS (SELECT * FROM CINÉMA C
WHERE C.NUMC = P.NUMC
AND COMPAGNIE = ‘Fox’ ) )

Pour finalement arriver à la forme la plus simple, où seul le prédicat NOT EXISTS provoque un niveau d’imbrication.

Forme 3 – prédicat NOT EXISTS uniquement :

SELECT DISTINCT NUMF, TITRE FROM FILM F
WHERE NOT EXISTS (
SELECT * FROM PASSE P, CINÉMA C
WHERE F.NUMF = P.NUMF
AND P.NUMC = C.NUMC
AND COMPAGNIE = ‘Fox’ )

Forme complète :

SELECT DISTINCT NUMF, TITRE
FROM FILM F WHERE NUMF IN (
SELECT NUMF FROM PASSE )
AND NOT EXISTS (
SELECT * FROM PASSE P, CINÉMA C
WHERE F.NUMF = P.NUMF
AND P.NUMC = C.NUMC
AND COMPAGNIE = ‘Fox’ )

Requête 19 : Trouver le nom et le prénom des acteurs qui ont eu un salaire plus important dans un film particulier que le salaire du réalisateur du même film.

Forme plate :

SELECT PA.PRÉNOM, PA.NOM
FROM PERSONNE PA, DISTRIBUTION D, FILM F
WHERE PA.NUMP = D.NUMA
AND D.NUMF = F.NUMF
AND D.SALAIRE > F.SALAIRE_RÉAL

Forme imbriquée 1 :

SELECT PRÉNOM, NOM
FROM PERSONNE
WHERE NUMP IN ( SELECT D.NUMA
FROM DISTRIBUTION D, FILM F
WHERE D.NUMF = F.NUMF
AND D.SALAIRE > F.SALAIRE_RÉAL )

Forme imbriquée 2 :

SELECT PRÉNOM, NOM FROM PERSONNE
WHERE NUMP IN (SELECT NUMA
FROM DISTRIBUTION D
WHERE D.SALAIRE > (SELECT F.SALAIRE_RÉAL FROMFILM F

Forme imbriquée :

SELECT DISTINCT PA.PRÉNOM, PA.NOM
FROM PERSONNE PA, DISTRIBUTION D
WHERE PA.NUMP = D.NUMA
GROUP BY D.NUMA, D.NUMF, PA.PRÉNOM, PA.NOM
HAVING SUM (SALAIRE) > (
SELECT SALAIRE_RÉAL
FROM FILM F
WHERE D.NUMF = F.NUMF