Open the assign08.qmd file and complete the exercises.
The Grades.sqlite file is preloaded into your working directory. In case there are any issues, you can also download it if you need to. It is up to you how much you want to do directly in SQL versus using R to complete the exercises below. Note: you will receive deductions for not using tidyverse syntax when applicable in this assignment. That includes the use of filter, mutate, and the up-to-date pipe operator |>.
The Grading Rubric is available at the end of this document.
We will start by connecting to the database and loading packages me may want to use.
library(tidyverse)
library(DBI)
library(RSQLite)
library(gt)
library(gtExtras)
db <- dbConnect(SQLite(), dbname = "Grades.sqlite")
dbSendQuery(conn = db,
"PRAGMA foreign_keys = ON")<SQLiteResult>
SQL PRAGMA foreign_keys = ON
ROWS Fetched: 0 [complete]
Changed: 0Recreate the graph below showing the total students by course in Spring 2015.
enrollment <- dbGetQuery(conn = db,
"SELECT name, section_id, COUNT(student_id) AS student_count
FROM grades
INNER JOIN sections USING(section_id)
WHERE year = 2015 AND (name = 'BUS 345' OR name = 'MBA 674')
GROUP BY name")Warning: Closing open result set, pending rowshead(enrollment) name section_id student_count
1 BUS 345 25822 31
2 MBA 674 42666 64enrollment |>
ggplot(aes(x = name, y = student_count)) +
geom_col() +
labs(title = "Total students by course, Spring 2015",
x = "Section",
y = "Number of Students") +
theme_minimal()
Show enrollments by section for the entire year 2015. Make sure you include year, semester, course name, section_id and the number of students in each section. Arrange the table by semester so that all of the Fall sections are listed first.
section_enrollment <- dbGetQuery(conn = db,
"SELECT year, semester, name, section_id,
COUNT(student_id) AS student_count
FROM grades
INNER JOIN sections USING(section_id)
WHERE year = 2015
GROUP BY year, semester, name, section_id")
head(section_enrollment) year semester name section_id student_count
1 2015 Fall BUS 377 68813 36
2 2015 Fall MBA 676 38737 33
3 2015 Fall MBA 676 86362 39
4 2015 Spring BUS 345 25822 31
5 2015 Spring MBA 674 29369 24
6 2015 Spring MBA 674 42666 40section_enrollment |>
gt() |>
cols_label(
year = fontawesome::fa("calendar-days"),
semester = md("**Semester**"),
name = md("**Course**"),
section_id = md("**Section**"),
student_count = md("**Enrollment**")) |>
tab_header(title = md("**2015 Enrollment by Section**")) |>
tab_options(heading.align = "left") |>
cols_align(align = "left") |>
opt_stylize(4) |>
gt_theme_538()| 2015 Enrollment by Section | ||||
|---|---|---|---|---|
| Semester | Course | Section | Enrollment | |
| 2015 | Fall | BUS 377 | 68813 | 36 |
| 2015 | Fall | MBA 676 | 38737 | 33 |
| 2015 | Fall | MBA 676 | 86362 | 39 |
| 2015 | Spring | BUS 345 | 25822 | 31 |
| 2015 | Spring | MBA 674 | 29369 | 24 |
| 2015 | Spring | MBA 674 | 42666 | 40 |
Recreate the graph below showing average final grade by section for 2015. The vertical red line showing the final average across all sections for the year is added using geom_vline().
final_avg_grade <- dbGetQuery(conn = db,
"SELECT name || '-' || section_id AS section_label,
AVG(grades.final_avg) AS avg_final_grade
FROM grades
INNER JOIN sections USING(section_id)
WHERE year = 2015
GROUP BY section_label")
head(final_avg_grade) section_label avg_final_grade
1 BUS 345-25822 80.19484
2 BUS 377-68813 80.00222
3 MBA 674-29369 77.42125
4 MBA 674-42666 79.21325
5 MBA 676-38737 77.63091
6 MBA 676-86362 78.36974final_avg_grade |>
mutate(overall_avg = mean(avg_final_grade, na.rm = TRUE)) |>
ggplot(aes(x = avg_final_grade, y = section_label)) +
geom_col(fill = "blue") +
geom_vline(aes(xintercept = overall_avg), color = "red", size = 0.5) +
labs(title = "Average final grade by section, 2015",
x = "Average final grade",
y = "Section",
caption = "Red line is the overall average for the year across all sections") +
theme_minimal()Warning: Using `size` aesthetic for lines was deprecated in ggplot2 3.4.0.
ℹ Please use `linewidth` instead.
Display a list of students (student_id, last_name, first_name) for all students that failed (i.e., final_avg < 65) MBA 674 in the Spring of 2015.
MBA674_FAIL <- dbGetQuery(conn = db,
"SELECT student_id, last_name, first_name, final_avg
FROM grades
INNER JOIN sections USING(section_id)
INNER Join students USING(student_id)
WHERE name = 'MBA 674'
AND semester = 'Spring'
AND year = 2015
AND final_avg < 65")
head(MBA674_FAIL) student_id last_name first_name final_avg
1 9553576 Garcia Daniel 64.05
2 7352157 Gonzales Kyrie 62.00
3 6106351 Middleton Sheridan 63.58
4 7237806 Fletcher Vicky 63.56
5 7197441 Brierley Sergio 62.01MBA674_FAIL |>
gt() |>
cols_label(
student_id = md("**Student ID**"),
last_name = md("**Last Name**"),
first_name = md("**First Name**"),
final_avg = md("**Final Grade**")) |>
tab_header(title = md("**MBA 674 Spring 2015**"),
subtitle = md("**Students Not Passing**")) |>
cols_align(align = "left") |>
opt_stylize(4) |>
gt_theme_538()| MBA 674 Spring 2015 | |||
|---|---|---|---|
| Students Not Passing | |||
| Student ID | Last Name | First Name | Final Grade |
| 9553576 | Garcia | Daniel | 64.05 |
| 7352157 | Gonzales | Kyrie | 62.00 |
| 6106351 | Middleton | Sheridan | 63.58 |
| 7237806 | Fletcher | Vicky | 63.56 |
| 7197441 | Brierley | Sergio | 62.01 |
To submit your assignment:
| Item (percent overall) |
100% - flawless | 67% - minor issues | 33% - moderate issues | 0% - major issues or not attempted |
|---|---|---|---|---|
| Document formatting: correctly implemented instructions (8%) |
||||
| Exercises - 21% each (84% ) |
||||
| Submitted properly to Brightspace (8%) |
NA | NA | You must submit according to instructions to receive any credit for this portion. |