BYU Student Author: @Sterling_Lane
Reviewers: @Marta_Ellsworth, @Andrew_Wilson
Estimated Time to Solve: 60 Minutes
We provide the solution to this challenge using:
- SQL
Need a program? Click here.
Overview
You just started at your office in January, and you’re still looking for ways to fit in with your team and succeed at work. It’s now the middle of February, and your office is buzzing with sports talk when you hear your supervisor start to talk about starting an office-wide Fantasy Baseball league for the upcoming season. This is perfect for you! You eagerly join the league with the chance to finally be a part of something. To make things better, you followed the previous baseball season closely and already collected the raw data from the players that year. You might have a leg up in coming up with the best-projected roster compared to your coworkers.
You know the raw data you have is a lot to sift through. You realize that you can use your budding SQL skills to your advantage to project which players are most likely to succeed. You do some research and now know which statistics are relevant. Now, you just need to take the raw baseball player data you have access to and turn it into a queryable database to answer your burning questions. Are you up to the challenge, and can you prove to your team that you’re more than just “the new kid”?
Instructions
- Download the Start Data file SQL file and run it in your SQL Editor to get the Starting Flat File
- If you are using AccessDB or would like a visual reference to the layout, download the Baseball Flat File Excel sheet instead.
- Use your knowledge of Third Normal Form (Primary Key: Foreign Key) Data Normalization to split the file into three separate tables: Player, PlayerStats, and Stats
- Create an additional column in the Stats table called “FantasyPoints” that calculates how many Fantasy Points are attributable to that particular statistic
- The table below shows how many fantasy points are attributable for each statistic
- The table below shows how many fantasy points are attributable for each statistic
- Now that you have a normalized database, use the three tables to list the top 5 Fantasy Players for each position (except DH since you can’t draft a Designated Hitter) by Fantasy Points. Present your results in a single query with three columns in this order: Position, PlayerName, TotalFantasyPoints. Order your results by Position Name alphabetically, then by TotalFantasyPoints descending.
- See below for a description of each position in the dataset if it’s helpful to you.
- See below for a description of each position in the dataset if it’s helpful to you.
Data Files
- Challenge197_Starting_Script.sql
- Challenge197_Baseball_Flat_File.xlsx
- Challenge197_Data_Dictionary.docx
Suggestions and Hints
- Two distinct Baseball Players can have the same name but play different positions. For example, Will Smith is a pitcher for Team X but a different Will Smith is a catcher for Team Y. You’ll want to ensure these players don’t get assigned the same Primary Key.
- Some tables may need you to create a primary key, some tables may not, and some tables may have concatenated keys. Ask yourself, “Does this column of data depend on one column or multiple columns for the result to be accurate?” If a column’s value depends on multiple columns of data, those columns will need to be concatenated into a Primary Key/Foreign Key Relationship in a separate table.
- Remember to explicitly define your foreign keys in any tables you create. Within the Create Table statement it looks like this (this may be different in other DB languages):
Create Table TableName ( FKey1 INT, FKey2 INT, Field3 VARCHAR(100), FOREIGN KEY (FKey1) REFERENCES RefTable(PKey1) FOREIGN KEY (FKey2) REFERENCES RefTable(PKey2) - For the final query, you may find the UNION statement to be helpful in helping to assemble all the data you need.
Solution
