Doubt Mysql compare several columns

tl; dr

This is a case that solves well with JOIN , after all all tables have a very regular structure, and a very direct relationship:

SELECT    nome,
          COUNT(*) AS atendidas,
          SUM(pontuacao.valor - padraocargo.valor) AS excedente

FROM      funcionario
          JOIN pontuacao ON codigo = codfunc
          JOIN padraocargo USING(requisito)

WHERE     pontuacao.valor >= padraocargo.valor

GROUP BY  codigo

ORDER BY  COUNT(*) DESC,
          SUM(pontuacao.valor - padraocargo.valor) DESC
;

Output:

nome  atendidas  excedente
JOSÉ          3         42
JOÃO          2         55

See working on SQL Fiddle .

(at the end of the response there is a version that counts the surplus in another way)

Understanding:

First of all, we have to relate the tables:

JOIN pontuacao ON codigo = codfunc
JOIN padraocargo USING(requisito)

In the case of pontuacao , we want the specific score of each employee, and the fields have different names, so we use ON . In the case of requisito , since the field is equal in both punctuation and padraocargo , we can opt for USING() .

Until then, see our JOIN working: ( fiddle )

nome  pontuacao  desejado
JOSÉ         67        40
JOSÉ         56        48
JOSÉ         76        69
JOÃO         56        40
JOÃO         87        48
JOÃO         34        69

As we look for cases where the score exceeds the standard, we use this condition:

WHERE pontuacao.valor >= padraocargo.valor

Resulting in:

nome  pontuacao  desejado
JOSÉ         67        40
JOSÉ         56        48
JOSÉ         76        69
JOÃO         56        40
JOÃO         87        48

But we do not want to compare line by line. We want to know who is the most qualified only, so we make a grouping per employee:

GROUP BY  codigo

The codigo field is the employee ID, so we use it as a criterion. This will hide the lines, so we need to return in another way:

SELECT    nome,
          COUNT(*) AS atendidas

And to help with reading, we classify starting with the most punctuated:

ORDER BY  COUNT(*) DESC

Okay, we've solved what was asked in the question. But there can be a "draw" in the sense that more than one employee meets the requirement. To improve sorting, we can return the surplus points in this way:

SUM(pontuacao.valor - padraocargo.valor) AS excedente

And, of course, sort as the second criterion:

ORDER BY  COUNT(*) DESC,
          SUM(pontuacao.valor - padraocargo.valor) DESC

Note that the surplus is the one that has passed. It has an interesting outlet if you want to know the general surplus. I will not detail much to not confuse, but in summary is just:

• Remove condition WHERE
• Change% with%

In this way, the surplus will count what has not been serviced as well. ( fiddle )

If you want to return only those employees who pass all criteria, you can do so:

• Take the ordering by COUNT(*) AS atendidas por SUM(IF(pontuacao.valor >= padraocargo.valor,1,0)) AS atendidas , since only the complete ones will be displayed
• Add a atendidas

Okay, but what if each job has different amounts of requirements?

• Do a query before getting the value:

  SELECT @numreq = COUNT(*) FROM padraocargo WHERE cargo = (id do cargo);
  

• Add a HAVING COUNT(*) = 3 at the end of the second query

Further reading:

  

What is the difference between INNER JOIN and OUTER JOIN?

Here's a solution that fits your problem. The logic was simple:

1- Return employees who did not meet the target in one or more requirements.

select f.nome, c.requisito, c.valor VALOR_NECESSARIO, p.valor VALOR_ALCANCADO
  from funcionario f inner join cargo c ON f.cargo = c.cod_cargo
  INNER JOIN pontuacao p ON c.requisito = p.requisito AND c.cod_cargo = f.cargo
  WHERE p.cod_funcionario = f.cod_funcionario
                       and p.requisito = c.requisito
                       and p.valor < c.valor ;

Return:

+------+-----------+------------------+-----------------+
| nome | requisito | VALOR_NECESSARIO | VALOR_ALCANCADO |
+------+-----------+------------------+-----------------+
| JOAO |         2 |               48 |              34 |
+------+-----------+------------------+-----------------+

That is, John did not reach the required value in requirement 2.

2- Now, I make a new query that brings all the employees except those who returned in the previous query (ie, only those who hit all the targets will come).

  select f.cod_funcionario COD_FUNC, f.nome NOME, p.valor VALOR_ALCANCADO
    from funcionario f INNER JOIN pontuacao p ON p.cod_funcionario = f.cod_funcionario
   where f.cod_funcionario not in (
select  f.cod_funcionario
  from funcionario f inner join cargo c ON f.cargo = c.cod_cargo
  INNER JOIN pontuacao p ON c.requisito = p.requisito AND c.cod_cargo = f.cargo
  WHERE p.cod_funcionario = f.cod_funcionario
                       and p.requisito = c.requisito
                       and p.valor < c.valor);

Return:

+----------+------+-----------------+
| COD_FUNC | NOME | VALOR_ALCANCADO |
+----------+------+-----------------+
|        1 | JOSE |              67 |
|        1 | JOSE |              56 |
|        1 | JOSE |              76 |
+----------+------+-----------------+