MySQL DB to validate (test poll) query for points
I have a database where I saved a table with user information, a table with a test (answers and dots), and a table with custom answers for each question. Each question is worth only 1 point and may have one or more correct answers. If all the answers are correct, and the user checks only one, he will receive only 0.25 points. I want to make a request to check the total scores for each user, but I haven't found a good method.
Users table:
+--------+------------+-----------+-------------------+------------+--------+
| userID | first_name | last_name | email | password | points |
+--------+------------+-----------+-------------------+------------+--------+
| 1 | Jhon | Jhonny | jhon@yahoo.com | secretPass | 0 |
| 2 | Dan | Dan | dan@yahoo.com | 1234 | 0 |
| 3 | Dick | Pop | dd@yahoo.com | 123456 | 0 |
| 4 | Mihaela | Micky | mihaela@yahoo.com | pass12 | 0 |
+--------+------------+-----------+-------------------+------------+--------+
Question table: (1 means the answer is good - we can have multiple correct answers)
+------------+--------------------------------------------------+---+---+---+---+
| questionID | question | a | b | c | d |
+------------+--------------------------------------------------+---+---+---+---+
| 1 | which of these are colors? | 1 | 0 | 0 | 1 |
| 2 | which of these are fruits? | 1 | 1 | 1 | 0 |
| 3 | which of these are programming language? | 0 | 1 | 1 | 0 |
| 4 | What is IPv6? | 0 | 0 | 0 | 1 |
+------------+--------------------------------------------------+---+---+---+---+
User Answer Table: (1 means the user selects this answer, but may be inccorect)
+------------+--------+---+---+---+---+
| questionID | userID | a | b | c | d |
+------------+--------+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 0 |
| 2 | 1 | 1 | 1 | 1 | 0 |
| 1 | 3 | 1 | 0 | 1 | 1 |
| 1 | 4 | 1 | 1 | 1 | 0 |
| 3 | 1 | 1 | 0 | 1 | 1 |
| 4 | 1 | 1 | 0 | 1 | 1 |
| 1 | 2 | 1 | 1 | 0 | 0 |
| 2 | 2 | 0 | 1 | 0 | 1 |
| 3 | 2 | 0 | 1 | 1 | 1 |
| 4 | 2 | 1 | 1 | 0 | 1 |
| 2 | 3 | 1 | 0 | 0 | 1 |
| 3 | 3 | 1 | 0 | 1 | 1 |
| 4 | 3 | 1 | 0 | 1 | 1 |
| 2 | 4 | 0 | 1 | 1 | 1 |
| 3 | 4 | 1 | 0 | 0 | 1 |
| 4 | 4 | 0 | 0 | 1 | 0 |
+------------+--------+---+---+---+---+
source to share
try it
SELECT
a.*,
u.name,
q.*,
# (a.a & q.a) + (a.b & q.b) + (a.c & q.c) + (a.d & q.d) userCorrects,
# (a.a + a.b + a.c + a.d) questionCorrects,
((a.a & q.a) + (a.b & q.b) + (a.c & q.c) + (a.d & q.d)) / (a.a + a.b + a.c + a.d) as userGrade
FROM
answer a
INNER JOIN
user u ON a.userID = u.id
INNER JOIN
question q ON a.questionID = q.id
source to share
You join the Q&A and add the correct answers. The formula for each answer is simple: the number of correct answers is divided by 5. You have a GROUP BY user ID and a SUM to get the results for each user.
select u.userid, u.first_name, u.last_name, counted.points
from
(
select
a.userid,
sum(((a.a = q.a) + (a.b = q.b) + (a.c = q.c) + (a.d = q.d) + (a.e = q.e)) / 5)
as points
from question q
join answer a on a.questionid = q.questionid
group by a.userid
) counted
join users u on u.userid = counted.userid;
In MySQL, true = 1
u false = 0
is therefore (a.a = q.a)
equal to 1 when correct and 0 when incorrect.
source to share
Good question...!!!
I think this query will help you with all the possible cases that may arise in this scenario.
SELECT
u.userID, u.first_name, u.last_name, u.email,
SUM(1 - ((!(answer.a = question.a)) + (!(answer.b = question.b)) + (!(answer.c = question.c)) + (!(answer.d = question.d))) * 0.25) AS Score
FROM answer
INNER JOIN
user u ON answer.userID = u.userID
INNER JOIN
question ON answer.questionID = question.questionID
GROUP BY answer.userID
source to share