HW Olympics SQL

HW Olympics SQL Assignment

Zachary Griffin, Data 205

Load Libraries

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

Load data

setwd("~/Schol Stuff/Montgomery College 2025/Data 205 Capstone")
athlete_events = read_csv("athlete_events.csv")
host_city_locations = read_csv("host_city_locations.csv")

Part A: Create the database:

Create a SQLite database called olympics.db. Inside this database you must create two tables, one for each dataset. The table created from athlete_events.csv must be named athlete_events, and the table created from host_city_locations.csv must be named host_city_locations. Both tables must be stored inside the same database file olympics.db.

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

Part B: SQL queries

Write SQL queries to answer the following questions.

How many records are in athlete_events?

SELECT COUNT(*) FROM athlete_events;

1 records

COUNT(*)
271116

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

Which Olympics host city is furthest north? You might have to use CAST or CONVERT to be able to work with the data in the right way. You can answer this question either by creating one query with a subquery, or by creating two different queries and executing them one after the other.

–See if the latitute values are numeric.

SELECT DISTINCT typeof(lat) FROM host_city_locations;

1 records

typeof(lat)
real

They are, so find the city with the highest positive value.

SELECT City FROM host_city_locations
ORDER BY lat DESC
LIMIT 1

1 records

City
Lillehammer

What is the list of host cities (with no duplicates)? After obtaining the result, export the output of Question 4 query as a CSV file named host_cities_YourName.csv.

cities <- dbGetQuery(olympics.db, '
SELECT DISTINCT City FROM host_city_locations
ORDER BY City ASC')

cities

                     City
1             Albertville
2               Amsterdam
3               Antwerpen
4                  Athina
5                 Atlanta
6               Barcelona
7                 Beijing
8                  Berlin
9                 Calgary
10               Chamonix
11      Cortina d'Ampezzo
12 Garmisch-Partenkirchen
13               Grenoble
14               Helsinki
15              Innsbruck
16            Lake Placid
17            Lillehammer
18                 London
19            Los Angeles
20              Melbourne
21            Mexico City
22               Montreal
23                 Moskva
24                 Munich
25                 Nagano
26                   Oslo
27                  Paris
28         Rio de Janeiro
29                   Roma
30         Salt Lake City
31           Sankt Moritz
32                Sapporo
33               Sarajevo
34                  Seoul
35                  Sochi
36           Squaw Valley
37              St. Louis
38              Stockholm
39                 Sydney
40                  Tokyo
41                 Torino
42              Vancouver

write_csv(cities, "host_cities_ZacGriffin.csv")

```