"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

November 12, 2010

TSQL Interesting Questions

Listed below are few TSQL Interesting code snippets and output also provided below the examples. Output I observed in SQL 2008 R2 version.

Example #1
declare @i int
select @i = -5
select +@i
Output: -5

Example #2
declare @i varchar
select @i = 'This is a test'
select @i
Output: T

Example #3
declare @val char(30)
set @val = 'SSc is cool'
select len(@val)
Output: 11

Example #4
declare @val as float, @val1 as float
set @val = 2.0
set @val1 = 1.4
select @val1+@val as sum
Output: 3.4 

Example #5. Minimum of each coulmn

create table #i ( i int, j int, k int)
insert #i values(1, 2, 3)
insert #i values(1, 3, 2)
insert #i values(2, 1, 3)
insert #i values(2, 3, 1)
insert #i values(3, 1, 2)
insert #i values(3, 2, 1)
insert #i values(1, 2, 1)
insert #i values(1, 1, 1)
insert #i values(1, 0, 1)

select (case when (i <= j and i <= k) then i when j <= k then j else k end)
from #i

Example #6. Greatest value of 3 columns. Here's an example of what you want using a derived table

declare @t table(id int, val1 int,val2 int,val3 int)
insert into @t(id,val1,val2,val3) values(1,100,200,300)
insert into @t(id,val1,val2,val3) values(2,300,400,500)
insert into @t(id,val1,val2,val3) values(3,600,700,800)

select id,max(vals)
   select id,val1
   from @t
union all
   select id,val2
   from @t
union all
   select id,val3
   from @t
) X(id,vals)
group by id

Example #7. Maxof All 3 columns

select max(vals)
   select A
   from #min
union all
   select B
   from #min
union all
   select C
   from #min
) X(vals)

Thanks to authors of this problems. I am not sure where I read this 'C' Programming Style Problems. I found it interesting.

No comments: