Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Exercises in t-SQL Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, November 28, 2012 9:41 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, April 07, 2014 7:35 AM Points: 1,172, Visits: 2,413
 Jeff Moden (11/28/2012)wolfkillj (11/28/2012)Jeff Moden (11/27/2012)CELKO (11/27/2012) Create a million row FizzBuzz table. FizzBuzz isn't a comment. It's the name of a very common problem based on a child's game, is frequently given on interviews, .. I had to do a FizzBuzz on an interview two years ago! I was appalled to be told that a lot of people failed it. The trick that even the experienced guys miss is MOD(n, 15) rather than just MOD(n, 3) and MOD(n, 5) in the CASE expression. The other exercise would be to teach people how to use Books Online and Google.Amen.Heh... too funny. I've given it as an interview question a couple of times just to see what would happen. DISMAL failure! I'd be happy if someone could actually get it right even using a WHILE loop never mind something like a cteTALLY or a cross join with ROW_NUMBER. Even the ones that think of the MOD(n,15) (n%15 in SQL Server) always put it last in the CASE statement simply because it comes last in the problem description.I'm not sure I'd think less of a coder who didn't know the "n mod 15" trick - that's a nugget of math trivia, not a fundamental programming construct. If the coder attempting to solve the "fizz-buzz" puzzle (a) knows how to generate a set of sequential integers in T-SQL without a loop construct, (b) understands that the "fizz-buzz-fizzbuzz" requirement is most easily and efficiently satisfied in T-SQL with a CASE expression, (c) recognizes that the "fizzbuzz" condition must come first in the CASE expression, and (d) codes the "fizzbuzz" condition to produce the correct result (either "WHEN n % 3 = 0 AND n % 5 = 0" or "WHEN n % 15 = 0"), he or she has passed with flying colors, in my opinion! In fact, I wouldn't fault a coder for getting the order of conditions in the CASE expression wrong at first as long as he or she identifies the cause of the incorrect result for multiples of 15 and corrects the error on the second try.I consider knowing modulus fuctions to be required basic mathematical knowledge for all programmers but especially for anyone working with a database. It goes hand-in-hand with such things as knowing how to do simple integer math for grouping and I certainly don't consider it to be a trivial math function or a trick question. I agree that all of what you've stated is equally important but the knowledge of basic math functions in any programmig language is not a trivial requirement.Still, I agree... missing one part of one question in the interview process does't guarantee a "No" by any means especially since most of the people I've asked do, in fact, get the modulus portion right and all of the rest of it wrong. I didn't mean that I wouldn't expect a coder to know the modulo function, but that I wouldn't expect a coder to know that all numbers that are divisible by both 3 and 5 are also divisible by 15. That's the "math trivia", and ignorance of that fact doesn't reflect negatively on one's ability as a programmer. On the other hand, a fizz-buzz solution that doesn't use the modulo function would raise some serious doubts about the coder's ability. Jason WolfkillBlog: SQLSouthTwitter: @SQLSouth
Post #1390268
 Posted Thursday, November 29, 2012 3:53 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, April 03, 2014 10:10 AM Points: 2,792, Visits: 4,874
 Sorry for my a bit unorthodox view on " FizzBuzz" problem in T-SQL, but couldn't keep myself quiet I don't think it's a good T-SQL question at all..Create a million row FizzBuzz table. FizzBuzz isn't a comment. It's the name of a very common problem based on a child's game, is frequently given on interviews, and is actually a wonderful teaching tool to show people the difference between RBAR, Hidden RBAR, and Set Based methods that use "Pseudo Cursors". Have them do it with explicit loops, with a recursive CTE, without either of those at least 3 different ways, and then measure and discuss the resource usage and performance differences. So far as I'm concerned, it's one of the more important concepts in SQL ServerAgree on this, but I wouldn't ask specifically for FizzBuzz. Probably question like "Could you show few ways to produce a list of N [sequential] numbers in T-SQL" would do good enough for finding if the man can think in "SETs" and aware of T-SQL features such as CTE's, windowed functions and even IDENTITY function which can produce it together with creating a table while used in SELECT INTO.If I want to check knowledge of CASE in T-SQL, I would prefer to ask the question where it's need to be used in ORDER BY...I had to do a FizzBuzz on an interview two years ago! I was appalled to be told that a lot of people failed it. The trick that even the experienced guys miss is MOD(n, 15) rather than just MOD(n, 3) and MOD(n, 5) in the CASE expression. Actually, in my experience, there are more people who miss data conversion on number to varchar required in the ELSE part of this CASE...If the coder attempting to solve the "fizz-buzz" puzzle (a) knows how to generate a set of sequential integers in T-SQL without a loop construct, (b) understands that the "fizz-buzz-fizzbuzz" requirement is most easily and efficiently satisfied in T-SQL with a CASE expression, (c) recognizes that the "fizzbuzz" condition must come first in the CASE expression, and (d) codes the "fizzbuzz" condition to produce the correct result (either "WHEN n % 3 = 0 AND n % 5 = 0" or "WHEN n % 15 = 0"), he or she has passed with flying colors, in my opinion! I cannot say that knowing MOD defines you as person who knows the basic Math functions in T-SQL. What makes MOD more basic than ABS or POWER for example?I do agree that Fizz-Buzz question is a good one for other programming languages, but T_SQL...Now, the final one: If you really want to make an impression (good or bad) on the interview, when asked such question, then ask interviewer if he want to see common solution using MOD or kind of "native" T-SQL which doesn't need one!When I've done it myself once, I caused a bit of surprised. Technical interviewer (I think at least 10 years younger than me), in front of his manager, instead of simply asking me to show how, replied back: "It's impossible, how will you check the number can be divided by 3 or 5?" And when I said, that it's not really required in T-SQL, I've been asked to explain myself by the manager. In reality it can be done by few ways. Here I will post just one of them:` ;WITH S1 AS (SELECT 1 N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) A(N) ,(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) B(N)) ,Set100 AS ( SELECT ROW_NUMBER() OVER (ORDER BY N) N, 'Fizz' F,'Buzz' B FROM S1 ) SELECT COALESCE(S15.F + S15.B, S5.B, S3.F, CAST(S1.N AS VARCHAR)) FROM Set100 S1 LEFT JOIN Set100 S3 ON S3.N * 3 = S1.N LEFT JOIN Set100 S5 ON S5.N * 5 = S1.N LEFT JOIN Set100 S15 ON S15.N * 15 = S1.N ORDER BY S1.N`No MOD, No CASE WHEN, just very basic T-SQL joins + COALESCE from ANSI beloved by J.C. ... I'm no gonna do discuss if it's slower than CASE WHEN with MOD, that was done just to show, if you really can think in "SET's" _____________________________________________"The only true wisdom is in knowing you know nothing""O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help
Post #1390407
 Posted Thursday, November 29, 2012 8:28 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, April 07, 2014 7:35 AM Points: 1,172, Visits: 2,413
 Eugene Elutin (11/29/2012)Sorry for my a bit unorthodox view on " FizzBuzz" problem in T-SQL, but couldn't keep myself quiet I don't think it's a good T-SQL question at all..Create a million row FizzBuzz table. FizzBuzz isn't a comment. It's the name of a very common problem based on a child's game, is frequently given on interviews, and is actually a wonderful teaching tool to show people the difference between RBAR, Hidden RBAR, and Set Based methods that use "Pseudo Cursors". Have them do it with explicit loops, with a recursive CTE, without either of those at least 3 different ways, and then measure and discuss the resource usage and performance differences. So far as I'm concerned, it's one of the more important concepts in SQL ServerAgree on this, but I wouldn't ask specifically for FizzBuzz. Probably question like "Could you show few ways to produce a list of N [sequential] numbers in T-SQL" would do good enough for finding if the man can think in "SETs" and aware of T-SQL features such as CTE's, windowed functions and even IDENTITY function which can produce it together with creating a table while used in SELECT INTO.If I want to check knowledge of CASE in T-SQL, I would prefer to ask the question where it's need to be used in ORDER BY...I had to do a FizzBuzz on an interview two years ago! I was appalled to be told that a lot of people failed it. The trick that even the experienced guys miss is MOD(n, 15) rather than just MOD(n, 3) and MOD(n, 5) in the CASE expression. Actually, in my experience, there are more people who miss data conversion on number to varchar required in the ELSE part of this CASE...If the coder attempting to solve the "fizz-buzz" puzzle (a) knows how to generate a set of sequential integers in T-SQL without a loop construct, (b) understands that the "fizz-buzz-fizzbuzz" requirement is most easily and efficiently satisfied in T-SQL with a CASE expression, (c) recognizes that the "fizzbuzz" condition must come first in the CASE expression, and (d) codes the "fizzbuzz" condition to produce the correct result (either "WHEN n % 3 = 0 AND n % 5 = 0" or "WHEN n % 15 = 0"), he or she has passed with flying colors, in my opinion! I cannot say that knowing MOD defines you as person who knows the basic Math functions in T-SQL. What makes MOD more basic than ABS or POWER for example?I do agree that Fizz-Buzz question is a good one for other programming languages, but T_SQL...Now, the final one: If you really want to make an impression (good or bad) on the interview, when asked such question, then ask interviewer if he want to see common solution using MOD or kind of "native" T-SQL which doesn't need one!When I've done it myself once, I caused a bit of surprised. Technical interviewer (I think at least 10 years younger than me), in front of his manager, instead of simply asking me to show how, replied back: "It's impossible, how will you check the number can be divided by 3 or 5?" And when I said, that it's not really required in T-SQL, I've been asked to explain myself by the manager. In reality it can be done by few ways. Here I will post just one of them:` ;WITH S1 AS (SELECT 1 N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) A(N) ,(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) B(N)) ,Set100 AS ( SELECT ROW_NUMBER() OVER (ORDER BY N) N, 'Fizz' F,'Buzz' B FROM S1 ) SELECT COALESCE(S15.F + S15.B, S5.B, S3.F, CAST(S1.N AS VARCHAR)) FROM Set100 S1 LEFT JOIN Set100 S3 ON S3.N * 3 = S1.N LEFT JOIN Set100 S5 ON S5.N * 5 = S1.N LEFT JOIN Set100 S15 ON S15.N * 15 = S1.N ORDER BY S1.N`No MOD, No CASE WHEN, just very basic T-SQL joins + COALESCE from ANSI beloved by J.C. ... I'm no gonna do discuss if it's slower than CASE WHEN with MOD, that was done just to show, if you really can think in "SET's"I would be impressed if an interviewee asked me if I wanted a fizzbuzz solution with or without the modulo operator. Eugene's solution does demonstrate a solid grasp of fundamental T-SQL concepts, but does require that the coder know that all numbers that are evenly divisible by 3 and 5 are also divisible by 15. Awareness or ignorance of that fact wouldn't tell me much about a coder's ability as a programmer. I would ask an interviewee who presented that solution why he or she did not use the modulo operator, though. It's a bit like asking a candidate for a carpentry job why he used a brace and bit to drill a hole when a power drill was available. There could be good reasons for using a basic tool over a more advanced tool, and I'd expect a qualified candidate to be able to articulate those reasons. The real problem with the fizzbuzz puzzle for evaluating a programmer's ability is that it is only a puzzle. Once someone has seen it solved, the puzzle presents no challenge other than recalling the solution. One need not even understand how the solution works as long as one can re-create the code. A more useful tool for determining whether a coder is at least minimally competent with T-SQL would be a problem more like a real-world task. I might provide a candidate with a simple data model like this:Employees table EmployeeID int PKLastName varchar(60)FirstName varchar(60)Computers tableComputerID int PKSerialNumber varchar(20)ModelNumber varchar(20)WarrantyEndDate dateEmployeeComputerAssignments tableEmployeeID int FK references Employees.EmployeeIDComputerID int FK references Computers.ComputerIDand ask him or her to write out (yes, on paper) a query that will return the names of all employees with computers that go out of warranty in December 2012, including the employee's ID and name and the computer's serial number, model number, and warranty end date, listed in order of warranty end date and employee last name, first name. Writing the query on paper requires the candidate to demonstrate the conceptual knowledge of the SELECT-FROM-JOIN-WHERE-ORDER BY construct without fumbling through it by trial-and-error at the keyboard (smart people with weak coding skills can usually manage to cobble something like this together if they can code-execute-evaluate enough times). Come to think of it, this is the kind of exercise that the OP seemed to be looking for!If the candidate provided an acceptable response, I might up the ante just a bit by asking for the same solution with an added column called RemoveFromServiceDate set to 5 days earlier than the WarrantyEndDate. That would demonstrate the candidate's awareness of basic date manipulation techniques. If the candidate succeeds with that, I might add a further challenge by specifying that when the RemoveFromServiceDate would otherwise fall on a Saturday or Sunday, it should be changed to the preceding Friday. I would expect to see a CASE statement and some use of the DATEPART or DATENAME functions. However, at this stage, I might also be satisfied if the candidate does not actually solve the problem on the spot but at least makes some intelligent response, e.g, asking whether a calendar table would be available or explaining that she knows that there are T-SQL functions that provide the required functionality but that she would need to look them up on Books Online to remember their names and syntax. Hmm, I'm actually off to participate in a panel interview of a candidate in a few minutes! Bwaa-ha-ha-ha! Jason WolfkillBlog: SQLSouthTwitter: @SQLSouth
Post #1390604
 Posted Thursday, November 29, 2012 8:40 AM
 SSCommitted Group: General Forum Members Last Login: Tuesday, January 15, 2013 11:11 AM Points: 1,945, Visits: 2,782
 Hmm, I'm actually off to participate in a panel interview of a candidate in a few minutes! Bwaa-ha-ha-ha!I just sit there in my black three piece suit, tenting my fingers like Montgomery Burns from The Simpsons. When it is time for me to ask a questions, I use "Does your religion require you to bury your dead?" It does not matter how they answer, I just shudder and keep tenting my fingers. It lets me know how they work under pressure Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL
Post #1390615
 Posted Thursday, November 29, 2012 8:55 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, April 03, 2014 10:10 AM Points: 2,792, Visits: 4,874
 CELKO (11/29/2012) Hmm, I'm actually off to participate in a panel interview of a candidate in a few minutes! Bwaa-ha-ha-ha!I just sit there in my black three piece suit, tenting my fingers like Montgomery Burns from The Simpsons. When it is time for me to ask a questions, I use "Does your religion require you to bury your dead?" It does not matter how they answer, I just shudder and keep tenting my fingers. It lets me know how they work under pressure In some countries you could be taken to court for such questions during interview, as it can constitute an attempt to discriminate on the religion grounds...I believe it's not a case in a "Sunny Alabama".Mazel tov! Allahu Akbar, Akbar indeed! Alleluia! ... _____________________________________________"The only true wisdom is in knowing you know nothing""O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help
Post #1390628
 Posted Thursday, November 29, 2012 9:05 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, April 03, 2014 10:10 AM Points: 2,792, Visits: 4,874
 I would ask an interviewee who presented that solution why he or she did not use the modulo operator, though. It's a bit like asking a candidate for a carpentry job why he used a brace and bit to drill a hole when a power drill was available. There could be good reasons for using a basic tool over a more advanced tool, and I'd expect a qualified candidate to be able to articulate those reasons. Actually, as I've said from the start, it's kind of stunt offered together with a cross-language common solution based on modulo. Saying above, even for a carpentry job, showing the ability of using brace and bit to drill a hole instead of power drill, would tell me that the man is a real one. Every idiot can use a power drill, it's a DIY tool at the end... _____________________________________________"The only true wisdom is in knowing you know nothing""O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help
Post #1390642
 Posted Thursday, November 29, 2012 9:25 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, April 03, 2014 10:10 AM Points: 2,792, Visits: 4,874
Post #1390661
 Posted Thursday, November 29, 2012 12:39 PM
 SSC-Addicted Group: General Forum Members Last Login: Sunday, September 29, 2013 1:24 AM Points: 429, Visits: 1,721
 Eugene Elutin (11/29/2012)Google for "Fizz Buzz T-SQL"BTW, a carpenter doesn't work without a toolbox and neither do I. If I was looking for a serious candidate I'd expect that he could not only Google but that he'd pull out his thumbdrive and grab an answer from there. My toolbox has hundreds of stored procedures, functions, code snippets (such as the DelimitedSplit8K function) and I can almost always find something I've already done that's close to what I need to do now. Why re-invent the wheel?Also, I'd be at a severe disadvantage without my RedGate tools such as SQL Prompt, SQL Search, SQL Snippets manager, etc. For example, if I want to create a new Tally table I just enter 'ctally' in a query window and bingo I have the entire code. Why do I have to cram my head full of trivia? Geez, I guess when it comes to SQL it's like the days when students weren't allowed to use calculators on tests.
Post #1390789
 Posted Thursday, November 29, 2012 12:56 PM
 SSCertifiable Group: General Forum Members Last Login: Thursday, April 17, 2014 3:16 PM Points: 5,986, Visits: 6,931