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.
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.
Comments