Relatório detalhado das respostas da enquete das questões do tipo única escolha com comando SQL

Relatório detalhado das respostas da enquete das questões do tipo única escolha com comando SQL

por Lino Vaz Moniz -
Número de respostas: 0

Segue o comando SQL que lista todas as respostas da enquete das questões do tipo única escolha 

Versões da enquete que foi testado:  

Filtro apenas na tabela de banco de questão mdl_questionnaire_resp_single 

SELECT id,response_id,question_id,choice_id FROM mdl_questionnaire_resp_single WHERE id > 0

 

Informação simples  com junção na tabela de enquete, tipo de questões, questão e resposta

Testado em Março/2022 no Moodle 3.11.2 Versão do questionnaire: 3.10.1 (Build - 2021080400)

SELECT rs.id,r.id AS mdlresponseid, qs.id AS mdlsurveyid,qs.realm AS surveyrealm,q.id AS mdlquestionnaireid,q.qtype AS questionnaireqtype,q.opendate AS questionnaireopendate,q.closedate AS questionnaireclosedate,q.grade AS questionnairegrade,q.completionsubmit AS questionnairecompletionsubmit, q.course AS mdlcourseid,ct.id AS mdlcoursecategoryid,ct.path AS coursecategorypath,r.submitted AS responsesubmitted,r.complete AS responsecomplete,r.grade AS responsegrade,r.submitted,r.userid AS mdluserid,qq.id AS mdlquestionid,qq.name AS questionname,qq.content AS questioncontent,qqc.id AS mdlquestionchoiceid,qqc.content AS questionchoicecontent,qqc.value AS questionchoicevalue FROM mdl_questionnaire_response r  INNER JOIN mdl_questionnaire q ON r.questionnaireid =q.id INNER JOIN mdl_questionnaire_survey qs ON qs.id=q.sid INNER JOIN mdl_course c ON c.id=q.course INNER JOIN mdl_course_categories ct ON ct.id=c.category  INNER JOIN mdl_questionnaire_resp_single rs ON rs.response_id=r.id INNER JOIN mdl_questionnaire_question qq ON qq.id=rs.question_id INNER JOIN mdl_questionnaire_quest_choice qqc ON qqc.id=rs.choice_id  WHERE rs.id > 0

Versão anterior (Testado em: Maio/2017 -  2017050101 e Dezembro/2013  - 2013122200)

SELECT rs.id,r.id AS mdlresponseid, r.survey_id AS mdlsurveyid,qs.realm AS surveyrealm,t.qid AS mdlquestionnaireid,q.qtype AS questionnaireqtype,q.opendate AS questionnaireopendate,q.closedate AS questionnaireclosedate,q.grade AS questionnairegrade,q.completionsubmit AS questionnairecompletionsubmit, q.course AS mdlcourseid,ct.id AS mdlcoursecategoryid,ct.path AS coursecategorypath,r.submitted AS responsesubmitted,r.complete AS responsecomplete,r.grade AS responsegrade,t.timemodified,t.userid AS mdluserid,qq.id AS mdlquestionid,qq.name AS questionname,qq.content AS questioncontent,qqc.id AS mdlquestionchoiceid,qqc.content AS questionchoicecontent,qqc.value AS questionchoicevalue FROM mdl_questionnaire_response r INNER JOIN mdl_questionnaire_attempts t ON r.id=t.rid INNER JOIN mdl_questionnaire_survey qs ON qs.id=r.survey_id INNER JOIN mdl_questionnaire q ON t.qid=q.id INNER JOIN mdl_course c ON c.id=q.course INNER JOIN mdl_course_categories ct ON ct.id=c.category  INNER JOIN mdl_questionnaire_resp_single rs ON rs.response_id=r.id INNER JOIN mdl_questionnaire_question qq ON qq.id=rs.question_id INNER JOIN mdl_questionnaire_quest_choice qqc ON qqc.id=rs.choice_id  WHERE rs.id > 0

 

Esse comando traz informações simples similar ao anterior, porém não faz join com a tabela questionnaire_survey . É um comando mais eficiente

Testado em Março/2022 no Moodle 3.11.2 Versão do questionnaire: 3.10.1 (Build - 2021080400)

SELECT rs.id,r.id AS mdlresponseid, qs.id AS mdlsurveyid,qs.realm AS surveyrealm,q.id AS mdlquestionnaireid,q.qtype AS questionnaireqtype,q.opendate AS questionnaireopendate,q.closedate AS questionnaireclosedate,q.grade AS questionnairegrade,q.completionsubmit AS questionnairecompletionsubmit, q.course AS mdlcourseid,ct.id AS mdlcoursecategoryid,ct.path AS coursecategorypath,r.submitted AS responsesubmitted,r.complete AS responsecomplete,r.grade AS responsegrade,r.submitted,r.userid AS mdluserid,qq.id AS mdlquestionid,qq.name AS questionname,qq.content AS questioncontent,qqc.id AS mdlquestionchoiceid,qqc.content AS questionchoicecontent,qqc.value AS questionchoicevalue FROM mdl_questionnaire_response r INNER JOIN mdl_questionnaire q ON r.questionnaireid =q.id INNER JOIN mdl_questionnaire_survey qs ON qs.id=q.sid INNER JOIN mdl_course c ON c.id=q.course INNER JOIN mdl_course_categories ct ON ct.id=c.category  INNER JOIN mdl_questionnaire_resp_single rs ON rs.response_id=r.id INNER JOIN mdl_questionnaire_question qq ON qq.id=rs.question_id INNER JOIN mdl_questionnaire_quest_choice qqc ON qqc.id=rs.choice_id  WHERE rs.id > 0

Versão anterior (Testado em: Maio/2017 -  2017050101 e Dezembro/2013  - 2013122200)

SELECT rs.id,r.id AS mdlresponseid, q.sid AS mdlsurveyid, t.qid AS mdlquestionnaireid,q.qtype AS questionnaireqtype,q.opendate AS questionnaireopendate,q.closedate AS questionnaireclosedate,q.grade AS questionnairegrade,q.completionsubmit AS questionnairecompletionsubmit, q.course AS mdlcourseid,r.submitted AS responsesubmitted,r.complete AS responsecomplete,r.grade AS responsegrade,t.timemodified,t.userid AS mdluserid,qq.id AS mdlquestionid,qq.name AS questionname,qq.content AS questioncontent,qqc.id AS mdlquestionchoiceid,qqc.content AS questionchoicecontent,qqc.value AS questionchoicevalue FROM mdl_questionnaire_resp_single rs INNER JOIN mdl_questionnaire_response r ON rs.response_id=r.id JOIN mdl_questionnaire_attempts t ON t.rid=r.id INNER JOIN mdl_questionnaire_quest_choice qqc ON qqc.id=rs.choice_id INNER JOIN mdl_questionnaire_question qq ON qq.id=rs.question_id INNER JOIN mdl_questionnaire q ON t.qid=q.id INNER JOIN mdl_course c ON c.id=q.course WHERE rs.id > 0

Informação detalhada com junção na tabela de enquete, tipo de questões, questão e resposta

Testado em Março/2022 no Moodle 3.11.2 Versão do questionnaire: 3.10.1 (Build - 2021080400)

SELECT rs.id,r.id AS mdlresponseid, qs.id AS mdlsurveyid,qs.realm AS surveyrealm,qs.name AS surveyname,qs.title AS surveytitle,q.id AS mdlquestionnaireid,q.name AS questionnairename, q.intro AS questionnaireintro,q.qtype AS questionnaireqtype,q.opendate AS questionnaireopendate,q.closedate AS questionnaireclosedate,q.resume AS questionnaireresume,q.grade AS questionnairegrade,q.completionsubmit AS questionnairecompletionsubmit, q.course AS mdlcourseid,c.fullname AS coursename,ct.id AS mdlcoursecategoryid,ct.name AS coursecategoryname,ct.path AS coursecategorypath,r.submitted AS responsesubmitted,r.complete AS responsecomplete,r.grade AS responsegrade,r.submitted,r.userid AS mdluserid,u.firstname AS userfirstname,u.lastname AS userlastname,u.email AS useremail,qq.id AS mdlquestionid,qq.name AS questionname,qq.content AS questioncontent,qqc.id AS mdlquestionchoiceid,qqc.content AS questionchoicecontent,qqc.value AS questionchoicevalue FROM mdl_questionnaire_response r INNER JOIN mdl_questionnaire q ON r.questionnaireid =q.id  INNER JOIN mdl_questionnaire_survey qs ON qs.id=q.sid INNER JOIN mdl_course c ON c.id=q.course INNER JOIN mdl_course_categories ct ON ct.id=c.category INNER JOIN mdl_user u ON r.userid=u.id INNER JOIN mdl_questionnaire_resp_single rs ON rs.response_id=r.id INNER JOIN mdl_questionnaire_question qq ON qq.id=rs.question_id INNER JOIN mdl_questionnaire_quest_choice qqc ON qqc.id=rs.choice_id  WHERE rs.id > 0

Versão anterior (Testado em: Maio/2017 -  2017050101 e Dezembro/2013  - 2013122200)


SELECT rs.id,r.id AS mdlresponseid, r.survey_id AS mdlsurveyid,qs.realm AS surveyrealm,qs.name AS surveyname,qs.title AS surveytitle,t.qid AS mdlquestionnaireid,q.name AS questionnairename, q.intro AS questionnaireintro,q.qtype AS questionnaireqtype,q.opendate AS questionnaireopendate,q.closedate AS questionnaireclosedate,q.resume AS questionnaireresume,q.grade AS questionnairegrade,q.completionsubmit AS questionnairecompletionsubmit, q.course AS mdlcourseid,c.fullname AS coursename,ct.id AS mdlcoursecategoryid,ct.name AS coursecategoryname,ct.path AS coursecategorypath,r.submitted AS responsesubmitted,r.complete AS responsecomplete,r.grade AS responsegrade,t.timemodified,t.userid AS mdluserid,u.firstname AS userfirstname,u.lastname AS userlastname,u.email AS useremail,qq.id AS mdlquestionid,qq.name AS questionname,qq.content AS questioncontent,qqc.id AS mdlquestionchoiceid,qqc.content AS questionchoicecontent,qqc.value AS questionchoicevalue FROM mdl_questionnaire_response r INNER JOIN mdl_questionnaire_attempts t ON r.id=t.rid INNER JOIN mdl_questionnaire_survey qs ON qs.id=r.survey_id INNER JOIN mdl_questionnaire q ON t.qid=q.id INNER JOIN mdl_course c ON c.id=q.course INNER JOIN mdl_course_categories ct ON ct.id=c.category INNER JOIN mdl_user u ON t.userid=u.id INNER JOIN mdl_questionnaire_resp_single rs ON rs.response_id=r.id INNER JOIN mdl_questionnaire_question qq ON qq.id=rs.question_id INNER JOIN mdl_questionnaire_quest_choice qqc ON qqc.id=rs.choice_id  WHERE rs.id > 0