Week 2A: SQL and R — Movie Ratings

Setup

# Install once (Console) if needed:
# install.packages(c("DBI","RMariaDB","dplyr","readr"))

library(DBI)
library(RMariaDB)
library(dplyr)
library(readr)

Connect to MySQL and list tables

con <- dbConnect(
  MariaDB(),
  host = "127.0.0.1",
  port = 3306,
  user = "root",
  password = "Ddik70enujmlk*", 
  dbname = "movie_ratings"        
)

# Quick check
dbListTables(con)

## [1] "ratings"

Bring the table from MySQL into an R data frame

ratings_df <- tbl(con, “ratings”) |> collect()

Peek at structure and a few rows

glimpse(ratings_df) head(ratings_df, 10)

Write a CSV copy to your project folder

write_csv(ratings_df, “ratings_export.csv”)

Confirm the file exists

file.exists(“ratings_export.csv”)

Average rating per movie (and count of ratings)

ratings_summary <- ratings_df |> group_by(movie) |> summarize(avg_rating = mean(rating), n = n(), .groups = “drop”) |> arrange(desc(avg_rating))

ratings_summary

ratings_by_rater <- ratings_df |> group_by(name) |> summarize(avg_rating = mean(rating), n = n(), .groups = “drop”) |> arrange(desc(avg_rating))

ratings_by_rater

dbDisconnect(con)