Here is the SQL query that you can use to get the desired result:

```sql
SELECT m.meter_number, b.owner_name, b.address
FROM building b
JOIN meter m ON b.building_id = m.building_id
JOIN bill bi ON m.meter_id = bi.meter_id
GROUP BY m.meter_number, b.owner_name, b.address
ORDER BY COUNT(bi.fine_paid) DESC, b.owner_name ASC;
```

Here is the step by step explanation of the query:

1. `SELECT m.meter_number, b.owner_name, b.address`: This line is selecting the columns that you want to display in the result.

2. `FROM building b`: This line is specifying the `building` table as the main table for the query.

3. `JOIN meter m ON b.building_id = m.building_id`: This line is joining the `meter` table with the `building` table on the `building_id` column.

4. `JOIN bill bi ON m.meter_id = bi.meter_id`: This line is joining the `bill` table with the `meter` table on the `meter_id` column.

5. `GROUP BY m.meter_number, b.owner_name, b.address`: This line is grouping the result by the `meter_number`, `owner_name`, and `address` columns.

6. `ORDER BY COUNT(bi.fine_paid) DESC, b.owner_name ASC`: This line is ordering the result by the number of times a fine was paid in descending order and then by the `owner_name` in ascending order.

Please replace the column and table names with the actual ones in your database.

Question

Here is the SQL query that you can use to get the desired result:

```sql
SELECT m.meter_number, b.owner_name, b.address
FROM building b
JOIN meter m ON b.building_id = m.building_id
JOIN bill bi ON m.meter_id = bi.meter_id
GROUP BY m.meter_number, b.owner_name, b.address
ORDER BY COUNT(bi.fine_paid) DESC, b.owner_name ASC;
```

Here is the step by step explanation of the query:

1. `SELECT m.meter_number, b.owner_name, b.address`: This line is selecting the columns that you want to display in the result.

2. `FROM building b`: This line is specifying the `building` table as the main table for the query.

3. `JOIN meter m ON b.building_id = m.building_id`: This line is joining the `meter` table with the `building` table on the `building_id` column.

4. `JOIN bill bi ON m.meter_id = bi.meter_id`: This line is joining the `bill` table with the `meter` table on the `meter_id` column.

5. `GROUP BY m.meter_number, b.owner_name, b.address`: This line is grouping the result by the `meter_number`, `owner_name`, and `address` columns.

6. `ORDER BY COUNT(bi.fine_paid) DESC, b.owner_name ASC`: This line is ordering the result by the number of times a fine was paid in descending order and then by the `owner_name` in ascending order.

Please replace the column and table names with the actual ones in your database.

Knowee AI · Accepted Answer