Moodle SQL - table of record answers of a questionnaire per student

Question

Do it to the guys! In the Moodle, in the database, in a quiz activity, where I find the record table of answers given by students in a given questionnaire?

I am doing so, where in the lastname field, is where I put a code for school and in the use Institution field to put school name, is the logical reasoning correct? rsrs:

SELECT 
     SUBSTRING(u.`department`,2,5) AS Turma,
     u.`firstname` AS Aluno,
     u.`lastname` AS Siem,
     u.`institution` AS Escola,
     i.`itemname` AS Simulado,
     g.`finalgrade` AS Nota, 

(select count(u.`firstname`) 

from  `mdl_grade_items` i INNER JOIN `mdl_grade_grades` g ON i.`id` = g.`itemid` INNER JOIN `mdl_user` u ON g.`userid` = u.`id`

  where itemid = 6  and courseid = 2  and lastname = 213 AND finalgrade != "NULL") totalAlunos,

(select avg(finalgrade) 

from  `mdl_grade_items` i INNER JOIN `mdl_grade_grades` g ON i.`id` = g.`itemid` INNER JOIN `mdl_user` u ON g.`userid` = u.`id`

  where itemid = 6  and courseid = 2  and lastname = 213 and department = ".5anoA") notaTurmaA,

(select avg(finalgrade) 

from  `mdl_grade_items` i INNER JOIN `mdl_grade_grades` g ON i.`id` = g.`itemid` INNER JOIN `mdl_user` u ON g.`userid` = u.`id`

  where itemid = 6  and courseid = 2  and lastname = 213 and department = ".5anoB") notaTurmaB,

(select avg(finalgrade)

from  `mdl_grade_items` i INNER JOIN `mdl_grade_grades` g ON i.`id` = g.`itemid` INNER JOIN `mdl_user` u ON g.`userid` = u.`id`

  where itemid = 6  and courseid = 2  and lastname = 213) as NotaGeralEscola

FROM
     `mdl_grade_items` i INNER JOIN `mdl_grade_grades` g ON i.`id` = g.`itemid`
     INNER JOIN `mdl_user` u ON g.`userid` = u.`id`

WHERE
     i.courseid = 2  AND itemtype = "mod"  AND itemmodule = "quiz"  AND finalgrade != "NULL"  AND g.`itemid` = 6


ORDER BY
     u.`department` ASC, finalgrade desc

Answer 1

I think you get something, with a similar query to this, adapt it according to what you need:

SELECT DISTINCT q.id, q.name, q.questiontext,  qw.id, qw.answer,qa.id, qa.rightanswer as resposta_aluno
FROM mdl_quiz_attempts qt 
INNER JOIN mdl_question_attempts qa ON qt.uniqueid=qa.questionusageid 
INNER JOIN mdl_question_answers qw ON qa.questionid=qw.question
INNER JOIN mdl_question q on q.id = qa.questionid
WHERE qt.id=? // ID DA TENTATIVA, NA TABELA quiz_attempts
ORDER BY qw.id ASC;