Answered step-by-stepFor the questions below, you MUST replace UCID with your UDIC when...
For the questions below, you MUST replace UCID with your UDIC when you create view named UCID_Player_Summary. For example, the
view name in question 1 should look something like NJ342_Player_Summary.
The Assignment is to write a view containing the following:
playerID
Player Full Name,
Total_401K amount from the People table
Number of yrs played from the Batting table
Number of Teams played for the Batting table
Career Total Home Runs from Batting
Career Batting Average (calculate the Batting Average using all the data for the player)
Last Year Played from Batting
Total Salary
Average Career Salary
Starting Salary from the Salaries table
Ending Salary from the Salaries table
Career percentage of salary increase (percent difference in starting and ending salary)
Career Total of Wins from Pitching Table
Career Total of Strike Outs (SO) from the Pitching Table
Career Power Fitness Ratio (statistical measure of the performance of a pitcher. It is the sum of strikeouts (SO) and walks (BB) divided
by sum of innings pitched (IPouts/3)from the Pitching Table
Total games played (G) from the Fielding Table
Total games started (GS) from the Fielding Table
Total Number of Awards as a player from AwardsPlayer and AwardsSharePlayer
Total number of Awards as a manager from AwardsManager and AwardsShareManager
Year Inducted in the Hall of Fame
# of times nominated for the hall of fame but not inducted (# of rows where inducted = 'N')
Hall of Fame (Yes or No) as an indicator if the player was actually elected to the Hall of Fame
Hints and Notes:
For career start and finish dates, do not use the Debut and FinalGame columns from the PEOPLE table. There are problems with
the data and they will cause incorrect calculations. Instead use Min(YearID) and Max(yearid) from the appropriate table to get
correct dates.
Remember to check your answers. Due to the data, if you do not associate the tables properly, you will lose results. You should
end up with the same number of rows in the results as the number of rows in the PEOPLE table. You do not have to have the
exact correct number of rows because of the amount of work involved to get things 100% correct, but (and no pun intended,)
you should be in the ballpark! (+/- 2000 rows). 15 points will be taken off if your answer does not contain all the required
columns.
I recommend breaking the required columns up into pieces and use WITH statements or subqueries to create each part. Use
left joins to ensure data is not dropped due to all players not having information in all tables.
Your view must return 20, 676 rows, If you have less than that number, you are not coding the LEFT JOINS properly, If you have
more than that number, you have a subquery that is returning more than 1 row per player. To find the problem subquery, run
the following:
select * from [your view name] order by playerid
select playerid, count(*) from [your view name] group by playerid having count(*) > 1 order by playerid
By looking at the view results for the columns that have different values for the same playerid will show you the
subquery(s) giving multiple results.
What you need to submit:
1. The SQL that creates your view
2. The SQL that selects all the rows from your view
3. Write a query that calculates the count of rows returned, average of the [# of yrs played], the average of the Average Salaries and the
averages of the Career Batting Averages, and the average of career of all players who's last name begins with the letter A using the
information in the view.
Answer & Explanation
Solved by verified expertsectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentes
sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliqu
Unlock access to this and over
10,000 step-by-step explanations
Have an account? Log In
Step-by-step explanation
sectetur adipiscing elit.
sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odi
sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui le
- sectsectetur adsectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus an
- sectsectetsectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risu
- sectsectetsectetur adipiscing elitssectetur asectetursectetur adipiscinsectetursectesectetursectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesq
- sectssectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitursectetsectetu
- sectssectetur adipiscing elit. Nam lacinia pulvinar tortor nesectetusectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibu
- sectssectetur adipiscing elit. Nam lacinia pulvinar tortor necsectetursectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapi
- sectsesectetur adipiscing elit. Nam lacinia pulvinar tortor nesectetursectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus ef
- sectssectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisisectetursectetur adipiscing elit. Nam lacinia pulvinar tortor nec f
- sectsesectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentessectetur adipsectetu
- sectsesectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesqsectetur adipisectetu
- sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestiesectetsectetur adipiscing elit. Nam la
sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat,
sectetur adipiscing elit. Nam lacinia pu
sectetur adipiscing elit. Nam lacin
sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque
sectsectetursectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie con
sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna.
sectetur adipiscing elit. Nam lacinia p
sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lac
sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum do
sectetur adipiscing elit. Nam l
sectsectetsectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapi
- sectetur adipiscingsectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque
- sectetur adipiscing elit. Nam laciniasectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet.
- sectetur adipiscing elit. Nam lacinia pulvinar tsectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam ris
- sectetur adipiscing elit. Nam lacinia pulvinar tortor nesectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante,
sectsectsectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risu
sectsectesectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. F
sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing e
Get unstuck with a CliffsNotes subscription
