Help with oracle database query

Asked

Viewed 57 times

-1

How to increase wages by 0.1% of those with lower salary than the AVG of your department

This is the table
tabelaFuncionario

I was able to average salaries per department:

SELECT cod_departamento, AVG(VAL_SALARIO)
FROM FUNCIONARIO
GROUP BY COD_DEPARTAMENTO;

1 answer

3


To raise the salary of FUNCIONÁRIOS can be done as follows:

1º - Let’s see which records will change:

Repare nos 4 registros marcados

According to their department, 1, 2 and 3 are required to have respectively less than ~3603, 2392 and ~1973 in VAL_SALARIO for their wages to rise by 10%.

MÉDIA POR DEPARTAMENTO

2nd - Now we need to do the UPDATE on record, this can be done this way:

UPDATE FUNCIONARIOS FUNC SET FUNC.VAL_SALARIO = FUNC.VAL_SALARIO * 1.10
WHERE FUNC.VAL_SALARIO < (SELECT AVG(FUNCIO.VAL_SALARIO)
FROM FUNCIONARIOS FUNCIO
WHERE FUNCIO.COD_DEPARTAMENTO = FUNC.COD_DEPARTAMENTO
GROUP BY FUNCIO.COD_DEPARTAMENTO);

Notice that the secret here are the ALIAS that we give the TABELAS that are the same, one we call FUNC and another we call FUNCIO to differentiate one from the other, thus the table record FUNC will be compared in table FUNCIO.

So we updated 4 records.

REGISTROS ATUALIZADOS

Which are the 4 brands previously: WILLIAN, VAGNER, ABNER e ANTONIO.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.