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
Bonus question:
Windows, MacOS, Linux, Solaris, Joyent SmartOS
Puzzle2:
select COUNT(‘a’)
Puzzle1:
since we mentioned it as varchar(2) but we are referring to a 3digit number, it is unable to print the number. it will work in same manner if we reffer to any number more than 2 digits.
1) in select statement if we use varchar (3) it will display the 634 otherwise its is dispalying star
2) select len(‘A’)
Answer1: SQL can’t convert the 3 digit number to a varchar(2), so it replaces the value with a star because it doesn’t fit. A varchar(3) would be needed.
Answer2: SELECT COUNT(”)
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Ans 1:
634 is numeric, it cant be convert/reduced to varchar with the size of 2 thats why it returns “*”
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
SELECT CAST(REVERSE(ASCII(‘d’))AS INT) — will returns 1
Bonus Q:
How many different Operating System (OS) NuoDB support?
Nuo DB will support 5 OSs which are Windows, MacOS, Linux, Solaris, Joyent SmartOS.
notes:
I could not able to answer all questions early morning at 7 : 10 am, coz the site http://www.wordpress.com was blocked at my office.
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
SELECT CAST(634 AS VARCHAR(2))
Ans 1:
A 3 digit numeric value cannot be converted/reduced to varchar with size of 2
this is the reason the result returns “*”
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
ANS 2:
SELECT CAST(REVERSE(ASCII(‘d’))AS INT)
— the result will returns int-1
Bonus Q:
How many different Operating System (OS) NuoDB support?
The NuoDB will support 5 OS which are Windows, MacOS, Linux, Solaris, Joyent SmartOS
Note:
My office firewall blocked http://www.wordpress.com‘s site thats why i couldn’t answer these questions early morning 7:10 am.
thank you. this kind of puzzles are interesting and innovative.
Puzzle 1:
Why following code when executed in SSMS displays result as a * (Star)?
Answer from https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
* = Result length too short to display.
Puzzle 2:
Write the shortest code that produces results as 1 without using any numbers in the select statement.
Answer SELECT (@@rowcount)
Bonus Q:
How many different Operating System (OS) NuoDB support?
5, Support for Windows, MacOS, Linux, Solaris, Joyent SmartOS
select (cast(‘TRUE’ as bit))
1) The varchar(2) doesn’t have enough room to show the number so it resorts to the *. If you use varchar(3) or greater then it displays correctly.
2) select ASCII(”)
Bonus: 5 operating systems
SELECT ~CAST( AS BIT)
Puzzle 1:
634 converted to a char. string is ‘634’, too large for a varchar(2) (much less a varchar(1)); if the 2 in the puzzle is changed to a 3, i.e.
SELECT CAST(634 AS VARCHAR(3))
a result other than “*” will be displayed. “*” is displayed because the result is too large — it would likewise be displayed if we attempted to CAST any three- (or larger) digit number, as opposed to 634, into a VARCHAR(2).
Puzzle 2:
Pick any table with at least one “int” column IC containing data and SELECT MAX(IC/IC) it. As an example, I connected to a server with a msdb database that has a non-empty table sysjobs and executed this statement:
SELECT MAX(start_step_id/start_step_id) FROM sysjobs
Bonus Q:
According to the Webpage you “hint”ed at, nuoDB has “[s]upport for Windows, MacOS, Linux, Solaris, Joyent SmartOS”, i.e. five different operating systems.
Hello Pinal,
Again, great puzzles, thanks!
Puzzle 1:
The result is a star, because varchar(2) does not allow to display the entire string. Use instead: SELECT CAST(634 AS VARCHAR)
Puzzle 2:
select ascii(‘b’) – ascii(‘a’)
or shorter:
select ascii(”) — the character you get by: select char(1)
Both probably not what you expect?! :-)
Bonus Q:
5 OS: Windows, MacOS, Linux, Solaris, Joyent SmartOS
Best regards,
Sparks
SELECT LEN(‘a’)
Puzzle #1
collate & implicite cast
>> * = Result length too short to display
Pinal, I have taken from your earlier post of how to display 0. Here is the simple answer to display 1
select count(*) where ‘a’!=’b’
1) Conversions from int to varchar are truncated when result length is too short to display.
2) select len(‘A’)
Bonus) NuoDB supports 5 OS
1).
Overflow, you are trying to convert the number 634 to a string but only giving the string 2 characters of space where 3 are needed.
2).select LEN(‘a’)
Puzzle 1:
The reason is the result is too short to display.
The result will be same for char, varchar, nchar, nvarchar. The correct result will be obtained for below query:
SELECT CAST(634 AS CHAR(3))
Puzzle 2:
SELECT Count(‘Your Answer Here’)
Bonus question:
The supported OS are Windows, MacOS, Linux, Solaris, Joyent SmartOS.
1. Becase cannot convert 3 digit number to varchar(2)
Same will hapen if we try SELECT CAST(63432 AS VARCHAR(3))
2. SELECT COUNT(*)
PS: Do not want any reward, just playing, but please reply me with the correct answers or write some blog post.
1 – Cast overflow error converting INT to VARCHAR returns *
2 – SELECT CAST(PI() AS BIT)
3 – 5