Quick SQL challenges for mid-experienced+ DBAs (and advanced T-SQLers)

  • (1) Sort rows with year column (smallint) and month column (char(3)) == Jan, Feb, Mar, etc. == correctly, without using CASE or other conditional expressions (the simpler the better).

    (2) Table "emp_training" has (emp_id int, training_id int, status char(1)) where status = F(fail)/P(pass). Each employee (emp_id) may pass a class only once but may fail it multiple times (or 0 or 1 time, of course). Write all necessary DDL to create a unique index -- with both Fs and Ps in it -- to enforce only 0/1 P but 0+ F(s). You may assume the table has a unique column "id" with the IDENTITY() property. (This is more advanced than the other qs.)

    (3) Flip a bit column from 0 to 1 or vice versa without conditional logic. Flip a numeric not-bit column likewise from a value of 0 to 1 or 1 to 0.

    (4) More advanced question: When should you use @@IDENTITY in preference to SCOPE_IDENTITY()?

    NOTE: Some people have already answered.  If you want to do these yourself first, wait to read more comments below until you have your own answers.

    • This topic was modified 1 month, 1 week ago by  ScottPletcher. Reason: Added NOTE
    • This topic was modified 1 month, 1 week ago by  ScottPletcher. Reason: Added data types for columns

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Here's 1, 3, and 4

    --(1)
    select v.sort_mo, input_str.mo
    from (values ('Jan'),('Feb'),('Mar')) input_str(mo)
    cross apply (values (parse(concat(input_str.mo, '-01-1900') as date))) v(sort_mo)
    order by v.sort_mo;

    --(3)
    declare @b bit=1;
    declare @i int=0;
    select 1-@b
    select 1-@i

    --(4)
    Never

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • 1

    SELECT t.*
    FROM MyTable t
    CROSS APPLY (VALUES ('Jan', 1),('Feb', 2),('Mar', 3),('Apr', 4),('May', 5),('Jun', 6),('Jul', 7),('Aug', 8),('Sep', 9),('Oct', 10),('Nov', 11),('Dec', 12)) M(Name,N)
    WHERE M.Name = t.mon
    ORDER BY t.yyyy, M.N

    OR

    SELECT t.*
    FROM MyTable t
    ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')

    2

    CREATE UNIQUE INDEX UX_emp_training_1 ON emp_training(emp_id, training_id)
    WHERE status = 'P'

    3

    select ~col1
    from MyTable

    4  If you want the value of the identity column after a trigger fired

  • Jonathan AC Roberts wrote:

    1

    SELECT t.*
    FROM MyTable t
    CROSS APPLY (VALUES ('Jan', 1),('Feb', 2),('Mar', 3),('Apr', 4),('May', 5),('Jun', 6),('Jul', 7),('Aug', 8),('Sep', 9),('Oct', 10),('Nov', 11),('Dec', 12)) M(Name,N)
    WHERE M.Name = t.mon
    ORDER BY t.yyyy, M.N

    OR

    SELECT t.*
    FROM MyTable t
    ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')

    2

    CREATE UNIQUE INDEX UX_emp_training_1 ON emp_training(emp_id, training_id) WHERE status = 'P'

    3

    select ~col1
    from MyTable

    4  If you want the value of the identity column after a trigger fired

    (1) Good, workable, but it could be simpler

    (2) D'OH, loophole I overlooked, nicely done!  Try creating one index with both P and Fs in it.

    (3) Correct for bit!  Other numerics need another approach.

    (4) Excellent!  (And note that, unless you have system triggers on the tables, you can control which trigger fires last so you can control which @@IDENTITY value comes last from the trigger(s) fired.)

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • ScottPletcher wrote:

    Jonathan AC Roberts wrote:

    1

    SELECT t.*
    FROM MyTable t
    CROSS APPLY (VALUES ('Jan', 1),('Feb', 2),('Mar', 3),('Apr', 4),('May', 5),('Jun', 6),('Jul', 7),('Aug', 8),('Sep', 9),('Oct', 10),('Nov', 11),('Dec', 12)) M(Name,N)
    WHERE M.Name = t.mon
    ORDER BY t.yyyy, M.N

    OR

    SELECT t.*
    FROM MyTable t
    ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')

    2

    CREATE UNIQUE INDEX UX_emp_training_1 ON emp_training(emp_id, training_id) WHERE status = 'P'

    3

    select ~col1
    from MyTable

    4  If you want the value of the identity column after a trigger fired

    (1) Good, workable, but it could be simpler

    (2) D'OH, loophole I overlooked, nicely done!  Try creating one index with both P and Fs in it.

    (3) Correct for bit!  Other numerics need another approach.

    (4) Excellent!  (And note that, unless you have system triggers on the tables, you can control which trigger fires last so you can control which @@IDENTITY value comes last from the trigger(s) fired.)

    (1)

    SELECT t.*
    FROM MyTable t
    ORDER BY CONVERT(date, t.mon + ' 1, ' + CONVERT(varchar, t.yyyy))

    (3) That is the correct logic and result for bitwise "not" when the number is in 2's compliment (e.g. smallint, int, bigint). Does it give the result you expect for tinyint?

    • This reply was modified 1 month, 1 week ago by  Jonathan AC Roberts. Reason: updated order by date
  • Jonathan AC Roberts wrote:

    ScottPletcher wrote:

    Jonathan AC Roberts wrote:

    1

    SELECT t.*
    FROM MyTable t
    CROSS APPLY (VALUES ('Jan', 1),('Feb', 2),('Mar', 3),('Apr', 4),('May', 5),('Jun', 6),('Jul', 7),('Aug', 8),('Sep', 9),('Oct', 10),('Nov', 11),('Dec', 12)) M(Name,N)
    WHERE M.Name = t.mon
    ORDER BY t.yyyy, M.N

    OR

    SELECT t.*
    FROM MyTable t
    ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')

    2

    CREATE UNIQUE INDEX UX_emp_training_1 ON emp_training(emp_id, training_id) WHERE status = 'P'

    3

    select ~col1
    from MyTable

    4  If you want the value of the identity column after a trigger fired

    (1) Good, workable, but it could be simpler

    (2) D'OH, loophole I overlooked, nicely done!  Try creating one index with both P and Fs in it.

    (3) Correct for bit!  Other numerics need another approach.

    (4) Excellent!  (And note that, unless you have system triggers on the tables, you can control which trigger fires last so you can control which @@IDENTITY value comes last from the trigger(s) fired.)

    (1) I think this is quite simple

    SELECT t.*
    FROM MyTable t
    ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')

    Maybe this is a tiny bit simpler?

    SELECT t.*
    FROM MyTable t
    ORDER BY t.yyyy, CONVERT(date, t.mon + ' 1, 2022')

    (3) That is the correct logic and result for bitwise "not" when the number is in 2's compliment (e.g. smallint, int, bigint). Does it give the result you expect for tinyint?

    (1) or maybe this?:

    ORDER BY t.yyyy, CHARINDEX(t.month, 'JanFebMarAprMayJunJulAugSepOctNovDec')

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • ScottPletcher wrote:

    Jonathan AC Roberts wrote:

    ScottPletcher wrote:

    Jonathan AC Roberts wrote:

    1

    SELECT t.*
    FROM MyTable t
    CROSS APPLY (VALUES ('Jan', 1),('Feb', 2),('Mar', 3),('Apr', 4),('May', 5),('Jun', 6),('Jul', 7),('Aug', 8),('Sep', 9),('Oct', 10),('Nov', 11),('Dec', 12)) M(Name,N)
    WHERE M.Name = t.mon
    ORDER BY t.yyyy, M.N

    OR

    SELECT t.*
    FROM MyTable t
    ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')

    2

    CREATE UNIQUE INDEX UX_emp_training_1 ON emp_training(emp_id, training_id) WHERE status = 'P'

    3

    select ~col1
    from MyTable

    4  If you want the value of the identity column after a trigger fired

    (1) Good, workable, but it could be simpler

    (2) D'OH, loophole I overlooked, nicely done!  Try creating one index with both P and Fs in it.

    (3) Correct for bit!  Other numerics need another approach.

    (4) Excellent!  (And note that, unless you have system triggers on the tables, you can control which trigger fires last so you can control which @@IDENTITY value comes last from the trigger(s) fired.)

    (1) I think this is quite simple

    SELECT t.*
    FROM MyTable t
    ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')

    Maybe this is a tiny bit simpler?

    SELECT t.*
    FROM MyTable t
    ORDER BY t.yyyy, CONVERT(date, t.mon + ' 1, 2022')

    (3) That is the correct logic and result for bitwise "not" when the number is in 2's compliment (e.g. smallint, int, bigint). Does it give the result you expect for tinyint?

    (1) or maybe this?:

    ORDER BY t.yyyy, CHARINDEX(t.month, 'JanFebMarAprMayJunJulAugSepOctNovDec')

    (3) Tinyint is probably ok, since it's unsigned, but int definitely does not work.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • ScottPletcher wrote:

    ScottPletcher wrote:

    Jonathan AC Roberts wrote:

    ScottPletcher wrote:

    Jonathan AC Roberts wrote:

    1

    SELECT t.*
    FROM MyTable t
    CROSS APPLY (VALUES ('Jan', 1),('Feb', 2),('Mar', 3),('Apr', 4),('May', 5),('Jun', 6),('Jul', 7),('Aug', 8),('Sep', 9),('Oct', 10),('Nov', 11),('Dec', 12)) M(Name,N)
    WHERE M.Name = t.mon
    ORDER BY t.yyyy, M.N

    OR

    SELECT t.*
    FROM MyTable t
    ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')

    2

    CREATE UNIQUE INDEX UX_emp_training_1 ON emp_training(emp_id, training_id) WHERE status = 'P'

    3

    select ~col1
    from MyTable

    4  If you want the value of the identity column after a trigger fired

    (1) Good, workable, but it could be simpler

    (2) D'OH, loophole I overlooked, nicely done!  Try creating one index with both P and Fs in it.

    (3) Correct for bit!  Other numerics need another approach.

    (4) Excellent!  (And note that, unless you have system triggers on the tables, you can control which trigger fires last so you can control which @@IDENTITY value comes last from the trigger(s) fired.)

    (1) I think this is quite simple

    SELECT t.*
    FROM MyTable t
    ORDER BY t.yyyy, CONVERT(date, '01 ' + t.mon + ' 2022')

    Maybe this is a tiny bit simpler?

    SELECT t.*
    FROM MyTable t
    ORDER BY t.yyyy, CONVERT(date, t.mon + ' 1, 2022')

    (3) That is the correct logic and result for bitwise "not" when the number is in 2's compliment (e.g. smallint, int, bigint). Does it give the result you expect for tinyint?

    (1) or maybe this?:

    ORDER BY t.yyyy, CHARINDEX(t.month, 'JanFebMarAprMayJunJulAugSepOctNovDec')

    (3) Tinyint is probably ok, since it's unsigned, but int definitely does not work.

    (3) It does give the correct result, numerics are stored as Two's complement.

    For example, the smallint number -1 is stored in binary as 1111111111111111 so if you flip the bits you get 0000000000000000.

    What result would you expect?

    Perhaps you could give an example of what you expect e.g. for the smallint number 8192?

  • The expectation, as in the OP, is that 0 becomes 1 and 1 becomes 0.  I didn't really mention -1; typically values are 0/1 for no/yes.

    DECLARE @int int
    DECLARE @smallint smallint

    SET @int = 0
    SET @int = ~@int
    SELECT @int AS int_not_zero
    SET @int = 1
    SET @int = ~@int
    SELECT @int AS int_not_one

    SET @smallint = 0
    SET @smallint = ~@smallint
    SELECT @smallint AS smallint_not_zero
    SET @smallint = 1
    SET @smallint = ~@smallint
    SELECT @smallint AS smallint_not_one

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • ok, that's not what I thought you meant. I thought you meant flip all the bits.

    This works for 0 and 1:

    DECLARE @smallint smallint = 0
    select @smallint ^ 1

     

  • Jonathan AC Roberts wrote:

    ok, that's not what I thought you meant. I thought you meant flip all the bits.

    This works for 0 and 1:

    DECLARE @smallint smallint = 0
    select @smallint ^ 1

    My intention was for:

    ABS(column - 1)

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • What are the datatypes for the two columns in Question #1?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    What are the datatypes for the two columns in Question #1?

    smallint and char(3) (and I have added the data types to the original post).

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • ScottPletcher wrote:

    Jonathan AC Roberts wrote:

    ok, that's not what I thought you meant. I thought you meant flip all the bits.

    This works for 0 and 1:

    DECLARE @smallint smallint = 0
    select @smallint ^ 1

    My intention was for:

    ABS(column - 1)

    I think

    column ^ 1

    Should be faster or

    1 - column

    more brief

  • Jonathan AC Roberts wrote:

    ScottPletcher wrote:

    Jonathan AC Roberts wrote:

    ok, that's not what I thought you meant. I thought you meant flip all the bits.

    This works for 0 and 1:

    DECLARE @smallint smallint = 0
    select @smallint ^ 1

    My intention was for:

    ABS(column - 1)

    I think

    column ^ 1

    Should be faster

    I can't imagine you could measure the difference between the two.

    ^1 would definitely not work with float, so technically it doesn't cover all "not-bit numeric" 😀

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 15 posts - 1 through 15 (of 40 total)

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