Question

Instructions: Complete the following queries using the Colonial Adventure Tours database. You will find the description including data of this database in page 16 to page 20 in Chapter 1 in your Concepts of Database Management textbook. For each following question, create a query using the Query Design option in Access. Make sure that you save and name each query in the following manner: Query1, Query2......Query14. Query Questions:

Queries:

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

2. List the last name of each guide that does not live in Massachusetts (MA).

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

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

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

6. List the customer number, customer last name, and customer first name of each customer that lives in New Jersey (NJ), New York (NY) or Pennsylvania (PA).

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

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

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. 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 ID, customer number, and total price for all reservations where the number of persons is greater than four. Use the column name TOTAL_PRICE for the calculated field.

12. What is the average distance and the average maximum group size for each type of trip?

13. How many current reservations does Colonial Adventure Tours have and what is the total number of persons for all reservations?

Answer 1

Below is the required queries with screenshots showing results:

Query 1:

SELECT Trip.TripName
FROM Trip
WHERE (((Trip.State)<>'NH'));

Query 2:

SELECT Guide.LastName
FROM Guide
WHERE (((Guide.State)<>'MA'));

Query 3:

SELECT Trip.TripName, Trip.StartLocation
FROM Trip
WHERE (((Trip.Type)='Biking'));

Query 4:

SELECT Trip.TripName
FROM Trip
WHERE (((Trip.Type)='Hiking') AND ((Trip.Distance)>6));

Query 5:

SELECT Trip.TripName
FROM Trip
WHERE (((Trip.Type)='Padding') OR ((Trip.State)='VT'));

Query 6:

SELECT Customer.CustomerNum, Customer.LastName, Customer.FirstName
FROM Customer
WHERE (((Customer.State)='NJ')) OR (((Customer.State)='NY') AND ((Customer.State)='PA'));

Query 7:

SELECT COUNT(*) AS TotalTrips
FROM Trip
WHERE (((Trip.Type)='Hiking')) OR (((Trip.Type)='Biking'));

Query 8:

SELECT Trip.TripName, Trip.State
FROM Trip
WHERE (((Trip.Season)='Summer'))
ORDER BY Trip.State, Trip.TripName;

Query 9:

SELECT Trip.State, Count(Trip.State) AS Trips
FROM Trip
GROUP BY Trip.State;

Query 10:

SELECT COUNT(*) AS Reservations
FROM Reservation
WHERE (Reservation.TripPrice)>20 AND (Reservation.TripPrice)<75;

Query 11:

SELECT Reservation.ReservationID, Reservation.TripID, Reservation.CustomerNum, (Reservation.NumPersons * (Reservation.TripPrice + Reservation.OtherFees)) AS TOTAL_PRICE
FROM Reservation
WHERE (((Reservation.NumPersons)>4));

Query 12:

SELECT Trip.Type, AVG(Trip.Distance) AS Average_Distance, AVG(Trip.MaxGrpSize) AS Average_MaxGrpSize
FROM Trip
GROUP BY Trip.Type;

Query 13:

SELECT COUNT(*) AS Total_Reservations, SUM(Reservation.NumPersons) AS Total_Persons
FROM Reservation;

This completes the requirement. Let me know if you have any questions.

Thanks!