Background

When you hear the word “relationship” what is the first thing that comes to mind? Probably not baseball. But a relationship is simply a way to describe how two or more objects are connected. There are many relationships in baseball such as those between teams and managers, players and salaries, even stadiums and concession prices. The graphs on Data Visualizations from Best Tickets show many other relationships that exist in baseball.

Your client would like developed SQL queries that they can use to retreive data for use on their website without needing Python. They expect to use Vega and Vega-LIte on their website. They would also like to see an example Vega Script with a Vega visualization.

Data:

Data Conneection: data.world baseball url
Connection Instructions: See Baseball Instructions for data.world

Readings:

• SQL for Data Science Readings (read all links)
• Why SQL is beating NoSQL, and what this means for the future of data

Optional References

• data.world Python package
• Lahman Data Dictionary

Grand Questions:

1. Write an SQL query to create a new dataframe about baseball players who attended BYU-Idaho. The new table should contain five columns: playerID, schoolID, salary, and the yearID/teamID associated with each salary. Order the table by salary (highest to lowest) and print out the table in your report.

2. This three-part question requires you to calculate batting average (number of hits divided by the number of at-bats)

   1. Write an SQL query that provides playerID, yearID, and batting average for players with at least one at bat. Sort the table from highest batting average to lowest, and show the top 5 results in your report.
   2. Use the same query as above, but only include players with more than 10 “at bats” that year. Print the top 5 results.
   3. Now calculate the batting average for players over their entire careers (all years combined). Only include players with more than 100 at bats, and print the top 5 results.

3. Pick any two baseball teams and compare them using a metric of your choice (average salary, home runs, number of wins, etc.). Write an SQL query to get the data you need. Use Python if additional data wrangling is needed, then make a graph in Altair to visualize the comparison. Provide the visualization and its description.

Deliverables:

Use this template to submit your Client Report. The template has three sections (for additional details please see the instructional template):

1. An “elevator pitch” that summarizes the entire case study.
2. Answers to the grand questions that include text, pictures, and tables.
3. An appendix that provides your commented code and justification for any programming that required you to choose an option.