Olympics SQL Database

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

A

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

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

B

1) Number of records in athlete_events

SELECT COUNT (*) AS totalrecords
FROM athlete_events

1 records

totalrecords
271116

Source: https://sqlserverguides.com/sql-get-number-of-rows-in-table/

2) How many different athletes in London

SELECT COUNT (Name) AS londonathletes
FROM athlete_events
WHERE City = "London"

1 records

londonathletes
22426

3) Most Northern host city

SELECT * FROM host_city_locations
ORDER BY lat DESC

Displaying records 1 - 10

Year	Season	City	lon	lat
1994	Winter	Lillehammer	10.4662306	61.11527
1952	Summer	Helsinki	24.9383791	60.16986
1952	Winter	Oslo	10.7522454	59.91387
1912	Summer	Stockholm	18.0685808	59.32932
1956	Summer	Stockholm	18.0685808	59.32932
1980	Summer	Moskva	37.6172999	55.75583
1936	Summer	Berlin	13.4049540	52.52001
1928	Summer	Amsterdam	4.8951679	52.37022
1908	Summer	London	-0.1277583	51.50735
1948	Summer	London	-0.1277583	51.50735

Lillehammer

4) List of host cities with no duplicates

query <- dbGetQuery(olympics.db, '
           SELECT DISTINCT City 
FROM host_city_locations
           ')
write_csv(query, "host_cities_JulianBeckert.csv")