Description
Use MySQL to make the following changes to the Colonial Adventure Tours database.
After each change, execute an appropriate query to show that the change was made
correctly.
1. Create a PADDLING table with structure shown in Figure-1
Figure-1
Column Type Length Decimal
Places
Null Allowed Description
Trip_ID Decimal 3 0 NO Trip Id (Primary
Key)
Trip_Name Char 75 Trip Name
State Char 2 Trip State
Distance Decimal 4 0 Trip Distance
Max_GRP_Size Decimal 4 0 Maximum number
of people on trip
Season Char 20 Season in which
trip takes place
2. Insert into the PADDLING table the trip ID, trip name, state, distance, maximum group size,
and season from the TRIP table for only those trips having type PADDLING.
NOTE: Do not use individual inserts, but only use one command instead.
3. For all trips located in Connecticut (CT), increase the maximum group size by two. Update
the PADDLING table accordingly.
4. Insert a new trip into the PADDLING table. The trip ID is 43, the trip name is Lake Champlain
Tour, the state is Vermont (VT), the maximum group size is 12, the distance is 16, and the
season is Summer.
5. Delete the trip in the PADDLING table with the trip ID 23.
6. The distance for the Pontook Reservoir Tour trip has been increased to an unknown number.
Change the PADDLING table to reflect this change.
7. Add to the PADDLING table a new character column named DIFFICULTY_LEVEL that is
three characters in length. Then set the default difficulty level value for all rows to the value
MOD.
8. Change the DIFFICULTY_LEVEL column in the PADDLING table to HRD for the Lake
Champlain Tour trip.
9. Change the length of the SEASON column in the PADDLING table to 25 characters.
10. Change the DIFFICULTY_LEVEL column in the PADDLING table to reject nulls.
11. Delete the PADDLING table from the database.
What to Hand In
Create a SQL script and upload it back to the Assignments link.
