A test system with three tables, A table holds the right answer, B table holds the answer to the user input, C table to save each question score

Now to go with B table corresponds to A table, if the answer is the same, so it is concluded that the corresponding points, the final score

Could you tell me how to write the SQL?

CodePudding user response:

A table: qid,
the right answerTable B: qid, users in the
C table: qid, score

 select A table. Qid, case when A table. The correct answer=B table. The user input the answer then C table. Score the else 0 end score 
From A table 
Left the join table on A table. B qid=B table. Qid 
Left the join table C on A table. Qid=C table. Qid 

CodePudding user response:

While traversing the exam examinee
The begin
The while loop current examinee answer B table
The begin
If current answer questions the answers correct answer relatively then
Write the examinee answer questions score (full score, part of the score, 0)

Next to the next as the answer
End

Next Next examinee
end

CodePudding user response:

 -- subject ID, 
 the right answerThe Create Table A (ID int, Answer varchar (50)); 
Insert into A select 1, 'A'; 
Insert into A select 2, 'A'; 
Insert into A select 3, 'A'; 

- the subject ID, user ID, user answer 
The Create Table B (ID int, UserID int, Answer varchar (50)); 
Insert into the select 1 B, 1, 'a'; 
Insert into B select 2, 1, 'B'. 
Insert into the select 3 B, 1, 'a'; 

Insert into the select B 1, 2, 'a'; 
Insert into the select 2 B, 2, 'a'; 
Insert into B select 3, 2, 'a'; 

- the subject ID, score 
The Create Table C (ID int, Point int); 
Insert into the select 1 C, 1; 
Insert into the select 2 C, 2; 
Insert into the select 3 C, 3; 

- the user 1 
The select b.u serid, sum (c.p oint) as point 
The from b 
Left the join a on b.I D=Anderson D and b.a nswer=a.a nswer 
Left the join c on Anderson, D=c.i D 
Where b.u serid=1 
Group by b.u serid; 

- the user 2 
The select b.u serid, sum (c.p oint) as point 
The from b 
Left the join a on b.I D=Anderson D and b.a nswer=a.a nswer 
Left the join c on Anderson, D=c.i D 
Where b.u serid=2 
Group by b.u serid; 

CodePudding user response:

Who generally should have the staff table, table, test - test table, test - test table (the standard answer, score), people - test - the answer table

Not given table structure, only their own imagination

CodePudding user response:

2 floor right answer, give points

CodePudding user response:

Create a new item for id review than in the same id and user input the correct answer to answer than the result

CodePudding user response:

This simple, create a temporary table, the user, qid, the correct answer, fill in the answers, scores, and then the total