Question

Colonial Adventure Tours Case

• 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 exercises.) 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.
• Save the file on your computer with your last name in the file name. (Example: case_1-1 _Jones.doc)
• Click the Choose File button to find and select your saved document.

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 2018.
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 OtherFees value in the Hiking table to $5.00 for all records on which the OtherFees value is $0.00.
17. Use a delete query to delete all trips in the Hiking table where the trip date is 6/12/2018.
18. One of the reservations agents at Colonial Adventure Tours created the query shown in Figure 2-49 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?

Answer 1

select name from TRIP where trip NOT LIKE 'New Hamshire%'


select name ,startLocation from trip where type ='Biking'


select name from trip where type ='Hiking' and distance > 6


select name from trip where type ='Padding' OR startLocation = 'Vermont (VT)'


select count(*) from trip where type in ('Hiking','Biking')



select t.tripName, t.type, MAX(groupSize)
From trip t, guide g
where g.guide ='Susan Kiley'
and g.tripName = t.name;
group by t.tripName, t.type;



select name , startLocation from trip
order by startLocation, name;

select name from trip t, guide g
where t.type ='Hiking'
and g.guide = 'Rita Boyers'
and g.tripName = t.name;


select startLocation,count(*) from trip
group by startLocation



select count(*) from trip
where price > 20 and price < 75




select r.reservationID, t.lastName, t.name
from Reservation, trip
where (select (count*) from Reservation group by name) > 4

Let me know if you have any doubts or if you need anything to change. 

If you are satisfied with the solution, please leave a +ve feedback : ) Let me know for any help with any other questions.

Thank You!
===========================================================================