General developer forum

SQL Report with Quiz Questions and Answers for all Courses

 
Picture of Juan F
SQL Report with Quiz Questions and Answers for all Courses
 

Greetings folks,

I have been asked by my Dean to come up with a report that targets a specific quiz for all of our Biology courses.  The quiz name is "BIO 208 Post Test Assessment".  The quiz has 61 questions and my Dean would like to see a summary of the 61 questions and how many students get them right/wrong.  Does anyone have any suggestions or places I can start?

When I'm in the individual course quiz I can click the Download button to download the data for the individual course, however, we are needing this for over 200 courses and it will need to be run every 2 months.

Thanks for any help you all can provide.

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: SQL Report with Quiz Questions and Answers for all Courses
 
Average of ratings: -
Picture of Juan F
Re: SQL Report with Quiz Questions and Answers for all Courses
 

Tim, thank you.  This gets me started with the following:

SELECT
    q.course,
    q.name,
    quiza.userid,
    quiza.quiz,
    quiza.id AS quizattemptid,
    quiza.attempt,
    quiza.sumgrades,
    qu.preferredbehaviour,
    qa.slot,
    qa.behaviour,
    qa.questionid,
    qa.questionsummary,
    qa.rightanswer,
    qa.responsesummary

FROM mdl_quiz_attempts quiza
JOIN mdl_quiz q ON q.id=quiza.quiz
JOIN mdl_question_usages qu ON qu.id = quiza.uniqueid
JOIN mdl_question_attempts qa ON qa.questionusageid = qu.id

WHERE q.name = "BIO 208 Post Test Assessment"

ORDER BY quiza.userid, quiza.attempt, qa.slot

I noticed that qa.questionsummary combines the question with the answers.  Any chance I can break this apart or find it broken apart in another table?

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: SQL Report with Quiz Questions and Answers for all Courses
Group Core developersGroup Documentation writersGroup Particularly helpful MoodlersGroup Plugin developers

JOIN mdl_question question ON question.id = qa.questionid and use question.questiontext. Where the answers are stored depends on the question type, but for multiple choice they are in question_answers.

 
Average of ratings: Useful (1)
Picture of Juan F
Re: SQL Report with Quiz Questions and Answers for all Courses
 

Tim, thank you for this...luckily, the questions are all MC.  Here's what I've come up with that gets me what I need:

SELECT
    concat( u.firstname, " ", u.lastname ) AS "Student Name",
    u.id,
    quiza.userid,
    q.course,
    q.name,
    quiza.attempt,
    qa.slot,
    que.questiontext AS 'Question',
    qa.rightanswer AS 'Correct Answer',
    qa.responsesummary AS 'Student Answer'

FROM mdl_quiz_attempts quiza
JOIN mdl_quiz q ON q.id=quiza.quiz
JOIN mdl_question_usages qu ON qu.id = quiza.uniqueid
JOIN mdl_question_attempts qa ON qa.questionusageid = qu.id
JOIN mdl_question que ON que.id = qa.questionid
JOIN mdl_user u ON u.id = quiza.userid

WHERE q.name = "BIO 208 Post Test Assessment"
AND q.course = "17926"

ORDER BY quiza.userid, quiza.attempt, qa.slot

If you see something incorrect please let me know.  I'm still a bit new at SQL.  smile

Thank you!

 
Average of ratings: Useful (1)
Picture of Uğur Feyzullahoğlu
Ynt: Re: SQL Report with Quiz Questions and Answers for all Courses
 

Hi,

How do I write a query to report.php department quiz report for the arrival of the human resources?


So just to make it look like here working in the human resources department



 
Average of ratings: -
Picture of Uğur Feyzullahoğlu
Ynt: Re: SQL Report with Quiz Questions and Answers for all Courses
 

I solved smile

 
Average of ratings: -
Picture of Ebin Manuval
Re: Ynt: Re: SQL Report with Quiz Questions and Answers for all Courses
 

how can I get the quiz open time and close time ?

 
Average of ratings: -
Picture of Uğur Feyzullahoğlu
Ynt: Re: Ynt: Re: SQL Report with Quiz Questions and Answers for all Courses
 

Hi,

Query below for exam start and end date

mdl_quiz_attempts columun -timestart-timefinish

SELECT mc.fullname , us.username , quiz.name, FROM_UNIXTIME(qa.timestart) ,FROM_UNIXTIME(qa.timefinish) , grade.grade

FROM mdl_quiz quiz, mdl_quiz_attempts qa, mdl_course mc, mdl_user us, mdl_quiz_grades grade

WHERE qa.quiz = grade.quiz

AND qa.userid = grade.userid

AND qa.quiz = quiz.id

AND qa.userid = us.id

AND us.id = grade.userid

AND quiz.course=mc.id

 
Average of ratings: Useful (2)
Picture of Pradeep Varma
Re: Ynt: Re: SQL Report with Quiz Questions and Answers for all Courses
 

Hi Could you please provide me SQL query

 
Average of ratings: -
Picture of Nadav Kavalerchik
Re: SQL Report with Quiz Questions and Answers for all Courses
Group Core developersGroup Particularly helpful MoodlersGroup Plugin developersGroup TestersGroup Translators

I have added the above SQL query to the Moodle wiki ad-hoc reports "repository" at:

https://docs.moodle.org/33/en/ad-hoc_contributed_reports#Student_responses_.28answers.29_to_quiz_questions

 
Average of ratings: Useful (1)