6 SQL Tricky Interview Questions

Hi, I discuss here 6 tricky SQL interview questions and answers.
To get result run these code at your end and see the Output.

----Question - 1. HOW TO REPLACE MULTIPLE COMMA FROM BELOW INPUT BY SINGLE COMMA?

DECLARE @input VARCHAR(100)='abc,,,dddd,,,,,,dde,,2lmdl3emf,cewec,,,,,'

SELECT @input,

REPLACE(@input,',','.,'),

REPLACE(REPLACE(@input,',','.,'),',.',''),

REPLACE(REPLACE(REPLACE(@input,',','.,'),',.',''),'.,',',')

----Question - 2. WHAT WOULD BE THE OUTPUT OF BELOW QUERY?

DECLARE @source TABLE(id INT)

DECLARE @dest TABLE(id INT)

INSERT @source VALUES (1),(1)

SELECT * FROM @source

MERGE @dest AS D

Using @Source AS S ON s.id=d.id

WHEN MATCHED THEN UPDATE SET d.id=s.id

WHEN NOT MATCHED BY TARGET THEN INSERT (id) VALUES (s.id);

SELECT * FROM @dest

-----Question - 3. WRITE QUERY TO GET OCCURENCE OF 'A' CHARECTER IN BELOW STRING?

SELECT LEN('ARKA GUPTA')-LEN(REPLACE('ARKA GUPTA','A',''))

-----Question - 4. WRITE QUERY TO GET THE ATTENDANCE COUNT?

DECLARE @ATT TABLE (EMP INT,DATEPRESENT VARCHAR(50))

INSERT @ATT VALUES (1,'1,2,3'),(2,'2,4,5,6'),(3,'1,2,5,4,7,9')

SELECT * FROM @ATT

SELECT *,LEN(DATEPRESENT)-LEN(REPLACE(DATEPRESENT,',','') )+1

FROM @ATT

-----Question - 5. HOW TO WRITE A UPDATE QUERY TO SWAP COLUMN VALUES?

DECLARE @TBL TABLE(ID VARCHAR(100),NAME VARCHAR(100))

INSERT @TBL VALUES(1,'AA'),(2,'BB'),(3,'CC')

SELECT * FROM @TBL

UPDATE @TBL

SET ID=NAME,NAME=ID

SELECT * FROM @TBL

----Question - 6. WRITE A QUERY TO GENERATE 1 TO 100 USING CTE?

DECLARE @I INT=100

;WITH CTE AS

(SELECT 1 AS ID

 UNION ALL

 SELECT ID+1

 FROM CTE

 WHERE ID<@I

)

SELECT * FROM CTE 

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,

Arka Gupta.

http://arkaguptablog.blogspot.in/

https://in.linkedin.com/in/arkagupta