Question Description
Im trying to learn for my Computer Science class and Im stuck. Can you help?
Exercise 7-2 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 ?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) ?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) ?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) ?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 SELECT statement, which returns the team and HR (home runs) ?elds. The statement sorts the records by the HR ?eld and includes the LIMIT keyword, assigned a value of 1. Because the records are sorted in ascending order, the statement returns the ?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) ?elds. The statement reverse sorts the records by the HR ?eld and includes the LIMIT key- word, assigned a value of 1. Because the records are sorted in descending order, the statement returns the ?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 ?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, 441 because each team has a di?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 ]