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.Sample output:SubmitSaveExecuteValidatePrevious Submission
Question
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.Sample output:SubmitSaveExecuteValidatePrevious Submission
Solution
Here is a SQL query that should give you the desired result:
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;
This query works as follows:
- It joins the
building,meter, andbilltables based on the relationships betweenbuilding_idandmeter_id. - It groups the results by
meter_number,owner_name, andaddress. - It orders the results by the count of
fine_paidin descending order (to get the owner who paid the most fines at the top). If there are multiple owners with the same number of fines, it then orders them byowner_namein ascending order.
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 all the 'bills' with 'meter' details. Display the records in ascending order based on meter number.Note:1. Display all the columns of bill and meter table2. Use Joins3. Tables involved bill and meter4. Order by is mandatory5. Compare your executed result with the sample output given below.
Write a query to display the details of all the bills whose 'total_units' greater than 10000, sorted by total_units in descending order.Note:1. Display all the columns present in the bill table.2. Specify the condition and retrieve the correct rows in the result(condition need to be specified on total_units 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]SubmitSaveExecutePrevious Submission1
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.
Write a query to display the meter_number from the meter table for which the bill is paid with maximum fine_amount of a month.Note:1. Display only the meter_number from the meter table for which the bill is paid with maximum fine_amount of a month.2. Tables involved are meter and bill3. Compare your executed result with the sample output given below.Sample output:SubmitSaveExecutePrevious Submission
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.