MYUCD CMS Data Queries

Pull all the quiz owners by number of quizzes, last name, first name

select qz.kerberos_name, p.last, p.first, count(qz.quizID) as quizcount 
from myweb_user.qz_quiz qz,  myweb_user.person p
where qz.KERBEROS_NAME = p.KERBEROS_NAME
group by qz.kerberos_name, p.last, p.first
order by quizcount desc, p.last

Pull all quiz publishers

SELECT distinct pm.user_account, p.last, p.first, p.DEPT_NAME
FROM MYWEB_USER.PAGE_METADATA pm, myweb_user.person p
WHERE pm.user_account = p.KERBEROS_NAME
	  AND (pm.IS_QUIZ IS NOT Null)
ORDER BY p.last

Pull all the quizzes that a user has proxy access to but does not 'own'

select distinct quizID, quizName, LOWER(quizName) as lowName, 'Y' as proxy
from myweb_user.qz_quiz Q, myweb_user.course_mgmt_proxy P, myweb_user.qzt_top T
where Q.quizID = T.quiz_id
	and T.crn = P.crn
	and T.term_code = P.term_code
	and P.email_name = '[LJW:email name]'
	and T.completed = '1' 
	and T.userType not like '%t'
	and T.userType != 'PP'
	and Q.kerberos_name != '[LJW:kerberos name]'
	and T.term_code IN (
		select distinct term_code as code 
		from myweb_user.course_mgmt_proxy
		where  email_name = '[LJW:email name]'
			   and cm_guest is null)

Pull the quiz users/crn's for the current term

SELECT pm.crn, pm.page_name, pm.file_name, pm.user_account, p.last, p.first, p.DEPT_NAME, pm.is_quiz, pm.deleted
FROM MYWEB_USER.PAGE_METADATA pm, myweb_user.person p
WHERE pm.TERM_CODE = '200601'
	  AND pm.user_account = p.KERBEROS_NAME
	  AND (pm.IS_QUIZ IS NOT Null)
ORDER BY p.last, pm.crn, pm.is_quiz desc

Get quiz usage data on individual users, like when quizzes were released.

SELECT TERM_CODE, PAGE_NAME, FILE_NAME, 
   USER_ACCOUNT, ACTIVITY_DATE, RELEASE_FLAG
FROM MYWEB_USER.PAGE_METADATA Tbl
Where IS_QUIZ = 'q'
	  AND USER_ACCOUNT = '[LJW:kerberos name here]'
ORDER BY  TERM_CODE, ACTIVITY_DATE

Get quiz owner(s) in cases where publisher does not 'own' his/her quizzes

SELECT p.LAST, p.FIRST
FROM myweb_user.person p
WHERE p.KERBEROS_NAME in
	  (SELECT kerberos_name
	  FROM myweb_user.qz_quiz
	  WHERE QUIZID in ( 
	  		SELECT FILE_NAME 
   			fROM MYWEB_USER.PAGE_METADATA Tbl
			Where IS_QUIZ = 'q' AND USER_ACCOUNT = '[LJW:kerberos name here]'))

Pull the instructors who've used the release quiz by date function in MyUCDavis

SELECT pm.user_account, p.last, p.first, pm.term_code, count (pm.file_name) as totalreleased
FROM MYWEB_USER.PAGE_METADATA pm, myweb_user.person p
WHERE pm.is_quiz = 'q'
	  and pm.release_flag = 'Y'
	  and  pm.USER_ACCOUNT = p.KERBEROS_NAME
GROUP BY pm.term_code, pm.user_account, p.last, p.first 
order by pm.term_code, p.last

Pull the question owners from the question pool by number of questions (where count is > 25)

SELECT qc.KERBEROS_NAME, p.last, p.first,p.DEPT_NAME, count(qc.questionID) as questioncount
FROM MYWEB_USER.QZ_QUES_CATALOG qc, myweb_user.person p
where qc.KERBEROS_NAME = p.KERBEROS_NAME
group by qc.kerberos_name, p.last, p.first, p.DEPT_NAME
HAVING count(QUESTIONID) > 25
order by QUESTIONCOUNT desc, p.last

Pull the owners of 'calculated' question types from the pool

SELECT qc.KERBEROS_NAME, p.last, p.first,p.DEPT_NAME, count(qc.TYPE) as typecount
FROM MYWEB_USER.QZ_QUES_CATALOG qc, myweb_user.person p
WHERE qc.KERBEROS_NAME = p.KERBEROS_NAME AND TYPE like 'calculated'
GROUP BY qc.kerberos_name, p.last, p.first, p.DEPT_NAME
ORDER BY Typecount desc

Pull the owners of question collections

SELECT qc.KERBEROS_NAME as owner, p.last, p.first,count(collectionid) as collections
FROM myweb_user.qz_coll_info qc, myweb_user.person p
WHERE qc.KERBEROS_NAME = p.KERBEROS_NAME
GROUP BY qc.KERBEROS_NAME, p.last, p.first
ORDER BY collections desc