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
Because it tries to convert 634 to character form but since the size of varchar declared is only 2, it returns unkown data
select count(*)
select ascii(”) for the second quiz
1. 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. (per books online) An * = Result length too short to display.
2. select day(”)
In short, a * means the length of the result is too short to display. * is the result of an int, smallint, or tinyint (from) datatype whose length is longer than the length defined for the char or varchar (to) datatype.
“E” would be the result if the (to) datatype was nchar or nvarchar in this scenario. “E” would also be the result if the (from) datatype was money, smallmoney, numeric, decimal, float or real and the (to) datatype was char, varchar, nchar, or nvarchar in the same scenario.
select isnumeric(‘+’) returns 1
Ans 1) varchar(2) is too short to display conversion result , so it is displaying * (Star).
Ans 2) I have found 7-8 ways to get result 1 without using any number in select statement. But, in terms of shortest code, I have found 2 statement with exactly same size.
First answer is inspired by one of your earlier post
Select Count(*)
And Second answer is
Select len(‘A’)
However, 2nd answer is shortest in terms of execution plan also.
Ans of Bonus Question ) NuoDB supports different Operation systems like Windows , MacOS, Linux, Solaris & Joyent SmartDB.
SELECT LEN(‘A’)
SELECT COUNT(*)
SELECT @@ROWCOUNT
SELECT PI()/PI()
SELECT POWER(234234234, 0)
SELECT ASCII(‘B’) – ASCII(‘A’)
SELECT MONTH(‘1 JAN 1990’)
Answer for Puzzle 1:-
SELECT CAST(634 AS VARCHAR(2))
* = Result length too short to display (special case of Truncating and Rounding Results) , SQL used to display * for this case. SQL gives Result as E when you convert money to varchar.
E = Error returned because result length is too short to display.
Answer for Puzzle 2:-
SELECT LEN(‘a’)
Answer for Bonus Question:-
I am not pretty sure for this answer, I believe NuoDB support SmartOS.
Regards,
Girijesh
Puzzle1 : Because the Varchar length(2) is lesser than number of digits(3).
Puzzle 2:
SELECT ASCII(‘B’)-ASCII(‘A’)
select count(*) from dual;
1. length of 634 is 3 so when we convert it into varchar(2) it print *
2. select count (‘abc’)
3. 5 OS supported
Puzzle 2 Solutions : Select COUNT(*)
1.because we are converting 3 digit number to 2 digit varchar,which is an implicit conversion.so due to length of varchar,data will be lost.So in case of lost data,sql server displays * everytime
2.select len(‘m’)
Puzzel-1 :
* = Result length too short to display
Puzzel -2 ans :
select count(*)
Hi Lakshmi Here,
Answer for Puzzle 1: It gives star because when the output length exceeds provided,.A three-character result cannot fit into a two character space.
Answer for Puzzle 2: select len(‘a’)
Puzzle 1 – Answer
The space provided is for 2 character, but since there are 3 characters SQL server uses shorthand and displays *
Puzzle 2 – Answer
SELECT CAST(GETDATE() AS INT)/CAST(GETDATE() AS INT) AS ANSWER
Puzzle 1) Converting tinyint, smallint or int to char or varchar(in the case of the puzzle) will display asterisk because the result is too short to be displayed.
Puzzle 2) SELECT ISDATE(GETDATE());
Sorry missed the bonus question.
Windows, MacOS, Linux, Solaris, Joyent SmartOS
Answer on first puzzle in on the following link
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms187928(v=sql.90)
in “Truncating and Rounding Results” section.
Answer on the second puzzle is
SELECT ASCII(‘b’) – ASCII(‘a’)