The answers for the questions below may be long.  Therefore, we recommend that
you copy this file to your home directory and copy your answers directly into
this text using cut and paste.  

---------------------------------
Part 1:  Connecting to a database
---------------------------------

The first part of the will focus on a sample database that is located on
dictator, samp_db.  All of you have logins to the database (the same as your
username).  To log into samp_db, type the following

mysql -u <your login> -h dictator samp_db

1.  What are the names of the tables (or relations) in samp_db?  Use 'show
    tables'.

2.  What is the schema for each table in samp_db?  Write your answers in the
    following form  

     TABLE_NAME( column_name1, column_name2, ... )

    Use the command 'show columns from <table name>'

Extra Credit:

  Movies.sd.com is a web site which allows users to find all the movies
  currently playing in San Diego based on a set of user preferences.  These
  user preferences include the title of the movie, the time it's playing, and
  the movie theater it's playing at.  Design a schema for
  the database that would store this information.  Use just one table.

  Movie( title, time, day, theater )

  
---------------------------------
Part 2:  Querying the data
---------------------------------

Part A:

What are the results of the following queries:

1.   SELECT first_name, last_name, email 
     FROM member 
     WHERE state = 'CA';

+------------+-----------+-----------------------+
| first_name | last_name | email                 |
+------------+-----------+-----------------------+
| Daniel     | Feit      | daniel.feit@pluto.com |
+------------+-----------+-----------------------+
1 row in set (0.00 sec)

2.  SELECT * 
    FROM member 
    WHERE email like '%@pluto.com';

| last_name | first_name | expiration | email                     | street                | city     | state | zip   | phone        |
+-----------+------------+------------+---------------------------+-----------------------+----------+-------+-------+--------------+
| Lundsten  | August     | 2002-08-11 | august.lundsten@pluto.com | 13 O'Malley St.       | Bogalusa | LA    | 96127 | 196-481-0897 |
| Eliason   | Jessica    | 2000-12-27 | jessica.eliason@pluto.com | 60 Century Av.        | Osborne  | KS    | 63198 | 896-268-0569 |
| Propper   | Harvey     | 2001-04-03 | harvey_propper@pluto.com  | 853 Van Hise Av.      | Lansing  | MI    | 39980 | 184-832-6901 |
| Feit      | Daniel     | 2001-05-04 | daniel.feit@pluto.com     | 181 E. Washington Av. | Stockton | CA    | 90255 | 167-064-7158 |
| Camosy    | Alan       | 2002-08-23 | alan.camosy@pluto.com     | 15 Kenwood Cir.       | Dallas   | TX    | 49786 | 443-837-6502 |
+-----------+------------+------------+---------------------------+-----------------------+----------+-------+-------+--------------+
5 rows in set (0.00 sec)

3.  SELECT * 
    FROM member 
    WHERE expiration > '2002-08-11';

+-----------+------------+------------+---------------------------+-----------------------+-------------+-------+-------+--------------+
| last_name | first_name | expiration | email                     | street                | city        | state | zip   | phone        |
+-----------+------------+------------+---------------------------+-----------------------+-------------+-------+-------+--------------+
| Weiss     | Nicole     | 2002-11-20 | nicole.w@mars.net         | 4488 E. Harmony Dr.   | Burns       | OR    | 92532 | 898-181-7231 |
| Matthews  | Bill       | 2002-09-15 | matthews.b@saturn.org     | 9902 Mound St.        | Fairbanks   | AK    | 54214 | 743-150-3797 |
| Camosy    | Alan       | 2002-08-23 | alan.camosy@pluto.com     | 15 Kenwood Cir.       | Dallas      | TX    | 49786 | 443-837-6502 |
| Brooks    | Carl       | 2002-09-12 | brooks_carl@jupiter.com   | 8755 Dapin Rd.        | Sarasota    | FL    | 19735 | 514-906-3111 |
| Fiorelli  | Neil       | 2002-11-07 | fiorelli.neil@mercury.net | 5599 Constitution Dr. | Ashland     | WI    | 85083 | 379-922-7719 |
| Stehle    | Joseph     | 2002-11-13 | s.joseph@venus.org        | 3688 N. Franklin St.  | San Antonio | TX    | 92419 | 217-542-0789 |
+-----------+------------+------------+---------------------------+-----------------------+-------------+-------+-------+--------------+

4.   SELECT * 
     FROM member 
     WHERE email like '%@pluto.com' AND  expiration > '2002-08-11';

+-----------+------------+------------+-----------------------+-----------------+--------+-------+-------+--------------+
| last_name | first_name | expiration | email                 | street          | city   | state | zip   | phone        |
+-----------+------------+------------+-----------------------+-----------------+--------+-------+-------+--------------+
| Camosy    | Alan       | 2002-08-23 | alan.camosy@pluto.com | 15 Kenwood Cir. | Dallas | TX    | 49786 | 443-837-6502 |
+-----------+------------+------------+-----------------------+-----------------+--------+-------+-------+--------------+
1 row in set (0.03 sec)

5.   SELECT * 
     FROM member 
     WHERE email like '%@pluto.com' OR expiration > '2002-08-11';

+-----------+------------+------------+---------------------------+-----------------------+-------------+-------+-------+--------------+
| last_name | first_name | expiration | email                     | street                | city        | state | zip   | phone        |
+-----------+------------+------------+---------------------------+-----------------------+-------------+-------+-------+--------------+
| Lundsten  | August     | 2002-08-11 | august.lundsten@pluto.com | 13 O'Malley St.       | Bogalusa    | LA    | 96127 | 196-481-0897 |
| Eliason   | Jessica    | 2000-12-27 | jessica.eliason@pluto.com | 60 Century Av.        | Osborne     | KS    | 63198 | 896-268-0569 |
| Propper   | Harvey     | 2001-04-03 | harvey_propper@pluto.com  | 853 Van Hise Av.      | Lansing     | MI    | 39980 | 184-832-6901 |
| Feit      | Daniel     | 2001-05-04 | daniel.feit@pluto.com     | 181 E. Washington Av. | Stockton    | CA    | 90255 | 167-064-7158 |
| Weiss     | Nicole     | 2002-11-20 | nicole.w@mars.net         | 4488 E. Harmony Dr.   | Burns       | OR    | 92532 | 898-181-7231 |
| Matthews  | Bill       | 2002-09-15 | matthews.b@saturn.org     | 9902 Mound St.        | Fairbanks   | AK    | 54214 | 743-150-3797 |
| Camosy    | Alan       | 2002-08-23 | alan.camosy@pluto.com     | 15 Kenwood Cir.       | Dallas      | TX    | 49786 | 443-837-6502 |
| Brooks    | Carl       | 2002-09-12 | brooks_carl@jupiter.com   | 8755 Dapin Rd.        | Sarasota    | FL    | 19735 | 514-906-3111 |
| Fiorelli  | Neil       | 2002-11-07 | fiorelli.neil@mercury.net | 5599 Constitution Dr. | Ashland     | WI    | 85083 | 379-922-7719 |
| Stehle    | Joseph     | 2002-11-13 | s.joseph@venus.org        | 3688 N. Franklin St.  | San Antonio | TX    | 92419 | 217-542-0789 |
+-----------+------------+------------+---------------------------+-----------------------+-------------+-------+-------+--------------+
10 rows in set (0.00 sec)

Part B:

6.  Find all members who get email at mercury.net

    SELECT * 
    FROM member 
    WHERE email like '%@mercury.net';

    +-----------+-------------+------------+---------------------------+-----------------------+---------+-------+-------+--------------+
    | last_name | first_name  | expiration | email                     | street                | city    | state | zip   | phone        |
    +-----------+-------------+------------+---------------------------+-----------------------+---------+-------+-------+--------------+
    | Block     | Christopher | 2002-07-03 | christopher_b@mercury.net | 606 Cumberland Ln.    | Bruneau | ID    | 58790 | 015-680-8696 |
    | Fiorelli  | Neil        | 2002-11-07 | fiorelli.neil@mercury.net | 5599 Constitution Dr. | Ashland | WI    | 85083 | 379-922-7719 |
    +-----------+-------------+------------+---------------------------+-----------------------+---------+-------+-------+--------------+
    2 rows in set (0.00 sec)

7.  Find all members who live in either Texas(TX) or Oregon(OR).

    SELECT * FROM member WHERE state = 'TX' OR state = 'OR';

    +-----------+------------+------------+---------------------------+----------------------+-------------+-------+-------+--------------+
    | last_name | first_name | expiration | email                     | street               | city        | state | zip   | phone        |
    +-----------+------------+------------+---------------------------+----------------------+-------------+-------+-------+--------------+
    | Weiss     | Nicole     | 2002-11-20 | nicole.w@mars.net         | 4488 E. Harmony Dr.  | Burns       | OR    | 92532 | 898-181-7231 |
    | Camosy    | Alan       | 2002-08-23 | alan.camosy@pluto.com     | 15 Kenwood Cir.      | Dallas      | TX    | 49786 | 443-837-6502 |
    | Beckett   | Luther     | 2001-06-06 | luther.b@mars.net         | 148 Greenbriar Dr.   | Sonora      | TX    | 52841 | 778-028-6040 |
    | Stehle    | Joseph     | 2002-11-13 | s.joseph@venus.org        | 3688 N. Franklin St. | San Antonio | TX    | 92419 | 217-542-0789 |
    | Harrison  | Marita     | 2001-11-07 | marita_harrison@earth.com | 64 Delaware Dr.      | Portland    | OR    | 57577 | 927-099-6116 |
    +-----------+------------+------------+---------------------------+----------------------+-------------+-------+-------+--------------+

8.  Find all members who live in Portland, OR

    SELECT * FROM member WHERE state = 'OR' AND city = 'Portland';

    +-----------+------------+------------+---------------------------+-----------------+----------+-------+-------+--------------+
    | last_name | first_name | expiration | email                     | street          | city     | state | zip   | phone        |
    +-----------+------------+------------+---------------------------+-----------------+----------+-------+-------+--------------+
    | Harrison  | Marita     | 2001-11-07 | marita_harrison@earth.com | 64 Delaware Dr. | Portland | OR    | 57577 | 927-099-6116 |
    +-----------+------------+------------+---------------------------+-----------------+----------+-------+-------+--------------+
    1 row in set (0.01 sec)

9.  Find all members whose membership expires in February 2002.  Hint: there are
    28 days in February 2002.

    SELECT * 
    FROM member 
    WHERE expiration >= '2002-02-01' AND expiration <= '2002-02-28';

    +-----------+------------+------------+------------------------+-----------------+----------+-------+-------+--------------+
    | last_name | first_name | expiration | email                  | street          | city     | state | zip   | phone        |
    +-----------+------------+------------+------------------------+-----------------+----------+-------+-------+--------------+
    | Arbogast  | Ruth       | 2002-02-10 | arbogast.ruth@mars.net | 95 Carnwood Rd. | Paris    | IL    | 31483 | 539-907-5225 |
    | Page      | Sarah      | 2002-02-06 | p_sarah@saturn.org     | 34 Harvest Ln.  | St. Paul | MN    | 02590 | 520-343-3572 |
    +-----------+------------+------------+------------------------+-----------------+----------+-------+-------+--------------+

10.  Find all members who's street name contains 'Av.' and return their first
     name, last name, and phone number.

      SELECT first_name, last_name, phone
      FROM member
      WHERE street LIKE '%Av.';

      +------------+-----------+--------------+
      | first_name | last_name | phone        |
      +------------+-----------+--------------+
      | Jessica    | Eliason   | 896-268-0569 |
      | Harvey     | Propper   | 184-832-6901 |
      | Daniel     | Feit      | 167-064-7158 |
      | Clifford   | Desmond   | 776-381-1029 |
      +------------+-----------+--------------+
      4 rows in set (0.00 sec)


---------------------------------
Part 3:  Modify the data
---------------------------------

Execute the following:

1.  CREATE TABLE hiking( trail CHAR(50), area CHAR(50), 
                         distance FLOAT, est_time FLOAT );

    Show results of the following:
    
    'show table'

    +-----------------------+
    | Tables in ssmallen_db |
    +-----------------------+
    | hiking                |
    +-----------------------+
    1 row in set (0.00 sec)

    
    'show columns from hiking'

    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | trail    | char(50)    | YES  |     | NULL    |       |
    | area     | char(50)    | YES  |     | NULL    |       |
    | distance | float(10,2) | YES  |     | NULL    |       |
    | est_time | float(10,2) | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)

2.  INSERT INTO hiking 
    VALUES( 'Cedar Creek Falls', 'Upper San Diego River', 4.5, 2.5 );

    INSERT INTO hiking (trail, area ) 
    VALUES ( 'East Mesa Loop', 'Cuyamaca Mountains' );	       

    Show results of 'select * from hiking'

    +-------------------+-----------------------+----------+----------+
    | trail             | area                  | distance | est_time |
    +-------------------+-----------------------+----------+----------+
    | Cedar Creek Falls | Upper San Diego River |     4.50 |     2.50 |
    | East Mesa Loop    | Cuyamaca Mountains    |     NULL |     NULL |
    +-------------------+-----------------------+----------+----------+
    2 rows in set (0.00 sec)

3.  UPDATE hiking 
    SET distance = 10.5, est_time = 5.5 
    WHERE trail = 'East Mesa Loop';

    Show results of 'select * from hiking'

    +-------------------+-----------------------+----------+----------+
    | trail             | area                  | distance | est_time |
    +-------------------+-----------------------+----------+----------+
    | Cedar Creek Falls | Upper San Diego River |     4.50 |     2.50 |
    | East Mesa Loop    | Cuyamaca Mountains    |    10.50 |     5.50 |
    +-------------------+-----------------------+----------+----------+
    2 rows in set (0.00 sec)

4.  DELETE FROM hiking 
    WHERE trail = 'Cedar Creek Falls';

    Show results of 'select * from hiking'

    +----------------+--------------------+----------+----------+
    | trail          | area               | distance | est_time |
    +----------------+--------------------+----------+----------+
    | East Mesa Loop | Cuyamaca Mountains |    10.50 |     5.50 |
    +----------------+--------------------+----------+----------+
    1 row in set (0.00 sec)


5. Give the SQL statements to insert the following values into the hiking table:

   +------------------------+--------------------+----------+----------+
   | trail                  | area               | distance | est_time |
   +------------------------+--------------------+----------+----------+
   | East Mesa Loop         | Cuyamaca Mountains |    10.50 |     5.50 |
   | Oak Canyon             | NULL               |     3.00 |     NULL |
   +------------------------+--------------------+----------+----------+

   INSERT INTO hiking
   VALUES ( 'Cottonwood Creek Falls', 'Laguna Mountains', 1.8, 1 );
   
   INSERT INTO hiking ( trail, distance )
   VALUES ( 'Oak Canyon', 3 );

6. Give the SQL statement(s) to update the entry for the 'Oak Canyon' trail.
   Set the area to 'Mission Trails Regional Park' and the estimated time
   (est_time) to 2 hours.  Your table should then look like the following:

   +------------------------+------------------------------+----------+----------+
   | trail                  | area                         | distance | est_time |
   +------------------------+------------------------------+----------+----------+
   | Cottonwood Creek Falls | Laguna Mountains             |     1.80 |     1.00 |
   | East Mesa Loop         | Cuyamaca Mountains           |    10.50 |     5.50 |
   | Oak Canyon             | Mission Trails Regional Park |     3.00 |     2.00 |
   +------------------------+------------------------------+----------+----------+

   UPDATE hiking
   SET area = 'Mission Trails Regional Park', est_time = 2
   WHERE trail = 'Oak Canyon' ;
   
7.  Give the SQL statement to delete trails with a distance greater than 5 miles.

    DELETE FROM hiking
    WHERE distance > 5;
    
8.  Give the SQL statement to create a table called 'rating'.  This table rates
    the difficulty of a hiking trail.  It will have two columns: the trail name, 'trail' 
    and the difficulty, 'difficulty'.  The trail name is a string of no more than 50
    characters and the difficulty is an integer (INT).

    CREATE table difficulty ( trail CHAR(50), difficulty INT );

    Show the results of 'show tables'

    +-----------------------+
    | Tables in ssmallen_db |
    +-----------------------+
    | difficulty            |
    | hiking                |
    +-----------------------+
    2 rows in set (0.00 sec)

    Show the results of 'show columns from difficulty'

    +------------+----------+------+-----+---------+-------+
    | Field      | Type     | Null | Key | Default | Extra |
    +------------+----------+------+-----+---------+-------+
    | trail      | char(50) | YES  |     | NULL    |       |
    | difficulty | int(11)  | YES  |     | NULL    |       |
    +------------+----------+------+-----+---------+-------+
    2 rows in set (0.00 sec)

9.  What is the command to delete the difficulty table?  

    DROP TABLE difficulty;


Extra Credit:

  Using the database schema for Movies.sd.com in Part 1, give the SQL commands to create
  a table and insert 5 tuples.  Create two queries that use AND, OR, and LIKE
  and show their results.