------------------ Démo jointures et sous-requêtes ---------------------
use AdventureWorksDW2019

-- Jointures ---
-- A.1 : le nom des revendeurs avec au moins une vente
-- 635
select distinct DimReseller.ResellerKey, ResellerName
from DimReseller join FactResellerSales
	on DimReseller.ResellerKey=FactResellerSales.ResellerKey

-- A.2 : le nom des revendeurs avec ou non une vente
-- 701
select distinct DimReseller.ResellerKey, ResellerName
from DimReseller left join FactResellerSales -- tous les revendeurs
	on DimReseller.ResellerKey=FactResellerSales.ResellerKey

-- A.3 : le nom des revendeurs sans vente
-- si champs PK (FK) sont null = il n'y a pas de correspondance
-- Résultat : 66
select distinct DimReseller.ResellerKey, ResellerName,
		FactResellerSales.* -- d'afficher toutes les colonnes ventes
from DimReseller left join FactResellerSales -- tous les revendeurs
	on DimReseller.ResellerKey=FactResellerSales.ResellerKey
where FactResellerSales.ResellerKey is null
-- quand le revendeur n'existe pas dans la table des ventes

--------------------------- UNION/Intersect/Except ------------
/* Afficher les numéros de produits de la table produit et de la table ventes
   même s'ils ne sont pas dans les deux tables en même temps
--> Ne comptabilise pas les doublons (si en double car sur l'une et sur l'autre table) */
-- 606
SELECT productkey
FROM dimproduct 
UNION  
-- 334 
SELECT productkey 
FROM FactResellerSales

/* Afficher les numéros de produits de la table produit et de la table ventes
   même s'ils ne sont pas dans les deux tables en même temps
--> Comptabilise les doublons (si en double car sur l'une et sur l'autre table) */
-- 940
-- 606
SELECT productkey
FROM dimproduct 
UNION all  
-- 334 
SELECT distinct productkey 
FROM FactResellerSales

/* Afficher les numéros de produits présents dans la table produit et dans la table ventes
   Que s'ils sont dans l'une et dans l'autre également (si que dans une table, on ne le prend pas)*/
-- 334 seuls ceux en lien / à l'intersection
-- 606
SELECT productkey
FROM dimproduct 
intersect  
-- 334 
SELECT productkey 
FROM FactResellerSales

/* Afficher les numéros de produits présents dans la table produit MAIS pas dans la table ventes*/
-- 272 que dans requete 1
-- 606
SELECT productkey
FROM dimproduct 
Except 
-- 334 
SELECT productkey 
FROM FactResellerSales

--------------------------- sous-requêtes dans le where
-- Je compare une colonne (listing) dans une première table avec une colonne (listing) dans une seconde table
-- B.1 Afficher les revendeurs pour lesquelles nous avons des ventes
-- Résultats : 635
-- noms des revendeurs avec des ventes
select distinct ResellerName, R.ResellerKey
from DimReseller r
where r.resellerkey in (
				-- requête secondaire : id revendeurs table ventes
				select distinct ResellerKey
				from FactResellerSales 
						)

-- B.2 Afficher les revendeurs qui n'ont pas fait de ventes
-- Résultats : 66
-- noms des revendeurs sans ventes
select distinct ResellerName, R.ResellerKey
from DimReseller r
where r.resellerkey not in (
				-- requête secondaire : id revendeurs table ventes
				select distinct ResellerKey
				from FactResellerSales 
						)

-- C.1 Je compare deux listings (ne peut être remplacé par une jointure)
-- Je souhaite récupérer les noms des revendeurs qui ont fait des ventes en 2012 mais pas en 2013
-- Résultat : 90

-- requête principale : revendeurs 2012 = 401
select distinct ResellerName, R.ResellerKey
from DimReseller r join FactResellerSales rs on r.ResellerKey=rs.ResellerKey
where year(orderdate) = 2012
 and r.resellerkey not in (
				-- requête secondaire : revendeurs 2013 = 489
				select distinct R.ResellerKey
				from DimReseller r join FactResellerSales rs on r.ResellerKey=rs.ResellerKey
				where year(orderdate) = 2013
						)

-- D.1 > ou <
-- Afficher le nombre de commandes en 2012 que si plus que le nombre de commandes en 2011
select COUNT(*) as 'nombre de commandes'
From FactInternetSales i
where year(i.orderdate) = 2012
having COUNT(*) > (
		select COUNT(*) as 'nombre de commandes'
		From FactInternetSales s
		where year(s.orderdate) = 2011)

-- D.2 Corrélation Suite D.1
-- Afficher les produits avec plus de ventes en 2012 qu'en 2011
select COUNT(distinct SalesOrderNumber) as 'nombre de commandes', EnglishProductName, p.productkey
From FactInternetSales i join DimProduct p on i.ProductKey=p.productkey
where year(i.orderdate) = 2012
group by p.productkey, EnglishProductName
having COUNT(distinct SalesOrderNumber) > 
		( select COUNT(distinct SalesOrderNumber) as 'nombre de commandes'
		  From FactInternetSales ii join DimProduct pp on ii.ProductKey=pp.productkey
		  where year(ii.orderdate) = 2011
		  and p.productkey=pp.productkey
		  group by pp.productkey
		)

		-- vérifier nombre de commandes en 2012 (et idem en 2011)
select COUNT(*) as 'nombre de commandes', EnglishProductName, p.productkey
From FactInternetSales i join DimProduct p on i.ProductKey=p.productkey
where year(i.orderdate) = 2012
group by EnglishProductName, p.productkey

-- E. =
-- Afficher le numéro et le nom de l'employé ainsi que la date de naissance 
-- de l'employé le plus jeune
set statistics time on
dbcc freeproccache

Select EmployeeKey, LastName, BirthDate as 'age'
From DimEmployee
WHERE BirthDate =  -- quand la date de naissance est égal à la date de naissance la plus récente
							( Select max(BirthDate) -- L'année la plus récente
							From DimEmployee )

							-- peut s'écrire également comme ça - 1ms de moins à peine
Select top 1 EmployeeKey, LastName, BirthDate as 'age'
From DimEmployee
order by birthdate desc

-- F. Exists : table dimproduct : 
-- Compter le nombre de produits par couleur 
-- et chercher s'il y a une couleur qui ne concerne qu'un seul produit
-- Résultat : 1 produit pour la couleur Grey
select * from dimproduct

Select count(productkey) as 'nombre de produits', color
From dimproduct 
Group by color
having count(productkey) = 1
-- order by PU_Vente

-- F.2 -- Afficher le produit concerné  par le résultat du F.! 
-- Résultat : MIshi KObe Niku
Select distinct EnglishProductName
From dimproduct
where exists (Select count(productkey) as 'nombre de produits', color
				From dimproduct as p
				where p.Color = dimproduct.color -- corrélation
				Group by color
				having count(productkey) = 1)

-----------------------------------------------------------------------------------

-- E.1 Sous-requêtes dans le FROM
-- Quelle est en moyenne, le nombre de produits par couleur ?
select avg([nombre de produits]) as Moyenne
from    (
-- Requête principale : La moyenne (calcul final)
		-- Requête secondaire : Compter le nombre de couleurs par produits (1er calcul à faire)
		select count(productkey) as 'nombre de produits', color
		From DimProduct
		group by color
		) as nb_col
		-- Possibilité de mettre d'autres sous-requêtes.... avec des virgules

-- E.2 Sous-requêtes dans le From (exemple 2) - plusieurs vers plusieurs (pas de cas sur cette BDD)

--------------------------------------------------------------------------
-- Faire exemple comme sur le support
-- Moyenne par client, moyenne générale
-- Moyenne par client - moyenne générale
-- Having : les meilleurs clients etc.

-- F.1 Sous-requêtes dans le Select
-- Je souhaite afficher le numéro, le nom et le prix du produit
-- ainsi que le prix moyen tous produits
-- exceptés les produits dont le prix n'est pas connu
select productkey, EnglishProductName, ListPrice, 
(select avg(listprice) From DimProduct) Moy_listprice
from DimProduct
where listprice is not null

-- F.2 quand le prix du produit est supérieur à la moyenne
select productkey, EnglishProductName, ListPrice, 
(select avg(listprice) From DimProduct) Moy_listprice
from DimProduct
where listprice is not null 
and Listprice > (select avg(listprice)
					From DimProduct)

