SQL Interview Questions

SQL Interview Questions

Local and Global Cursor :
While declaring the cursors, we can specify a cursor as Local or Global cursor.

Local Cursor will exists for the particular object and it cannot be reference by an external object. By default, cursors are Local. If we need to make it as global and can be accessed by all the objects. We need to specify it as global.
If the cursor is declared as Global, it can be accessed by all the objects in the existing connection.

Subquery:Subquery is an important concept in T-SQL. The outer query will provide the result based on the result given by the subquery.

Select id from table1 where id = (select max(id) from table1) -Red color is the subquery.

The outer table's output "id" depends on the subquery "id" value.
Here equal to (=) is used so that the subquery will retrieve only one id. If the subquery gives more than one value we have to use "IN" operator.
Select id from table1 where id in (select id from table1)
generally, the outer query is waiting for the subquery to finish its execution. So, it will hit the performance in a huge application with complex joins. So, we should avoid subqueries.

Correlated subquery:
These queries are little bit interesting one. The inner query depends on the outer queries input and there will be a mutal co-ordination occurs between these inner and outer query.
select name from venkat1 v
where id in (select id from venkat1 v1 where v.id=v1.id)
In the above query, the inner query depends on the outer queries output.