Week5 - Views / Design

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

1. Create a view named 'building_details' to select all the details present in the building table.

create view building_details as
select *
from building;

2. Create a view named "building_owners" to select all the owner_name present in the building table.

create view building_owners as
select owner_name
from building;

3. Create a view named 'owner_details' to select the owner_name and contact_number of the buildings with the length of owner_name greater than 15.

create view owner_details as
select owner_name,contact_number
from building
where length(owner_name)>15;

4. Create a view named 'bill_details' to select all the details of the bills which has completed payment with no fine amount.

create view bill_details as
select *
from bill
where fine_amount is null

5. Create a view named 'electricity_reading_details' to select all the details of the electricity_reading whose total_units per day is greater than 500 units.

create view electricity_reading_details as
select *
from electricity_reading
where total_units > 500;

6. Create a view named 'meter_details' to select all the details of the buildings whose meter_number starts with 'SG' and ends with '2'.

create view meter_details as
select * 
from building 
where id in (
  select building_id 
  from meter 
  where meter_number 
  like 'SG%2')

7. Create a view named "home_buildings" to select the name of the building_type whose electricity connection type is ‘Home’.

create view home_buildings as
select name 
from building_type 
where connection_type_id in (
  select id
  from electricity_connection_type
  where connection_name = 'Home')

8. Create a view named "all_payable_amount" to select the meter_number from meter table and its corresponding payable_amount from the bill table.

create view all_payable_amount as
select m.meter_number, b.payable_amount 
from meter m
inner join bill b on m.id=b.meter_id

9. Create a view named "daily_readings" to display the daily hourly readings from h1 to h24 for each meter with meter number displayed.

create view daily_readings as
select r.h1,r.h2,r.h3,r.h4,r.h5,r.h6,r.h7,r.h8,r.h9,r.h10,r.h11,r.h12,r.h13,r.h14,r.h15,r.h16,r.h17,r.h18,r.h19,r.h20,r.h21,r.h22,r.h23,r.h24,m.meter_number
from meter m,electricity_reading r
where m.id=r.meter_id;

10. Create a view named "total_unit_spent_by_commercial" to display the sum of the total_units spent by the commercial connection type buildings.

create view total_unit_spent_by_commercial as
select sum(total_units) as sum_of_total_units
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 = (
        select id
        from electricity_connection_type
        where connection_name = 'Commercial')
	)
  )
)