# The Bowling Challenge

• Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/theb

• Hi Andy,

I have some questions  :

- Can there be multiple games for 1 UserId in the table ? And if so, should the procedure only list the score of the last game ?

- The inserts in your article all uses FrameNumber = 1. Is it up to the stored prcoedure to correct this, are is it a copy/paste mistake in the article ?

Greetings,

Bert De Haes

• Also:

- can we assume that scores are entered in sequence, ie the RowID can be used to get the order that balls were thrown?

- will the table hold the scores for more than one player?

-----
JL

• I think your sample data in the artice has a typo. Should the last two entries use values of (1,2,4) instead of (1,1,4).

Additionally, how do you intend to represent bonus balls in the tenth. If I bowl a 300, I throw three balls in the tenth frame. Sometimes bowlers refer to these extra balls as the eleventh and twelfth frames.

Would the values be three sets of (1, 10, 10) or would they be (1, 10, 10), (1, 11, 10), and (1, 12, 10)?

I can deal with either methodology. I just need to know what the test data will look like.

• great questions. Ones I han't thought of. I'll ping Andy for some clarification.

• I think the table should also have a GameNumber column so you can track the frames properly for each user if they bowl multiple games on the same day.

The greatest obstacle to transforming the world is that we lack the clarity and imagination to conceive that it could be different. -- Roberto Unger

• Are there any restrictions on which version of SQL can be used to solve this problem?

• What should the output look like if the last frame bowled was a strike and it wasn't the 10th frame? A NULL, maybe?

Frame 5 NULL

Or something else? Standardized output will make it easier for you to programatically test solutions, if that's your plan...

--
whoisactive

• two questions:

1. should we compensate for incomplete games, or assume the games are always going to consist of 10 frames?

2. do you want error checking/handling for invalid scores (scores over 10) being entered?

• Getanothername, wouldn't you expect error checking to be handled by constraints rather than the query to select the data?

--
whoisactive

• Based on where I work, I wouldn't expect anything. Not too sure what kind of constraint you could put onto the Scores table to enforce that the sum of any two balls within a frame isn't greater than 10.

• It's doable, but that might be another challenge (or perhaps, a question of whether a bowling system would really be modelled with this table)... But IMHO for the sake of this exercise we should be able to assume that input data has been constrained and is in a consistent state...

Now I just need Andy Warren to back me up on that

--
whoisactive

• Let me see if I can answer some of those, tell me if I missed any.

- Microsoft TSQL from 6.5, 7.0, or 2000. No Yukon features on this go around, might be fun to tackle again later and see how it affects the solution.

- Could be multiple games, I'd say ideally it calcs for whatever game you point it at. For the puzzle, we'll just assume there is only one user with one game worth of data. If you'd like to add gameid to the table, thats fine.

- I did assume that balls would be scored in the order thrown, kinda like the machine just records # pins, has no concept of ball number. I'll work with you on that too, if you think having the ball number makes it easier to work with, assume that it gets captured too.

- I consider it to be 10 frames, then again, Im only an amateur bowler, if that. If you want to represent as 12 frames, ok too.

- Score the game with however many frames there, though I'd say the # frames shouldn't exceed 10 (or 12 as noted above) and score should never be more than 300 (or less than 0!)

- As far as constraints, we'd probably define some, but here we just treat as a data store and assume the UI/middle tier has submitted valid entries. We're working on algorithm. If a table changes helps, include the revised definition and why.

• andy,

I have my answer ready. How do i send it to you? (I need email id)

-- Amit

"There is no 'patch' for stupidity."