Knowee
Questions
Features
Study Tools

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.

Question

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.

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

Solution

Based on the instructions provided, here is a SQL query that should provide the desired output:

SELECT 
    building.owner_name, 
    building.address, 
    meter.meter_number, 
    bill.payable_amount
FROM 
    building
JOIN 
    meter ON building.building_id = meter.building_id
JOIN 
    bill ON meter.meter_id = bill.meter_id
WHERE 
    bill.year = 2017 AND 
    bill.month = 12 AND 
    bill.fine_amount IS NULL
ORDER BY 
    building.owner_name ASC;

This query works as follows:

  1. It selects the columns owner_name, address, meter_number, and payable_amount from the tables building, meter, and bill respectively.

  2. It joins the building table with the meter table on the building_id column, and then joins the result with the bill table on the meter_id column.

  3. It filters the results to only include records where the year is 2017, the month is 12, and the fine_amount is null.

  4. Finally, it orders the results in ascending order by the owner_name column.

This problem has been solved

Similar Questions

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 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.Sample output: [Only few records are shown here]ID METER_ID MONTH YEAR DUE_DATE TOTAL_UNITS PAYABLE_AMOUNT IS_PAYED PAYMENT_D FINE_AMOUNT ID METER_NUMBER BUILDING_ID39 26 8 2017 01-SEP-17 20700 724500 1 14-SEP-17 72450 26 SG190123 2614 10 10 2017 01-NOV-17 750 16875 1 09-NOV-17 1687.5 10 SG198329 10

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

Write a query to display the owner_name, building_type name,  meter_number and payment_status of the bill which are raised in the month December 2017.Payment_Status will be payment_date is the payment is done, else if the payment is not done then display as 'Not Paid Yet'. Give an alias name as 'Payment_Status'. Display the record in ascending order based on owner_name.Note:1. 1. Display only the below 4 columns,     i. owner_name     ii. building_type name     iii. meter_number     iv. payment_statusraised in the month december 2017.2. Use Joins3. Tables involved are bill, meter, building and building_type4. Specify condition on year, month of bill table.5. Order by is mandatory. 6. Specify the alias name without any mistake.7. Compare your executed result with the sample output given below.

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.

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.