Loading the libraries

library(DBI)
library(RSQLite)
library(tidyverse)

Loading the data

athlete_events <- readr::read_csv("athlete_events.csv")
host_city_locations <- readr::read_csv("host_city_locations.csv")

Creating a connection to SQLite database

olympics <- dbConnect(RSQLite::SQLite(), "olympics.db")
copy_to(olympics,athlete_events)
copy_to(olympics,host_city_locations)
dbListTables(olympics)
## [1] "athlete_events"      "host_city_locations" "sqlite_stat1"       
## [4] "sqlite_stat4"

Question 1: How many records are in athlete_events?

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

Question 2: How many different athletes competed in Olympics held in London?

SELECT COUNT(DISTINCT Name)
FROM athlete_events
WHERE City = 'London'
1 records
COUNT(DISTINCT Name)
16924

Question 3: Which Olympics host city is furthest north?

So the question is asking the which city is the furthest north and that means, the city with the highest latitude.

SELECT City
FROM host_city_locations
ORDER BY CAST("lat" AS NUMERIC) DESC
LIMIT 1
1 records
City
Lillehammer

Question 4: What is list of host cities (with no duplicates)

SELECT DISTINCT City
FROM host_city_locations
Displaying records 1 - 10
City
Athina
Paris
St. Louis
London
Stockholm
Antwerpen
Chamonix
Amsterdam
Sankt Moritz
Los Angeles

Exporting the results to the CSV

host_cities <- dbGetQuery(olympics,
                          "SELECT DISTINCT City
                          FROM host_city_locations")
write.csv(host_cities,"host_cities_Thiloni.csv")

Disconnecting the query

dbDisconnect(olympics)