In the following exercise, you will use the Access Files located in the Files se
ID: 3750821 • Letter: I
Question
In the following exercise, you will use the Access Files located in the Files section of your Canvas shell. For this assignment I do not want a MS Access file but rather a snapshot of the SQL commands and the result of the query after it is executed
2. 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 name, customer’s last name, customer’s first name, and total cost for all reservations where the number of persons is greater than four. Use the column name TotalCost for the calculated field.
In the following exercise, you will use the Access Files located in the Files section of your Canvas shell. For this assignment I do not want a MS Access file but rather a snapshot of the SQL commands and the result of the query after it is executed
2. 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 name, customer’s last name, customer’s first name, and total cost for all reservations where the number of persons is greater than four. Use the column name TotalCost for the calculated field.
9 2
Explanation / Answer
1. 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
SELECT trip_id, SUM((trip_price + other_fees) * num_persons) AS total_cost
FROM reservation
GROUP BY trip_id;
2. List the reservation ID, trip name, customer’s last name, customer’s first name, and total cost for all reservations where the number of persons is greater than four. Use the column name TotalCost for the calculated field.
SELECT r.reservation_id, t.trip_name, c.last_name, c.first_name, (r.trip_price + r.other_fees) AS total_cost
FROM reservation r
JOIN trip t
ON r.trip_id = t.trip_id
JOIN customer c
ON r.customer_num = c.customer_num
WHERE r.num_persons > 4;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.