April 22, 2012 at 2:49 am
Hi All,
Im curious if this can be done and if it can what would be the best way to achieve it, I have an online exam im building that has a total of 36 questions, i want to store the correct answers to the questions in a table called ExamAnswers
I then want to then pass in the answers the user has given in to a stored procedure and compare the answers they have given with the correct answers from the ExamAnswers table and return a count on how many they got correct,
Is that possible if so how could i do it?
April 22, 2012 at 3:09 am
Please don’t compare questions & answers row-by-row. If I understood the question correctly, you already have a table with correct answers. Create another table (temporary / actual) for user supplied data and join them based on QueNum and AnsVal. It will return you rows for matching (correct answers) rows which you can use for further calculation.
April 22, 2012 at 3:14 am
Someone told me to compare row by row, but i did think that was a bad way of doing it, thats why i asked on here to see if there is a correct way of doing it,
ill do what you have said 😀 thanks
April 22, 2012 at 3:27 am
It was a nice decision on your part. In general, you should avoid row-by-row operations as much as possible.
April 22, 2012 at 5:44 am
Sounds possible, but without seeing the table definitions it's hard to be more precise than that.
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2012 at 7:02 am
Hi Thanks for the response, Below is my Result table this holds all the correct answers for the exam
CREATE TABLE [dbo].[Correct_ExamAnswers](
[Answer1] [varchar](15) NULL,
[Answer2] [varchar](15) NULL,
[Answer3] [varchar](15) NULL,
[Answer4] [varchar](15) NULL,
[Answer5] [varchar](15) NULL,
[Answer6] [varchar](15) NULL,
[Answer7] [varchar](15) NULL,
[Answer8] [varchar](15) NULL,
[Answer9] [varchar](15) NULL,
[Answer10] [varchar](15) NULL,
[Answer11] [varchar](15) NULL,
[Answer12] [varchar](15) NULL,
[Answer13] [varchar](15) NULL,
[Answer14] [varchar](15) NULL,
[Answer15] [varchar](15) NULL,
[Answer16] [varchar](15) NULL,
[Answer17] [varchar](15) NULL,
[Answer18] [varchar](15) NULL,
[Answer19] [varchar](15) NULL,
[Answer20] [varchar](15) NULL,
[Answer21] [varchar](15) NULL,
[Answer22] [varchar](15) NULL,
[Answer23] [varchar](15) NULL,
[Answer24] [varchar](15) NULL,
[Answer25] [varchar](15) NULL,
[Answer26] [varchar](15) NULL,
[Answer27] [varchar](15) NULL,
[Answer28] [varchar](15) NULL,
[Answer29] [varchar](15) NULL,
[Answer30] [varchar](15) NULL,
[Answer31] [varchar](15) NULL,
[Answer32] [varchar](15) NULL,
[Answer33] [varchar](15) NULL,
[Answer34] [varchar](15) NULL,
[Answer35] [varchar](15) NULL,
[Answer36] [varchar](15) NULL
) ON [PRIMARY]
Below is my ExamResults Table this is populated when the user has clicked Finish on the exam so basicallys it inserts the values
CREATE TABLE [dbo].[ExamResults](
[UserID] [int] NOT NULL,
[Question1] [varchar](15) NULL,
[Question2] [varchar](15) NULL,
[Question3] [varchar](15) NULL,
[Question4] [varchar](15) NULL,
[Question5] [varchar](15) NULL,
[Question6] [varchar](15) NULL,
[Question7] [varchar](15) NULL,
[Question8] [varchar](15) NULL,
[Question9] [varchar](15) NULL,
[Question10] [varchar](15) NULL,
[Question11] [varchar](15) NULL,
[Question12] [varchar](15) NULL,
[Question13] [varchar](15) NULL,
[Question14] [varchar](15) NULL,
[Question15] [varchar](15) NULL,
[Question16] [varchar](15) NULL,
[Question17] [varchar](15) NULL,
[Question18] [varchar](15) NULL,
[Question19] [varchar](15) NULL,
[Question20] [varchar](15) NULL,
[Question21] [varchar](15) NULL,
[Question22] [varchar](15) NULL,
[Question23] [varchar](15) NULL,
[Question24] [varchar](15) NULL,
[Question25] [varchar](15) NULL,
[Question26] [varchar](15) NULL,
[Question27] [varchar](15) NULL,
[Question28] [varchar](15) NULL,
[Question29] [varchar](15) NULL,
[Question30] [varchar](15) NULL,
[Question31] [varchar](15) NULL,
[Question32] [varchar](15) NULL,
[Question33] [varchar](15) NULL,
[Question34] [varchar](15) NULL,
[Question35] [varchar](15) NULL,
[Question36] [varchar](15) NULL,
[Result] [int] NULL,
Now comes the difficult part what i want to do is compare Question1 with Answer1 etc all the way down to Question36 with Answer36 and return an int which will be the number of correct answers if someone can help me with a snippet or a starting point i would appreciate it.
Thank you
April 22, 2012 at 7:10 am
Sample data please?
How many rows in Correct_ExamAnswers? How do I tell which exam a user wrote?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2012 at 7:23 am
It’s a bad design. If you need to add another question in the exam will you alter the table (and keep altering as & when required)? Please redesign it & make the question & answers appear as rows rather than columns. Then the set based approach that I already explained will work properly.
April 22, 2012 at 7:26 am
There is only one row in the correct_ExamAnswers with 36 columns is that what you ment?, In the ExamResults Table there is a UserID column which gets populated with the current user logged in which is an Int, i have a UserAccount table which will be used to compare the information which is here
CREATE TABLE [dbo].[UserAccount](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[uPassword] [varchar](50) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[Surname] [varchar](50) NOT NULL,
[Email] [varchar](75) NOT NULL,
[ExamTaken] [bit] NOT NULL,
[AccountType] [int] NULL,
Sample data how do you want it? Question1 = 4 etc? or is there a more easier way of getting this information out of the Correct_ExamResults table without having to do Question1 = 4 Question2 = etc
April 22, 2012 at 7:28 am
Dev (4/22/2012)
It’s a bad design. If you need to add another question in the exam will you alter the table (and keep altering as & when required)? Please redesign it & make the question & answers appear as rows rather than columns. Then the set based approach that I already explained will work properly.
How do i do that? im not strong in SQL
Update sorry ignour the above,
Twigged on what you mean so basically have two columns
Q A
Question1 4
Question2 11
etc
April 22, 2012 at 7:35 am
.Netter (4/22/2012)
There is only one row in the correct_ExamAnswers with 36 columns is that what you ment?
So what happens if a second exam is added? How would you be able to store the correct answers to multiple exams? Surely an exam system can't be expected to only ever have one exam in it?
What happens if another exam needs to be stored and it has 40 questions, not 36? And a third with only 10?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2012 at 7:44 am
This Exam System only has one exam which has 36 questions, the questions never change, this is purely being made to replace the current paper based version we have in place at the moment,
I have Listened to what Dev has said and changed the Correct_ExamResults table to the following
CREATE TABLE [dbo].[Correct_ExamAnswers](
[Question] [varchar](15) NULL,
[Answer] [varchar](15) NULL
) ON [PRIMARY]
April 22, 2012 at 7:50 am
Are you absolutely sure there will never, never, never, never be a second exam? Never have something with a second set of answers?
p.s. You need to change the examresults table the same way if you are going to change either. If you're absolutely certain that the questions will never change, your original design is sufficient.
p.p.s Sample data - see the article I posted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2012 at 7:52 am
Now it’s turning to database design question than T-SQL question. Please don’t mind, if you are not comfortable in doing the designing please seek for help in your organization. It’s not something that somebody can guide you on forum in a day or two.
At high level I believe you need Exam, Question_Set, Question (with options & correct answer), Exam_Schedule, Candidate etc. tables in your database.
April 22, 2012 at 7:56 am
GilaMonster (4/22/2012)
Are you absolutely sure there will never, never, never, never be a second exam? Never have something with a second set of answers?p.s. You need to change the examresults table the same way
p.p.s Sample data - see the article I posted.
There will only be one Exam which is the one in question at the moment, When users come in for an interview they will sit this exam! and there information will be recorded in the ExamResults Tables so
User Score
User1 27
user2 10
user3 21
Ok ill look at your post and post back with sample data
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply