Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: TSQL knowledge test.

    Yo!

    My company asked me to do a tech interview on a potential contractor for a three month assignment writing SQL Server stored procedures.

    I have some things I can ask him to test his qualifications, such as cross-tab query design, UDF usage, running total queries, select first 'N' queries, Primary Keys vs Clustered Indexes, etc...

    I was curious what questions other people might ask in order to assess a person's skill. Remember that this is SQL programming only, not admin stuff, and I want to ask questions with a range of difficulty from easy to head-cracker.

    Any suggestions?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ask what Error handling is and what type sql can throw, and how they should be checked....what system variables are checked and what they mean

    Ask what cursors are and how/why/when they should be used

    what is show plan and what does it do?

    What is index intersection

    what is a composite key

    what are the pluses and minueses of using an IDENTITY Column

    What are the types of constraints and what are they for

    What methods are there to write data out to a text file

    What methods are there to get data in to a table

    What is denormalization

    what are the normal forms and what do they describe

    Who is Edgar F Codd

    What is a trigger and what do they do

    what do the virtual tables inserted and deleted contain?

    how many angels can dance on the head of a pin?

    How did the Eagles win their last playoff game?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. what is a full outer join? natural join? theta join?

    2. say you had to change the account number from 6 digits to 5 in every table that it occurs in, how would you approach this?

    3. who would you rather go out with, ashanti or beyonce?

    4. assume a many-to-many relationship between employees and departments, what are two different ways to find the employees that aren't in dept X?



    answers:

    1. must be able to define full outer -- natural is a bonus, theta is yet another bonus

    2. write a query on INFORMATION_SCHEMA to find the tables (only half marks if browsing EM), then run queries to see if any numbers are actually larger than 99999, if so, you cannot do it without corrupting data

    3. yes i'm just kidding, you're not allowed to ask this type of question

    4. NOT EXISTS subquery, and LEFT OUTER checking for IS NULL



    want more? feel free to get ideas from my previous 200+ Ask the Expert answers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Brett: nice list

    in answer to your last question, Farverer had a brain cramp and threw a butt-ugly interception
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks Brett and Rudy! Some of your questions stray a bit into database design, which this guy won't be doing, but all in all a good sampling of subskills.


    Edgar F Codd? I had to look that one up. I though it might be some sort of normalization mnemonic like "Roy G. Biv"
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    What is your name on DBForums?

    If you get stuck on a problem, what sources do you consult?

    For the second one, BOL should figure somewhere in the list.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by r937
    1. what is a full outer join? natural join? theta join?

    2. say you had to change the account number from 6 digits to 5 in every table that it occurs in, how would you approach this?

    3. who would you rather go out with, ashanti or beyonce?

    4. assume a many-to-many relationship between employees and departments, what are two different ways to find the employees that aren't in dept X?



    answers:

    1. must be able to define full outer -- natural is a bonus, theta is yet another bonus

    2. write a query on INFORMATION_SCHEMA to find the tables (only half marks if browsing EM), then run queries to see if any numbers are actually larger than 99999, if so, you cannot do it without corrupting data

    3. yes i'm just kidding, you're not allowed to ask this type of question

    4. NOT EXISTS subquery, and LEFT OUTER checking for IS NULL



    want more? feel free to get ideas from my previous 200+ Ask the Expert answers
    Never knew it was called a theta join...

    http://www.microsoft.com/technet/tre...n/relation.asp

    I got a kick when M$ starts speaking of the optimizer making "magic" happen...


    sheeesh


    kinda like the miracle thing...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    Edgar F Codd? I had to look that one up.
    You're kidding, right?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    Some of your questions stray a bit into database design
    Which ones are you refering too?

    And what's the gig?

    I can build that sproc in 2 notes....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    quote:
    --------------------------------------------------------------------------------
    Originally posted by blindman
    Edgar F Codd? I had to look that one up.
    --------------------------------------------------------------------------------



    You're kidding, right?
    Yup, that's a good one!

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    I thought codd was a fish - dang - back to the books ...

    What is sql injection and how do you prevent it ?
    temp table vs table datatype
    indexed views and what are the requirements ?
    Why would you use sp_recompile ?
    If a query were performing poorly what tools would you use - and what would you look for ?
    What is a derived table and how are they used ?
    What is the difference between clustered indexes and non-clustered indexes ?
    Oh and how many clustered indexes can you have per table ?
    How would you instantiate a com object using tsql ?
    What is a self join - and I am not referring to a madonna video.
    When would you use "text in row" ?
    What is DTS/bcp/BULK INSERT ... ?
    Describe the most common tasks you have used in DTS and when would you use them.
    there are three ants on a triangle, one at each corner. at a given moment in time, they all set off for a different corner at random. what is the probability that they don't collide?
    If a bear cra**** in the woods, would you bring toilet paper ?

  12. #12
    Join Date
    Sep 2003
    Posts
    522
    the ambition with wich bm presents himself should be enough for the interview. lack of courtesy (he dared to correct me on that!!!!!) should mean that he is a know-it-all-man (am i wrong??????) do we realy need to bother to answer his plea for help??? isn't a total silence what he is really asking for??? mr "i-know-it-all-and-don't-even-bother-to-think-otherwise"????? i really pitty your wife/girlfriend/boyfriend/...AND LEAVE ALONE YOUR DOG!!!!

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    codd's on first


    What is sql injection and how do you prevent it ?
    - a terrible database illness and you must put a condom on your url variables and text input fields
    temp table vs table datatype
    - i.e. will i need this table tomorrow
    indexed views and what are the requirements ?
    - a single lens reflex camera with a 12 meg flash card
    Why would you use sp_recompile ?
    - that's just it, it would not even occur to me to use it
    If a query were performing poorly what tools would you use - and what would you look for ?
    - EXPLAIN SELECT in mysql, EXPLAIN PLAN, or in microsoft access, Repair Database
    What is a derived table and how are they used ?
    - hey, that's what orthonogality is all about
    What is the difference between clustered indexes and non-clustered indexes ?
    - the trail of raisins is in a specific order
    Oh and how many clustered indexes can you have per table ?
    - as many as you want, up to one
    How would you instantiate a com object using tsql ?
    - i wouldn't, that's the programmer's job, i just test the sp before it goes live
    What is a self join - and I am not referring to a madonna video.
    - involves two tables of which each of them is a virtual copy of the other, except at least one of them's there, sorta like schroedinger's cat
    When would you use "text in row" ?
    - has it gotten out of row again? that darned text
    What is DTS/bcp/BULK INSERT ... ?
    - several ways of accomplishing the same thing, so that you are always thinking of microsoft's generosity in giving you plenty of options that you have to read the fine manual to know which is better
    Describe the most common tasks you have used in DTS and when would you use them.
    - um, moving/copying data? like, when i needed to move it? or copy it? like from excel to sql server? i've done that... oh yeah, and copy the live data to my own database to play with, i've done that too
    there are three ants on a triangle, one at each corner. at a given moment in time, they all set off for a different corner at random. what is the probability that they don't collide?
    - WAIT, I CAN DO THIS ONE... IT'S EASY -- 25%!!
    Last edited by r937; 01-13-04 at 01:22.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Sep 2003
    Posts
    522
    ...yup, and many more :-)

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by ms_sql_dba
    the ambition with wich bm presents himself should be enough for the interview. lack of courtesy (he dared to correct me on that!!!!!) should mean that he is a know-it-all-man (am i wrong??????) do we realy need to bother to answer his plea for help??? isn't a total silence what he is really asking for??? mr "i-know-it-all-and-don't-even-bother-to-think-otherwise"????? i really pitty your wife/girlfriend/boyfriend/...AND LEAVE ALONE YOUR DOG!!!!
    Reminds me of an old Star trek episode...

    "Non Sequitur...does not compute...."

    Where did that come from? Did a post get deleted?


    DTS?

    Yeah...never use it.....

    (Well in production systems anyway)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •