Scenario Based SQL Interview Questions

Scenario Based SQL Interview Questions : Today we are taking about most ask scenario base question in SQL Interview Question.If you are crack this question you have definitely hired.Scenario Based SQL Interview Questions is now days very popular for any interviewer. Most of them rely on scenario base SQL Interview Question. So lets Start.

Scenario : Interviewer may give you a table and a query and ask you for on which column should I create a Clustered Index and Why?

Let’s say there is a Employee table with Id Column as a Primary Key

We have below query in StoreProcedure which is very slow :

SELECT Id, Name, Salary, Gender from Employee Where Salary BETWEEN 2500 AND 50000

Ans. Interviewer may give you above base question & ask on which column should I create a Clustered Index and Why?

Simple Langauge : As we know about clustered Index column is faster than normal column. Clustered Index is arrange the value in ascending order. hence if we create Clustered Index on Salary column then it will sorted in ascending order which will help to RUN the Query faster between that range.

This query has more focus on Salary column so we should have something over Salary column which will help this query to execute faster.

we have to go with creating Clustered Index on Salary by removing Clustered Index from Primary Key Id Column. Which will definitely helps this query to run faster.

Technical Langauge : When you create a clustered index on a column, the data in the table is physically sorted based on the values in that column. This can help speed up queries that involve range scans or searches within that column because the data is organized in a way that makes it easier for the database engine to locate the desired records.

So, if you create a clustered index on the “Salary” column, and your query involves searching for records within a specific salary range, the database engine can quickly locate those records because they are stored in sorted order based on salary. This can result in faster query performance compared to when there is no clustered index or when the clustered index is on a different column.

What are Views? Indexed View? Advantages of Views?

A view is nothing more than a saved SQL query. A view can also be considered as a virtual table.
A standard or Non-indexed view is just a stored SQL query. When, we try to retrieve data from the view, the data is actually retrieved from the underlying base tables. So, a view is just a virtual table it does not store any data, by default.
However, when we create an index, on a view, the view gets materialized. This means, the view is now, capable of storing data. In SQL server, we call them Indexed views.

Advantages of using views

. Views can be used to reduce the complexity of the database schema, for non IT users. For example the view can hides the complexity of joins. Non-IT users, finds it easy to query the view, rather than writing complex joins.
Views can be used as a mechanism to implement row and column level security.

Row Level Security:

For example, I want an end user, to have access only to IT Department employees. If I grant him access to the underlying tblEmployees and tblDepartments tables, he will be able to see, every department employees. To achieve this, I can create a view, which returns only IT Department employees, and grant the user access to the view and not to the underlying table.

Column Level Security:

Salary is confidential information and I want to prevent access to that column. To achieve this, we can create a view, which excludes the Salary column, and then grant the end user access to these views, rather than the base tables.

Views can be used to present only aggregated data and hide detailed data.

Can we update underlying base tables through View? [Tricky question] Depending on your answer he can ask you Single/Multiple base tables?

Yes. We can update the base tables through a view if there is single underlying base table.
For a view based on multiple base tables we can use instead of trigger to correctly update the base table values.

What are Triggers? Different types of Triggers?

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.
There are different types of triggers :

1. DML Triggers which are again divided into Instead Of Triggers and After Triggers

2. DDL Triggers

3. Logon Triggers.

DML Triggers : DML stands for Data Manipulation Language. INSERT, UPDATE, and DELETE statements are DML statements. DML triggers are fired, whenever data is modified using INSERT, UPDATE, and DELETE events.

DML triggers can be again classified into 2 types.
1. After triggers (Sometimes called as FOR triggers)
2. Instead of triggers

After triggers, as the name says, fires after the triggering action. The INSERT, UPDATE, and DELETE statements, causes an after trigger to fire after the respective statements complete execution.

On other hand, as the name says, INSTEAD of triggers, fires instead of the triggering action. The INSERT, UPDATE, and DELETE statements, can cause an INSTEAD OF trigger to fire INSTEAD OF the respective statement execution.

DDl Triggers :

DDL triggers fire in response to DDL events – CREATE, ALTER, and DROP (Table, Function, Index, Stored Procedure etc…).

DDL Triggers again classified into 2 categories :
Database Triggers : DDL Triggers specific to database.
Server Scoped Trigger : When you create a server scoped DDL trigger, it will fire in response to the DDL events happening in all of the databases on that server.

Use of DDL triggers :

If you want to execute some code in response to a specific DDL event
To prevent certain changes to your database schema
Audit the changes that the users are making to the database structure

Logon Triggers :

As the name implies Logon triggers fire in response to a LOGON event. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established.

Logon triggers can be used for

1. Tracking login activity

2. Restricting logins to SQL Server

3.Limiting the number of session for a specific user

What are different magical/special tables available in Triggers?

There are 2 magical tables available while working with triggers:

1. INSERTED Table

2. DELETED Table

Trigger	INSERTED or DELETED?
Instead of Insert	DELETED table is always empty and the INSERTED table contains the newly inserted data.
Instead of Delete	INSERTED table is always empty and the DELETED table contains the rows deleted
Instead of Update	DELETED table contains OLD data (before update), and inserted table contains NEW data(Updated data)

Subcribe My Channel –