SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The Bowling Challenge

By Andy Warren,

I wrote an article recently (Is all code really code? One question from an interview) that discusses why I think developers look at TSQL as being "different" than "real" code. The article definitely provoked a good discussion, you may find it interesting just for that part alone. So what about you, SQL reader? While I don't argue that you should use the correct tool for the task, are you capable of writing something more than the average insert, update, and delete stored procedures? Is that a challenge?!

Now bear with me for a moment while we cover one other background topic. Recently I conducted a training session with my team to introduce them to Test Driven Development (TDD). If you're not familiar with it, the idea is simple - before you write any code, you write a test that fails, then you make the test pass, and so on. It's not something that seems obviously more productive when you talk about it and in truth I've yet to do it enough to be able to give a fair opinion. If you're at the point in your career where the following makes sense, you might be ready to tackle it - using TDD results in code that is more easily tested. A nice side affect is you get almost 100% code coverage.

For the training session the task was to use TDD to create a set of objects that would score a bowling game. I drew the inspiration from Robert Martin's fine book Agile Software Development, Principles, Patterns, and Practices, but you can see the excerpt in question here. Once we got into it we found that designing tests and objects wasn't the hard part, it was the scoring algorithm. Almost everyone (we pair programmed) had a game object and a collection of frame objects with just minor variations.

If you've bowled before you know that the scoring is a little, well, complicated. If you haven't you should go once! Until you have time, you can review how to score here. No one got their code to work in the time allotted, but we had some fun and got to try something new.

Ready for the challenge?

Create a table using the following script:

create table Scores (
RowID int identity(1,1) not null,
UserID int not null,
DateAdded smalldatetime default GetDate(),
FrameNumber tinyint not null,
NumberOfPins tinyint not null)

For the challenge we won't worry about indexes or security. It'll be up to you to populate the table with data to use for Part 2 as needed.

Part 2 is to write a stored procedure using pure TSQL only (no sp_oa~ procs or custom extended stored procedures) that will calculate the score for each frame that is possible to score. For example, if the bowler has completed four throws hitting 4 pins each time, the results would look something like this:

Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1, 1, 4)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1, 1, 4)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1, 1, 4)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1, 1, 4)

Running our stored proc should return something like the output below. They earned 8 pins in the first frame, 8 pins in the second frame for a cumulative total in the second frame of 16 pins.

Frame 1    8
Frame 2    16

If they have completed all ten frames the tenth frame should contain the total score for the entire game. Here is one hint: a perfect game should have a final score of 300.

Send me your code as a script file - don't post in the discussion area! If you have questions to clarify the requirements or just on TDD, please do post those. The contest will be open for 30 days from the date this is published in the newsletter. Once the contest ends, we'll start testing. We'll pick a winner from among the entries that passes all the tests. Steve Jones will be the final judge. And what do you get for winning? It's more work than you think, so we've got a prize worthy of it

A free printed copy of our Best of SQLServerCentral.com Volume 2
A SQLServerCentral.com shirt!

Want to have a little more fun? Challenge the other DBA's on your team, or see if the developers can do it, even if they have to use code instead of TSQL.


Total article views: 9232 | Views in the last 30 days: 0
Related Articles


Top 10 scores


Bowling Challenge - The Results

Andy started a contest this summer to see who could develop a T-SQL method of keeping a bowling scor...


The Bowling Challenge

Andy ... in your article you populate the bowling score table with the following data: Insert into ...


Bowling Challenge - The Results

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column...




sql puzzles