gistfile1.sql

// Course Instructor

SELECT DW8HCQA_PS_PERSONAL_DATA.NAME, DW8HCQA_PS_CLASS_TBL.ACAD_ORG, DW8HCQA_PS_CLASS_TBL.CATALOG_NBR, DW8HCQA_PS_CLASS_TBL.STRM, DW8HCQA_PS_CLASS_TBL.CAMPUS, DW8HCQA_PS_CLASS_INSTR.INSTR_ROLE, DW8HCQA_PS_CLASS_TBL.CRSE_ID, DW8HCQA_PS_CLASS_TBL.CRSE_OFFER_NBR, DW8HCQA_PS_CLASS_TBL.CLASS_SECTION, DW8HCQA_PS_CLASS_INSTR.INSTR_ASSIGN_SEQ
FROM DW8HCQA_PS_CLASS_TBL INNER JOIN (DW8HCQA_PS_CLASS_INSTR INNER JOIN DW8HCQA_PS_PERSONAL_DATA ON DW8HCQA_PS_CLASS_INSTR.EMPLID = DW8HCQA_PS_PERSONAL_DATA.EMPLID) ON (DW8HCQA_PS_CLASS_INSTR.CLASS_SECTION = DW8HCQA_PS_CLASS_TBL.CLASS_SECTION) AND (DW8HCQA_PS_CLASS_TBL.STRM = DW8HCQA_PS_CLASS_INSTR.STRM) AND (DW8HCQA_PS_CLASS_TBL.CRSE_OFFER_NBR = DW8HCQA_PS_CLASS_INSTR.CRSE_OFFER_NBR) AND (DW8HCQA_PS_CLASS_TBL.CRSE_ID = DW8HCQA_PS_CLASS_INSTR.CRSE_ID)
WHERE (((DW8HCQA_PS_CLASS_TBL.ACAD_ORG)="D0326") AND ((DW8HCQA_PS_CLASS_TBL.CATALOG_NBR)=" 113") AND ((DW8HCQA_PS_CLASS_TBL.STRM)="1078") AND ((DW8HCQA_PS_CLASS_TBL.CAMPUS)="COL"))
ORDER BY DW8HCQA_PS_PERSONAL_DATA.NAME;


// Lectures by Term

SELECT DW8HCQA_PS_PERSONAL_DATA.NAME, DW8HCQA_PS_CLASS_INSTR.EMPLID, DW8HCQA_PS_CLASS_TBL.*
FROM (DW8HCQA_PS_CLASS_TBL INNER JOIN DW8HCQA_PS_CLASS_INSTR ON (DW8HCQA_PS_CLASS_TBL.CLASS_SECTION = DW8HCQA_PS_CLASS_INSTR.CLASS_SECTION) AND (DW8HCQA_PS_CLASS_TBL.STRM = DW8HCQA_PS_CLASS_INSTR.STRM) AND (DW8HCQA_PS_CLASS_TBL.CRSE_OFFER_NBR = DW8HCQA_PS_CLASS_INSTR.CRSE_OFFER_NBR) AND (DW8HCQA_PS_CLASS_TBL.CRSE_ID = DW8HCQA_PS_CLASS_INSTR.CRSE_ID)) INNER JOIN DW8HCQA_PS_PERSONAL_DATA ON DW8HCQA_PS_CLASS_INSTR.EMPLID = DW8HCQA_PS_PERSONAL_DATA.EMPLID
WHERE (((DW8HCQA_PS_CLASS_TBL.ACAD_ORG)="D0326") AND ((DW8HCQA_PS_CLASS_TBL.CAMPUS)="COL") AND ((DW8HCQA_PS_CLASS_TBL.STRM)="1088") AND ((DW8HCQA_PS_CLASS_TBL.SSR_COMPONENT)="LEC"));


// WI09 116 Sections

SELECT DW8HCQA_PS_CLASS_TBL.*
FROM DW8HCQA_PS_CLASS_TBL
WHERE (((DW8HCQA_PS_CLASS_TBL.ACAD_ORG)="D0326") AND ((DW8HCQA_PS_CLASS_TBL.CAMPUS)="COL") AND ((DW8HCQA_PS_CLASS_TBL.STRM)="1090") AND ((DW8HCQA_PS_CLASS_TBL.CRSE_ID)="102547"));


// Name and Username by EmplId

SELECT DW8HCQA_PS_PERSONAL_DATA.EMPLID, DW8HCQA_PS_PERSONAL_DATA.NAME_PREFIX, DW8HCQA_PS_PERSONAL_DATA.FIRST_NAME, DW8HCQA_PS_PERSONAL_DATA.LAST_NAME, lcase(DW8HCQA_PS_PERSONAL_DATA.CAMPUS_ID) AS username
FROM DW8HCQA_PS_PERSONAL_DATA
WHERE (((DW8HCQA_PS_PERSONAL_DATA.EMPLID)=[EmplID:]));


// EmplId by Username

SELECT DW8HCQA_PS_EMAIL_ADDRESSES.EMPLID, DW8HCQA_PS_EMAIL_ADDRESSES.EMAIL_ADDR
FROM DW8HCQA_PS_EMAIL_ADDRESSES
WHERE (((DW8HCQA_PS_EMAIL_ADDRESSES.EMAIL_ADDR)=[username:]+"@osu.edu") AND ((DW8HCQA_PS_EMAIL_ADDRESSES.E_ADDR_TYPE)="CAMP"));