For this assignment (joins), link the tables in either the WHERE clause or in the FROM clause - depending on the requirements of the question. It will be clearly stated. When coding join...on, join the tables in alphabetical order - families before guests), then link the tables on a separate line. After tables are properly linked, code the other conditions for the result. For table aliases, use the first letter of the table name: activities = a families = f invoice = i invoicedetails = id cleaning = cl condos = c guests = 9 guides = gu housekeeping = h personnel = p condostays = cs reservations ar For column aliases, if you are asked to include them, they will be (shown in parenthesis). Always use "double quotes" for column aliases; do not code the word (as). Code ALL statements in lowercase, one clause per line Qualify attributes only if they are spelled identically in all statement tables (i.e. s.s# = c.s#) or if needed by the query. When ordering the result, use the column reference (i.e. - order by 4). When comparing dates, use date functions. Dates are considered character data - use a single quote around any part of the date. When joining, code the join specified in the parenthesis before the question: - for simple joins; the linking info is in the WHERE clause - for inner or outer joins, omit the word (inner/outer) and use Join... On, or left/right Join... On (follow the formats given in the lecture videos) Ptype: housekeeping or activity guide PERSONNEL (PID, LName, FName, Phone, HireDate, MgrNum, PType) MgrNum - employee's manager number HOUSEKEEPING (HKID, Assigned Condos) Assigned Condos: the condo numbers that have been assigned for cleaning. CLEANING (CondoNum, DateCleaned, HKID) CONDOS (CondoNum, BldgNum, UnitNum, SqrFt, Bdrms, Baths, WeeklyFee) Bdrms - number of bedrooms in unit Baths - number of baths in unit CONDOSTAYS (CondoNum, GuestNum, StartDate, EndDate) StartDate - arrival EndDate - departure FAMILIES (GuestNum, FName, Relationship, Birthdate) Guest's children: Relationship: son/daughter GUESTS (GuestNum, RLName, RFname, City, State, Phone, SpouseFName) Rename - last name of registered guest RFname - first name of registered guest INIVALEN GUESTS (GuestNum, RLName, RFname, City, State, Phone, SpouseFName) Rlname - last name of registered guest RFname - first name of registered guest INVOICE (INVNum, CondoNum, GuestNum) 4 INVOICEDETAILS (INVNum, RID, AID) RDate - date of reservation NumberinParty -number of people participating in the group RESERVATIONS (RID, Guest Num, AID, GID, RDate, NumberinParty) Hrs - number hours for the activity PPP - price per person Distance -length in miles ACTIVITIES (AID Description, Hrs, PPP, Distance, Type) GUIDES (GID, CertDate, CertRenewDate) Question 9 8 pts 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. Then retrieve the price increase of only those condos affected by this price increase - show all data for those records. (Use one screenshot only for your screen shot file). Attempt this question after all prior questions are completed. -- beds --baths -- beds Question 10 9 pts How many reservations for activities does each guide have? List the guide ID, last name, and hire date, and the number of reservations (reservations per guide). Calculate the number of reservations on a separte line. Show only those guides with 4 or more reservations and display the largest number of reservations first. -- personnel & guides -- reservations Question 11 10 pts Retrieve the guest number, family last name, the number of times each family stayed at the resort in 2019 (number of stays in 2019) and the total fees collected (total condo fees collected) from each family for the rented condos. Calculate the number of stays and total fees on separate lines. Show guests with the highest total collected first. -- condos and condostays --guests Question 12 11 pts Write the fully nested query to display the guest number (guests on horseback), city and state of all guests who reserved a horseback riding activity in June and July of 2019. For this assignment (joins), link the tables in either the WHERE clause or in the FROM clause - depending on the requirements of the question. It will be clearly stated. When coding join...on, join the tables in alphabetical order - families before guests), then link the tables on a separate line. After tables are properly linked, code the other conditions for the result. For table aliases, use the first letter of the table name: activities = a families = f invoice = i invoicedetails = id cleaning = cl condos = c guests = 9 guides = gu housekeeping = h personnel = p condostays = cs reservations ar For column aliases, if you are asked to include them, they will be (shown in parenthesis). Always use "double quotes" for column aliases; do not code the word (as). Code ALL statements in lowercase, one clause per line Qualify attributes only if they are spelled identically in all statement tables (i.e. s.s# = c.s#) or if needed by the query. When ordering the result, use the column reference (i.e. - order by 4). When comparing dates, use date functions. Dates are considered character data - use a single quote around any part of the date. When joining, code the join specified in the parenthesis before the question: - for simple joins; the linking info is in the WHERE clause - for inner or outer joins, omit the word (inner/outer) and use Join... On, or left/right Join... On (follow the formats given in the lecture videos) Ptype: housekeeping or activity guide PERSONNEL (PID, LName, FName, Phone, HireDate, MgrNum, PType) MgrNum - employee's manager number HOUSEKEEPING (HKID, Assigned Condos) Assigned Condos: the condo numbers that have been assigned for cleaning. CLEANING (CondoNum, DateCleaned, HKID) CONDOS (CondoNum, BldgNum, UnitNum, SqrFt, Bdrms, Baths, WeeklyFee) Bdrms - number of bedrooms in unit Baths - number of baths in unit CONDOSTAYS (CondoNum, GuestNum, StartDate, EndDate) StartDate - arrival EndDate - departure FAMILIES (GuestNum, FName, Relationship, Birthdate) Guest's children: Relationship: son/daughter GUESTS (GuestNum, RLName, RFname, City, State, Phone, SpouseFName) Rename - last name of registered guest RFname - first name of registered guest INIVALEN GUESTS (GuestNum, RLName, RFname, City, State, Phone, SpouseFName) Rlname - last name of registered guest RFname - first name of registered guest INVOICE (INVNum, CondoNum, GuestNum) 4 INVOICEDETAILS (INVNum, RID, AID) RDate - date of reservation NumberinParty -number of people participating in the group RESERVATIONS (RID, Guest Num, AID, GID, RDate, NumberinParty) Hrs - number hours for the activity PPP - price per person Distance -length in miles ACTIVITIES (AID Description, Hrs, PPP, Distance, Type) GUIDES (GID, CertDate, CertRenewDate) Question 9 8 pts 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. Then retrieve the price increase of only those condos affected by this price increase - show all data for those records. (Use one screenshot only for your screen shot file). Attempt this question after all prior questions are completed. -- beds --baths -- beds Question 10 9 pts How many reservations for activities does each guide have? List the guide ID, last name, and hire date, and the number of reservations (reservations per guide). Calculate the number of reservations on a separte line. Show only those guides with 4 or more reservations and display the largest number of reservations first. -- personnel & guides -- reservations Question 11 10 pts Retrieve the guest number, family last name, the number of times each family stayed at the resort in 2019 (number of stays in 2019) and the total fees collected (total condo fees collected) from each family for the rented condos. Calculate the number of stays and total fees on separate lines. Show guests with the highest total collected first. -- condos and condostays --guests Question 12 11 pts Write the fully nested query to display the guest number (guests on horseback), city and state of all guests who reserved a horseback riding activity in June and July of 2019