What are Subquery and Co-related Queries in SQL Server ? | SQL Server Interview Questions & Answers

Buy SQL Server course from here www.questpond....
For more such videos visit www.questpond.com
For more such videos subscribe www.youtube.co...
In this video we will try to understand what are SQL server sub queries , co-related queries and how they differ from each other.
See our other Step by Step video series below :-
For more such videos subscribe / questpondvideos
See our other Step by Step video series below :-
JavaScript Interview Questions: • JavaScript Interview Q...
Learn SQL Server Step by Step tinyurl.com/ja4...
Learn MSBI Step by Step in 32 hours:- goo.gl/TTpFZN
Python Tutorial for Beginners:- • Python Tutorial for Be...
Learn Data Science in 1 hour :- tinyurl.com/y5...
Learn Power BI Step by Step:- tinyurl.com/y6...
PHP Interview Questions : • PHP Interview Question...
C# tutorial for beginners(4 hrs):- • C# Tutorial for Beginn...
Learn Azure Step by Step:- • Azure Tutorial for Beg...
Azure AZ-900 fundamentals certification :- • AZ 900 Certification |...
Angular Interview Questions and Answers :- • Angular Interview Ques...
Learn Angular tutorial step by step tinyurl.com/yc...
Learn MVC 5 step by step in 16 hours:- • Learn ASP.NET MVC 5 ( ...
Learn Design Pattern Step by Step goo.gl/eJdn0m
Learn Tableau step by step :- tinyurl.com/kh...
Preparing for C# / .NET interviews start here • C# Interview Questions...
We are also distributing a 100 page Ebook "Sql Server Interview Question and Answers". If you want this ebook please share this video in your facebook/twitter/linkedin account and email us on [e-mail] with the shared link and we will email you the PDF.
-----------------------------------------------------------------------------------------------
For more details :-
🌐 Website : www.questpond.com
📱 Mob. No. : [phone] / [phone] / [phone]
📧Email : [e-mail] / [e-mail] / [e-mail]
Share, Support, Subscribe and Connect us on!!!
KZbin: / questpondvideos
Twitter: / questpond
Instagram : / questpond
Facebook : / questpond
#SQLServer #SQLServerInterviewQuestions #SubqueryinSQL #Subquery&Co-relatedQueries #SQLServerTutorial #SQLServerInterviewQuestions&Answers

[@questpondvideos]

20+ SQL Server Interview Questions : kzbin.info/www/bejne/iXbHcnluorh-iZY 20 MSBI Interview Questions : kzbin.info/www/bejne/hKjCpHt7gNOCi6c 10+ Power BI Interview Questions : kzbin.info/www/bejne/eaDdlGyNg6eFqpY SQL Server Joins : kzbin.info/www/bejne/gYXZint7o9mppJo SQL Step by Step - kzbin.info/www/bejne/q3jPl4NvpJyeo7s Software Architecture Interview Questions : kzbin.info/www/bejne/d6W3mJaYgbSkg68 Angular Step by Step Tutorial for Beginners : kzbin.info/www/bejne/Y2q5lIptgqd3qZo 25 Angular Interview Questions : kzbin.info/www/bejne/Y5vIoKx6f6mmia8 35 Important JavaScript Interview Questions : kzbin.info/www/bejne/kJOXlYOfbKh0hLc 30 Important C# Interview Questions : kzbin.info/www/bejne/eHzcn3h4hdacf68 25+ OOPS Interview Questions : kzbin.info/www/bejne/q2qcqHSld893rLM 25 Important ASP.NET Interview Questions : kzbin.info/www/bejne/ponQfpejf7p2Zsk 20 PHP Interview Questions : kzbin.info/www/bejne/Z5PTf4avo7qlopo 5 MSBI Interview Questions : kzbin.info/www/bejne/a3abYmiXjaaqj7M 20 MySQL Interview Questions : kzbin.info/www/bejne/b5nJm3Zjd9WMaJI

[@davidjayson63]

Instablaster.

[@questpondvideos]

👊👊👊👊👊👊Smash Subscribe button if you like it.👊👊👊👊👊👊

[@kirankumar-iz3tw]

i already smashed.....i cant do one more smash....it will get un subscribe

[@mallikarjunpidaparthi]

@@kirankumar-iz3tw LOL!

[@basicsofdataengineering]

Searched from different channels, but at last , you clarified what subquery actually is. Thank You Sir who ever you are❤️

[@jzhuo]

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

[@dipranjan1987]

This is the best explanation I have ever seen.Thank you so much for such elaborated explanation

[@shobhitsrivastava4496]

Your explanations are very clear sir, keep up your good work, may god bless you!!

[@kitchenking9231]

Your all videos of both channel are just excellent..I wish if all may be free to access

[@chitrasrinivas7089]

Excellent explanation sir.....it's really helped me

[@mahendratawade606]

Thanks...you cleared my this concept 👍👍👍

[@pa32779]

Thank you so much bro, helped me a lot than my professor or books! Keep it up!

[@searez7251]

Clear, precise and concise, thank you for your time.

[@Subhash_VCU]

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

[@maheshwara.r.7600]

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)

[@abdulsacur2615]

best explanation about Correlated Subqueries

[@itwithfun3843]

have u purchased subcription ??

[@arunprashanth5817]

Great and simple explanation with examples. came here after watching a couple of videos that failed to explain this concept so neatly.

[@RameshK-rn3hf]

Decent explanation without confusing.. Excellent

[@cassondrad2280]

OUTSTANDING. You're explanation/demonstration. Well done. I get it. What a big help, thank you, thank you. I finally understand.

[@mastas1931]

Yr explaintion is awesome, pls make more

[@priyasrinivasan1644]

Thanks got clear cut idea about corelated query.

[@siddharthasharma9316]

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.

[@ItsWithinYou]

Nicely explained! Thank you for sharing!

[@pattaweechutimasakul7219]

Great Explaination

[@hridya85]

Very good explanation..really helpful

[@VinodKumar-nn7go]

great videos, by the way i did not know SQL server is a 'he' ... lol

[@subhashkomy]

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

[@kapilsrivastava8007]

Excellent 👌

[@joshc6763]

Good accent. Fast and furious nice and clean

[@itwithfun3843]

have u purchased subcription ??

[@knowledgeworld3666]

select * from empsal where salary=( select min(salary) from empsal where salary in( SELECT top(3) salary from EmpSal order by salary desc ))

[@shellprince790]

Thank you so mch... Finally i understood subqueries and co-related queries...

[@RamcharitmanasHindi]

VERY NICE LECTURE .. SUPER EXPLATION .. HELPED ME A LOT

[@maddusuvarnalatha]

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

[@unpsir1311]

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.

[@AbhishekKumarMishra_abhi101009]

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))

[@yennamnava7580]

Your Videos are seriously worthful...... thanku...

[@pratikkulkarni4929]

Superrr explanation thank u

[@haticetercuman4546]

Excellent explanation

[@swetasworld7045]

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??

[@MrJaga121]

I had that doubt too. Did you get the query to get the 2nd highest if the salary are duplicates?

[@Dark-wc4pu]

Then the result will show two employees name instead of one because both the employees have second highest salary among all the salaries.

[@frankwills414]

Great explanation, but I have a question. Why was the count function used in the subquery ?

[@maddusuvarnalatha]

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

[@SatishKumar-ji4us]

Nice and clear explanation

[@279Palu]

very clear explanation.. thanks

[@noelbarcelona210]

thanks for this buddy!

[@naveenakanuganti8068]

have been reading ur other book on software testing ,awesome book!!

[@shanugupta5332]

What is the book name?

[@lakshmiy4103]

Great sir

[@drs10301]

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.

[@jhjhj2172]

very well explained .. thank you so much

[@knowledgeworld3666]

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

[@knowledgeworld3666]

select * from empsal where salary=( select min(salary) from empsal where salary in( SELECT top(3) salary from EmpSal order by salary desc ))

[@utkarshkunwar4319]

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 :)

[@d.ndriotis9209]

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.

[@umeshkanaujia6700]

Thanks. It was nice and clear.

[@bhuwanpandey4342]

Remove = from inner query than only you will get the correct 2nd highest salary or nth highest salary

[@pranjalsharma499]

Nice Video

[@shaikhshahfaisalshaikh6517]

thnx sir... this is very good explanation

[@705chacko]

thanks,nice and simple explanation.

[@johnedwards6253]

Thank you! I finally got it!

[@kishorpatel3369]

Thanxs for good explainatory video.... Keep it up...

[@rahulhameed2215]

audio is very loud and good, how did u record it, which software u used here ?

[@Debjit625]

While recording he just shouted like a girl watching horror movies ,just like that u can try

[@madanrajpoot]

Awesome Video..

[@TechandArt]

Grt learning

[@mihaelaneagu7844]

It,s so clear

[@indrasenareddy2193]

very well explained..

[@freelancher5331]

love from nepal.

[@noufal_mundodan]

thank U.... thank U........... finally i got it.

[@naturetalk7806]

sir , not getting result when the same salary of 2 employees.

[@gokukanishka]

Will output of subquery always distinct

[@madhavikatta7346]

thank you

[@MrMMohsen]

Can correlated queries be always rewritten as a JOIN query and return the same results ?

[@sss2005sunil1]

good video, great examples

[@mergasisaarthiban87]

I guess we need to add count ( distinct e2.salary ) incase of duplicate salaries !

[@neerajsharma4085]

Yes

[@amsavallit]

good explanation

[@TheGiselamother]

good job! thank you for share........

[@nasimbeksaidmurodov3419]

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?

[@neerajsharma4085]

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.

[@bmkkar8568]

How many nested query we can use within a Sub - query ??

[@questpondvideos]

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.

[@OneDayCloserToHim]

Last I saw, Oracle allows up to 255 levels of subqueries in the WHERE clause

[@zeelanmca2011]

nice video..:)

[@kirankumar-iz3tw]

thanks

[@kiransurvase96]

in sub query can we use NOT IN Keyword .... please Explain??? Thanks. It mean a lot.....

[@saifahmedyassen3725]

yes you can , and when you use it all the salaries which are less than 150 will be the output.

[@kiransurvase96]

Thanks ....means Exactily the reverse Output...???

[@itwithfun3843]

have u purchased subcription ??

[@TheSastrastudent]

actually 200 is the third highest value in this table but you said 2nd highest.

[@aishwaryaramesh9343]

yes thats true and everyone here are appreciating..Explaination is Good But answer is Wrong..

[@sonu981]

nice stuff

[@Ganeshw0]

this correlated query is not working for for second highest salary if there is duplicate records

[@SamVsCode]

yes, if the highest salary is repeated twice, the co-related query solution will fail.

[@knowledgeworld3666]

@@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 ))

[@bilatenawtube2520]

great

[@raghunadhav8287]

nice

[@mohammadroufa8591]

Shiv raj. (Empsal table) 100. 150 I want out put like this how to write a query to this problem

[@smastan3975]

Nice

[@bmkkar8568]

when we use EXIT and NOT EXIT in sub query.

[@pulkitindia]

What if there are more than 1 user on the same 2nd highest salary i.e. 200?

[@manjit534]

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;

[@Debjit625]

Great

[@vasssrini]

his name murali gud lecturer for learn sql

[@wiepcorbier]

In almost every sentence there is a 'you know'. Very irritating.

[@mrneb8995]

wow

[@HarpalSingh-wi6fe]

. .

[@madansanagapalli1638]

excellent explaination .

[@kapilsrivastava8007]

Very well explained