Project Objectives

Retrieve data from multiple tables

Join the table itself and run additional checks over the rows

Use set operations to combine data from multiple tables

Change the structure of an existing table

Add new data to existing tables

Use transactions to make data updates and revert the updates

Best practices to follow:

Write detailed comments for SQL statements.

Organize and structure SQL statements for readability.

Your tasks are as follows:

Task $1$: The StayWell Property Management team wants a list of all property addresses combined with the name of the owners. You will need to return three columns: the first name as FIRST$\_$NAME, the last name as LAST$\_$NAME, and the property address as ADDRESS of the owners.

Task $2$: StayWell$$s maintenance team wants to recheck the apartments that had plumbing service requests beforehand. You will need to return all the property IDs and the addresses of the properties that had plumbing service request $($CATEGORY$\_$NUMBER $1).$

Task $3$: The StayWell property management team wants to get in touch with those owners having properties larger than $2,000$ square feet. You need to return all the available information of the corresponding owners to the property management team.

Task $4$: The StayWell maintenance team wants to minimize the number of planned visits to properties. The team wants to learn all the properties that have different planned service dates. You need to return all available data in the SERVICE$\_$REQUEST table, where requests are made for the same property with a different NEXT$\_$SERVICE$\_$DATE.

Be sure to only show one set per row for the corresponding service request.

Task $5$: The marketing team wants to celebrate the success of StayWell with a party. The team wants a table with the names of all residents and owners combined into single column named PARTICIPANT. You need to combine this information from the tables and send it back to the team.

You do not need to create a new table in the database schema.

Task $6$: The StayWell marketing team wants to send mail to all residents. You need to return the first name and surname of all the residents combined as NAME, with their addresses named ADDRESS. However, the address should be retrieved from the PROPERTY table for residents.

Task $7$: The development team wants to add new residents and new service requests to StayWell without checking the latest IDs and manually incrementing it$.$ Therefore, you need to alter the RESIDENTS table and change the RESIDENT$\_$ID field to an auto$-$incremented field of type SMALLINT.

Task $8$: The Colombia City office of StayWell indicated that there is a new tenant by the name of Yigit Yilmaz staying at the property with an ID of $13.$ You need to include this new resident in the RESIDENTS table.

Task $9$:

The StayWell property management team wants to add two additional properties and run some simulation tests relating to market coverage. Add the following properties to the PROPERTY table:

PROPERTY$\_$ID OFFICE$\_$NUM ADDRESS SQR$\_$FT BDRMS FLOORS OWNER$\_$NUM

$1419$ Houston Drive $1,10021$ MO$100$

$15111$ Village Drive $1,30031$ CO$103$

However, the team does not want this data in the database once they complete their operations.

Complete the following:

Create a transaction with the changes rolled back.

Task $9$: Create a transaction with the changes rolled back.