1. Display all attributes for all records in the INSPECTS table, sorted by the date of the next inspection.

2. Display the inspector ID, inspector name, building ID, and date of next inspection

for all inspectors who have any inspections scheduled after April 1st, 2020

of buildings with more than three floors,

sorted by inspector name, building ID, and date of next inspection.

3. Display the name of the corporate client and the name of the corporate client who referred it,

for every corporate client referred by a corporate client in the Music industry,

sorted by the corporate client name.

In the output, the column headers should be "Client Name" and "Referred By".

4. Display the staff member ID and staff member name

of staff members cleaning apartments

rented by corporate clients whose corporate location is Chicago,

sorted by staff member name.

Do not display the same staff member more than once.

5. Display the first and last names, current salaries, salaries after a 20% raise, and phone numbers

of each manager who resides in a building that he or she also manages

whose current salary is between $50,000 and $60,000 (use the keyword BETWEEN),

and whose last name either begins with S or ends with T,

ordered by their last name, first name, and phone number.

When displaying salaries in the output, use column headers Current_Salary and New_Salary.

(Note that MPhone attribute is in a table called MANAGERPHONE.)

6. Display the building ID, apartment number, and employee (staffmember or inspector) name of apartments

that are either cleaned by a staffmember employee whose name begins with C

or are located in a building inspected by an inspector employee whose name begins with B,

ordered by the employee name, building ID, and apartment number.

The column header for the employee name should be Employee_Name.

Use the UNION keyword in your solution.

Schema for the HAFH (Home Away from Home) Realty Company. Also see CreateAndLoadHAFH.sql on Canvas.

INSPECTOR table

Attributes:

InsID

InsName

Primary key (InsID)

BUILDING table

Attributes:

BuildingID

BNoOfFloors

ManagerID

Primary key (BuildingID)

Foreign key (ManagerID) references MANAGER(ManagerID) , Relationship: Manages, NOT NULL, ON DELETE RESTRICT

CORPCLIENT table

Attributes:

CCID

CCname

CCIndustry

CCLocation

CCIDreferredby

Primary key (CCID)

Unique key(CCname)

Foreign key (CCIDreferredby) references CORPCLIENT(CCID) , Relationship: Refers, NULL, ON DELETE RESTRICT

MANAGER table

Attributes:

ManagerID

Bdate

MFirstName

MlastName

Mbonus

Msalary

BuildingID

Primary key (ManagerID)

Foreign key (BuildingID) references BUILDING(BuildingID) , Relationship: ResidesIn, NOT NULL, ON DELETE RESTRICT

STAFFMEMBER table

Attributes:

SMemberID

SMemberName

Primary key (SMemberID)

APARTMENT table

Attributes:

BuildingID

AptNo

AnoOfBedrooms

CCID

Primary key (BuildingID, AptNo)

Foreign key (BuildingID) references BUILDING(BuildingID) , Relationship: LocatedIn, NOT NULL, ON DELETE CASCADE

Foreign key (CCID) references CORPCLIENT(CCID) , Relationship: Leases, NULL, ON DELETE RESTRICT

INSPECTS table

Attributes:

InsID

BuildingID

DateLast

DateNext

Primary key (InsID, BuildingID)

Foreign key (InsID) references INSPECTOR(InsID) , Relationship: Inspects, NOT NULL, ON DELETE CASCADE

Foreign key (BuildingID) references BUILDING(BuildingID) , Relationship: Inspects, NOT NULL, ON DELETE CASCADE

CLEANS table

Attributes:

BuildingID

AptNo

SMemberID

Primary key (BuildingID, AptNo, SMemberID)

Foreign key (BuildingID, AptNo) references APARTMENT(BuildingID, AptNo) , Relationship: Cleans, NOT NULL, ON DELETE CASCADE

Foreign key (SMemberID) references STAFFMEMEBER(SMemberID) , Relationship: Cleans, NOT NULL, ON DELETE CASCADE

MANAGERPHONE table

Attributes:

ManagerID

Mphone

Primary key(ManagerID, Mphone)

Foreign key(ManagerID) references MANAGER (ManagerID), NOT NULL, ON DELETE CASCADE

HAFH (Home Away from Home) Realty Company leases apartments to corporate clients. HAFH Realty Company Property Management Database will keep track of HAFH buildings, apartments, corporate clients, building managers, cleaning staff members, and building inspectors.

The property management database for the realty company HAFH will capture data about the following:

For each building: BuildingID (unique) and BNoOfFloors (number of floors in the building);

For each apartment: AptNo (partially unique, i.e., unique within a building) and ANoOf Bedrooms (number of bedrooms in the apartment);

For each corporate client: CCID (unique), CCName (unique), CCLocation, and CCIndustry;

For each manager: ManagerID (unique), MFullName (composed of MFirstName and MLastName), multiple MPhone numbers, MBDate, MAge (derived from MBdate and current date), MSalary, and MBonus (not every manager will have a bonus);

For each staff member: SMemberID (unique) and SMemberName;

For each inspector: InsID (unique) and InsName;

Each building has one or more apartments. Each apartment is located in exactly one building.

Each apartment is either leased to one corporate client or to no one. Each corporate client leases between zero and many apartments.

Each corporate client refers between zero and many corporate clients. Each corporate client can be referred by one corporate client or is not referred by any.

Each apartment is cleaned by either one or two staff members. Each staff member cleans between five and ten apartments.

Each manager manages one or many buildings. Each building is managed by exactly one manager.

Each manager resides in exactly one building. Each building either has one manager residing in it or no managers residing in it.

Each inspector inspects one or many buildings. Each building is inspected by one or many inspectors.

For each building that a particular inspector inspects, the dates of the last inspection by the inspector and of the next future inspection by the inspector are recorded."