Load libraries

library(tidyverse)
library(RSQLite)

Load data

athlete_events <- read_csv("https://raw.githubusercontent.com/rgriff23/Olympic_history/refs/heads/master/data/athlete_events.csv")
host_city_locations <- read_csv("https://raw.githubusercontent.com/rgriff23/Olympic_history/refs/heads/master/data/host_city_locations.csv")

Create the database

olympics <- dbConnect(SQLite(), ":memory:")
copy_to(olympics, athlete_events)
copy_to(olympics, host_city_locations)

SQL Queries

1. How many records are in athlete_events?

SELECT COUNT(*)
FROM athlete_events
1 records
COUNT(*)
271116

2. How many different athletes competed in Olympics held in London?

SELECT COUNT(DISTINCT Name)
FROM athlete_events
WHERE City LIKE "london"
1 records
COUNT(DISTINCT Name)
16924

3. Which Olympics host city is furthest north?

SELECT city, MAX(lat)
FROM host_city_locations
1 records
City MAX(lat)
Lillehammer 61.11527

4. What is the list of host cities (with no duplicates)?

SELECT DISTINCT city 
FROM host_city_locations
head(host_cities)
##        City
## 1    Athina
## 2     Paris
## 3 St. Louis
## 4    London
## 5 Stockholm
## 6 Antwerpen
write_csv(host_cities, "host_cities_SamR.csv")

Disconnect from database

dbDisconnect(olympics)