SQL Report with Quiz Questions and Answers for all Courses

SQL Report with Quiz Questions and Answers for all Courses

by Juan F -
Number of replies: 10

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: -
In reply to Juan F

Re: SQL Report with Quiz Questions and Answers for all Courses

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

https://docs.moodle.org/dev/Overview_of_the_Moodle_question_engine#Detailed_data_about_an_attempt might help you get started.

Average of ratings: -
In reply to Tim Hunt

Re: SQL Report with Quiz Questions and Answers for all Courses

by Juan F -

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: Useful (1)
In reply to Juan F

Re: SQL Report with Quiz Questions and Answers for all Courses

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of 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)
In reply to Tim Hunt

Re: SQL Report with Quiz Questions and Answers for all Courses

by Juan F -

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)
In reply to Juan F

Ynt: Re: SQL Report with Quiz Questions and Answers for all Courses

by Uğur Feyzullahoğlu -

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


Attachment imaj.jpg
Average of ratings: -
In reply to Uğur Feyzullahoğlu

Ynt: Re: SQL Report with Quiz Questions and Answers for all Courses

by Uğur Feyzullahoğlu -

I solved smile

Average of ratings: -
In reply to Uğur Feyzullahoğlu

Re: Ynt: Re: SQL Report with Quiz Questions and Answers for all Courses

by Ebin Manuval -

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

Average of ratings: -
In reply to Ebin Manuval

Ynt: Re: Ynt: Re: SQL Report with Quiz Questions and Answers for all Courses

by Uğur Feyzullahoğlu -

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)
In reply to Uğur Feyzullahoğlu

Re: Ynt: Re: SQL Report with Quiz Questions and Answers for all Courses

by Pradeep Varma -

Hi Could you please provide me SQL query

Average of ratings: -