Ginger Grant teaches T-SQL basics for data analysis, showing how to calculate summary statistics like averages directly in SQL Server. She covers SELECT-FROM-WHERE syntax, GROUP BY for per-category calculations, and HAVING to filter aggregated results, using examples from economic data and UFO sighting reports.
Chapters
Welcome to the Data Camp course on developing SQL for Data Science. My name is Ginger Grant and I'll be your instructor for this course. I have been working with Transact SQL, commonly known as T SQL, the version of SQL used in SQL server for over 10 years and I have been recognized by Microsoft as a Data Platform MVP and Certified Trainer. As president of the data analytics consultancy Desert Outgrow, I provide training and develop solutions using SQL and other tools to help clients find meaning from their data. In this course we are going to use SQL to analyze data for exploratory data analysis. In the first chapter we will be looking at summary information commonly used in other languages like R and Python. In chapter two we are going to look at some SQL functions useful for manipulating data by using math and date functions. Chapter 3 contains techniques for modifying the data and lastly in chapter 4, T SQL windowing functions data scientists spend a lot of time analyzing and aggregating data by generating summary statistics such as the mean, minimum and maximum values of columns. If your data exists in a database, the fastest way to generate summary statistics is by using SQL. In the videos of this chapter we we will run queries on the economic indicators table. The data displays by country several interesting statistics like Gross Domestic Product or GDP and Internet use. An example of a table is included here which includes different integers, text and floats. You can calculate common summary statistics such as the minimum, maximum and average of each column by using the min, max and average functions respectively. As you can see on this slide, you need to pass a column name to the relevant functions to calculate the summary statistics. Note that all of the keywords such as select, from and where along with the function names are written in capital letters. T SQL is by default case insensitive, so you don't have to write them in any particular case. But capitalizing keywords and function names is a standard convention. As you saw in the previous example, these functions by default calculate summary statistics for the entire column. What if you want to calculate these statistics for a specific group, say only for the Solomon Islands, you can do this by adding the where clause as shown here. In the previous example we aggregated for the entire table. Here you see that to calculate these stats for each country, you will have to use the group by clause as shown here. Now if you want to take this one step further by returning only countries where Internet use is greater than 100, you can't use the where clause. When you want to filter on calculations which contain group by, you can use the halving clause to filter rows. Here is a complete example. Using the having clause, we are filtering the query to show only countries having monthly Internet use greater than 100 hours a month. In the following exercises you will practice calculating summary statistics using data gatherated from MUFON and Mutual UFO network on all unexplained sightings all over the world in the incidents table. Now let's try some examples.
