Description
Single Table Queries
Use Workbench/Command Line to create the commands that will run the
following queries/problem scenarios.
Use MySQL and the Colonial Adventure Tours database to complete the following
exercises.
1. List the last name of each guide that does not live in Massachusetts (MA).
2. List the trip name of each trip that has the type Biking.
3. List the trip name of each trip that has the season Summer.
4. List the trip name of each trip that has the type Hiking and that has a distance longer
than 10 miles.
5. 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). Use the
IN operator in your command.
6. Repeat Exercise 5 and sort the records by state in descending order and then by
customer last name in ascending order.
7. How many trips are in the states of Maine (ME) or Massachusetts (MA)?
8. How many trips originate in each state?
9. How many reservations include a trip price that is greater than $20 but less than $75?
10. How many trips of each type are there?
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. Find the name of each trip containing the word “Pond.”
13. List the guide’s last name and guide’s first name for all guides that were hired before
June 10, 2013.
14. What is the average distance and the average maximum group size for each type of
trip?
15. Display the different seasons in which trips are offered. List each season only once.
16. List the reservation IDs for reservations that are for a paddling trip. (Hint: Use a
subquery.)
17. What is the longest distance for a biking trip?
18. For each trip in the RESERVATION table that has more than one reservation, group
by trip ID and sum the trip price. (Hint: Use the COUNT function and a HAVING clause.)
19. How many current reservations does Colonial Adventure Tours have and what is the
total number of persons for all reservations?
Note: If you could answer any 17 queries correctly, you will get full points.
When you have all of your commands ready to run, test them in MySQL and save it as a
sql script.
What to Hand In
Create a sql script and upload it back to the Assignments link.