/    /  SQL- Interview questions Part 5

1. What are the types of subquery?

Answer: There are two types of subquery – Correlated and Non-Correlated.

1. Correlated sub query:

This cannot be considered as independent query, but it can refer the column in a table listed in the FROM the list of the main query.

2. Non-Correlated sub query:

This Sub query can be considered as independent query and the output of subquery are substituted in the main query.


2. What is a stored procedure?

Answer: Stored Procedure is a function consists of many SQL statement to access the database system. Various SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.


3. What is a trigger?

Answer: A DB trigger is a code or programs that automatically execute with response to some event on a table or view in a database. Especially, trigger helps to maintain the integrity of the database.


4. Explain the difference between DELETE and TRUNCATE commands?

Answer: DELETE command is used to remove rows from the table, and WHERE clause can be used for conditional set of parameters. A delete statement can be performed after Commit and Rollback .TRUNCATE removes all rows from the table. Truncate operation cannot be rolled back.


5. Explain, What are local and global variables and their differences?

Answer: They are as follows;

1. Local variables:

They are the variables which can be used or exist inside the function. They are not known to the other functions and those variables cannot be referred or used. Local Variables can be created whenever that function is called.

2. Global variables:

They are the variables which can be used or exist throughout the program. A variable which is same, and declared in global cannot be used in functions. They cannot be created whenever that function is called.