In this project, you will write SQL statements that return team
names, games played, and number of at-bats from the
teamstats
table in the
baseball_stats
database. You will also write SQL state-
ments that return the teams that have the least and most all-time
home runs. For these select queries, you will need to use the
LIMIT
keyword, which restricts the number of records returned from the
database. For example, if you specify a value of 10 with the
LIMIT
keyword, the database returns the fi
rst 10 records that match the con-
ditions of your query. Finally, you will write SQL statements that use
the
SUM()
function to return the total number of games played by all
teams and the
AVG()
function to return the common batting average
for all teams.
1. Return to MySQL Monitor.
2. Enter the following
SELECT
statement, which returns the
team
,
G
(games played), and
AB
(at bats) fi
elds from the
teamstats
table:
mysql
SELECT team, G, AB FROM teamstats;[ENTER
]
3. Enter the following
SELECT
statement, which returns the
team
,
G
(games played), and
AB
(at bats) fi
elds from the
teamstats
table, sorted by team name:
mysql
SELECT team, G, AB FROM teamstats ORDER BY
team;[ENTER
]
4. Enter the following
SELECT
statement, which returns the
team
,
G
(games played), and
AB
(at bats) fi
elds from the
teamstats
table, reverse sorted by team name:
mysql
SELECT team, G, AB FROM teamstats ORDER BY
team DESC;[ENTER]
5. Enter the following
SELECTstatement, which returns the teamand HR
(home runs) fields. The statement sorts the records by
theHR
field and includes the
LIMITkeyword, assigned a value
of 1. Because the records are sorted in ascending order, the
statement returns the fi
rst record, which lists the team with
the least all-time home runs: the Tampa Bay Rays, with 1713.
mysql> SELECT team, HR FROM teamstats ORDER BY HR
LIMIT 1;[ENTER]
6. Enter the following
SELECT
statement, which also returns
the
team
and
HR
(home runs) fi
elds. Th
e statement reverse
sorts the records by the
HR
fi
eld and includes the
LIMIT
key-
word, assigned a value of 1. Because the records are sorted
in descending order, the statement returns the fi
rst record,
which lists the team with the most all-time home runs: the
New York Yankees, with 13,914.
mysql
SELECT team, HR FROM teamstats ORDER BY HR
DESC LIMIT 1;[ENTER
]
7. Enter the following
SELECT
statement, which uses the
SUM()
function to return the total number of games played by sum-
ming the contents of the
G
fi
elds. Because each game played
was between two teams in the database, the sum will be twice
the actual number of games, so you divide the result by two.
You should see a value of 182,525.
mysql
SELECT SUM(G)/2 FROM teamstats;[ENTER
]
8. Enter the following
SELECT
statement, which uses the
AVG()
function to return the batting average for all teams by averag-
ing the contents of the
AVG
fi
elds. You should see a value of
0.26199999650319.
mysql
SELECT AVG(AVG) FROM teamstats;[ENTER
]
9. Unfortunately, this is not the true all-time batting average,
because each team has a diff
erent number of at-bats. Enter
the following
SELECT
statement, which gets the weighted
average per team, and divides by the total number of at-bats.
You should see a value of 0.26256022536176.
mysql
SELECT SUM(AVG*AB)/SUM(AB) FROM
teamstats;[ENTER
]