Advanced Queries SQL Homework
Introduction
The purpose of this homework assignment is to assess your ability to write simple queries on multiple tables in SQL, as well as to reinforce some skills you learned doing the first homework:
- Retrieving data from multiple tables using INNER, LEFT, and RIGHT
- Returning all or some
- Returning all or some
- Renaming
- Calculating some descriptive statistics on
- Using Boolean operators NOT, AND, and
- Overring the order of
- Returning a calculated column
The Scenario
“Metro Elite Guesthouse” is a business that uses advanced databases for seamless room categorization, personalized guest management, efficient bookings, and transparent extra charges, setting hospitality standards high. As a consultant, you are going to use SQL to develop and test a set of queries.
Click here to place your order https://assignmenthelpcentral.com/order/
Advanced Queries SQL Homework
General Instructions
- Submissions must be made electronically through Canvas –emailed submissions will not be
- The assignment is due at the date/time indicated in
- After you will have uploaded your answer file, check to make sure that you uploaded the correct file and that it was received. Uploading the wrong file is the same as not turning it in on time
Teams
- Students can elect to work with one other Students can work individually, as well.
- All members of the team will receive the same
Preparation
- Download the workbook SQL-HW2-Answerbook.xlsx and save it using the naming convention outlined
- Rename the workbook using the following naming convention – SQL-HW2-LastFirst.xlsx (if solo) or SQL-HW2-LastFirst1-LastFirst2.xlsx (if a team of two) – this will help us with the sorting and For example, SQL-HW1-BrownAlice-SmithJohn.xlsx.
- Enter your names and GT account numbers in the indicated area on the first worksheet: |Student Id|.
Advanced Queries SQL Homework
General Rules
- You must submit your SQL code as well as a screenshot of the result. An ‘answer’ includes both the relevant SQL and accompanying screenshots. See Query 0 in the answer XLSX for an example of what the screenshot must look like.
- If you need to make your screenshot smaller to fit on the same page as the applicable SQL statement, preserve the aspect ratio of the image to prevent distorting the image.
- You must use the explicit JOIN notation for all multi-table The implicit join notation is no longer considered best practice. See here
- There may be records added to the database in the The queries you author (without revision) will therefore need to accommodate any such additions.
- All queries return at least one row of
- When using GROUP BY, you must refer to field name/s and cannot refer to fields by position (e.g., 1). This is not best practice.
- Do not include in your query output any fields that are not necessary to answer the query question. It is good practice to include extra fields just to help you check your output while you are working but they should be removed before you finalize the query.
Advanced Queries SQL Homework
- List the specific fields required in the output – do NOT use the * wildcard character, unless you are explicitly told this is permissible for that query.
- The class materials cover all the approaches, keywords, techniques and best practices needed to answer all the queries. Therefore, only use the provided course materials in formulating your queries, unless you are explicitly asked to refer to an external Sometimes AI suggests an approach that is not covered in this course and does not, in fact, reflect best practice. In such cases, you will be penalized for deviating from best practice, as using a different approach is evidence of failure to learn the approach as endorsed in the provided course materials.
- The last line of your output may show a row of NULL values. This row is actually an artifact of MySQL Workbench and not part of your query So, there is no reason to be concerned and there is no need to attempt to avoid having this row of NULL values.
- For each query screenshot, you must include a comment with your name/s. Comments are indicated by enclosing them in /* */ and will not be The line should be in the form
/* <First> <Last> */ OR /* <First1> <Last1>, <First2> <Last2> */
Were your name George Burdell, the first line of your query would be:
/* George Burdell */
Advanced Queries SQL Homework
Queries
The name of the schema containing the tables you will be querying is guesthouse. Therefore, designate guesthouse as your default schema. Note: When this is the default schema, there is no need to include the schema name in any query.
A data dictionary for this schema (i.e., an explanation of the tables and fields) and data model have been provided towards the end of this document.
For each query, you will need to determine the appropriate table/s to query. Again, refer to the data model and data dictionary to become more familiar with the database.
Query 1
- Join the following tables (using an INNER JOIN): booking, guest.
- The results of this query should display all fields within the joined For this query, you can use the * wildcard operator.
- You should have 347 records in your
- Note: For this query you do NOT need to include a screenshot, but you still must submit your
Advanced Queries SQL Homework
Query 2
- Extend the previous query to also join the following tables (using INNER JOINs): room, room_type.
- For this query, you can use the * wildcard
- Then extend the query to also join the extra
- Note: Some guests did not buy any extras, some purchased one extra, while other guests purchased more than one extra. For those guests who did not buy any extras, using an INNER JOIN would lose their bookings from the joined dataset (this is not what we want). Accordingly, join the extra table using a LEFT JOIN to preserve all bookings for guests who did not order any extras.
- The results should display all fields within the joined
- You should have 376 records in your
- Note: For this query you do NOT need to include a screenshot, but you still must submit your
Note about the rate table:
- There is one more table: rate. However, there is no need to refer to (or join) this table as it does not feature in any of the below queries. Nonetheless, it is of interest to note:
- The rate table has three fields: room_type, occupancy, room_rate.
- The rate charged for a booking depends on two things: the room type, and the number of occupants (i.e., the number of people staying in the booked room). Accordingly, the rate table has a primary key consisting of the concatenation (combination) of both room_type and Therefore, to ‘join’ the applicable room_rate to a specific booking we would need to take into consideration the rate table’s multi-attribute primary key.
Advanced Queries SQL Homework
Query 3
- Retrieve the booking ID, booking date, and guest ID for all bookings made after January 1, 2023, by guests with the last name ‘Smith’.
- Do not use the * wildcard operator in the SELECT (in this and any of the following queries).
Query 4
- How many bookings were made after January 1, 2023, by guests with the last name ‘Smith’?
- After filtering out unwanted records using a WHERE clause, you will need to pass the filtered dataset to an appropriate aggregator function.
- Rename the column displaying the total number of individual bookings: num_bookings.
Query 5
- For each guest, what is the total number of nights they have booked over all their bookings?
- You must use the GROUP BY clause to group the results by
- Return their guest ID, first name, and last name, alongside the total number of
- Order the output with the guest with the greatest number of nights stayed being listed
- Rename the column with the total number of nights: total_nights.
- Limit the rows of output to
Advanced Queries SQL Homework
Query 6
- For each room type requested, what is the total number of bookings?
- You must use the GROUP BY statement to group the results by room type
- “the total number of bookings” is not referring to booking
- Provide both the room type requested (the ‘category’) as well as the count of bookings for each
- Limit the output to only the room type with the highest number of
- Do not use a join – this query can be answered from the booking
- Rename the column with the number of bookings:
Query 7
- How many individual bookings have been made for each room occupancy level, and what is the average duration of the stays (in nights) for each level of occupancy?
- Room occupancy levels (the number of people who will occupy a booked room) range from 1 to 3 guests.
- You must use the GROUP BY clause to group the results by occupants.
- Rename the column with the number of bookings: num_bookings_occupant.
- Rename the column with the average number of nights: avg_nights.
Advanced Queries SQL Homework
Query 8
- Retrieve the total number of occupants for each room occupancy level and their average stay duration for any room occupancy level with an average stay duration exceeding 3.1 nights.
- Room occupancy levels (the number of people who will occupy a booked room) range from 1 to 3 guests.
- You must use the GROUP BY clause to group the results by occupants.
- You must use the HAVING clause as you may not wish to report results for all occupancy
- Provide both the total number of occupants for each occupancy level (the ‘category’) as well as the average stay duration for each category.
- Rename the column in the output with how many occupants at each occupancy level:
num_occupants.
- Rename the column in the output with the average duration of stay: avg_nights.
Query 9
- Retrieve the total number of occupants for each room occupancy level and their average stay duration for any room occupancy level with an average stay duration exceeding the overall average stay duration across all bookings.
- Room occupancy levels (the number of people who will occupy a booked room) range from 1 to 3 guests.
- You must use the GROUP BY clause to group the results by occupants.
- You must use the HAVING clause as you may not wish to report results for all occupancy
- You must use a subquery to calculate the overall average nights as this value has not been given to you.
- Provide the total number of occupants for each occupancy level (the ‘category’) as well as the average stay duration for each category.
- Rename the column in the output with how many occupants stayed at each occupancy level:
num_occupants.
- Rename the column in the output with the average duration of stay: avg_nights.
Advanced Queries SQL Homework
Query 10
- Retrieve the guest ID, booking ID, and number of nights for any guest whose stay is longer than one standard deviation above the average stay of all bookings.
- You must use a subquery to calculate the one standard deviation above the average stay of all bookings, as this value has not been given to you.
- As we are working with a sample of bookings, use the Sample Standard Deviation – which is used when working with a subset of the population. MySQL has a built-in function that will calculate the Sample Standard Deviation.
- Some information about certain guests may be repeated — which is
- Order the results by the guest id.
- Limit the rows of output to
Query 11
- List the guests who have stayed for a total of more than 10 nights across all their
- Return their guest ID, the total number of their bookings, and the cumulative nights of their
- You must use the GROUP BY clause to group the results by guest
- You must use the HAVING clause to preclude any groups that are not of
- Order the results by the total nights in descending
- Rename the column in the output with the total number of their bookings: tot_book.
- Rename the column in the output with the total nights of their bookings: tot_nights.
Query 12
- Which guests have never made a booking?
- While there are several ways to approach this query, you may wish to consider creating a list of guests who have made a booking and comparing values within that list against the list of all guests (using special logical operators).
- Note: you must do this in a single query and do not use a
- Return their guest ID, first name, and last
- Order the results by the guest id.
- Limit the rows of output to
Advanced Queries SQL Homework
Query 13
- How many bookings do not have any associated extras?
- First retrieve bookings without any These records will then be passed to an aggregator to determine how many individual booking transactions there were.
Query 14
- Which room types have no bookings?
- Using an INNER JOIN will keep only matched records between the joined This could be a problem.
- Provide the room type ID and
Query 15
- Retrieve the bookings when the guest stayed for more nights than the average nights of stay for the room type that the guest booked, and the address of the guest begins with ‘Birmingham’.
- This question requires use of a Common Table Expression (CTE).
- [SQL-5] Subqueries covers what a CTE is and how to use
- For each booking, report the guest ID, booking ID, and the number of nights of the
- Order results by guest ID.
- This question requires use of a Common Table Expression (CTE).