banner

Exercise 7-1: Baseball Stats

Last login: Fri Nov 16 12:20:29 on ttys000
You have mail.
mbp ~ > ssh ca86_49@php.missioncollege.edu
ca86_49@php.missioncollege.edu's password: 
Linux php 2.6.24-19-server #1 SMP Wed Jun 18 15:18:00 UTC 2008 i686

The programs included with the Ubuntu system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Ubuntu comes with ABSOLUTELY NO WARRANTY, to the extent permitted by
applicable law.

To access official Ubuntu documentation, please visit:
http://help.ubuntu.com/
Last login: Fri Nov  9 22:19:00 2012 from m206-131.dsl.tsoft.com
ca86_49@php:~$ mysql -u ca86_49 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3921
Server version: 5.0.51a-3ubuntu5.1 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| ca86_49            | 
+--------------------+
2 rows in set (0.00 sec)

mysql> use ca86_49;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| ca86_49    | 
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE teamstats (Team VARCHAR(50), FirstYear INT,
    -> G INT, W INT, L INT, Pennants INT, WS INT,
    -> R INT, AB INT, H INT, HR INT, AVG FLOAT,
    -> RA INT, ERA FLOAT);
Query OK, 0 rows affected (0.08 sec)

mysql> describe teamstats
    -> ;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Team      | varchar(50) | YES  |     | NULL    |       | 
| FirstYear | int(11)     | YES  |     | NULL    |       | 
| G         | int(11)     | YES  |     | NULL    |       | 
| W         | int(11)     | YES  |     | NULL    |       | 
| L         | int(11)     | YES  |     | NULL    |       | 
| Pennants  | int(11)     | YES  |     | NULL    |       | 
| WS        | int(11)     | YES  |     | NULL    |       | 
| R         | int(11)     | YES  |     | NULL    |       | 
| AB        | int(11)     | YES  |     | NULL    |       | 
| H         | int(11)     | YES  |     | NULL    |       | 
| HR        | int(11)     | YES  |     | NULL    |       | 
| AVG       | float       | YES  |     | NULL    |       | 
| RA        | int(11)     | YES  |     | NULL    |       | 
| ERA       | float       | YES  |     | NULL    |       | 
+-----------+-------------+------+-----+---------+-------+
14 rows in set (0.00 sec)

mysql> ls
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ls' at line 1
mysql>  load data local infile '/tmp/team_stats.txt' into table teamstats;
Query OK, 30 rows affected (0.00 sec)
Records: 30  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from teamstats;
+-------------------------------+-----------+-------+-------+-------+----------+------+-------+--------+--------+-------+-------+-------+------+
| Team                          | FirstYear | G     | W     | L     | Pennants | WS   | R     | AB     | H      | HR    | AVG   | RA    | ERA  |
+-------------------------------+-----------+-------+-------+-------+----------+------+-------+--------+--------+-------+-------+-------+------+
| Arizona Diamondbacks          |      1998 |  1819 |   914 |   905 |        1 |    1 |  8379 |  62131 |  16137 |  1933 |  0.26 |  8422 | 4.26 | 
| Atlanta Braves                |      1876 | 19764 |  9786 |  9825 |       17 |    3 | 88243 | 677310 | 176434 | 12203 |  0.26 | 87693 | 3.65 | 
| Baltimore Orioles             |      1901 | 16861 |  7965 |  8786 |        7 |   26 | 72633 | 572146 | 148197 | 11335 | 0.259 | 77029 | 3.99 | 
| Boston Red Sox                |      1901 | 16848 |  8657 |  8108 |       12 |    7 | 77981 | 575510 | 153781 | 11671 | 0.267 | 75352 | 3.88 | 
| Chicago Cubs                  |      1876 | 19796 | 10103 |  9537 |       16 |    2 | 91616 | 678492 | 178828 | 12479 | 0.264 | 88269 | 3.66 | 
| Chicago White Sox             |      1901 | 16855 |  8476 |  8276 |        6 |    3 | 73603 | 570404 | 148833 |  9662 | 0.261 | 72730 | 3.73 | 
| Cincinnati Reds               |      1882 | 19382 |  9766 |  9480 |       10 |    5 | 87300 | 661241 | 173334 | 11533 | 0.262 | 86119 | 3.71 | 
| Cleveland Indians             |      1901 | 16863 |  8571 |  8201 |        5 |    2 | 76441 | 575356 | 153431 | 11338 | 0.267 | 74685 | 3.82 | 
| Colorado Rockies              |      1993 |  2565 |  1203 |  1362 |        1 |    0 | 13426 |  88540 |  24687 |  2944 | 0.279 | 14085 | 5.17 | 
| Detroit Tigers                |      1901 | 16885 |  8497 |  8295 |       10 |    4 | 78163 | 575699 | 152746 | 12050 | 0.265 | 77194 | 3.96 | 
| Florida Marlins               |      1993 |  2561 |  1214 |  1347 |        2 |    2 | 11353 |  87282 |  22744 |  2392 | 0.261 | 12150 | 4.39 | 
| Houston Astros                |      1962 |  7526 |  3754 |  3766 |        1 |    0 | 31521 | 255339 |  65127 |  5533 | 0.255 | 31387 | 3.75 | 
| Kansas City Royals            |      1969 |  6380 |  3097 |  3281 |        2 |    1 | 28357 | 218508 |  57869 |  4777 | 0.265 | 29341 |  4.2 | 
| Los Angeles Angels of Anaheim |      1961 |  7684 |  3808 |  3873 |        1 |    1 | 32963 | 260842 |  67180 |  6374 | 0.258 | 33437 | 3.94 | 
| Los Angeles Dodgers           |      1884 | 19185 |  9985 |  9063 |       22 |    6 | 86117 | 654177 | 171436 | 11154 | 0.262 | 82189 | 3.54 | 
| Milwaukee Brewers             |      1969 |  6387 |  3031 |  3352 |        1 |    0 | 28365 | 217387 |  56262 |  5794 | 0.259 | 29478 | 4.21 | 
| Minnesota Twins               |      1901 | 16869 |  8069 |  8691 |        6 |    3 | 73789 | 575390 | 151683 |  9232 | 0.264 | 76764 | 3.94 | 
| New York Mets                 |      1962 |  7518 |  3606 |  3904 |        4 |    2 | 30637 | 254460 |  63737 |  5941 |  0.25 | 31404 | 3.75 | 
| New York Yankees              |      1901 | 16836 |  9491 |  7252 |       39 |   26 | 81894 | 574293 | 153682 | 13914 | 0.268 | 70675 | 3.63 | 
| Oakland Athletics             |      1901 | 16818 |  8127 |  8604 |       15 |    9 | 74389 | 570335 | 147976 | 11408 | 0.259 | 77323 | 3.99 | 
| Philadelphia Phillies         |      1883 | 19190 |  8964 | 10114 |        6 |    2 | 85314 | 657092 | 172008 | 11457 | 0.262 | 90976 | 3.97 | 
| Pittsburgh Pirates            |      1882 | 19343 |  9706 |  9501 |        9 |    5 | 87028 | 663975 | 176269 |  9970 | 0.265 | 85954 |  3.7 | 
| San Diego Padres              |      1969 |  6393 |  2948 |  3443 |        2 |    0 | 25778 | 216951 |  54721 |  4799 | 0.252 | 28161 | 3.96 | 
| San Francisco Giants          |      1883 | 19263 | 10274 |  8832 |       20 |    5 | 90107 | 657818 | 174147 | 13262 | 0.265 | 82904 | 3.56 | 
| Seattle Mariners              |      1977 |  5098 |  2393 |  2703 |        0 |    0 | 23395 | 174802 |  46262 |  4967 | 0.265 | 24578 | 4.45 | 
| St. Louis Cardinals           |      1882 | 19387 |  9950 |  9286 |       21 |   10 | 89091 | 665624 | 177650 | 10504 | 0.267 | 86128 | 3.68 | 
| Tampa Bay Rays                |      1998 |  1817 |   760 |  1057 |        1 |    0 |  8095 |  62386 |  16374 |  1713 | 0.262 |  9599 |  4.9 | 
| Texas Rangers                 |      1961 |  7671 |  3592 |  4073 |        0 |    0 | 34233 | 262238 |  67976 |  7249 | 0.259 | 36295 | 4.28 | 
| Toronto Blue Jays             |      1977 |  5101 |  2539 |  2559 |        2 |    2 | 23597 | 175373 |  46322 |  5018 | 0.264 | 23389 | 4.21 | 
| Washington Nationals          |      1969 |  6385 |  3050 |  3330 |        0 |    0 | 26235 | 216094 |  54832 |  4946 | 0.254 | 27732 | 3.91 | 
+-------------------------------+-----------+-------+-------+-------+----------+------+-------+--------+--------+-------+-------+-------+------+
30 rows in set (0.00 sec)

mysql>