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. |