Use SQL to slove the problem Q1. (1) How many reservations for activities does e
ID: 3602165 • Letter: U
Question
Use SQL to slove the problem
Q1. (1) How many reservations for activities does each guide have? List the guide ID, last name, and hiredate, and the number of reservations labeled Reservations per Guide. Show only those guides with 4 or more reservations and display the largest number of reservations first.
Q2. (1) List the number of times each family stayed at the resort in 2017 and the total revenue collected from each family for the rented condos. Include the guest number, family last name and label other output as Number of Stays and Total Collected. Show guests with the highest total collected first.
Q3. (1) List all activity types and descriptions that the guests participated in during June. Label the output Activity Types and Popular Activities in June. Do not include duplicate listings. Include the number of people participating in each activity and label the output, Number of Participants. Store the result in June_Activities. Show the output, listing the description first then the type, and finally the number participating. Order the output by type. (one screenshot)
Q4. (1) Write the fully nested query to display the city and state of all guests who reserved a horseback riding activity in June or July.
Q5. (1) Unit C105 had quite a bit of damage after the last family stayed during the week of July 22, 2017. Write a fully nested query to obtain a list of the children’s names alphabetically and their ages. Label the names, Children Staying in C105 July 22nd Week.
Q6. (1) How much money did each guest spend on activities this year? Include the guest's last name and ‘Money Spent on Activities’. Include only those guests spending at least $225.00. Display the highest values first.
Q7. (1) Write a fully nested query to display those guides who lead activities that are more than 10 miles. List their first and last name and years employed by the resort. Store the result in Ten_Mile_Guides. Display the data by employee who has been with the resort the shortest amount of time first.
Q8. (11) Write a combination join and nested query to: List all activities that any guest of A104 participated. Housekeeping found a snake in the condo and are trying to figure out how it may have gotten there. List the date of the activity as well. Label the activity description, Activities for Families in A104.
Q9. (7) Write a transaction script to store the following data: New guests, Martin & Trudy Winters from Mankato Minnesota will be staying at the resort the week starting October 21st, 2017. They have 2 children; son Trevor, who was 5 on June 2, 2017 and daughter Brooke, who turned 8 on Jan 15, 2017. They have not yet decided if they want to participate in any activities. We will assign them guest number G24 and place them in condo 5, A105. They can be reached at 218-555-5872. Check each table along the way to ensure that the data was entered correctly (include SQL to test). Use comments to explain what you are doing at each step. Show each record added.
Q10. (24) Retrieve all invoice data (including details) for the McLean family (see data required on ERD). They are challenging their total bill. Your final output will include all invoice data as shown on the ERD. Hint: Create a series of views keeping those attributes you need along the way; calculate the activity totals, then the invoice details totals, then the invoice condo fees, and finally the total billed. Do not hardcode; start with what you know.
PERSONNEL ame ame redate Type signedCondos CLEANING KID AMILIES CONDOS elationship GUIDES hdate Agel nitNum qrFt drms aths VeeklyFee GUESTS CONDOSTAYS pouseFName Guest# RESERVATIONS ACTIVITIES ndDate otalFeePaid escription ID ID ate lumberinParty otalCost ypeExplanation / Answer
Q1) How many reservations for activities does each guide have? List the guide ID, last name, and hiredate, and the number of reservations labeled Reservations per Guide. Show only those guides with 4 or more reservations and display the largest number of reservations first.
The above query returns guids information and the total reservations who is having reservations count greater than 4.
Q2) List the number of times each family stayed at the resort in 2017 and the total revenue collected from each family for the rented condos. Include the guest number, family last name and label other output as Number of Stays and Total Collected. Show guests with the highest total collected first.
The above query returns the family information and condos they stay in the order highest total amount first.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.