Budget-Educator/sql-overview.sql at master · NYU-CS6313-Projects/Budget-Educator · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
SELECT
	budget.`DBN` AS `DBN`,
	geographical.`Location_Name` AS 'School Name',
	budget.`Total_Budget` AS `Budget`,
	demographics.`total_enrollment` AS `Total Enrollment`,
	(budget.`Total_Budget`/demographics.`total_enrollment` ) AS 'Budget Per Student',

	geographical.`Location_Type_Description` AS `School Type`,
	geographical.`Location_Category_Description` AS `School Category`,
	geographical.`Primary_Address` AS `Street Address`,
	geographical.`City` AS `City`,
	geographical.`State_Code` AS `State`,
	geographical.`Zip` AS `Zip Code`,
	geographical.`boroughCode` AS `Borough`,
	geographical.`latitude` AS `Latitude`,
	geographical.`longitude` AS `Longitude`,
	geographical.`loc` AS `Coordinates`,

	progress.`progress_report_type` AS `Report Type`,
	progress.`progress_report_grade` AS `Report Grade`,
	demographics.`fl_percent` AS `FL Percent`,
	demographics.`frl_percent` AS `FRL Percent`,
	demographics.`prek` AS `Pre-K Demographics`,
	demographics.`k` AS `K Demographics`,
	demographics.`grade1` AS `Grade 1 Demographics`,
	demographics.`grade2` AS `Grade 2 Demographics`,
	demographics.`grade3` AS `Grade 3 Demographics`,
	demographics.`grade4` AS `Grade 4 Demographics`,
	demographics.`grade5` AS `Grade 5 Demographics`,
	demographics.`grade6` AS `Grade 6 Demographics`,
	demographics.`grade7` AS `Grade 7 Demographics`,
	demographics.`grade8` AS `Grade 8 Demographics`,
	demographics.`grade9` AS `Grade 9 Demographics`,
	demographics.`grade10` AS `Grade 10 Demographics`,
	demographics.`grade11` AS `Grade 11 Demographics`,
	demographics.`grade12` AS `Grade 12 Demographics`,
	demographics.`ell_num` AS `Number of English Language Learners`,
	demographics.`ell_percent` AS `Percent of English Language Learners`,
	demographics.`sped_num` AS `Number of Special Education Students`,
	demographics.`sped_percent` AS `Percent of Special Education Students`,
	demographics.`ctt_num` AS `Number of Collaborative Team Teaching Students`,
	demographics.`selfcontained_num` AS `Number of Self-Contained Classroom Students`,
	demographics.`asian_num` AS `Number of Asian Students`,
	demographics.`asian_per` AS `Percent of Asian Students`,
	demographics.`black_num` AS `Number of Black Students`,
	demographics.`black_per` AS `Percent of Black Students`,
	demographics.`hispanic_num` AS `Number of Hispanic Students`,
	demographics.`hispanic_per` AS `Percent of Hispanic Students`,
	demographics.`white_num` AS `Number of White Students`,
	demographics.`white_per` AS `Percent of White Students`,
	demographics.`male_num` AS `Number of Male Students`,
	demographics.`male_per` AS `Percent of Male Students`,
	demographics.`female_num` AS `Number of Female Students`,
	demographics.`female_per` AS `Percent of Female Students`,
	graduation_rates.`Total Cohort` AS `Number of Students in Cohort`,
	graduation_rates.`Total Grads - n` AS `Number of Graduation Students in Cohort`,
	graduation_rates.`Total Grads - % of cohort` AS `Percent of Graduation Students in Cohort`,
	graduation_rates.`Total Regents - n` AS `Number of Regents in Cohort`,
	graduation_rates.`Total Regents - % of cohort`,
	graduation_rates.`Total Regents - % of grads`,
	graduation_rates.`Advanced Regents - n`,
	graduation_rates.`Advanced Regents - % of cohort`,
	graduation_rates.`Advanced Regents - % of grads`,
	graduation_rates.`Regents without Advanced - n`,
	graduation_rates.`Regents without Advanced - % of cohort`,
	graduation_rates.`Regents without Advanced - % of grads`,
	graduation_rates.`Local - n`,
	graduation_rates.`Local - % of cohort`,
	graduation_rates.`Local - % of grads`,
	graduation_rates.`Still Enrolled - n`,
	graduation_rates.`Still Enrolled - % of cohort`,
	graduation_rates.`Dropped Out - n`,
	graduation_rates.`Dropped Out - % of cohort`,
	ela_scores.`Number Tested` AS `Number of Students Tested for ELA`,
	ela_scores.`Mean Scale Score` AS `ELA Mean Scale Score`,
	ela_scores.`Num Level 1` AS `Number of Students Scoring Level 1 in ELA`,
	ela_scores.`Pct Level 1` AS `Percent of Students Scoring Level 1 in ELA`,
	ela_scores.`Num Level 2` AS `Number of Students Scoring Level 2 in ELA`,
	ela_scores.`Pct Level 2` AS `Percent of Students Scoring Level 2 in ELA`,
	ela_scores.`Num Level 3` AS `Number of Students Scoring Level 3 in ELA`,
	ela_scores.`Pct Level 3` AS `Percent of Students Scoring Level 3 in ELA`,
	ela_scores.`Num Level 4` AS `Number of Students Scoring Level 4 in ELA`,
	ela_scores.`Pct Level 4` AS `Percent of Students Scoring Level 4 in ELA`,
	math_scores.`Number Tested` AS `Number of Students Tested for Math`,
	math_scores.`Mean Scale Score` AS `Math Mean Scale Score`,
	math_scores.`Num Level 1` AS `Number of Students Scoring Level 1 in Math`,
	math_scores.`Pct Level 1` AS `Percent of Students Scoring Level 1 in Math`,
	math_scores.`Num Level 2` AS `Number of Students Scoring Level 2 in Math`,
	math_scores.`Pct Level 2` AS `Percent of Students Scoring Level 2 in Math`,
	math_scores.`Num Level 3` AS `Number of Students Scoring Level 3 in Math`,
	math_scores.`Pct Level 3` AS `Percent of Students Scoring Level 3 in Math`,
	math_scores.`Num Level 4` AS `Number of Students Scoring Level 4 in Math`,
	math_scores.`Pct Level 4` AS `Percent of Students Scoring Level 4 in Math`

FROM budget
	INNER JOIN demographics on demographics.DBN=budget.DBN
	INNER JOIN geographical on geographical.DBN=budget.DBN
	LEFT JOIN progress ON progress.DBN=budget.DBN
	LEFT JOIN graduation_rates on graduation_rates.DBN=budget.DBN
	LEFT JOIN ela_scores on ela_scores.DBN=budget.DBN
	LEFT JOIN math_scores on math_scores.DBN=budget.DBN

ORDER BY DBN