SQL SERVER – 2 T-SQL Puzzles and Win USD 50 worth Amazon Gift Card and 25 Other Prizes

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))

SQL SERVER - 2 T-SQL Puzzles and Win USD 50 worth Amazon Gift Card and 25 Other Prizes puzzlestar

Puzzle 2:

Write the shortest code that produces results as 1 without using any numbers in the select statement.

SQL SERVER - 2 T-SQL Puzzles and Win USD 50 worth Amazon Gift Card and 25 Other Prizes puzzle1

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)

NuoDB
Previous Post
SQL SERVER – Effect of Collation on Resultset – SQL in Sixty Seconds #026 – Video
Next Post
SQL SERVER – Replace a Column Name in Multiple Stored Procedure All Together

Related Posts

365 Comments. Leave new

  • Michael Samteladze
    September 23, 2012 4:32 pm

    Answer for the first puzzle is here:
    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms187928(v=sql.90)

    in “Truncating and Rounding Results” section.

    Answer for the second puzzle is:

    SELECT ASCII(‘b’) – ASCII(‘a’)

    Reply
  • Here are my answers:

    1. When integers are implicitly converted to a varchar data type, if the integer is too large to fit into the varchar datatype field, SQL Server enters ASCII character 42, the asterisk (*).

    2. we have some mathematical functions. I picked cos and exp. All of these statements return 1 as a result:

    select cos($) — dollar
    select exp(£) — British pound

    select cos(â‚¡) — Costa Rican colón
    select exp(â‚¡)

    select cos(₦) — Nigerian naira
    select exp(₦)

    select cos(Â¥) — Japnese Yen
    select exp(Â¥)

    To be honest, you can use almost any currency sign in cos or exp function and it will return 1. The thing is that currency sign is treated like a numeric value:

    eg: select isnumeric(â‚¡) returns 1 and select $ returns 0.00. So at cos($) and exp($) we have value equal to one as these functions represent 1 at a zero point.

    3. BONUS Q:

    5 OS: Windows, MacOS, Linux, Solaris, Joyent SmartOS

    Reply
  • Stefano Tempesta
    September 23, 2012 5:09 pm

    Puzzle 1: Overflow, 634 is longer than 2 characters, so SSMS displays a *

    Puzzle 2: SELECT count(*)

    Bonus Q: Support for Windows, MacOS, Linux, Solaris, Joyent SmartOS

    Reply
  • Prithiviraj Kulasingham
    September 23, 2012 6:28 pm

    Hi Pinal,
    You rock. These are really teasers.

    Reply
  • Vladimir Matushevskiy
    September 23, 2012 7:08 pm

    Puzzle 1: under MSDN, result of conversions from int, smallint, or tinyint to char or varchar can be * which means “Result length too short to display”

    Puzzle 2: SELECT(PI()/PI())

    Bonus Q: Windows, MacOS, Linux, Solaris, Joyent SmartOS

    Reply
  • select 1

    Reply
  • Johan Parlevliet
    September 23, 2012 11:05 pm

    select len(‘A’)

    Reply
  • This is documented in the Truncating and Rounding Results section of CAST and CONVERT in Books Online. The * means ‘Result length too short to display’. This odd behaviour is maintained for backward compatibility with old versions of SQL Server. The more modern types nchar and nvarchar return an error instead:

    SELECT CAST(643 AS nvarchar(1));
    SELECT CAST(45 as nchar(1));

    Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type nvarchar.

    Reply
  • Hi Pinal,

    Ans:

    Puzzal 1
    SELECT CAST(634 AS VARCHAR(2)) Query
    When converting INT to CHAR/VARCHAR If result length is too short to display (ie: VARCHAR(2) in our query) by default it display *.If it is SELECT CAST(63 AS VARCHAR(2)) it display 63.If we add any 1 or more value after 63 and convert it to VARCHAR(2) ie SELECT CAST(634 AS VARCHAR(2)) displays * by default .

    Puzzal 2
    SELECT (COUNT(*))
    –O/P: 1

    Thanks & Regards,
    Abhijeet Desai

    Reply
  • Puzzle 1: SELECT (COUNT(*))
    Puzzle 1: Result is truncated and then CAST displays * for varchar conversion.
    Bonus Q: NUODB Supports 5 different types of OS

    Reply
  • Hi Sir,

    PUZZLE 1
    ——————
    Why following code when executed in SSMS displays result as a * (Star)?

    SELECT CAST(634 AS VARCHAR(2))

    ANSWER : When we try to cast a numeric value to a varchar less than the length of the original numeric value
    by default (*)star sign will be displayed in the result

    Puzzle 2:
    ——————

    Write the shortest code that produces results as 1 without using any numbers in the select statement.

    ANSWER :
    The shortest code that produces results as 1 is SELECT COUNT(*)

    Thanks and Regards,
    P.Anish Shenoy
    Quadwave, Bangalore

    Reply
  • Answer for bonus Q

    NUODB supports Windows, MacOS, SmartOS, Solaris and Linux platforms

    Thanks and Regards,
    P.Anish Shenoy,

    Reply
  • Answer for Bonus Q

    Nuo DB Supports different OS :
    1. MacOS 10.7 or higher
    2. Windows
    (32-bit, 64-bit)
    3. RHEL 5 & 6 (64-bit)
    4. SuSe 10 & 11 (64-bit)
    5. Amazon Basic EC2 (64-bit)
    6.Ubuntu 10 & 11 (64-bit)
    7. Solaris 11 (Intel 64-bit)
    8. Joyent SmartOS (Intel 64 bit)

    Thanks and Regards,
    P.Anish Shenoy
    Quadwave, Bangalore.

    Reply
  • 1.You are casting three digit number with two digit varchar so it will give * ouput for any number greater two digits

    2.select (len(‘sudhir’)/len(‘sudhir’)) it will give 1

    Reply
  • Hi Pinal

    Puzzle 1:
    Why following code when executed in SSMS displays result as a * (Star)?

    Answer:

    When we convert character or binary expressions like char,varchar,nchar,nvarchar 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 varchar are truncated if the size is smaller than the string passed as a parameter in the target data type.

    In the above SELECT statement, the literal 634 is implicitly typed as INT data type. Therefore, when it tries to CAST it to VARCHAR(2) data type which does not have enough space for all the characters, the result will be returned as * . On the other hand, in order to convert the above statement successfully to varchar type, it needs 3 bytes since there are 3 digits in the source needing one byte for each digit to be stored as character type. Since in the statement, target expression type varchar(2) has a length of only 2 bytes and the value of 634 cannot fit in this size (varchar(2)) and the result will be asterisk(*).

    Reference: https://www.microsoft.com/en-us/download/details.aspx?id=51958
    https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017

    Puzzle 2:

    Write the shortest code that produces results as 1 without using any numbers in the select statement.

    Answer:

    Some simple select statement to return 1

    SELECT count(‘x’)
    go
    select len(‘x’)
    go
    select count_big(‘c’)
    go
    select @@Rowcount
    go
    select Rowcount_Big()
    go
    select Isnumeric(‘.’)
    go
    select Getansinull()
    go
    select Pi()/Pi()
    go
    select Isdate(getdate())
    go
    select @@Textsize/@@Textsize
    go
    select @@Datefirst/@@Datefirst
    go
    select @@Lock_Timeout/@@Lock_Timeout
    go
    select @@Max_Connections/@@Max_Connections
    go
    select @@Max_Precision/@@Max_Precision
    go
    select @@Options/@@Options
    go
    select @@Textsize/@@Textsize
    go
    select @@Rowcount/@@Rowcount
    go

    Thanks
    Vijayakumar P
    Kochi,India

    Reply
  • 3.select (len(‘S’))

    Reply
  • Puzzle 1: The * indicates an overflow error caused by truncation

    Puzzle 2: SELECT LEN(‘a’)

    Bonus Q: 5 OS are supported – Windows, MacOS, Linux, Solaris, Joyent SmartOS

    Reply
  • #1: An error is being produced because 634 is 3 characters but we are converting to varchar(2)

    #2: Select CHARINDEX(‘a’,’abc’)

    Reply
  • Puzzle 2:
    Write the shortest code that produces results as 1 without using any numbers in the select statement.

    select len(‘a’)

    Reply
  • Puzzle -2:
    select EXP(”)

    Reply

Leave a Reply