Assignment 2. Part a. SQL and R - Movie ratings analysis.

Author

Maxim Arisov

Published

September 12, 2026

SQL and R - Movie Ratings Analysis

Introduction. Approach

Creation of SQL Database using the PostgreSQL

For this assignment, I collected movie ratings from different users. The ratings are from 1 to 5. I used three tables: users, movies, and ratings. The ratings table connects each user with the movies they rated. For this particular assignment 2, part a, I will create the SQL Database based on the provided instructions (using CREATE TABLE, etc.). I will show the creation of data in the Excel sheets, first, and then transferring it to CSV files, and eventually uploading to the PostgreSQL.

Creation of CSV file from a SQL Query. R use.

I will then create another CSV files from PostgreSQL Query and will use it in R studio to count ratings per user and movie, compute average ratings, identify and clean any missing values, and calculating means and counts. Dplyr will be implemented during coding process. First, I stored the data using SQL. I used separate tables to keep the data organized. Second, I loaded the data into R. I combined the tables using user_id and movie_id. Then, I used R to check and analyze the data. I calculated the average rating for each movie and the number of ratings for each movie. For the assignment 2a, I also checked for missing ratings. I kept missing ratings as NA because a missing value means the user did not give a rating.

Code Base

This project analyses movie ratings obtained from participants. The data is stored using users, movies, and ratings.

Code

movies <- read.csv("https://raw.githubusercontent.com/Maximus2485/Assignment-2.-part-a.-movie-ratings/refs/heads/main/movies.updated.csv")
ratings <- read.csv("https://raw.githubusercontent.com/Maximus2485/Assignment-2.-part-a.-movie-ratings/refs/heads/main/movie%20ratings.updated.csv")
users <- read.csv("https://raw.githubusercontent.com/Maximus2485/Assignment-2.-part-a.-movie-ratings/refs/heads/main/users.%20updated.csv")

head(movies)

  movie_id        title
1        1   La La Land
2        2      Robocop
3        3 Harry Potter
4        4   Terminator
5        5       Shreck
6        6    Star Wars

Code

head(ratings)

  user_id movie_id rating
1       4        4      3
2       1        5      5
3       2        1      5
4       5        2     NA
5       3        3      4
6       4        2      2

Code

head(users)

  user_id        name
1       1 Nobik Dukes
2       2      Han Ar
3       3   Brian Law
4       4    Fedor Zu
5       5  Eren South

Lets install the SQL packages now

Code

library(DBI)

Warning: package 'DBI' was built under R version 4.5.2

Code

library(RSQLite)

Warning: package 'RSQLite' was built under R version 4.5.2

Code

con <- dbConnect(SQLite(), "movie_ratings.db")

Lets connect to SQLite

Code

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), "movie_ratings.db")

dbIsValid(con)

[1] TRUE

Lets create SQL tables in SQLite

Code

CREATE TABLE IF NOT EXISTS users1 (
user_id INT PRIMARY KEY,
name TEXT
);

Code

CREATE TABLE IF NOT EXISTS movies1 (
movie_id INT PRIMARY KEY,
title TEXT
);

Code

CREATE TABLE IF NOT EXISTS ratings1 (
user_id INT, movie_id INT, rating INT,
FOREIGN KEY (user_id) REFERENCES users (user_id),
FOREIGN KEY (movie_id) REFERENCES movies(movie_id)
);

Lets run a command to see whether the tables were created

Code

DBI::dbListTables(con)

[1] "movies1"  "ratings1" "users1"

Lets check now the data variables

Code

DBI::dbListFields(con, "movies1")

[1] "movie_id" "title"

Code

DBI::dbListFields(con, "users1")

[1] "user_id" "name"

Code

DBI::dbListFields(con, "ratings1")

[1] "user_id"  "movie_id" "rating"

Lets populate SQL tables

Code

dbWriteTable(con, "users1", users, overwrite = TRUE)
dbWriteTable(con, "movies1", movies, overwrite = TRUE)
dbWriteTable(con, "ratings1", ratings, overwrite = TRUE)

Lets check missing ratings in SQL

Code

SELECT *
FROM ratings1 
WHERE rating is NULL

9 records
user_id	movie_id	rating
5	2	NA
5	4	NA
1	3	NA
4	3	NA
1	2	NA
3	2	NA
5	5	NA
1	6	NA
5	6	NA

Lets join the 3 tables with SQL

Code

SELECT
    u.user_id,
    u.name,
    m.movie_id,
    m.title,
    r.rating
FROM ratings1 AS r
JOIN users1 AS u
    ON r.user_id = u.user_id
JOIN movies1 AS m
    ON r.movie_id = m.movie_id;

Displaying records 1 - 10
user_id	name	movie_id	title	rating
4	Fedor Zu	4	Terminator	3
1	Nobik Dukes	5	Shreck	5
2	Han Ar	1	La La Land	5
5	Eren South	2	Robocop	NA
3	Brian Law	3	Harry Potter	4
4	Fedor Zu	2	Robocop	2
1	Nobik Dukes	1	La La Land	5
2	Han Ar	5	Shreck	4
5	Eren South	4	Terminator	NA
4	Fedor Zu	1	La La Land	3

Now, Lets bring the SQL results into the R

Code

movie_data <- DBI::dbGetQuery(con,
                              "SELECT
    u.user_id,
    u.name,
    m.movie_id,
    m.title,
    r.rating
FROM ratings1 AS r
JOIN users1 AS u
    ON r.user_id = u.user_id
JOIN movies1 AS m
    ON r.movie_id = m.movie_id;
"
                              )
head(movie_data)

  user_id        name movie_id        title rating
1       4    Fedor Zu        4   Terminator      3
2       1 Nobik Dukes        5       Shreck      5
3       2      Han Ar        1   La La Land      5
4       5  Eren South        2      Robocop     NA
5       3   Brian Law        3 Harry Potter      4
6       4    Fedor Zu        2      Robocop      2

Lets analyse the data in R

Code

#Lest check the average rating for each movie (mean)
aggregate(
  rating ~ title, 
  data = movie_data,
  FUN = mean,
  na.rm = TRUE
)

         title   rating
1 Harry Potter 4.666667
2   La La Land 3.000000
3      Robocop 1.500000
4       Shreck 4.500000
5    Star Wars 3.666667
6   Terminator 2.750000

Code

#Lets check and find missing ratings
sum(is.na(movie_data$rating))

[1] 9

Code

#Lets find number of ratings for each movie
aggregate(
  rating ~ title, 
  data = movie_data,
  FUN = function(x) sum(!is.na(x))
)

         title rating
1 Harry Potter      3
2   La La Land      5
3      Robocop      2
4       Shreck      4
5    Star Wars      3
6   Terminator      4

Code

#Lets check number of ratings for user
aggregate(
  rating ~ name,
  data = movie_data,
  FUN = function(x) sum(!is.na(x))
)

         name rating
1   Brian Law      5
2  Eren South      2
3    Fedor Zu      5
4      Han Ar      6
5 Nobik Dukes      3

Conclusion

During this assignment 2. part a, I stored movie rating data in a SQL database and used SQL subsequently to join the tables. Then, I loaded the obtained data into the R and analyzed the ratings. At the end, I checked for missing values and calculated the average rating, number of ratings for each movie.

Use of resources

The R for Data Science (2e) in addition with Gemini AI were used to find some issues and shortcomings.

GitHub raw data is available at the links below:

https://raw.githubusercontent.com/Maximus2485/Assignment-2.-part-a.-movie-ratings/refs/heads/main/movie%20ratings.updated.csv

https://raw.githubusercontent.com/Maximus2485/Assignment-2.-part-a.-movie-ratings/refs/heads/main/movies.updated.csv

https://raw.githubusercontent.com/Maximus2485/Assignment-2.-part-a.-movie-ratings/refs/heads/main/users.%20updated.csv

References

Google DeepMind. (2025). Gemini 3 Flash [Large language model].
[https://gemini.google.com.](https://gemini.google.com/) Accessed September 12th, 2026.

Wickham, H., Çetinkaya-Rundel, M., & Grolemund, G. (2023). R for data science (2nd ed.). O’Reilly Media. https://r4ds.hadley.nz/