In This Project, You Will Write SQL Statements That Return Team

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

(games played), and

(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

(games played), and

(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

(games played), and

(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

(home runs) fi

elds. Th

e statement reverse

sorts the records by the

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

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

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

]