Exercises on MySQL for Engineers

Do the following using MySQL DBMS

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?