TCS 10 Tricky SQL Server Interview Questions/Query Asked during TCS Interview

TCS SQL SERVER INTERVIEW QUESTIONS ANSWERS 2016 SET

Today I want to share my interview experince with you, TCS was looking for 4+ years SQL Server Experienced Developers, So I attend this interview(05/05/2016) to know the latest SQL Server question asked in marked currently.
Below are the set of query which are asked during my TCS interview question answers session(tech round), Which was held in Gurgaon TCS office. So be updated with new set of query, last query is most interesting. Some of them asked during HCL interview as well I had attend HCL interview as well. Most of them are very tricky queries. Must share with your friends.

1). How to select random record form a table?
Ans: Select top 1 * from <TableName> order by newId()

2). Suppose that you have table Employee with a column EName which contain Records Employee name(EName) as A,B,A,A,B,D,C,M,A, Write a query which will change/Swap the EName A to B and B to A.
Ans: UPDATE Employee

set EName = (CASE

WHEN EName='A' THEN 'B'

WHEN EName='B' THEN 'A'

ELSE EName

END)

3). Write a query to create a clone of existing table without using Create Command.
Ans: SELECT * INTO <NewTable> FROM <ExistingTable> WHERE 1=2

SELECT TOP 0 * INTO <NewTable> FROM <ExistingTable>

4). Table Tbl1 has 100 rows, Table Tbl2 has 0 rows so number of rows returned by the below query?
SELECT Tbl1.* from Tbl1, Tbl2;
Ans : No row will be retun by this query

5). Write a query to print 1 to 100 in sql server without using loops?
Ans: Use Recursive common table expression:

;WITH CTE

(

SELECT 1 [Sequence]

UNION ALL

SELECT [Sequence] + 1 FROM CTE WHERE [Sequence] <100

)

SELECT * FROM CTE

Using Loop:

DECLARE @i INT

SET @i = 0

WHILE (@i < 100)

BEGIN

SELECT @i = @i + 1

PRINT @i

END

6). Write a query to calculate number of A in string 'VIKASAAA'?
Ans: SELECT LEN('VIKASAAA') - LEN(REPLACE('VIKASAAA', 'A', ''))

7). What would be the output of below query?
SELECT * FROM ( SELECT 1 UNION ALL SELECT 2 ) M
Ans: It will throw error because in sub query no column name specified

8). What would be the output of below query?
Ans: SELECT SUM(A) AS [Sum] FROM ( SELECT 1 A UNION ALL SELECT NULL A) M

9). For 5/2, I want resut as 2.5, How you will do that in SQL Server?

SELECT CAST(MyIntField1 AS float) / CAST(MyIntField2 AS float)

10). You have two tables with blank value in both table as shown in below image, Then what would be the output of the following Query based on the tables shown in image?

SELECT T1.*, T2.* FROM Table1 T1 INNER JOIN Table2 T2

ON T1.Name = T2.Name