1. Using the view provided in the assignment page, write a query that uses the RANK function to rank the careerBA column where the careerBA < 0.40. Your results must show the playerid, Full Name, CareerBA and the rank for the players. This query returns 17,658 rows playerid forstte01 vorhecy01 oterore01 underfr01 Full Name Terry Jay (Terry) Forster Henry Bert (Cy) Vorhees Regino Jose (Reggie) Otero Frederick Theodore (Fred) Underwood vorhecy01 oterore01 underfr01 careerBA 0.3974 0.3913 0.3913 0.3889 BA_rank 1 2 2 4 2. Write the same query as #2 but eliminate any gaps in the ranking. This query returns 17,658 rows playerid Full Name careerBA forstte01 Terry Jay (Terry) Forster 0.3974 Henry Bert (Cy) Vorhees 0.3913 Regino Jose (Reggie) Otero 0.3913 Frederick Theodore (Fred) Underwood 0.3889 BA_rank 122 M 2 2 3 3. Write the same query as #1, but find the ranking within the last year played by the player starting with the most current year and working backwards. Also eliminate any player where the career batting average is = 0. This query returns 14,818 rowa playerid ceasedy01 castiiv01 camarda01 Full Name Dylan Edward (Dylan) Cease Ivan Enrique (Ivan) Castillo Daniel Ricardo (Dan) Camarena playerid ceasedy01 castiiv01Ivan camarda01 abbotco01 lastplayed 2021 2021 2021 4. Write the same query as #3, but show the ranking by quartile ( use the NTILE(4) parmeter). This query returns 14,818 rows. lastplayed 2021 2021 2021 2021 Full Name Dylan Edward (Dylan) Cease Enrique (Ivan) Castillo Daniel Ricardo (Dan) Camarena Cory James (Cory) Abbott To help check your answer, Ntile 2 starts at row 281 with playerid moncayo01 lastplayed careerBA 0.3750 0.3333 0.3333 Full Name Yoan Manuel (Yoan) Moncada 2021 careerBA 0.3750 0.3333 0.3333 0.3333 BA_rank 1 2 2 careerBA 0.2610 Ntile 1 1 1 1 Ntile 2 5. Using the Salaries table, write a query that compares the averages salary by team and year with the windowed average of the 3 prior years and the 1 year after the current year. This query returns 1,068 rows. Note: You will need to use multiple subqueries to get the answer. Avg_Salary Windowed_Salary teamid yearid 1997 $1,004,370.06 $1,109,258.56 1998 $1,214,147.06 $1,201,073.76 ANA 1999 $1,384,704.15 $1,329,673.38 2000 $1,715,472.23 $1,380,639.81 6. For each team and every player that played for that team, write a query that returns the Teamid, playerid, the Team's Average Salary, the Players average salary and the rank of the team against all other teams and the rank of the player against all other players. Limit your data to the teams that are ranked in the top 10. This query should return 2,927 rows. 7. teamid playerid BR3 scherma01 BR3 priceda01 BR3 bauertr01 Team Avg $7,298,831.40 $7,298,831.40 $7,298,831.40 Player Sal $34,603,480.00 1 $32,000,000.00 1 $31,333,333.00 1 Tsal_Rank quinlro01 Robb Michael (Robb) Quinlan guerrvl01 Vladimir (Vladimir) Guerrero anderga01 Garret Joseph (Garret ) Anderson figgich01 Desmond DeChone (Chone ) Figgins fullmbr01 Bradley Ryan (Brad ) Fullmer and player Write a query that shows that teamid, playerid, Player Full Name, total hits, total at bats, total batting average (calculated by using sum(H)*1.0/sum(AB) as the formula) and show the players rank within the team and the rank within all players. Only include players that have a minimum of 150 career hits. This query returns 2,445 rows teamid playerid Full Name Total Hits 55 206 1468 PSal_Rank 242 187 3 6 7 Team All Totals Batting Batting Batting At Bats Avg Rank Rank 160 0.3438 1 612 0.3366 2 4903 0.2994 3 817 0.2962 4 635 0.2945 5 89 147 1199 1422 1544 8. When you were completing the View Assignment, you were not able to include the last college a player attended because you were not familiar with the RANK function. Using the RANK function, write a query that shows the playerid, last year attending college, the schoolid and the rank used to decide on the last school. as well as the last school the player attended. I used multiple subqueries to get my answer. Your query should return 6,577 rows. playerid aardsda01 abadan01 abbeybe01 abbotje01 yearid schoolid 2003 rice 1993 gamiddl 1892 vermont 1994 kentucky srank 3 2 4 3