Bonjour,

Pour un projet à mon IUT Informatique, on doit représenter des requêtes SQL sous forme d'algèbre relationnel (des arbres) et faire les requêtes. Or là, ça fait toute une aprem et une partie de la soirée que je bloque sur la question 2 du sujet. Les TD et les cours ne m'aident vraiment pas.

Voilà le sujet : https://www.mediafire.com/file/v15byhkysc8cspc/Projet_BdD_2018_2019_p2_S2.pdf/file

Et voici le jeu d'essai qu'on utilise : https://docs.google.com/spreadsheets/d/1BwjjDMwedRuXr3PEprgONqa7h0PMC836UbPP3-jVano/edit?usp=sharing

Donc, on a commencé à par faire l'arbre : https://photos.app.goo.gl/Fc5N6UkDP8oMKgGA9

CA c'est notre essai final, au départ on avait d'autres choses qui ne fonctionnait pas vraiment plus... (Ce qui est entouré en rouge c'est la partie qu'on n'arrive pas à faire, le reste c'est normalement bon).

A la base on avait ça : https://photos.app.goo.gl/RZLbQ4wGBctN6r868 ce qui me semble + juste, mais bon vu que la requête ne fonctionne pas comme ça.

Du coup, voici la requête qu'on essaye de faire : 

SELECT nom_ens, prenom_ens
FROM enseignant, jury, soutenance
WHERE enseignant.id_ens=jury.id_ens_pres AND enseignant.id_ens=jury.id_ens AND enseignant.id_ens=jury.id_ens_exp
AND jury.no_jury=soutenance.no_jury
AND note IN (
    SELECT MAX(AVG(note))
    FROM soutenance
    )

La requête correspond à la deuxième photo.

Avec cette requête on se prend une erreur du type : #1111 - Utilisation invalide de la clause GROUP

On a essayé pas mal de trucs, même des trucs qui ne sont pas dans nos cours. (CàD : mettre une requête (SELECT...FROM) dans le FROM de la requête principal, ça fonctionne pas + et comme on ne l'a pas vu en cours, je doute qu'on soit autorisé à utiliser ça.

Voilà, merci de votre aide !

Bonjour,

Je ne pense pas que l'on puisse utiliser deux fonctions d'agrégat en même temps (ici MAX et AVG), d'où l'erreur ...

Ensuit la requête ne correspond pas à l'énoncé :

L'énoncé dit :

Quels enseignants composaient le jury ayant attribué la meilleure moyenne de notes en 2018 ?

Selon moi, il faut d'abord calculer toutes les moyennes par jury, puis calculer le maximum :

-- Moyenne maximale
SELECT MAX( moyenne ) AS max_moy
FROM (
	-- Moyenne des notes par jury
	SELECT S.no_jury, AVG( S.note ) AS moyenne
	FROM soutenance S
	GROUP BY S.no_jury
)

Le problème ici c'est qu'il est possible que plusieurs jurys aient la même moyenne (max_moy) alors comment déterminer lequel choisir ?

Bon ... une fois cela dit, la suite :

-- Les jurys ayant la meilleure moyenne
SELECT S.no_jury
FROM soutenance S
GROUP BY S.no_jury
HAVING AVG( note ) = (
	SELECT MAX( moyenne ) AS max_moy
	FROM (
		-- Moyenne des notes par jury
		SELECT S.no_jury, AVG( S.note ) AS moyenne
		FROM soutenance S
		GROUP BY S.no_jury
	)
)

Là cette requête donne la liste des jurys ayant donné la meilleure moyenne ... il manque juste à rajouter les professeurs associés, cela avec une jointure par enseignant (trois jointures donc utilisation d'alias) :

SELECT
	S.no_jury,
	EP.nom_ens AS nom_pres,
	EP.prenom_ens AS prenom_pres,
	E.nom_ens AS nom_ens,
	E.prenom_ens AS prenom_ens, 
	EE.nom_ens AS nom_exp,
	EE.prenom_ens AS prenom_exp, 
FROM
	soutenance S
		INNER JOIN jury J
			ON S.no_jury = J.no_jury
		INNER JOIN enseignant EP
			ON J.id_ens_pres = EP.id_ens
		INNER JOIN enseignant E
			ON J.id_ens = EP.id_ens
		INNER JOIN enseignant EE
			ON J.id_ens_exp = EP.id_ens
GROUP BY
	S.no_jury,
	EP.nom_ens,
	EP.prenom_ens,
	E.nom_ens,
	E.prenom_ens, 
	EE.nom_ens,
	EE.prenom_ens
HAVING AVG( note ) = (
	SELECT MAX( moyenne ) AS max_moy
	FROM (
		-- Moyenne des notes par jury
		SELECT S.no_jury, AVG( S.note ) AS moyenne
		FROM soutenance S
		GROUP BY S.no_jury
	)
)

Ainsi ça devrait le faire ...

Par contre le modèle est vraiment mal fait ... Si un jour un décide de rajouter ou supprimer des enseignants dans le jury il faudra modifier le modèle ... pas glop ... enfin ... ce n'est pas la question ...

-
Edité par Benzouye 7 février 2019 à 10:46:35

(C'est moi, je me suis trompé de compte...)

Hello,

Merci pour la réponse, j'ai quand même un soucis, ton code fonctionne en récupérant le maximum de la moyenne de TOUT ! Or, j'ai passé toute ma soirée hier a essayé de le modifier pour faire la moyenne des jurys de 2018. Le truc c'est que, soit il me fait la moyenne de tout les jurys de l'année 2018, or il faut que ce soit tout les même jurys qui l'ont fait en 2018 pour comparer de qui entre J7, J8 et J9 (jury de 2018) qui a la meilleur moyenne de note.

Donc j'ai réussit à faire la moyenne de toutes les notes de 2018, mais c'est pas ça qu'il faut.. Franchement, je suis perdu, car c'est un truc qu'on a pas fait en cours (ou pas encore), or j'aimerais bien avancer sur ce projet.

 -- Les jurys ayant la meilleure moyenne
SELECT S.no_jury
FROM soutenance S, jury
WHERE YEAR(date_reunion) = 2018
AND jury.no_jury=S.no_jury
GROUP BY S.no_jury
HAVING AVG( note ) = (
    SELECT MAX( moyenne ) AS max_moy
    FROM (
        -- Moyenne des notes par jury
        SELECT S.no_jury, AVG( S.note ) AS moyenne
        FROM soutenance S, jury
        GROUP BY S.no_jury
    ) AS moyenne
)

Voilà, j'ai rajouté la condition date_reunion, mais du coup, il me renvoie un résultat vide si c'est pas 2017. Je ne comprend pas bien la logique du truc...

(Par contre, tu m'as fait comprendre comment afficher les profs d'un coup, je ne savais pas qu'on pouvait faire ça avec AS (on l'a jamais vu en cours) donc merci !)

-
Edité par Deewens 8 février 2019 à 11:12:01

Deewens a écrit:

faire la moyenne des jurys de 2018

Il suffit de rajouter la condition de date dans la sous-requête également :

SELECT
	S.no_jury,
	EP.nom_ens AS nom_pres,
	EP.prenom_ens AS prenom_pres,
	E.nom_ens AS nom_ens,
	E.prenom_ens AS prenom_ens,
	EE.nom_ens AS nom_exp,
	EE.prenom_ens AS prenom_exp,
FROM
	soutenance S
		INNER JOIN jury J
			ON S.no_jury = J.no_jury
		INNER JOIN enseignant EP
			ON J.id_ens_pres = EP.id_ens
		INNER JOIN enseignant E
			ON J.id_ens = EP.id_ens
		INNER JOIN enseignant EE
			ON J.id_ens_exp = EP.id_ens
WHERE YEAR( J.date_reunion ) = 2018
GROUP BY
	S.no_jury,
	EP.nom_ens,
	EP.prenom_ens,
	E.nom_ens,
	E.prenom_ens,
	EE.nom_ens,
	EE.prenom_ens
HAVING AVG( note ) = (
	SELECT MAX( moyenne ) AS max_moy
	FROM (
		-- Moyenne des notes par jury en 2018
		SELECT S.no_jury, AVG( S.note ) AS moyenne
		FROM
			soutenance S
				INNER JOIN jury J
					ON S.no_jury = J.no_jury
		WHERE YEAR( J.date_reunion ) = 2018
		GROUP BY S.no_jury
	)
)

Okay, ça fonctionne, merci du coup de main... Reste plus qu'a trouver comment le matérialiser sous forme d'arbre algébrique :/ !

Juste une question, plus haut tu avais dit :

"Par contre le modèle est vraiment mal fait ... Si un jour un décide de rajouter ou supprimer des enseignants dans le jury il faudra modifier le modèle ... pas glop ... enfin ... ce n'est pas la question ..."

Que veux-tu dire par "le modèle"? C'est ce qu'on nous a donné comme consigne de départ au Semestre 1 (on avait un projet sur cette même base au premier Semestre et c'est là qu'on nous avait donné les consignes pour la faire comme ça la base.).

Le modèle c'est la structure des tables et leurs colonnes ...

Dans ton exemple de données, un jury est composé de 3 enseignants représentés par 3 clés étrangères vers la table enseignants dans la table jury.

Cela implique qu'il n'y ai aucune variation dans le jury, toujours 3 enseignants et toujours dans ces rôles (pres, ens et exp, président, expert et simple je suppose). On aurait pu imaginer une relation n,n entre jury et enseignant donc une table intermédiaire avec une colonne rôle ... Ainsi l'on pourrait stocker 1, 2, 3, n enseignants dans un même jury ... mais c'est finalement un détail ici ...

Hello, c'est encore moi et j'ai encore des soucis, sur la question 4 cette fois.

Ca fait des semaines que je n'arrive pas à le faire ce projet d'ailleurs, j'essaye d'éviter de demander, mais là vraiment, j'arrive à un point ou je n'arrive pas à faire quoi que ce soit (et les profs de l'IUT aident pas du tout, m'enfin bon) et surtout, la date de rendu approche à grand pas !

La question 4 est simple en soit, le soucis c'est qu'il faut récupérer le "cas 0", c'est à dire (de ce que je comprend), il faut que pour les enseignants qui n'ont participé à aucun jury, il y ait écrit COUNT(id_ens) : 0, or je n'y arrive pas.

Je suis obligé de placer une condition dans WHERE qui ne récupère que les jurys qui ont participé à des soutenances, or si je fais ça, le COUNT ne compte évidemment pas les jurys qui n'ont pas fait, et donc ne m'affiche pas le CAS 0, il les enlèves du résultat !

SELECT enseignant.id_ens, COUNT(enseignant.id_ens)
FROM jury, enseignant, soutenance
WHERE (YEAR(date_reunion) = 2017 OR YEAR(date_reunion) = 2018)
AND jury.no_jury=soutenance.no_jury
AND (enseignant.id_ens=jury.id_ens_pres OR enseignant.id_ens=jury.id_ens OR enseignant.id_ens=jury.id_ens_exp)
GROUP BY enseignant.id_ens

C'est ce que j'avais fait au départ, sauf qu'évidemment le cas 0 n'est pas récupéré puisqu'il Séléctionne à condition que no_jury de jury soit présente dans soutenance, donc forcément les enseignants qui n'ont participé à aucun jury ne sont pas sélectionné.

A moins que je ne comprenne pas la question, mais pour moi, "A combien de jury chaque enseignant à t'il participé ?" a combien de jury, pour moi "jury" c'est le nombre de soutenance que tel jury à fait... Ca se trouve je me trompe complètement, mais bon.

M'voilà, merci.

Bonjour,

Tu aurais dû créer un autre sujet pour ta nouvelle question ... mais passons ...

LordHawk a écrit:

le cas 0 n'est pas récupéré puisqu'il Séléctionne à condition que no_jury de jury soit présente dans soutenance, donc forcément les enseignants qui n'ont participé à aucun jury ne sont pas sélectionné

C'est l'intérêt des jointures externes que de rendre "facultatives" les conditions de jointure.

Exemple avec LEFT JOIN :

SELECT E.id_ens, COUNT(*) AS nb_jury
FROM
	enseignant E
		LEFT JOIN jury J
			ON E.id_ens=J.id_ens_pres
			OR E.id_ens=J.id_ens
			OR E.id_ens=J.id_ens_exp
WHERE
	YEAR(J.date_reunion) = 2017
	OR YEAR(J.date_reunion) = 2018
GROUP BY E.id_ens

Par contre dans le jeu de données de départ, il y 10 enseignants et tous apparaissent dans la table jury ... donc aucun cas 0 ...

-
Edité par Benzouye 11 mars 2019 à 8:50:56

Ah desolé, je crée un nouveau sujet la prochaine fois...

Merci pour ta rep. Le truc c'est qu'a l'IUT on a jamais vu cette façon de faire les jointures. (Left join, etc) seulement faire un = entre deux attributs.

J'essaie ça tout a a l'heure quand même.

C'est une syntaxe un peu vieillote, même si elle fonctionne quand même.

Pour faire avec cette syntaxe :

SELECT E.id_ens, COUNT(*) AS nb_jury
FROM enseignant E, jury J
WHERE
	(
		YEAR(J.date_reunion) IN ( 2017, 2018 )
		AND (
			E.id_ens=J.id_ens_pres
			OR E.id_ens=J.id_ens
			OR E.id_ens=J.id_ens_exp
		)
	)
	OR J.id_jury IS NULL
GROUP BY E.id_ens

Super, j'ai réussi à finir le projet pratiquement, merci beaucoup pour ton aide !

Je met en résolu !