Please complete Task0-task 9 (SQL only)

Scenario and Database Model: InstantStay

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 collect the information by combining the data in the same database table. In addition, you will use functions for counting, average and maximum calculation while grouping the rows in the tables.

Task 1:

The InstantStay House Development team works on the houses and the coverage of InstantStay over the world. They require a detailed analysis on the count of houses in the InstantStay. To start with, they require the count of houses based in each state in a descending order:

SELECT HouseState, COUNT(HouseID) FROM HOUSE GROUP BY HouseState ORDER BY COUNT(HouseID) DESC; 

The above query returns the state and the number of houses located in the corresponding state in the descending order:

^{Number of houses by state}

Task 2:

In addition, House Development team wants the same information (as mentioned in the Task 1) along with the city, state level details:

SELECT HouseState, HouseCity, COUNT(HouseID) FROM HOUSE GROUP BY HouseState, HouseCity ORDER BY COUNT(HouseID) DESC; 

This statement returns the number of houses collected by city and state level:

^{Number of houses by state and city}

Task 3:

The House Development team considers that having a limited availability of houses in a state could be risky and less beneficial for the business. For Example, The InstantStay will be unable to process the reservation requests if the request count is higher than count of registered houses in the state currently available to rent out. Therefore to take further steps to work on such issues, the team requires to know all the states having less than 2 properties registered in the system:

SELECT COUNT(HouseID), HouseState FROM HOUSE GROUP BY HouseState HAVING COUNT(HouseID) < 2 ORDER BY COUNT(HouseID) DESC; 

The above query will collect the states with having less than 2 houses in the system:

^{States with limited availability}

Task 4:

The House Development team also requires to calculate the total number of rooms available in each state:

SELECT HouseState, SUM(HouseNumberOfRooms) AS TotalAvailability FROM HOUSE GROUP BY HouseState; 

The above SQL statement calculates the total availability as the sum of rooms for each state:

^{States with total availability}

Task 5:

In addition, the House Development team wants to know the largest and average house in terms of number of rooms for each state:

SELECT HouseState, MAX(HouseNumberOfRooms) AS LargestHouse, AVG(HouseNumberOfRooms) AS AverageHouse FROM HOUSE GROUP BY HouseState; 

The above SQL query will return the largest house and average house for each state:

^{Average and largest houses by state}

Task 6:

The InstantStay Marketing team is planning to create some maps to show the coverage of InstantStay throughout the country. Therefore, they need some specific information such as distinct ZIP codes of all houses and distinct list of cities in two separate tables:

SELECT DISTINCT HouseZIPCode FROM HOUSE; 

SELECT DISTINCT HouseCity FROM HOUSE; 

This statement will return two tables, one for ZIP codes and other for cities:

^{Distinct ZIP codes and cities in two tables}

Task 7:

In addition, the InstantStay Marketing team wants to create a word cloud from the cities of the houses. They want to learn the number of characters in the longest city:

SELECT MAX(LENGTH(HouseCity)) FROM HOUSE; 

It returns a single entry for the length of the longest city:

^{City length}

Task 8:

The InstantStay Owner Relationships team focus on the success of InstantStay by creating strong connection to the owners. They want to send celebration mails to the owners on their joining date in the system.

They need the combined details which includes name and surname of the owners with their email addresses. In addition, they are planning to make this as practice for every year. The team requires the day and month of owners joining date to send emails on exact dates every year:

SELECT CONCAT(OwnerFirstName, CONCAT(' ', OwnerLastName)) AS Name, OwnerEmail, MONTH(OwnerJoinDate) AS Month, DAY(OwnerJoinDate) AS Day FROM OWNER; 

The above SQL query summarized the required information for the celebration emails:

^{Joint date and owner summary}

Task 9:

The team wants to create a specialized carpets to the houses with the initials of owners. They wanted to get the uppercase first letter of firstname and surname of the owners:

SELECT UPPER(SUBSTR(OwnerFirstName, 1, 1)) AS initial_1, UPPER(SUBSTR(OwnerLastName, 1, 1)) AS initial_2 FROM OWNER; 

The output of the above query should resemble the example shown below:

^{Initials of the owners}