How you can print 1 to 10 table using SQL Query, HCL

Asked in HCL interview(my friend faced this questions experience 6 years)
For print table you can use CTE as below, (you can copy query from comment section)
Please share your solution as well

 

Read More

SQL Server Replace Tricky Query asked in HCL

How you will replace "A" with "B" and "B" with "A" in a string using sql query?(HCL Interview)

Ans:

 

Read More

How Use sp_MSForEachTable SP for Tables Row Count

Easiest way to get row count of every table in a database. You can use sp_MSForEachTable
SP

CREATE TABLE #Tablescounts

(table_name varchar(255),row_count int)

 

EXEC sp_MSForEachTable @command1 = 'INSERT #Tablescounts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

 

SELECT * FROM #Tablescounts

Read More

SQL Pivot Tricky Query Example Asked in TCS

1). We have a table(#Temp1) contain RollNumber, Subject, Marks and want output as below, Write query for same.(pivot) asked in TCS

 

SELECT * FROM (SELECT * FROM(SELECT RN,[Subject],Marks from #Temp1)a

PIVOT(MAX(Marks) for [Subject] in ([Math],[Hindi],[English]))E) K

CROSS APPLY (SELECT MAX(Marks) MaxMarks FROM #temp1 WHERE RN = K.RN) S

 







2). Now write query which will show Subject as well like below .(pivot)

 

SELECT * FROM (

SELECT * FROM( SELECT RN,[Subject],Marks from #temp1)a

PIVOT (MAX(marks) for [Subject] in ([Math],[Hindi],[English]))E) K

CROSS APPLY (

SELECT CONCAT(Marks,('-'+[Subject])) MaxMarks_Subject FROM (

SELECT ROW_nUMBER() OVER(PArtition by rn order by Marks DESC) RowNum,Marks,Subject FROM #temp1 Where RN= K.RN)

J WHERE J.RowNum = 1

) S

Read More

Most Important SQL Interview Question, Order of Query Process asked in Wipro/Sapient

Asked in Wipro and Sapient SQL Server Interview

What will execute first tell the order of every keyword used in below query?

Ans is here: 

Read More