given these tables

SQL QUERIES NEEEDED

1. For each country and month, derive the portion of rooms which are reserved, free, and unavailable.

2. For each country, derive the portion of rooms which are reserved. Associate a rank to each country according to the portion of checkout rooms for that country in a particular year with respect to all reserved rooms for that country. The country with the highest ratio of checkout rooms in a particular year must rank first.

3. For each country and month, produce the cumulative income of 4-star hotels.

Assumption

The room status needs to be calculated from the booking table and the checkout table i.e

Free room = total rooms-(booked + checked out) at any given time

Unavailable = checked out at any given time

Reserved = booked at any given time

Example

Query for the above table

SELECT T1.country, T1.year, T1.month ,T1.booked,T2.checkedout, (100- (T1.booked + T2.checkedout)) AS 'Free' FROM ( SELECT hotel.country, time.year, time.month, COUNT(booking.room_id) as booked FROM booking LEFT JOIN room on room.room_id = booking.room_id LEFT JOIN hotel on room.hotel_id = hotel.hotel_id LEFT JOIN time on booking.time_id = time.time_id GROUP BY hotel.country, time.year, time.month ORDER by hotel.country, time.year, time.month ) AS T1 INNER JOIN ( SELECT hotel.country, time.year, time.month, COUNT(checkout.room_id) as checkedout FROM checkout LEFT JOIN room on room.room_id = checkout.room_id LEFT JOIN hotel on room.hotel_id = hotel.hotel_id LEFT JOIN time on checkout.time_id = time.time_id GROUP BY hotel.country, time.year, time.month ORDER BY hotel.country, time.year, time.month )AS T2 ON T1.country=T2.country AND T1.year=T2.year AND T1.month=T2.month

Time PK time id Booking day Agent PK booking id month PK agent_id FK customer id ear name FK room_id Room FK hotel id PK room id FK agent id FK hotel_id FK time id no of beds revenue tv whirlpool bath promotion rate Customer Checkout PK customer id PK checkout id name FK hotel id Hotel FK customer_id PK hotelid FK time id city proince cou ntry category