Exercise 7: MySQL

The answers for the questions below may be long. Therefore, copy this file to your home directory and place your answers directly into this text using cut and paste. This file should be named exercise.txt and placed within a directory called ex7 inside your exercises_done directory. So, the absolute path name should look like the following:

/home/your_login/exercises_done/ex7/exercises.txt



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 the command: 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

  3. 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.

Part 2: Querying data

Part A:

What are the results of the following queries: 
  1.      SELECT first_name, last_name, email 
     FROM member 
     WHERE state = 'CA';

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

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

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

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

Part B:

Give the SQL statements for the following:
  1. Find all members who get email at mercury.net

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

  3. Find all members who live in Portland, OR

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

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

Part 3: Modifying data

You all have been given your own databases, login_db for this part. Please be sure to log out of the samp_db database and log into your database before continuing. The following is an example of how to log into your own database:

mysql -u ssmallen -h dictator ssmallen_db

Part A:

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

    Put results for the following commands into exercises.txt:
    
    show tables:

    
    show columns from hiking:


  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' );	       

    Put results for the following commands into exercises.txt:

    select * from hiking


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

    Put results of select * from hiking into exercises.txt:


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

    Put results of select * from hiking into exercises.txt:

Part B:

  1. 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 |
   +------------------------+--------------------+----------+----------+
  2. 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 |
   +------------------------+------------------------------+----------+----------+
   | East Mesa Loop         | Cuyamaca Mountains           |    10.50 |     5.50 |
   | Oak Canyon             | Mission Trails Regional Park |     3.00 |     2.00 |
   +------------------------+------------------------------+----------+----------+

  3. Give the SQL statement to delete trails with a distance greater than 5 miles.

  4. 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).

    Put the results of show tables into exercises.txt

    Put the results of show columns from rating into exercises.txt

  5. What is the command to delete the rating table?

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.