Grouping Data with SQL - Lab

Introduction

In this lab, you'll query data from a table populated with Babe Ruth's career hitting statistics. Then you'll use aggregate functions to pull interesting information from the table that basic queries cannot track.

Objectives

• Describe the relationship between aggregate functions and GROUP BY statements
• Use Group By statements in SQL to apply aggregate functions like: COUNT, MAX, MIN, and SUM
• Create an alias in a SQL query
• Use the HAVING clause to compare different aggregates
• Compare the difference between the WHERE and HAVING clause

Babe Ruth - Career Hitting Statistics

We will query from a table containing data of Babe Ruth statistics that has the structure below. The name of the table is babe_ruth_stats, it can be found in the babe_ruth.db file in this repo.

Connect to the Database

Import sqlite3 and pandas. Then, connect to the database in the babe_ruth.db file and instantiate a cursor. In the following questions write SQL queries to answer questions about the data on the babe_ruth_stats table.

# Your code here

Total Seasons

Return the total number of years that Babe Ruth played professional baseball

# Your code here

Seasons with NY

Return the total number of years Babe Ruth played with the NY Yankees.

#Your code here

Most HR

Select the row with the most HR that Babe Ruth hit in one season

#Your code here

Least HR

Select the row with the least number of HR hit in one season.

#Your code here

Total HR

Return the total number of HR hit by Babe Ruth during his career

#Your code here

Five Worst HR Seasons With at Least 100 Games Played

Above you saw that Babe Ruth hit 0 home runs in his first year when he played only five games. To avoid this and other extreme outliers, first filter the data to include only those years in which Ruth played in at least 100 games. Then, select all of the columns for the 5 worst seasons, in terms of the number of home runs, where he played over 100 games.

#Your code here

Average Batting Average

Select the average, AVG, of Ruth's batting averages. The header of the result would be AVG(AVG) which is quite confusing, so provide an alias of career_average.

#Your code here

Total Years and Hits Per Team

Select the total number of years played (AS num_years) and total hits (AS total_hits) Babe Ruth had for each team he played for.

#Your code here

Number of Years with Over 300 Times On Base

We want to know the years in which Ruth successfully reached base over 300 times. We need to add hits and BB to calculate how many times Ruth reached base. Simply add the two columns together (ie: SELECT [columnName] + [columnName] AS ...) and give this value an alias of on_base. Select the year and on_base for only those years with an on_base over 300.

#Your code here

Summary

Well done! In this lab, you continued to add complexity to SQL statements, which included using some aggregate functions, the GROUP BY statement, and the HAVING statement. You wrote queries that showed Babe Ruth's total years and home runs per team as well as selected only years that met a minimum value of our calculated on base attribute.