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?

technology databases

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

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

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

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

Show answer
Correct Option: B

AI Explanation

To find the maximum salary paid in each job category of each department, we need to group the data by department and job category and then find the maximum salary within each group.

Let's go through each option to determine which one correctly achieves this:

Option A) SELECT dept_id, job_cat, MAX(salary) FROM employees WHERE salary > MAX(salary); This option is incorrect because the WHERE clause is comparing the salary to the maximum salary, which is not a valid comparison. Additionally, the MAX(salary) function cannot be used in the WHERE clause.

Option B) SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat; This option is correct because it uses the GROUP BY clause to group the data by department and job category. It then uses the MAX(salary) function to find the maximum salary within each group.

Option C) SELECT dept_id, job_cat, MAX(salary) FROM employees; This option is incorrect because it does not include the GROUP BY clause, which is necessary to group the data by department and job category.

Option D) SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id; This option is incorrect because it only groups the data by department, but it does not include the job category in the grouping. We need to group by both department and job category to find the maximum salary in each job category of each department.

Therefore, the correct answer is Option B.

Find more quizzes: