1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
| CREATE TABLE T_EMP
(
EMP_NUM INT,
EMP_SERVICE VARCHAR(16),
EMP_SALAIRE FLOAT
)
INSERT INTO T_EMP VALUES (1, 'COMPTA', 12345.12)
INSERT INTO T_EMP VALUES (2, 'COMPTA', 1234.32)
INSERT INTO T_EMP VALUES (3, 'RH', 8541.16)
INSERT INTO T_EMP VALUES (4, 'RH', 2563.21)
INSERT INTO T_EMP VALUES (5, 'RH', 11222.33)
INSERT INTO T_EMP VALUES (6, 'COMPTA', 5421.59)
SELECT EMP_SERVICE, AVG(EMP_SALAIRE) AS SALAIRE_MOYEN,
MAX(EMP_SALAIRE) AS SALIARE_MAX,
(SELECT EMP_NUM
FROM T_EMP T1
WHERE T1.EMP_SERVICE = T.EMP_SERVICE
AND T1.EMP_SALAIRE = (SELECT MAX(EMP_SALAIRE)
FROM T_EMP T2
WHERE T2.EMP_SERVICE = T.EMP_SERVICE))
AS EMP_SALAIRE8MAX_SERVICE
FROM T_EMP T
GROUP BY EMP_SERVICE |
Partager