Web Development Exercise 7-2

Feb 21st, 2015
Sigchi4life
Category:
Computer Science
Price: $5 USD

Question description

 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 first 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) fields 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) fields 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) fields 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) fields. The statement sorts the records by
                          the HR field and includes the LIMIT keyword, assigned a value
                          of 1. Because the records are sorted in ascending order, the
                          statement returns the first 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) fields. The statement reverse
                          sorts the records by the HR field and includes the LIMIT key-
                          word, assigned a value of 1. Because the records are sorted
                          in descending order, the statement returns the first 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 fields. 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 fields. 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 different 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 ]

Tutor Answer

(Top Tutor) Daniel C.
(997)
School: UC Berkeley
PREMIUM TUTOR

Studypool has helped 1,244,100 students

8 Reviews


Summary
Quality
Communication
On Time
Value
darnay
Dec 3rd, 2016
" The best tutor out there!!!! "
dontuwannaknow
Nov 24th, 2016
" Excellent work as always thanks so much "
SjSurvivor143
Nov 11th, 2016
" Thanks for the help. "
ddg82
Nov 2nd, 2016
" Thanks, good work "
tinytim47
Oct 19th, 2016
" Wow this is really good.... didn't expect it. Sweet!!!! "
jestin7
Oct 10th, 2016
" This tutor is great! "
mixedballz
Oct 6th, 2016
" excellent work as always thanks for the help "
lilmoe415
Sep 27th, 2016
" Thank you, Thank you, for top quality work, this is your guy!! "
Ask your homework questions. Receive quality answers!

Type your question here (or upload an image)

1829 tutors are online

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors