MPSI 833 TP d'informatique - 15
Bases de données - 3 - Corrigé
1 Cinéma
1. Quels sont les prénoms des personnes ? Les prénoms sans doublon ? Qu'observez-vous ?
SELECT prenom FROM personne
SELECT DISTINCT prenom FROM personne
On observe que les prénoms sont rangés dans l'ordre alphabétique avec la seconde demande : ceci s'explique
par le fait que pour supprimer les doublons de manière e cace en termes de complexité, le mieux est de
commencer par trier . . .
2. Quel est le nom des personnes dont le prénom est John ?
SELECT nom FROM personne WHERE prenom = ' John '
3. Quels sont les titres des lms réalisés dans les années 90 ?
SELECT t i t r e FROM f i l m WHERE annee >= 1990 AND annee < 2000
4. Quelles sont les personnes qui sont des acteurs ?
SELECT DISTINCT nom , prenom FROM
personne JOIN j o u e r
ON personne . idp = j o u e r . ida
5. Quelles sont les personnes qui sont des réalisateurs ?
SELECT DISTINCT nom , prenom FROM
personne JOIN f i l m
ON personne . idp = f i l m . i d r
6. Qui est à la fois acteur et réalisateur ?
SELECT DISTINCT nom , prenom FROM
personne JOIN j o u e r
ON personne . idp = j o u e r . ida
INTERSECT
SELECT DISTINCT nom , prenom FROM
personne JOIN f i l m
ON personne . idp = f i l m . i d r
7. Dresser la liste de toutes les interprétations possibles, en précisant le nom et le prénom de l'acteur ainsi
que le rôle et le titre du lm. La liste sera triée par ordre alphabétique du nom.
SELECT nom , prenom , r o l e , t i t r e FROM
personne JOIN j o u e r ON idp = ida
JOIN f i l m ON f i l m . i d f = j o u e r . i d f
ORDER BY nom
8. Quels sont les titres des lms où Kevin Spacey a joué un rôle ?
Page 1
MPSI 833 TP d'informatique - 15
SELECT t i t r e FROM
f i l m JOIN j o u e r
ON f i l m . i d f = j o u e r . i d f
JOIN personne
ON personne . idp = j o u e r . ida
WHERE nom = ' Spacey ' AND prenom = ' Kevin '
9. Quels sont les drames que l'on a pu voir après le 1er janvier 2000 ?
SELECT DISTINCT t i t r e FROM f i l m
JOIN p r o j e c t i o n
ON f i l m . i d f = p r o j e c t i o n . i d f
WHERE genre = ' Drame '
AND j o u r > ' 2000 −01 −01 '
10. Dresser la liste des acteurs (nom et prénom) en précisant le nombre de lms dans lesquels ils ont joué. Le
résultat doit être trié par ordre alphabétique des noms.
SELECT nom , prenom , COUNT( ∗ ) AS nb_films FROM
personne JOIN j o u e r ON personne . idp = j o u e r . ida
GROUP BY nom , prenom
ORDER BY nom
11. Quels sont les acteurs ayant joué dans des drames ?
SELECT personne . nom , personne . prenom FROM
personne JOIN j o u e r
ON personne . idp = j o u e r . ida
JOIN f i l m
ON j o u e r . i d f = f i l m . i d f
WHERE genre = ' P o l i c i e r '
12. Quels sont les titres des lms où Kevin Spacey a joué un rôle et qui ont été projetés au cinéma UGC ?
SELECT t i t r e FROM
f i l m JOIN j o u e r
ON f i l m . i d f = j o u e r . i d f
JOIN personne
ON personne . idp = j o u e r . ida
JOIN p r o j e c t i o n
ON p r o j e c t i o n . i d f = f i l m . i d f
JOIN cinema
ON p r o j e c t i o n . i d c = cinema . i d c
WHERE personne . nom = ' Spacey '
AND personne . prenom = ' Kevin '
AND cinema . nom = 'UGC'
Page 2
MPSI 833 TP d'informatique - 15
13. Combien de lms diérents ont été projetés à l'UGC ?
SELECT COUNT( ∗ ) FROM
(
SELECT DISTINCT p r o j e c t i o n . i d f FROM
cinema JOIN p r o j e c t i o n
On cinema . i d c = p r o j e c t i o n . i d c
WHERE cinema . nom = 'UGC'
GROUP BY p r o j e c t i o n . i d f
)
14. Quels sont les acteurs ayant joué dans tous les lms de Lars von Trier ?
On peut déjà voir comment calculer le nombre de lms réalisés par Lars von Trier :
SELECT COUNT( ∗ ) FROM
f i l m JOIN personne
ON f i l m . i d r = personne . idp
WHERE personne . nom = ' von T r i e r '
AND personne . prenom = ' Lars '
On va ensuite faire une jointure entre la table des lms réalisés par von Trier et les tables personne et
jouer puis regrouper par personne et il restera à compter pour savoir dans combien de lms de von Trier
chaque acteur a joué. On teste alors l'égalité avec le nombre calculé précédemment.
SELECT personne . nom FROM
(
SELECT f i l m . i d f AS i FROM
personne JOIN f i l m
ON personne . idp = f i l m . i d r
WHERE personne . nom = ' von T r i e r '
)
JOIN j o u e r
ON j o u e r . i d f = i
JOIN personne
ON personne . idp = j o u e r . ida
GROUP BY personne . idp
HAVING COUNT( ∗ ) =
(
SELECT COUNT( ∗ ) FROM
f i l m JOIN personne
ON f i l m . i d r = personne . idp
WHERE personne . nom = ' von T r i e r '
AND personne . prenom = ' Lars '
)
Page 3
MPSI 833 TP d'informatique - 15
2 Colles
import s q l i t e 3
con = s q l i t e 3 . connect ( ' c o l l e s . s3db ' )
c = con . c u r s o r ( )
On commence par traiter un élève à la main, par exemple celui dont l'identité est 595.
Il faut déjà chercher son nom par une requête SQL :
id_eleve = 595
c . e x e c u t e ( ' ' 'SELECT nom FROM e l e v e s WHERE i d e = ' ' '+ str ( id_eleve ) )
for a in c . f e t c h a l l ( ) :
nom_eleve = a [ 0 ]
On va maintenant aller chercher ses notes de colle avec la date :
c . e x e c u t e ( ' ' 'SELECT c o l l e s . date AS d , note FROM
c o l l e s JOIN e l e v e s ON c o l l e s . i d e = e l e v e s . i d e
WHERE e l e v e s . i d e = ' ' ' + str ( id_eleve ) + ' ' ' ORDER BY d ' ' ' )
On crée son chier en écriture et on y écrit les informations obtenues par la requête :
f i c h i e r = open ( ' notes_ ' + nom_eleve + ' . t x t ' , 'w ' )
f i c h i e r . w r i t e ( ' Notes de c o l l e de ' + nom_eleve + ' : \ n\n ' )
for ( date , note ) in c . f e t c h a l l ( ) :
f i c h i e r . w r i t e ( date + ' \ t ' + str ( note ) + ' \n ' )
f i c h i e r . close ()
Il ne reste plus qu'à automatiser la chose par une fonction (ide désigne l'identité de l'élève et nom son nom que
l'on obtiendra facilement) :
def f e u i l l e ( ide , nom ) :
f i c h i e r = open ( ' notes_ ' + nom + ' . t x t ' , 'w ' )
f i c h i e r . w r i t e ( ' Notes de c o l l e de ' + nom + ' : \ n\n ' )
c . e x e c u t e ( ' ' 'SELECT c o l l e s . date AS d , note FROM
c o l l e s JOIN e l e v e s ON c o l l e s . i d e = e l e v e s . i d e
WHERE e l e v e s . i d e = ' ' ' + str ( i d e ) + ' ' ' ORDER BY d ' ' ' )
for ( date , note ) in c . f e t c h a l l ( ) :
f i c h i e r . w r i t e ( date + ' \ t ' + str ( note ) + ' \n ' )
f i c h i e r . close ()
Il ne reste plus qu'à aller chercher tous les couples (ide, nom) par une requête SQL puis à appliquer la fonction
à chacun de ces couples !
c . e x e c u t e ( ' ' 'SELECT ide , nom FROM e l e v e s ' ' ' )
for a in c . f e t c h a l l ( ) :
feuille (a [0] , a [1])
con . commit ( )
con . c l o s e ( )
Page 4
