Question 7: Group By

Examine the description of the EMPLOYEES table:

EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
JOB_CAT VARCHARD2(30)
SALARY NUMBER(8,2)

Which statement shows the maximum salary paid in each job category of each department?


A.SELECT dept_id, job_cat, MAX(salary) FROM employees
WHERE salary > MAX(salary);

B.SELECT dept_id, job_cat, MAX(salary) FROM employees
GROUP BY dept_id, job_cat;

C. SELECT dept_id, job_cat, MAX(salary) FROM employees;

D. SELECT dept_id, job_cat, MAX(salary) FROM employees
GROUP BY dept_id;


Answer: B


Explanation:
This answer provides correct syntax and semantics to show the maximum salary paid in each job category of each department.



Incorrect Answers

A: This query will not return any row because condition SALARY > MAX(SALARY) is FALSE.

C: This query will return error because you cannot show maximum salary with DEPT_ID and JOB_CAT without grouping by these columns.

D: The GROUP BY clause is missing JOB_ID column.

E: You don't need to group results of query by SALARY in the GROUP BY column.