The owner of Colonial Adventure Tours knows that being able to run queries is one of the most important benefits of using a DBMS. In the following exercises, you will use the data in the Colonial Adventure Tours database shown in Figures 1-15 through 1-19 in Chapter 1. (If you use a computer to complete these exercises, use a copy of the Colonial Adventure Tours database so you will still have the original data when you complete Chapter 3.) In each step, use QBE to obtain the desired results. You can use the query feature in a DBMS to complete the exercises using a computer, or you can simply write a description of how you would complete the task. Check with your instructor if you are uncertain about which approach to take.

1. List the name of each trip that does not start in New Hampshire (NH).

2. List the name and start location for each trip that has the type Biking.

3. List the name of each trip that has the type Hiking and that has a distance greater than six miles.

4. List the name of each trip that has the type Paddling or that is located in Vermont (VT).

5. How many trips have a type of Hiking or Biking?

6. List the trip name, type, and maximum group size for all trips that have Susan Kiley as a guide.

7. List the trip name and state for each trip that occurs during the Summer season. Sort the results by trip name within state.

8. List the name of each trip that has the type Hiking and that is guided by Rita Boyers.

9. How many trips originate in each state?

10. How many reservations include a trip with a price that is greater than $20 but less than $75?

11. List the reservation ID, customer last name, and the trip name for all reservations where the number of persons included in the reservation is greater than four.

12. List the trip name, the guide€™s first name, and the guide€™s last name for all trips that originate in New Hampshire (NH). Sort the results by guide€™s last name within trip name.

13. List the reservation ID, customer number, customer last name, and customer first name for all trips that occur in July, 2016.

14. Colonial Adventure Tours calculates the total price of a trip by adding the trip price plus other fees and multiplying the result by the number of persons included in the reservation. List the reservation ID, trip name, customer€™s last name, customer€™s first name, and total cost for all trips where the number of persons is greater than four.

15. Create a new table named Hiking that includes all columns in the Reservation table where the trip type is Hiking.

16. Use an update query to change the Other Fees value in the Hiking table to $5.00 for all records on which the Other Fees value is $0.00.

17. Use a delete query to delete all trips in the Hiking table where the trip date is 6/12/2016.

18. One of the reservations agents at Colonial Adventure Tours created the query shown in Figure 2-46 to list each trip name and the last name and first name of each corresponding guide. The query results included 410 records and he knows that this result is incorrect. Why did he get so many records? What should he change in the query design to get the correct query results?

Figure 1-15

Figure 2-46

GuideNum LastName FirstName Address State PostalCode PhoneNum City HireDate AM01 BR01 617-555-6032 6/3/2012 54 Quest Ave. 140 Oakton Rd. 25 Old Ranch Rd. 7 Moose Head Rd. Dummer 943 Oakton Rd. 9 Congaree Ave. 24 Shenandoah Rd. Springfield 12 Heather Rd. 15 Riverton Rd. Williamsburg MA Jaffrey Abrams Miles 01096 03452 603-555-2134 3/4/2012 Boyers Devon Rita NH Harley 1/8/2012 01375 781-555-7767 DH01 Sunderland MA GZ01 603-555-8765 11/4/2012 Gregory Kiley Zach NH 03588 KS01 Jaffrey Fraconia 4/8/2013 03452 603-555-1230 NH Susan 603-555-0003 6/10/2013 03580 KS02 Kelly Marston Rowan Sam NH MA MR01 01101 781-555-2323 9/14/2015 Ray RH01 Mount Desert ME 207-555-9009 6/2/2014 Hal 04660 Stevens SL01 05825 802-555-3339 9/5/2014 Lori Coventry VT Unser 342 Pineview St. CT UG01 203-555-8534 2/2/2015 Glory Danbury 06810