-- TP n°2 : Projection, restriction, distinct et tri
-- TP n°3 : Fonctions de groupe et agrégats

-- il faut faire un use de la bd avant de lancer le script
use emp
-- tee ouvre un fichier pour rediriger les résultat. On le fermera à la fin du script
tee ResultatsTP0203.txt;

-- ---------------------------------------------------------------------------------------------------------
select "-- 1 : Tous les employés avec tous les attributs";

SELECT * 
FROM EMPLOYES \p;
-- CP : NE

-- ---------------------------------------------------------------------------------------------------------
select "-- 2 : Tous les employés";

SELECT NE, NOM
FROM EMPLOYES \p;
-- CP : NE

-- ---------------------------------------------------------------------------------------------------------
select "3 : Tous les employés triés par n° de département croissant, ordre alphabétique des jobs, ancienneté décroissante";

SELECT ND, JOB, DATEMB, NE, NOM
FROM EMPLOYES
ORDER BY ND ASC, JOB ASC, DATEMB DESC \p;
-- CP : NE
-- REMARQUE : on peut se passer de ASC

-- ---------------------------------------------------------------------------------------------------------
select "4 : Tous les employés avec leurs salaires triés par salaire décroissant";

SELECT SAL, NE, NOM
FROM EMPLOYES
ORDER BY SAL DESC \p;
-- CP : NE

-- ---------------------------------------------------------------------------------------------------------
select "5 : Tous les employés du département 30 avec tous leurs attributs";

SELECT * 
FROM EMPLOYES
WHERE ND=30 \p;
-- CP : NE

-- ---------------------------------------------------------------------------------------------------------
select "6 : Tous les employés du département 30 triés par ordre alphabétique";

SELECT NOM, NE, ND
FROM EMPLOYES
WHERE ND=30
ORDER BY NOM ASC \p;
-- CP : NE

-- ---------------------------------------------------------------------------------------------------------
select "7 : Tous les managers des départements 20 et 30";

SELECT NE, NOM, JOB, ND
FROM EMPLOYES
WHERE upper(JOB)="MANAGER" 
AND (ND=20 OR ND=30) \p;
-- CP : NE

select "Variante :";

SELECT NE, NOM, JOB, ND
FROM EMPLOYES
WHERE upper(JOB)="MANAGER" 
AND ND IN (20, 30) \p;

-- ---------------------------------------------------------------------------------------------------------
select "8 : Tous les employés qui ne sont pas managers et qui sont embauchés en 1981";

SELECT NE, NOM, JOB, YEAR(DATEMB)
FROM EMPLOYES
WHERE upper(JOB) !="MANAGER" 
AND YEAR(DATEMB)=1981 \p;
-- CP : NE

-- ---------------------------------------------------------------------------------------------------------
select "9 : Tous les jobs de la société";

SELECT DISTINCT JOB
FROM EMPLOYES \p;
-- CP : JOB

-- ---------------------------------------------------------------------------------------------------------
select "10 : Tous les employés ne travaillant pas dans le département 30 et qui 
soit ont un salaire > à 2800, soit sont manager";

SELECT NE, NOM, ND, SAL, JOB
FROM EMPLOYES
WHERE ND!=30 
AND (SAL>2800 OR upper(JOB)="MANAGER") \p;
-- CP : NE

-- ---------------------------------------------------------------------------------------------------------
select "11 : Tous les employés dont le salaire est compris entre 1000 et 2000";

SELECT NE, NOM, SAL
FROM EMPLOYES
WHERE SAL BETWEEN 1000 AND 2000 \p;
-- CP : NE

-- ---------------------------------------------------------------------------------------------------------
select "12 : Tous les numéros de département non vides (dans lesquels travaille au moins un employé)";

SELECT DISTINCT ND
FROM EMPLOYES \p;
-- CP : ND

-- ---------------------------------------------------------------------------------------------------------
select "12 bis : Tous les jobs par département";

SELECT DISTINCT ND, JOB
FROM EMPLOYES;
-- CP : ND, JOB

-- ---------------------------------------------------------------------------------------------------------
select "13 : Tous les employés ayant ou pouvant avoir une commission";

SELECT NE, NOM, COMM
FROM EMPLOYES
WHERE COMM IS NOT NULL \p;
-- CP : NE

-- ---------------------------------------------------------------------------------------------------------
select "14 : Tous les salaires, commissions et totaux (salaire + commission) des vendeurs";

SELECT NE, NOM, SAL, COMM, (SAL+COMM), JOB
FROM EMPLOYES
WHERE upper(JOB)="SALESMAN" \p;
-- CP : NE

-- ---------------------------------------------------------------------------------------------------------
select "15 : Tous les salaires, commissions et totaux (salaire + commission) des employés";

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

-- ---------------------------------------------------------------------------------------------------------
select "16 : Tous les employés embauchés en 1981";

SELECT NE, NOM, YEAR(DATEMB)
FROM EMPLOYES
WHERE YEAR(DATEMB)=1981 \p;
-- CP : NE

-- ---------------------------------------------------------------------------------------------------------
select "17 : Tous les employés avec leur date d'embauche, la date du jour et leur 
nombre d'années d'ancienneté (on considérera que toute année commencée vaut pour une année), triés par 
ancienneté (on utilisera les fonctions de base de traitement de date et de traitement de chaîne).";

SELECT YEAR(FROM_DAYS(TO_DAYS(CURRENT_DATE) - TO_DAYS(DATEMB))) AS ANCIENETE, NE, NOM, DATEMB, CURRENT_DATE
FROM EMPLOYES
ORDER BY ANCIENETE \p;
-- CP : NE

-- ---------------------------------------------------------------------------------------------------------
select "18 : Tous les employés ayant un A en troisième lettre de leurs noms";
SELECT NE, NOM
FROM EMPLOYES
WHERE NOM like '__A%' \p;
-- CP : NE

select "Variante (on préfèrera la première solution):";
SELECT NE, NOM
FROM EMPLOYES
WHERE SUBSTR(NOM, 3, 1)='A' \p;
-- CP : NE

-- ---------------------------------------------------------------------------------------------------------
select "19 : Tous les employés ayant au moins deux A dans leurs noms";

SELECT NE, NOM
FROM EMPLOYES
WHERE NOM LIKE "%A%A%" \p;
-- CP : NE

-- ---------------------------------------------------------------------------------------------------------
select "20 : Donner les quatre dernières lettres du nom de chaque employé";

SELECT NE, NOM, SUBSTR(NOM, LENGTH(NOM)-3, 4)
FROM EMPLOYES \p;
-- CP : NE

select "Variante :";

SELECT NE, NOM, SUBSTR(NOM, -4, 4)
FROM EMPLOYES \p;

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

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

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

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

-- ---------------------------------------------------------------------------------------------------------
select "23 : Combien de départements non vides dans la société";

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

-----------------------------------------------------------------------------------------------------------
select "24 : Combien de vendeurs (« Salesman ») dans la société";

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

-- ---------------------------------------------------------------------------------------------------------
select "25 : Combien de jobs différents dans la société";

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

-- ---------------------------------------------------------------------------------------------------------
select "26 : Combien y a-t-il d'employés qui n'ont pas et ne peuvent pas avoir de commission ?";

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

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

-- ---------------------------------------------------------------------------------------------------------
select "27 : Salaires minimum, maximum et moyen de la société";

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

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

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

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

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

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

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

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

SELECT JOB, AVG(SAL), MIN(SAL), MAX(SAL), COUNT(*)
FROM EMPLOYES
GROUP BY JOB \p;
-- CP : JOB

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

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

-- ---------------------------------------------------------------------------------------------------------
select "33 : 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), COUNT(*)
FROM EMPLOYES
GROUP BY ND
HAVING COUNT(*)>2 \p;
-- CP : ND

-- ---------------------------------------------------------------------------------------------------------
select "34 : 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 "35 : Quels sont les jobs pour lesquels la moyenne du salaire est supérieure à 2000 ?";

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

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

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

-- ---------------------------------------------------------------------------------------------------------
select "37 : 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 "38 : 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(*)
FROM EMPLOYES
GROUP BY TRANCHE, MIN, MAX \p;
-- CP : TRANCHE = MIN = MAX

-- ---------------------------------------------------------------------------------------------------------
select "39 : Quels sont les 3 employés qui ont le salaire le plus élevé ?";

SELECT NE, nom, sal
FROM EMPLOYES
ORDER BY sal DESC
LIMIT 3 \p;

-- ---------------------------------------------------------------------------------------------------------
select "40 : Choisir un SALESMAN au hasard.";

SELECT NE, nom, job
FROM EMPLOYES
WHERE upper(JOB)='SALESMAN'
ORDER BY rand()
LIMIT 1 \p;

notee;