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')
        )
      )
    )