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
Puzzle 1: The result displays a * as the int conversion is truncated.
Puzzle 2: SELECT CAST(‘true’ AS bit)
Bonuz Q: NuoDB supports Windows, MacOS, Linux, Solaris, Joyent SmartOS.
Answer to bonus question is 5. They are Windows, MacOS, Linux, Solaris, Joyent SmartOS
Puzzle 1: The conversion will lead to loss of data so the output is shown as *. As explained by MS “It requires explicit CAST to prevent the loss of precession or scale that might occur in an implicit conversion”
Puzzle 2: Select FLOOR(TAN(45)) — 1
Let me know if I am correct.
Hi Pinal,
1) * = Result length too short to display
2) SELECT LEN(‘a’)
1. Since the varchar size is 2 but the number 634 size is bigger than the varchar size, the output results as a star(*)
2.select len(‘a’)
3. 6
Puzzle1: The cast reduces the number of decimals that can be stored to two when there are three that need to be displayed. A star is output whenever all the digits in a number can’t fit the variable it is stored in.
Puzzle 2: SELECT EXP(”)
Bonus Puzzle: 5 different OS’s
Puzle 1. Due to Truncation when converting int to varchar, SQL Server defaults the value to ‘*’. This will throw error when converting to nchar, nvarchar
Puzzle 2. select (select CHARINDEX(‘A’,’A’))
in the puzzzle 1 we are casting integer to varchar but as we all know char dont have sufficient bytes of space to store integer 634.
So it is displaying star (*) because of casting to varchar from interger.
Q1 : * is the error result, 634 is long to display in varchar(2)
Q2 : select CAST(‘true’ as bit)
Bonus Q: : MacOS, Windows, RHEL, SuSe, Ubuntu, Solaris , Joyent SmartOS
select cast(634 as varchar(2))
Result shows ‘*’ because of
* means it requires implecit CAST to prevent the loss of precision.
So that Query should like
SELECT CAST(634 AS VARCHAR(3)) will shows the right out put
OR
SELECT CAST(634 AS VARCHAR) In this Precision set Itself as requires.
star comes for numerical values and for Decimal value if precision is small then it shows “*”
Thank you & Regards
Kaushal Dhora
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
SELECT count(‘ ‘)
Puzzle-1 Ans :if the Length of the Result too short to display While Converting the int/Smallint/tinyint value into Char/Varchar. By Default SSMS will display the resultset as “*”.The right Conversion for this example is Select Cast(5687 as CHAR(4)) will give the Expected result.
Puzzle 1:
Result length too short to display
Puzzle 2:
SELECT COUNT(*)
if the Length of the Result too short to display While Converting the int/Smallint/tinyint value into Char/Varchar. By Default SSMS will display the resultset as “*”.The right Conversion for this example is Select Cast(5687 as CHAR(4)) will give the Expected result
Ans 1: number are you trying to cast has length more than the size it try to cast.
Number length is 3 and trying to convert in size 2, so we are getting result as *.
Puzzle 1 : if the Length of the Result too short to display While Converting the int/Smallint/tinyint value into Char/Varchar. By Default SSMS will display the resultset as “*”.The right Conversion for this example is Select Cast(5687 as CHAR(4)) will give the Expected result.
Puzzle 2 : SELECT COUNT(*)
shortest code for result 1 is SELECT EXP(‘ ‘)
Puzzle 1 : if the Length of the Result too short to display While Converting the int/Smallint/tinyint value into Char/Varchar. By Default SSMS will display the resultset as “*”.The right Conversion for this example is Select Cast(5687 as CHAR(4)) will give the Expected result.
Puzzle 2 : SELECT COUNT(*)
select LEN(‘a’)
or
select count(*)
or
select exp(”)
Puzzle1: The cast reduces the number of decimals that can be stored to two when there are three that need to be displayed. A star is output whenever all the digits in a number can’t fit the variable it is stored in.
Puzzle 2: SELECT EXP($)
Bonus Puzzle: 5 different OS’s