Week5 - Usage of Subqueries to Solve Queries / Design

The questions can come in any order, so make sure you are selecting right option for all questions.

1. Write a query to display all the building details in which building_type named ‘Library’. Display the records in ascending order based on their owner_name.

select * from building where  building_type_id = (select id from building_type where name='Library') order by owner_name;

2. Write a query to display all the contact number of the building_type named 'Police Station'. Display the records in ascending order based on the contact number.

select contact_number from building where  building_type_id=(select id from building_type where name = 'Police Station') order by contact_number;

3. Write a query to display the name of the building_type with the meter_number 'SG824012'.

select name from building_type where id = (select building_type_id from building where id =(select building_id from meter where meter_number = 'SG824012'));

4. Write a query to display the meter_number of the building_type named 'College'. Display the records in ascending order based on their meter_number.

select meter_number from meter where building_id in (select id from building where building_type_id = (select id from building_type where name='College')) order by meter_number;

5. Write a query to display the total_units and payable_amount of the particular meter number 'SG934826'.

select total_units, payable_amount from bill where meter_id = (select id from meter where meter_number= 'SG934826');

6. Write a query to display the number of the buildings who has given the fine amount.Give an alias name as 'building_count'.

select count(id) as building_count from building where id in (select building_id from meter where id in (select meter_id from bill where fine_amount is not null));

7. Write a query to display the average of payable_amount from the bill table for the buildings with electricity connection slab rate less than 24. Give an alias name for payable amount as 'payable_amount'.

select avg(payable_amount) as payable_amount from bill where meter_id in (select id from meter where building_id in (select id from building where building_type_id in (select id from building_type where connection_type_id in (select connection_type_id from slab where rate < 24))));

8. Write a query to display the number of bills from the bill table which are payed for the building with connection_type 'Commercial'. Give an alias name as 'payment_count'.

select count(id) as payment_count from bill where is_payed = 1 and meter_id in (select id from meter where building_id in (select id from building where building_type_id in (select id from building_type where connection_type_id in ( select id from electricity_connection_type where connection_name = 'Commercial'))));

9. 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.

select owner_name from building where id  = (select building_id from meter where id = (select meter_id from bill where total_units = (select max(total_units) from bill)));

10. Write a query to display the meter_number from the meter table which got minimum amount of total_units in a month. Display the records in ascending order based on their meter_number.

select meter_number from meter where id = (select meter_id from bill where total_units = (select min(total_units) from bill));

11. Write a query to display the meter_number from the meter table which got maximum total_units during 8PM for a day in the electricity_reading.

select meter_number from meter where id  = (select meter_id from electricity_reading where  h20  in (select max(h20) from electricity_reading));

12. Write a query to display the meter_number from the meter table which got minimum total_units during 9PM for a day in the electricity_reading. Display the records in ascending order based on their meter_number.

select meter_number from meter where id  in (select meter_id from electricity_reading where  h21  in (select min(h21) from electricity_reading)) order by meter_number;

13. Write a query to display the address from the building table whose connection_type name is 'Home'. Display the records in ascending order based on their address.

select address from building where building_type_id in (select id from building_type where connection_type_id = (select id from electricity_connection_type where connection_name = 'Home')) order by address;

14. Write a query to display the payment_date from the bill table for the meter_number 'SG288942'.

select payment_date from bill where meter_id = (select id from meter where meter_number = 'SG288942');

15. Write a query to display the owner_name from the building table who consumed minimum total_units of current for the day.

select owner_name from building where id  in (select building_id from meter where id in (select meter_id from electricity_reading where total_units in (select min(total_units) from electricity_reading)));

16. 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.

select meter_number from meter where id = (select meter_id from bill where fine_amount = (select max(fine_amount) from bill));

17. Write a query to display the meter_number from the meter table whose electricity_reading registered on a particular day of '2018/05/07'. Display the records in ascending order based on their meter_number.

select meter_number from meter where id in (select meter_id from electricity_reading where day ='07-MAY-18') order by meter_number;

18. Write a query to display the owner_name and contact_number from the building table who have done bill payment in the month of 'October'. Display the records in ascending order based on their owner_name.

select owner_name,contact_number from building where id in (select building_id from meter where id in (select meter_id from bill where payment_date like '%OCT%')) order by owner_name;

19. Write a query to display the sum of h20 and average of h20 from the electricity_reading table consumed by the building_type named as 'Commercial' on 8PM. Give an alias name for total as 'total_amount' and average as 'average_amount'.

select sum(h20) as total_amount,avg(h20) as average_amount from electricity_reading where meter_id in (select id from meter where building_id in (select id from building where building_type_id in (select id  from building_type where connection_type_id in (select id from electricity_connection_type where connection_name='Commercial'))));

20. Write a query to display the rate from the slab table whose meter_number is 'SG567423'. If there are multiple records display in ascending order based on rate.

select rate from slab where connection_type_id = (select connection_type_id from building_type where id = (select building_type_id from building where id = (select building_id from meter where meter_number='SG567423')));