Você tem uma tabela EMPLOYEE com as colunas nome, departamento, salário.
Quais são as duas instruções SQL que produzem um relatório que mostra apenas os funcionários com um salário maior do que a média salarial do departamento?
Escolha duas respostas corretas:
a) SELECT name, dept, salary FROM EMPLOYEE e WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE f WHERE e.dept = f.dept);
b) SELECT name, dept, salary FROM EMPLOYEE GROUP BY dept HAVING salary > AVG(salary);
c) SELECT name, dept, salary FROM EMPLOYEE e HAVING (SELECT AVG(salary) FROM EMPLOYEE f WHERE f.dept = e.dept GROUP BY dept) < salary;
d) SELECT name, dept, salary FROM EMPLOYEE,(SELECT dept as avg_dept, AVG(salary) as avg_sal FROM EMPLOYEE GROUP BY 1) a WHERE dept = avg_dept AND salary > avg_sal;
e) SELECT name, dept, salary FROM EMPLOYEE e WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE f GROUP BY dept) AND e.dept = f.dept;
Alguém consegue me ajudar com essa questão?
PS: se possível, favor explicar.
Att,