.Net Interview Questions and Answers

Search:

SQL server interview question: -What are Sub-Queries, co-related queries and difference between them?

By : Shiv Prasad Koirala | Jul 2nd, 2012 | Views : 12089 | Comments : 0 Bookmark and Share
Note :- This is a favorite interview question which keeps coming up , so I have put a detailed explanation of the same.

 

Sub query a.k.anested queries is a Query inside Query. Many times you would like to have series of SQL chained where output of one query is sent as input to the other query for filtering and manipulation.

 

sql

Courtesy: -http://www.yankodesign.com

 

For example you can see in the figure below we have two tables one table has the employee salary and the second table has phone numbers. Let's say we want to find phone numbers of employees whose salaries are greater than 150.

 

sql

 

We can achieve this by using subquery. One query ( i.e. inner query ) will get all employee whose salaries are greater than 150. This output will be fed to another query who will pull out phone numbers accordingly.

 

In simple words we will have two queries one inner query and the other an outer query. The output of inner query is fed to the input of outer query and then the final output is displayed.

 

sql

sql

 

The Sub query will look something as shown below. First we fetch from the EmpSal table and get salary values which are more than 150. The results of this query is sent to outer query and finally the output is displayed.

 

sql

 

Now let's say if we want to find employees who have the second highest salary from the salary table , so how will you go about it.

 

sql

 

This can be achieved by using co-related queries. The query would look something as shown below. In this case we need to evaluate each record to see the position of each one of them and then fetch second highest accordingly.

 

In other words first the outer query will send record to the inner query, who will then evaluate the same until the conditions are met.

 

In case you are still confused you can go through following youtube video where I put a detailed explanation about the same.

 

sql

 

Putting in simple words in co-related queries data moves back and forth for each record while in subquery the data moves unidirectional from the inner query to the outer query.

 

sql

 

So below is a simple summary of what you can speak in the interview.

 

Subquery

Co-related

The inner query is independent / self-contained and gets evaluated first and then passes the results to the outer query. The inner query needs the values from the outer query and passes results to the outer query.

sql

 

See for more stuffs on SQL Server Interview question

 

Regards,

 

Click to view more from author's on SQL Server Interview question

 
Rate this Article :
1 Star
2 Stars
3 Stars
4 Stars
5 Stars
1 1 1 2 1
 
 
 
 

.NET interview questions 6th edition (Sixth edition) - By Shivprasad Koirala

In this article we will explain .NET interview questions 6th edition (Sixth edition) - By Shivprasad Koirala.... Read More

WCF Interview questions and answers: -What is the difference between WCF fault exceptions and normal .NET exceptions?

In this article we will show difference between WCF fault exceptions and normal .NET exceptions. For more articles and videos visit us on http://www.questpond.com/... Read More

WCF interview questions and answers: - What is the difference between Service endpoint and Client endpoint?

In this article we will explain What is the difference between Service endpoint and Client endpoint.... Read More

C# and .NET interview question: - Define Named and Optional Arguments?

Visual C# 2010 introduces two interesting features called as Named and Optional Arguments.... Read More

MVC vs ASP.NET ( MVC Interview question with answers )?

In this article we will explain MVC vs ASP.NET.For more articles and videos visit us on www.questpond.com... Read More

.NET interview questions 6th edition (Sixth edition) - By Shivprasad Koirala

In this article we will explain .NET interview questions 6th edition (Sixth edition) - By Shivprasad Koirala.... Read More

WCF Interview questions and answers: -What is the difference between WCF fault exceptions and normal .NET exceptions?

In this article we will show difference between WCF fault exceptions and normal .NET exceptions. For more articles and videos visit us on http://www.questpond.com/... Read More

WCF interview questions and answers: - What is the difference between Service endpoint and Client endpoint?

In this article we will explain What is the difference between Service endpoint and Client endpoint.... Read More

C# and .NET interview question: - Define Named and Optional Arguments?

Visual C# 2010 introduces two interesting features called as Named and Optional Arguments.... Read More

MVC vs ASP.NET ( MVC Interview question with answers )?

In this article we will explain MVC vs ASP.NET.For more articles and videos visit us on www.questpond.com... Read More

Article Categories

Learn in Hindi