Assignment 08

Author

Garrett DeVoe

Go to the shared posit.cloud workspace for this class and open the assign08 project. 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.

Exercises

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(ggplot2)
db <- dbConnect(SQLite(), dbname = "Grades.sqlite")
dbSendQuery(conn = db, 
            "PRAGMA foreign_keys = ON")

<SQLiteResult>
  SQL  PRAGMA foreign_keys = ON
  ROWS Fetched: 0 [complete]
       Changed: 0

Exercise 1

Recreate the graph below showing the total students by course in Spring 2015.

TotalStuSp2015 <- dbGetQuery(conn = db,
"SELECT name, COUNT(student_id) AS total_students
FROM grades 
INNER JOIN sections USING(section_id)
WHERE year = '2015' AND semester = 'Spring'
GROUP BY name")

Warning: Closing open result set, pending rows

ggplot(TotalStuSp2015, aes(x = reorder(name, total_students), y = total_students)) +
  geom_bar(stat = "identity") +
  labs(title = "Total students by course, Spring 2015",
       x = "Section",
       y = "Number of students") +
  theme_minimal()

Exercise 2

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.

dbGetQuery(conn = db,
"SELECT name, section_id, year, semester, COUNT(student_id)
FROM grades 
INNER JOIN sections USING(section_id)
WHERE year = '2015' 
GROUP BY section_id
ORDER BY 
    CASE 
        WHEN semester = 'Fall' THEN 1
        WHEN semester = 'Spring' THEN 2
        ELSE 3
        END
") |>
  gt() |>
  cols_label(
    name = md("**Name**"),
    section_id = md("**Section ID**"),
    year = md("**Year**"),
    semester = md("**Semester**"),
    "COUNT(student_id)" = md("**Enrollments**"))

Name	Section ID	Year	Semester	Enrollments
MBA 676	38737	2015	Fall	33
BUS 377	68813	2015	Fall	36
MBA 676	86362	2015	Fall	39
BUS 345	25822	2015	Spring	31
MBA 674	29369	2015	Spring	24
MBA 674	42666	2015	Spring	40

Exercise 3

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().

Avg2015 <- dbGetQuery(conn = db,
"SELECT name AS section_name, AVG(final_avg) AS avg_final_grade
FROM grades
INNER JOIN sections USING(section_id)
WHERE year = '2015' 
GROUP BY name")

ggplot(Avg2015, aes(x = avg_final_grade, y = section_name)) +
  geom_col(fill = "blue") +
  labs(
    title = "Average final grade by section, 2015",
    x = "Average final grade",
    y = "Section"
  ) +
  theme_minimal()

Exercise 4

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.

dbGetQuery(conn = db,
"SELECT name, student_id, last_name, first_name, final_avg
    FROM students
    INNER JOIN grades USING(student_id)
    INNER JOIN sections USING(section_id)
    WHERE name = 'MBA 674'
    AND year = 2015 
    AND semester = 'Spring'
    AND final_avg < 65
") |>
  gt() |>
  cols_label(
    name = md("**Course**"),
    student_id = md("**Student ID**"),
    last_name = md("**Last Name**"),
    first_name = md("**First Name**"),
    final_avg = md("**Final Avg.**"))

Course	Student ID	Last Name	First Name	Final Avg.
MBA 674	9553576	Garcia	Daniel	64.05
MBA 674	7352157	Gonzales	Kyrie	62.00
MBA 674	6106351	Middleton	Sheridan	63.58
MBA 674	7237806	Fletcher	Vicky	63.56
MBA 674	7197441	Brierley	Sergio	62.01

Submission

To submit your assignment:

Change the author name to your name in the YAML portion at the top of this document
Render your document to html and publish it to RPubs.
Submit the link to your Rpubs document in the Brightspace comments section for this assignment.
Click on the “Add a File” button and upload your .qmd file for this assignment to Brightspace.

Grading Rubric

Item (percent overall)	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.