The owner of Colonial Adventure Tours knows that being able to run queries is one of the

Question:

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