x

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! :)

http://www.print4tomorrow.co.uk/assets/ExamQuestion_dbImage.jpg
more ▼

asked Jan 13 '12 at 03:54 AM in Default

Amber-Rose gravatar image

Amber-Rose
58 5 6 8

(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

UserID

UserName

Password

Table 2 -Results

UserID

ExamID

ExamQ_ID

ExamQ_S1

ExamQ_S2

ExamQ_S3

ExamQ_S4

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 '12 at 04:19 AM

Amardeep gravatar image

Amardeep
1.3k 84 88 89

(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 '12 at 04:54 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

This way didn't work 100%, whereas the previous answer worked perfectly, thank you for your contribution though! :)
Jan 17 '12 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x673
x22
x7

asked: Jan 13 '12 at 03:54 AM

Seen: 902 times

Last Updated: Jan 13 '12 at 03:54 AM