Week9 - Stored Functions / Design

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

1. Create a function named 'findTotalUnit' which takes meterNumber as its input parameter with varchar as its datatype and it returns the average total units consumed by the meter number for a month.

Hints:
Function name: findTotalUnit
Input parameter: meterNumber is type of varchar

Design Rules:
If the meterNumber passed as input matches with the meter_number in the table then it returns the average total units for the given input.

    create or replace function findTotalUnit(meterNumber in varchar2)
    return varchar is
        avgUnits varchar(255);
    begin
        select avg(total_units)
        into avgUnits
        from bill
        where meter_id in (
          select id
          from meter
          where meter_number = meterNumber)
        group by meter_id;

        return avgUnits;
    end;
    /

2. Create a function named 'findCountofBuildingType' which have the connectionName as its input parameter and it returns the count of the building type for the given input.

Hints:
Function Name: findCountofBuildingType
Input parameter: connectionName is type of varchar

Design Rules:
If the connectionName passed as input matches with the connection_name in the table then it returns the count of the building types for given input.

    create or replace function findCountofBuildingType(connectionName in varchar2)
    return int is
    cntBldng int;
    begin
    select count(name) into cntBldng
    from building_type
    where connection_type_id =(
      select id
      from electricity_connection_type
      where connection_name = connectionName);

    return cntBldng;
    end;
    /

3. Create a function named 'findBuildingType' which takes meterNumber as its input parameter and returns the corresponding building type for the given input.

Hints:
Function Name: findBuildingType
Input parameter: meterNumber is type of varchar

Design Rules:
If themeterNumber passedas input matches with the meter_number in the table then it returns the building_type for the given input.

    create or replace function findBuildingType(meterNumber in varchar2)
    return varchar is
    name varchar(255);
    begin
    select name into name
    from building_type
    where id = (
      select building_type_id
      from building
      where id = (
        select building_id
        from meter
        where meter_number = meterNumber));

        return name;
    end;
    /

4. Create a function named 'showPayedOrNot' which takes meterNumber as its input parameter and returns the String "Payed" or "Not Payed" from the bill corresponding to the given input.

Hints:
Function name: showPayedOrNot
Input parameter: meterNumber is type of varchar

Design Rules:
1) If the meterNumber passed as input matches with the meter_number in the table, if 'is_payed' value get as '0' then it returns 'Payed' for the given input.

2) If the meterNumber passed as input matches with the meter_number in the table, if 'is_payed' value get as '1' then it returns 'Not Payed' for the given input

    create or replace function showPayedOrNot(meterNumber in varchar2)
    return varchar is
    paid varchar(255);
    begin
    select case when is_payed = 0 then 'Payed' else 'Not Payed' end into paid
    from bill
    where meter_id = (
      select id
      from meter
      where meter_number = meterNumber);
      return paid;
    end;
    /

5. Create a function named 'ownerHavingMaximumFineAmount' which returns the ownerName ofthe building who have maximum fine amount in the bill.

Hints:
Function Name: ownerHavingMaximumFineAmount
Input Parameter : none

    create or replace function ownerHavingMaximumFineAmount
    return varchar is
    ownerName varchar(255);
    begin
    select owner_name into ownerName
    from building
    where id in (
      select building_id
      from meter
      where id in (
        select meter_id
        from bill
        where fine_amount in (
          select max(fine_amount)
          from bill)));
      return ownerName;
    end;
    /