We all love brain teasers and interesting puzzles. Today I decided to come up with 2 interesting puzzles and winner of the contest will get USD 50 worth Amazon Gift Card. The puzzles are sponsored by NuoDB. Additionally, The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Bonus Q:
How many different Operating System (OS) NuoDB support?
Click here HINT
If you can solve above puzzles you will be eligible for winning USD 50 Amazon Gift Card. However, you can always enroll yourself for following Bonus Prizes where if you have good chance of winning USD 10 Amazon Gift Card (if you are first 25 individual in specific time).
Bonus Prizes:
The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.
Rules:
- Please leave an answer in the comments section below.
- You can resubmit your answer multiple times, the latest entry will be considered valid.
- The winner will be announced on 1st October.
- Last day to participate in the puzzle is September 28th, 2012.
- All valid answer will be kept hidden till September 28th, 2012.
- Only One Winner will get USD 50 worth Amazon Gift Card.
- The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.
- The winner will be selected using random algorithm.
UPDATE: The winner of the contest is Jaime Mtnz Lafargue (@jaimeml)
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
365 Comments. Leave new
Hi Pinal,
Second answer for Puzzle no. 2 is as follows.
select CHAR(49)
Thanks
Manish
Puzzle 1 Answer:
SELECT CAST(634 AS VARCHAR(2))
When we convert int, smallint or tinyint to Char or varchar, if result length is too short to display then result is shown as *
ref:
————————————————————————————————
Puzzle 2 Answer:
select LEN(‘a’)
Question 3: How many different Operating System (OS) NuoDB support?
Answer: Windows, MacOS, Linux, Solaris, Joyent SmartOS
The results for Puzzle 2:
select (ascii(‘b’)- ascii(‘a’))
Puzzle 1:
When an int, tinyint or smallint is converted to a varchar (or char) and is truncated a * is returned.
Puzzle 2:
SELECT CAST(‘true’ as BIT)
Bonus Q:
Windows, MacOS, Linux, Solaris, Joyent SmartOS
Good fun!
Graham
Ohh I am late to this contest but would like to post the answer.
Answers:
Puzzle01: SQL Server throws a * because we are passing 3 string characters with assigning 2 in memory. Technically it’s an overflow. If we will assign VARCHAR(3) then we will get 634.
Puzzle02: SELECT LEN(‘A’)
There are many way to solve the puzzle 2 but I feel above is the shortest SELECT query to get the result 1
because of Over flow it showing * if we place the 3 digits then the var char (3) is ok else over flow comes
Because of Overflow.
SELECT LEN(‘A’)
SELECT CAST(532 AS VARCHAR(2)) — This is because of the overflow of 532 which is of 3 characters
select LEN(‘L’) — This gives the result as 1.
ANswer 1: because of Overflow. there are three integers in the given number and we are casting it in the length of two character string. so, result is ‘*’ and it indicates that result length too short to display.
Answer 2: Select Count(0)
Bonus Q: 5 different Operating System (OS) supports NuoDB
1. That is because of the digit overflow. 3 digits can’t fit into varchar(2). Same is the case with SELECT CAST(61 AS VARCHAR(1))
2. select (count(*))
second question’s answer is SELECT (ASCII(”))
same result for the following query
SELECT CAST(634 AS VARCHAR(1))
select len(‘a’)
puzzle 1
Ans:
SELECT CAST(634 AS VARCHAR(2))
Because of data truncation sql server produces * data truncation.
whenever data truncation for following explicit conversion sql server results *.
int, smallint, or tinyint -> char,varchar
puzzle 2
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Ans:
SELECt CHAR(ASCII(‘Q’)-ASCII(”))
Bonas Q:
How many different Operating System (OS) NuoDB support?
Ans:
5
1) SELECT CAST(634 AS VARCHAR(2)) – Returns a star because of loss of precision
2) SELECT (CAST(‘true’ as bit))
Bonus: 5 unless you count the flavors of Linux as seperate
when increase size of varchar it return real data, 634 or any 3 digit no. is not coming in range of varchar size which is given in varchar(2). conversion from int and smallint to varchar which size is smaller than given int value length, is not truncated. that’s why it returns *.
and second answer is .
SELECT (CAST( ‘true’ AS bit))
Puzzle #1:
(help in SSMS)
When you convert character or binary expressions ( char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.
Data type => to data type => Result
int, smallint or tinyint => VarChar => *
Puzzle #2:
select COUNT(*)
Bonus:
Answer: 5
1. Windows
2. MacOS
3. Linux
4. Solaris
5. Joyent SmartOS
Puzzle1: * is displayed because result length too short to display. String ‘634’ of 3 chars doesnt fit in varchar(2)
Puzzle2: SELECT LEN(‘x’)
Bonus Q: 10 Operating systems. Note: RHEL 5 & 6 is considered as a single operating system here.
#2. SELECT ASCII(”); The SOH character is created by opening up a text editor, entering ALT-001 and copying and pasting the result into the SQL statement.
My original comment didn’t format correctly. The command is: SELECT ACII(‘SOH’); The SOH character is x01, which is created by opening up a text editor, entering ALT-001 and copying and pasting the result into the SQL statement.