Question

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?

Answer 1

Solution

1)
SELECT LAST_NAME FROM GUIDE
WHERE STATE <> 'MA';

2)
SELECT TRIP_NAME FROM TRIP
WHERE TYPE='Biking';

3)
SELECT TRIP_NAME FROM TRIP
WHERE SEASON='Summer';

4)
SELECT TRIP_NAME FROM TRIP
WHERE TYPE='Hiking' AND DISTANCE > 10;

5)
SELECT CUSTOMER_NUM, LAST_NAME, FIRST_NAME FROM CUSTOMER
WHERE STATE IN('NJ','NY','PA');

6)
SELECT CUSTOMER_NUM, LAST_NAME, FIRST_NAME FROM CUSTOMER
WHERE STATE IN('NJ','NY','PA')
ORDER BY STATE DESC,LAST_NAME ASC;

7)
SELECT COUNT(*) FROM TRIP
WHERE STATE IN('ME','MA');

8)
SELECT STATE, COUNT(TRIP_ID) FROM TRIP
GROUP BY STATE
ORDER BY STATE;

9)
SELECT COUNT(*) FROM RESERVATION
WHERE TRIP_PRICE>20 AND TRIP_PRICE<75;

10)
SELECT TYPE, COUNT(TRIP_ID) FROM TRIP
GROUP BY TYPE
ORDER BY TYPE;

---

answered all the questions

if you have any doubt, please mention it, love to help

all the best

please upvote