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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
| with service as
(
select 's1' Numserv ,'Comptabilité' libserv from dual union
select 's2' ,'Ressources Humaines' from dual union
select 's3' ,'Production' from dual
), EMPLOYE as
(select 'e1'Numemp,'Cantou' nom,'Bernard' prenom,'f1' numfonct,'s1' numserv from dual union
select 'e2', 'Bleuet' , 'Dominique','f2','s2' from dual union
select 'e3', 'Blanc' ,'Maxime','f3','s2' from dual union
select 'e4', 'AAAA' ,'aaaa','f3','s1' from dual union
select 'e9', 'BBBB' ,'bbbb','f4','s1' from dual union
select 'e5', 'CCCC' ,'cccc','f2','s1' from dual union
select 'e6', 'DDDD' ,'dddd','f3','s1' from dual union
select 'e10', 'Deliat' , 'Justine','f4','s3' from dual union
select 'e7', 'EEEE' , 'eeee','f1','s3' from dual union
select 'e8', 'FFFF' , 'ffff','f5','s2' from dual
)
,FONCTION as
(
select 'f1' Numfonct, 'LF1' libfonct, 1000 salaire from dual union
select 'f2' Numfonct, 'LF2' libfonct, 5000 salaire from dual union
select 'f3' Numfonct, 'LF3' libfonct, 4000 salaire from dual union
select 'f4' Numfonct, 'LF4' libfonct, 2000 salaire from dual union
select 'f5' Numfonct, 'LF5' libfonct, 7000 salaire from dual union
select 'f6' Numfonct, 'LF6' libfonct, 600 salaire from dual
)
select S.libserv,e.nom,e.prenom,F.salaire
from EMPLOYE E join
(
select max(Ff.salaire)salaire,Ss.numserv numserv,SS.libserv libserv
from service SS join EMPLOYE EE on SS.numserv=EE.numserv join fonction FF on EE.numfonct=FF.numfonct
group by SS.libserv,SS.numserv
) S on E.numserv=S.numserv join fonction F on E.numfonct=F.numfonct
where F.salaire=S.salaire
order by S.libserv |