Assignment 08

Author

Colby Chavarie

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

spring_2015_courses <- dbGetQuery(db, "
  SELECT s.name AS course_name, COUNT(g.student_id) AS student_count
  FROM grades g
  JOIN sections s ON g.section_id = s.section_id
  WHERE s.year = 2015 AND s.semester = 'Spring'
  GROUP BY s.name
  ORDER BY student_count DESC
")
Warning: Closing open result set, pending rows
spring_2015_courses |> 
  ggplot(aes(x = reorder(course_name, -student_count), y = student_count)) +
  geom_col() +
  labs(title = "Total Students by Course (Spring 2015)",
       x = "Course",
       y = "Number of Students")

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.

enrollments_2015 <- dbGetQuery(db, "
  SELECT 
    s.year,
    s.semester,
    s.name AS course_name,
    s.section_id,
    COUNT(g.student_id) AS student_count
  FROM grades g
  JOIN sections s ON g.section_id = s.section_id
  WHERE s.year = 2015
  GROUP BY s.year, s.semester, s.name, s.section_id
  ORDER BY 
    CASE 
        WHEN s.semester = 'Fall' THEN 1
        WHEN s.semester = 'Spring' THEN 2
        ELSE 3
    END,
    s.name
")

enrollments_2015 |> 
  gt() |> 
  tab_header(title = "Enrollments by Section - 2015")
Enrollments by Section - 2015
year semester course_name section_id student_count
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

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

avg_grades <- dbGetQuery(db, "
  SELECT 
    s.section_id,
    s.name AS course_name,
    s.semester,
    s.year,
    ROUND(AVG(g.final_avg), 2) AS avg_final_grade
  FROM grades g
  JOIN sections s ON g.section_id = s.section_id
  WHERE s.year = 2015
  GROUP BY s.section_id, s.name, s.semester, s.year
")

overall_avg <- dbGetQuery(db, "
  SELECT ROUND(AVG(final_avg), 2) AS overall_avg
  FROM grades g
  JOIN sections s ON g.section_id = s.section_id
  WHERE s.year = 2015
")$overall_avg

avg_grades |> 
  ggplot(aes(x = reorder(section_id, avg_final_grade), y = avg_final_grade)) +
  geom_col(fill = "steelblue") +
  geom_hline(yintercept = overall_avg, color = "red", linetype = "dashed") +
  labs(title = "Average Final Grade by Section (2015)",
       x = "Section ID",
       y = "Average Final Grade")

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.

failed_students <- dbGetQuery(db, "
  SELECT stu.student_id, stu.last_name, stu.first_name
  FROM grades g
  JOIN sections s ON g.section_id = s.section_id
  JOIN students stu ON g.student_id = stu.student_id
  WHERE s.year = 2015 AND s.semester = 'Spring'
    AND s.name = 'MBA 674' AND g.final_avg < 65
")

failed_students |> 
  gt() |> 
  tab_header(title = "Students Who Failed MBA 674 (Spring 2015)")
Students Who Failed MBA 674 (Spring 2015)
student_id last_name first_name
9553576 Garcia Daniel
7352157 Gonzales Kyrie
6106351 Middleton Sheridan
7237806 Fletcher Vicky
7197441 Brierley Sergio

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