Planning queries with an E:R diagram

An E:R diagram visually shows all tables, fields, and relationships in a database. You are given the following E:R diagram:

You are tasked with building a report that shows Age of Oldest Athlete by Region. What tables will need to be included in the query?

Deze oefening maakt deel uit van de cursus

Reporting in SQL

Cursus bekijken

Praktische interactieve oefening

Zet theorie om in actie met een van onze interactieve oefeningen.

Begin met trainen

Deze oefening maakt deel uit van de cursus

Reporting in SQL

SkillTag.level.intermediateSkillTag.label

4.8+

Begin de cursus gratis

Before you can start building out reports to answer specific questions, you should get familiar with the data. In this chapter, you will learn how to use E:R diagrams and data exploration techniques to get a solid understanding of the data to better answer business-related questions.

Exercise 1: Case study Exercise 2: Identifying the base report Exercise 3: Building the base report Exercise 4: Athletes vs events by sport Exercise 5: The Olympics dataset Exercise 6: Planning queries with an E:R diagram

Huidige oefening

Exercise 7: Age of oldest athlete by region Exercise 8: Number of events in each sport Exercise 9: Exploring our data Exercise 10: Exploring summer_games Exercise 11: Validating our query Exercise 12: Report 1: Most decorated summer athletes

Queries can get large, fast. It's important to take a logical approach when building more complicated queries. In this chapter, you will take a step-by-step approach to plan and build a complex query that requires you to combine tables in multiple ways and create different types of fields.

Exercise 1: Planning the query Exercise 2: Planning the SELECT statement Exercise 3: Planning the filter Exercise 4: Combining tables Exercise 5: JOIN then UNION query Exercise 6: UNION then JOIN query Exercise 7: Creating custom fields Exercise 8: CASE statement refresher Exercise 9: BMI bucket by sport Exercise 10: Troubleshooting CASE statements Exercise 11: Filtering and finishing touches Exercise 12: Filtering with a JOIN Exercise 13: Filtering with a subquery Exercise 14: Report 2: Top athletes in nobel-prized countries

Although it would be nice, data in the real-world is rarely stored in an ideal way. Simply put: data can get messy. In chapter 3, you will learn how to deal with this messy data by fixing data type issues, cleaning messy strings, handling nulls, and removing duplication.

Exercise 1: Converting data types Exercise 2: Identifying data types Exercise 3: Interpreting error messages Exercise 4: Using date functions on strings Exercise 5: Cleaning strings Exercise 6: String functions Exercise 7: Replacing and removing substrings Exercise 8: Fixing incorrect groupings Exercise 9: Dealing with nulls Exercise 10: Filtering out nulls Exercise 11: Fixing calculations with coalesce Exercise 12: Report duplication Exercise 13: Identifying duplication Exercise 14: Fixing duplication through a JOIN Exercise 15: Report 3: Countries with high medal rates

The value of reporting really shows when presenting not-so-obvious insights through complex calculations. In this chapter, you will learn how to build more complicated fields by leveraging window functions and layered calculations. You will gain hands-on experience building two advanced calculations in particular: the percent of a total calculation and the performance index calculation.

Exercise 1: Building complex calculations Exercise 2: Testing out window functions Exercise 3: Average total country medals by region Exercise 4: Most decorated athlete per region Exercise 5: Comparing groups Exercise 6: Volume vs efficiency metrics Exercise 7: Percent of gdp per country Exercise 8: GDP per capita performance index Exercise 9: Comparing dates Exercise 10: Month-over-month comparison Exercise 11: Week-over-week comparison Exercise 12: Report 4: Tallest athletes and % GDP by region Exercise 13: Course summary