library(RSQLite)
library(readr)
# Estabelece a conexão com o arquivo do banco de dados SQLite
# A conexão é armazenada na variável 'conn' para ser usada posteriormente
conn <- dbConnect(RSQLite::SQLite(), "database.sqlite3")
cat("--- Início da Análise ---\n\n")--- Início da Análise ---cat("Carregando dados dos arquivos CSV para o banco de dados...\n")Carregando dados dos arquivos CSV para o banco de dados...# Uma lista que mapeia o nome da tabela no banco de dados para o nome do arquivo .csv
tabelas_para_carregar <- list(
"instructors" = "instructors.csv",
"teachings" = "teachings.csv",
"sections" = "sections.csv",
"course_offerings" = "course_offerings.csv",
"subject_memberships" = "subject_memberships.csv",
"subjects" = "subjects.csv",
"grade_distribution" = "grade_distributions.csv",
"courses" = "courses.csv",
"rooms" = "rooms.csv",
"schedules" = "schedules.csv"
)
# Um loop 'for' que itera sobre a lista acima
for (nome_tabela in names(tabelas_para_carregar)) {
ficheiro_csv <- tabelas_para_carregar[[nome_tabela]]
dados_csv <- read_csv(ficheiro_csv)
# Escreve o data frame para o banco de dados SQLite
# 'overwrite = TRUE' : gse a tabela já existir, ela será substituída
dbWriteTable(conn, nome_tabela, dados_csv, overwrite = TRUE, row.names = FALSE)
cat(paste("Tabela '", nome_tabela, "' carregada.\n", sep=""))
}Tabela 'instructors' carregada.Tabela 'teachings' carregada.Tabela 'sections' carregada.Tabela 'course_offerings' carregada.Tabela 'subject_memberships' carregada.Tabela 'subjects' carregada.Tabela 'grade_distribution' carregada.Tabela 'courses' carregada.Tabela 'rooms' carregada.Tabela 'schedules' carregada.cat("Carregamento de todas as tabelas concluído.\n\n")Carregamento de todas as tabelas concluído.cat("PERGUNTA 1: Quantos professores lecionaram disciplinas de Estatística?\n")PERGUNTA 1: Quantos professores lecionaram disciplinas de Estatística?# Esta é uma consulta SQL (Query) para encontrar a resposta
# 'INNER JOIN': usado para unir várias tabelas para criar uma ligação entre 'instructors' (professores) e 'subjects' (assuntos/disciplinas)
# 'COUNT(DISTINCT i.id)' é usado para contar cada professor apenas uma vez, mesmo que ele tenha lecionado várias disciplinas de estatística
query_contagem <- "
SELECT COUNT(DISTINCT i.id) AS total_professores FROM instructors i
INNER JOIN teachings t ON i.id = t.instructor_id
INNER JOIN sections s ON t.section_uuid = s.uuid
INNER JOIN course_offerings co ON s.course_offering_uuid = co.uuid
INNER JOIN subject_memberships sm ON co.uuid = sm.course_offering_uuid
INNER JOIN subjects sub ON sm.subject_code = sub.code
WHERE sub.abbreviation = 'STAT';"
# 'dbGetQuery' envia a consulta para o banco de dados e retorna o resultado
total_professores <- dbGetQuery(conn, query_contagem)
cat("RESULTADO: O número total de professores de Estatística é", total_professores$total_professores, "\n\n")RESULTADO: O número total de professores de Estatística é 379 # Para evitar cálculos repetidos, usa-se uma Common Table Expression (CTE) com o nome 'gpa_por_oferta'
# Uma CTE é como uma tabela temporária que existe apenas durante a execução da consulta
cte_gpa_stat <- "
WITH gpa_por_oferta AS (
SELECT
gd.course_offering_uuid,
-- A fórmula abaixo calcula o GPA ponderado. Cada nota (A=4, B=3, etc.)
-- é multiplicada pelo número de alunos que a obtiveram.
CAST(
(gd.a_count * 4.0) + (gd.ab_count * 3.5) + (gd.b_count * 3.0) + (gd.bc_count * 2.5) +
(gd.c_count * 2.0) + (gd.d_count * 1.0) + (gd.f_count * 0.0)
AS REAL) /
-- O resultado é dividido pelo número total de alunos para obter a média.
-- A condição 'WHERE' no final garante que não haja divisão por zero.
(gd.a_count + gd.ab_count + gd.b_count + gd.bc_count + gd.c_count + gd.d_count + gd.f_count) AS gpa
FROM grade_distribution gd
INNER JOIN subject_memberships sm ON gd.course_offering_uuid = sm.course_offering_uuid
INNER JOIN subjects s ON sm.subject_code = s.code
WHERE s.abbreviation = 'STAT' AND
(gd.a_count + gd.ab_count + gd.b_count + gd.bc_count + gd.c_count + gd.d_count + gd.f_count) > 0
)
"cat("PERGUNTA 2a: Ranking dos professores (do mais difícil para o mais fácil)\n")PERGUNTA 2a: Ranking dos professores (do mais difícil para o mais fácil)# A consulta agrupa os dados por professor, calcula a média de GPA de todas as turmas que ele lecionou e ordena o resultado em ordem crescente ('ASC') de GPA
query_prof_dificil <- paste(cte_gpa_stat, "
SELECT i.name, AVG(gpo.gpa) AS media_gpa FROM gpa_por_oferta gpo
INNER JOIN sections s ON gpo.course_offering_uuid = s.course_offering_uuid
INNER JOIN teachings t ON s.uuid = t.section_uuid
INNER JOIN instructors i ON t.instructor_id = i.id
GROUP BY i.name HAVING media_gpa IS NOT NULL ORDER BY media_gpa ASC;
")
prof_dificil <- dbGetQuery(conn, query_prof_dificil)
cat("RESULTADO:\n"); print(prof_dificil)RESULTADO: name media_gpa
1 JAMES D KUELBS 2.598750
2 JONATHON PETERSON 2.776515
3 XIUFENG SHAO 2.778846
4 ALEXANDER FISH 2.806624
5 KAILEI CHEN 2.808345
6 GINA OH 2.829212
7 MANJUSHA KANCHARLA 2.829212
8 MITCHELL PAUKNER 2.829212
9 XIAOWU DAI 2.829212
10 JUNGWON MUN 2.829510
11 STEPHEN WAINGER 2.831169
12 SCOTT HOTTOVY 2.845013
13 ALEXANDER KISELEV 2.846457
14 DAVID GRIFFEATH 2.851097
15 HAODA FU 2.853257
16 BROOK LUERS 2.879431
17 GREGORY SHINAULT 2.879627
18 ZHENGXIAO WU 2.879727
19 KYLE HERBET 2.889505
20 MARIA KAMENETSKY 2.889505
21 ANATOLE BECK 2.916667
22 BIN DAI 2.920796
23 TONGHAI YANG 2.928750
24 FLORIAN BERTRAND 2.936222
25 ROBERT HARRON 2.941860
26 BO HUANG 2.946662
27 JUN ZHANG 2.948128
28 GINA BENNINGER 2.950007
29 YING ZHANG 2.950291
30 LAM HO 2.951368
31 XIRAN WANG 2.953705
32 ERICA LEE DEADMAN 2.960538
33 LIN QI 2.963204
34 BEN ADAM HAALAND 2.963915
35 ELOISA D CHAVAS 2.963915
36 MIN JUNG LEE 2.963915
37 SCOTT JOSEPH HETZEL 2.963915
38 THOMAS G. KURTZ 2.964575
39 RUIFANG SONG 2.971030
40 GARY HOWARD SCHROEDER 2.972139
41 CHEN JING 2.975522
42 XIWEN MA 2.977989
43 JENNIFER NGUYEN 2.981764
44 XIAO NIE 2.982129
45 STEFFEN LEMPP 2.982948
46 HEATHER MARIE BRAZEAU 2.987785
47 PHILIP WOOD 2.991361
48 VICTOR LUO 2.992599
49 MIHAELA IFRIM 2.994867
50 SONA Z SWANSON 2.995804
51 IAN BRANSTAD RILEY 2.998924
52 STEPHEN BERG 3.000759
53 GREGORIO MORENO-FLORES 3.006926
54 SUKHENDU MEHROTRA 3.006926
55 DAVID ANDERSON 3.007687
56 ALBERTO DEL PIA 3.008475
57 HUILIN HU 3.008475
58 YUAN JIANG 3.010278
59 YALI WANG 3.010747
60 SAMUEL STECHMANN 3.011409
61 MICHAEL FERRIS 3.013265
62 YUANZHI LI 3.013867
63 YUCHEN ZHOU 3.015731
64 DONG XIA 3.019202
65 TAERI UHM 3.019504
66 XU HE 3.030121
67 KUNLING HUANG 3.030852
68 RICHARD A BRUALDI 3.031250
69 GENG LI 3.031697
70 YUJIN CHUNG 3.031870
71 LONG PHAN 3.032653
72 WAI TONG FAN 3.037341
73 JASON RICHARD SWANSON 3.037567
74 CHENXI LI 3.038674
75 TIEN VO 3.041164
76 ARNOLD MILLER 3.041521
77 VARSHA KULKARNI 3.042199
78 XINYU SONG 3.044683
79 MICHAEL GEORGE ILTIS 3.046525
80 SANGBUM CHOI 3.049892
81 ZHONGJIE YU 3.051449
82 WESLEY CHANG 3.051553
83 KEVIN PACKARD 3.053309
84 LANCINE KONATE 3.056818
85 GUN WOONG PARK 3.062425
86 SHIXUE LIU 3.062475
87 DIETRICH UHLENBROCK 3.062500
88 LEV BORISOV 3.062500
89 NICHOLAS STEPHEN KEULER 3.063624
90 SHENG WANG 3.067008
91 YOUNG MIN PARK 3.069966
92 YIFAN MEI 3.070067
93 STEVEN SAM 3.071429
94 ANDREW LESLIE 3.072120
95 DANIELE CAPPELLETTI 3.072535
96 BINGYING XIE 3.073960
97 YONGFENG WU 3.076751
98 CUIZE HAN 3.077179
99 YUAN WANG 3.078424
100 HAOYANG FAN 3.081805
101 PAUL M TERWILLIGER 3.082121
102 JOHN WILTSHIRE-GORDON 3.082207
103 RUI CHEN 3.082593
104 RUIFENG XU 3.084936
105 JEEA CHOI 3.086289
106 RICHARD A. JOHNSON 3.086767
107 SHIZHEN WANG 3.086901
108 DEYUAN JIANG 3.090588
109 SEULKEE YUN 3.094185
110 CRYSTAL CHEN 3.095842
111 ANDREJ ZLATOS 3.095877
112 DONGHYUN LEE 3.095877
113 SEHO PARK 3.096566
114 YU QIU LIU 3.096809
115 HUIKUN ZHANG 3.096972
116 JINGCI MENG 3.101283
117 CHEN CHENG 3.102190
118 ISMOR FISCHER 3.103061
119 SHI JIN 3.104167
120 LUXI CAO 3.104769
121 TIMO SEPPALAINEN 3.105690
122 FANG FANG 3.110370
123 ALEXANDER COVINGTON 3.111639
124 PERLA REYES 3.111961
125 XIN LI 3.113253
126 STEPHEN WRIGHT 3.114188
127 MIN NIU 3.117976
128 YAOGUO XIE 3.118258
129 YOUNGDEOK HWANG 3.119397
130 CHIEN-WEI CHEN 3.120651
131 JUNHEE HAN 3.122777
132 KYLE HEBERT 3.123311
133 BOWEN HU 3.124625
134 JARED BROWN 3.125739
135 DONALD PORTER 3.125820
136 JU HEE CHO 3.126176
137 SEUNGBONG HAN 3.127089
138 DUY NGUYEN 3.127230
139 QIONG ZHANG 3.127695
140 KRISTEN CYFFKA 3.129051
141 XIPEI YANG 3.129603
142 CHANHAN HSU 3.131195
143 TING-LI LIN 3.131207
144 ZUOFENG SHANG 3.131835
145 DOUGLAS M. BATES 3.132054
146 QUOC TRAN 3.132478
147 TIMOTHY IDOWU 3.134587
148 KAM-WAH TSUI 3.135666
149 ALAN HUANG 3.135890
150 THU LE 3.135890
151 CHENLIANG XU 3.137928
152 HAO TENG 3.138909
153 ZIFENG ZHAO 3.139562
154 JINGLAN LI 3.139864
155 BI CHENG WU 3.141444
156 RONGJUN ZHU 3.141444
157 YUNONG LIN 3.141444
158 SEBASTIEN ROCH 3.143769
159 CHENGNING ZHANG 3.143824
160 BENEDEK VALKO 3.144103
161 LILUN DU 3.151753
162 JOHN DAVIS 3.154961
163 MENG SONG 3.155272
164 AKICHIKA OZEKI 3.156774
165 MICHAEL HOGAN 3.158610
166 NELLIE LAUGHLIN 3.158610
167 KRISHNAKUMAR BALASUBRAMANIAN 3.161415
168 TRAM TA 3.161932
169 JIE WEI 3.165379
170 XINJIE HE 3.165379
171 XUEYAO CHEN 3.165379
172 SHENG ZHANG 3.166985
173 ZHIGUO XIAO 3.168129
174 YONGJOON KIM 3.170750
175 JIAN WU 3.172156
176 XUN ZHAO 3.172156
177 YI LI 3.174638
178 YUCHANG WU 3.175851
179 TUN LEE NG 3.175885
180 ROBERT WARDROP 3.176172
181 JIALE XU 3.177288
182 WEI ZHENG 3.178521
183 YILIN ZHANG 3.179882
184 SHANE HUBLER 3.180855
185 LU YANG 3.182515
186 MING XIE 3.183949
187 YAOYAO XU 3.184198
188 JIE ZHANG 3.184266
189 SOHEIL SADEGHI 3.184912
190 AUGUST JENSEN 3.186477
191 BIN ZHANG 3.186818
192 XINWEI DENG 3.188123
193 JUN YIN 3.190211
194 YI LIU 3.190216
195 BIN ZHU 3.191236
196 MINJING TAO 3.192533
197 RUI TANG 3.192670
198 WENWEN ZHANG 3.194143
199 YOURAN QI 3.197223
200 QI TANG 3.198726
201 CLAIRE BOBST 3.198790
202 CHAN PARK 3.198790
203 RUNGANG HAN 3.198790
204 SEAN KENT 3.198790
205 COLIN LONGHURST 3.202127
206 JINGJIANG PENG 3.204333
207 HAN CHEN 3.204471
208 SHANG WU 3.205246
209 HYEBIN SONG 3.207425
210 JOHN KANE 3.208955
211 MARIA ROJO 3.211143
212 YAN CHEN 3.211756
213 FAN YANG 3.213706
214 FREDERICK BOEHM 3.214150
215 YANG ZHAO 3.214661
216 KATHERINE GOODE 3.214854
217 HAO ZHOU 3.216016
218 NICHOLAS HENDERSON 3.218392
219 CHAOYANG YU 3.218851
220 XIN ZHANG 3.218986
221 HAO CHEN 3.219028
222 MUHONG GAO 3.219432
223 PEIGEN ZHOU 3.219780
224 GONZALO CONTADOR 3.220789
225 TAO YU 3.222944
226 KAZUHIKO SHINKI 3.226305
227 MICHAEL KUTZLER 3.227701
228 FAN GAO 3.230484
229 JIAJIE CHEN 3.232079
230 JIANCHANG HU 3.233581
231 CHELSEY GREEN 3.234988
232 CHIA-CHIEH LIN 3.235165
233 WENZHI CAO 3.237211
234 SOKOL VAKO 3.238013
235 CLAUDIA SOLIS LEMUS 3.238985
236 JUN LI 3.241528
237 HAO ZHENG 3.242036
238 RUOSI GUO 3.242110
239 SHULEI WANG 3.242617
240 REBECCA POST 3.245172
241 YI CHAI 3.246143
242 DEREK NORTON 3.246591
243 KEEGAN KORTHAUER 3.246591
244 ROBERT R MEYER 3.250000
245 SIJING LI 3.255120
246 DEBRAJ DAS 3.256619
247 QUEFENG LI 3.257292
248 ABIGAIL BENZINE 3.259891
249 XU XU 3.260706
250 BRET HANLON 3.261007
251 QIURONG CUI 3.261957
252 ANRU ZHANG 3.263386
253 XIAO GUO 3.268782
254 TONG LI 3.273505
255 DONGGYU KIM 3.274206
256 HUI WANG 3.286842
257 KEVIN HASEGAWA ENG 3.286842
258 XIAODAN WEI 3.286842
259 PAUL SAVARIAPPAN 3.295253
260 YUQING XU 3.296830
261 LILI ZHENG 3.296835
262 VICTORIA MANSFIELD 3.298793
263 LUWAN ZHANG 3.302315
264 YANNAN QIU 3.303571
265 JIWEI ZHAO 3.306842
266 JOSEP GINEBRA 3.310328
267 ZHUANG WU 3.312176
268 TZU HSIANG HUNG 3.315428
269 YONGSU LEE 3.316044
270 LIE XIONG 3.317622
271 JILI WANG 3.317977
272 XIAOPING FENG 3.318434
273 GARVESH RASKUTTI 3.319266
274 XIUYU MA 3.322746
275 MATTHEW BALLARD 3.325581
276 XINXIN YU 3.325765
277 BENJAMIN RECHT 3.330618
278 SHUYUN YE 3.331709
279 BO YANG 3.334041
280 DUZHE WANG 3.336602
281 JIE SONG 3.336770
282 LAN LUO 3.341579
283 RYAN ZEA 3.347095
284 NORBERT BINKIEWICZ 3.353447
285 XIAOMAO LI 3.361319
286 BEHZAD AALIPUR 3.365720
287 ALBRECHT KLEMM 3.368421
288 SONG WANG 3.372888
289 LEI XU 3.378728
290 DEREK BEAN 3.386328
291 SHENGJI JIA 3.387091
292 ALYSSA DIGILIO 3.389795
293 KEN ONO 3.393742
294 LIAM JOHNSTON 3.393910
295 CHENSHENG KUANG 3.397811
296 TING YE 3.407432
297 NING FAN 3.409989
298 YAZHEN WANG 3.411371
299 QING LI 3.421575
300 KARL ROHE 3.429018
301 WEI-YIN LOH 3.441321
302 ROBERT WAYNE GREEN 3.454167
303 HYUNSEUNG KANG 3.456140
304 ANQI SHI 3.459885
305 CECILE ANE 3.472430
306 MARI PALTA 3.473684
307 SHUAI CHEN 3.473684
308 BEHZAD AALIPUR HAFSHEJANI 3.475000
309 JESSE THOMAS HOLZER 3.478261
310 SUNDUZ KELES 3.491439
311 CUICUI QI 3.491995
312 COLE COOK 3.509529
313 ZHIGENG GENG 3.512411
314 GUILHERME VIEIRA NUNES LUDWIG 3.516654
315 ANNE BRUCKNER 3.516667
316 QI JIANG 3.517719
317 EDWARD ERKER 3.517980
318 BRET LARGET 3.521318
319 ERIK NORDHEIM 3.526813
320 JENNIFER BIRSTLER 3.529323
321 ZHENGJUN ZHANG 3.531262
322 RONALD GANGNON 3.532353
323 YOUNG LEE 3.533655
324 STEPHEN AARON STANHOPE 3.554733
325 JEE YEON KIM 3.571429
326 CHRISTOPHER WAGNER 3.573529
327 SHUANG HUANG 3.574468
328 GUANNAN SUN 3.579254
329 LILI LAN 3.579727
330 QIAN ZHIGUANG 3.583467
331 MURRAY CLAYTON 3.594354
332 JOHN GILLETT 3.605526
333 KJELL DOKSUM 3.609227
334 KYUNGMANN KIM 3.612258
335 SIJIAN WANG 3.613318
336 JAMES ANDERSON 3.615385
337 ZIJIAN NI 3.615741
338 ZHANG CHUNMING 3.617905
339 JUN ZHU 3.618937
340 THOMAS COOK 3.627118
341 JUNHO LEE 3.642857
342 HUAIBAO FENG 3.644737
343 MICHELLE HARRIS 3.647374
344 MARY LINDSTROM 3.661405
345 YUAN LI 3.671209
346 NORMAN DRAPER 3.680000
347 MOO K CHUNG 3.681818
348 THEVAASIINEN CHANDERENG 3.692064
349 YANBING ZHENG 3.692308
350 PAUL RATHOUZ 3.695175
351 JUN SHAO 3.695449
352 JASON P FINE 3.718750
353 JOSEPH DEUTSCH 3.722976
354 CHRISTINA M. KENDZIORSKI 3.725000
355 ADIN-CRISTIAN ANDREI 3.749432
356 KARL BROMAN 3.761584
357 RICHARD J. CHAPPELL 3.765269
358 REBECCA KOSCIK 3.772160
359 FANGFANG WANG 3.773396
360 SHAN LU 3.775413
361 MICHAEL NEWTON 3.789922
362 BRIAN YANDELL 3.799402
363 MICHAEL LIOU 3.813559
364 DAVID DEMETS 3.833333
365 LU MAO 3.910283
366 MING YUAN 3.924561
367 CHAOQUN MEI 3.937853
368 YU MENGGANG 3.939200
369 GRACE WAHBA 3.972642
370 GUANHUA CHEN 4.000000
371 SRIKANTHMADHAVAN ARAVAMUTHAN 4.000000
372 YAJUAN SI 4.000000cat("\nPERGUNTA 2b: Ranking dos professores (do mais fácil para o mais difícil)\n")
PERGUNTA 2b: Ranking dos professores (do mais fácil para o mais difícil)query_prof_facil <- paste(cte_gpa_stat, "
SELECT i.name, AVG(gpo.gpa) AS media_gpa FROM gpa_por_oferta gpo
INNER JOIN sections s ON gpo.course_offering_uuid = s.course_offering_uuid
INNER JOIN teachings t ON s.uuid = t.section_uuid
INNER JOIN instructors i ON t.instructor_id = i.id
GROUP BY i.name HAVING media_gpa IS NOT NULL ORDER BY media_gpa DESC;
")
prof_facil <- dbGetQuery(conn, query_prof_facil)
cat("RESULTADO:\n"); print(prof_facil)RESULTADO: name media_gpa
1 YAJUAN SI 4.000000
2 SRIKANTHMADHAVAN ARAVAMUTHAN 4.000000
3 GUANHUA CHEN 4.000000
4 GRACE WAHBA 3.972642
5 YU MENGGANG 3.939200
6 CHAOQUN MEI 3.937853
7 MING YUAN 3.924561
8 LU MAO 3.910283
9 DAVID DEMETS 3.833333
10 MICHAEL LIOU 3.813559
11 BRIAN YANDELL 3.799402
12 MICHAEL NEWTON 3.789922
13 SHAN LU 3.775413
14 FANGFANG WANG 3.773396
15 REBECCA KOSCIK 3.772160
16 RICHARD J. CHAPPELL 3.765269
17 KARL BROMAN 3.761584
18 ADIN-CRISTIAN ANDREI 3.749432
19 CHRISTINA M. KENDZIORSKI 3.725000
20 JOSEPH DEUTSCH 3.722976
21 JASON P FINE 3.718750
22 JUN SHAO 3.695449
23 PAUL RATHOUZ 3.695175
24 YANBING ZHENG 3.692308
25 THEVAASIINEN CHANDERENG 3.692064
26 MOO K CHUNG 3.681818
27 NORMAN DRAPER 3.680000
28 YUAN LI 3.671209
29 MARY LINDSTROM 3.661405
30 MICHELLE HARRIS 3.647374
31 HUAIBAO FENG 3.644737
32 JUNHO LEE 3.642857
33 THOMAS COOK 3.627118
34 JUN ZHU 3.618937
35 ZHANG CHUNMING 3.617905
36 ZIJIAN NI 3.615741
37 JAMES ANDERSON 3.615385
38 SIJIAN WANG 3.613318
39 KYUNGMANN KIM 3.612258
40 KJELL DOKSUM 3.609227
41 JOHN GILLETT 3.605526
42 MURRAY CLAYTON 3.594354
43 QIAN ZHIGUANG 3.583467
44 LILI LAN 3.579727
45 GUANNAN SUN 3.579254
46 SHUANG HUANG 3.574468
47 CHRISTOPHER WAGNER 3.573529
48 JEE YEON KIM 3.571429
49 STEPHEN AARON STANHOPE 3.554733
50 YOUNG LEE 3.533655
51 RONALD GANGNON 3.532353
52 ZHENGJUN ZHANG 3.531262
53 JENNIFER BIRSTLER 3.529323
54 ERIK NORDHEIM 3.526813
55 BRET LARGET 3.521318
56 EDWARD ERKER 3.517980
57 QI JIANG 3.517719
58 ANNE BRUCKNER 3.516667
59 GUILHERME VIEIRA NUNES LUDWIG 3.516654
60 ZHIGENG GENG 3.512411
61 COLE COOK 3.509529
62 CUICUI QI 3.491995
63 SUNDUZ KELES 3.491439
64 JESSE THOMAS HOLZER 3.478261
65 BEHZAD AALIPUR HAFSHEJANI 3.475000
66 SHUAI CHEN 3.473684
67 MARI PALTA 3.473684
68 CECILE ANE 3.472430
69 ANQI SHI 3.459885
70 HYUNSEUNG KANG 3.456140
71 ROBERT WAYNE GREEN 3.454167
72 WEI-YIN LOH 3.441321
73 KARL ROHE 3.429018
74 QING LI 3.421575
75 YAZHEN WANG 3.411371
76 NING FAN 3.409989
77 TING YE 3.407432
78 CHENSHENG KUANG 3.397811
79 LIAM JOHNSTON 3.393910
80 KEN ONO 3.393742
81 ALYSSA DIGILIO 3.389795
82 SHENGJI JIA 3.387091
83 DEREK BEAN 3.386328
84 LEI XU 3.378728
85 SONG WANG 3.372888
86 ALBRECHT KLEMM 3.368421
87 BEHZAD AALIPUR 3.365720
88 XIAOMAO LI 3.361319
89 NORBERT BINKIEWICZ 3.353447
90 RYAN ZEA 3.347095
91 LAN LUO 3.341579
92 JIE SONG 3.336770
93 DUZHE WANG 3.336602
94 BO YANG 3.334041
95 SHUYUN YE 3.331709
96 BENJAMIN RECHT 3.330618
97 XINXIN YU 3.325765
98 MATTHEW BALLARD 3.325581
99 XIUYU MA 3.322746
100 GARVESH RASKUTTI 3.319266
101 XIAOPING FENG 3.318434
102 JILI WANG 3.317977
103 LIE XIONG 3.317622
104 YONGSU LEE 3.316044
105 TZU HSIANG HUNG 3.315428
106 ZHUANG WU 3.312176
107 JOSEP GINEBRA 3.310328
108 JIWEI ZHAO 3.306842
109 YANNAN QIU 3.303571
110 LUWAN ZHANG 3.302315
111 VICTORIA MANSFIELD 3.298793
112 LILI ZHENG 3.296835
113 YUQING XU 3.296830
114 PAUL SAVARIAPPAN 3.295253
115 XIAODAN WEI 3.286842
116 KEVIN HASEGAWA ENG 3.286842
117 HUI WANG 3.286842
118 DONGGYU KIM 3.274206
119 TONG LI 3.273505
120 XIAO GUO 3.268782
121 ANRU ZHANG 3.263386
122 QIURONG CUI 3.261957
123 BRET HANLON 3.261007
124 XU XU 3.260706
125 ABIGAIL BENZINE 3.259891
126 QUEFENG LI 3.257292
127 DEBRAJ DAS 3.256619
128 SIJING LI 3.255120
129 ROBERT R MEYER 3.250000
130 KEEGAN KORTHAUER 3.246591
131 DEREK NORTON 3.246591
132 YI CHAI 3.246143
133 REBECCA POST 3.245172
134 SHULEI WANG 3.242617
135 RUOSI GUO 3.242110
136 HAO ZHENG 3.242036
137 JUN LI 3.241528
138 CLAUDIA SOLIS LEMUS 3.238985
139 SOKOL VAKO 3.238013
140 WENZHI CAO 3.237211
141 CHIA-CHIEH LIN 3.235165
142 CHELSEY GREEN 3.234988
143 JIANCHANG HU 3.233581
144 JIAJIE CHEN 3.232079
145 FAN GAO 3.230484
146 MICHAEL KUTZLER 3.227701
147 KAZUHIKO SHINKI 3.226305
148 TAO YU 3.222944
149 GONZALO CONTADOR 3.220789
150 PEIGEN ZHOU 3.219780
151 MUHONG GAO 3.219432
152 HAO CHEN 3.219028
153 XIN ZHANG 3.218986
154 CHAOYANG YU 3.218851
155 NICHOLAS HENDERSON 3.218392
156 HAO ZHOU 3.216016
157 KATHERINE GOODE 3.214854
158 YANG ZHAO 3.214661
159 FREDERICK BOEHM 3.214150
160 FAN YANG 3.213706
161 YAN CHEN 3.211756
162 MARIA ROJO 3.211143
163 JOHN KANE 3.208955
164 HYEBIN SONG 3.207425
165 SHANG WU 3.205246
166 HAN CHEN 3.204471
167 JINGJIANG PENG 3.204333
168 COLIN LONGHURST 3.202127
169 SEAN KENT 3.198790
170 RUNGANG HAN 3.198790
171 CHAN PARK 3.198790
172 CLAIRE BOBST 3.198790
173 QI TANG 3.198726
174 YOURAN QI 3.197223
175 WENWEN ZHANG 3.194143
176 RUI TANG 3.192670
177 MINJING TAO 3.192533
178 BIN ZHU 3.191236
179 YI LIU 3.190216
180 JUN YIN 3.190211
181 XINWEI DENG 3.188123
182 BIN ZHANG 3.186818
183 AUGUST JENSEN 3.186477
184 SOHEIL SADEGHI 3.184912
185 JIE ZHANG 3.184266
186 YAOYAO XU 3.184198
187 MING XIE 3.183949
188 LU YANG 3.182515
189 SHANE HUBLER 3.180855
190 YILIN ZHANG 3.179882
191 WEI ZHENG 3.178521
192 JIALE XU 3.177288
193 ROBERT WARDROP 3.176172
194 TUN LEE NG 3.175885
195 YUCHANG WU 3.175851
196 YI LI 3.174638
197 XUN ZHAO 3.172156
198 JIAN WU 3.172156
199 YONGJOON KIM 3.170750
200 ZHIGUO XIAO 3.168129
201 SHENG ZHANG 3.166985
202 XUEYAO CHEN 3.165379
203 XINJIE HE 3.165379
204 JIE WEI 3.165379
205 TRAM TA 3.161932
206 KRISHNAKUMAR BALASUBRAMANIAN 3.161415
207 NELLIE LAUGHLIN 3.158610
208 MICHAEL HOGAN 3.158610
209 AKICHIKA OZEKI 3.156774
210 MENG SONG 3.155272
211 JOHN DAVIS 3.154961
212 LILUN DU 3.151753
213 BENEDEK VALKO 3.144103
214 CHENGNING ZHANG 3.143824
215 SEBASTIEN ROCH 3.143769
216 YUNONG LIN 3.141444
217 RONGJUN ZHU 3.141444
218 BI CHENG WU 3.141444
219 JINGLAN LI 3.139864
220 ZIFENG ZHAO 3.139562
221 HAO TENG 3.138909
222 CHENLIANG XU 3.137928
223 THU LE 3.135890
224 ALAN HUANG 3.135890
225 KAM-WAH TSUI 3.135666
226 TIMOTHY IDOWU 3.134587
227 QUOC TRAN 3.132478
228 DOUGLAS M. BATES 3.132054
229 ZUOFENG SHANG 3.131835
230 TING-LI LIN 3.131207
231 CHANHAN HSU 3.131195
232 XIPEI YANG 3.129603
233 KRISTEN CYFFKA 3.129051
234 QIONG ZHANG 3.127695
235 DUY NGUYEN 3.127230
236 SEUNGBONG HAN 3.127089
237 JU HEE CHO 3.126176
238 DONALD PORTER 3.125820
239 JARED BROWN 3.125739
240 BOWEN HU 3.124625
241 KYLE HEBERT 3.123311
242 JUNHEE HAN 3.122777
243 CHIEN-WEI CHEN 3.120651
244 YOUNGDEOK HWANG 3.119397
245 YAOGUO XIE 3.118258
246 MIN NIU 3.117976
247 STEPHEN WRIGHT 3.114188
248 XIN LI 3.113253
249 PERLA REYES 3.111961
250 ALEXANDER COVINGTON 3.111639
251 FANG FANG 3.110370
252 TIMO SEPPALAINEN 3.105690
253 LUXI CAO 3.104769
254 SHI JIN 3.104167
255 ISMOR FISCHER 3.103061
256 CHEN CHENG 3.102190
257 JINGCI MENG 3.101283
258 HUIKUN ZHANG 3.096972
259 YU QIU LIU 3.096809
260 SEHO PARK 3.096566
261 DONGHYUN LEE 3.095877
262 ANDREJ ZLATOS 3.095877
263 CRYSTAL CHEN 3.095842
264 SEULKEE YUN 3.094185
265 DEYUAN JIANG 3.090588
266 SHIZHEN WANG 3.086901
267 RICHARD A. JOHNSON 3.086767
268 JEEA CHOI 3.086289
269 RUIFENG XU 3.084936
270 RUI CHEN 3.082593
271 JOHN WILTSHIRE-GORDON 3.082207
272 PAUL M TERWILLIGER 3.082121
273 HAOYANG FAN 3.081805
274 YUAN WANG 3.078424
275 CUIZE HAN 3.077179
276 YONGFENG WU 3.076751
277 BINGYING XIE 3.073960
278 DANIELE CAPPELLETTI 3.072535
279 ANDREW LESLIE 3.072120
280 STEVEN SAM 3.071429
281 YIFAN MEI 3.070067
282 YOUNG MIN PARK 3.069966
283 SHENG WANG 3.067008
284 NICHOLAS STEPHEN KEULER 3.063624
285 LEV BORISOV 3.062500
286 DIETRICH UHLENBROCK 3.062500
287 SHIXUE LIU 3.062475
288 GUN WOONG PARK 3.062425
289 LANCINE KONATE 3.056818
290 KEVIN PACKARD 3.053309
291 WESLEY CHANG 3.051553
292 ZHONGJIE YU 3.051449
293 SANGBUM CHOI 3.049892
294 MICHAEL GEORGE ILTIS 3.046525
295 XINYU SONG 3.044683
296 VARSHA KULKARNI 3.042199
297 ARNOLD MILLER 3.041521
298 TIEN VO 3.041164
299 CHENXI LI 3.038674
300 JASON RICHARD SWANSON 3.037567
301 WAI TONG FAN 3.037341
302 LONG PHAN 3.032653
303 YUJIN CHUNG 3.031870
304 GENG LI 3.031697
305 RICHARD A BRUALDI 3.031250
306 KUNLING HUANG 3.030852
307 XU HE 3.030121
308 TAERI UHM 3.019504
309 DONG XIA 3.019202
310 YUCHEN ZHOU 3.015731
311 YUANZHI LI 3.013867
312 MICHAEL FERRIS 3.013265
313 SAMUEL STECHMANN 3.011409
314 YALI WANG 3.010747
315 YUAN JIANG 3.010278
316 HUILIN HU 3.008475
317 ALBERTO DEL PIA 3.008475
318 DAVID ANDERSON 3.007687
319 SUKHENDU MEHROTRA 3.006926
320 GREGORIO MORENO-FLORES 3.006926
321 STEPHEN BERG 3.000759
322 IAN BRANSTAD RILEY 2.998924
323 SONA Z SWANSON 2.995804
324 MIHAELA IFRIM 2.994867
325 VICTOR LUO 2.992599
326 PHILIP WOOD 2.991361
327 HEATHER MARIE BRAZEAU 2.987785
328 STEFFEN LEMPP 2.982948
329 XIAO NIE 2.982129
330 JENNIFER NGUYEN 2.981764
331 XIWEN MA 2.977989
332 CHEN JING 2.975522
333 GARY HOWARD SCHROEDER 2.972139
334 RUIFANG SONG 2.971030
335 THOMAS G. KURTZ 2.964575
336 SCOTT JOSEPH HETZEL 2.963915
337 MIN JUNG LEE 2.963915
338 ELOISA D CHAVAS 2.963915
339 BEN ADAM HAALAND 2.963915
340 LIN QI 2.963204
341 ERICA LEE DEADMAN 2.960538
342 XIRAN WANG 2.953705
343 LAM HO 2.951368
344 YING ZHANG 2.950291
345 GINA BENNINGER 2.950007
346 JUN ZHANG 2.948128
347 BO HUANG 2.946662
348 ROBERT HARRON 2.941860
349 FLORIAN BERTRAND 2.936222
350 TONGHAI YANG 2.928750
351 BIN DAI 2.920796
352 ANATOLE BECK 2.916667
353 MARIA KAMENETSKY 2.889505
354 KYLE HERBET 2.889505
355 ZHENGXIAO WU 2.879727
356 GREGORY SHINAULT 2.879627
357 BROOK LUERS 2.879431
358 HAODA FU 2.853257
359 DAVID GRIFFEATH 2.851097
360 ALEXANDER KISELEV 2.846457
361 SCOTT HOTTOVY 2.845013
362 STEPHEN WAINGER 2.831169
363 JUNGWON MUN 2.829510
364 XIAOWU DAI 2.829212
365 MITCHELL PAUKNER 2.829212
366 MANJUSHA KANCHARLA 2.829212
367 GINA OH 2.829212
368 KAILEI CHEN 2.808345
369 ALEXANDER FISH 2.806624
370 XIUFENG SHAO 2.778846
371 JONATHON PETERSON 2.776515
372 JAMES D KUELBS 2.598750cat("\nPERGUNTA 2c: Ranking das disciplinas (da mais difícil para a mais fácil)\n")
PERGUNTA 2c: Ranking das disciplinas (da mais difícil para a mais fácil)# Agrupa por disciplina (nome e número), calcula o GPA médio e ordena de forma crescente
query_disc_dificil <- paste(cte_gpa_stat, "
SELECT c.name, c.number, AVG(gpo.gpa) as media_gpa FROM gpa_por_oferta gpo
INNER JOIN course_offerings co ON gpo.course_offering_uuid = co.uuid
INNER JOIN courses c ON co.course_uuid = c.uuid
GROUP BY c.name, c.number HAVING media_gpa IS NOT NULL ORDER BY media_gpa ASC;
")
disc_dificil <- dbGetQuery(conn, query_disc_dificil)
cat("RESULTADO:\n"); print(disc_dificil)RESULTADO: name number
1 Introduction to the Theory of Probability 431
2 Linear Programming Methods 525
3 Introduction to Probability and Mathematical Statistics I 309
4 Introductory Statistics for Engineers 224
5 Introduction to Stochastic Processes 632
6 Introduction to Statistical Methods 301
7 Introduction to Probability and Mathematical Statistics II 310
8 Introduction to Combinatorics 475
9 Introduction to Theory and Methods of Mathematical Statistics I 311
10 Introductory Applied Statistics for Engineers 324
11 An Introduction to Sample Survey Theory and Methods 411
12 Introductory Applied Statistics for the Life Sciences 371
13 Accelerated Introduction to Statistical Methods 302
14 Applied Regression Analysis 333
15 Applied Categorical Data Analysis 421
16 Introduction to Theory and Methods of Mathematical Statistics II 312
17 Introduction to Time Series 349
18 Statistical Experimental Design 424
19 Theory of Probability I 733
20 Intro to Biostats for Phamacy 441
21 Applied Multivariate Analysis 456
22 Nonlinear Optimization I 726
23 Introduction to Computational Statistics 471
24 Financial Statistics 461
25 Statistical Methods for Bioscience I 571
26 Mathematical Statistics I 609
27 Statistical Methods for Bioscience II 572
28 Statistical Methods II 602
29 Theory of Probability II 734
30 Mathematical Statistics 709
31 Statistical Methods I 601
32 Theory and Application of Regression and Analysis of Variance II 850
33 Introduction to Statistical Inference 610
34 Mathematical Statistics 710
35 Learning a Statistical Language 327
36 Theory and Application of Regression and Analysis of Variance I 849
37 Statistical Methods for Clinical Trials 641
38 Professional Skills in Data Science 627
39 Theory of Probability 831
40 Special Topics in Statistics 479
41 Introduction to Clinical Trials I 542
42 Introduction to Bayesian Decision and Control I 775
43 Generalized Linear Models 851
44 Statistical Consulting 998
45 Introduction to Biostatistics 541
46 Introduction to Biostatistical Methods for Public Health 511
47 Applied Time Series Analysis, Forecasting and Control I 701
48 Statistical Methods for Spatial Data 575
49 Statistical Computing 771
50 Theory of Probability 832
51 Multivariate Analysis I 760
52 Large Sample Theory of Statistical Inference 732
53 Special Topics in Statistics 692
54 Special Topics in Statistics 679
55 Statistical Methods for Epidemiology 642
56 Survival Analysis Theory and Methods 741
57 Introductory Nonparametric Statistics 351
58 Statistical Methods for Molecular Biology 877
59 Nonlinear Regressn Analysis 824
60 Topics in the Theory of Probability 833
61 Seminar 992
62 Statistical Model Building and Learning 840
63 Decision Trees for Multivariate Analysis 761
64 Non Parametric Statistics 809
65 Estimation of Functions from Data 860
66 Experimental Design I 803
67 Data Science Practicum 628
68 Empir Proc&Semiparmtrc Infernc 834
69 Nonparametric Statistics and Machine Learning Methods 841
70 Sample Survey Theory and Method 811
media_gpa
1 2.891568
2 2.957360
3 3.031950
4 3.039691
5 3.059488
6 3.076261
7 3.097313
8 3.117675
9 3.149073
10 3.178822
11 3.210439
12 3.223803
13 3.250943
14 3.308202
15 3.323837
16 3.329263
17 3.407680
18 3.419259
19 3.425017
20 3.426641
21 3.429645
22 3.447456
23 3.458916
24 3.475757
25 3.489562
26 3.502526
27 3.513428
28 3.520567
29 3.538027
30 3.556040
31 3.564059
32 3.595450
33 3.596234
34 3.600373
35 3.611165
36 3.611908
37 3.623389
38 3.637931
39 3.646316
40 3.654499
41 3.662425
42 3.674624
43 3.680000
44 3.695769
45 3.695793
46 3.722009
47 3.753848
48 3.753965
49 3.755939
50 3.810367
51 3.830763
52 3.830808
53 3.836684
54 3.837578
55 3.842905
56 3.855044
57 3.866667
58 3.869444
59 3.909091
60 3.931512
61 3.953487
62 3.967902
63 3.968244
64 3.971397
65 3.977381
66 3.982051
67 4.000000
68 4.000000
69 4.000000
70 4.000000cat("\nPERGUNTA 2d: Ranking das disciplinas (da mais fácil para a mais difícil)\n")
PERGUNTA 2d: Ranking das disciplinas (da mais fácil para a mais difícil)# Agrupa por disciplina, calcula o GPA médio e ordena de forma decrescente
query_disc_facil <- paste(cte_gpa_stat, "
SELECT c.name, c.number, AVG(gpo.gpa) as media_gpa FROM gpa_por_oferta gpo
INNER JOIN course_offerings co ON gpo.course_offering_uuid = co.uuid
INNER JOIN courses c ON co.course_uuid = c.uuid
GROUP BY c.name, c.number HAVING media_gpa IS NOT NULL ORDER BY media_gpa DESC;
")
disc_facil <- dbGetQuery(conn, query_disc_facil)
cat("RESULTADO:\n"); print(disc_facil)RESULTADO: name number
1 Data Science Practicum 628
2 Empir Proc&Semiparmtrc Infernc 834
3 Nonparametric Statistics and Machine Learning Methods 841
4 Sample Survey Theory and Method 811
5 Experimental Design I 803
6 Estimation of Functions from Data 860
7 Non Parametric Statistics 809
8 Decision Trees for Multivariate Analysis 761
9 Statistical Model Building and Learning 840
10 Seminar 992
11 Topics in the Theory of Probability 833
12 Nonlinear Regressn Analysis 824
13 Statistical Methods for Molecular Biology 877
14 Introductory Nonparametric Statistics 351
15 Survival Analysis Theory and Methods 741
16 Statistical Methods for Epidemiology 642
17 Special Topics in Statistics 679
18 Special Topics in Statistics 692
19 Large Sample Theory of Statistical Inference 732
20 Multivariate Analysis I 760
21 Theory of Probability 832
22 Statistical Computing 771
23 Statistical Methods for Spatial Data 575
24 Applied Time Series Analysis, Forecasting and Control I 701
25 Introduction to Biostatistical Methods for Public Health 511
26 Introduction to Biostatistics 541
27 Statistical Consulting 998
28 Generalized Linear Models 851
29 Introduction to Bayesian Decision and Control I 775
30 Introduction to Clinical Trials I 542
31 Special Topics in Statistics 479
32 Theory of Probability 831
33 Professional Skills in Data Science 627
34 Statistical Methods for Clinical Trials 641
35 Theory and Application of Regression and Analysis of Variance I 849
36 Learning a Statistical Language 327
37 Mathematical Statistics 710
38 Introduction to Statistical Inference 610
39 Theory and Application of Regression and Analysis of Variance II 850
40 Statistical Methods I 601
41 Mathematical Statistics 709
42 Theory of Probability II 734
43 Statistical Methods II 602
44 Statistical Methods for Bioscience II 572
45 Mathematical Statistics I 609
46 Statistical Methods for Bioscience I 571
47 Financial Statistics 461
48 Introduction to Computational Statistics 471
49 Nonlinear Optimization I 726
50 Applied Multivariate Analysis 456
51 Intro to Biostats for Phamacy 441
52 Theory of Probability I 733
53 Statistical Experimental Design 424
54 Introduction to Time Series 349
55 Introduction to Theory and Methods of Mathematical Statistics II 312
56 Applied Categorical Data Analysis 421
57 Applied Regression Analysis 333
58 Accelerated Introduction to Statistical Methods 302
59 Introductory Applied Statistics for the Life Sciences 371
60 An Introduction to Sample Survey Theory and Methods 411
61 Introductory Applied Statistics for Engineers 324
62 Introduction to Theory and Methods of Mathematical Statistics I 311
63 Introduction to Combinatorics 475
64 Introduction to Probability and Mathematical Statistics II 310
65 Introduction to Statistical Methods 301
66 Introduction to Stochastic Processes 632
67 Introductory Statistics for Engineers 224
68 Introduction to Probability and Mathematical Statistics I 309
69 Linear Programming Methods 525
70 Introduction to the Theory of Probability 431
media_gpa
1 4.000000
2 4.000000
3 4.000000
4 4.000000
5 3.982051
6 3.977381
7 3.971397
8 3.968244
9 3.967902
10 3.953487
11 3.931512
12 3.909091
13 3.869444
14 3.866667
15 3.855044
16 3.842905
17 3.837578
18 3.836684
19 3.830808
20 3.830763
21 3.810367
22 3.755939
23 3.753965
24 3.753848
25 3.722009
26 3.695793
27 3.695769
28 3.680000
29 3.674624
30 3.662425
31 3.654499
32 3.646316
33 3.637931
34 3.623389
35 3.611908
36 3.611165
37 3.600373
38 3.596234
39 3.595450
40 3.564059
41 3.556040
42 3.538027
43 3.520567
44 3.513428
45 3.502526
46 3.489562
47 3.475757
48 3.458916
49 3.447456
50 3.429645
51 3.426641
52 3.425017
53 3.419259
54 3.407680
55 3.329263
56 3.323837
57 3.308202
58 3.250943
59 3.223803
60 3.210439
61 3.178822
62 3.149073
63 3.117675
64 3.097313
65 3.076261
66 3.059488
67 3.039691
68 3.031950
69 2.957360
70 2.891568dbDisconnect(conn)
cat("\n--- Fim da Análise ---\nConexão com o banco de dados fechada.\n")
--- Fim da Análise ---
Conexão com o banco de dados fechada.