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

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 calculate the total in sql?

The total or sum of a particular column can be calculated SUM functions as below,

SELECT SUM(ColumnName) AS Salary FROM tableName

Write a query to select the department that contains more than 5 five employee, get the department id and also the number of employees whose salary is more than 40,000?

SELECT DepartmentId, Count(*)
FROM Employee E, Department D
WHERE E.DepartmentId = D.DepartmentId
AND E.Salary > 40000
AND (SELECT DepartmentId FROM Employee
GROUP BY DepartmentId
HAVING COUNT(*) 5)

What are Cursors in Sql-Server?

Cursors are known for fetching row one by one fom a table.Actually they work just like as For-loop or while-Loop. Cursor always fetch one record at a time.
A cursor allows us to iterate a set of rows returned by a query and process each row accordingly.
Actually we should not use cursors because they degrade performance of database and also uses lots of server resources.ecause if there are lakhs of rows and cursor is manipulating each row one by one then definitely it will slow the performance of database.
We always write Cursors inside stored procedure.

What is the output of the query, SELECT * from Table1 CROSS JOIN table2

Suppose if we have two tables in our database - table1 and table2.
then the CROSS JOIN of the two tables gives the cartesian product of the two tables.

Suppose if we have Employee table containing Employee information and Department table containing Departments.
Then when we implement CROSS JOIN, it will map all the Departments to each Employee and displays the product as below,

Employee1 Dept1
Employee1 Dept2
Employee1 Dept3
Employee2 Dept1
Employee2 Dept2
Employee2 Dept3
.
.
.
.

What considerations must be there when working with Cursors?

We have to follow below 5 steps to accomplish Cursors:-

1). Declare cursor.
2). Open cursor.
3). Fetch rows from cursor.
4). Close cursor.
5). Deallocate cursor.

When the cursor is open then it must be closed and then deallocated.

What are the types of Cursors?

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

How many joins are there in Sql-Server?

There are following joins in Sql Server:

1). Inner joins: It returns all rows when there is at least one match in both tables
2). Non-Equi joins: It uses comparison operator instead of the equal sign like >, <, >=, <= along with conditions.
3). Outer joins
a). Left outer joins: It return all rows from the left table,and the matched rows from the right table
b). Right outer joins: It return all rows from the right table,and the matched rows from the left table
c). Full outer joins: It displays all the matching and non matching rows of both the tables.
4). Self joins: Joining the table itself called self join.It happens in the same table.
5). Cross joins: A cross join that produces Cartesian product of the tables.

What is the use of Month in-built Sql-Server function?

Month extract month part from any specified date.
Syntax:

Select Month('date_expression')

What will be output of below query? Select Month('01/23/2014') as 'Month';

Output will be 1,because Month in-built sql server function will extract only month part from any date.Here month is 01 i.e. Jan,so it will return 1.

What is the return type of Month in-built sql server function?

Month in-built Sql-Server function always returns as integer value as month,date and year part in numeric value.

What will happen,if we run below query in query analizer? Select Month('Rajesh') as 'Month';

It will throw below error as:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Because as we know that,we can pass only Date values in Month function.But here we passed Text,so it will give us above error.
So always pass Date value to avoid exception.

How to modify table column datatype?

With the help of alter column,we can modify datatype of any column.

alter table project_master alter column budget decimal(18,2);

How to drop any column from table?

With the help of drop column,we can remove any column from Table

alter table project_master drop column project_tracking_id;

What is the use of sp_rename in Sql Server?

sp_rename is a system stored procedure which is basically used to rename any existing Stored Procedure.
We provide existing sp name and new sp name in sp_rename.

Syntax:

exec sp_rename 'proc1','proc2';

Where: proc1 is our existing procedure name which will be renamed.
and proc2 is our new procedure name.

How to rename sp_get_users_list to sp_get_all_list_of_users.

We can use sp_rename to do this:

exec sp_rename 'sp_get_users_list', 'sp_get_all_list_of_users';

What do we mean by Substring function in Sql Server?

Substring function is a Sql Server in-built function which is used to extract a portion of text or character from a given string.We can get any characters from withing any string value.

Syntax:

Substring(string_expression,string_start_position,string_length);

Where:
string_expression: It would be any string value from which we will extract portion.

string_start_position: It is an integer value that specifies from which position,the substring begin.The first position is the 1.

string_length: It specified the count of characters.

Give an example of SubString function?

Suppose i have one string value .i.e. 'Vishal Kumar';
And i want to get only Vishal as output,so i will write as

Select Substring('Vishal Kumar',1,6) as Portion;

Output would be:Vishal

What will be the output of below Substring function? Select Substring('Vishal Kumar',7,6) as Portion;

Output would be: Kumar

Here,we have specified start position as 7 and length is 6,so substring function will start counting from 7 upto 6.So it will return Kumar.

What will happen,when we execute below query? Select Substring('Vishal Kumar','Neeraj',10) as Portion;

It will give us below error:

Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of substring function.

Meaning that,we can not pass varchar value in 2nd and 3rd parameter because substring function takes integer value in 2nd and 3rd parameter that is start_position and string_length.
so always give numeric value in start_position and string_length parameter.

How to rename a Table Column in Sql Server?

With the help of sp_rename system function,we can rename table,column,stored procedure.

Syntax:-
EXEC sp_rename 'Table1.column_name1', 'column_name2','COLUMN';