Have a Question ?

Home / Answered Questions / CIS365 / cis365-w5a1-joins-group-nest-3-bonus-points-for-early-submission-started-nov-12-q-638

(Solved): CIS365 : W5A1 - Joins/Group/Nest: 3 bonus points for early submission : Started: Nov 15...


CIS 365

W5A1 - Joins/Group/Nest: 3 bonus points for early submission

Started: Nov 15

Quiz Instructions
BONUS POINTS FOR 24HR EARLY SUBMISSION.  THEY WILL BE APPLIED ONLY IF ALL QUESTIONS WERE ANSWERED COMPLETELY - NO BONUS POINTS FOR MISSING FILES.
This is an INDIVIDUAL assignment - do not discuss answers with other class members other than general questions on slack.

Write the SQL scripts (queries) to answer all questions.  Use the Family Adventure Resorts ERD and schema that is provided in the Week 4 module page on Canvas.

You will need to keep a copy of your queries in a notepad file (your notepad file includes queries only, without results) and submit it so that we can copy your queries into our database and test them.
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.  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 the final questions of your assignment.  Make sure you use comment statements to include your name and the assignment number (first screen only), and each full question including the question number and points (reminder - a comment begins with /* and ends with */ for multiple-line images). 

The points for each question typically, are one point for each clause (line of code). The code typed into Canvas will not include comments.

 

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:

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.

Show all columns of data (do not concatenate names).
 

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)
THIS CONTENT IS PROTECTED AND MAY NOT BE SHARED, UPLOADED, SOLD, OR DISTRIBUTED

 

Question 1                                  8 pts
(simple join)

List all condo numbers in building c (building c condos), then the date they were cleaned, then the first and last name of the housekeeper who cleaned them in August, 2019.  Do not list duplicate info.

 

 

Question 2                             6 pts
 (inner join)

Display the activities reserved in June, 2019 for more than 3 people. Display the activity description (activities reserved in june 2019 > 3 participants). Do not include duplicate information.

 

Question 3                       5 pts
(inner join)

Retrieve a list of all guides who did not renewed their certifications last year (2019).  Include ID, first & last name, and (number of days overdue).  Code the calculation on a separate line.

 

Question 4                        7 pts
(simple join)

Management needs the names of all guides who lead an activity with an expired certificate last year - 2019. Display the guide's first & last name, the certificate renewal date, and the date and description (activity) of the activity.

 

Question 5                       11 pts
(inner join)

We need the date (horseback reservation) and first & last names of all girls - the children - who went horseback riding last year - 2019.  List by family name within date.

 

Question 6                            5 pts
(self join - simple)

For each manager (m), retrieve their last name (manager) and hire date and the last names (personnel) and hire dates of all personnel (p) working under them.  Code the manager's data and personnel's data on separate lines.  List by the manager's last name.

 

Question 7                         4 pts
(left join)

Display all guests who have ever participated in an activity, and include those guests in the result who have not participated. Display the guest number and last name, the reservation number, the activity number, the date of the activity, and the number who participated in the activity.  List the results by the reservation number.                
Hint: this requires only 1 statement.

 

Question 8                    4 pts
(right join)

Display all guests who have NEVER participated in an activity.  Display the guest number and last name, and the reservation number.
  Hint: This is a modification of Q7.

 

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.

 

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.

 

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.

 

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.

 

Question 13                  12 pts
Write a combination join and nested query:
Retrieve the date of the reservation and all activity descriptions (a104 guests activities in 2019) that any guest of a104 participated. Housekeeping found a snake and are trying to figure out how it may have gotten into the condo.
Hint: you will bypass the guests table.

 

Question 14                   15 pts
Unit C105 had quite a bit of damage after the last family stayed during the week of July 20, 2019.
Write a fully nested query to obtain a list of the children’s names (children staying in c105 july 20th week) and ages.  Code the age on a separate line.
Hint: you are looking for an exact match on the date so don't use a date function here.  Follow the ERD - use all tables.

 


Question 15                        5 pts
Upload your screen shots file.  Label the filename: firstInitialLastName.pdf
       (i.e. KMoser.pdf)

This file must be uploaded to receive any points for this assignment.  Missing file will result in zero score for this assignment.

5 points for a complete set of screenshots
4 points for somewhat complete
3 points for almost complete
2 points for not quite complete
1 points for not complete
0 points for missing file
Upload 
Choose a File
 

Question 16                      5 pts
Upload your scripts file.  Label the filename: firstInitialLastName.txt
       (i.e. KMoser.txt)
This file must be uploaded to receive any points for this assignment.  Missing file will result in zero score for this assignment.
5 points for a complete set of screenshots
4 points for somewhat complete
3 points for almost complete
2 points for not quite complete
1 points for not complete
0 points for missing file
Upload 

 



We have an Answer from Expert View Expert Answer

Expert Answer


We have an Answer from Expert
Buy This Answer $15

-- OR --

Subscribe To View Unlimited Answers
Subscribe $20 / Month