Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

Number
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

Expected output

Sqr
1
4
9
16
25
36
49

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

Advertisements