1. Using the view provided in the assignment page, write a query that uses the RANK...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 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
Expert Answer:
Answer rating: 100% (QA)
SOLUTION IS GIVEN BELOW Q1 SELECT playerid FullName CareerBA RANK OVER ORDER BY CareerBA ASC AS Rank FROM ViewBA WHERE CareerBA 040 Q2 SELECT playerid FullName CareerBA DENSERANK OVER ORDER BY CareerB... View the full answer
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these databases questions
-
The figure represents a portion of a direction field of an autonomous first-order differential equation dy/dx = f (y). Reproduce the figure on a separate piece of paper and then...
-
Imagine that marketing researchers working for a food company want to determine whether children would prefer ketchup of a different color. They develop red, green, and blue ketchups that all taste...
-
If the price of medication increases, what will be the effect? Explain with an example.
-
Daniel agreed to erect an apartment building for Steven for $12 million and that Daniel would suffer a deduction of $12,000 per day for every day of delay. Daniel was twenty days late in finishing...
-
From basic mechanics of materials, in the derivation of the bending stresses, it is found that the radius of curvature of the neutral axis, , is given by = E I/M. Find the x and y coordinates of the...
-
Thaam Company Ltd. had 100 units in beginning inventory at a total cost of NT$300,000. The company purchased 200 units at a total cost of NT$680,000. At the end of the year, Thaam had 75 units in...
-
14 P 3 Perform the indicated calculation.
-
Juras Inc. and Hinson Inc. have the following operating data: a. Compute the operating leverage for Juras Inc. and Hinson Inc. b. How much would income from operations increase for each company if...
-
Why do managers struggle with ethics when it comes to making strategic management decisions for their organization?
-
Teresina Amusement Park, SA has a fiscal year ending on September 30. Selected data from the September 30 worksheet are as follows. Instructions (a) Prepare a complete worksheet. (b) Prepare a...
-
Explain the role of the gut microbiome in metabolism. How do microbial communities influence human metabolic processes, and what implications does this have for health and disease?
-
Last year, Tammy opened an investment account with $6200 . At the end of the year, the amount in the account had decreased by 6.5% . How much is this decrease in dollars? How much money was in her...
-
Abel Plumbing Company's pickup truck, which had an actual cash value of $25,000 and a replacement cost of $30,000, was insured under a Business Auto Coverage Form for both collision and comprehensive...
-
Find the antiderivative. After a substitution u = cos x, the integral becomes cos (x) sin (x) dx =
-
Julie McCain is a regular commodities speculator. Currently, she is considering a short position in July oat contracts, which are currently trading at 312. Her analysis suggests that July oat...
-
Why do you think JP Morgan Chase was the firm the government turned to for bailing out Bear Stearns?
-
Question 19 Identify the major and minor product(s) of the following reaction: Br If there is more than one minor product, be sure to draw all of them. Incorrect. Major product(s): HC CH Incorrect....
-
Ask students to outline the reasons why the various elements of culture (social structures and control systems, language and aesthetics, religion and other belief systems, educational systems, etc.)...
-
While preparing Massie Miller's 2012 Schedule A, you review the following list of possible charitable deductions provided by Massie: Cash contribution to a family whose house burned...
-
Calculate the amount of the child and dependent care credit allowed for 2012 in each of the following cases, assuming the taxpayers had no income other than the stated amounts. a. William and Carla...
-
Dr. Ivan I. Incisor and his wife Irene are married and file a joint return for 2012. Ivan's Social Security number is 477-34-4321 and he is 48 years old. Irene I. Incisor's Social Security number is...
-
Companies are facing a great amount of change in every facet of their operations today. To remain competitive, companies must keep abreast of current developments in several areas. You recently got...
-
For each of the situations listed, identify the primary standard from the IMA Statement of Ethical Professional Practice that is violated (competence, confidentiality, integrity, or credibility.)...
-
Sustainability involves more than just the impact of actions on the environment. The triple bottom line recognizes that a company has to measure its impact on its triple bottom line for its long-term...
Study smarter with the SolutionInn App