Q16-Q20

Q16. What is order of statement execution in SQL?

SELECT DISTINCT <TOP_specification> <select_list>

FROM <left_table>

<join_type> JOIN <right_table> ON <join_condition>

WHERE <where_condition>

GROUP BY <group_by_list>

HAVING <having_condition>

ORDER BY <order_by_list>

Q17. How you do exception handling in your SQL stored procedures?

Q18. How I can fetch the 10th highest salary in a table?

Q19. What are views. How they are different from Stored procedures. Pros and Cons of using view in SQL?

Q20. How to run Execution plan in sql server? What input you can take from it?

=======================================================================

Q16. What is the order of statement execution in SQL?

Answer:

FROM clause

JOIN..ON clause OUTER clause

WHERE clause

GROUP BY clause

HAVING clause

SELECT clause

DISTINCT clause

ORDER BY clause

TOP clause

=======================================================================

Q17. How you do exception handling in your SQL stored procedures?

Answer:

    We can have TRY-CATCH block in sql SP to handle errors. below is the syntax for placing a try catch block. 

We also make sure the Transaction is rollback in case of error. 

BEGIN TRY

    BEGIN TRANSACTION;

    

    -- Business logic here

    INSERT INTO Orders (...) VALUES (...);

    UPDATE Inventory SET ...;

    

    COMMIT TRANSACTION;

END TRY

BEGIN CATCH

    IF @@TRANCOUNT > 0

        ROLLBACK TRANSACTION;

    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();

    DECLARE @ErrorNumber INT = ERROR_NUMBER();

    RAISERROR('Error %d: %s', 16, 1, @ErrorNumber, @ErrorMessage);

END CATCH

=======================================================================

Q18. How I can fetch the 10th highest salary in a table?

Answer:

Two ways 

1) using CTE with ROW_NUMBER() and 

2) using sub queries. 

WITH CTE AS

(

    SELECT EmpID, EmpName, EmpSalary,

           RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)

    FROM dbo.Salary

)

SELECT EmpID, EmpName, EmpSalary

FROM CTE

WHERE RN = @NthRow

=======================================================================

Q19. Pros and Cons of using view in SQL?

Answer: 

Views are also called virtual tables in SQL. S

Stored procedure usually have better performance because they are pre compiled code for sql server and it will simply return data on execution. but With views you still have to write your sql query to return data as view act as a table in front. 

Advantages:

1. Security. We make user to see specific columns of tables using View. 
2. Simplicity. Complex query around multiple table could be convert into sinlge query to a single view. 
3. Data Integrity. If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.

Disadvantages of views

1. Performance - Views create the appearance of a table, but the DBMS must still translate queries against the view into queries against the underlying source tables. If the view is defined by a complex, multi-table query then simple queries on the views may take considerable time.
2. Update restrictions - When a user tries to update rows of a view, the DBMS must translate the request into an update on rows of the underlying source tables. This is possible for simple views, but more complex views are often restricted to read-only.

=======================================================================

Q20. How to run Execution plan in sql server? What input you can take from it?

Answer:

Execution plan can be run with the button near "Execute" in SSMS. 

You can check the order of execution of queries from right to left.  We have cost in execution plan.