In this exercise you will practice:

Joining Tables

Aggregate Functions

Grouping Data

Using the HAVING Statement

Creating Derived Columns

Sub-Selects

Remember, the WHERE clause is used to extract particular records. The HAVING clause is used with a GROUP BY statement and contains aggregate functions (e.g. HAVING SUM(W)>100). The HAVING waits to fire until all records are selected and aggregate functions are performed. You cannot use aggregate functions in a WHERE.

Turn in numbered the SELECTs derived from the following problems. All derived and aggregate function columns must have a proper name. Make your answers RED!

1. If you haven't already, perform an inner join on Players and Managers on PlayerID and name it vwPlayerManager. Include all fields in Players and all fields in Managers but the PlayerID in Managers (redundant). How many records are there? (2)

2. Look over the data. Change the join to a left join. Look over the data results. How many records are returned with a count? _____________ (2)

3. Explain the difference in the number of records then change the join back to an inner join. (3)

4. Using vwPlayerManagers, create a SELECT to display the managers' first and last names, PlayerID, team, and year. Sort the data first by the team, then by year. (3)

Note: We include the PlayerID to differentiate players with potentially the same name such as Ken Griffey.

5. Using vwPlayerManagers, create a SELECT to display the Manager's Name along with total career wins and total career losses. Order by the last name then first name. (3)

6. Alter the previous statement to include the winning percentage (as a decimal): (3) The formula is Total Wins /Total Games Played You get Total Games Played by adding up wins and losses.

7. Using vwPlayerManager, list the team and how many managers they have had ordered most to fewest. (3)

8. Using vwPlayersBatting, list the city and how many players were born in that city, only for players born in Washington State. Order by city. (3)

9. Using vwPlayersBatting, list the country and the number of players born in that country. Order by Country. (3)

10. Using a vwPlayerSalaries view, provide a list of average salaries, per team, in the year 2005. Order by TeamID. (3)

PAUSE FOR PRACTICE!

Problem: What are the maximum total wins of any manager?

Solution: To solve this, you first need to create a list of all managers and the sum of their wins.

Step 1 Make a SELECT of PlayerIDs and the sum of their wins.

select sum(W) TotalWins from vwPlayerManagers group by playerIDpk

Step 2 Use that SELECT like a table to select the largest value

Select max(TotalWins) Wins from (select sum(W) TotalWins from vwPlayerManagers group by playerIDpk) as TempTable

Answer Returned (at the time I run): 3731

Anytime you SELECT from a SELECT, the sub-select must be given an alias - in this case, TempTable. Also, the SELECT in the innermost parenthesis executes first. Above, a memory table we called TempTable was created. From TempTable we selected the most Wins.

Using sub-SELECTs, as in this example, can be extremely powerful. Don't let yourself be confused by the layering. Always work from the inside out (inner-most parenthesis.

Now you can even go a step further. In my data, I am returned back a total wins of 3731. The data may have changed. Now I can find the manager with a total of 3731.

select namelast,namefirst,playeridpk,sum(W) TotalWins from vwPlayerManagers group by namelast,namefirst,playerIDpk having sum(W)=3731

It returns Connie Mac.

But because I hardcoded 3731, this statement is not guaranteed to be accurate in the future. Better yet, replace the 3731 with parenthesis enclosing the entire SELECT statement that generated that 3731.

select namelast,namefirst,playeridpk,sum(W) TotalWins from vwPlayerManagers group by namelast,namefirst,playerIDpk having sum(W)=(

Select max(TotalWins) Wins from (select sum(W) TotalWins from vwPlayerManagers

group by playerIDpk) as TempTable )

Now your statement is guaranteed to work in the future when the number of wins changes.

Back to work!

11. What were the most games ever managed by one manager? (4)

12. Which manager managed the most games? Provide the answer and the SELECTs. (4)

13. What was the largest salary by any player? (2)

14. Who had that salary? (2)

15. Provide the manager name, total career wins, and total career losses for only managers with an overall (career) winning record (total wins > total losses). Remember, we are looking for totals! Keep it simple and use HAVING. (4)