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 )
Also the final query doesnt result in the same output that was asked in the question. I really liked some of his videos but these are not for software developers in my opinion but for someone who just wants to clear a test. Good enough for those scenarios i believe :)
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.
@@abhayjaswal3207 well if the highest salary of one dept is equal to normal salary of other dept, it would also the print the data where the salary is not highest but equal to highest salary of some dept.
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 )
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.
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;
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 )
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
This is the right query. This will give you the name, salary, and department of the employees who earn the most in each department. If there are multiple employees in the same department with the same highest salary, they will all be included in the result. SELECT e.first_name, e.salary, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE (e.department_id, e.salary) IN ( SELECT department_id, MAX(salary) FROM employees GROUP BY department_id );
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
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.
Maybe, in the outer query, we can also add another condition of checking the dept name of outer and inner query along with salary. Then I think this problem will not exist.
Yes, you are correct. Check if this might work - select e_name,dept,salary from employee where (dept,salary) in (select dept,max(salary) from employee group by dept);
SELECT name,salary,dept FROM emp where (dept,salary) in (SELECT dept,max(salary) FROM emp group by dept); This one will be the right query................... I assume some features.
select* from emp where concat (dept, " ",salary) in (select concat (dept," ",max(salary)) from emp group by dept) This query will work in all cases. Suggest me any test case if I am wrong.
The Correct query is : Select Fname, Salary from employee where (Dno, Salary) In (Select Dno, Max(Salary) from employee group by Dno); Explaination :- the the inner query will give us the pairs , now outer query will take that pairs and will check if(Dno==Dno && Salary==Salary) and then will give us the output
select name,salary,dept from emp a where salary in ( select max(salary) as max_salary from emp b where a.dept=b.dept group by dept ) OR select a.name,a.salary,b.dept from emp a inner join (select dept,max(salary) as salary from emp group by 1) b on (a.dept=b.dept and a.salary = b.salary)
Sir your quary is only showing the names who are taking Max salary. But as per your question it should be shown highest salary, dept, and E name. (Tasted in Microsoft SQL server)
Hello Varun. First, thanks for creating these videos. Now, kindly tell us a query where we can display the name of employees drawing hishest salary, Dept name and salary simultaneously in a single query. Thanks bro.
Sir is query ma issue ye a skta k agr kisi or dept k bndy ki salaray chahty kuch bhi ho agr wo match kr jaye kisi or dept k max salaried employee sa to us ka data bhi print ho jana. Correct me if I am wrong. Anyways amazing lectures and quality.
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.
As per question answer should be:: select PERSON_NAME,DEP_ID,sal from employee where (DEP_ID,sal) in (select DEP_ID,max(sal) from employee group by DEP_ID);
I think you dont need to put inner query this will work fine.. Select Dept,emp_Name,Max(Salary) from Employee group by Dept,emp_name; This query wll give you name of employee with max salary in each department.
when you group by emp_name then actually each rows will be considered as a seperate group...so,all the salaries will be returned in the mas(sal) column...and it will be same as the original table...
If the highest salary of one department is the lowest salary of another department then also that employeename will be printed . It would work well if we take department name with max salary inside IN
at around 10:00,, maybe there be chances that more than one dep. having the same maximum salary,,,,, what about that case? selecting name in that case may work differently
I think this solution is Partially right only becoz if an employee from a department is taking minimum salary in that department but that salary is maximum in some other department then his name will be printed which is wrong.
BHAI MAI APKEY VIDEOS HAMESHSA FOLLOW KARTHA HU AND I LIKE THE WAY U TEACH US I HAVE LIL DOUBT WHILE IM APPLYING TO GET NAME OF EMP ITS SHOWING SYNTAX ERORR HOPE U WILL SEE MY COMMENT LOVE U BRO FROM ANDHRA PRADESH❤
this wrong right is :- select ename ,concat(salary+dept)from employee where concat(salary,dept) in (select concat(max(salary),dept) from employee group by dept);
I used 'JOIN' to get the desired output instead of nested query - not sure how nested query works here. -- create a table CREATE TABLE students ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, dept TEXT NOT NULL, salary INT not null ); -- insert some values INSERT INTO students VALUES (1, 'Ryan', 'IT', 10000),(2, 'Joanna', 'MRKT', 20000), (3, 'Ravi', 'HR', 30000), (4, 'Nitin', 'MRKT', 40000), (5, 'Varun', 'IT', 50000), (6, 'Shivam', 'MRKT', 30000); -- fetch some values Select S.name, S.dept, S.salary From students as S JOIN (Select dept, max(salary) as salary From students Group by dept) as T on S.dept = T.dept and S.salary =T.salary;
Both are different parts of the question. If you want highest salary with name then you can use select name,salary from emp where salary IN (select max(salary) from emp group by dept);
What if we have two tables separately, in which one table contain employee salary, name then other table contain department name and common attribute is dno
You are amazing man. Thank you so much for your lovely sessions. I particularly like the punjabi flavour in your Hindi. These sessions have been of so much use to people like us. May you always stay healthy and happy. Coud you make a few sessions on SQL queries that are frequently used by manual testers. Would be of a great great help. Thank you once again from the bottom of my heart. Let us know your name dude.
If table is Ram-HR-10000,ravi-HR_20000,shiv-IT-10000 Then this query would fail Coz inner query will return (10000,20000) N here ram also get 10000 but that is not maximum salary in his dept
What if Amrit salary was 30000 Then Amrit name will also be displayed because 30000 is IN the subquery. But from marketing department Nitin is taking maximum salary and not Amrit. So the query what sir told doesn't display name department. It just display the name who are taking max salary. And not who are taking max salary in particular department. Correct me if I'm wrong.
Query provided by you sir is wrong. Correct query is : SELECT e.E_name, e.dept, e.salary FROM emp e JOIN ( SELECT dept, MAX(salary) AS max_salary FROM emp GROUP BY dept ) d ON e.dept = d.dept AND e.salary = d.max_salary;
correct me if im wroung im righting this query expectinf there is reduntant data in salary : select e_name from emp where salery in(select max(salary from group by dept) and where dept in (select dept from group by dept)
Hello Sir, I have one doubt on this final query to find names of emp who having highest salary dept wise. Suppose, a emp working on MRKT whose salary is 30,000 (but in MRKT dpt highest salary is 40,000) . According to this query that emp name also come to output as that 30,000 value is in the inner query. Kindly please help me to clear my doubt.
SQL> select first_name from worker2 where salary in(select department,max(salary) from worker2 group by department) * ERROR at line 1: ORA-00913: too many values "I am having this error in oracle 11g". Cant resolve.
![ทุกครั้งที่หลับตา (Lucid Dream) - AYLA's [ Official MV ]](http://i.ytimg.com/vi/4i1OBAQyv58/mqdefault.jpg)
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 )
Ya right, but sir ne sabse pehle sirf salary likha tha inner query me so it's acc to that.
Correct buddy.
@@sunidhidwivedi2181 what happens when 2 depts have same max salary
Ya right dude 🤘
Also the final query doesnt result in the same output that was asked in the question. I really liked some of his videos but these are not for software developers in my opinion but for someone who just wants to clear a test. Good enough for those scenarios i believe :)
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);
Thanks
وماذا لو كان هناك تشابه في القيم (نفس الأجر)ستكون النتيجة خاطئة
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.
Your videos on sql are really the ones which can clear the doubts of any person on this particular topic. thanks sir!
The correct query should be (tested):
Select Ename, Salary from Emp where (Dept, Salary) In (Select Dept, Max(Salary) from Emp group by Dept);
Yes it's correct, but what's wrong with the query that he has explained. Why it is not generating desired results as it seems to be correct as well.
@@abhayjaswal3207 watch the concepts of "group by" clause you'll get whats wrong.....
@@abhayjaswal3207 well if the highest salary of one dept is equal to normal salary of other dept, it would also the print the data where the salary is not highest but equal to highest salary of some dept.
Thanks
❤
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 )
Greatly explained thnaks 😄😄
perfect!
I Hv doubt on this but now cleared thank all.
th-cam.com/video/QTaiF8N6i3Y/w-d-xo.html
th-cam.com/video/QTaiF8N6i3Y/w-d-xo.html🙏🏻
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) ;
will this query work? - select dept, e_name, max(salary) from emp
group by dept, e_name;
@@ankush_in_sync5998nope cause we can use only aggregate function or the attribute which is used for group by after the select
This will also work
SELECT E_name
FROM EMP
WHERE (Dept,Salary) IN (SELECT Dept,Max(Salary)
FROM EMP
GROUP BY Dept)
th-cam.com/video/QTaiF8N6i3Y/w-d-xo.html
th-cam.com/video/QTaiF8N6i3Y/w-d-xo.html🙏🏻
no .of rows in output = no. of rows in table A i.e 3 .........................very nice explanation ........sir upload more videos on it...
Thanks Om..Sure we will..Keep learning and sharing..
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.
This video had a mistake, which still hasn't been rectified yet, maybe because of that the dislikes were more on this one.
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;
Inner the flow of your videos we are forgotten to like your videos the videos 😅😅😅
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 )
Yes
yes
Best vedio ever seen everything is cleared in sql.Thank you Sir.
I think this is enough since group by displays only a single tuple from each group :)
select emp,max(salary),dept
from emp
group by dept
you cant use other attributes with group by, but only in aggregate functions
th-cam.com/video/QTaiF8N6i3Y/w-d-xo.html
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
Man you are a lifesaver!
The Query worked really smooth!
Thanks!
Thanks for share this video
thank you sir given explantion sub query.
This is the right query. This will give you the name, salary, and department of the employees who earn the most in each department. If there are multiple employees in the same department with the same highest salary, they will all be included in the result.
SELECT e.first_name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE (e.department_id, e.salary) IN (
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
);
Select ename,dept from emp where salary IN (Select MAX(salary) from emp group by dept);
this will print ename,dept,salary in alongside manner
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
The right query is
select e_name from emp
where (dept,salary) in (select dept,max(salary) from emp group by dept);
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.
Maybe, in the outer query, we can also add another condition of checking the dept name of outer and inner query along with salary. Then I think this problem will not exist.
Exactly correct
Anyone has solution for this using correlated subquery
yeah true..
Yes, you are correct.
Check if this might work -
select e_name,dept,salary from employee
where (dept,salary) in (select dept,max(salary) from employee group by dept);
Well explained and more class expect sir...... This section...sql....
Sir I think if there is someone with 30000 in IT his name will also be printed even though highest is 50000 in IT
Same ques? May be someone else with other dept have salary equal to tha max salary of other dept .
Exactly...
Please give correct solutions
select max(salary),dept_name, emp_name from employee
group by dept_name
order by salary desc
this will run in all cases
Very informative video 👍🏻 thank you ✌🏻
Thank you sir....
great explaination
Ans is : select e_name , dept , salary from emp where (dept,salary) in (select dept,max(salary) from emp group by dept);
yes
Thankss @anom_wiz-gaming
SELECT name,salary,dept
FROM emp
where (dept,salary) in
(SELECT dept,max(salary)
FROM emp
group by dept);
This one will be the right query................... I assume some features.
good one
I'm Wasting my fees in university To be honest ...you're real GEM .. Much Respect From Pakistan
select* from emp where concat (dept, " ",salary) in (select concat (dept," ",max(salary)) from emp group by dept)
This query will work in all cases.
Suggest me any test case if I am wrong.
Aap bhaut payar se samjhate hai
The Correct query is :
Select Fname, Salary from employee where (Dno, Salary) In (Select Dno, Max(Salary) from employee group by Dno);
Explaination :-
the the inner query will give us the pairs , now outer query will take that pairs and will check if(Dno==Dno && Salary==Salary) and then will give us the output
select name,salary,dept from emp a where salary in (
select max(salary) as max_salary from emp b
where a.dept=b.dept
group by dept )
OR
select a.name,a.salary,b.dept from emp a inner join
(select dept,max(salary) as salary from emp group by 1) b on (a.dept=b.dept and a.salary = b.salary)
Excellent
select ename from emp where (dep,salary) IN( select dep,MAX(salary) from emp GROUP BY dep );
Sir your quary is only showing the names who are taking Max salary.
But as per your question it should be shown highest salary, dept, and E name.
(Tasted in Microsoft SQL server)
This query will work :
select Distinct e_name,dept,salary from emp where (dept , salary) in (select dept,max(salary) from emp group by dept);
select E_name, salary
from Emp
where dept in
(select dept from Emp group by dept having max(salary));
sir...u r fab :)
Select E_name,dept, salary
From emp
where salary IN
(Select max(salary)
From emp
Group by dept );
Select emp name where salary = (Select max(salary) , department from emp group by department id)
You explained expected scenario in very easy way :)
Observe carefully
Query framed by Varun Sir is absolutely right
Hello Varun. First, thanks for creating these videos. Now, kindly tell us a query where we can display the name of employees drawing hishest salary, Dept name and salary simultaneously in a single query. Thanks bro.
Time [ 8:30 ] Query :-
SELECT ENAME, DEPT, SALARY FROM EMP WHERE (DEPT,SALARY) IN (SELECT DEPT,MAX(SALARY) FROM EMP GROUP BY DEPT);
Outstanding work thank you 🙏
Dept should be there in the select of subqurey because group by attribute should be included in select...
My approch was-
Select dept, salary
From employee
Group by dept having max(salary)
Thanku sir
Very nice sir
Sir is query ma issue ye a skta k agr kisi or dept k bndy ki salaray chahty kuch bhi ho agr wo match kr jaye kisi or dept k max salaried employee sa to us ka data bhi print ho jana.
Correct me if I am wrong.
Anyways amazing lectures and quality.
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.
As per question answer should be::
select PERSON_NAME,DEP_ID,sal from employee where (DEP_ID,sal) in (select DEP_ID,max(sal) from employee group by DEP_ID);
thnkss alott ji..u r the great ji...God bless u forever....🙏🙏🙏🙏🙏🙏
Very well explained😊
You are the best ❤️😌
Fire ho app🔥🔥
Select emp name where salary in (Select max(salary) , department from emp group by department id)
I think you dont need to put inner query this will work fine.. Select Dept,emp_Name,Max(Salary) from Employee group by Dept,emp_name; This query wll give you name of employee with max salary in each department.
when you group by emp_name then actually each rows will be considered as a seperate group...so,all the salaries will be returned in the mas(sal) column...and it will be same as the original table...
love u sir u are great like me ...b/c u were creating video i am following sequence now, i am at video number 62..
Btw i subscribed and will recommend your channel to my contacts. Super Like.
Thank you so much sir
V v v vv good sir g
If the highest salary of one department is the lowest salary of another department then also that employeename will be printed . It would work well if we take department name with max salary inside IN
Thank you so much sir now I am confident writing queries. 👍
Thank u so much its very important for 10 class also
Thank you sir ☺️
THANKS A LOT SIR
at around 10:00,, maybe there be chances that more than one dep. having the same maximum salary,,,,, what about that case? selecting name in that case may work differently
I think this solution is Partially right only becoz if an employee from a department is taking minimum salary in that department but that salary is maximum in some other department then his name will be printed which is wrong.
hats off!
BHAI MAI APKEY VIDEOS HAMESHSA FOLLOW KARTHA HU AND I LIKE THE WAY U TEACH US I HAVE LIL DOUBT WHILE IM APPLYING TO GET NAME OF EMP ITS SHOWING SYNTAX ERORR HOPE U WILL SEE MY COMMENT LOVE U BRO FROM ANDHRA PRADESH❤
this wrong right is :- select ename ,concat(salary+dept)from employee where concat(salary,dept) in (select concat(max(salary),dept) from employee group by dept);
Best teacher you are ❤️🤗
Bad English you have ❤️🤗
@@pranavbhanot816 You. Tooo 🤗🤗
We can use order by if we are not using sub queries
love you sir from nepal...
nepali apna map thik kra lo...boht pele jaoge warna
@@gaurav01911 haha
I used 'JOIN' to get the desired output instead of nested query - not sure how nested query works here.
-- create a table
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL,
salary INT not null
);
-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'IT', 10000),(2, 'Joanna', 'MRKT', 20000),
(3, 'Ravi', 'HR', 30000), (4, 'Nitin', 'MRKT', 40000), (5, 'Varun', 'IT', 50000),
(6, 'Shivam', 'MRKT', 30000);
-- fetch some values
Select S.name, S.dept, S.salary
From students as S
JOIN (Select dept, max(salary) as salary From students Group by dept) as T
on S.dept = T.dept
and S.salary =T.salary;
Thanks 😊
subscribed and belled . very detailed
#Gate Smashers
Sir in this question query is to write highest salary dept wise and with name
But
In the given sol it's just giving the name?
Both are different parts of the question. If you want highest salary with name then you can use
select name,salary from emp where salary IN (select max(salary) from emp group by dept);
@@GateSmashers thank you so much sir ur lessons r too good... 🙏
Please make video on software engineering n data structure
Select dept,max(salary),ename from employee group by dept,salary; query likh sakte hai kya
Sir outer query me select e_name,salary ayega shaayd because hme dono haiye output me
What if we have two tables separately, in which one table contain employee salary, name then other table contain department name and common attribute is dno
This should be the correct query (tested) -
SELECT E_NAME, SALARY
FROM EMP
WHERE SALARY IN
(
SELECT MAX(SALARY)
FROM EMP
GROUP BY DEPT
);
No it’s wrong.
hats offff u sir
You are amazing man. Thank you so much for your lovely sessions. I particularly like the punjabi flavour in your Hindi. These sessions have been of so much use to people like us. May you always stay healthy and happy. Coud you make a few sessions on SQL queries that are frequently used by manual testers. Would be of a great great help. Thank you once again from the bottom of my heart. Let us know your name dude.
If table is
Ram-HR-10000,ravi-HR_20000,shiv-IT-10000
Then this query would fail
Coz inner query will return (10000,20000)
N here ram also get 10000 but that is not maximum salary in his dept
What if Amrit salary was 30000 Then Amrit name will also be displayed because 30000 is IN the subquery. But from marketing department Nitin is taking maximum salary and not Amrit. So the query what sir told doesn't display name department. It just display the name who are taking max salary. And not who are taking max salary in particular department. Correct me if I'm wrong.
Query provided by you sir is wrong. Correct query is : SELECT e.E_name, e.dept, e.salary
FROM emp e
JOIN (
SELECT dept, MAX(salary) AS max_salary
FROM emp
GROUP BY dept
) d
ON e.dept = d.dept AND e.salary = d.max_salary;
but sir wasn't the use use of dept necessary if we use group by dept??
Thanks
correct me if im wroung im righting this query expectinf there is reduntant data in salary :
select e_name from emp where salery in(select max(salary from group by dept) and where dept in (select dept from group by dept)
Sir plzz upload more videos on more complex queries
sir what will happen if one more tuple is there in employee table whose salary is 40,000 and department is IT.this query will print that name also
If any condition HR maximum salary 30,000 and MRKT minimum salary 30,000 who's E_name are printed in this queries
Obviously it will not work in this query
Then may be we have to compositly check the dept and salary both after finding group by max salary of each department
Thank you sir
Then it will show the the name corresponding to highest salary in MRKT
Hello Sir, I have one doubt on this final query to find names of emp who having highest salary dept wise.
Suppose, a emp working on MRKT whose salary is 30,000 (but in MRKT dpt highest salary is 40,000) . According to this query that emp name also come to output as that 30,000 value is in the inner query.
Kindly please help me to clear my doubt.
SQL>
select first_name from worker2 where salary in(select department,max(salary) from worker2 group by department)
*
ERROR at line 1:
ORA-00913: too many values
"I am having this error in oracle 11g". Cant resolve.