Please complete Task0-task 4 (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 combining data from multiple tables. You will use the JOIN functions of MySQL with various different techniques to retrieve the data required for business operations.

Task 1:

The local authorities require all the guest information, such as their first and last name and their stay start and end dates, without checking the existence of reservation data:

SELECT GuestFirstName, GuestLastName, S.StayStartDate, S.StayEndDate FROM GUEST G LEFT JOIN STAY S ON S.GuestID = G.GuestID; 

It is expected to get all guests some of which have no reservation date:

^{Combination of all guests and stays}

Task 2:

The InstantStay Legal team requires all house owner's first and last names along with their house ids and addresses. Collect the information from HOUSE and OWNER tables and return in a consolidated way:

SELECT OwnerFirstName, OwnerLastName, HouseID, HouseAddress, HouseCity, HouseZIPCode, HouseState FROM HOUSE JOIN OWNER USING (OwnerID); 

This statement returns the house information combined with their owners:

^{House and owner data}

Task 3:

The InstantStay Finance team wants to collect all Stay IDs with the price, discount and channel commission rate:

SELECT StayID, StayPrice, StayDiscount, ChannelCommission FROM STAY JOIN CHANNEL ON STAY.ChannelID = CHANNEL.ChannelID; 

The statement returns stay information with their price, commission and discount values:

^{Reservations with price, discount and commission}

Task 4:

In a case wherein guests are canceling the reservations or altering their stay days, the respective reimbursements and cancellations payments are reflected with negative prices in the reservation tables. The InstantStay Finance team requires the list of Stay IDs, GuestIDs, and the positive dollar amount rounded up to the nearest whole number:

SELECT StayID, GuestID, CEIL(ABS(StayPrice)) AS StayPrice FROM STAY WHERE StayPrice < 0; 

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

^{Canceled or altered stays}

Task 5

During the guest user analysis, developers realized there could be duplicate users in the system. Check for the guests with the same name but different GuestIDs to check whether they are duplicate or not:

SELECT * FROM GUEST G JOIN GUEST H ON G.GuestFirstName = H.GuestFirstName AND G.GuestLastName = H.GuestLastName AND G.GuestID != H.GuestID 

The results should look like the example shown below:

^{Duplicate users}