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