Please complete Task0-task 5 (SQL only)

Scenario and Database Model: InstantStay

Task 0

InstantStay is the new online marketplace to find accommodation in various cities and states. It focuses on gathering the house owners and tenants together as a trusted platform. Hosts join to the system with their houses to be rented out. Similarly, guests join to the system to rent available houses. In addition, channels such as travel agencies, newspapers join InstantStay for connecting customers from different platforms.

In the OWNER table, data for the house owners are collected with their personal information as shown in the following table:

^{OWNER Table}

In the HOUSE table, all houses in InstantStay are collected with detailed address and respective characteristics about the houses:

^{HOUSE Table}

Guests are the customers registered to the platform to access information, offers and stay at houses in InstantStay. Data of the guests are collected in the GUEST table:

^{GUEST Table}

As a large platform, InstantStay hosts multiple channels with different commission rates. The channels related information is collected in the CHANNEL table:

^{CHANNEL Table}

Finally, each reservation in the system is collected in STAY table. Reservations are collected with the corresponding house, guest and channel, start date, and end date. The price information with discount are also tracked in this table. Negative prices indicate the cancellations and the required repayments. Collected data for reservations are as follows:

^{STAY Table}

You are assigned as the database administrator to collect and manage transactional data of the InstantStay operations. Your main task is to create SQL scripts to help other teams in InstantStay to retrieve the required data. In the following tasks, you will collect the requested data to help other teams be successful on their business operations.

In this practice lab, you will work with subqueries to gather information from multiple tables. In further detail, you will first retrieve some information from a table and use that information to filter another table. It is an important task where daily business operations require combining information from different sources.

Task 1:

The InstantStay Finance team require all the available information about the reservations where the commission rate of the channel is higher than 10%.

SELECT * FROM STAY WHERE ChannelID IN (SELECT ChannelID FROM CHANNEL WHERE ChannelCommission > 0.1); 

The statement first looks for all the channels with the commission rate higher than 0.1. Then, it uses the channels to check for the corresponding reservations in the STAY table:

^{Stay information filtered by commission rate}

Task 2:

The InstantStay Finance team requires average price per stay rounded to two decimal places for all the houses in the system. However, ensure that you do not include the cancelled stays with negative payment information.

SELECT HouseID, ROUND(AVG(StayPrice * (1 - StayDiscount)), 2) AS Payment FROM STAY WHERE StayPrice > 0 GROUP BY HouseID; 

The average price information sent to the InstantStay Finance team should resemble the example shown below:

^{Average payment for house}

Task 3:

The InstantStay Marketing team wants to learn the apartment that have more than average number of stays. Use the following script:

SELECT HouseID, COUNT(StayID) AS Stays FROM STAY GROUP BY HouseID HAVING COUNT(StayID) > (SELECT AVG(s.Stays) FROM (SELECT COUNT(StayID) AS Stays FROM STAY GROUP BY HouseID) AS s); 

This statement first calculates the average number of stays for houses and uses this information to compare all other houses. Finally, it collects the HouseIDs and reservation counts for the corresponding apartments:

^{Higher than average used houses}

Task 4:

The Marketing team wants to get all the houses in the system which are larger than the average in size. For the calculation, you will compare against the AVG of HouseSquareMeter for all the houses in InstantStay.

SELECT * FROM HOUSE WHERE HouseSquareMeter >= (SELECT AVG(HouseSquareMeter) FROM HOUSE); 

This statement returns the complete information about the houses larger than average:

^{Houses larger than average}

Task 5:

The Marketing team wants to get the name and email information for all the guests which have been registered into the system so far though not stayed in any property yet. The team is planning to use the collected information to fill the email templates with first name, last name and email fields and then send the reminder emails.

SELECT g.GuestFirstName, g.GuestLastName, g.GuestEmail FROM GUEST g WHERE g.GuestID != ALL (SELECT DISTINCT s.GuestID FROM STAY s); 

This statement collects the guests where their IDs not mentioned in STAY table at all:

^{Guests without reservation yet}

Task 6:

The InstantStay Legal team wants to send new General Data Protection Regulation (GDPR) emails who are registered in the system. Collect email information for all owners and guests.

SELECT OwnerEmail FROM OWNER UNION SELECT GuestEmail FROM GUEST; 

Since the house owners and guests are in separate tables, the above statement will join and return all email addresses:

^{Email address of all users}