Question

Use SQL to slove the problem

1. (7) List all condos in building C, the date they were cleaned, and the full name of the staff who cleaned them in August.

2. (5) Display the activities reserved in June for more than 3 people. Include the activity description and label the output as Activities Reserved in June.

3. (4) Listing for all guides and their certification renewal dates for next year. Include full name and hire date.

4. (6) Management needs the names of all guides who lead an activity with an expired certificate. Display the guide's full name, the certificate renewal date, and the date and description of the ‘activity’.

5. (8) We need the date and full names of all girls (children) who went horseback riding this year. List by family name within date.

6. (4) How many rafting trips were taken this year? Label the output, Rafting Trips.

7. (9) List the first and last names and ages of all children who are 7, 10, and 13 years of age. Include the building and unit number of the condo in which they are staying the week of July 29th. Display oldest children first.

8. (7) Management needs a listing of all 3-bed, 2-bath units at the resort that were occupied July 1ST week. Include building, condo, and unit number. Order by unit number within each building.

9. (4) What is the most number of days, the least number of days, and the average number of days our employees have been with the resort? Label all columns Most Days Employed, Least Days Employed and Average Days Employed.

10. (4) For each manager, list their last name (Manager) and hiredate and the last names and hiredates of all employees (Employee) working under them. List by the manager's last name.

11. (4) Management wants a list of first and last names of all employees and the number of months they've been with the company (Months Employed). List the newest employees first.

12. (3) What is the average total cost of all reservations this year? Show the cost in 2 decimal places. Label the output, Average Total Cost in 2017.

13. (3) What were the total revenues for activities this year? Display the output as, Total Activity Revenues.

14. (3) The guests in condo A105 want to reserve an activity but lost the activities brochure that was provided in their condo. They remembered that Eagle was in the description, but cannot recall anything else. List the information for all specified activities for the guests so that they can make their plans.

15. (5) One of our guests left their tablet in condo 6 on June 10th (checkout). We need to call and get the address so that it can be returned. Look up the full name, spouse's name, and phone number. Maybe we can catch them before they leave the area.

16. (4) Which employees have guided reservations for more than 4 people? Include the guide's full name.

17. (11) For each activity completed in June, display the name of the activity (Activity), the guide's last name (Guide), the guest's last name (Guest), and the building and unit number where the guest stayed. List by activity.

18. (9) Some condo fees are increasing. All 3-bed, 2-bath condos have increased 8% in the price of the weekly stay. Make the changes in the database first, and then show the price increase of those condos affected by this price increase. (Use one screenshot only)

THIS IS THE REALATIONSHIP TABLE

PERSONNEL PID LName, FName, Phone, HireDate, Mgr#, PType) Ptype: housekeeping or activity guide Mgr#-employee's manager number AssignedCondos: the condo numbers that have been assigned for cleaning. HOUSEKEEPING (HKID AssignedCondos) CLEANING (Condo#, DateCleaned, HKID) Bdrms-number of bedrooms in unit Baths- number of baths in unit CONDOS (Condo#, Bldg#, UnitNum, SqrFt, Bdrms, Baths, weeklyFee) StartDate - arrival CON DOSTAYS (Condo, Guest, StartDate, EndDate) EndDate departure FAMILIES (Guest FName, Relationship, Birthdate) Guest's children: Relationship: son/daughter

Answer 1

Note: In the below answer, The Current_date() , year(), Month() function would be varyng based on different RDBMS.

1. List all condos in building C, the date they were cleaned, and the full name of the staff who cleaned them in August.
Solution:
SELECT CONDOS.Condo#,CLEANING.DateCleaned,
CONCAT(PERSONNEL.FNAME,PERSONNEL.LName) AS FullName
INNER JOIN CLEANING ON CONDOS.Condo#=CLEANING.Condo#
INNER JOIN CLEANING.HKID = PERSONNEL.PID
WHERE CONDOS.Bldg# = 'C' AND Month(CLEANING.DateCleaned)='August';

3. Listing for all guides and their certification renewal dates for next year. Include full name and hire date.

SELECT GUIDES.CertDate,CONCAT(PERSONNEL.FNAME,PERSONNEL.LName) AS FullName,
GUIDES.CertRenewDate,PERSONNEL.HireDate FROM GUIDES INNER JOIN
PERSONNEL.PID = GUIDES.GID
WHERE Year(CertRenewDate)=Year(CURRENT_DATE)+1

6. How many rafting trips were taken this year? Label the output, Rafting Trips.
Rafting Trips were not mentioned in any table attributes.
-- need more infor for this question. Which tables contains the attribute or value ' Rafting Trips'