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 VARCHAR2(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 v

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

Show answer
Correct Option: B
Explanation:

To solve this question, the user needs to know SQL syntax for selecting data from a table, using aggregate functions like MAX(), and grouping data using GROUP BY.

Option A: This option is incorrect because it includes a comparison statement with MAX(salary), which is not valid. The MAX() function can only be used with aggregate functions or in GROUP BY queries.

Option B: This option is correct. It selects the department ID, job category, and maximum salary for each group of department ID and job category. The GROUP BY clause is used to group the data by department ID and job category.

Option C: This option is incorrect because it does not include a GROUP BY clause, so it would return the overall maximum salary in the entire table, rather than the maximum salary for each job category in each department.

Option D: This option is incorrect because it selects the same data as option B, but uses an alias for the table name. This is not necessary and does not affect the query.

Option E: This option is incorrect because it includes the salary column in the GROUP BY clause, which would group the data by salary as well. This would result in multiple rows for each department ID and job category, with the same maximum salary value.

Therefore, the correct answer is: B. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;

Find more quizzes: