Working with basic mathematics and statistics in SQL
TINST 311: Database Management & Data Analysis Lab 3: Basic Math and Stats General Description This lab focuses on working with basic mathematics and statistics in SQL. For this lab, you will be importing a data set and creating SQL queries to perform various mathematical functions. Requirements: In order to complete this lab, you will need to perform the following: • • • • Create a table Import data to the table Perform various mathematical SQL queries Answer the questions on pages 2+ from this lab Steps: Step 1: Create a new database in pgAdmin named ‘lab03’ – this can be done either via SQL or from the pgAdmin GUI Step 2: Run the SQL from appendix A to create a table that has the number of arrests per NFL stadium by year (2011-2015) Steps 3a: Download the file ‘nfl_active_player_stats.csv’ from the ‘Data’ folder on Canvas. Step 3b: Create the table that will hold the data within the CSV file you downloaded in step 3a. You should be able to create a new table on your own – use your best judgment when choosing data types. Name the table ‘nfl_player_stats’ Step 4: Import the data from the CSV downloaded in step 3a into the table created in step 3b. Look to the basic math slides and chapter 4 of Practical SQL for help on this step. Step 5: Answer the questions starting on the following page. Step 6: Put all of your answers into a new document and turn that document into Canvas. Save your file as Lab03_ e.g. Lab03_Atwood.docx. I will accept any document file type you’d prefer. Questions: For questions 1-3, use the nfl_player_stats table: Question 1: Write a SQL query that returns the player’s name as ‘Player Name’, position as ‘Position’, height as ‘Height’, and weight as ‘Weight’. 1a. Paste your SQL query: Question 2: Write a query that returns the player’s name as ‘Player Name’, current team as ‘Current Team’, position as ‘Position’, and a calculated field called ‘Age Signed.’ The calculated field will give the age that the player was when he first joined the NFL. This can be calculated by subtracting the years of experience from the player’s age. 2a. Paste your SQL query: Question 3: Now write a query that returns the player’s name as ‘Player Name’, current team as ‘Current Team’, position as ‘Position’, and a calculated field of the player’s BMI. Use the imperial BMI formula which is calculated as BMI = weight (lb) ÷ height2 (in2) × 703. 3a. Paste your SQL query: For questions 4-7, use the nfl_stadium_arrests table: Question 4: Write a query to make sure our data is correct. The query should select the team name, the total arrests attribute, and then a calculated field showing the difference of all of the individual years (2011-2015) added together, minus the total field. Name the calculated field ‘difference’. You should see a value of 0 for ‘difference’ in each row. 4a. Paste your SQL query: Question 5: Write a query that selects the team name, and then calculates what percentage of total arrests per stadium happened in 2015. Name your calculated field ‘2015_pct_of_total’ 5a. SQL: 5b. Percentage of total arrests for 2015 in Arizona: Question 6: Write a query that shows the percentage change of arrests between 2011 and 2015 for each stadium. Name your calculated field ‘pct_change’ 6a. SQL: 6b. Percentage change in Carolina, Green Bay, San Francisco, and Seattle? Question 7: Write a query that finds the sum and average of arrests in 2012. Name the fields appropriately. 7a. Paste your SQL query: For question 8, use the nfl_player_stats table: Question 8: Use percentile_cont(.5) to find the median age, median height, and median weight of all players. Name the fields appropriately (e.g. ‘Median Age’). 8a. Paste your SQL query: For questions 9 & 10, use the nfl_stadium_arrests table: Question 9: Write a query that shows the sum, average, and median values for total arrests across all stadiums. Name the fields appropriately. 9a. Paste your SQL query: 9b. What is the average number of arrests? Median? What do these numbers tell you? Question 10: Write a query that shows the quartile values of the total arrests across all stadiums. 10a. 25th percentile: 10b. 50th percentile: 10c. 75th percentile: For question 10, use the nfl_player_stats table: Question 10: Write queries to answer the following: 11a. What is the most common age among active players in the NFL? 11b. What is the most common height? 11c. What is the most common amount of experience? References: The player stats dataset was downloaded from https://www.kaggle.com/kendallgillies/nflstatistics/ and then edited slightly for this lab. The NFL stadium arrest data was downloaded from https://inclass.kaggle.com/washingtonpost/nfl-arrests/ then aggregated and pivoted for this lab. Appendix A: Use the following code for step 2 in the instructions: CREATE TABLE nfl_stadium_arrests ( team varchar(20), arrests_2011 smallint, arrests_2012 smallint, arrests_2013 smallint, arrests_2014 smallint, arrests_2015 smallint, total_arrests smallint ); INSERT INTO nfl_stadium_arrests VALUES (‘Arizona’,38,37,35,29,27,166), (‘Baltimore’,3,null,20,10,13,46), (‘Carolina’,2,9,16,10,18,55), (‘Chicago’,3,8,7,8,null,26), (‘Cincinnati’,9,16,6,23,3,57), (‘Dallas’,5,58,32,25,49,169), (‘Denver’,4,12,40,33,16,105), (‘Green Bay’,46,61,65,56,60,288), (‘Houston’,9,18,7,2,4,40), (‘Indianapolis’,25,16,22,17,11,91), (‘Jacksonville’,24,10,12,10,8,64), (‘Kansas City’,11,6,21,27,6,71), (‘Miami’,null,19,14,29,12,74), (‘New England’,41,43,37,38,29,188), (‘New York Giants’,188,145,182,175,209,899), (‘New York Jets’,192,148,143,166,209,858), (‘Oakland’,127,133,123,143,132,658), (‘Philadelphia’,21,16,51,20,18,126), (‘Pittsburgh’,126,207,185,113,39,670), (‘San Diego’,251,277,185,137,133,983), (‘San Francisco’,29,42,28,210,145,454), (‘Seattle’,2,17,8,2,3,32), (‘Tampa Bay’,10,8,8,2,5,33), (‘Tennessee’,4,8,14,20,37,83), (‘Washington’,17,34,26,18,12,107);
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
