web development exercise 7 2
Exercise 7-2_x000D_
In this project, you will write SQL statements that return team_x000D_
names, games played, and number of at-bats from the teamstats_x000D_
table in the baseball_stats database. You will also write SQL state-_x000D_
ments that return the teams that have the least and most all-time_x000D_
home runs. For these select queries, you will need to use the LIMIT_x000D_
keyword, which restricts the number of records returned from the_x000D_
database. For example, if you specify a value of 10 with the LIMIT_x000D_
keyword, the database returns the ï¬rst 10 records that match the con-_x000D_
ditions of your query. Finally, you will write SQL statements that use_x000D_
the SUM() function to return the total number of games played by all_x000D_
teams and the AVG() function to return the common batting average_x000D_
for all teams._x000D_
1. Return to MySQL Monitor._x000D_
2. Enter the following SELECT statement, which returns the team,_x000D_
G (games played), and AB (at bats) ï¬elds from the teamstats_x000D_
_x000D_
table:_x000D_
mysql> SELECT team, G, AB FROM teamstats;[ENTER ]_x000D_
_x000D_
3. Enter the following SELECT statement, which returns the team,_x000D_
G (games played), and AB (at bats) ï¬elds from the teamstats_x000D_
table, sorted by team name:_x000D_
mysql> SELECT team, G, AB FROM teamstats ORDER BY_x000D_
team;[ENTER ]_x000D_
_x000D_
4. Enter the following SELECT statement, which returns the team,_x000D_
G (games played), and AB (at bats) ï¬elds from the teamstats_x000D_
table, reverse sorted by team name:_x000D_
mysql> SELECT team, G, AB FROM teamstats ORDER BY_x000D_
team DESC;[ENTER ]_x000D_
_x000D_
5. Enter the following SELECT statement, which returns the team_x000D_
and HR (home runs) ï¬elds. The statement sorts the records by_x000D_
the HR ï¬eld and includes the LIMIT keyword, assigned a value_x000D_
of 1. Because the records are sorted in ascending order, the_x000D_
statement returns the ï¬rst record, which lists the team with_x000D_
the least all-time home runs: the Tampa Bay Rays, with 1713._x000D_
mysql> SELECT team, HR FROM teamstats ORDER BY HR_x000D_
LIMIT 1;[ENTER ]_x000D_
_x000D_
6. Enter the following SELECT statement, which also returns_x000D_
the team and HR (home runs) ï¬elds. The statement reverse_x000D_
sorts the records by the HR ï¬eld and includes the LIMIT key-_x000D_
word, assigned a value of 1. Because the records are sorted_x000D_
in descending order, the statement returns the ï¬rst record,_x000D_
which lists the team with the most all-time home runs: the_x000D_
New York Yankees, with 13,914._x000D_
mysql> SELECT team, HR FROM teamstats ORDER BY HR_x000D_
DESC LIMIT 1;[ENTER ]_x000D_
_x000D_
7. Enter the following SELECT statement, which uses the SUM()_x000D_
function to return the total number of games played by sum-_x000D_
ming the contents of the G ï¬elds. Because each game played_x000D_
was between two teams in the database, the sum will be twice_x000D_
the actual number of games, so you divide the result by two._x000D_
You should see a value of 182,525._x000D_
mysql> SELECT SUM(G)/2 FROM teamstats;[ENTER ]_x000D_
8. Enter the following SELECT statement, which uses the AVG()_x000D_
function to return the batting average for all teams by averag-_x000D_
ing the contents of the AVG ï¬elds. You should see a value of_x000D_
0.26199999650319._x000D_
mysql> SELECT AVG(AVG) FROM teamstats;[ENTER ]_x000D_
_x000D_
9. Unfortunately, this is not the true all-time batting average, 441_x000D_
because each team has a different number of at-bats. Enter_x000D_
the following SELECT statement, which gets the weighted_x000D_
average per team, and divides by the total number of at-bats._x000D_
You should see a value of 0.26256022536176._x000D_
mysql> SELECT SUM(AVG*AB)/SUM(AB) FROM_x000D_
teamstats;[ENTER ]_x000D_
