|
|
|||||
|
|
SQL Queries for EOC Analysis EOC Predictions with teacher name - using the abc_target
files
ALG1: 2018,
2022, 2023, 2052 (MA08 predictor) GEOM:
2030 (ALG1 predictor) ALG2:
2024 (ALG1 predictor) BIOL:
3020 (RD08 and ENG1 predictors) CHEM:
3050 (BIO predictor) PSCI:
3010 (MA08 predictor) PHYS:
3060 (CHEM and GEOM predictors) ENG1:
1021 (RD08 predictor) CECO:
4052 (BIO and ENG1 predictors) USHI:
4021, 40105XS (CECO and ENG1 predictors) NOTE: 1) The following queries are written to use a CUR test build. If you need to use the queries against any other build, you must change each occurrence of CUR in the query to the appropriate build name.
SELECT m.student_id as 'accmem
id', h.student_id as 'histaudit id', m.school_code as 'school',
m.statecourse as 'state course', m.localcourse as 'local course',
m.coursetitle, m.teachername as 'teacher', m.semester as 'SEM', m.section as
'SEC', m.student_id, m.grade, m.last_name, m.first_name, m.sex,
m.minority_code as 'ethn', h.ma06level as 'MA06 Level',h.ma06score as 'MA06
score', h.ma06cscore as 'MA06 cscore', h.ma07level as 'MA07Level',
h.ma07score as 'MA07 score', h.ma07cscore as 'MA07 Cscore', h.ma08level as 'MA08
Level',h.ma08score as 'MA08 score', h.ma08cscore as 'MA08 Cscore',
h.alg1level as 'ALG1 Level', h.alg1score as 'ALG1 score', h.alg1cscore as 'ALG1
Cscore', t.current_testid as 'test', t.predicted_score as 'predicted score',
t.predicted_cscore as 'predicted Cscore' FROM (SELECT DISTINCT student_id,
last_name, first_name, grade, school_code, statecourse, localcourse,
coursetitle, semester, section, teachername, sex, minority_code FROM accmem
WHERE collection_code = 'CUR' AND semester = '2' AND statecourse in ('2018X','2022X','2023X','2052X'))
m LEFT JOIN (SELECT student_id, subject, current_testid, predicted_score,
predicted_cscore FROM abc_target WHERE collection_code = 'CUR' and subject =
'A1' and current_testid = 'ALG1') t ON (m.student_id = t.student_id) LEFT
JOIN (SELECT student_id, last_name, first_name, ma06level, ma06score,
ma06cscore, ma07level, ma07score, ma07cscore, ma08level, ma08score,
ma08cscore, alg1level, alg1score, alg1cscore FROM histaudit WHERE
collection_code = 'CUR') h ON (m.student_id = h.student_id) ORDER BY
m.school_code, m.teachername, m.semester, m.localcourse, m.section,
m.last_name, m.first_name
ALGEBRA II
SELECT
m.student_id as 'accmem id', h.student_id as 'histaudit id', m.school_code
as 'school', m.statecourse as 'state course', m.localcourse as 'local
course', m.coursetitle, m.teachername as 'teacher', m.semester as 'SEM',
m.section as 'SEC', m.student_id, m.grade, m.last_name, m.first_name, m.sex,
m.minority_code as 'ethn', h.ma06level as 'MA06 Level',h.ma06score as 'MA06
score', h.ma06cscore as 'MA06 Cscore', h.ma07level as 'MA07Level',
h.ma07score as 'MA07 score', h.ma07cscore as 'MA07 Cscore', h.ma08level as 'MA08
Level', h.ma08score as 'MA08 score', h.ma08cscore as 'MA08 Cscore',
h.alg1level as 'ALG1 Level', h.alg1score as 'ALG1score', h.alg1cscore as 'ALG1
Cscore', h.geomlevel as 'GEOM Level',h.geomscore as 'GEOM score',
h.geomcscore as 'GEOM Cscore', h.alg2level as 'ALG2 Level',h.alg2score as 'ALG2
score', h.alg2cscore as 'ALG2 Cscore', t.current_testid as 'test',
t.predicted_score as 'predicted score', t.predicted_cscore as 'predicted
Cscore' FROM (SELECT DISTINCT student_id, last_name, first_name, grade,
school_code, statecourse, localcourse, coursetitle, semester, section,
teachername, sex, minority_code FROM accmem WHERE collection_code = 'CUR'
and semester = '2' AND statecourse like '2024X%' ) m LEFT JOIN (SELECT
student_id, subject, current_testid, predicted_score, predicted_cscore FROM
abc_target WHERE collection_code = 'CUR' and subject = 'A2' and
current_testid = 'ALG2') t ON (m.student_id = t.student_id) LEFT JOIN
(SELECT student_id, last_name, first_name, ma06level, ma06score, ma06cscore,
ma07level, ma07score, ma07cscore, ma08level, ma08score, ma08cscore,
alg1level, alg1score, alg1cscore, geomlevel, geomscore, geomcscore,
alg2level, alg2score, alg2cscore FROM histaudit WHERE collection_code =
'CUR') h ON (m.student_id = h.student_id) ORDER BY m.school_code,
m.teachername, m.semester, m.localcourse, m.section, m.last_name,
m.first_name
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
BIOLOGY
SELECT
m.student_id as 'accmem id', h.student_id as 'histaudit id', m.school_code
as 'school', m.statecourse as 'state course', m.localcourse as 'local
course', m.coursetitle, m.teachername as 'teacher', m.semester as 'SEM',
m.section as 'SEC', m.student_id, m.grade, m.last_name, m.first_name, m.sex,
m.minority_code as 'ethn', h.rd06level as 'RD06 Level', h.rd06score as 'RD06
score', h.rd06cscore as 'RD06 Cscore', h.rd07level as 'RD07Level',
h.rd07score as ‘RD07 score', h.rd07cscore as 'RD07 Cscore', h.rd08level as 'RD08
Level', h.rd08score as 'RD08 score', h.rd08cscore as 'RD08 Cscore',
h.engllevel as 'ENG1 Level', h.englscore as 'ENG1 score', h.englcscore as 'ENG1
Cscore', h.biollevel as 'BIO Level', h.biolscore as 'BIO score',
h.biolcscore as 'BIO Cscore', t.current_testid as 'test', t.predicted_score
as 'predicted score', t.predicted_cscore as 'predicted Cscore' FROM (SELECT
DISTINCT student_id, last_name, first_name, grade, school_code, statecourse,
localcourse, coursetitle, semester, section, teachername, sex, minority_code
FROM accmem WHERE collection_code = 'CUR' and semester = '2' and statecourse
like '3020%') m LEFT JOIN (SELECT student_id, subject, current_testid,
predicted_score, predicted_cscore FROM abc_target WHERE collection_code =
'CUR' and subject = 'BI' and current_testid = 'BIOL') t ON (m.student_id =
t.student_id) LEFT JOIN (SELECT student_id, last_name, first_name, rd06level,
rd06score, rd06cscore, rd07level, rd07score, rd07cscore, rd08level,
rd08score, rd08cscore, biollevel, biolscore, biolcscore, engllevel,
englscore, englcscore FROM histaudit WHERE collection_code = 'CUR') h ON (m.student_id
= h.student_id) ORDER BY m.school_code, m.semester, m.localcourse,
m.teachername, m.section, m.last_name, m.first_name
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PHYSICAL SCIENCE
SELECT
m.student_id as 'accmem id', h.student_id as 'histaudit id', m.school_code
as 'school', m.statecourse as 'state course', m.localcourse as 'local
course', m.coursetitle, m.teachername as 'teacher', m.semester as 'SEM',
m.section as 'SEC', m.student_id, m.grade, m.last_name, m.first_name, m.sex,
m.minority_code as 'ethn', h.ma06level as 'MA06 level',h.ma06score as 'MA06
score', h.ma06cscore as 'MA06 Cscore', h.ma07level as 'MA07Level',
h.ma07score as 'MA07 score', h.ma07cscore as 'MA07 Cscore', h.ma08level as 'MA08
Level', h.ma08score as 'MA08 score', h.ma08cscore as 'MA08 Cscore,
h.pscilevel as 'PSCI Level', h.psciscore as 'PSCIscore', h.pscicscore as 'PSCI
Cscore', t.current_testid as 'test', t.predicted_score as 'predicted score',
t.predicted_cscore as 'predicted Cscore' FROM (SELECT DISTINCT student_id,
last_name, first_name, grade, school_code, statecourse, coursetitle,
localcourse, semester, section, teachername, sex, minority_code FROM accmem
WHERE collection_code = 'CUR' and semester = '2' and statecourse like
'3010%' ) m LEFT JOIN (SELECT student_id, subject, current_testid,
predicted_score, predicted_cscore FROM abc_target WHERE collection_code =
'CUR' and subject = 'PS' and current_testid = 'PSCI') t ON (m.student_id =
t.student_id) LEFT JOIN (SELECT student_id, last_name, first_name, ma06level,
ma06score, ma06cscore, ma07level, ma07score, ma07cscore, ma08level,
ma08score, ma08cscore, pscilevel, psciscore, pscicscore FROM histaudit WHERE
collection_code = 'CUR') h ON (m.student_id = h.student_id) ORDER BY
m.school_code, m.semester, m.localcourse, m.section, m.teachername,
m.last_name, m.first_name
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ENGLISH
SELECT
m.student_id as 'accmem id', h.student_id as 'histaudit id', m.school_code
as 'school', m.statecourse as 'state course', m.localcourse as 'local
course', m.coursetitle, m.teachername as 'teacher', m.semester as 'SEM',
m.section as 'SEC', h.student_id, m.grade, m.last_name, m.first_name, m.sex,
m.minority_code as 'ethn', h.rd05level as 'RD05 Level', h.rd05score as 'RD05
Score', h.rd05cscore as 'RD05 Cscore', h.rd06level as 'RD06Level',
h.rd06score as 'RD06 score', h.rd06cscore as 'RD06 Cscore', h.rd07level as 'RD07
Level', h.rd07score as 'RD07 score',h.rd07cscore as 'RD07 Cscore',
h.rd08level as 'RD08 Level', h.rd08score as 'RD08score', h.rd08cscore as 'RD08
Cscore', h.engllevel as 'ENG1 Level',h.englscore as 'ENG1 score',
h.englcscore as 'ENG1 Cscore', t.current_testid as 'test', t.predicted_score
as 'predicted score', t.predicted_cscore as 'predicted Cscore' FROM (SELECT
DISTINCT student_id, last_name, first_name, grade, school_code, statecourse,
localcourse, coursetitle, semester, section, teachername, sex, minority_code
FROM accmem WHERE collection_code = 'CUR' and semester = '2' and statecourse
like '1021%') m LEFT JOIN (SELECT student_id, subject, current_testid,
predicted_score, predicted_cscore FROM abc_target WHERE collection_code =
'CUR' and subject = 'E1' and current_testid = 'ENGL') t ON (m.student_id =
t.student_id) LEFT JOIN (SELECT student_id, last_name, first_name, rd05level,
rd05score, rd05cscore, rd06level, rd06score, rd06cscore, rd07level,
rd07score, rd07cscore, rd08level, rd08score, rd08cscore, engllevel,
englscore, englcscore FROM histaudit WHERE collection_code = 'CUR') h ON (m.student_id
= h.student_id) ORDER BY m.school_code, m.semester, m.localcourse,
m.teachername, m.section, m.last_name, m.first_name
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
CIVICS and ECONOMICS
SELECT
m.student_id as 'accmem id', h.student_id as 'histaudit id', m.school_code
as 'school', m.statecourse as 'state course', m.localcourse as 'local
course', m.coursetitle, m.teachername as 'teacher', m.semester as 'SEM',
m.section as 'SEC', m.student_id, m.grade, m.last_name, m.first_name, m.sex,
m.minority_code as 'ethn', h.rd06level as 'RD06 level', h.rd06score as 'RD06
score', h.rd06cscore as 'RD06 Cscore', h.rd07level as 'RD07Level',
h.rd07score as 'RD07 score',h.rd07cscore as 'RD07 Cscore', h.rd08level as 'RD08
Level', h.rd08score as 'RD08 score',h.rd08cscore as 'RD08 Cscore',
h.biollevel as 'BIO Level', h.biolscore as 'BIO score', h.biolcscore as 'BIO
Cscore', h.engllevel as 'ENG1 Level', h.englscore as 'ENG1
score',h.englcscore as 'ENG1 Cscore', h.cecolevel as 'CIV Level',
h.cecoscore as 'CIV score', h.cecocscore as 'CIV Cscore', t.current_testid
as 'test', t.predicted_score as 'predicted score', t.predicted_cscore as
'predicted Cscore' FROM (SELECT DISTINCT student_id, last_name, first_name,
grade, school_code, statecourse, localcourse, coursetitle, semester,
section, teachername, sex, minority_code FROM accmem WHERE collection_code =
'CUR' and semester = '2' AND statecourse like '4052%') m LEFT JOIN (SELECT
student_id, subject, current_testid, predicted_score, predicted_cscore FROM
abc_target WHERE collection_code = 'CUR' and subject = 'CI' and
current_testid = 'CECO') t ON (m.student_id = t.student_id) LEFT JOIN
(SELECT student_id, last_name, first_name, rd06level, rd06score, rd06cscore,
rd07level, rd07score, rd07cscore, rd08level, rd08score, rd08cscore,
biollevel, biolscore, biolcscore, engllevel, englscore, englcscore,
cecolevel, cecoscore, cecocscore FROM histaudit WHERE collection_code =
'CUR') h ON (m.student_id = h.student_id) ORDER BY m.school_code,
m.teachername, m.semester, m.localcourse, m.section, m.last_name,
m.first_name
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
HISTORY
SELECT m.student_id as 'accmem id', h.student_id as 'histaudit id', m.school_code as 'school', m.statecourse as 'state course', m.localcourse as 'local course', m.coursetitle, m.teachername as 'teacher', m.semester as 'SEM', m.section as 'SEC', m.student_id, m.grade, m.last_name, m.first_name, m.sex, m.minority_code as 'ethn', h.rd06level as 'RD06 level', h.rd06score as 'RD06 score', h.rd06cscore as 'RD06 Cscore', h.rd07level as 'RD07 Level', h.rd07score as 'RD07 score', h.rd07cscore as 'RD07 Cscore’, h.rd08level as 'RD08 Level', h.rd08score as 'RD08 score', h.rd08cscore as 'RD08 Cscore', h.cecolevel as 'CIV Level',h.cecoscore as 'CIV Score', h.cecocscore as 'CIV Cscore', h.engllevel as 'ENG1 Level', h.englscore as 'ENG1 score', h.englcscore as 'ENG1 Cscore', h.biollevel as 'BIOLevel', h.biolscore as 'BIO score', h.biolcscore as 'BIO Cscore', h.ushilevel as 'USHI Level',h.ushiscore as 'USHI score', h.ushicscore as 'USHI Cscore', t.current_testid as 'test', t.predicted_score as 'predicted score', t.predicted_cscore as 'predicted Cscore' FROM (SELECT DISTINCT student_id, last_name, first_name, grade, school_code, statecourse, localcourse, coursetitle, semester, section, teachername, sex, minority_code FROM accmem WHERE collection_code = 'CUR' and semester = '2' AND (statecourse like '4021%' or statecourse = '40105XS')) m LEFT JOIN (SELECT student_id, subject, current_testid, predicted_score, predicted_cscore FROM abc_target WHERE collection_code = 'CUR' and subject = 'US' and current_testid = 'USHI') t ON (m.student_id = t.student_id) LEFT JOIN (SELECT student_id, last_name, first_name, rd06level, rd06score, rd06cscore, rd07level, rd07score, rd07cscore, rd08level, rd08score, rd08cscore, cecolevel, cecoscore, cecocscore, engllevel, englscore, englcscore, biollevel, biolscore, biolcscore, ushilevel, ushiscore, ushicscore FROM histaudit WHERE collection_code = 'CUR') h ON (m.student_id = h.student_id) ORDER BY m.school_code, m.teachername, m.semester, m.localcourse, m.section, m.last_name, m.first_name
|
| | ||
