Moodle SQL - record table responses from one questionnaire per student

0

Faaaaala galera! In moodle, in the database, in a quiz activity, where do I find the record table of answers given by students in a given questionnaire?

I'm doing so, where in the field lastname, is where I put a code for school and in the field institution use to put name of school, is 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
    
asked by anonymous 01.11.2017 / 13:34

1 answer

1

I think you get something, with a query similar 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;
    
23.11.2017 / 18:25