Get exam score using SQL

Get exam score using SQL

by Lee Taylor-Vaughan -
Number of replies: 3

Hello:

How can I query Moodle and get an exam score for a specific user, in a specific course, for a specific lesson, for a specific quiz?

Our external system issues our certificates and we need to import the exam scores from our current LMS. We are moving over to moodle and our only stumbling block is not knowing the SQL schema, to achieve the above task. 

Thanks

Lee


Average of ratings: -
In reply to Lee Taylor-Vaughan

Re: Get exam score using SQL

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
This query below is from a bigger one of mine that is broader, so it probably has columns that you do not need.

The key is to change the last three "where" conditions to your specific course, module #, and student. My guess is that you only need the quiz number (module) because it will be unique to one course, but I'll leave it up to you.

# QUIZ
SELECT cm.course AS CourseID,
c.shortname AS Course_Shortname,
c.idnumber AS Course_IDNumber,
cm.section AS TopicID,
t.name AS TopicName,
cm.id AS ActivityID,
q.name AS ActivityName,
cm.module AS ActTypeNum,
m.name AS ActTypeName,
cm.completion AS ActCompType,
cm.visible AS ActivityVisible,
if(q.timeopen=0, 0, from_unixtime(q.timeopen, "%m/%d/%Y %h:%i:%s %p")) AS ActivityTimeOpen,
if(q.timeclose=0, 0, from_unixtime(q.timeclose, "%m/%d/%Y %h:%i:%s %p")) AS ActivityTimeClosed,
cm.instance AS LogInstance,
from_unixtime(ls.timecreated, "%m/%d/%Y %h:%i:%s %p") AS LogDate,
ls.action AS LogAction,
ls.target as LogTarget,
ls.origin AS LogOrigin,
ls.userid AS StudentID,
ls.ip AS StudentIP,
u.lastname AS StudentLastName,
u.firstname AS StudentFirstName,
qa.quiz AS AttemptID,
qa.id AS StudentItem,
qa.attempt AS ItemAttemptNumber,
if(qa.timestart=0, 0, from_unixtime(qa.timestart, "%m/%d/%Y %h:%i:%s %p")) AS StudentItemStartTime,
if(qa.timefinish=0, 0, from_unixtime(qa.timefinish, "%m/%d/%Y %h:%i:%s %p")) AS StudentItemEndTime,
qa.sumgrades AS StudentItemGrade,
gg.rawgrade AS StudentActivityGrade,
q.grade AS ActivityMaxGrade
FROM mdl_course_modules cm
JOIN mdl_modules m ON m.id = cm.module
JOIN mdl_course c ON c.id = cm.course
JOIN mdl_course_sections t ON t.id = cm.section
JOIN mdl_quiz q ON q.id = cm.instance
LEFT JOIN mdl_grade_items gi ON (gi.iteminstance = cm.instance
AND cm.course = gi.courseid)
JOIN mdl_logstore_standard_log ls ON (ls.contextinstanceid = cm.id
AND ls.courseid = cm.course)
LEFT JOIN mdl_quiz_attempts qa ON (qa.id = ls.objectid
AND qa.userid = ls.userid)
JOIN mdl_grade_grades gg ON (gg.itemid = gi.id
AND ls.userid = gg.userid)
JOIN mdl_user u ON u.id = ls.userid
WHERE cm.module = 16
AND cm.visible = 1
AND ls.component = "mod_quiz"
AND ls.action in ("submitted")
AND cm.course IN (93610)
and ls.userid in (3348)
and cm.id in (7396)
Average of ratings: Useful (1)