SQL Puzzle 1

Q1. How to select Random record from a Table

select top 1 * from Employee order by newid()

Q2. You have a table Employee with a column EName which contains Records Employee Name as A,B,A,A,B,D,C,M,A . Write a query to swap EName A to B and B to A.

UPDATE Employee

SET EName = (CASE WHEN EName='A' THEN 'B'

                  WHEN EName='B' THEN 'A'

                  ELSE EName

           END)

Q3. Write a query to calculate number of A in string AUSTRALIAA.

SELECT LEN('AUSTRALIAA')-LEN(REPLACE('AUSTRALIAA','A',''))    

Q4. What will be the output of below query?

SELECT * FROM (SELECT 1 UNION ALL SELECT 2) M

Msg 8155, Level 16, State 2, Line 1

No column name was specified for column 1 of 'M'.

Correct way :

SELECT * FROM (SELECT 1 as Col1 UNION ALL SELECT 2) M

Col1

1

2

Q5. What will be the output of below query?

SELECT SUM(A) as Total FROM (SELECT 1 A UNION ALL SELECT NULL A) M

Total

1

Q6. For 5/2, I want result as 2.5

SELECT CAST(5 AS float)/CAST(2 AS float)

Q7. What will be the output of below query?

SELECT CASE WHEN 1=1 THEN 'Hello'

            WHEN 2=2 THEN 'Hii'

            ELSE 'Bye'

       END AS Name

Output: Hello

Q8. What will be the output of below query?

SELECT 'Binod' + NULL + 'Kumar' AS [Full Name]

Output: NULL