1. Write an SQL statement to list the reservation ID, trip date, and total trip cost for all reservations. The total trip cost can be calculated by multiplying the trip price times the number of persons on the trip, and then adding the other fees. The total trip cost column should be named TotalCost.

2. Write a query to list all guide first and last names and the state they live in, sorting first by state, then by last name, then by first name, all in ascending order.

3. Write a query to list each state name and the number of customers who live in that state.

4. Write a query to display the count of the number of trips that have a distance between 6 and 12 miles. Include trips that are exactly 6 miles and 12 miles. Name the column NumTrips.

5. Write an SQL statement to list the average trip distance by Type. Your results will show first the average Biking trip distance, then average Hiking trip distance, then the average Paddling trip distance. Name the average column AvgDistance.

6. Write an SQL statement to list the customer's first and last name, reservation ID, and trip date (note: this is a multiple table query - reference the relationships below).

7. Write an SQL statement to display the guide's first name, last name, and trip name for all trips (reference relationships below).

8. Write an SQL statement (using a subquery) to display the first name and last name of all customers who have a reservation with a trip price greater than 28 dollars.

9. Write an SQL statement (INSERT query) to insert yourself as a guide. Use the same format for the primary key (for example, Eric Cameron would be guided EC01). You can make up any address and phone number. Use today's date for your hire date.

10. Write an SQL statement to show all trips and the guides assigned for each trip. Ensure all guides without trips assigned (Ray Marston and yourself) appear in the results.

11. Write an SQL statement (UPDATE query) to change the trip price for reservation 1800029 to 150.00.

12. Write an SQL statement (DELETE query) to delete reservation 1800030.

 

Customer Customer Num LastName FirstName Address City State Postal Code Phone Reservation ReservationID TripID TripDate NumPersons TripPrice Other Fees Customer Num Trip TripID TripName StartLocation State Distance MaxGrpSize Type Season Guide Guide Num LastName FirstName Address City State Postal Code Phone Num Hire Date TripGuides TripID Guide Num FIGURE 1-20: E-R diagram for the Colonial Adventure Tours database