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.
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.
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191 | 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 DISTRIBUTIONWHERE NUMF IN (SELECT NUMFFROM 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ÉEForme 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 XWHERE F.NUMF = X.NUMFAND 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.NUMCAND S.NBPLACES >= 200 AND S.TAILLE_ÉCRAN > 30AND S.NUMC = P.NUMC AND S.NUMS = P.NUMSAND P.HORAIRE ’22 :00’ AND P.NUMF = F.NUMFAND 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.NUMFAND EXISTS ( SELECT * FROMCINÉMA C WHERE C.NUMC = P.NUMCAND 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, TITREFROM 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 |