SQL Puzzle | Find Square Number from Numbers Table

Puzzle Statement

Here consider you have a table with numbers from 1 to 50. You have to find out numbers which are numbers which are square of some number from the list. E.g. 1,4,9,16,25,36,49

Sample Input

Expected output

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION #

--
--Here I am using Master..Spt_Values table to get numbers, You can use your own table.

;WITH CTE AS
(
	SELECT DISTINCT Number , SQUARE(number) Sqr
	FROM 
		MASTER..SPT_VALUES num
	WHERE 
		Number >= 1 AND Number <= 50 ) 
        SELECT Sqr FROM CTE WHERE EXISTS 
( 
        SELECT DISTINCT number FROM MASTER..SPT_VALUES x 
        WHERE x.number = CTE.Sqr AND x.Number >= 1 AND x.Number <= 50
)

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Kumar Khowal 

Http://MSBISkills.com