Question

Consider the following relations:
emp(empno, ename, age, job, salary, dob, doj, deptno)
Find employees who earns highest salary for their job
(A)select ename, job, salary from emp
where sal in (select max(sal) from emp group by job);
(B) select ename, job, salary from emp
where (job, sal) in(select job, max(sal) from emp group by job);
(C) select ename.job, salary from emp
where (job, sal) = (select job, max(sal) from emp group by job):
(D) Both B and C​

Answer 1

Answer:

The answer is A

Explanation:

As it is asked that we have to find all the employees that who earn highest salaries in their "particular" jobs.

Answer 2

Answer:

The correct answer is option(A)

select ename, job, salary from emp

where sal in (select max(sal) from emp group by job);

Explanation:

The given relation is :

emp(empno, ename, age, job, salary, dob, doj, deptno)

We are asked to find those employees who earn the highest salary for their job.

• So first we will use the select command to select the details of the employe like ename, job, and, salary.

select ename, job, salary from emp

• After that, we need to specify the criteria for the highest salary. For selecting the highest salary we will use the max function of SQL.

select ename, job, salary from emp where sal in (select max(sal)

• Since we applying this selection on the emp table so just write the table name after the selection.

select ename, job, salary from emp where sal in (select max(sal) from emp

• Lastly, group all the employees by their jobs. So final query looks like this:

select ename, job, salary from emp where sal in (select max(sal) from emp group by job);

#SPJ2