Interesting T - SQL Queries (T - SQL Interview Questions)

Question 1
Write a single DML statement to replace 0 with 1 & 1 with 0?

Answer
create table #temp

(nm varchar(10),

id int)



insert into #temp values

('one', 1), ('zero', 0), ('one', 1), ('zero', 0), ('one', 1),

('zero', 0), ('one', 1), ('zero', 0), ('one', 1), ('zero', 0)



select * from #temp



update #temp

set id = case when id = 1 then 0

when id = 0 then 1

end



select * from #temp


Question 2
Write a Query to get maximum 3rd salaried employee
create table #emp

(empid int,

empsal money)

insert into #emp values(1, 25000), (2, 30000), (3, 15000), (4, 40000), (5, 22000) ,(6, 18000)

select * from #emp order by empsal desc



--using top keyword
 select top 1 * from



(
  select top 3 * from #emp

order by empsal desc

)a

order by empsal asc



--using ranking function
 select empid, empsal



from
 (
  select empid, empsal, RANK() over (order by empsal desc) rnk

from #emp

)a

where a.rnk = 3



--using corelated subquery
 select *

from #emp e1

where (3-1) = (select COUNT(distinct (e2.empsal)) from #emp e2 where e2.empsal > e1.empsal)

Changing the font color/background color of a row dynamically while sending mails from SQL Server

Normally if we like to track our data from the DB on a daily basis, either we generate some reports using SSRS and schedule the reports to specific mail ID, or we write some queries and send the summary data as a report in a mail using sp_send_dbmail in an HTML format.

If there are some specfic values which we are tracking on a daily basis, and if we want to highlight the particular row with specific color based on some conditions, it is easy to set the color dynamically in SSRS.

To achieve the same using sp_send_dbmail , we have to write SQL queries which generate such kind of XMLs.

Consider the below example. Suppose we have a table which has data as below

place	Temperature
Sirsi	30
Bangalore	35
Hyderabad	45
Mumbai	41

We have to highlight the data for which the temperature is more than 40 degrees. We can highlight either by making it as bold/changing background color/changing font color

Dynamic Background Color Change

To dynamically change background color we need xml as given below:

<tr bgcolor = "red">

       <td>Hyderabad<td>

       <td>45</td></tr>

<tr bgcolor = "white">

        <td>Sirsi<td>

        <td>30</td></tr>

Note: This is just a sample xml

We can generate such XML using below query

SELECT

         CASE WHEN temperature > 40 THEN '#F78181' 

                    ELSE 'white'

             END AS [@bgcolor],

         td = place, '',

         td = temperature, ''

FROM test_fontcolor

FOR XML PATH('tr')

To send an email use the below format

declare @tableHTML nvarchar(max)

SET @tableHTML =

      N'<H1>Test Data</H1>' +

      N'<H4>TEST_sub_header</H4>' +

      N'<H4>TEST_header_1</H4>' +

      N'<table border="1">' +

      N'<tr><th>place</th>

      <th>Temperature</th>' +

      N'</tr>' +

      CAST ( (

        SELECT

         CASE WHEN temperature > 40 THEN '#F78181' 

                    ELSE 'white'

             END AS [@bgcolor],

         td = place, '',

         td = temperature, ''

        FROM test_fontcolor

        FOR XML PATH('tr')

      ) AS NVARCHAR(MAX) ) +

      N'</table>' ;

   

EXEC msdb.dbo.sp_send_dbmail

        --@profile_name='WP Store BI Notifications',

     @recipients='v-raheg@microsoft.com',

     @from_address = 'storenote@microsoft.com',

     @subject = 'Test Background Color',

     @body = @tableHTML,

     @body_format = 'HTML' ;

The output of would be  as below:

Test Data

TEST_sub_header

TEST_header_1

place	Temperature
Sirsi	30
Bangalore	35
Hyderabad	45
Mumbai	41

Dynamaic Font Color Change

To change the color of the font dynamically the XML should look like as below:

<tr>

   <font color = "red">

         <td> Hyderabad </td>

         <td> 45 </td>

   </font>

</tr>

<tr>

   <font color = "black">

         <td> Sirsi</td>

         <td> 30 </td>

   </font>

</tr>

SQL query to generate XML as above would look like :

SELECT

      CASE WHEN Temperature>40 THEN 'red'

            ELSE 'black'

      END  AS "font/@color",

      place  as "font/td",'',

      CASE WHEN Temperature>40 THEN 'red'

            ELSE 'black'

      END  AS "font/@color",

    Temperature as "font/td"

FROM

      test_fontcolor

ORDER BY place

FOR XML PATH('tr')

NOTE: Above query does not generate XML exactly like above. For every td tag there would be one font tag. It will generate like:

<tr>

   <font color = "red">

         <td> Hyderabad </td>

   </font>

  <font color = "red">

         <td> 45 </td>

   </font>

</tr>

To send an email use the below code:

declare @tableHTML nvarchar(max)

SET @tableHTML =

                N'<H1>Test Data</H1>' +

                N'<H4>TEST_sub_header</H4>' +

                N'<H4>TEST_header_1</H4>' +

                N'<table border="1">' +

                N'<tr><th>place</th><th>Temperature</th>' +

                N'</tr>' +

                CAST ( ( SELECT

                                          CASE WHEN Temperature>40 THEN 'red'

                                                ELSE 'black'

                                          END  AS "font/@color",

                                          place  as "font/td",'',

                                          CASE WHEN Temperature>40 THEN 'red'

                                                ELSE 'black'

                                          END  AS "font/@color",

                                          Temperature as "font/td"

                                    FROM

                                          test_fontcolor

                                    ORDER BY place

                                    FOR XML PATH('tr')

                ) AS NVARCHAR(MAX) ) +

                N'</table>' ;  

                               

EXEC msdb.dbo.sp_send_dbmail

     --@profile_name='WP Store BI Notifications',

       @recipients='v-raheg@microsoft.com',

      @from_address = 'storenote@microsoft.com',

       @subject = 'Test Font Color',

       @body = @tableHTML,

       @body_format = 'HTML' ;

The out put would be :

Test Data

TEST_sub_header

TEST_header_1

place	Temperature
Sirsi	30
Bangalore	35
Hyderabad	45
Mumbai	41