Homework answers / question archive / 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

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

Computer Science

Share With

---

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...

 

2. Write the same query as #2 but eliminate any gaps in the ranking. This query returns 17,658 rows

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 row

 

4. Write the same query as #3, but show the ranking by quartile ( use the NTILE(4) parmeter). This
query returns 14,818 rows.

 

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.

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. 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

 

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.

 

9. You've decided that due to the number of queries that use the Salaries table, you need to create a primary key
consisting of Playerid, Teamid, Yearid and LGID. When try to create the primary key, you will be told that there
are duplicate records. You will receive the following error
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object
name 'dbo.Salaries' and the index name 'PK__Salaries__89095551BDD28FCE'. The duplicate key value
is (NL, ARI , 2019, GreinZa01).

 

10 Using a recursive CTE, write a query that will generate the days of the week using the DATENAME(DW, N)
function where N indicates the day of the week. Your query must recurse and use N+1 to get the next day of
the week. The output should be: