CIS 365 Business Database Concepts

W4A1 - Simple SQL Queries (116 points)

Write the SQL scripts (queries) to answer the following questions. Use the Family Adventure Resorts database that is provided. Your queries will be copied in the SQL Editor window pane in MySQL from/to your notepad file (your notepad file includes queries only without results). All results will be shown in the results window pane. Take a screenshot of each query/result – one per page - (similar to the example shown below, cropping out the unnecessary areas) and insert into a Word document - must be read without magnifying glasses!

Incorrect screenshot formats = 10 points deduction!

All screenshots will be inserted into ONE document in the correct numerical order (Q1, Q2, Q3, ...). When finished, save your Word document as a .pdf file. The .pdf file and notepad file will be submitted as:

YourLastName-W4A1.pdf and YourLastName-W4A1.txt

Make sure you use comment statements to include your name (first screen only), the assignment number, and each full question including the question number and points (10 point loss for missing comments. A comment begins with /* and ends with */ - see image below). Use column and table aliases as shown in videos to simplify your queries or to make your output readable. Create queries as if you cannot see the data (start with what you know – no hardcoding!). Scripts coded as shown in lectures; separate lines of code and indent for readability/ease of grading.

Show all columns of data (do not concatenate names). All data shown is from 2019 but check year.

If joining, use SIMPLE joins; the linking info is in the WHERE clause

Q1. (6) List the first name, last name, years employed, and certification renewal date of all guides who had to renew their certifications in 2019 – they are overdue. List by earliest date first.

Q2. (5) Which guides need to renew their certificates this year (2020)? We need to contact them to remind them to renew so that they can work. Get their first name, last name, hire date, and phone number.

Q3. (8) List the first and last names and ages of all children at the resort in 2019, alphabetically by family name and oldest child first. List each child only once. Check year.

Q4. (5) We’re going to be increasing some condo fees. For each condo that is over 1000 square feet but under 1500 square feet, find the building, unit, and current weekly fee. List by building.

Q5. (5) We want to offer vacation weeks at a discount for all guests who visited in 2019 from Northeastern states; New York, New Jersey, Massachusetts, Connecticut, Pennsylvania, and New Hampshire. Find the state, last name, and phone number; show alphabetically by state.

Q6. (8) One of our guests staying at the resort in June 2019 requested information on our availability this year. I

can’t remember the name of the family, but I think they have a daughter; an unusual name but if I saw it, I would remember it. Find the guest number, the family name, the daughter’s name, and phone number of all guests matching this request. Check year.

Q7. (7) How many biking trips over 8 miles were reserved in 2019? Label the output appropriately. Check year.

Q8. (9) What are the names of the guides who led hiking trips over 1 hour long in 2019? Include their hire date and list the employees who have been employed the longest, first. Check year.

Q9. (9) Find the first and last names of all the children who went on a rafting trip in July 2019 for more than 3 hours? List alphabetically by family name. Check year.

Q10. (7) What was the average revenue of all condo rentals in 2019? Show the value with 2 decimal places. Label the output, Average Revenue for Condo Rentals in 2019. Check year.

Q11. (7) We found some personal property that must be returned to one of our guests. Which guests stayed in condo 10 in 2019? Find the registered first name, family name and phone number. Show the results alphabetically by family name. Check year.

Q12. (5) What was the total that the resort earned on activities in 2019? Label the output, Total Revenue for Activities in 2019. Check year.

Q13. (6) We are trying to decide whether to continue the horseback riding and hiking activities. How much did we make in total for both activities in 2019? Label the results appropriately.

Q14. (12) As her family checked out of their condo, Madison Peterson gave her bracelet to the housekeeping staff member, who was cleaning the condo, to hold for her so that her sister would not break it. She forgot to ask for it back. Find the name and phone number of the employee who is holding Madison’s bracelet – we need to return it to her. The room was cleaned Aug 17 of 2019.

Q15. (6) For each manager, find their last name (Manager) and hire date and the last names and hire dates of all employees (Employee) working under them. Show result alphabetically by the manager's last name.

Q16. (4) What is the most number of months, the least number of months, and the average number of months our employees have been with the resort up to today, 2020? Label columns Most Months Employed,

Least Months Employed and Average Months Employed.

Q17. (7) The price of oats is forcing an increase in all horseback riding activities of 15%. Make the changes in the database first and then show the description and the new price of the activities. Show most expensive activity first. (You’ll need to execute the update and select statements separately)