Exercice Langage SQL : BD Cinéma (Partie 2)

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 12 : Quel est le total des salaires des acteurs du film « Nuits blanches à Seattle ».
Requête 13 : Donner la moyenne des salaires des acteurs par film, avec le titre et l’année correspondants.
Requête 14 : Trouver le genre des films des années 80 dont le budget moyen dépasse 200.000 $.
Requête 15 : Pour chaque film de Spielberg (titre, année), donner le total des salaires des acteurs.
Requête 16 : Lister les cinémas dont la taille moyenne d'écran est supérieure à 40 mètres carrés.
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.
Requête 18 : Trouver le titre des films qui ne passent à aucun cinéma de la 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.

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

Exercice Langage SQL : BD Cinéma (Partie 2)

0 commentaires:

Enregistrer un commentaire