-- Démo pas à pas
use AdventureWorksDW2019

-- A. Affichage des colonnes et des lignes 
-- A.1 Je souhaite afficher toutes les colonnes de la table dimgeography
-- Résultats : 655 lignes
Select *
From DimGeography

-- A.2 Je souhaite afficher toutes les colonnes mais que pour les 100 premières lignes
Select top 100 *
From DimGeography

-- A.3 Je souhaite afficher les 75 premières lignes mais que la colonne ipaddresslocator
Select top 75 IpAddressLocator
From DimGeography

-- A.4 Je souhaite afficher les 50 premières lignes mais que les colonnes ipaddresslocator et stateprovincecode
Select top 75 IpAddressLocator, StateProvinceCode
From DimGeography

-- B. Distinct
-- B1 - Nombre de pays différents = 6
select distinct FrenchCountryRegionName
from DimGeography

-- B2 - Nombre de pays et villes différentes = 564
select distinct FrenchCountryRegionName, city
from DimGeography

-- C. Renommage des colonnes
-- C.1 - Même requête mais renmomer FrenchCountryRegionName en "Pays du client" et city en "ville"
select distinct FrenchCountryRegionName as 'Pays du client', 
				city as ville -- exemple avec l''employé
from DimGeography

-- C.2 - Renommer colonnes car fonction intégrée 
select BirthDate, year(birthdate) as 'Année'
from dimcustomer

-- D.1 - Afficher le frenchcountryregionname (renommé pays du client) et le code postal
-- Trier les deux par ordre ascendant : d'abord le pays puis le code postal
-- puis utiliser l'alias du pays dans le order by et mettre le postalcode en descendant
select FrenchCountryRegionName as 'pays du client', PostalCode
from DimGeography
order by [pays du client], PostalCode desc
-- order by FrenchCountryRegionName, Postalcode

----------------------------------- 2ème partie ---------------
-- E : les where 
-- E.1 Where simple : Clients français exceptés parisiens
select *
From dimgeography
where FrenchCountryRegionName = 'France' and
	city <> 'Paris'

-- E.2 Where IN : Clients français et états-uniens exceptés ceux de paris et de miami
-- Afficher seulement les villes et pays et trier les par ordre ascendant
select FrenchCountryRegionName,city
From dimgeography
where FrenchCountryRegionName in ('France','États-Unis')
and city not in ('Paris','Miami', 'Paris la Defense')
order by FrenchCountryRegionName, city

-----> faire exercices E1 à E6 pour valider cette première partie

-- E.3 Where LIKE
-- les pays commence par A
-- stateprovince ne contient pas South
select *
From dimgeography
where FrenchCountryRegionName like 'A%'
and StateProvincename not like '%south%'

-- E.4 Pareil que le E.3 mais quand le geographykey est entre 170 et 176
select *
From dimgeography
where FrenchCountryRegionName like 'A%'
and StateProvincename not like '%south%'
and geographykey between 170 and 176

-- Tous les pays qui commencent par A ou C
select *
		-- left(FrenchCountryRegionName,1)
		-- caractères à gauche d'une colonne : 1er caractère à gauche
From DimGeography
where left(FrenchCountryRegionName,1) in ('A','C')
--(FrenchCountryRegionName like 'A%' OR FrenchCountryRegionName like 'C%')

-- E.5 gestion des null dans conditions
-- quand colonne middlename est non renseignée (null) et enddate renseignée
-- afficher middlename et enddate
select middlename,enddate
From DimEmployee
where MiddleName is null and enddate is not null

-- E.6 fonction year()
-- Les noms des employés quand la date d'embauche est à partir de 2010
select LastName,hiredate, 
		year(hiredate) as 'année d''embauche'
From DimEmployee
where year(hiredate) >= 2010

-- E.7 calculer l'âge des employés aujourd'hui - DATEDIFF()
-- l'âge des employés à la date d'embauche
select birthdate, getdate() as 'date du jour',
		datediff(year,BirthDate, getdate()) as 'âge maintenant',
		datediff(year, birthdate, hiredate) as 'âge date d''embauche'
From DimEmployee

-----> faire suite des exercices

------------------------------ 3ème partie --------------- GROUP BY 
-- F Group by
-- F Count seul
select count(City) as 'nombre de ville'
from dimgeography

-- F.1 Compter le nombre de villes pas pays
-- et trier par nombre de villes (décroissant)
select count(City) as 'nombre de villes', FrenchCountryRegionName
from dimgeography
group by FrenchCountryRegionName
order by [nombre de villes] desc

-- F.2 Compter le nombre d'employés par département et par title
-- trier par département (ascendant)
-- puis par nombre d'employés descendant
select count(EmployeeKey) as 'nombre d''employés'
from dimemployee

select DepartmentName, title,
		count(EmployeeKey) as 'nombre d''employés'
from dimemployee
group by DepartmentName, title
order by DepartmentName, [nombre d'employés] desc

------------------------------------------------------------------- G. Having -------------

-- Compter le nombre de produits par couleurs
-- quand la couleur n'est pas NA -- PAS FA - Colonne
-- quand le numéro de produit est entre 2 et 500 -- PAS FA - Colonne
-- quand le nombre de produits est supérieur à 50 -- FA
select * From dimproduct
Select color, 
		count(productkey) as 'nombre de produits'
From dimproduct
where color <> 'NA' and productkey between 2 and 500
group by color
having count(productkey) > 50

-- G.1 Comme le F.1 mais avec conditions en plus
-- Quand le geographykey est entre 50 et 100
-- Quand le nombre de villes est supérieur à 60
select count(City) as 'nombre de villes', FrenchCountryRegionName
from dimgeography
where GeographyKey between 50 and 500
group by FrenchCountryRegionName
having count(city) > 60
order by [nombre de villes] desc

-- G.2 Comme le F.2 mais avec conditions en plus
-- quand l'employé est né à partir de 1980
-- Quand le département et title à plus de 10 employés
select * from DimEmployee

select DepartmentName, title,
		count(EmployeeKey) as 'nombre d''employés'
from dimemployee
where year(birthdate) > 1980
group by DepartmentName, title
having count(EmployeeKey) > 10
order by DepartmentName, [nombre d'employés] desc

----------------------------------------------------------------------- H. CASE ---------------
-- H.1 les hommes et les femmes
-- puis compter le nombre d'employés par genre et situation familiale
select count(EmployeeKey) as 'nombre de clients' --Gender, maritalstatus,
	,case
		when gender = 'F' and maritalstatus = 'M' then 'Femme mariée'
		when gender = 'F' and maritalstatus = 'S' then 'Femme célibataire'
		when gender = 'M' and maritalstatus = 'M' then 'Homme marié'
		when gender = 'M' and maritalstatus = 'S' then 'Homme célibataire'
	else 'null'
	end as 'Genre et statut marital'
from DimEmployee
group by gender, maritalstatus

-- H.2 quand un employé à moins de 50 heures de vacations alors 'peu de vacances'
-- quand un employé à entre 50 et 69 heures alors 'vacances moyenne'
-- quand un employé à plus de 70 heures  alors 'beaucoup de vacances'
-- combien d'employés par type de vacations
-- trier par nombre d'employés (descendant)
select count(EmployeeKey) as 'nombre d''employés',
	case
		when VacationHours < 50 then 'peu de vacances'
		when VacationHours < 70 then 'vacances moyenne'
		else 'beaucoup de vacances'
	end as 'type de vacances'
from DimEmployee
group by case
		when VacationHours < 50 then 'peu de vacances'
		when VacationHours < 70 then 'vacances moyenne'
		else 'beaucoup de vacances'
	end
order by [nombre d'employés] desc

-- Pas faire exo H3 si groupe débutant

------------------------------------------------------------------ I. Fontions intégrées

-- I.1 Fonctions d'agrégations autres
-- moyenne de l'unitsbalance et le max de l'unitcost
-- par année, trimestre et par mois (trier ordre asc année et mois)- movementdate
select avg(unitsbalance) as moyenne, 
		max(unitcost) as maximum,
	year(movementdate) as année, 
	datepart(Quarter,movementdate) as trimestre,
	month(movementdate) as mois
From FactProductInventory
group by year(movementdate),datepart(Quarter,movementdate), month(movementdate)
order by année, mois

-- I.1 Suite - Fonctions d'agrégations autres
-- Quand le mois est entre 3 et 12
-- Quand le coût max à l'unité est supérieur ou égal à 1000
select avg(unitsbalance) as moyenne, 
		max(unitcost) as maximum,
	year(movementdate) as année, 
	datepart(Quarter,movementdate) as trimestre,
	month(movementdate) as mois
From FactProductInventory
group by year(movementdate),datepart(Quarter,movementdate), month(movementdate)
order by année, mois

-- I.2 Fonctions d'agrégations autres : les différents types de count

-- compter le nombre de produits --> chaque produit unique/nb lignes
-- compter le nombre de couleurs
-- count(*) -- nombre de lignes
-- count(colonne) -- nombre de valeurs renseignées
-- count(distinct colonne) -- nombre de valeurs renseignées différentes/distinctement
select count(*) as 'nombre de lignes',
		count(productkey) as 'nombre de produits',
		count(ProductSubcategoryKey) as 'nb souscat renseignées',
		count(distinct color) as 'nombre de couleurs'
From DimProduct

-- I.5 Conversion - Table employees -- ne pas faire (car fonction dans fonction - difficile pour débutants)
-- puis nombre d'années entre datekey et maintenant
select datekey, 
	convert(char(8), datekey) as 'transformation texte',
	convert(date,convert(char(8), datekey)) as 'transformation date'
	, datediff(year,convert(date,convert(char(8), datekey)),getdate()) as 'nombre d''années'
From FactProductInventory