Tags

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


SQL Puzzle | The ZERO Blank Puzzle | SQL Interview Question

In this puzzle you have to find difference between a and b column between each row and if the difference is not equal to 0 then show the difference i.e. a – b otherwise 0. Now you need to replace this zero with blank

Please check the sample input and the expected output.

Sample Input

Id a b
1 10 4
2 10 10
3 1 10000000
4 15 15

Expected Output

Id a b OUTPUT
1 10 4 6
2 10 10
3 1 10000000 -9999999
4 15 15

Use below script to create table and insert sample data into it.

--

CREATE TABLE TheZeroPuzzle
(
	Id INT
	,a INT 
	,b INT
)
GO

INSERT INTO TheZeroPuzzle VALUES 
(1,10,4) 
, (2,10,10) 
, (3,1, 10000000 )
, (4,15,15)
GO

SELECT * FROM TheZeroPuzzle
GO

--

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 – 1 | Using CAST Only

--

SELECT *
	, CASE WHEN a - b <> 0 THEN CAST(a - b AS VARCHAR)
			ELSE '' END OUTPUT
FROM TheZeroPuzzle

--

Output-1

--                            

Id          a           b           OUTPUT
----------- ----------- ----------- --------------------
1           10          4           6
2           10          10          
3           1           10000000    -9999999
4           15          15          

(4 rows affected)



--

SOLUTION – 2 | Using Case and CONCAT

--

SELECT *
	, CASE WHEN a - b <> 0 THEN CONCAT(a-b,'') ELSE '' END OUTPUT
FROM TheZeroPuzzle

--

Output-2

--                            

Id          a           b           OUTPUT
----------- ----------- ----------- --------------------
1           10          4           6
2           10          10          
3           1           10000000    -9999999
4           15          15          

(4 rows affected)



--

SOLUTION – 3 | Using STR and CASE

--

SELECT *
	, CASE WHEN a - b <> 0 THEN STR(a-b) ELSE '' END OUTPUT
FROM TheZeroPuzzle


--

Output-3

--                            

Id          a           b           OUTPUT
----------- ----------- ----------- --------------------
1           10          4           6
2           10          10          
3           1           10000000    -9999999
4           15          15          

(4 rows affected)



--

SOLUTION – 4 | Using CONCAT and CASE

--

SELECT *
	, CONCAT( CASE WHEN a - b <> 0 THEN a - b END ,'')  OUTPUT
FROM TheZeroPuzzle


--

Output-4

--                            

Id          a           b           OUTPUT
----------- ----------- ----------- --------------------
1           10          4           6
2           10          10          
3           1           10000000    -9999999
4           15          15          

(4 rows affected)



--

Please add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn. Thanks in advance.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com