- 2013-03-24 - Licence Fondamentale d'Informatique
lundi 25 mars 2013

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


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
Schéma complémentaire
                                    RÉCOMPENSE (NUMR, CATÉGORIE, FESTIVAL)
                                    RÉCOMPENSE_FILM (NUMF, ANNÉE, NUMR)
                                    RÉCOMPENSE_ACTEUR (NUMA, NUMF, ANNÉE, NUMR)
Pour répondre aux questions suivantes, il faut noter que lorsqu'un acteur reçoit une récompense, le film en reçoit une indirectement.
Ce schéma complémentaire conduit à utiliser une union dans les requêtes.

Réaliser les Requêtes suivantes:
Requête 24 : Donner le titre des films qui ont été primés au moins une fois (y compris les récompenses des acteurs jouant dans le film).
Requête 25 : Lister les cinémas qui ont exclusivement passé des films primés.
Requête 26 : Donner le titre des films qui ont reçu au moins trois récompenses.
Requête 27 : Noms et prénoms des acteurs qui ont reçu plus de récompenses qu'aucun acteur qui a joué dans "Casablanca" n'en a eu.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99

Requête 24 : Donner le titre des films qui ont été primés au moins une fois (y compris les récompenses des acteurs jouant dans le film).
 
Forme plate:
 SELECT DISTINCT F.TITRE, F.ANNÉE
 FROM FILM F, RÉCOMPENSE_FILM RF
 WHERE F.NUMF = RF.NUMF
 UNION
 SELECT DISTINCT F.TITRE, F.ANNÉE
 FROM FILM F, RÉCOMPENSE_ACTEUR RA
WHERE F.NUMF = RA.NUMF
 
Forme imbriquée:
 SELECT TITRE, ANNÉE
 FROM FILM
 WHERE NUMF IN (
      SELECT NUMF
      FROM RÉCOMPENSE_FILM
      UNION
      SELECT NUMF FROM RÉCOMPENSE_ACTEUR )
 
Requête 25 : Lister les cinémas qui ont exclusivement passé des films primés.
 
Forme imbriquée:
 SELECT NOM, VILLE
 FROM CINÉMA C
 WHERE NOT EXISTS (
      SELECT * FROM PASSE P
      WHERE P.NUMC = C.NUMC
      AND NOT EXISTS (SELECT * FROM RÉCOMPENSE_FILM RF
           WHERE RF.NUMF = P.NUMF )
      ANDNOT EXISTS (
           SELECT * FROM RÉCOMPENSE_ACTEUR RA
WHERE RA.NUMF = P.NUMF ) )
 
Forme imbriquée– prédicat NOT EXISTS :
 SELECT NOM, VILLE
 FROM CINÉMA C
 WHERE NOT EXISTS (
      SELECT * FROM PASSE P
      WHERE P.NUMC = C.NUMC
      AND NOT EXISTS ( SELECT * FROM (
                SELECT NUMF
                FROM RÉCOMPENSE_FILM
                UNION
                SELECT NUMF
                FROM RÉCOMPENSE_ACTEUR ) AS R
WHERE R.NUMF = P.NUMF ) )
 
 
Forme imbriquée – prédicat NOT IN :
 SELECT NOM, VILLE
 FROM CINÉMA
 WHERE NUMC NOT IN (
      SELECT NUMC
      FROM PASSE
      WHERE NUMF NOT IN (
           SELECT R.NUMF FROM (
                SELECT NUMF
                FROM RÉCOMPENSE_FILM
                UNION
                SELECT NUMF
                FROM RÉCOMPENSE_ACTEUR ) AS R ) ) )
 
Requête 26 : Donner le titre des films qui ont reçu au moins trois récompenses.
 
Forme imbriquée:
 SELECT TITRE, ANNÉE
 FROM FILM
 WHERE NUMF IN (
      SELECT R.NUMF
      FROM (
           SELECT NUMF
           FROM RÉCOMPENSE_FILM
           UNION
           SELECT NUMF
           FROM RÉCOMPENSE_ACTEUR ) AS R
      GROUP BY R.NUMF
      HAVING COUNT (*) >= 3 ) 
Requête 27 : Noms et prénoms des acteurs qui ont reçu plus de récompenses qu”'”aucun acteur qui a joué dans "Casablanca" n”'”en a eu.
 
Forme imbriquée:
 SELECT PRÉNOM, NOM
 FROM PERSONNE
 WHERE NUMP IN (
      SELECT NUMA
      FROM RÉCOMPENSE_ACTEUR
      GROUP BY NUMA
      HAVING COUNT (*) > (
           SELECT MAX (
                SELECT COUNT (*)
                FROM RÉCOMPENSE_ACTEUR
                WHERE NUMA IN (
                    SELECT NUMA
                    FROM DISTRIBUTION
                    WHERE NUMF IN (
                         SELECT NUMF
                         FROM FILM
                         WHERE TITRE = ‘Casablanca’))
                GROUP BY NUMA)))
15:51
Read More
Publié par à 15:51 0 commentaires

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

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 ) ) )
15:51
Read More
Publié par à 15:51 0 commentaires

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.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191

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
15:50
Read More
Publié par à 15:50 0 commentaires

Exercice Langage SQL : BD Cinéma (Partie1)


Énoncé de l'Exercice (BD Cinéma):
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 1 : Retrouver la liste de tous les films.
Requête 2 : Retrouver la liste des films dont la longueur dépasse 180 min.
Requête 3 : Donner la liste de tous les genres de film.
Requête 4 : Trouver le titre et l’année des films de science fiction dont le budget dépasse 5.000.000 $.
Requête 5 : Donner le nombre de films par genre.
Requête 6 : Donner le nombre de films de 1960 par genre.
Requête 7 : Trouver le titre des films réalisés par Roman Polanski.
Requête 8 : Quels sont les acteurs comiques (nom, prénom) qui ont joué dans un film de Spielberg.
Requête 9 : Trouver le titre et l’année du film le plus long.
Requête 10 : Nom et prénom des acteurs qui ont joué Gavroche dans les différentes versions des « Misérables » avec les dates correspondantes.
Requête 11 : Donner le nom et le prénom des réalisateurs qui ont joué dans au moins un de leurs propres films.




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124

Requête 1 : Retrouver la liste de tous les films.
 
SELECT * FROM FILM
 
Requête 2 : Retrouver la liste des films dont la longueur dépasse 180 min.
 
SELECT * FROM FILM
WHERE LONGUEUR > 180
 
Requête 3 : Donner la liste de tous les genres de film.
 
SELECT DISTINCT GENRE
FROM FILM
 
Requête 4: Trouver le titre et l’’année des films de science fiction dont le budget dépasse 5.000.000 $.
 
SELECT TITRE, ANNÉE 
FROM FILM
WHERE GENRE = ‘SciFi’
ANDBUDGET > 5000000
 
Requête 5 : Donner le nombre de films par genre.
 
SELECT  GENRE, COUNT (*)
FROM FILM
GROUP BY GENRE
 
Requête 6 : Donner le nombre de films de 1960 par genre.
 
SELECT GENRE, COUNT (*)
FROM FILM
WHERE ANNÉE = 1960
GROUP BY GENRE
 
Requête 7  Trouver le titre des films réalisés par Roman Polanski
 
Forme plate :
 SELECT F.TITRE
 FROM FILM F, PERSONNE P
 WHERE F.RÉALISATEUR = P.NUMP
 AND P.PRÉNOM = ‘Roman’
 AND P.NOM = ‘Polanski’
 
Forme imbriquée :
 SELECT TITRE
 FROM FILM
 WHERE RÉALISATEUR IN (
      SELECT NUMP
      FROM PERSONNE
      WHERE PRÉNOM = ‘Roman’
      AND NOM = ‘Polanski’ )
 
Requête 8 : Quels sont les acteurs comiques (nom, prénom) qui ont joué dans un film de Spielberg.
 
Forme plate :
 SELECT PA.PRÉNOM, PA.NOM
 FROM PERSONNE PA, DISTRIBUTION D, FILM F, PERSONNE PR
 WHERE PA.NUMP = D.NUMA
AND D.SPÉCIALITÉ = ‘Comique’
AND D.NUMF = F.NUMF
AND F.RÉALISATEUR = PR.NUMP
AND PR.NOM = ‘Spielberg’
 
Forme imbriquée :
 SELECT PRÉNOM, NOM
 FROM PERSONNE
 WHERE NUMP IN (
      SELECT NUMA
      FROM DISTRIBUTION
      WHERE SPÉCIALITÉ = ‘Comique’
      ANDNUMF IN (
           SELECT NUMF
           FROMFILM
           WHERE RÉALISATEUR IN (
              SELECT NUMP
              FROMPERSONNE
              WHERE NOM = ‘Spielberg’ ) ) )
 
Requête 9 : Trouver le titre et l’année du film le plus long.
 
Forme imbriquée :
 SELECT TITRE, ANNÉE
 FROM FILM
 WHERE LONGUEUR = (
      SELECT MAX (LONGUEUR)
      FROM FILM )
 
Requête 10 : Nom et prénom des acteurs qui ont joué Gavroche dans les différentes versions des « Misérables » avec les dates correspondantes.
 
Forme plate :
 SELECT P.PRÉNOM, P.NOM, F.DATE
 FROM PERSONNE P, DISTRIBUTION D, FILM F
 WHERE P.NUMP = D.NUMA
 AND D.RÔLE = ‘Gavroche’
 AND G.NUMF = F.NUMF
 AND F.TITRE = ‘Les misérables’
 
Forme imbriquée :
 SELECT P.PRÉNOM, P.NOM, F.DATE
 FROM PERSONNE P, FILM F
 WHERE F.TITRE = ‘Les misérables’
 AND (P.NUMP, F.NUMF) IN (
      SELECT NUMA, NUMF
      FROM DISTRIBUTION
      WHERE RÔLE = ‘Gavroche’ )
 
Requête 11 : Donner le nom et le prénom des réalisateurs qui ont joué dans au moins un de leurs propres films.
 
Forme plate :
 SELECT DISTINCT P.PRÉNOM, P.NOM
 FROM PERSONNE P, FILM F, DISTRIBUTION D
 WHERE P.NUMP = F.RÉALISATEUR
 AND F.NUMF = D.NUMF
 AND D.NUMA = F.RÉALISATEUR
 
Forme imbriquée SQL-92 :
 SELECT DISTINCT PRÉNOM, NOM
 FROM PERSONNE
 WHERE NUMP IN (
      SELECT RÉALISATEUR
      FROM FILM
      WHERE (RÉALISATEUR, NUMF) IN (
           SELECT NUMA, NUMF
           FROM DISTRIBUTION ) )
15:49
Read More
Publié par à 15:49 0 commentaires
Inscription à : Articles (Atom)
 
-