Assignment2A_CodeBase

Assignment 2A

The goal of this assignment is to get us familiar with use of SQL, and linking R to SQL. Preliminary work to this was creating a survey of movies for my friends to fill out, that I could then upload into a SQL database to do further work on. This survey contains ratings from 1-5 for all movies with an additional option to rate the movie as “did not see”.

The Code

For the SQL side of this work, I was able to use the postgres GUI to do all of my work, but I extrapolated the SQL from there. I created 3 different documents to hold the native SQL code that was executed behind the GUI.

This was the query for building the table:

”

– Table: public.Movies

– DROP TABLE IF EXISTS public.”Movies”;

CREATE TABLE IF NOT EXISTS public.”Movies” ( “Timestamp” text COLLATE pg_catalog.”default”, “The Odyssey” text COLLATE pg_catalog.”default”, “Dune 2” text COLLATE pg_catalog.”default”, “Minecraft Movie” text COLLATE pg_catalog.”default”, “Barbie” text COLLATE pg_catalog.”default”, “Backrooms” text COLLATE pg_catalog.”default”, “Five Nights at Freddys 2” text COLLATE pg_catalog.”default”, “““““” text COLLATE pg_catalog.”default”, “““““2” text COLLATE pg_catalog.”default” )

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.”Movies” OWNER to postgres;

”

This was the Query for importing the data:

”

““\copy public.”Movies”(“Timestamp”, “The Odyssey”, “Dune 2”, “Minecraft Movie”, “Barbie”, “Backrooms”, “Five Nights at Freddys 2”, ““““““,”““““2”) FROM ‘C:/Users/Nick/DOWNLO1/UNTITL1.CSV/UNTITL~1.CSV’ WITH(FORMAT csv, DELIMITER ‘,’, QUOTE ’“‘, ESCAPE’’’’);”“”

”

This was the Query for exporting the data:

”

SELECT * FROM public.”Movies” LIMIT 100

”

I will then load that data into this file using R:

Movies <- read.csv("Movies_raw.csv")

As you can see this data is pretty messy, so I’m going to clean it up a little before calling this done. I need to remove the first row and change the columns to not all say “the odyssey”. weird anomaly from the google forms.

library(tidyverse)
Warning: package 'tidyverse' was built under R version 4.5.3
Warning: package 'ggplot2' was built under R version 4.5.3
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.2.0     ✔ readr     2.2.0
✔ forcats   1.0.1     ✔ stringr   1.6.0
✔ ggplot2   4.0.3     ✔ tibble    3.3.1
✔ lubridate 1.9.5     ✔ tidyr     1.3.2
✔ purrr     1.2.1     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
Movies <- Movies[-1,]
Movies <- rename(Movies,
                "The Odyssey" = The.Odyssey..The.Odyssey.,
                "Dune 2" = The.Odyssey..Dune.2.,
                "Minecraft Movie" = The.Odyssey..Minecraft.Movie.,
                "Barbie" = The.Odyssey..Barbie.,
                "Backrooms" = The.Odyssey..Backrooms.,
                "Five Nights at Freddys 2" = The.Odyssey..Five.Nights.at.Freddy.s.2.)
Movies <- Movies |>
  select(Timestamp, `The Odyssey`, `Dune 2`, `Minecraft Movie`, Barbie, Backrooms, `Five Nights at Freddys 2`)
Movies
                    Timestamp The Odyssey      Dune 2 Minecraft Movie
2   2026/09/10 7:26:19 AM AST 5 - Amazing 5 - Amazing          3 - Ok
3   2026/09/10 8:43:32 AM AST           2 did not see     did not see
4   2026/09/10 8:53:03 AM AST 5 - Amazing      3 - Ok               2
5   2026/09/10 9:08:15 AM AST 5 - Amazing      3 - Ok               2
6  2026/09/10 10:21:28 AM AST did not see did not see          3 - Ok
7  2026/09/10 10:51:44 AM AST           2      3 - Ok          3 - Ok
8  2026/09/10 10:54:53 AM AST      3 - Ok      3 - Ok               4
9  2026/09/10 10:58:26 AM AST                       4          3 - Ok
10 2026/09/10 11:01:48 AM AST did not see did not see          3 - Ok
11 2026/09/10 11:53:36 AM AST           4           2         1 - Bad
        Barbie   Backrooms Five Nights at Freddys 2
2  5 - Amazing did not see                  1 - Bad
3  5 - Amazing did not see              did not see
4  5 - Amazing      3 - Ok                        2
5            4           4                   3 - Ok
6       3 - Ok did not see              did not see
7       3 - Ok           2                        2
8       3 - Ok           4                   3 - Ok
9            4           4                        4
10           4      3 - Ok                  1 - Bad
11           2           2                  1 - Bad

Conclusion

Overall, This assignment was fairly straightforward mainly due to the use of postgres’s user friendly GUI. This allowed me to have a low code/no code approach to importing and exporting data, but for this assignment it is still valuable to know what’s happening on the back end to allow that. I did not directly connect to SQL from this R document as you can see above, but I was able to import the .csv, and I included the raw SQL above for how you would do this if you were connected.