Daily Archives: June 4, 2015

T-SQL Query | [ The FizzBuzz Puzzle ]

04 Thursday Jun 2015

Posted by in T SQL Puzzles

≈ Leave a comment

Tags

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


T-SQL Query | [ The FizzBuzz Puzzle ]

Original Puzzle Link –  http://imranontech.com/2007/01/24/using-fizzbuzz-to-find-developers-who-grok-coding/

Puzzle Description

This is a really cool puzzle. I must admit, I got it incorrect for the first time. Lets check out the puzzle. We have to write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”.

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

 

Expected Output

number Outputs
1 1
2 2
3 FIZZ
4 4
5 BUZZ
6 FIZZ
7 7
8 8
9 FIZZ
10 BUZZ
11 11
12 FIZZ
13 13
14 14
15 FIZZBUZZ
16 16
17 17
18 FIZZ
19 19
20 BUZZ
21 FIZZ
22 22
23 23
24 FIZZ
25 BUZZ
26 26
27 FIZZ
28 28
29 29
30 FIZZBUZZ
31 31
32 32
33 FIZZ
34 34
35 BUZZ
36 FIZZ
37 37
38 38
39 FIZZ
40 BUZZ
41 41
42 FIZZ
43 43
44 44
45 FIZZBUZZ
46 46
47 47
48 FIZZ
49 49
50 BUZZ
51 FIZZ
52 52
53 53
54 FIZZ
55 BUZZ
56 56
57 FIZZ
58 58
59 59
60 FIZZBUZZ
61 61
62 62
63 FIZZ
64 64
65 BUZZ
66 FIZZ
67 67
68 68
69 FIZZ
70 BUZZ
71 71
72 FIZZ
73 73
74 74
75 FIZZBUZZ
76 76
77 77
78 FIZZ
79 79
80 BUZZ
81 FIZZ
82 82
83 83
84 FIZZ
85 BUZZ
86 86
87 FIZZ
88 88
89 89
90 FIZZBUZZ
91 91
92 92
93 FIZZ
94 94
95 BUZZ
96 FIZZ
97 97
98 98
99 FIZZ
100 BUZZ

 

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
  • Try not to use loop here.

Update June 4 | Solutions – Pawan Kumar Khowal 


--

SELECT DISTINCT number,
	CASE WHEN CAST(number AS VARCHAR(3)) % 15 = 0 THEN 'FIZZBUZZ'
		 WHEN CAST(number AS VARCHAR(3)) % 3 = 0 THEN 'FIZZ'
	     WHEN CAST(number AS VARCHAR(3)) % 5 = 0 THEN 'BUZZ'
    ELSE
		CAST(number AS VARCHAR(3))
	END Outputs
FROM master..spt_values WHERE number BETWEEN 1 AND 100 ORDER BY number

--

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

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal

T-SQL Query | [ Change 0 to 1 and 1 to 0 Puzzle ]

04 Thursday Jun 2015

Posted by in T SQL Puzzles

≈ 1 Comment

Tags

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


T-SQL Query | [ Change 0 to 1 and 1 to 0 Puzzle ]

Puzzle Description

1. We have a table called ZeroOne.
2. Here we need to show 1 instead of 0 and 0 instead of 1.
3. Please check out the sample input and expected output for details.

Sample Input

Id
0
1
0
1
0
0

Expected Output

Id Ids
0 1
1 0
0 1
1 0
0 1
0 1

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

Script

Use the below script to generate the source table and fill them up with the sample data.


--

CREATE TABLE ZeroOne
(
Id INT
)
GO

INSERT INTO ZeroOne VALUES (0),(1),(0),(1),(0),(0)

--

Update June 4 | Multiple Solutions – Pawan Kumar Khowal 


--

SELECT *, CASE ID WHEN 0 THEN 1 ELSE 0 END Ids FROM ZeroOne

SELECT *, (Id - 1) * -1 Ids FROM ZeroOne

SELECT *, 1 - Id Ids FROM ZeroOne

SELECT *, IIF(ID=0,1,0) Ids FROM ZeroOne

SELECT Id, (Id+1/2 -1) * -1 Ids FROM ZeroOne

DECLARE @t AS INT = 1
SELECT Id, CHOOSE(@t,1,0) Ids FROM ZeroOne

--

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

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal