SQL Server Interview Questions

Here is the list of SQL server interview questions gathered from my end. You will be finding questions from various companies that I have attended so far. Let’s get started.

TECTORO

1. Apply all joins for the following table and write the number of rows?

Answer:
Inner join -4 rows
Cross joins-4 rows
Left join-4 rows
Right join-4 rows

 

 


2. Explain view concepts with examples?

Answer:

A View is a DB object used to select query.View can be also considered as a virtual table. It takes less space to store.

Syntax:

create view Vw_for_computers

as

select * from students where std_course =’computers’

Execution:

select * from Vw_for_computers            ( In o/p we can see only computers department data)


Advantages of view

A View can be used to reduce the complexity of database schema.

Ex: When non-IT users want to access the database. It is not simple for them to combine 4-5 tables and get the data. So, we write a view which joins all the tables and give view access to non-IT users.

 A view can be used as the mechanism to implement row and column level security.

Ex: If we want IT department manager who only want IT employee list. If I grant access to the base table, he can see other department details also. So, create a view , within the view retrieve only IT employee. Then give access to view.

— A View can be used to present aggregated data and hide detailed data.

Ex1: Create view Vw_employee

As

Select ID, Name, Dept from tblemployee

Join tbldepartment

On tblemployee.departmentID  = tbldepartment.DeptId

Where tbldepartment.deptname = ‘IT’

Ex2: Create view VwSummarized data

As

Select DeptName, count(ID) as TotalEmployees

From tblEmployee

Join tblDepatment

On tblEmployee.DeptID = tblDepatment.DeptID

Groupby Deptname

O/P :

DeptName TotalEmployees
Admin 1
HR 2
IT 2
Payroll 1

3. Write the difference between Functions and Stored procedure?

S.No                      Functions                      Stored Procedure
1  It must return a value  It is optional.It can return zero or any value.
2  It has only I/P parameters  It has both I/P and O/P parameters
3  Functions allow only select statement.  It allows select as well as DML statements
4  Functions can be used Where/Having/Select section  Procedures cant be used.
5  Exception cant be handled  Exception can be handled by using try-catch block
6  Transactions are not allowed within functions  Transactions are used in stored procedure.
7 Only table variables are used in functions. It will not allow temporary var.  Sp’s uses both table and temporary var.
8  Function cannot call SP  SP can call function
9  Functions can be used in join clause as result set  Procedures can’t be used in join clause.