Bienvenue

Dans cette section, vous trouverez des ressources sur les cours que j'enseigne à savoir de la documentation, du code, des vidéos etc. Dès que je trouve quelque chose d'intéressant à partager, je le mettrai à disposition ici pour mes étudiants.

---

Série d'exercices sur les sous requetes

Travaux dirigés 16

			##Auteur : Enseignant
			##École : Collège Marie Victorin 
			##Session : Hiver 2026
			##Titre du cours : Introduction aux bases de données
			##Titre du programme : Travaux dirigés 16
			##Version : 1.0
			##Date : 00/00/2026
			##Langage et version : SQL 
			##Moteur : SQL Server Express
		

			-- ==============================================================================
			-- Les commandes DML : Les sous requêtes
			-- Les sous requetes

			-- Objectif : 
			-- Ce Laboratoire vise à acquérir les compétences suivantes
			-- ========================================================
			-- Les regroupements, les conditions multicritères, les jointures 
			-- Les sous requetes.		

			-- Base de données à utiliser : AdventureWorksEvalFR
			USE AdventureWorksEvalFR;
			GO

		

			-- Requête 1
			-- *********
			-- Afficher les motifs de vente dont le type est connu et dont le nom contient le mot Price ou le mot Promotion.

			-- On affichera : 
			-- l’identifiant du motif
			-- le nom du motif
			-- le type du motif

			-- Trier les résultats par type de motif, puis par nom.
			-- Vote code ici
			SELECT
				IDMotifVente,
				Nom,
				TypeMotif
			FROM dbo.MotifVente
			WHERE TypeMotif IS NOT NULL
			  AND (
					Nom LIKE '%Price%'
					OR Nom LIKE '%Promotion%'
				  )
			ORDER BY
				TypeMotif,
				Nom;

		

			-- Requête 2
			-- *********
			-- Afficher les personnes dont le type de personne est un des types utilisés par au moins 500 personnes.

			-- On affichera : 

			-- l’identifiant de la personne ;
			-- le type de personne ;
			-- le prénom ;
			-- le nom de famille ;
			-- la promotion courriel.

			-- Afficher uniquement les personnes dont le prénom et le nom de famille sont connus.
			-- Trier le résultat type de personne et par nom de famille

			-- Vote code ici
			SELECT
				IDEntiteAffaires,
				TypePersonne,
				Prenom,
				NomFamille,
				PromotionCourriel
			FROM dbo.Personne
			WHERE Prenom IS NOT NULL
			  AND NomFamille IS NOT NULL
			  AND TypePersonne IN /*('1','3','7')*/
			  (
				  SELECT TypePersonne
				  FROM dbo.Personne
				  GROUP BY TypePersonne
				  HAVING COUNT(*) >= 500
			  )
			ORDER BY
				TypePersonne,
				NomFamille;
		

			-- Requête 3
			-- *********
			-- Afficher les personnes pour lesquelles on connaît à la fois : une adresse courriel, un numéro de téléphone, le type de téléphone.
			-- Afficher seulement les personnes dont le type de téléphone appartient aux types réellement utilisés par plus de 100 personnes.

			-- On affichera : 

			-- l’identifiant de la personne
			-- le prénom
			-- le nom de famille
			-- l’adresse courriel
			-- le numéro de téléphone
			-- le type de téléphone.

			-- Trier les résultats par le nom de famille
			-- Vote code ici
			SELECT
				p.IDEntiteAffaires,
				p.Prenom,
				p.NomFamille,
				ac.AdresseCourriel,
				tp.NumeroTelephone,
				tnt.Nom AS TypeTelephone
			FROM dbo.Personne p
			INNER JOIN dbo.AdresseCourriel ac
				ON p.IDEntiteAffaires = ac.IDEntiteAffaires
			INNER JOIN dbo.TelephonePersonne tp
				ON p.IDEntiteAffaires = tp.IDEntiteAffaires
			INNER JOIN dbo.TypeNumeroTelephone tnt
				ON tp.IDTypeNumeroTelephone = tnt.IDTypeNumeroTelephone
			WHERE ac.AdresseCourriel IS NOT NULL
			  AND tp.NumeroTelephone IS NOT NULL
			  AND tnt.IDTypeNumeroTelephone IS NOT NULL
			  AND tnt.IDTypeNumeroTelephone IN
			  (
				  SELECT IDTypeNumeroTelephone
				  FROM dbo.TelephonePersonne
				  GROUP BY IDTypeNumeroTelephone
				  HAVING COUNT(*) > 100
			  )
			ORDER BY
				p.NomFamille;
		

			-- Requête 4
			-- *********
			-- Afficher les adresses liées à une entité d’affaires et dont le type d’adresse est utilisé par plus de 1 000 entités.

			-- On affichera : 

			-- L’identifiant de l’entité d’affaires
			-- la ville
			-- le code postal
			-- le nom de l’état ou de la province
			-- le nom du pays ou de la région
			-- le type d’adresse.

			-- Le résultat sera trié par le pays, l'état et la ville

			-- Vote code ici
			SELECT
				aea.IDEntiteAffaires,
				a.Ville,
				a.CodePostal,
				ep.Nom AS NomEtatProvince,
				pr.Nom AS NomPaysRegion,
				ta.Nom AS TypeAdresse
			FROM dbo.EntiteAffaires ea
			INNER JOIN dbo.AdresseEntiteAffaires aea
				ON ea.IDEntiteAffaires = aea.IDEntiteAffaires
			INNER JOIN dbo.Adresse a
				ON aea.IDAdresse = a.IDAdresse
			INNER JOIN dbo.TypeAdresse ta
				ON aea.IDTypeAdresse = ta.IDTypeAdresse
			INNER JOIN dbo.EtatProvince ep
				ON a.IDEtatProvince = ep.IDEtatProvince
			INNER JOIN dbo.PaysRegion pr
				ON ep.CodePaysRegion = pr.CodePaysRegion
			WHERE aea.IDTypeAdresse IN
			(
				SELECT IDTypeAdresse
				FROM dbo.AdresseEntiteAffaires
				GROUP BY IDTypeAdresse
				HAVING COUNT(*) > 1000
			)
			ORDER BY
				pr.Nom,
				ep.Nom,
				a.Ville;
		

			-- Requête 5
			-- *********
			-- Afficher les employés actifs dont le taux horaire courant est supérieur au taux horaire moyen courant de tous les employés actifs.

			-- On affichera : 

			-- l’identifiant de l’employé
			-- le prénom
			-- le nom de famille
			-- le titre du poste
			-- le département
			-- le quart de travail
			-- le taux horaire courant.

			-- Attention : pour chaque employé, il faut utiliser seulement son taux horaire le plus récent.

			-- Trier le réasultat par le taux horaire en ordre décroissant

			-- Vote code ici
			SELECT
				e.IDEntiteAffaires,
				p.Prenom,
				p.NomFamille,
				e.TitrePoste,
				d.Nom AS NomDepartement,
				q.Nom AS NomQuartTravail,
				hp.Taux AS TauxHoraireCourant
			FROM dbo.Employe e
			INNER JOIN dbo.Personne p
				ON e.IDEntiteAffaires = p.IDEntiteAffaires
			INNER JOIN dbo.HistoriqueDepartementEmploye hde
				ON e.IDEntiteAffaires = hde.IDEntiteAffaires
			INNER JOIN dbo.Departement d
				ON hde.IDDepartement = d.IDDepartement
			INNER JOIN dbo.QuartTravail q
				ON hde.IDQuartTravail = q.IDQuartTravail
			INNER JOIN dbo.HistoriquePaieEmploye hp
				ON e.IDEntiteAffaires = hp.IDEntiteAffaires
			WHERE e.EstActuel = 1
			  AND hde.DateFin IS NULL
			  AND hp.DateChangementTaux =
			  (
				  SELECT MAX(hp2.DateChangementTaux)
				  FROM dbo.HistoriquePaieEmploye hp2
				  WHERE hp2.IDEntiteAffaires = e.IDEntiteAffaires
			  )
			  AND hp.Taux >
			  (
				  SELECT AVG(hp3.Taux)
				  FROM dbo.Employe e3
				  INNER JOIN dbo.HistoriquePaieEmploye hp3
					  ON e3.IDEntiteAffaires = hp3.IDEntiteAffaires
				  WHERE e3.EstActuel = 1
					AND hp3.DateChangementTaux =
					(
						SELECT MAX(hp4.DateChangementTaux)
						FROM dbo.HistoriquePaieEmploye hp4
						WHERE hp4.IDEntiteAffaires = e3.IDEntiteAffaires
					)
			  )
			ORDER BY
				hp.Taux DESC;
		

			-- Requête 6
			-- *********
			-- Afficher les départements qui ont un nombre d’employés actifs supérieur au nombre moyen d’employés actifs par département.

			-- On affichera : 

			-- le nom du département
			-- le groupe du département
			-- le nombre d’employés actifs.

			-- Afficher les départements par nombre d'employés décroissant

			-- Vote code ici
			SELECT
				d.Nom AS NomDepartement,
				d.NomGroupe,
				COUNT(e.IDEntiteAffaires) AS NombreEmployesActifs
			FROM dbo.Departement d
			INNER JOIN dbo.HistoriqueDepartementEmploye hde
				ON d.IDDepartement = hde.IDDepartement
			INNER JOIN dbo.Employe e
				ON hde.IDEntiteAffaires = e.IDEntiteAffaires
			WHERE e.EstActuel = 1
			  AND hde.DateFin IS NULL
			GROUP BY
				d.Nom,
				d.NomGroupe
			HAVING COUNT(e.IDEntiteAffaires) >
			(
				SELECT AVG(NombreEmployes)
				FROM
				(
					SELECT
						COUNT(e2.IDEntiteAffaires) AS NombreEmployes
					FROM dbo.Departement d2
					INNER JOIN dbo.HistoriqueDepartementEmploye hde2
						ON d2.IDDepartement = hde2.IDDepartement
					INNER JOIN dbo.Employe e2
						ON hde2.IDEntiteAffaires = e2.IDEntiteAffaires
					WHERE e2.EstActuel = 1
					  AND hde2.DateFin IS NULL
					GROUP BY
						d2.IDDepartement
				) AS SyntheseDepartements
			)
			ORDER BY
				NombreEmployesActifs DESC;
		

			-- Requête 7
			-- *********
			-- Afficher les territoires de vente qui respectent les conditions suivantes :

			-- le territoire a au moins un vendeur rattaché
			-- les ventes cumulées du territoire sont supérieures à la moyenne des ventes cumulées de tous les territoires
			-- le territoire est associé à au moins un état ou une province.

			-- On affichera : 

			-- le nom du territoire
			-- le groupe du territoire
			-- le pays ou la région
			-- le nombre d’états ou provinces associés
			-- les ventes cumulées de l’année.

			-- Trier les résultats par ventes cumulées année

			-- Vote code ici
			SELECT
				tv.Nom AS NomTerritoire,
				tv.Groupe AS GroupeTerritoire,
				pr.Nom AS NomPaysRegion,
				COUNT(ep.IDEtatProvince) AS NombreEtatsProvinces,
				tv.VentesCumuleesAnnee
			FROM dbo.TerritoireVente tv
			INNER JOIN dbo.PaysRegion pr
				ON tv.CodePaysRegion = pr.CodePaysRegion
			INNER JOIN dbo.EtatProvince ep
				ON tv.IDTerritoire = ep.IDTerritoire
			WHERE tv.VentesCumuleesAnnee >
			(
				SELECT AVG(VentesCumuleesAnnee)
				FROM dbo.TerritoireVente
			)
			AND tv.IDTerritoire IN
			(
				SELECT IDTerritoire
				FROM dbo.Vendeur
				WHERE IDTerritoire IS NOT NULL
			)
			GROUP BY
				tv.IDTerritoire,
				tv.Nom,
				tv.Groupe,
				pr.Nom,
				tv.VentesCumuleesAnnee
			HAVING COUNT(ep.IDEtatProvince) >= 1
			ORDER BY
				tv.VentesCumuleesAnnee DESC;
		

			-- Requête 8
			-- *********
			-- Afficher les vendeurs dont les ventes cumulées de l’année sont supérieures ou égales à leur dernier quota connu.

			-- On affichera : 

			-- l’identifiant du vendeur
			-- le prénom
			-- le nom de famille
			-- le territoire
			-- la date du dernier quota
			-- le dernier quota
			-- les ventes cumulées de l’année.

			-- Attention : Le quota le plus récent doit être utilisé pour chaque vendeur.

			-- Trier les résultats par ventes cumulées année

			-- Vote code ici
			SELECT
				v.IDEntiteAffaires,
				p.Prenom,
				p.NomFamille,
				tv.Nom AS NomTerritoire,
				hq.DateQuota,
				hq.QuotaVente AS DernierQuota,
				v.VentesCumuleesAnnee
			FROM dbo.Vendeur v
			INNER JOIN dbo.Personne p
				ON v.IDEntiteAffaires = p.IDEntiteAffaires
			INNER JOIN dbo.TerritoireVente tv
				ON v.IDTerritoire = tv.IDTerritoire
			INNER JOIN dbo.HistoriqueQuotaVendeur hq
				ON v.IDEntiteAffaires = hq.IDEntiteAffaires
			WHERE hq.DateQuota =
			(
				SELECT MAX(hq2.DateQuota)
				FROM dbo.HistoriqueQuotaVendeur hq2
				WHERE hq2.IDEntiteAffaires = v.IDEntiteAffaires
			)
			AND v.VentesCumuleesAnnee >= hq.QuotaVente
			ORDER BY
				v.VentesCumuleesAnnee DESC;
		

			-- Requête 9
			-- *********
			-- Afficher les vendeurs dont le territoire actuel fait partie des territoires ayant eu au moins un historique de territoire actif.
			-- Afficher uniquement les vendeurs dont les ventes cumulées de l’année sont supérieures à la moyenne des ventes cumulées des vendeurs de leur propre territoire.

			-- On affichera : 

			-- l’identifiant du vendeur ;
			-- le prénom ;
			-- le nom de famille ;
			-- le territoire actuel ;
			-- le groupe du territoire ;
			-- les ventes cumulées de l’année.

			-- Trier les résultats par le territoire et les ventes cumulées.

			-- Vote code ici
			SELECT
				v.IDEntiteAffaires,
				p.Prenom,
				p.NomFamille,
				tv.Nom AS NomTerritoire,
				tv.Groupe AS GroupeTerritoire,
				v.VentesCumuleesAnnee
			FROM dbo.Vendeur v
			INNER JOIN dbo.Personne p
				ON v.IDEntiteAffaires = p.IDEntiteAffaires
			INNER JOIN dbo.TerritoireVente tv
				ON v.IDTerritoire = tv.IDTerritoire
			WHERE v.IDTerritoire IN
			(
				SELECT IDTerritoire
				FROM dbo.HistoriqueTerritoireVente
				WHERE DateFin IS NULL
			)
			AND v.VentesCumuleesAnnee >
			(
				SELECT AVG(v2.VentesCumuleesAnnee)
				FROM dbo.Vendeur v2
				WHERE v2.IDTerritoire = v.IDTerritoire
			)
			ORDER BY
				tv.Nom,
				v.VentesCumuleesAnnee DESC;
		

			-- Requête 10
			-- **********
			-- Produire un rapport par pays/région pour les pays/régions qui ont un nombre de personnes localisées supérieur à la moyenne.
			-- Une personne est considérée comme localisée si elle est liée à une adresse.

			-- On affichera : 

			-- le pays ou la région
			-- le nombre de personnes localisées
			-- le nombre de personnes ayant une adresse courriel
			-- le nombre de personnes ayant un téléphone
			-- le nombre de types d’adresse utilisés
			-- le nombre de territoires de vente associés au pays/région.

			-- Le rapport doit afficher seulement les pays/régions dont le nombre de personnes localisées est supérieur à la moyenne des personnes localisées par pays/région.

			-- Trier les résultats par type de motif, puis par nom.

			-- Vote code ici
			SELECT
				pr.Nom AS NomPaysRegion,
				COUNT(DISTINCT p.IDEntiteAffaires) AS NombrePersonnesLocalisees,
				(
					SELECT COUNT(DISTINCT ac.IDEntiteAffaires)
					FROM dbo.AdresseCourriel ac
					INNER JOIN dbo.AdresseEntiteAffaires aea2
						ON ac.IDEntiteAffaires = aea2.IDEntiteAffaires
					INNER JOIN dbo.Adresse a2
						ON aea2.IDAdresse = a2.IDAdresse
					INNER JOIN dbo.EtatProvince ep2
						ON a2.IDEtatProvince = ep2.IDEtatProvince
					WHERE ep2.CodePaysRegion = pr.CodePaysRegion
				) AS NombrePersonnesAvecCourriel,

				(
					SELECT COUNT(DISTINCT tp.IDEntiteAffaires)
					FROM dbo.TelephonePersonne tp
					INNER JOIN dbo.AdresseEntiteAffaires aea3
						ON tp.IDEntiteAffaires = aea3.IDEntiteAffaires
					INNER JOIN dbo.Adresse a3
						ON aea3.IDAdresse = a3.IDAdresse
					INNER JOIN dbo.EtatProvince ep3
						ON a3.IDEtatProvince = ep3.IDEtatProvince
					WHERE ep3.CodePaysRegion = pr.CodePaysRegion
				) AS NombrePersonnesAvecTelephone,

				COUNT(DISTINCT ta.IDTypeAdresse) AS NombreTypesAdresse,

				(
					SELECT COUNT(*)
					FROM dbo.TerritoireVente tv
					WHERE tv.CodePaysRegion = pr.CodePaysRegion
				) AS NombreTerritoiresVente

			FROM dbo.PaysRegion pr
			INNER JOIN dbo.EtatProvince ep
				ON pr.CodePaysRegion = ep.CodePaysRegion
			INNER JOIN dbo.Adresse a
				ON ep.IDEtatProvince = a.IDEtatProvince
			INNER JOIN dbo.AdresseEntiteAffaires aea
				ON a.IDAdresse = aea.IDAdresse
			INNER JOIN dbo.EntiteAffaires ea
				ON aea.IDEntiteAffaires = ea.IDEntiteAffaires
			INNER JOIN dbo.Personne p
				ON ea.IDEntiteAffaires = p.IDEntiteAffaires
			INNER JOIN dbo.TypeAdresse ta
				ON aea.IDTypeAdresse = ta.IDTypeAdresse
			GROUP BY
				pr.CodePaysRegion,
				pr.Nom
			HAVING COUNT(DISTINCT p.IDEntiteAffaires) >
			(
				SELECT AVG(NombrePersonnesParPays)
				FROM
				(
					SELECT
						COUNT(DISTINCT p2.IDEntiteAffaires) AS NombrePersonnesParPays
					FROM dbo.PaysRegion pr2
					INNER JOIN dbo.EtatProvince ep4
						ON pr2.CodePaysRegion = ep4.CodePaysRegion
					INNER JOIN dbo.Adresse a4
						ON ep4.IDEtatProvince = a4.IDEtatProvince
					INNER JOIN dbo.AdresseEntiteAffaires aea4
						ON a4.IDAdresse = aea4.IDAdresse
					INNER JOIN dbo.Personne p2
						ON aea4.IDEntiteAffaires = p2.IDEntiteAffaires
					GROUP BY
						pr2.CodePaysRegion
				) AS MoyennePays
			)
			ORDER BY
				NombrePersonnesLocalisees DESC;
		

---