SQL Query Help - Scoring Multiple Choice Tests

Question

Say I have a Student table, it's got an int ID. I have a fixed set of 10 multiple choice questions with 5 possible answers. I have a normalized answer table that has the question id, the Student.answer (1-5) and the Student.ID

I'm trying to write a single query that will return all scores over a certain pecentage. To this end I wrote a simple UDF that accepts the Student.answers and the correct answer, so it has 20 parameters.

I'm starting to wonder if it's better to denormalize the answer table, bring it into my applcation and let my application do the scoring.

Anyone ever tackle something like this and have insight?

Answer 1

I would probably leave it up to your application to perform the scoring. Check out Maybe Normalizing Isn't Normal by Jeff Atwood.

Answer 2

The architecture you are talking about could become very cumbersome in the long run, and if you need to change the questions it means more changes to the UDF you are using.

I would think you could probably do your analysis in code without necessarily de-normalizing your database. De-normalization could also lend to inflexibility, or at least added expense to update, down the road.

Answer 3

No way, you definitely want to keep it normalized. It's not even that hard of a query.

Basically, you want to left join the students correct answers with the total answers for that question, and do a count. This will give you the percent correct. Do that for each student, and put the minimum percent correct in a where clause.

Answer 4

Denormalization is generally considered a last resort. The problem seems very similar to survey applications, which are very common. Without seeing your data model, it's difficult to propose a solution, but I will say that it is definitely possible. I'm wondering why you need 20 parameters to that function?

A relational set-based solution will be simpler and faster in most cases.

Answer 5

If I understand your schema and question correctly, how about something like this:

select student_name, score
from students
  join (select student_answers.student_id, count(*) as score
        from student_answers, answer_key
        group by student_id
        where student_answers.question_id = answer_key.question_id
          and student_answers.answer = answer_key.answer)
  as student_scores on students.student_id = student_scores.student_id
where score >= 7
order by score, student_name

That should select the students with a score of 7 or more, for example. Just adjust the where clause for your purposes.

Answer 6

This query should be quite easy... assuming you have the correct answer stored in the question table. You do have the correct answer stored in the question table, right?

Answer 7

@Stu

That would be the sensible thing right? Then the answer is "no". :-) The answers will be variable...crazy I know but it's going to be used to pair students in groups, so Student A will be paired with students who answered say 70% the same.

@Dave

I think a small variation of your query will work, I'll try it out when I get home (it's a side project)

Answer 8

@Dave

2 small tweaks and it flies...I had to move the group by clause to after the where and added a limiter and kapow..thanks a ton.