Great WebCT CE6 SQL commands

Returns: All courses a student is enrolled in, their status and instructor
Input: webct_id

select p.webct_id,lc.id,lc.name,lc.description,lc.source_id,rd.name,m.status_flag,m.delete_status,p2.webct_id
from webct.learning_context lc, webct.PERSON p, webct.member m, webct.role r, webct.role_definition rd,
webct.learning_context lc2, webct.PERSON p2, webct.member m2, webct.role r2, webct.role_definition rd2
where lc.id = m.learning_context_id
and m.person_id = p.id
and r.member_id = m.id
and rd.id = r.role_definition_id
and p.webct_id = 'username'
and lc.id = lc2.id
and lc2.id = m2.learning_context_id
and m2.person_id = p2.id
and r2.member_id = m2.id
and rd2.id = r2.role_definition_id
and rd2.name = 'SINS'
order by lc.source_id;

-----------------------------------------------------------

Returns: All enrollments in a class for all roles
Input: source_id

select p.webct_id,lc.id,lc.name,lc.description,lc.source_id,rd.name,m.status_flag,m.delete_status,p2.webct_id
from webct.learning_context lc, webct.PERSON p, webct.member m, webct.role r, webct.role_definition rd,
webct.learning_context lc2, webct.PERSON p2, webct.member m2, webct.role r2, webct.role_definition rd2
where m.person_id = p.id
and lc.id = m.learning_context_id
and r.member_id = m.id
and rd.id = r.role_definition_id
and lc.source_id = 'source_id'
and lc.id = lc2.id
and lc2.id = m2.learning_context_id
and m2.person_id = p2.id
and r2.member_id = m2.id
and rd2.id = r2.role_definition_id
and rd2.name = 'SINS'
and p.webct_id not like '%webct_demo%'
order by rd.name, p.webct_id;

-----------------------------------------------------------

Returns: All enrollments in a class for all roles
Input: course name (AB-1234-01); term code

select p.webct_id,lc.id,lc.name,lc.description,lc.source_id,rd.name,m.status_flag,m.delete_status,p2.webct_id
from webct.learning_context lc, webct.PERSON p, webct.member m, webct.role r, webct.role_definition rd,
webct.learning_context lc2, webct.PERSON p2, webct.member m2, webct.role r2, webct.role_definition rd2
where m.person_id = p.id
and lc.id = m.learning_context_id
and r.member_id = m.id
and rd.id = r.role_definition_id
and lc.name like 'coursename%'
and lc.source_id LIKE '%termcode'
and lc.id = lc2.id
and lc2.id = m2.learning_context_id
and m2.person_id = p2.id
and r2.member_id = m2.id
and rd2.id = r2.role_definition_id
and rd2.name = 'SINS'
and p.webct_id not like '%webct_demo%'
order by rd.name, p.webct_id;

WebCT, Campus Edition 6, CE 6, SQL, enrollments, classes, databases