Sql Server Interview Questions and Answers (1758) - Page 53

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

How to replace string in sql-server?

With the help of Replace in-built sql-server function,we can replace any text within sql-server.It takes 3 parameters as

Syntax:

Replace('string text','searched string','replaced string');

String Text:- It can be any string.
Searched String:- The text to be searched from within 1st parameter i.e. string text.
Replaced String:- This is the new value or text which will replace 2nd parameter value(searched string) from 1st parameter value(string text).

What is the output of below code? declare @str navrchar(50); set @str = 'Rajesh Kumar'; Select Replace(@str,'Kumar','Sathua') as 'New Value';

Output would be Rajesh Sathua.As we know that Replace function replace the text from given string.Here 1st expression is rajesh kumar,i want to replace kumar with sathua,so i will pass kumar in 2nd parameter and new value will be passed in 3rd parameters.

How to assign value to variables in sql server?

We use SET keyword to assign values to variables in Sql Server.

For Example:-

Declare @name nvarchar(50);

Set @name = 'Rajesh';

Print @name;

How to declare any variable in sql server?

We use Declare keyword to declare any variables in Sql Server.

Syntax:-

Declare @variable_name data-type;

For Example:-

Declare @project_name nvarchar(50);

Declare @requisition_id int;

How to see output of any value in sql server?

We use Print keyword to print output of any value.

declare @project_name  varchar(50);

set @project_name = 'Job Site';

Print @project_name;

Output:Job Site

What is the use of Len function in Sql Server?

The Len() function is an in-built Sql Server function which is used to checking the length of the value.

Syntax:-

Select Len(column_name) from table_name;

Give an example of Len function.

Declare @name nvarchar(50);

Set @name = 'RajeshKumarSathua';

Select Len(@name) as 'Name_length';

Output would be:17

What is the use of Avg function in Sql-Server?

AVG() function is an in-built Sql-Server function which is used to calculate the average of a column values.
Note:Column must be of Integer type.

Syntax:

Select Avg(column_name) From table_name;

For Example:

Select Avg(Budget) from Project_Master;

What will happen when we execute below query? select avg('123') select avg('rajesh')

It will give us below error which says as
Msg 8117, Level 16, State 1, Line 1
Operand data type varchar is invalid for avg operator.

As we know that Avg as name suggests will only apply on integer column,if we supply other than numeric value then it will throw above error.

How to calculate column values in Sql-Server?

With the help of Sum function of Sql-Server,we can calculate column values.

Syntax:

Select Sum(column_name) from tbl_name;

For Example:

Select Sum(competency_values) from competency_details;

Suppose,i have one table named competency_details which has one column named competency_value and column contains 200,345,455 and 100. What will be the output of below query? Select Sum(competency_value) as 'Total Competency' from competency_details;

NOTE: This is objective type question, Please click question title for correct answer.

Suppose,i have one table named competency_details which has one column named competency_value and column contains 200,345,455 and 100 What will be the output of below query? Select Avg(competency_value) as 'Total Competency' from competency_details;

NOTE: This is objective type question, Please click question title for correct answer.

What do we mean by @@Fetch_Status in Cursor?

@@Fetch_Status function is an in-built Sql-Server function,used when dealing with Cursor object and it checks whether any row is successfully returned from cursor or not.
The value of @@Fetch_Status is undefined before any fetches have occurred on the connection.
It returns 0 if it retrieves rows successfully.

What is the return type of @@Fetch_Status Function?

The return type of @@Fetch_Status is always integer.
In Cursor,we check for @@Fetch_Status as:

While @@Fetch_Status = 0

begin

 Fetch Next From cursor_name;

end

What is the result of this query, SELECT * FROM Employee E, Department D WHERE E.DEPTNO=D.DEPTNO

In this query, there are two tables Employee and Department and E is the alias name for Employee table and D is the alias name of D. The Employee table contains Employee details and the Department table contains Departments and Employee Id's.
When you need to know in which department is the employee present then you can use this query.

What is the output of the query, SELECT * FROM Employee_ksm E INNER JOIN Department D ON E.DepartmentId=D.DepartmentId

This query is used when you need to join two tables and get the ouput based on where clause. Suppose you have two tables one for maintaining your Employee details and the other for Departments present in your company.
When you want to know in departments the Employee is present then you can use this query.

What is the output of the following query? SELECT * FROM Employee_ksm MINUS SELECT * FROM Department

This query gives the records that are present in Employee_ksm table and not in Department.

Result of the following query, SELECT * FROM Employee_ksm WHERE Employee_ksm.DepartmentId IN(SELECT DepartmentId FROM Department)

When we want to get the records from both the tables where the DepartmentId in Employee_ksm matches the DepartmentId in Department table.

What is the output of the following query? SELECT * from table1 INTERSECT SELECT * from table2

NOTE: This is objective type question, Please click question title for correct answer.

What are the values that @@Fetch_Status Function return while dealing with Cursor object n Sql-Server?

@@Fetch_Status returns 0,-1 and -2 values.
Where
0 - denotes that Fetch is successfully done meaning row is found.
-1 - denotes that Fetch statement failed or the row was beyond the result set.
-2 - denotes that Row which was fetched are not found or missing.