-- Série 1 : Projection, restriction, distinct et tri
-- Série 2 : Fonctions de groupe et agrégats
-- Série facultative

-- Le fichier peut être entièrement copié/collé dans une calculette mysql pour produire un fichier de résultats.

-- il faut faire un use de la bd avant de lancer le script
use empdept

-- tee ouvre un fichier pour rediriger les résultats. 
-- On le fermera à la fin du script
tee ./TP_SQL_01_Select_monotable_FDG_RESULTATS.sql;

-- ---------------------------------------------------------------------------------------------------------
-- ---------------------------------------------------------------------------------------------------------
select "SERIE 2 : Fonctions de groupe et agrégats";


-- ---------------------------------------------------------------------------------------------------------
select "1 : Combien d'employés dans la société";

SELECT COUNT(*)
FROM EMPLOYES \p;
-- CP : NULL

-- ---------------------------------------------------------------------------------------------------------
select "2 : Combien d'employés embauchés en 1981";

SELECT COUNT(*) 
FROM EMPLOYES
WHERE YEAR(DATEMB)=1981 \p;
-- CP : NULL

-- ---------------------------------------------------------------------------------------------------------
select "3 : Combien de vendeurs (SALESMAN) dans la société";

SELECT COUNT(*)
FROM EMPLOYES
WHERE FONCTION="SALESMAN" \p;
-- CP : NULL

-- ---------------------------------------------------------------------------------------------------------
select "4 : Combien de départements dans la société";

SELECT COUNT(DISTINCT ND)
FROM EMPLOYES \p;
-- CP : NULL

-- ---------------------------------------------------------------------------------------------------------
select "5 : Combien de fonctions différentes dans la société";

SELECT COUNT(DISTINCT FONCTION)
FROM EMPLOYES \p;
-- CP : NULL

-- ---------------------------------------------------------------------------------------------------------
Select "6:	Combien y a-t-il d’employés qui n’ont pas et ne peuvent pas avoir de commission (la valeur est NULL) ?";

SELECT COUNT(*) 
FROM EMPLOYES
WHERE COMM IS NULL \p;
-- CP : NULL

select "6 : Variante 1 :";
SELECT COUNT(*) - COUNT(COMM)
FROM EMPLOYES \p;

select "6 : Variante 2 :";
SELECT COUNT(*) 
FROM EMPLOYES
WHERE COMM >=0 \p;

-- ---------------------------------------------------------------------------------------------------------
select "7 : Quels sont les salaires minimum, maximum et moyen de la société ?";

SELECT MIN(SAL), MAX(SAL), AVG(SAL)
FROM EMPLOYES \p;
-- CP : NULL

-- ---------------------------------------------------------------------------------------------------------
select "8 : Salaires moyens des vendeurs";

SELECT AVG(SAL)
FROM EMPLOYES
WHERE FONCTION="SALESMAN" \p;
-- CP : NULL

-- ---------------------------------------------------------------------------------------------------------
select "9 : Salaires moyens de tous les employés en tenant compte des commissions";

SELECT AVG(SAL + IFNULL(COMM, 0))
FROM EMPLOYES \p;
-- CP : NULL

-- ---------------------------------------------------------------------------------------------------------
select "10 : Pourcentage moyen de la commission des vendeurs par rapport à leur salaire";

SELECT AVG((COMM/(SAL+COMM))*100) RatioCommTotal
FROM EMPLOYES
WHERE FONCTION="SALESMAN" \p;
-- CP : RatioCommTotal

-- ---------------------------------------------------------------------------------------------------------
select "11 : Quel est le salaire moyen, les salaires min et max et le nombre d'employé par profession ?";

SELECT FONCTION, MIN(SAL) MINSAL, MAX(SAL) MAXSAL, AVG(SAL) AVGSAL, COUNT(*) NBEMP
FROM EMPLOYES
GROUP BY FONCTION \p;
-- CP : FONCTION

-- ---------------------------------------------------------------------------------------------------------
select "12 : Quels sont les salaires maximums de chaque département ?";

SELECT ND, MAX(SAL) MAXSAL
FROM EMPLOYES
GROUP BY ND \p;
-- CP : ND

-- ---------------------------------------------------------------------------------------------------------
select "13 : Quels sont les départements dans lesquels travaillent plus de deux personnes et quels sont les salaires moyens dans ces départements ?";

SELECT ND, AVG(SAL) AVGSAL, COUNT(*) NBEMP
FROM EMPLOYES
GROUP BY ND
HAVING COUNT(*)>2 \p;
-- CP : ND

-- ---------------------------------------------------------------------------------------------------------
select "14 : Quels sont les départements dans lequel il y a plus que 4 personnes ?";

SELECT ND, COUNT(*)
FROM EMPLOYES
GROUP BY ND
HAVING COUNT(*)>4 \p;
-- CP : ND

-- ---------------------------------------------------------------------------------------------------------
select "15 : Quels sont les fonctions pour lesquels la moyenne du salaire est supérieure à 2000 ?";

SELECT FONCTION, AVG(SAL) AVGSAL
FROM EMPLOYES
GROUP BY FONCTION 
HAVING AVG(SAL)>2000 \p;
-- CP : FONCTION

-- ---------------------------------------------------------------------------------------------------------
select "16 : Combien y a-t-il d'employés par département et par fonction et quelle est la moyenne de leurs salaires ?";

SELECT ND, FONCTION, COUNT(*) NBEMP, AVG(SAL) AVGSAL
FROM EMPLOYES
GROUP BY ND, FONCTION \p;
-- CP : ND, FONCTION

-- ---------------------------------------------------------------------------------------------------------
select "17 : Quel est le nombre d'employés par année d'embauche ?";

SELECT YEAR(DATEMB), COUNT(*)
FROM EMPLOYES
GROUP BY YEAR(DATEMB) \p;
-- CP : YEAR(DATEMB)

-- ---------------------------------------------------------------------------------------------------------
select "18 : Combien y a-t-il d'employés par tranches de salaire de 1000 (0 à 999, 1000 à 1999, etc.)";

SELECT (SAL div 1000 + 1) AS TRANCHE, 
((SAL div 1000)*1000) as MIN, ((SAL div 1000 + 1)*1000 - 0.01) as MAX, COUNT(*) NBEMP
FROM EMPLOYES
GROUP BY TRANCHE, MIN, MAX \p;
-- CP : TRANCHE = MIN = MAX

-- ---------------------------------------------------------------------------------------------------------
select "19 : Combien d’employés et de départements par fonction. Vérifiez bien votre résultat :  par exemple, il y a deux ANALYST, tous les deux dans le département 20, la réponse est donc: 2 employés et 1 département pour la fonction ANALYST.";

SELECT FONCTION, COUNT(*) NBEMP, COUNT(DISTINCT ND) NBDEPT
FROM EMPLOYES
GROUP BY FONCTION \p;
-- CP : FONCTION

-- ---------------------------------------------------------------------------------------------------------
select "20 : Liste des employés par fonction.";

SELECT FONCTION, GROUP_CONCAT(NOM) LISTE_EMPLOYES
FROM EMPLOYES
GROUP BY FONCTION \p;
-- CP : FONCTION

-- ---------------------------------------------------------------------------------------------------------
select "21 : Liste des employés par départements.";

SELECT ND, GROUP_CONCAT(NOM) LISTE_EMPLOYES
FROM EMPLOYES
GROUP BY ND \p;
-- CP : ND

-- ---------------------------------------------------------------------------------------------------------
select "22 : Liste des fonctions par département.";

SELECT ND, GROUP_CONCAT(DISTINCT FONCTION) LISTE_FONCTION
FROM EMPLOYES
GROUP BY ND \p;
-- CP : ND

-- ---------------------------------------------------------------------------------------------------------
select "23 : Liste des départements par fonction.";

SELECT FONCTION, GROUP_CONCAT(DISTINCT ND) LISTE_DEPT
FROM EMPLOYES
GROUP BY FONCTION \p;
-- CP : ND

-- ---------------------------------------------------------------------------------------------------------
select "24 : Quel est le nombre d’employés par département et par fonction, trié par département décroissant.";

SELECT ND, FONCTION, COUNT(*) NBEMP
FROM EMPLOYES
GROUP BY ND, FONCTION 
ORDER BY ND DESC \p;
-- CP : ND, FONCTION

-- ---------------------------------------------------------------------------------------------------------
select "25 : La même chose, mais trié par nombre d’employé et par numéro de département croissant. On fera une version avec alias.";

SELECT ND, FONCTION, COUNT(*) NBEMP
FROM EMPLOYES
GROUP BY ND, FONCTION 
ORDER BY NBEMP, ND \p;
-- CP : ND, FONCTION

-- ---------------------------------------------------------------------------------------------------------
select "26.	Combien y a-t-il d’employés et de départements par fonction. Vérifiez bien votre résultat :  par exemple, il y a deux ANALYST, tous les deux dans le département 20, la réponse est donc: 2 employés et 1 département pour la fonction ANALYST.";

SELECT FONCTION, COUNT(NE), COUNT(DISTINCT ND)
FROM EMPLOYES
GROUP BY FONCTION \p;
-- CP : FONCTION


-- Fermeture du fichier des résultats
notee;