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;
select contact_number from building where building_type_id=(select id from building_type where name = 'Police Station') order by contact_number;
select name from building_type where id = (select building_type_id from building where id =(select building_id from meter where meter_number = 'SG824012'));
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;
select total_units, payable_amount from bill where meter_id = (select id from meter where meter_number= 'SG934826');
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));
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))));
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'))));
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)));
select meter_number from meter where id = (select meter_id from bill where total_units = (select min(total_units) from bill));
select meter_number from meter where id = (select meter_id from electricity_reading where h20 in (select max(h20) from electricity_reading));
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;
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;
select payment_date from bill where meter_id = (select id from meter where meter_number = 'SG288942');
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)));
select meter_number from meter where id = (select meter_id from bill where fine_amount = (select max(fine_amount) from bill));
select meter_number from meter where id in (select meter_id from electricity_reading where day ='07-MAY-18') order by meter_number;
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;
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'))));
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')));