Week5 - Display Data from Multiple Tables / 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 'bills' with 'meter' details. Display the records in ascending order based on meter number

select * from bill b, meter m where b.meter_id = m.id order by m.meter_number;

2. Write a query to display the meter number,owner name,address and contact number of all the buldings. Display the record in ascending order based on owner name.

select m.meter_number, b.owner_name,b.address,b.contact_number 
from building b,meter m 
where b.id=m.building_id 
order by b.owner_name;

3. Write a query to display the meter number,owner name,address,contact number,building type name and connection name of all buildings.Display the records in ascending order based on building type name.

select m.meter_number,b.owner_name,b.address,b.contact_number,t.name,c.connection_name 
from meter m,building b, building_type t, electricity_connection_type c 
where m.building_id=b.id 
	and b.building_type_id=t.id
    and t.connection_type_id = c.id 
order by t.name;

4. Write a query to display the connection name ,from_unit and to_unit and rate of all the slabs.Display the records in ascending order based on connection_name.

select c.connection_name,s.from_unit,s.to_unit,s.rate 
from electricity_connection_type c,slab s 
where s.connection_type_id = c.id 
order by c.connection_name;

5. Write a query to display the building owner name ,address and number of connections present in each building. Display the records in ascending order based on owner name.Give an alias name as connection_count.

select b.owner_name,b.address,count(m.id) as connection_count 
from building b inner 
join building_type t on b.building_type_id = t.id inner 
join meter m on b.id = m.building_id 
group by b.owner_name,b.address 
order by b.owner_name;

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

select b.owner_name,b.address,m.meter_number,bl.payable_amount 
from building b 
join meter m on b.id = m.building_id 
join bill bl on m.id = bl.meter_id 
where bl.year = 2017 
	and bl.month = 12 
    and bl.fine_amount is null 
order by b.owner_name;

7. Write a query to display owner name,address ,meter number and payable amount of all the bill which is having the maximum fine amount. If there is multiple record display the record in descending order by fine_amount.

select b.owner_name, b.address, m.meter_number, bl.payable_amount 
from building b 
join meter m on b.id = m.building_id 
join bill bl on m.id = bl.meter_id 
where bl.fine_amount = (
	select max(fine_amount) 
    from bill) 
order by bl.fine_amount desc;

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

select b.owner_name,b.address,m.meter_number, total_units 
from building b 
join meter m on m.building_id = b.id 
join bill bl on m.id = bl.meter_id 
where bl.month = 12 and bl.year = 2017 
order by bl.total_units desc;

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

select b.owner_name, sum(bl.payable_amount) as totalbillamount 
from building b 
join meter m on b.id = m.building_id 
join bill bl on m.id = bl.meter_id 
where year = 2017 
group by b.owner_name 
having sum(bl.payable_amount) = (
	select max(sum(bi.payable_amount)) 
	from bill bi 
	join meter m on bi.meter_id = m.id 
	join building b on m.building_id = b.id
	where year = 2017 
	group by b.owner_name)
order by b.owner_name

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

select bu.owner_name,bt.name, m.meter_number, 
	case when b.is_payed = 0 then 'Not Paid Yet' 
    	else to_char(b.payment_date, 'DD-MON-YY') end 
        as payment_status 
from bill b 
join meter m on b.meter_id = m.id
join building bu on m.building_id = bu.id 
join building_type bt on bu.building_type_id = bt.id 
where b.year= 2017 and b.month=12 
order by bu.owner_name asc;

11.Write a query to display the connection_name and number of connections under each connection_name and give alias name as connection_count. Display the records in descending order based on connection_count.

select t.connection_name, count(t.id) as connection_count 
from electricity_connection_type t 
join building_type bt on bt.connection_type_id = t.id 
join building b on bt.id = b.building_type_id 
group by t.connection_name 
order by connection_count desc;

12.Write a query to display the building_type name and the number of buildings under the type which has maximum number of buildings. Give an alias name for number of buildings as number_of_buildings and for building name as building_name. If there are multiple records sort in ascending order by building_name.

select bt.name as building_name , count(b.building_type_id) as number_of_buildings 
from building_type bt 
join building b on bt.id = b.building_type_id 
group by bt.name 
having count(b.building_type_id) = (
	select max(building_count) from (
		select count(building_type_id) as building_count 
		from building 
        group by building_type_id
    )  max_buildings
)
order by building_name;

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

select m.meter_number, b.owner_name,b.address 
from bill bi 
join meter m on bi.meter_id = m.id 
join building b on m.building_id = b.id 
group by m.meter_number, b.owner_name, b.address
having count(bi.id) = (
	select max(num_fines) 
	from (
    	select count(id) as num_fines 
    	from bill 
    	group by meter_id
  	) max_fines
) 
order by b.owner_name asc;

14. Write a query to display the meter_number,owner_name and address of the owner who paid second least fine total amount. If there are multiple records display in ascending order based on owner name.

select m.meter_number, bu.owner_name, bu.address 
from bill b
join meter m on b.meter_id = m.id 
join building bu on m.building_id = bu.id 
where b.fine_amount = (
	select min(fine_amount) 
	from bill
	where fine_amount not in (
		select min(fine_amount)
		from bill
	)
)

15. Write a query to display the meter_number ,total_units, payable_amount and hourly usage of by morning, afternoon, evening and night for the month December 2017 of each meter. Display the record in descending order by total_units.

select m.meter_number,
	er.total_units,
    b.payable_amount,
    sum(h6+h7+h8+h9+h10+h11) as morning,
    sum(h12+h13+h14+h15) as afternoon,
    sum(h16+h17+h18+h19) as evening,
    sum(h20+h21+h22+h23+h24+h1+h2+h3+h4+h5) as night
from electricity_reading er
join meter m on er.meter_id = m.id
join bill b on m.id = b.meter_id
where b.month = 12 and b.year = 2017
group by m.meter_number,er.total_units,b.payable_amount
order by er. total_units desc;