The T-SQL Quiz

  • This method is not very elegant but it seems to work quickly.

    select getdate()

    Declare @sql int

    set @sql=1

    select @sql

    while @sql<100

    begin

    set @sql=@sql+1

    begin

    if @sql % 3=0 and @sql % 5=0

    begin

    print 'bizzbuzz'

    end

    else

    if @sql % 3=0 and @sql % 5 not in (0)

    begin

    print 'bizz'

    end

    else

    if @sql % 5=0 and @sql % 3 not in (0)

    begin

    print 'buzz'

    end

    else

    begin

    print @sql

    end

    end

    end

     

    I personally wouldn't agree with using 15 rather than "5 and 3".  If we posed the problem using x and y variables rather than numbers then we would be need to work out "x times  y" and it would probably be as quick to to use a joint number condition. 

    Thanks for the test.  Only been a DBA for 6 months so it made me feel a little surer about my programming.

     

  • I just wish it was only people interviewing for their first jobs. We're getting people with 5-8 years of "experience" that don't know the answer. I'd attribute it to nervousness, but the other questions we ask on the technical phone screen are equally simple and once someone tanks the index question, they usually fail the others. Like, "what's the difference between blocking and deadlocking" or "tell me a couple of the reasons a query might recompile" (and that one includes a freeby - rebooting the server). Those three questions blow more people out of the water than I care to think about. Oh, and, we interview them on the phone. I've heard people typing the question as we asked it. If you can't read the answer correctly, find another career.

     

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • A simple "x times y" wouldn't do the trick in many cases.  You'd have to instead find the least common multiple.

    Example: 10 and 50.  10 * 50 = 500, but 50, 100, 150, 200, etc, are all valid as well.

    Any clever methods for finding the least common multiple in T-SQL?

     

    --
    Adam Machanic
    whoisactive

  • To be fair, a reboot does not trigger a recompile, but rather forces the queries to be re-compiled... A recompile will actually fire the recompile event, whereas a compile (or re-compile as the case may be) will not... subtle difference, I suppose--in both cases the end result is essentially the same--but you might use it to further weed through your candidates

     

    --
    Adam Machanic
    whoisactive

  • All true. Honestly we only allowed it to pass because all the answers we were getting were so damned pathetic. We also allowed people count dropping and recreating the proc as a recompile. Most never even got to adding WITH RECOMPILE to the query let alone all the various & sundry other reasons. Every time I read articles by you, Itzik, Kalen Delany, Ken Henderson, etc., I'm convinced I don't belong in this job. Then I go through 50-60 phone screenings and I get reassurance that I might actually be qualifiied to earn my pay.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • God-awful loops and cursors.  It's all I seem to see these days, and getting people to think about changing them?  Even when you can demonstrate 100%, 200%, 500%, or 1000% improvement, people hate to give up those loops and cursors.

  • Hey it's not running on my SQL 4.2 servers!  LOFL

  • with

    r3 (remainder, format) as (select 3, 'Bizz'),

    r5 (remainder, format) as (select 5, 'Buzz'),

    r15 (remainder, format) as (select 15, 'BizzBuzz'),

    __nums (num) as (select null union all select null),

    _nums (num) as (

    select row_number() over (order by [1].num)

    from

    __nums as [1],

    __nums as [2],

    __nums as [3],

    __nums as [4],

    __nums as [5],

    __nums as [6],

    __nums as [7]

    ),

    nums (num) as (select top (100) cast(num as decimal) from _nums)

    select coalesce(r15.format, r5.format, r3.format, cast(n.num as varchar)) as format

    from nums n

    left outer join r15 on n.num % r15.remainder = 0

    left outer join r5 on n.num % r5.remainder = 0

    left outer join r3 on n.num % r3.remainder = 0

  • I think there are some nicer solutions here than mine, but I went with a dumb cte approach that seems to work decent. I was surprised to see how many folks repeated their mod operations.

    with nums as

    (

    select 1 as i

    union all

    select

    i + 1

    from nums where i < 100

    )

    select

    case i % 3

    when 0 then case i % 5 --%3

    when 0 then 'BizzBuzz' --%3, %5

    else 'Bizz' end --%3, !%5

    else case i % 5 --!%3

    when 0 then 'Buzz' --!%3, %5

    else convert(varchar(3), i) end --!%3, !%5

    end

    as [data]

    from nums

  • I personally wouldn't agree with using 15 rather than "5 and 3".  If we posed the problem using x and y variables rather than numbers then we would be need to work out "x times  y" and it would probably be as quick to to use a joint number condition.

    Well, so long as you're going to change the rules, then let's change our code to adapt to the rules:

    /* Greatest common demominator */

    CREATE FUNCTION dbo.GCD(@a INT, @b-2 INT)

    RETURNS INT

    AS

    BEGIN

        RETURN CASE WHEN @b-2 <> 0 THEN dbo.GCD(@b, @a % @b-2)

     ELSE @a END

    END

    GO

    /* Least common multiple */

    CREATE FUNCTION dbo.LCM(@a INT, @b-2 INT)

    RETURNS INT

    AS

    BEGIN

        RETURN CASE WHEN @a = 0 OR @b-2 = 0 THEN NULL

                  ELSE (@a/ dbo.GCD(@a, @b-2)) * @b-2 END;

    END

    GO

    DECLARE @i INT;

    SET @i = dbo.LCM(@x, @y);

    SELECT CASE WHEN n % @i = 0 THEN 'BizzBuzz'

        WHEN n % @x = 0 THEN 'Bizz'

        WHEN n % @y = 0 THEN 'Buzz'

        END

    .../* etc. */

    Every time n % @x = 0 you also have to perform the check to make sure n % @y = 0.  And the second check will only evaluate to True once every @y times.  Using @x = 3 and @y = 5 and 100 numbers from the actual problem, that's something like 27 wasted comparisons of n % 5 = 0.  For 1,000,000 rows that's a little more than 266,600 wasted comparisons.

    Multiplying it out as shown results in no wasted comparisons, but you do have the overhead of calculating the least common multiple (per Adam Machanic's recommendation) and one variable assignment.

  • I was just gearing the solution to the problem at hand, with the parameters given   The least common multiple is an interesting problem though.

    /* Greatest common demominator */

    CREATE FUNCTION dbo.GCD(@a INT, @b-2 INT)

    RETURNS INT

    AS

    BEGIN

        RETURN CASE WHEN @b-2 <> 0 THEN dbo.GCD(@b, @a % @b-2)

     ELSE @a END

    END

    GO

    /* Least common multiple */

    CREATE FUNCTION dbo.LCM(@a INT, @b-2 INT)

    RETURNS INT

    AS

    BEGIN

        RETURN CASE WHEN @a = 0 OR @b-2 = 0 THEN NULL

                  ELSE (@a/ dbo.GCD(@a, @b-2)) * @b-2 END;

    END

    GO

  • Hmm, I vaguely remember learning how to do this in high school, and Wikipedia isn't helping.

    http://en.wikipedia.org/wiki/Least_common_multiple

    lcm(a,b) = (a * b) / gcd(a,b)

    http://en.wikipedia.org/wiki/Greatest_common_divisor

    gcd(a,b) = (a * b) / lcm(a,b)

    ... so it's a recursive problem?

    --
    Adam Machanic
    whoisactive

  • Yes, I just posted the code to calculate LCM and GCD   So I suppose the code needs to change to calculate the LCM and print 'BizzBuzz' for each occurrence of the LCM; which for 5 and 3 is 15.

  • Ooo.  I sure hope you used a Perl script to write that one out.

    Roger L Reid

  • declare @a int, @b int
    select @a = 3, @b = 5
    ;with gcd (a, b) as ( select @a, @b union all select b, a % b from gcd where b  0 )
    select @a / gcd.a * @b as lcd from gcd where b = 0

Viewing 15 posts - 61 through 75 (of 309 total)

You must be logged in to reply to this topic. Login to reply