SQL Query to display quizzes by section and instructor

As part of our upgrade to Vista 4.1.1 we’re testing a known issue with the Assessment/Quiz feature where the student, after submitting, sees the correct quiz answers whether the Instructor intended them to or not… (It looks, at this juncture, as though it’s a simple matter of the default having changed from Vista3 to Vista4). We do have a workaround in process.

So… we wanted to know who would be affected by this problem, and here’s the way we came up with to get the answer, also useful just to know what the Assessment Tool usage is on your system. This query is courtesy my colleague, Brandon Rich.

— quiz counts by section with instructor/designer names
select p.webct_id, p.name_fn, m.role, lc.name, count(sc.label) as num_quizzes
from webct.rpt_member m, webct.person p,
webct.section_column sc, webct.section_book sb,
webct.co_lc_assignment x, webct.learning_context lc
where sc.cobalt_type=’Quiz’
and sc.section_book_id = sb.id
and sb.template_id = x.template_id
and x.learning_context_id = lc.id
and m.person_id = p.id
and m.learning_context_id = lc.id
and ( m.role = ‘SINS’ OR m.role = ‘SDES’ )
and p.name_fn is not null
group by p.webct_id, p.name_fn, m.role, lc.name
order by count(sc.label) desc;