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
ANSWERS :
PUZZLE 1:
FROM DATATYPE : int , smallint, or tinyint
TO DATATYPE : char , varchar
RESULT : *
FROM DATATYPE : int , smallint, tinyint , money , smallmoney, numeric, decimal, float, or real
TO DATATYPE : char , varchar , nchar , nvarchar
RESULT : E
* = Result length too short to display. E = Error returned because result length is too short to display.
PUZZLE 2:
select exp(”)
in the puzzle 1 we are casting 3 digits and we have written size of the varchar is 2 so because of that it is displaying *.
It is the property of char and varchar that when we try to cast more digits or char den we written it will display *.
and for 2nd puzzle the the shortest code foe displaying 1 is
Select EXP(”)
Answer 1
SELECT CAST(634 AS VARCHAR(2))
we have taken length as 2 but 634 is having length 3 hence.It is showing *
Answer 2
SELECT CAST(64 AS VARCHAR(1))
1. Overflow
2. select LEN(‘a’)
PUZZLE 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Answer:
select LEN(‘u’)
SELECT (ASCII(‘B’) -ASCII(‘A’))
When integers are implicitly converted to a character data type, if the integer is too large to fit into the character field, SQL Server enters ASCII character 42, the asterisk (*).
PUZZLES – 2:
select Exp(”)
select count(*)
select len(‘a’)
select len(‘a’)
Answer 1) Due to overflow from trying to cast a 3 digit integer down to a varchar of size 2.
Answer 2) SELECT COUNT(‘A’)
puzzle 1: while converting int to varchar and given length is less then the given number in this case result is very small for display and it will show *
puzzle 2: sortest method for getting 1 without usin number
select count(*)
Puzzle 2: select COUNT(*)
Puzzle 1: resulting expression too small to display then cast values that why we are getting ‘*’
Puzzle 1: As the varchar is restricted to 2 places, it is showing * for 3 digited 634.
Puzzle 2: select DB_ID(‘master’)
Bonus Q: 5 Operating System
The value 634 does not fit in 2 characters (CHAR(2) or VARCHAR(2)). You need at least 3 chars.
Oh! and for the second question:
select count(getdate()) –> 1
Q1: When integers are implicitly converted to a character data type, if the integer is too large to fit into the character field, SQL Server enters ASCII character 42, the asterisk (*).
Q2: NuoDB supports Windows, MacOS, Linux, Solaris, Joyent SmartOS.
Here is the answer for the first puzzle:
The select statment is trying to convert a number with three algarisms which is bigger than the maximum number fo algarisms allowed by varchar(2), namely, 2 characters.
Here is the naswer to the second puzzle:
Select ASCii(‘A’)/ASCii(‘A’)
Puzzle 2:
ANS : SELECT (LEN(‘a’))
Puzzle 1 : According to SQL Help on the CAST function : “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…”
So, conversions from int, smallint or tinyiny to char, varchar, nchar, nvarchar, binary, and varbinary are not truncated. That’s why in the SELECT CAST(634 AS VARCHAR(2)) statement, the “*” is displayed, because the result field is too short to display the value.
Puzzle 2 : SELECT ISDATE(GETDATE())
Bonus question : NuoDB supports 5 operating systems
Hello. My answers :
1- The result length is too short to display. Converting data type int to varchar does not imply trucation when the result length is shorter than necessary.
I we had typed “SELECT CAST(634 AS VARCHAR(3))” the result would have been ‘634’
2- SELECT COUNT(*)
That’s the shortest I could find.
Bonus Q – 5 different OSs (Windows, MacOS, Linux, Solaris, Joyent SmartOS)