SQL Tutorial : Exploring our data

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. --- Last lesson, we learned how E:R diagrams help us understand the structure of a database. But structure is only half of the equation; we also want to understand the actual data. This is where DATA EXPLORATION comes in. The simplest way to explore data is to look at a preview, such as in a SQL client or in the DataCamp console. While this does quickly answer questions about what each field means, there are limitations to this approach. First, since it’s only a preview of the dataset, it’s possible the preview does not provide an accurate picture. For example, when looking in the explorer at the summer_games table, you may only see NULL values for the bronze field. While it is useful to understand NULL values exist, it does not provide any information about existing values. Second, previews do not provide any information on the distribution of values. For example, you may work for a company that works primarily in Europe. If the first few rows in the preview are North American countries, you may incorrectly conclude the company primarily works with North American clients. A more robust approach to exploring the data is to run simple queries. By SELECTing the distinct values in the region field, we learn that the field is formatted with all uppercase. We also have a better understanding of what the region field represents after viewing the values. You can also add a GROUP BY instead of DISTINCT to get this report. This query may perform better, but our dataset is not large enough to worry about performance. Regardless, it’s an important concept to know about. Another exploration technique is to include an aggregated metric and sort the data to identify value distributions. By adding COUNT(*) to our query, you can see which values are most common. The