hw6

library(DBI)
library(RSQLite)

## Warning: package 'RSQLite' was built under R version 4.4.3

library(readr)

athlete_events <- read_csv("athlete_events.csv")

## Rows: 271116 Columns: 15
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (10): Name, Sex, Team, NOC, Games, Season, City, Sport, Event, Medal
## dbl  (5): ID, Age, Height, Weight, Year
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

host_city_locations <- read_csv("host_city_locations.csv")

## Rows: 52 Columns: 5
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (2): Season, City
## dbl (3): Year, lon, lat
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

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

dbWriteTable(con, "athlete_events", athlete_events, overwrite = TRUE)

dbWriteTable(con, "host_city_locations", host_city_locations, overwrite = TRUE)

dbListTables(con)

## [1] "athlete_events"      "host_city_locations"

ans1 <- dbGetQuery(con, "
SELECT COUNT(*) AS total_records
FROM athlete_events;
")

ans2 <- dbGetQuery(con, "
SELECT COUNT(DISTINCT Name) AS athletes_in_london
FROM athlete_events
WHERE City = 'London';
")

ans3 <- dbGetQuery(con, "
  SELECT City 
  FROM host_city_locations 
  WHERE lat = (SELECT MAX(CAST(lat AS FLOAT)) FROM host_city_locations)
")

ans4 <- dbGetQuery(con, "SELECT DISTINCT City FROM athlete_events ORDER BY City")