good explanation on sub query and co-related queries, but i think the example given for co-related queries is a terrible solution for finding the second highest salary, this can be much better achieved using a sub query like -> select top 1 .... from (select top 2 ... from ..... order by salary desc) as top2salaries order by salary asc
Like a loop withn a loop , Each row of e1 is passed and checked with the condition e2.salary greater than e1.salary and if it satsfies count e2.salary, then outer query conditions that is if is two then execute the outer query and print empname
THANKs for clear explanation of Co-related Sub-query. But the query shown for Co-related Sub-query does NOT fetch ANY records when multiple employees have SAME salaries and also gives Incorrect records of Nth Highest salary if multiple employees have SAME salaries in a Table. We can correct the query by adding "DISTINCT" in INNER query. select * from emp E1 where 2 =(select Count(DISTINCT E2.salary) from emp E2 where E2.salary >= E1.salary)
The difference between both of them is of references ...as in subquery we have no reference or relation between both of inner and outer query but in the latter one we referenced the outer query inside the inner query.
Your X Highest salary will only worked when there is only one employee having x Highest salary. If there are more then one employee with X highest salary then your query will return no output
Hi sir When I am converting Date to integer or decimal using cast and convert, it's reflecting the date value less than its actual value. Please suggest.
Sir,, a very nice explanation from your side.., sir plz tell me which software you are using for SQL queries,,, and if it is available for free,, send me the link.
Thanks for your video. It was easy to follow. I had a question in related to co-related query. All the record in table has unique salary. What happens if there are two people who had same salary and we wanted the second highest salary? Is that number going to be 2 still??
Actually through innere query he is counting number of employees having greater salary than outer query employees id In fist case count is 4 In second case count is 4 Like dt when ever count is 2 dt employee ID will be selected as final answer
Hi guys, I have a question. I want to figure out e2 from the query. How can I find that? I know if we want to change Table's name with 'as' after that it shows in new table where EmpSal changes to e2. But could we put it before 'from'? How COUNT(e2.Salary) work their?
Thank you for the explanation sir, just a doubt I had and that being can't we right a simple code in order to get the second highest salary? Like: SELECT EmpName from EmpSal ORDER BY Salary DESC LIMIT 1 OFFSET 1; Pls. do correct me if I did something wrong :)
I do not know the answer to your question. However subqueries are used in order to make filtering a lot easier and often they are used in very complex situations. In this video we only have a simple example so it is expected that we can use many different methods to accomplish the same thing.
@@SamVsCode yes right. See this query which will find any higest number of query like 2nd, 3rd...10th highest select * from empsal where salary=( select min(salary) from empsal where salary in( SELECT top(3) salary from EmpSal order by salary desc ))
Use ROW_NUMBER OR RANK window functions to achieve what you've asked. Assume the table has an entry as : pulkit 150 5 respectively. Then the below RANK function will display both pulkit and raju. select * from (select e. * , ROW_NUMBER() over (order by salary desc) row_num from empsal e) where row_num=3; OR select * from (select e. * , RANK() over (order by salary desc) rank from empsal e) where rank=3;
20+ SQL Server Interview Questions : th-cam.com/video/SEdAF8mSKS4/w-d-xo.html
20 MSBI Interview Questions : th-cam.com/video/Nw_sHEKnOUE/w-d-xo.html
10+ Power BI Interview Questions : th-cam.com/video/Cozc9WNBRt4/w-d-xo.html
SQL Server Joins : th-cam.com/video/KTvYHEntvn8/w-d-xo.html
SQL Step by Step - th-cam.com/video/uGlfP9o7kmY/w-d-xo.html
Software Architecture Interview Questions : th-cam.com/video/AtTgcbLOqMM/w-d-xo.html
Angular Step by Step Tutorial for Beginners : th-cam.com/video/-9VcW7MBDs8/w-d-xo.html
25 Angular Interview Questions : th-cam.com/video/-jeoyDJDsSM/w-d-xo.html
35 Important JavaScript Interview Questions : th-cam.com/video/Zb4dPi7CANU/w-d-xo.html
30 Important C# Interview Questions : th-cam.com/video/BKynEBPqiIM/w-d-xo.html
25+ OOPS Interview Questions : th-cam.com/video/u99wAoBjDvQ/w-d-xo.html
25 Important ASP.NET Interview Questions : th-cam.com/video/pXmMdmJUC0g/w-d-xo.html
20 PHP Interview Questions : th-cam.com/video/1bpNSynUrl8/w-d-xo.html
5 MSBI Interview Questions : th-cam.com/video/5E815aXAwYQ/w-d-xo.html
20 MySQL Interview Questions : th-cam.com/video/9hfjC-BpY20/w-d-xo.html
Instablaster.
Searched from different channels, but at last , you clarified what subquery actually is. Thank You Sir who ever you are❤️
Your explanations are very clear sir,
keep up your good work,
may god bless you!!
This is the best explanation I have ever seen.Thank you so much for such elaborated explanation
good explanation on sub query and co-related queries, but i think the example given for co-related queries is a terrible solution for finding the second highest salary, this can be much better achieved using a sub query like -> select top 1 .... from (select top 2 ... from ..... order by salary desc) as top2salaries order by salary asc
Thanks...you cleared my this concept 👍👍👍
Great and simple explanation with examples. came here after watching a couple of videos that failed to explain this concept so neatly.
Your all videos of both channel are just excellent..I wish if all may be free to access
👊👊👊👊👊👊Smash Subscribe button if you like it.👊👊👊👊👊👊
i already smashed.....i cant do one more smash....it will get un subscribe
@@kirankumar-iz3tw LOL!
Clear, precise and concise, thank you for your time.
Excellent explanation sir.....it's really helped me
Thank you so much bro, helped me a lot than my professor or books! Keep it up!
OUTSTANDING. You're explanation/demonstration. Well done. I get it. What a big help, thank you, thank you. I finally understand.
best explanation about Correlated Subqueries
have u purchased subcription ??
Like a loop withn a loop , Each row of e1 is passed and checked with the condition e2.salary greater than e1.salary and if it satsfies count e2.salary, then outer query conditions that is if is two then execute the outer query and print empname
Decent explanation without confusing.. Excellent
Nicely explained! Thank you for sharing!
THANKs for clear explanation of Co-related Sub-query.
But the query shown for Co-related Sub-query does NOT fetch ANY records when multiple employees have SAME salaries and also gives Incorrect records of Nth Highest salary if multiple employees have SAME salaries in a Table. We can correct the query by adding "DISTINCT" in INNER query.
select *
from emp E1
where 2 =(select Count(DISTINCT E2.salary)
from emp E2
where E2.salary >= E1.salary)
Thanks got clear cut idea about corelated query.
Yr explaintion is awesome, pls make more
The difference between both of them is of references ...as in subquery we have no reference or relation between both of inner and outer query but in the latter one we referenced the outer query inside the inner query.
Thank you so mch... Finally i understood subqueries and co-related queries...
Your Videos are seriously worthful...... thanku...
Very good explanation..really helpful
Good accent. Fast and furious nice and clean
have u purchased subcription ??
VERY NICE LECTURE .. SUPER EXPLATION .. HELPED ME A LOT
Great Explaination
Excellent 👌
Your X Highest salary will only worked when there is only one employee having x Highest salary. If there are more then one employee with X highest salary then your query will return no output
well explain the concept but this will work only if we do not have duplicate salaries, I think you need to use count(distinct(salary))
Hi sir
When I am converting Date to integer or decimal using cast and convert, it's reflecting the date value less than its actual value.
Please suggest.
Sir,, a very nice explanation from your side.., sir plz tell me which software you are using for SQL queries,,, and if it is available for free,, send me the link.
This query is not produced the result if salary will be same for another employee means two different employees salary will be same as 200
select * from empsal where salary=(
select min(salary) from empsal where salary in(
SELECT top(3) salary from EmpSal order by salary desc ))
very clear explanation.. thanks
very well explained .. thank you so much
Superrr explanation thank u
Excellent explanation
have been reading ur other book on software testing ,awesome book!!
What is the book name?
It,s so clear
Thanxs for good explainatory video....
Keep it up...
Thanks for your video. It was easy to follow.
I had a question in related to co-related query.
All the record in table has unique salary. What happens if there are two people who had same salary and we wanted the second highest salary? Is that number going to be 2 still??
I had that doubt too. Did you get the query to get the 2nd highest if the salary are duplicates?
Then the result will show two employees name instead of one because both the employees have second highest salary among all the salaries.
thanks for this buddy!
Nice and clear explanation
Thank you! I finally got it!
Thanks. It was nice and clear.
Great explanation, but I have a question. Why was the count function used in the subquery ?
Actually through innere query he is counting number of employees having greater salary than outer query employees id
In fist case count is 4
In second case count is 4
Like dt when ever count is 2 dt employee ID will be selected as final answer
thnx sir... this is very good explanation
thanks,nice and simple explanation.
select * from empsal where salary=(
select min(salary) from empsal where salary in(
SELECT top(3) salary from EmpSal order by salary desc ))
sir , not getting result when the same salary of 2 employees.
Will output of subquery always distinct
Fine but it will be helpful to us if you explain hiw to frame a co related query in detail instead of pasting code here
Great sir
Awesome Video..
very well explained..
great videos, by the way i did not know SQL server is a 'he' ... lol
Hi guys,
I have a question.
I want to figure out e2 from the query. How can I find that? I know if we want to change Table's name with 'as' after that it shows in new table where EmpSal changes to e2. But could we put it before 'from'? How COUNT(e2.Salary) work their?
It works like this row1 of outer query is compared with all the rows of inner query if condition is matched it takes the count of inner query.
Grt learning
good video, great examples
Can correlated queries be always rewritten as a JOIN query and return the same results ?
thank U.... thank U........... finally i got it.
Thank you for the explanation sir, just a doubt I had and that being can't we right a simple code in order to get the second highest salary? Like:
SELECT EmpName from EmpSal
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;
Pls. do correct me if I did something wrong :)
I do not know the answer to your question. However subqueries are used in order to make filtering a lot easier and often they are used in very complex situations. In this video we only have a simple example so it is expected that we can use many different methods to accomplish the same thing.
Nice Video
good explanation
love from nepal.
good job! thank you for share........
thank you
audio is very loud and good, how did u record it, which software u used here ?
While recording he just shouted like a girl watching horror movies ,just like that u can try
I guess we need to add count ( distinct e2.salary ) incase of duplicate salaries !
Yes
Remove = from inner query than only you will get the correct 2nd highest salary or nth highest salary
this correlated query is not working for for second highest salary if there is duplicate records
yes, if the highest salary is repeated twice, the co-related query solution will fail.
@@SamVsCode yes right. See this query which will find any higest number of query like 2nd, 3rd...10th highest
select * from empsal where salary=(
select min(salary) from empsal where salary in(
SELECT top(3) salary from EmpSal order by salary desc ))
nice video..:)
How many nested query we can use within a Sub - query ??
There is a limit do not know upfront. We have limit of 16 in oracle i think and in SQL Server also there should be a limit.
Last I saw, Oracle allows up to 255 levels of subqueries in the WHERE clause
thanks
Shiv raj. (Empsal table)
100. 150
I want out put like this how to write a query to this problem
in sub query can we use NOT IN Keyword ....
please Explain???
Thanks.
It mean a lot.....
yes you can , and when you use it all the salaries which are less than 150 will be the output.
Thanks ....means Exactily the reverse Output...???
have u purchased subcription ??
What if there are more than 1 user on the same 2nd highest salary i.e. 200?
Use ROW_NUMBER OR RANK window functions to achieve what you've asked.
Assume the table has an entry as : pulkit 150 5 respectively. Then the below RANK function will display both pulkit and raju.
select * from (select e. * , ROW_NUMBER() over (order by salary desc) row_num from empsal e) where row_num=3;
OR
select * from (select e. * , RANK() over (order by salary desc) rank from empsal e) where rank=3;
nice stuff
Great
great
Nice
when we use EXIT and NOT EXIT in sub query.
nice
actually 200 is the third highest value in this table but you said 2nd highest.
yes thats true and everyone here are appreciating..Explaination is Good But answer is Wrong..
his name murali gud lecturer for learn sql
wow
In almost every sentence there is a 'you know'. Very irritating.
. .
excellent explaination .
Very well explained