Examine the structure of the employees table.
You want to display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would get the correct output?
A.
Option A
B.
Option B
C.
Option C
D.
Option D
Explanation:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.s
qlref%2Fsrc%2Ftpc%2Fdb2z_sql_subselectexamples.htm
A and C are correct, B and D wrong – “group by” some function is not allowed there.
The answers are wrong. A and C are correct as said by Deivsto. Group by is not needed. Although C has the group by but it’s kind of useless.
I don’t understand why c works. In my opinion, there is no big difference between C and D. So strange.
because aggregate functions are not allowed with group by function
B and D don’t work.
If a group function is placed in a WHERE clause, an “ORA-00934: group function is not allowed here” error is returned.
A and C are Correct. Tested.
A and C are Correct. Tested.
select minsal, maxsal
from(select min(salary) minsal, max(salary) maxsal
from employees
where hire_date <sysdate-365 group by min(salary) , max(salary) ) ; —B incorrect
select min(salary) minsal, max(salary) maxsal
from employees
where hire_date <sysdate-365
group by min(salary) , max(salary) ; —D incorrect
select min(salary), max(salary)
from(select salary from employees where hire_date <sysdate-365) ; —-A : correct
select minsal, maxsal
from(select min(salary) minsal, max(salary) maxsal
from employees
where hire_date <sysdate-365 )
group by minsal, maxsal; ———C: correct