SQL Puzzle #8

We need a single select statement to get the count of particular character on a string.

For Eg.,

DECLARE @string varchar(50) = 'Microsoft SQL Server',
        @SearchChar  char(1)     = 'r'

Its result should be '3'

SELECT LEN(@string) - LEN(REPLACE(@string,@SearchChar,''))

SELECT DATALENGTH(REPLACE  (@string, @SearchChar, @SearchChar+@SearchChar)) - DATALENGTH (@string) As [Character Count]

SQL Puzzle #7

We have a table variable say MyTable, structure like below.

declare @MyTable Table
(
TrnDate Date, -- Transaction Date
CrDb char(1),  -- Credit/Debit
Amount  money
)

-- Data population
insert into @MyTable values
('01-jul-2012','C',1000),('02-jul-2012','D',200),('03-jul-2012','C',2500),('04-jul-2012','C',800),('05-jul-2012','D',5000)

--select * from @MyTable

we need a single query to get the result as below.

TrnDate    CrDb Amount                Balance
---------- ---- --------------------- ---------------------
2012-07-01 C    1000.00               1000.00
2012-07-02 D    200.00                800.00
2012-07-03 C    2500.00               3300.00
2012-07-04 C    800.00                4100.00
2012-07-05 D    5000.00               -900.00

select TrnDate,CrDb,a.Amount,t.Balance
from @MyTable a
cross apply
(
select sum(case when crdb = 'C' then amount else amount*-1 end) as Balance
from @MyTable b
where  b.TrnDate <= a.TrnDate
) t

SQL Puzzle #6

We have a table say myTable and looks like below.

ID Name Sal
1 a         1000
1 a         2000
2 b         3000
2 b         2000
3 c         2000

And need a query to get the result as below.

ID Name Sal1 Sal2
1 a 1000 2000
2 b 3000 2000
3 c 2000 0



Ans:

Declare  @testTable Table
(
ID int,
Name varchar(50),
Sal money
)
Insert into @testTable
Values
(1, 'a', 1000),
(1, 'a', 2000),
(2, 'b', 3000),
(2, 'b', 2000),
(3, 'c', 2000)


select ID,Name,isnull([sal1],0) As [sal1],isnull([sal2],0) As [sal2]
from
(
     select id,name,sal,'Sal'+cast(ROW_NUMBER() over (partition by id,name order by id,name) as varchar(10)) as rownum
     from @testTable
) sources
pivot
(
     sum(sal)
     For rownum in([sal1],[sal2])
) pvt


NB: We can also do it with column dynamically..

SQL Puzzle #5

We have a table, say MyTable and data spreading looks like as follow;

Col1       Col2       Col3       Col4
1              A             B              C      
2              E              F              G      
3              I               J               K      
4              M            N             O    

And need a query to get the output as below;

Col1       MergedCol
1              A      
1              B      
1              C      
2              E      
2              F      
2              G      
3              I      
3              J      
3              K      
4              M      
4              N      
4              O    

And I have a simple strait forward query to get this result.
SELECT col1,col2 from MyTable
Union ALL
SELECT col1,col3 from MyTable
Union ALL
SELECT col1,col4 from MyTable
Union ALL
SELECT col1,col5 from MyTable




Ans:
Create Table TblTest
(
Col1 Int,
Col2 Varchar(50),
Col3 Varchar(50),
Col4 Varchar(50),
)


Insert Into TblTest Values (1,'A','B','C'),(2,'E','F','G'),(3,'I','J','K'),(4,'M','N','O')

Select * From TblTest

Select Col1,Upt.ColNew From TblTest UnPivot (ColNew For ColNewSt In (Col2,Col3,Col4))AS UPT

SQL Puzzle #4 - Comma separated value split up without using loop.

We have a table variable named @TableVar

--// Creating Table variable
Declare @TableVar Table
(
Data varchar(max)
)

--// Inserting data to table variable
Insert into @TableVar values('01-May-2012,05-May-2012,13-Apr-2012')

Qn: Write a query to get the result as shown below without using loop.

  Data
-------------
2012-05-01
2012-05-05
2012-04-13


Ans :

DECLARE @XML XML

SELECT @XML = '<Root><Child><Data>'+REPLACE(Data,',','</Data></Child><Child><Data>')+'</Data></Child></Root>' FROM @TableVar

SELECT CAST(DOC.COL.value('Data[1]','NVARCHAR(400)') AS DATE) AS Data
FROM @XML.nodes('/Root/Child') DOC(COL)




Another Method :

--// Creating Table variable
Declare @TableVar Table
(
Data varchar(max)
)

--// Inserting data to table variable
Insert into @TableVar values('01-May-2012,05-May-2012,13-Apr-2012')

Declare @Str AS Varchar(Max)
Select @Str = 'Select ' +'''' + REPLACE (Data,',',' ''UNION Select ''')  FRom @TableVar
Set @Str = @Str + ''''
Exec (@Str)

SQL Puzzle #3 - How to delete all the records from the entire table in a database using single line query?

How to delete all the records from the entire table in a database using single line query?


Ans : sp_MSForEachTable "Delete ?"

Try it another method;

DECLARE @str VARCHAR(MAX);
SELECT @str =
(
SUBSTRING
(
(SELECT 'DELETE FROM ' + TABLE_NAME + ';' FROM INFORMATION_SCHEMA.TABLES
FOR XML PATH('')),1,99999
)
)
EXEC (@str)

SQL Puzzle #2 - How to populate the same data 5 times to table without using any loop/cursor/recursive query.

We have a table, say MyTable and we want to populate data to the table using one INSERT query.
My question is, how to populate the same data 5 times to table without using any loop/cursor/recursive query.

--// Table
Create Table MyTable
(
FirstName Varchar(50),
SecondName Varchar(50)
)

--// Sample Insert query as follows;

Insert into MyTable values('Sachin','Tendulkar')

Ans:

Insert into MyTable values('Sachin','Tendulkar')

GO 5

SQL Puzzle #1 - Write the shortest code that produces results as 1 without using any numbers in the select statement.

Ans:

SELECT DIFFERENCE('Ant', 'Elephant')

SELECT DATALENGTH(' ')

SELECT LEN('x')

SELECT ISNUMERIC($)

SELECT ISNUMERIC('+')

SELECT ISNUMERIC('-')

SELECT ISNUMERIC('.')

SELECT COUNT_BIG(*)

SELECT COUNT(*)

SELECT CEILING(RAND())

SELECT ISDATE(GETDATE())

SELECT MONTH(GETDATE())/MONTH(GETDATE())

SELECT DAY(GETDATE())/DAY(GETDATE())

SELECT YEAR(GETDATE())/YEAR(GETDATE())

SELECT POWER($,$)

SELECT ISNULL(NULL,COUNT(*))

SELECT LEN(NEWID())/LEN(NEWID())

SELECT @@SPID/@@SPID

SELECT UNICODE('A')/ UNICODE('A')

SELECT ISNUMERIC(@@SPID)

SELECT COS('')

SELECT ROW_NUMBER() over (order by A )From (Select 'A' As A )A

select Count('a')

Select SCHEMA_ID()

Select COUNT(Schema_id())

Select Len(Schema_id())

Select CHARINDEX('A','A')

Select LEN(CHARINDEX('A','A'))

Select Count(CHARINDEX('A','A'))