Discussion :

[OhKerod]

Bonjour,

Je fais un TP pour mes cours et je bloque à une question:

Voici le script de la BDD:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE TABLE IF NOT EXISTS `acteur` (
  `ACT_NOM` varchar(30) NOT NULL DEFAULT '',
  `ACT_VILLE` varchar(30) DEFAULT NULL,
  `ACT_MAIL` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`ACT_NOM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Contenu de la table `acteur`
--
 
INSERT INTO `acteur` (`ACT_NOM`, `ACT_VILLE`, `ACT_MAIL`) VALUES
('AAAAAA', 'Paris', 'AAAA.mail1@free.fr'),
('BBBBBB', 'Toulouse', 'BBBB.mail1@orange.fr'),
('CCCCCC', 'Toulouse', 'CCCC.mail1@sfr.fr'),
('DDDDDD', 'Bordeaux', 'DDDD.mail1@gmail.com'),
('EEEEEE', 'LaRochelle', 'EEEE.mail1@gmail.com'),
('FFFFFFF', 'Paris', 'FFFF.mail1@free.fr'),
('GGGGG', 'Lyon', 'GGGG.mail1@gmail.com');
 
-- --------------------------------------------------------
 
--
-- Structure de la table `film`
--
 
CREATE TABLE IF NOT EXISTS `film` (
  `FILM_NUM` decimal(5,0) NOT NULL,
  `FILM_TITRE` varchar(128) DEFAULT NULL,
  `DATE_SORTIE` date DEFAULT NULL,
  `FILM_LANG` decimal(2,0) NOT NULL,
  `FILM_GENRE` decimal(1,0) NOT NULL,
  `NbEntrees` decimal(5,0) NOT NULL,
  PRIMARY KEY (`FILM_NUM`),
  KEY `FK_FILM_LANGUE` (`FILM_LANG`),
  KEY `FK_FILM_GENRE` (`FILM_GENRE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Contenu de la table `film`
--
 
INSERT INTO `film` (`FILM_NUM`, `FILM_TITRE`, `DATE_SORTIE`, `FILM_LANG`, `FILM_GENRE`, `NbEntrees`) VALUES
('1', 'Film1', '2015-06-27', '1', '5', '2000'),
('2', 'Film2', '2016-06-28', '2', '1', '5200'),
('3', 'Film3', '2017-05-15', '1', '3', '2500'),
('4', 'Film4', '2015-09-16', '3', '5', '3700'),
('5', 'Film5', '2018-05-03', '2', '3', '1600'),
('6', 'film6', '2018-05-20', '4', '2', '4900'),
('7', 'film7', '2018-09-04', '1', '4', '3600'),
('8', 'Film8', '2018-01-03', '3', '5', '1400'),
('9', 'Film9', '2018-05-15', '3', '2', '1600'),
('10', 'Film10', '2018-05-10', '3', '5', '1400'),
('11', 'Film11', '2018-09-10', '3', '2', '3500'),
('12', 'Film12', '2018-09-10', '3', '5', '4400');
 
-- --------------------------------------------------------
 
--
-- Structure de la table `genre`
--
 
CREATE TABLE IF NOT EXISTS `genre` (
  `GENRE_CODE` decimal(1,0) NOT NULL,
  `GENRE_LIBELLE` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`GENRE_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
--
-- Contenu de la table `genre`
--
 
INSERT INTO `genre` (`GENRE_CODE`, `GENRE_LIBELLE`) VALUES
('1', 'Aventure'),
('2', 'Comédie'),
('3', 'Drame'),
('4', 'Animation'),
('5', 'Policier');
 
-- --------------------------------------------------------
 
--
-- Structure de la table `jouer`
--
 
CREATE TABLE IF NOT EXISTS `jouer` (
  `JOU_FILM` decimal(5,0) NOT NULL,
  `JOU_ACT` varchar(30) NOT NULL,
  `JOU_ROLE` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`JOU_FILM`,`JOU_ACT`),
  KEY `FK_JOUER_ACTEUR` (`JOU_ACT`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Contenu de la table `jouer`
--
 
INSERT INTO `jouer` (`JOU_FILM`, `JOU_ACT`, `JOU_ROLE`) VALUES
('1', 'BBBBBB', 'secondaire'),
('1', 'DDDDDD', 'invité'),
('2', 'AAAAAA', 'secondaire'),
('2', 'DDDDDD', 'invité'),
('2', 'FFFFFFF', 'secondaire'),
('3', 'AAAAAA', 'invité'),
('3', 'EEEEEE', 'principal'),
('4', 'BBBBBB', 'principal'),
('4', 'GGGGG', 'secondaire'),
('5', 'AAAAAA', 'principal'),
('6', 'EEEEEE', 'principal'),
('7', 'AAAAAA', 'figurant'),
('8', 'BBBBBB', 'secondaire'),
('10', 'FFFFFFF', 'secondaire'),
('10', 'GGGGG', 'secondaire'),
('11', 'AAAAAA', 'principal'),
('12', 'AAAAAA', 'secondaire');
 
-- --------------------------------------------------------
 
--
-- Structure de la table `langue`
--
 
CREATE TABLE IF NOT EXISTS `langue` (
  `LANG_CODE` decimal(2,0) NOT NULL,
  `LANG_NOM` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`LANG_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Contenu de la table `langue`
--
 
INSERT INTO `langue` (`LANG_CODE`, `LANG_NOM`) VALUES
('1', 'FRANCAIS'),
('2', 'ANGLAIS'),
('3', 'ESPAGNOL'),
('4', 'ITALIEN'),
('5', 'CHINOIS'),
('6', 'RUSSE');
 
--
-- Contraintes pour les tables exportées
--
 
--
-- Contraintes pour la table `film`
--
ALTER TABLE `film`
  ADD CONSTRAINT `FK_FILM_GENRE` FOREIGN KEY (`FILM_GENRE`) REFERENCES `genre` (`GENRE_CODE`),
  ADD CONSTRAINT `FK_FILM_LANGUE` FOREIGN KEY (`FILM_LANG`) REFERENCES `langue` (`LANG_CODE`);
 
--
-- Contraintes pour la table `jouer`
--
ALTER TABLE `jouer`
  ADD CONSTRAINT `FK_JOUER_ACTEUR` FOREIGN KEY (`JOU_ACT`) REFERENCES `acteur` (`ACT_NOM`),
  ADD CONSTRAINT `FK_JOUER_FILM` FOREIGN KEY (`JOU_FILM`) REFERENCES `film` (`FILM_NUM`);

La question:

f) Liste des acteurs (nom d'acteur, email) qui ont joué dans des films ayant comme libellé genre Policier et Aventure.

(Vous pouvez utiliser ce site :http://sqlfiddle.com pour générer la BDD et faire les requetes si besoin.)

J'ai essayé plein de combinaison possible pour essayer de récupérer les données qui m'intéressent mais en vain.

Merci infiniment

Paul

[tatayo]

Bonjour,
Par principe on ne donne pas la solution à des exercices sur le forum.
Tu peux nous montrer ce que tu as tenté, ainsi on pourra te donner des pistes pour avancer.
Je peux tout de même te donner quelques indices.
D'après l'énoncé, un film n'a qu'un genre.
Donc tu peux commencer par chercher les genres ayant pour libellé "Policier" ou "Aventure".
En partant de cette requête, tu cherches les films dont le genre a pour libelle "Policier" ou "Aventure". Il suffit d'ajouter une jointure.
Puis, une fois que tu as cette requête, tu ajoutes une dernière jointure pour récupérer les acteurs de ces films.
Et pour finir, tu revois la partie SELECT pour ne renvoyer que les données demandées dans l'exercice.

Tatayo.

[OhKerod]

Merci j'ai finalement trouvé. J'étais sur la bonne piste. Mais tes indices m'ont bien aidé faut croire.

MERCI

[OhKerod]

Je suis à nouveau bloqué, le début du TP était facile mais plus j'avance...:/

Il me demande la langue qui a le moins de films associés.

Ce code me semble un bon début, il me retourne le nombre de films associé pour chaque langue

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
 
select COUNT(l.lang_nom) as Total ,l.lang_nom from film f
join langue l on l.lang_code=f.film_lang
group by l.lang_nom

donc il me reste qu'à prendre le minimum mais c'est là que ça coince. J'ai essayé ça mais il me dit que toutes mes tables dérivés doivent avoir leur propre alias...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
select MIN(Total),l.lang_nom from (
select COUNT(l.lang_nom) as Total ,l.lang_nom from film f
join langue l on l.lang_code=f.film_lang
group by l.lang_nom)

Un ptit coup de pouce svp serait le bienvenue !

[tatayo]

Là il manque juste un alias pour la sous-requête. Tu as une autre erreur dans le SELECT, mais MySQL va te le signaler.

MAIS. Et oui, il y a un MAIS.
Je pense que MySQL va accepter la requête, mais d'autres SGBDs seraient moins conciliant. Tu as une fonction d'agrégation dans le SELECT (celui de la requête principale), mais pas de GROUP BY.
Toutes les colonnes d'un SELECT qui ne font pas l'objet d'une agrégation DOIVENT se trouver dans le GROUP BY. Seul MySQL laisse passer.

Tatayo.

[OhKerod]

Non là j'ai beau tout essayer je vois pas ou sont mes erreurs, je vois pas ou il manque l'alias. Tu pourrais être plus spécifique stp merci ?
(et mysql me signale rien du tout à part pour l'alias manquant)

[tatayo]

Il faut un alias pour la sous-requête:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
select s.col1,s.col2 from
(
select …
) as s

Dans la requête principale tu ne peux pas utiliser une table de la sous-requête, l.lang n'est donc pas valide. C'est l'autre erreur dont je parlais.
MySQL ne l'a pas (encore) signalée, car il s'est arrêté à la première erreur trouvée: l'alias manquant.

Tatayo.

[OhKerod]

Merci de ton aide.
J'avance mais ça bloque encore, j'arrive pas à lui dire de sélectionner la valeur la plus petit qu'il a calculée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
select l.lang_nom from (
select COUNT(l.lang_nom) as Total ,l.lang_nom from film f
join langue l on l.lang_code=f.film_lang
group by l.lang_nom) as l
where Total=MIN(Total)

Invalid use of group function
ça vient du where mais je sais pas quoi faire d'autre...

[tatayo]

Tu peux trier le résultat sur la colonne total, et ne prendre que la première ligne.

Tatayo.

[OhKerod]

Merci. Voici mon code si ça peut en aider d'autres.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select l.lang_nom from (
select COUNT(l.lang_nom) as Total ,l.lang_nom from film f
join langue l on l.lang_code=f.film_lang
group by l.lang_nom
order by COUNT(l.lang_nom)
LIMIT 1) as l

[OhKerod]

Rebonjour,
Comment je peux faire ça dans une requête:

J'ai une requête qui me renvoie A,B,C,D,E et une autre qui me renvoie A et C.
Je veux soustraire la deuxième à la première et obtenir B,D,E.
J'ai essayé avec un LEFT JOIN.

Concrètement

Je veux récupérer le nom des acteurs qui n'ont jamais joué de role secondaire donc j'ai fait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select distinct a.act_nom from acteur a
LEFT JOIN jouer j on j.jou_act=a.act_nom
where j.jou_role NOT LIKE 'Secondaire'

mais c'est faux :/

Merci de ton aide

[tatayo]

Bonjour,
Si tu mets un critère sur la table de droite d'un LEFT OUTER JOIN dans le WHERE, tu transformes de fait la jointure externe en jointure interne.
Une piste: remets le critère (le not like) dans la jointure, mets un SELECT * (je sais, c'est mal mais ici c'est justifié) et regarde le résultat de la requête.
Tu devrais trouver rapidement ce qu'il faut ajouter et modifier pour avoir le bon résultat.

Le but ici n'est pas de chercher ceux qu'il ont un rôle différent de secondaire, mais qui n'ont pas de rôle secondaire. Ce n'est pas tout à fait la même chose.

Tatayo.

[OhKerod]

La table de droite c'est bien la table acteur dans mon code ?

[tatayo]

Bha non:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
select 1
from Table1 LEFT OUTER JOIN Table2 on …

Pour moi la table de gauche est à gauche (donc Table1), et la table de droite, à droite (donc Table2).

Tatayo.

[escartefigue]

bonjour

Dans une jointure OUTER quand LEFT ou RIGHT est précisé il qualifie la table ou la vue dont le nom précède le mot clef LEFT ou RIGHT,
par exemple dans la requête qui suit, TABLE1 est la table de gauche et TABLE2 est la table OUTER

SELECT [...] FROM TABLE1 LEFT OUTER JOIN TABLE2 .

Et, dans la requête qui suit, c'est TABLE1 qui est à droite, et TABLE2 qui est la table OUTER
SELECT [...] FROM TABLE1 RIGHT OUTER JOIN TABLE2

[OhKerod]

Merci pour vos réponses mais j'ai beau essayé, je comprends pas. Mettre la condition where dans le join ? Je vois pas du tout comment ...


et la dernière question avec laquelle je bloque un ptit peu : on me demande de trouver quel acteur a jouer dans tous les genres de films avec le code en dessous ça renvoit bien l'acteur en question sauf que j'ai un peu triché car j'ai mis la valeur numérique du nombre de genre existant. J'arrive pas à récupérer cette valeur. J'ai essayé en rajoutant un join sur la table genre et en faisant COUNT (g.genre_code) from genre g. Mais j'arrive pas à l'intégrer sans avoir des erreurs.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
select a.act_nom from acteur a
join jouer j on j.jou_act=a.act_nom
join film f on f.film_num=j.jou_film
group by a.act_nom
having COUNT(distinct f.film_genre)=5

Merci.

[escartefigue]

Tu peux trier le résultat sur la colonne total, et ne prendre que la première ligne.

Tatayo.

Attention, à moins que ça ait changé avec MySQL V8, jusqu'aux versions antérieures, l'utilisation de LIMIT n'a d'impact que sur la restitution et n'empêche pas le moteur du SGBD de parcourir toutes les occurrences au détriment des perfs bien sur :/

Voici un exemple de solution possible, ici j'utilise des CTE pour éviter de créer les tables mais peu importe

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
with LA_LANG(LA_id, LA_lib) as -- table des langues
   (select 001, 'français'        
    union all
    select 002, 'espagnol'        
    union all
    select 003, 'allemand'          
    union all
    select 004, 'swahili'         
   )
  , FI_FILM(FI_id, FI_titre, LA_id) as -- table des films
   (select 001, 'le grand bleu     ', 001   
    union all
    select 002, 'cuándo comemos    ', 002   
    union all
    select 003, 'les bronzés       ', 001   
    union all
    select 004, 'wann essen wir    ', 003   
    union all
    select 005, 'viva España       ', 002   
    union all
    select 006, 'Léon              ', 001   
    union all
    select 007, 'Imekwenda vibaya  ', 004   
   )
  , XC_COUN(LA_id, XC_nbr) as -- nombre de films par langue
   (select LA_id
         , count(FI_id)
    from fi_film
    group by LA_id)
select LA.LA_id -- résultat 
     , LA.LA_lib
     , XC.XC_nbr
from LA_LANG LA
inner join XC_COUN XC
   on XC.LA_id=LA.LA_id
where not exists
     (select 1
      from xc_coun x2
      where x2.XC_nbr < XC.XC_nbr)

On obtient

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
LA_ID        LA_LIB    XC_NBR       
------------------------------------
3            allemand  1            
4            swahili   1