9. Using a subquery along with an IN clause in the WHERE statement, write a query that identifies all the playerids, the players full name and the team names who in 2021 that were playing on teams that existed prior to 1910. You should use the appearances table to identify the players years and the TEAMS table to identify the team name. Sort your results by players last name. Your query should return 613 rows. playerid Full Name Team_Name abbotco01 Cory James ( Cory ) Abbott Chicago Cubs abreual01 Albert Enmanuel ( Albert ) Abreu New York Yankees abreujo02 Jose Dariel ( Jose ) Abreu Chicago White Sox

10. Using the Salaries table, find the players full name, average salary and the last year they played for each team they played for during their career. Also find the difference between the players salary and the average team salary. You must use subqueries in the FROM statement to get the team and player average salaries and calculate the difference in the SELECT statement. Sort your answer by the last year in descending order , the difference in descending order and the playerid in ascending order. The query should return 12,928 rows playerid Full Name teamid Last Year Player Average Team Average Difference greinza01 Donald Zachary ( Zack ) Greinke HOU 2021 $33,710,942.00 $2,661,251.38 $31,049,690.62 colege01 Gerrit Alan ( Gerrit ) Cole BLA 2021 $36,000,000.00 $5,711,395.76 $30,288,604.24 arenano01 Nolan James ( Nolan ) Arenado SL4 2021 $35,025,000.00 $5,118,736.17 $29,906,263.83 scherma01 Maxwell Martin ( Max ) Scherzer BR3 2021 $34,603,480.00 $7,298,831.40 $27,304,648.60

11. Rewrite the query in #11 using a WITH statement for the subqueries instead of having the subqueries in the from statement. The answer will be the same. Please make sure you put a GO statement before and after this problem. 5 points will be deducted if the GO statements are missing and I have to add them manually.

12. Using a scalar queries in the SELECT statement and the salaries, batting, pitching and people tables , write a query that shows the full Name, the average salary (from SALARIES table), career batting average (from the BATTING table), career ERA (from the PITCHING table) and the number of teams the player played (from the BATTING table). Format the results as shown below and only use the PEOPLE table in the FROM statement of the top level select. This query returns 20,370 rows

Full Name Total Teams Avg Salary Avg ERA Avg BA Fernando Antonio ( Fernando ) Abad 11 $753,280.00 4.22 0.1111 Kurt Thomas ( Kurt ) Abbott 10 $470,777.78 NULL 0.2559 Lawrence Kyle ( Kyle ) Abbott 4 $129,500.00 8.44 0.0968 NOTE: The columns required for problems #13 through #16 were created in the Add Additional Columns script. You do not need to create or alter any columns. Also, do not format the data you insert into the new columns, formatting the data within a table may make them in unusable in calculations

13. The players union has negotiated that players will start to have a 401K retirement plan. Using the [401K Contributions] column in the Salaries table, populate this column for each row by updating it to contain 6% of the salary in the row. You must use an UPDATE query to fill in the amount. This query updates 32,862 rows. Use the column names given, do not create your own columns. Include a select query with the results sorted by playerid as part of your answer that results the rows shown below. playerid salary 401K Contributions A.Mi01 3250000.00 195000.00 A.Mi01 3250000.00 195000.00 aardsda01 300000.00 18000.00 aardsda01 387500.00 23250.00

14. Contract negotiations have proceeded and now the team owner will make a seperate contribution to each players 401K each year. If the players salary is under $1 million, the team will contribute another 5%. If the salary is over $1 million, the team will contribute 2.5%. You now need to write an UPDATE query for the [401K Team Contributions] column in the Salaries table to populate the team contribution with the correct amount. You must use a CASE clause in the UPDATE query to handle the different amounts contributed. This query updates 32,862 rows. playerid salary 401K Contributions 401K Team Contributions A.Mi01 3250000.00 195000.00 81250.00 A.Mi01 3250000.00 195000.00 81250.00 aardsda01 300000.00 18000.00 15000.00 aardsda01 387500.00 23250.00 19375.00