SQL Results? Continued....

Earlier I asked the question....

"I am trying to create a multiple choice test which will be available online soon, hopefully. The problem I am having is working out how I can use SQL to build the test, so the result of the test is added up and shown at the end of the test, the test taker can see their answers (right and wrong) and show the final results as a percentage at the end. I only started my apprenticeship 3 weeks ago so I am still not fully confident on some aspects of SQL, so any help would be much appreciated!"

Beneath is an image of how the database looks already, by using this, could you demonstrate how we would be able to make the database recognise which answers the test taker has answered wrong or right?

I can explain the table in more detail if required!

Thanks! :)


more ▼

asked Jan 13, 2012 at 03:54 AM in Default

avatar image

58 6 6 11

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You might be require few more tables to store user information and transaction information-

Table 1-Users




Table 2 -Results








Now you can create SQL statement to check correct answer with Question Master table.

Something as given below-

SELECT UseID,ExamID From CPD_EXAM_Questions Q INNER JOIN Results R on Q.ExamID=R.ExamID AND Q.UseID=R.USerid AND Q.ExamQ_S1=R.ExamQ_S1 AND Q.ExamQ_S2=R.ExamQ_S2 AND Q.ExamQ_S3=R.ExamQ_S3 AND Q.ExamQ_S4=R.ExamQ_S4

more ▼

answered Jan 13, 2012 at 04:19 AM

avatar image

1.4k 90 93 97

(comments are locked)
10|1200 characters needed characters left

The way you have it laid out, you're going to have a hard time setting the correct answer. Basically this design is what is called denormalized. Normalization is the process of discretely separating storage of one type of information from other types of information. It is harder to do do, especially when you're getting started, but offers a much higher degree of flexibility.

Instead of having everything in big table, if you created the tests in one table, questions in another, then answers in a third. That way you can put all kinds of descriptive stuff about the test, question, and answer, in those discrete tables. Each of these tables needs a primary key (something that identifies the row). Then, to match an answer to a question, create another table. This table will just consist of the two primary keys from the question and answer table. You can also mark the correct answer for a given question in this table (it also means you can reuse answers, a classic multiple choice tactic). You'll then want to create foreign key references between all these tables.

Then, you create another table that records answers. It should show the user primary key and the answer chosen for a question. Then, when it's time to "grade" the test, you just write a query that JOINs between this UserAnswer table and the Answer table to determine which ones they got right.

At least, that's how I'd get it done.

more ▼

answered Jan 13, 2012 at 04:54 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

This way didn't work 100%, whereas the previous answer worked perfectly, thank you for your contribution though! :)

Jan 17, 2012 at 06:25 AM Amber-Rose
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 13, 2012 at 03:54 AM

Seen: 1208 times

Last Updated: Jan 13, 2012 at 03:54 AM

Copyright 2018 Redgate Software. Privacy Policy