Write a query to get the owner_name from the building table who have consumed maximum total_units of current in a month. Display the records in ascending order based on their owner_name.Note:1. Display only the owner_name from the building table who have consumed maximum total_units of current in a month.2. Tables involved are building, meter and bill3. Order by is mandatory4. Compare your executed result with the sample output given below.

Question

...expand

🧐 Not the exact question you are looking for?Go ask a question

Solution 1

Here is a SQL query that should give you the desired result:

SELECT b.owner_name
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 b.owner_name
HAVING SUM(bi.total_units) = (
  SELECT MAX(total_units_sum)
  FROM (
    SELECT SUM(bi.total_units) AS total_units_sum
    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 b.owner_name
  ) subquery
)
ORDER BY b.owner_name ASC;

This query works as follows:

It joins the building, meter, and bill tables based on their relationships.
It groups the results by owner_name and calculates the sum of total_units for each owner.
It selects the owners whose total units consumption is equal to the maximum total units consumption. This is done by a subquery that calculates the maximum sum of total units.
Finally, it orders the results by owner_name in ascending order.

This problem has been solved

Solution 2

Here

Solution 3

Here is a step-by-step guide to writing the SQL query:

First, you need to join the three tables (building, meter, and bill) together. You can do this using the JOIN keyword. The tables are likely connected through a common field, possibly the building_id or meter_id. For this example, let's assume that the building table has a field called building_id, the meter table has a field called building_id and meter_id, and the bill table has a field called meter_id.
Next, you need to find the maximum total_units of current consumed in a month. You can do this using the MAX() function in SQL, which returns the maximum value in a set of values.
You then need to group the results by owner_name, because you want to find the maximum total_units for each owner. You can do this using the GROUP BY keyword.
Finally, you need to order the results by owner_name in ascending order. You can do this using the ORDER BY keyword.

Here is the SQL query that fulfills these requirements:

SELECT b.owner_name, MAX(bi.total_units) as max_units
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 b.owner_name
ORDER BY b.owner_name ASC;

This query first joins the three tables together. It then selects the owner_name from the building table and the maximum total_units from the bill table. It groups the results by owner_name and orders them in ascending order by owner_name.

This problem has been solved

Similar Questions

Write a query to display the meter_number,owner_name and address of the owner who paid fine most number of times. If there are multiple records display in ascending order based on owner_name.Note:1. Display only the below 3 columns, i. meter_number ii. owner_name iii. address with the condition who paid fine most number of times.2. Use Joins3. Tables involved are building, meter and bill4. Order by is mandatory.5. Compare your executed result with the sample output given below.

Write a query to display owner name,address ,meter number and total units consumed by all the customers in the month december 2017.Display the records in descending order by total_units.Note:1. Display only the below 4 columns, i. owner name ii. address iii. meter number iv. Total unitsby all the customers in the month december 2017.2. Use Joins3. Tables involved building, meter and bill4. Specify condition on year, month column of bill table5. Order by is mandatory.

Write a query to display owner name,address ,meter number and payable amount of all the bill which are all not having fine_amount and that are generated for 2017 December. Display the records in ascending order based on owner name.Note:1. Display only the below 4 columns, i. owner name ii. address iii. meter number iv. payable_amountwith the condition not having fine_amount that are generated for 2017 December.2. Use Joins3. Tables involved building, meter and bill4. Specify condition on year, month and fine_amount columns of bill table. year must be 2017, month must be 12 and fine_amount should be not null5. Order by is mandatory. 6. Compare your executed result with the sample output given below.

Write a query to display the entire details of the 'building' whose owner_name starts with the letter 'M', Display the records in ascending order based on their owner_name.Note:1. Display all the columns present in the building table.2. Specify the condition and retrieve the correct rows in the result(condition need to be specified on owner_name column).3. You can compare your executed result with the sample output given below.4. Order by is mandatorySample output: [Only few records are shown here]

Write a query to display the owner_name and sum of payable_amount who paid the maximum bill amount in the year 2017. If there are multiple records display the record in ascending order based on owner_name Give an alias name as "TotalBillAmount".Note:1. Display only the owner_name and sum of payable_amount who paid the maximum bill amount in the year 2017.2. Use Joins3. Tables involved building, meter and bill4. Specify condition on year column of bill table. sum of payable_amount must be maximum in this year.5. Order by is mandatory. 6. Compare your executed result with the sample output given below

1/3

Upgrade your grade with Knowee

Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.