IS631View Defintion: create view IS631View As With Player as (Select playerid, nameGiven + ' ( ' +
Question:
IS631View Defintion:
create view IS631View As With Player as (Select playerid, nameGiven + ' ( ' + nameFirst + ' ) ' + NameLast as [Full Name] from people), AvSalaries as (select playerid, avg(Salary) as [Average Salary], sum(salary) as [Total Salary] from salaries group by playerid), CareerBat AS ( select playerid, sum(HR) as CareerRuns, convert(Decimal(6,4),(Sum(H)*1.0/sum(AB))) as CareerBA, Convert(Decimal(6,4),max(H*1.0/AB)) as MaxBA, max(yearid) as LastPlayed from Batting where AB > 0 group by PLayerid), CareerPitch As (select PLayerid, Sum(W) as CareerWins, sum(l) as CareerLoss, Sum(HR) as CareerPHR, Convert(Decimal(5,2),avg(ERA)) as AvgERA, MAX(ERA) as MaxERA, SUm(SO) as [Career SO], max(so) as [High SO] from pitching group by playerid) select player.playerid, player.[Full NAme], [Average Salary], [Total Salary], CareerBA, MaxBA, CareerWins, CareerLoss, CareerPHR, AvgERA, MaxERA, [Career SO], [High SO], LastPlayed from Player left join AvSalaries on player.playerid = AvSalaries.playerID left join CareerBat on PLayer.PLayerid = CareerBat.playerid left join CareerPitch on player.playerid = CareerPitch.playerid go select * from IS631View select count(*) from IS631View select count(*) from people
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 rows
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- 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:
Essentials Of Modern Business Statistics With Microsoft Office Excel
ISBN: 9781337298353
7th Edition
Authors: David R. Anderson, Dennis J. Sweeney, Thomas A. Williams