Please complete Task0-task 6 (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 retrieving data from the tables with specific columns, calculations and ordering. In addition, you will work with the date functions of MySQL to gather information from the rows in the tables.

Task 1:

The InstantStay Channel Development team works together with channels in the platform to increase their potential and number of reservations. The Channel development team requires all data available about the channels for their new dashboard team. Run the following query to get the all channel information:

SELECT * FROM CHANNEL; 

The results you send to the Channel Development team should look like the example shown below:

^{All channel information}

Task 2:

In addition, Channel Development team requires the following information for their weekly-channel presentation:

list of all channel names,

channel names ordered by commission in starting with the highest commission rate,

channel names which have commission rates higher than 10%.

Run the following queries and send the collected information:

SELECT ChannelName FROM CHANNEL; 

SELECT ChannelName, ChannelCommission FROM CHANNEL ORDER BY ChannelCommission DESC; 

SELECT ChannelName, ChannelCommission FROM CHANNEL WHERE ChannelCommission > 0.1; 

The results you send to the Channel Development team should look like the example shown below:

Task 3

The InstantStay Finance team requires the actual prices for the stays in the system. Calculate the actual price using the price and discount amount from the corresponding tables. Run the following query to get the actual price data:

SELECT StayID, StayPrice * (1 - StayDiscount) AS 'Actual Price' FROM STAY; 

This statement returns the computed actual price:

^{Actual price data}

Task 4:

The InstantStay Finance team indicates that the retrieved data is not suitable for making payments as currency. You need to round up the actual prices to have only 2 decimals:

SELECT StayID, ROUND(StayPrice * (1 - StayDiscount), 2) AS 'Actual Price Rounded' FROM STAY; 

The above query calculates prices in compliance to the currency:

Task 5

The InstantStay Owner Relationships team wants to send thank you notes to the owners joined in the last year and still in the InstantStay system. Collect all owners joined in the last year and did not leave the system yet:

SELECT * FROM OWNER WHERE (OwnerJoinDate >= DATE_ADD(CURDATE(), INTERVAL - 1 YEAR)) AND OwnerEndDate IS NULL; 

You will get the detailed information about the owners:

^{Owners joined in the last year}

Task 6:

The InstantStay Owner Relationships team wants to send reminders to the owners that left the system. Collect the email address of the owners, notification date as one week later of their leave and last day of the month for financial closure:

SELECT OwnerEmail, DATE_ADD(OwnerEndDate, INTERVAL 1 WEEK) AS NotificationDate, LAST_DAY(OwnerEndDate) AS FinancialClosure FROM OWNER WHERE OwnerEndDate IS NOT NULL; 

This statement will return email address and notification dates as follows:

^{Owners left the system}