15. You have now been asked to populate the columns to the PEOPLE table that contain the total number of HRs hit ( Total_HR column) by the player and the highest Batting Average the player had during any year they played ( High_BA column). Write a single query that correctly populates these columns. You will need to use a subquery to make is a single query. This query updates 17,593 rows if you use AB > 0 in the where statement. It updates 19,898 rows in nullif is used for batting average. After your update query, write a query that shows the playerid, Total HRs and Highest Batting Average for each player. The Batting Average must be formatted to only show 4 decimal places. Sort the results by playerid. The update query will update 17841 rows and the select query will return 20,370 rows. playerid Total_HR Career_BA aardsda01 0 0.0000 aaronha01 755 0.3545 aaronto01 13 0.2500 aasedo01 0 0.0000

16. You have also been asked to populate a column in the PEOPLE table ( Total_401K column) that contains the total value of the 401K for each player in the Salaries table. Write the SQL that correctly populates the column. This query updates 5,981 rows. Also, include a query that shows the playerid, the player full name and their 401K total from the people table. Only show players that have contributed to their 401Ks. Sort the results by playerid. . This query returns 5,981 rows. playerid Full Name 401K Total aardsda01 David Allan ( David ) Aardsma $837,322.50 aasedo01 Donald William ( Don ) Aase $253,000.00 abadan01 Fausto Andres ( Andy ) Abad $35,970.00

17. 2021 Fan Cost Index (the amount it costs for a group of four people to attend an MLB game was an average of $256.41. MLB management has asked you to calculate the following using the teamid, name, yearid, attendance and GHomes (# of home games) from teams table: a. The total amount the team lost due to covid (The difference between pre-COVID and COVID Attendance (from the Teams table) multiplied by the per person Fan Cost Index) b. The average loss per game (Total amount lost/Total number of COVID HGames) c. The number of extra games it would take to recover the losses (total amount lost / average loss per game d. Per-COVID average attendance (pre-COVID attendance/pre-COVID HGames) e. COVID average attendance (sum of attendance / sum HGames) f. COVID drop in per average game attendance (e minus d) g. % drop in attendance due to cover (e divided by d) Use values for 2020 and 2021 for the COVID value and 2019 for the pre-COVID values. teamid name Total_Team_Loss CV_per_Game Games_To CV_Avg_ Pre_CV_Avg CV drop in CV_% $_Loss Recover attendance attendance attendance drop LAA Los Angeles Angels of Anaheim -172,817,391.29 -13,534.14 72 13,413 37,271 -23,858 35.99% LAN Los Angeles Dodgers -169,331,753.75 -13,743.35 53 25,267 49,065 -23,798 51.50% NYA New York Yankees -167,260,345.56 -13,333.89 63 17,498 40,795 -23,297 42.89% SLN St. Louis Cardinals -162,692,145.00 -13,948.23 59 19,467 42,967 -23,500 45.31% Your query should return 30 rows.My recommendation is to use separate subqueries to get the required pre-COVID and COVID related data. You can then use the info in the main select to calculate the required values.

18. As with any job, players are given raises each year, write a query that calculates the increase each player received and calculate the % increase that raise makes. You will only need to use the SALARIES and PEOPLE tables. You answer should include the columns below. Include the players full name and sort your results by playerid in ascending order and year in descending order. This query returns 15,569 rows. You cannot use advanced aggregate functions such as LAG for this question. The answer can be written using only the SQL parameters you learned in this chapter. playerid Player Name yearid Current Prior Salary Salary Salary Salary Difference Increase aardsda01 David Allan ( David ) Aardsma 2011 $4,500,000.00 $2,750,000.00 $1,750,000.00 63.63% aardsda01 David Allan ( David ) Aardsma 2010 $2,750,000.00 $419,000.00 $2,331,000.00 556.32% aasedo01 Donald William ( Don ) Aase 1988 $675,000.00 $625,000.00 $50,000.00 8.00% aasedo01 Donald William ( Don ) Aase 1987 $625,000.00 $600,000.00 $25,000.00 4.16% abadfe01 Fernando Antonio ( Fernando ) Abad 2015 $1,087,500.00 $525,900.00 $561,600.00 106.78% abadfe01 Fernando Antonio ( Fernando ) Abad 2012 $485,000.00 $418,000.00 $67,000.00 16.02%