The resort manager wants to know the details of the guest who brought their pet to the resort. Write a query to display the guest id, the resort id, the number of days they stayed (give alias name as NUMBER_OF_DAYS), count of adults, children and pet, their total charge incurred. Display the output in the descending order of pet count.
Answers
Answer:
select guestid, resortid,(todate-fromdate) as numberofdays, adultcount, childcount, petcount, totalcharge
from booking
where petcount>0 and childcount>0
order by petcount desc;
Explanation:
Answer:
To display the output in descending order of pet count, the query code has been provided.
Explanation:
To display the required details of the guests who brought their pets to the resort, we can use SQL's SELECT statement along with various functions such as COUNT(), SUM(), and ALIAS to rename the columns for better readability. The following query can be used to retrieve the necessary information:
The Query:
SELECT g.guest_id, r.resort_id, DATEDIFF(g.check_out_date, g.check_in_date) AS NUMBER_OF_DAYS,
COUNT(CASE WHEN g.guest_type = 'Adult' THEN 1 ELSE NULL END) AS ADULT_COUNT,
COUNT(CASE WHEN g.guest_type = 'Child' THEN 1 ELSE NULL END) AS CHILD_COUNT,
COUNT(CASE WHEN g.guest_type = 'Pet' THEN 1 ELSE NULL END) AS PET_COUNT,
SUM(g.charge_incurred) AS TOTAL_CHARGE_INCURRED
FROM guests g
JOIN reservations res ON g.reservation_id = res.reservation_id
JOIN resorts r ON res.resort_id = r.resort_id
WHERE g.guest_type IN ('Adult', 'Child', 'Pet') AND g.has_pet = 1
GROUP BY g.guest_id, r.resort_id, NUMBER_OF_DAYS
ORDER BY PET_COUNT DESC;
In this query, we first select the guest_id and resort_id columns from the guests and resorts tables, respectively. We then use the DATEDIFF function to calculate the number of days the guest stayed in the resort, and alias it as NUMBER_OF_DAYS.
Next, we use the COUNT function with a CASE statement to count the number of adults, children, and pets in each reservation. We then use the SUM function to calculate the total charge incurred by each guest.
To filter the results to only include guests with pets, we use the WHERE clause to select only the rows where guest_type is 'Pet' and has_pet is 1.
Finally, we group the results by guest_id, resort_id, and NUMBER_OF_DAYS, and order them in descending order of PET_COUNT.
In summary, this SQL query retrieves the necessary information about guests who brought their pets to the resort, including their guest_id, resort_id, number of days stayed, counts of adults, children, and pets, and total charge incurred. The query can help the resort manager to better understand the needs of guests who bring pets and make informed decisions regarding pet policies and amenities.
For more such question: https://brainly.in/question/8269576
#SPJ2