NEED HELP WITH THESE SQL COMMANDS ASAP! the tables are a file that you can not s

Question

NEED HELP WITH THESE SQL COMMANDS ASAP! the tables are a file that you can not see it has to be uploaded to oracale i juts need help with the commands!

Colonial Adventure Tours Use SQL and the Colonial Adventure Tours database to complete the following exercises. Provide screen snips of query outputs st 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. 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?

Explanation / Answer

Answer)

Table names and design should be provided. Other than that, we have to assume the assume the tables and the design. Please change the attributes to the original ones in the table if found to be different.

5. List customer number, customer last name, customer first name of each customer that lives in NJ, NY, or PY.

Answer:

select customer_number, customer_last_name, customer_first_name from customer where state in ('NJ','NY','PA');

This is a simple query where the customer_number, customer_last_name, customer_first_name are printed where the location is given as in the question options.

6. Sort the records by state in descending order and then by customer in ascending order.

Answer:

select customer_number, customer_last_name, customer_first_name from customer where state in ('NJ','NY','PA') ORDER BY state DESC, customer_last_name ASC;

Order by is used to order the results in the ascending and descending order as wanted in the question.

7. How many trips are in the states if ME or MA?

Answer:

select count(trip_id) from trip where state in ('ME','MA');

Let the table name be trip where the trip_id is a primary key, so we use the count to get the count where state is in ME or MA.

8. How many trips originate in each state?

Answer:

select count(trip_id) from trip group by state;

We use the count to get the count of the number of trips and group by the state, so that we get the count for the number of trips in each state.

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.