Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Exercises in t-SQL Expand / Collapse
Author
Message
Posted Wednesday, November 28, 2012 9:41 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:21 PM
Points: 1,210, Visits: 2,512
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 Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1390268
Posted Thursday, November 29, 2012 3:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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 Server

Agree 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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:21 PM
Points: 1,210, Visits: 2,512
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 Server

Agree 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 PK
LastName varchar(60)
FirstName varchar(60)

Computers table
ComputerID int PK
SerialNumber varchar(20)
ModelNumber varchar(20)
WarrantyEndDate date

EmployeeComputerAssignments table
EmployeeID int FK references Employees.EmployeeID
ComputerID int FK references Computers.ComputerID

and 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 Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1390604
Posted Thursday, November 29, 2012 8:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
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 Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1390615
Posted Thursday, November 29, 2012 8:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062

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


May be, if you are hiring graduate...
Some prefer "smart people" over "strong coding skills", simply because if you smart, you can learn...
Also, from the business prospective, if I ask someone, "how to solve Fizz Buzz puzzle in T-SQL", I would expect first choice from real expert as:
Google for "Fizz Buzz T-SQL".
It will, most likely, in real life, help to solve the common problem a lot quicker, meaning saving me time and money!
In my humble opinion, that small thing constitutes difference between Professional and Expert. Yep, good professional can write the right code in 5 min. However, expert can spot common problems and find ready-to-use solution in a matter of seconds (it's all to do with inventing bicycles)...
Then I could discuss other, more SQL relevant things and even insist he will scramble something on paper, still putting candidate at front of SSMS and ask him to use it would give me better idea of his skills and experience with SQL Server...
Even simple "can you tell me what columns "this" table have and what their datatypes are?" can give you good insight of how experienced the candidate is. Some will use mice and expand the table in Object View (would be good to ask for some table which is located at the middle of the 1000 odd list), some will use sp_help, some will press couple of buttons and get what you need...



_____________________________________________
"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 #1390661
Posted Thursday, November 29, 2012 12:39 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 6,142, Visits: 7,195
Steven Willis (11/29/2012)

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.

 


You'd be amazed at the number of shops I've been at that have locked out the USB ports. Throw in websense and 75% of your tools are now gone because they're on locked out blog sites until you go through the release process for each one. Independent of each other for evaluation.

Most sites I've worked at also do not include the Redgate (or any other) tools. They're not required really, just helpful. Some shops simply won't spring for 'em, they feel the 100k/SQL Server is enough, thankyouverymuch.

Additionally, I don't want a script kiddy equivalent loose in my SoR databases. Not saying you are, but someone who absolutely NEEDS the net to find equivalent scripts is not someone I trust to be able to decipher complex algorithms when working in an existing codebase. In my personal opinion you should be able to be dropped into an isolated server room with a copy of BoL & MSDN, a workstation with some office software, and a server, and get done whatever work needs to be done. Anything else is just a timesaver.

Do I expect a candidate to know how to find his own answers, use google, search sites, forums, and the rest? Yes, yes I do. They'd waste too much time otherwise puzzling out the answer. However, I *do* want someone who knows the tools so that, if push came to shove, they COULD puzzle it out, and I need to find that out during the interview as well.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1390800
Posted Thursday, November 29, 2012 1:39 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
The key in my suggestion of Google wasn't really - find everything on internet, but:
expert can spot common problems and find ready-to-use solution in a matter of seconds

That would count as important part of problem-solving skills.
You should also note, that even with access to internet not everyone will be able to solve the problem. You will really need to know what to "google" for...


_____________________________________________
"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 #1390827
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse