SQL Tutorial : The Olympics dataset

Want to learn more? Take the full course at https://learn.datacamp.com/courses/reporting-in-sql at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work. --- Now that you understand the case study, it’s time to learn about our dataset. Before we dig into the data, I’d like to introduce the concept of an E:R Diagram. An E:R or entity-relationship diagram is a visual representation of a database's structure. E:R diagrams show all tables and fields, and connects objects visually to show relationships. An E:R diagram is a great resource to quickly understand the structure of a database. Here’s the E:R diagram of our Olympics dataset. It consists of 5 tables: summer_games, winter_games, athletes, countries, and country_stats. Pay attention to the fields found in each table. You’ll notice several “id” fields. An id field represents a unique object and allows for joins between two tables. For example, each “id” field in the countries table represents a unique country. This id relates to the “country_id” field in the country_stats table. It should be no surprise, then, that when we join the two tables, we join on these two highlighted fields. Let’s dig a bit deeper into each of the tables. The athlete's table appears straightforward; each row represents an athlete, including their name, gender, age, height, and weight. The summer_games and winter_games tables have the exact same structure. Each row represents the results of a given athlete within an Olympic event. There are three fields that appear to represent the medals in the event: bronze, silver, and gold. We will need to dig into these three fields to understand the format. Countries is a straightforward table as well, as it simply includes the country and its corresponding region. Lastly, the country_stats table tracks several metrics related to countries, including gdp, population, and nobel_prize_winners. When dealing with a novel dataset, I recommend tak