Question

Write an aggregate called selectivesum that sums the values of numbers in a column, but only if the value is present in the table t . E.g., suppose t contains the values 0, 1, 2, 3, 4, 5, 6, 7, 8, 9. Suppose that table m has a column y of type integer. Here is the table M

Psuedocode for the sfunc function:The result of the query SELECT selectivesum(y) FROM m; is 7.

step(x, y)

if(y is in t)

return x + y

else

return x

Here is the code to create the table m:

drop table if exists m;

create table m (x text, y integer);

insert into m values (‘cat’, 4), (‘erpi’, 12), (‘llar’, 3);

Answer 1

Given-

Table m :

consisting of the datatypes text and integer.

Table t:

contains numeric values

selectivesum() -> function which sums the values in a particular column only if they are present in the table .

Code-

CREATE OR REPLACE FUNCTION selectivesum(y)

RETURN number IS

total number(2) :=0;

BEGIN

IF y is in t THEN

total := total+y;

RETURN total;

END;

Explanation-

Function is created with the given parameter and then the function checks if the value of y ( integer ) which is in the table m and adds to the total variable which is initialized as zero in the beginning of the procedure, if it is present in another table t, which consists of only numerical values, and then the return statement is used to give the total computed.