Colonial Adventure Tours

Answer each of the following questions using the Colonial Adventure Tours database (please download the original copy of the database). Please code SQL statements in Access to obtain the desired results. Program and run the following queries. Save each query as Query01, Query02, ..., Query10.

NOTE: You must write your SQL using the syntax you learned in class and in your textbook (lecture notes). If you generate your SQL from QBE, you will get zero point. For example, if you use INNER JOIN for JOIN operation, you will get zero point.

• List the name (TripName) and start location (StartLocation) for each trip that has the type 'Paddling'.
• List the name of each trip (TripName) that has the type 'Hiking' and that has a distance of between 10 miles and 20 miles.
• How many trips have a type of Hiking or Biking? Make the column name 'Number_of_Trips' in the resulting table.
• List the trip ID (TripID) and its customer names (FirstName, LastName).
• List the trip name (TripName), trip type (Type), and maximum group size (MaxGrpSize) for all trips that have Sam Kelly as a guide.
• List the trip name (TripName) and state (State) for each trip that occurs during the Summer season. Sort the results by trip name within state in ascending order. (Hint: State is the major sort key.)
• List the name of each trip (TripName) that has the type 'Hiking' and that is guided by Lori Stevens.
• How many trips originate in each state? (Hint: The result needs a State column and a column for number of trips. Please name the column for number of trips as 'Number_of_Trips')
• How many reservations include a trip with a price (TripPrice) that is greater than $20 but less than $75? Please name the column 'Number_of_Reservations' in the resulting table.
• List the reservation ID (ReservationID), customer number (CustomerNum), customer first name (FistName), customer last name (LastName), and TripDate for all trips that occur in July 2016.