1- Liste des coureurs entraînés par WEBER.
(num Coureur, nom coureur, ville)

SELECT
	NumCoureur
	, NomCoureur
	, VilleCoureur
FROM
	Coureur, Entraineur
WHERE
	Coureur.NumEntr = Entraineur.NumEntr
AND
	Entraineur.NomEntr LIKE 'WEBER'
;

SELECT
	NumCoureur
	, NomCoureur
	, VilleCoureur
FROM
	Coureur
INNER JOIN
	Entraineur
ON
	Entraineur.NumEntr = Coureur.NumEntr
WHERE
	Entraineur.NomEntr LIKE 'WEBER'
;



2- Qui a gagné la "course du Lion" du 12/09/04 ?
(nom coureur, ville)

SELECT 
	Coureur.NomCoureur
	, Coureur.VilleCoureur
FROM
	Coureur
	, Participe
	, Course
WHERE
	Coureur.NumCoureur = Participe.NumCoureur
AND
	Participe.NumCourse = Course.NumCourse
AND
	Course.DateCourse = '12/09/04'
AND
	Participe.Ordre = 1
;

SELECT 
	Coureur.NomCoureur
	, Coureur.VilleCoureur
FROM
	Coureur
INNER JOIN
	Participe
ON
	Participe.NumCoureur = Coureur.NumCoureur
INNER JOIN
	Course
ON
	Course.NumCourse = Participe.NumCourse
WHERE
	Course.DateCourse = '12/09/04'
AND
	Participe.Ordre = 1
	
;


3- Liste des participants à la "course du Lion" du 12/09/04,
classée par ordre croissant d'ordre d'arrivée. (nom
coureur, ordre).

SELECT 
	Coureur.NomCoureur
	, Participe.Ordre
FROM
	Coureur
	, Participe
	, Course
WHERE
	Coureur.NumCoureur = Participe.NumCoureur
AND
	Participe.NumCourse = Course.NumCourse
AND
	Course.DateCourse = '12/09/04'
ORDER BY
	Participe.Ordre
;

SELECT 
	Coureur.NomCoureur
	, Participe.Ordre
FROM
	Coureur
INNER JOIN
	Participe
ON
	Participe.NumCoureur = Coureur.NumCoureur
INNER JOIN
	Course
ON
	Course.NumCourse = Participe.NumCourse
WHERE
	Course.DateCourse = '12/09/04'
ORDER BY
	Participe.Ordre
;


4- A quelles courses ont participé les coureurs entraînés par
WEBER ?
(libellé course, date, ville, n° coureur)


SELECT
	Course.LibCourse
	, Course.DateCourse
	, Course.VilleCourse
	, Participe.NumCoureur
FROM
	Course
	, Participe
	, Entraineur
	, Coureur
WHERE
	Course.NumCourse = Participe.NumCourse
AND
	Participe.NumCoureur = Coureur.NumCoureur
AND
	Coureur.NumEntr = Entraineur.NumEntr
AND
	Entraineur.NomEntr LIKE 'WEBER'
;		


SELECT
	Course.LibCourse
	, Course.DateCourse
	, Course.VilleCourse
	, Participe.NumCoureur
FROM
	Course
INNER JOIN
	Participe
ON
	Participe.NumCourse = Course.NumCourse
INNER JOIN
	Coureur	
ON
	Coureur.NumCoureur = Participe.NumCoureur
INNER JOIN
	Entraineur
ON
	Entraineur.NumEntr = Coureur.NumEntr
WHERE
	Entraineur.NomEntr LIKE 'WEBER';





5- Liste des coureurs qui ont déja gagné une course et nom de
leur entraîneur.
(nom coureur, libellé course, date course, nom
entraîneur).


SELECT
	Coureur.NomCoureur
	, Course.LibCourse
	, Course.DateCourse
	, Entraineur.NomEntr
FROM
	Coureur
	, Course
	, Entraineur
	, Participe
WHERE
	Coureur.NumEntr = Entraineur.NumEntr
AND
	Coureur.NumCoureur = Participe.NumCoureur
AND
	Course.NumCourse = Participe.NumCourse
AND
	Participe.Ordre = 1
;


SELECT
	Coureur.NomCoureur
	, Course.LibCourse
	, Course.DateCourse
	, Entraineur.NomEntr
FROM
	Coureur
INNER JOIN
	Entraineur
ON
	Entraineur.NumEntr = Coureur.NumEntr
INNER JOIN
	Participe
ON
	Participe.NumCoureur = Coureur.NumCoureur
INNER JOIN
	Course
ON
	Course.NumCourse = Participe.NumCourse
WHERE
	Participe.Ordre = 1
;






6- Pour chaque coureur donnez son adresse et pour ceux qui ont
couru, la liste des courses auxquelles ils ont participé.
(numcoureur, ville, numcourse,libcourse)



SELECT
	Coureur.NumCoureur
	, Coureur.VilleCoureur
	, Participe.NumCourse
	, Course.LibCourse
FROM
	Coureur
FULL OUTER JOIN
	Participe
ON
	Participe.NumCoureur = Coureur.NumCoureur
FULL OUTER JOIN
	Course
ON
	Participe.NumCourse = Course.NumCourse
;




7- Quels sont les courses qui se sont déroulées dans la ville
où habite DUPONT (Libellé course, date course)

SELECT
	Course.LibCourse
	, Course.DateCourse
FROM
	Course
WHERE
	VilleCourse LIKE (SELECT VilleCoureur FROM Coureur WHERE NomCoureur LIKE 'DUPONT')
;




8- Liste des coureurs qui sont entraînés par FANNIER et qui
n’ont jamais couru.
(nomcoureur) -- Utilisation du not exists



SELECT
	Coureur.NumCoureur
	, Coureur.NomCoureur
FROM
	Coureur
WHERE
	NOT EXISTS
	(SELECT 
		Participe.NumCoureur
	 FROM
		Participe
	INNER JOIN
		Coureur
	ON
		Participe.NumCoureur = Coureur.NumCoureur
	INNER JOIN
		Entraineur
	ON
		Coureur.NumEntr = Entraineur.NumEntr
	WHERE
		Entraineur.NomEntr LIKE 'FANNIER')
;
	

Note : Utilisation de NOT IN ici / Echec des utilisations de NOT EXISTS

SELECT 
	NomCoureur
FROM 
	ENTRAINEUR 
INNER JOIN 
	COUREUR 
USING (NUMENTR)
WHERE 
	NOMENTR='FANNIER'
AND NOT EXISTS (
		SELECT 
			NUMCOUREUR
		FROM 
			PARTICIPE
		WHERE 
			PARTICIPE.NUMCOUREUR=COUREUR.NUMCOUREUR
);

9- 

SELECT 
	NOMCOUREUR
FROM 
	COUREUR 
INNER JOIN 
	PARTICIPE 
USING (NUMCOUREUR)
INNER JOIN 
	COURSE 
USING (NUMCOURSE)
WHERE 
	NUMCOURSE='18' 
AND 
	ORDRE < (SELECT 
			ORDRE 
		FROM 
			PARTICIPE 
		INNER JOIN 
			COUREUR 
		USING(NUMCOUREUR) 
		WHERE 
			NUMCOURSE = '18' 		AND 
			NOMCOUREUR = 'ALBERT');

10- 

SELECT 
	NOMCOUREUR
	, NOMENTR
FROM 
	ENTRAINEUR 
INNER JOIN 
	COUREUR 
USING (NUMENTR) 
WHERE 
	VILLECOUREUR = 'BELFORT' 
OR 
	NOMENTR = 'WEBER';

11- 
SELECT 
	NOMCOUREUR
FROM 
	COUREUR
WHERE 
	NOMCOUREUR NOT IN (
				SELECT 
					NOMCOUREUR
				FROM 
					COUREUR 
				INNER JOIN 
					PARTICIPE 
				USING (NUMCOUREUR)
 				INNER JOIN 
					COURSE 
				USING (NUMCOURSE)
				WHERE 
					LIBCOURSE='Course du Lion' 
				AND 
					DATECOURSE LIKE '%05');

12- 
SELECT 
	NOMCOUREUR
FROM 
	ENTRAINEUR 
INNER JOIN 
	COUREUR 
USING (NUMENTR) 
WHERE 
	VILLECOUREUR='BELFORT' 
AND 
	NOMENTR='DUBROCK'
UNION
SELECT 
	NOMCOUREUR
FROM 
	ENTRAINEUR 
INNER JOIN 
	COUREUR 
USING (NUMENTR) 
WHERE 
	VILLECOUREUR='MONTBELIARD' 
AND 
	NOMENTR='DUBROCK';

13- SELECT 
	NOMCOUREUR
	, LIBCOURSE
FROM 
	COUREUR 
INNER JOIN 
	PARTICIPE 
USING (NUMCOUREUR)
INNER JOIN 
	COURSE 
USING (NUMCOURSE)
WHERE 
	DATECOURSE='17/01/05'
UNION
SELECT 
	NOMCOUREUR
	, LIBCOURSE
FROM 
	COUREUR 
INNER JOIN 
	PARTICIPE 
USING (NUMCOUREUR)
INNER JOIN 
	COURSE 
USING (NUMCOURSE)
WHERE 
	DATECOURSE='25/01/05';

14- 
SELECT 
	NOMCOUREUR
FROM 
	COUREUR
MINUS
SELECT 
	NOMCOUREUR
FROM 
	COUREUR 
INNER JOIN 
	PARTICIPE 
USING (NUMCOUREUR)
INNER JOIN 
	COURSE 
USING (NUMCOURSE)
WHERE 
	DATECOURSE='17/01/05';

15- 
SELECT 
	NOMCOUREUR
FROM 
	COUREUR 
INNER JOIN 
	PARTICIPE 
USING (NUMCOUREUR)
INNER JOIN 
	COURSE 
USING (NUMCOURSE)
WHERE 
	DATECOURSE='17/01/05';
INTERSECT
SELECT 
	NOMCOUREUR
FROM 
	COUREUR 
INNER JOIN 
	PARTICIPE 
USING (NUMCOUREUR)
INNER JOIN 
	COURSE 
USING (NUMCOURSE)
WHERE 
	DATECOURSE='25/01/05';

16- 
SELECT 
	NUMCOUREUR
	, NOMCOUREUR
FROM 
	ENTRAINEUR 
INNER JOIN 
	COUREUR 
USING (NUMENTR) 
WHERE 
	VILLEENTR='BELFORT' 
AND 
	(NUMCOUREUR='2' 
	OR NUMCOUREUR='7' 
	OR NUMCOUREUR='8');

17-
Liste des coureurs qui ont gagné toutes les courses auxquelles ils ont participé

SELECT DISTINCT
	NomCoureur
FROM
	Coureur
INNER JOIN
	Participe
USING(NumCoureur)
WHERE
	NumCoureur
NOT IN
	(SELECT 
		NumCoureur 
	FROM 
		Participe
	WHERE
		Ordre != 1);





18-
Noms des coureurs qui ont participé exactement aux memes courses que martin

selection de tous les coureurs ayant couru aux cotés de Martin

SELECT DISTINCT
	NomCoureur
FROM
	Coureur
INNER JOIN
	Participe
USING (NumCoureur)
WHERE
	NumCourse IN (SELECT 
				NumCourse
			FROM
				Participe
			INNER JOIN
				Coureur
			USING(NumCoureur)
			WHERE
				NomCoureur = 'MARTIN')
AND
	NomCoureur != 'MARTIN'
ORDER BY 
	NomCoureur;



19-
SELECT 
	NOMENTR
	,COUNT(NUMENTR)
FROM 
	ENTRAINEUR 
INNER JOIN 
	COUREUR 
USING (NUMENTR) 
GROUP BY 
	NOMENTR
HAVING 
	COUNT(NUMENTR) > 2;

20- 
SELECT 
	NOMENTR
	, COUNT(*)
FROM 
	ENTRAINEUR 
INNER JOIN 
	COUREUR 
USING (NUMENTR)
GROUP BY 
	NOMENTR
UNION
SELECT 
	VILLECOUREUR
	, COUNT(*)
FROM 
	ENTRAINEUR 
INNER JOIN 
	COUREUR 
USING (NUMENTR)
GROUP BY 
	VILLECOUREUR

21- 
SELECT 
	VILLEENTR
	, AVG(SALAIREENTR)
FROM 
	ENTRAINEUR
GROUP BY 
	VILLEENTR
ORDER BY 
	VILLEENTR

22- 
SELECT 
	NOMCOUREUR
	, COUNT(*)
FROM 
	COUREUR 
INNER JOIN 
	PARTICIPE 
USING (NUMCOUREUR)
INNER JOIN 
	COURSE 
USING (NUMCOURSE)
WHERE 
	ORDRE < 4
GROUP BY 
	NOMCOUREUR


23- Liste des coureurs qui ont déja participé à plus de 2
courses.
(nom coureur, nbcourses)

SELECT 
	NomCoureur
	, COUNT(NumCourse) AS NbCourse
FROM
	Coureur
INNER JOIN
	Participe
USING (NumCoureur)
HAVING 
	COUNT(NumCourse) >= 2
GROUP BY
	NomCoureur


24- Nombre de participants par course disputée à Belfort après
le 18/01/05 (numérocourse, libellé course, nbparticipants)

SELECT
	Numcourse
	, LibCourse
	, COUNT(NumCoureur) AS NbParticipant
FROM
	Course
INNER JOIN
	Participe
USING (NumCourse)
WHERE
	VilleCourse = 'BELFORT'
AND
	DateCourse > '18/01/05'
GROUP BY
	NumCourse
	, LibCourse;


25- Courses dans lesquelles ont participé plus de 5 coureurs.
(libellé course, nbcoureurs)

SELECT
	LibCourse
	, COUNT(NumCoureur) AS NbCoureurs
FROM
	Course
INNER JOIN
	Participe
USING (NumCourse)
HAVING COUNT(NumCoureur) >= 5
GROUP BY
	NumCourse
	, LibCourse;


26- Liste des coureurs qui habitent Belfort et qui ont
participé à plus de 2 courses dans cette ville.
(numcoureur, nomcoureur)

SELECT
	NumCoureur
	, NomCoureur
FROM
	Coureur
WHERE
	VilleCoureur = 'BELFORT'
AND
	



27- Quels sont les entraîneurs qui gagnent plus de 7000F et
qui entraînent 3 coureurs ? (nomentraîneur, salaire,
nbcoureurs)


SELECT
	NomEntr
	, SalaireEntr
	, COUNT(NumCoureur) AS NbCoureur
FROM
	Entraineur
INNER JOIN
	Coureur
ON
	Coureur.NumEntr = Entraineur.NumEntr
WHERE
	SalaireEntr >= 7000
HAVING COUNT(NumCoureur) = 3
GROUP BY
	NomEntr
	, SalaireEntr


Sans group by ?


28- Nombre de coureurs qui n’ont pas encore couru.

SELECT
	COUNT(NumCoureur) AS NbCoureur
FROM
	Coureur
WHERE
	NumCoureur NOT IN (SELECT NumCoureur FROM Participe)
;


29- Quel est le nombre d’entraîneurs qui habitent Belfort et
qui entraînent plus de 2 coureurs différents.
(nbentraîneur) Rédiger une requête avec un group by
Rédiger une requête sans group by



SELECT
	COUNT(NumEntr) AS NbEntraineur
FROM
	Entraineur
WHERE
	VilleEntr = 'BELFORT'
AND
	NumEntr IN (	SELECT 
				NumEntr 
		    	FROM 
				Entraineur
			INNER JOIN
				Coureur
			USING(NumEntr)
			HAVING COUNT(NumCoureur) > 2
			GROUP BY
				NumEntr
		   )


30- Liste des coureurs, avec pour ceux qui ont couru, le nombre
de courses auxquelles ils ont participé.
(NomCoureur, Nbcourses)


SELECT
	NomCoureur
	, COUNT(NumCourse) AS NbCourses
FROM
	Coureur
FULL OUTER JOIN
	Participe
ON
	Participe.NumCoureur = Coureur.NumCoureur
GROUP BY
	NomCoureur



31- Noms des coureurs qui ont participé à au moins une course à
laquelle a participé ‘Martin’. Autojointure ou sous-requête

SELECT DISTINCT
	NomCoureur
FROM 
	Coureur
INNER JOIN
	Participe
ON
	Participe.NumCoureur = Coureur.NumCoureur
WHERE
	Participe.NumCourse IN (	SELECT 
						NumCourse
					FROM
						Participe
					INNER JOIN
						Coureur
					USING (NumCoureur)
					WHERE NomCoureur = 'MARTIN')
;



32- Liste des courses qui se sont déroulés il y plus de 2 ans.
(libelle course, datecourse) Deux solutions demandées : l'une utilisera la fonction add_months


SELECT
	LibCourse
	, DateCourse
FROM
	Course
WHERE
	DateCourse < '%/04/05';

SELECT
	LibCourse
	, DateCourse
FROM
	Course
WHERE
	ADD_MONTHS(DateCourse, 24) < SYSDATE;




33- Entraîneurs dont le salaire est supérieur au salaire moyen
de tous les entraîneurs. Solution 1 : (nomentraineur, salaire)
Solution 2 : (nomentraineur, salaire, salmoyen)

SELECT
	NomEntr
	, SalaireEntr
FROM
	Entraineur
WHERE SalaireEntr > (	SELECT
				AVG(SalaireEntr)
			FROM
				Entraineur)


SELECT
	NomEntr
	, SalaireEntr
	, AVG (SalaireEntr) AS SalMoyen
FROM
	Entraineur
WHERE SalaireEntr > (	SELECT
				AVG(SalaireEntr)
			FROM
				Entraineur)
GROUP BY
	NomEntr
	, SalaireEntr

Dans la deuxieme version de la requete, le calcul du salaire moyen est incohérent (?)


34- Liste des coureurs qui ont gagné plus de courses que
‘Martin’.
(nomcoureur, nbcourses)

SELECT
	NomCoureur
	, COUNT(NumCourse) AS NbCourse
FROM
	Coureur
INNER JOIN
	Participe
ON
	Participe.NumCoureur = Participe.NumCoureur
WHERE
	Ordre = 1
HAVING 
	COUNT(NumCourse) > (	SELECT 
					COUNT(NumCourse)
				FROM
					Participe
				INNER JOIN
					Coureur
				ON
					Coureur.NumCoureur = Participe.NumCoureur
				WHERE
					NomCOureur = 'MARTIN'
				AND
					Ordre = 1)
GROUP BY
	NomCoureur;
Résultat incohérent ...



35- Course(s) dans la(les)quelle(s) il y a eu le plus de
participants.
(libellé course, nbcoureurs)


SELECT
	LibCourse
	, COUNT(NumCoureur) AS NbCoureurs
FROM
	Course
INNER JOIN
	Participe
ON
	Participe.NumCourse = Course.NumCourse
HAVING COUNT(NumCoureur) >= MAX(SELECT 
					COUNT(NumCoureur) AS NbCoureurs
				FROM
					Participe); 
	
	
36- Liste des coureurs qui ont participé à toutes les courses
qui se sont déroulées à Belfort durant l’année 2005.
(numcoureur) Deux solutions : avec comptage et not exists


SELECT
	Coureur.NumCoureur
FROM
	Coureur
INNER JOIN
	Participe
ON
	participe.NumCoureur = Coureur.NumCoureur
WHERE
	Numcourse IN (	SELECT	
				NumCourse 
			FROM 
				Course
			WHERE
				VilleCourse = 'BELFORT'
			AND
				DateCourse < '01/01/06'
			AND
				DateCourse >= '01/01/05'

			);