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;
/