Skip to content

Instantly share code, notes, and snippets.

@tummykung
Last active September 6, 2015 23:05
Show Gist options
  • Save tummykung/9d91241c9b8332d4453e to your computer and use it in GitHub Desktop.
Save tummykung/9d91241c9b8332d4453e to your computer and use it in GitHub Desktop.
select STRAIGHT_JOIN
ps_class_instr.CRSE_ID,
ps_class_instr.STRM,
ENRL_TOT,
EMPLID as 'INSTR',
ACTUAL_ENRL_TOT as 'COURSE_ENRL_TOT',
GRDE_ENRL_TOT as 'GRDE_ENRL_TOT',
ps_class_instr.CLASS_SECTION as 'CLASS_SCTN',
CLASS_NBR as 'CLASS_NBR',
ACAD_GROUP as 'ACAD_GROUP',
SUBJECT as 'SBJ',
CATALOG_NBR as 'CATALOG_NBR',
DESCR,
ROUND(GRADE_AVG, 3) as GRADE_AVG,
GRADE_AVG_RANK,
(@total - GRADE_AVG_RANK)/@total as 'GRADE_AVG_PCT',
ROUND(GPA, 3) as GPA,
ROUND(DIFFICULTY01, 3) as DIFF1,
round(UNIT_AVG, 2) as 'Q_UNIT_AVG'
from
ps_class_instr USE INDEX FOR JOIN (FULL)
join
Course
on
ps_class_instr.CRSE_ID=Course.CRSE_ID and ps_class_instr.STRM=Course.STRM and ps_class_instr.CLASS_SECTION=Course.CLASS_SECTION
where SUBJECT='CS' and CATALOG_NBR LIKE '221%';
+----+-------------+----------------+------+---------------------------------------------+------+---------+---------------------------------------------------------------+---------+-------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+----------------+------+---------------------------------------------+------+---------+---------------------------------------------------------------+---------+-------------
| 1 | SIMPLE | ps_class_instr | ALL | FULL | NULL | NULL | NULL | 1497401 |
| 1 | SIMPLE | Course | ref | STRM,FULL,SUBJECT_AND_CATALOG,CLASS_SECTION | FULL | 778 | august.ps_class_instr.CRSE_ID,august.ps_class_instr.STRM,func | 1 | Using where
+----+-------------+----------------+------+---------------------------------------------+------+---------+---------------------------------------------------------------+---------+------------
*************************** 1. row ***************************
Table: ps_class_instr
Create Table: CREATE TABLE `ps_class_instr` (
`CRSE_ID` varchar(6) NOT NULL,
`CRSE_OFFER_NBR` int(38) NOT NULL,
`STRM` varchar(4) NOT NULL,
`SESSION_CODE` varchar(3) NOT NULL,
`CLASS_SECTION` varchar(4) NOT NULL,
`CLASS_MTG_NBR` int(38) NOT NULL,
`INSTR_ASSIGN_SEQ` int(38) NOT NULL,
`EMPLID` varchar(255) DEFAULT NULL,
`INSTR_ROLE` varchar(4) NOT NULL,
`GRADE_RSTR_ACCESS` varchar(1) NOT NULL,
`CONTACT_MINUTES` int(38) NOT NULL,
`SCHED_PRINT_INSTR` varchar(1) NOT NULL,
`INSTR_LOAD_FACTOR` float(10,4) NOT NULL,
`EMPL_RCD` int(38) NOT NULL,
`ASSIGN_TYPE` varchar(3) NOT NULL,
`WEEK_WORKLOAD_HRS` float(5,2) NOT NULL,
`ASSIGNMENT_PCT` float(5,2) NOT NULL,
`AUTO_CALC_WRKLD` varchar(1) NOT NULL,
KEY `STRM` (`STRM`),
KEY `FULL` (`CRSE_ID`,`STRM`,`CLASS_SECTION`),
KEY `INSTR_ROLE` (`INSTR_ROLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment