Lec-63: SQL Queries and Subqueries (part-5) | Database Management System
HTML-код
- Опубликовано: 17 сен 2018
- 👉Subscribe to our new channel: / @varunainashots
► Structured Query Language (SQL)(Complete Playlist):
• Structured Query Langu...
Other subject-wise playlist Links:
--------------------------------------------------------------------------------------------------------------------------------------
►Design and Analysis of algorithms (DAA):
• Design and Analysis of...
►Computer Architecture (Complete Playlist):
• Computer Organization ...
► Theory of Computation
• TOC(Theory of Computat...
►Artificial Intelligence:
• Artificial Intelligenc...
►Computer Networks (Complete Playlist):
• Computer Networks (Com...
►Operating System:
• Operating System (Comp...
►Database Management System(Complete Playlist):
• DBMS (Database Managem...
►Discrete Mathematics:
• Discrete Mathematics
►Compiler Design:
• Compiler Design (Compl...
►Number System:
• Number system
►Cloud Computing & BIG Data:
• Cloud Computing & BIG ...
►Software Engineering:
• Software Engineering
►Data Structure:
• Data Structure
►Graph Theory:
• Graph Theory
►Programming in C:
• C Programming
►Digital Logic:
• Digital Logic (Complet...
---------------------------------------------------------------------------------------------------------------------------------------
Our social media Links:
► Subscribe to us on RUclips: / gatesmashers
►Subscribe to our new channel: / @varunainashots
► Like our page on Facebook: / gatesmashers
► Follow us on Instagram: / gate.smashers
► Follow us on Instagram: / varunainashots
► Follow us on Telegram: t.me/gatesmashersofficial
► Follow us on Threads: www.threads.net/@gate.smashers
--------------------------------------------------------------------------------------------------------------------------------------
►For Any Query, Suggestion or notes contribution:
Email us at: gatesmashers2018@gmail.com
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 :)
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.
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
❤
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 videos on sql are really the ones which can clear the doubts of any person on this particular topic. thanks sir!
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)
ruclips.net/video/QTaiF8N6i3Y/видео.html
ruclips.net/video/QTaiF8N6i3Y/видео.html🙏🏻
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
Outstanding work thank you 🙏
Well explained and more class expect sir...... This section...sql....
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.
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.
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;
Thank u so much its very important for 10 class also
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
Very informative video 👍🏻 thank you ✌🏻
Btw i subscribed and will recommend your channel to my contacts. Super Like.
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
ruclips.net/video/QTaiF8N6i3Y/видео.html
sir...u r fab :)
thank you sir given explantion sub query.
Thanks for share this video
Very well explained😊
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.
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..
so can i use directly aggregate functions for salary even if im using group by clause for the department?
Inner the flow of your videos we are forgotten to like your videos the videos 😅😅😅
subscribed and belled . very detailed
You explained expected scenario in very easy way :)
Thank you so much 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.
great explaination
Aap bhaut payar se samjhate hai
Thank you so much sir now I am confident writing queries. 👍
You are the best ❤️😌
Thank u sir
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);
Thanks
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)
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
In my database, i'm getting 12 rows for the inner query but when I apply the outer query, i'm getting 24 rows. How is that possible?
Thanku 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.
Excellent
hats off!
Thanks 😊
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
Thank you sir ☺️
Sir, if max salary for each group is same ,and name of the employees are different then how to retrieve those names..??
how aggregate fun work on the group by clause after grouping into a group
I am assuming when our table is grouped in to dept then how there is an occurrence of salary which is extracted by the aggregate function in above example
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.
Thank you sir
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.
ruclips.net/video/QTaiF8N6i3Y/видео.html
ruclips.net/video/QTaiF8N6i3Y/видео.html🙏🏻
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
);
Best vedio ever seen everything is cleared in sql.Thank you Sir.
Fire ho app🔥🔥
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..
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)
bhaiya agar sub query me dept bhi add krre hai to run nhi hora , too many values ara , why?
The right query is
select e_name from emp
where (dept,salary) in (select dept,max(salary) from emp group by dept);
V v v vv good sir g
hello sir
in this query, if there r more than 1 employee, who is getting maximum salary than what will be the output????
Very nice sir
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
Sir outer query me select e_name,salary ayega shaayd because hme dono haiye output me
hats offff u sir
select E_name, salary
from Emp
where dept in
(select dept from Emp group by dept having max(salary));
sir, in the last query if 30000 salary may be one of the other dept candidate can also have in that case it will it retrieve that candidate name
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
Sir what if maximum salary vale ka naam print karvana ho table mei. How will we do that if its not possible to use other column than dept in SELECT???
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 query will work :
select Distinct e_name,dept,salary from emp where (dept , salary) in (select dept,max(salary) from emp group by dept);
👍👍
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...
Sir plzz upload more videos on more complex queries
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
Pls tell us about platform for using SQL ,pls describe the link.....
Nice
My approch was-
Select dept, salary
From employee
Group by dept having max(salary)
Best teacher you are ❤️🤗
Bad English you have ❤️🤗
@@pranavbhanot816 You. Tooo 🤗🤗
what if there is one more row with dept IT and person having 1000 salary ... it will aslo get printed ryt ? so it says that the query is wrong !!
sir, if hr and mrkt has same max salary will have any confusion?
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);
Dept should be there in the select of subqurey because group by attribute should be included in select...
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
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)
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
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.
Sir, I have a doubt
What if there is another tuple in the table with data : E_id = 5, E_name = Mohan, Dept = IT, Salary = 40000
then the person Mohan will also be in OUTPUT?
Please, comment on this.
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
suppose if we want to print all the information about RAVI
then what is the format
Yogesh Rai select * from employee where e_name = ‘ravi’
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
Select dept,max(salary),ename from employee group by dept,salary; query likh sakte hai kya
Select emp name where salary = (Select max(salary) , department from emp group by department id)
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;
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
What if the Salary Column includes the following salaries as per the order- HR->10K, MRKT->30K, HR->30K, MRKT->50K, IT->50K. Then as per the query, we will get Highest Salaries as MRKT->30K, MRKT->50K, IT->50K...Which is wrong, Because HR(30K),MRKT(50K),IT(50K).. Please guide me..!
Sir yaha hr me highest salary 30000 h
Agar sir mrkt me Amrit ka salary 30000 hota to jb ename find krre te tb 30000 ke liye do do names aate kya
Or
Kya jo ename h vo sirf salary k numbers se match krra h ya particular Dept me jo salary h usse match krra h?
Sir can we write like this
Select ename, max (salary) from emp where dept in (select dept group by dept) order by salary