Lec-63: SQL Queries and Subqueries (part-5) | Database Management System

► Structured Query Language (SQL)(Complete Playlist):

Other subject-wise playlist Links:
--------------------------------------------------------------------------------------------------------------------------------------
►Design and Analysis of algorithms (DAA):
►Computer Architecture (Complete Playlist):
► Theory of Computation
►Artificial Intelligence:
►Computer Networks (Complete Playlist):
►Operating System:
►Database Management System(Complete Playlist):
►Discrete Mathematics:
►Compiler Design:
►Number System:
►Cloud Computing & BIG Data:
►Software Engineering:
►Data Structure:
►Graph Theory:
►Programming in C:
►Digital Logic:

---------------------------------------------------------------------------------------------------------------------------------------
Our social media Links:
--------------------------------------------------------------------------------------------------------------------------------------
►For Any Query, Suggestion or notes contribution:

[rinkirathore]

I think the appropriate query would be this one:
Select E_name, dept, salary
From emp
where (dept, salary) IN
(Select dept, max(salary)
From emp
Group by dept )

[kediarahul]

The correct query should be (tested):
Select Ename, Salary from Emp where (Dept, Salary) In (Select Dept, Max(Salary) from Emp group by Dept);

[satviknema]

Sir add this extra tuple in the table: [6 | Harry | IT | 40000]
According to our query, we will get the output as:
Ravi
Nithin
Varun
Harry

Whereas the answer should have been:
Ravi
Nithin
Varun

I think the error here is, we are only filtering on the basis on salary alone. It maybe possible that one department's highest salary is equal to other department's salary (which is not max in that department). In this example, highest in IT is 50k (Varun), and highest in MRKT is 40k (Nithin). Now Harry is in IT, whose salary is 40k, which is same as the maximum of MRKT, but not the maximum of IT. Hence it also got included in the answer

The solution for this can be, in the outter query, we also add department along with the salary for filtering, such that only the maximum salary with that particular department is included.

The proper query can be:
Select ename
from emp where (dept, salary) IN
(select dept, max(salary)
from emp
group by dept )

[amiteshraj]

I think the appropriate query would be this:
select E_name from emp where (dept, salary) in (select dept, max(salary) from emp group by dept);

[legend]

This will also work
SELECT E_name
FROM EMP
WHERE (Dept, Salary) IN (SELECT Dept, Max(Salary)
FROM EMP
GROUP BY Dept)

[vardaansangar]

The right answer for this is
Select e_name, dept, salary from emp
where (dept, salary) IN
(Select dept, Max(salary) from emp GROUP BY dept) ;

[prateeksachdeva]

Your videos on sql are really the ones which can clear the doubts of any person on this particular topic. thanks sir!

[jahangirahmed]

Your video clips help me a lot to understand the concept of SQL. Before this, I knew nothing about queries. I understand easily what you teaches. God bless u.

[indiancoder]

Sir I think if there is someone with 30000 in IT his name will also be printed even though highest is 50000 in IT

[vishwanathgr]

Here I think, department and salary columns should be added in where and then we can department wise highest salary or else result will have repeatative department wise salary.


Select * from emp where ( dept, salary) in (select dept, max( salary) from emp group by dept )

[surendrasinghrajput]

Sir this query have a drawback.
The query which u wrote also gives those employee names who have the salary 30000, 40000, 50000 even the employee has not max salary in their department.

[OmitA]

no .of rows in output = no. of rows in table A i.e 3 nice explanation upload more videos on it...

[princemishra]

my version of query:
select a.ename, a.salary from emp as a, (select dept as dept, max(salary) as salary from emp group by dept) as b where a.dept = b.dept and a.salary = b.salary;

[weslyworld]

These guys are teaching us for free, spending all their time and energy and there are morons who spend their time to dislike such videos. Let's be humans guys and appreciate such genuine efforts. Dislikers kindly rethink.

[ritikrustagi]

Sir the query for the given question is not correct as if consider the case when the salary of two person of different department is equal and one of them is having a highest salary in it's department. So when the given query is executed, it will display the name corresponding to the highest salary of that department along with the name of other person having the same salary but different department which is not the highest.

[ankanmazumdar]

Please find the more sensible query as there can same employees getting same salaries in different dept.-
select ENAME, salary, DEPT
from (
select *
, DENSE_RANK() over(partition by DEPT order by salary desc) as Highest_sal
from EMP11
) a
where a.Highest_sal = 1

[Naturelover-hcun]

Inner the flow of your videos we are forgotten to like your videos the videos 😅😅😅

[divyanshmishra]

select emp_name, dept_name, salary from employee
where salary in (select max(salary) from employee group by dept_name)
order by salary desc

OR

select max(salary), dept_name from employee
group by dept_name
order by salary desc

[_MilonPaul]

thank you sir given explantion sub query.

[sourabh]

The right query is

select e_name from emp
where (dept, salary) in (select dept, max(salary) from emp group by dept);