User-defined functions in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a simple function that returns the square of a number.

SQL

CREATE FUNCTION square(num INT) RETURNS INT AS $$ BEGIN RETURN num [1] num; END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

A+

B*

C-

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using '+' instead of '*' will add the number to itself, not square it.

Using '-' or '/' will cause incorrect results or errors.

The function multiplies the input number by itself to return its square.

2fill in blank

medium

Complete the code to create a function that returns the length of a given text.

SQL

CREATE FUNCTION text_length(txt TEXT) RETURNS INT AS $$ BEGIN RETURN [1](txt); END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

Acount

Bchar_length

Clength

Dsize

Attempts:

3 left

💡 Hint

Common Mistakes

Using count which counts rows, not string length.

Using char_length is valid but here only length is accepted.

The length function returns the number of characters in the text.

3fill in blank

hard

Fix the error in the function that calculates the factorial of a number.

SQL

CREATE FUNCTION factorial(n INT) RETURNS INT AS $$ BEGIN IF n = 0 THEN RETURN 1; ELSE RETURN n [1] factorial(n - 1); END IF; END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

A/

B+

C-

D*

Attempts:

3 left

💡 Hint

Common Mistakes

Using '+' will sum numbers incorrectly.

Using '-' or '/' will cause wrong results.

The factorial is calculated by multiplying the number by the factorial of one less than the number.

4fill in blank

hard

Fill both blanks to create a function that returns the maximum of two numbers.

SQL

CREATE FUNCTION max_two(a INT, b INT) RETURNS INT AS $$ BEGIN IF a [1] b THEN RETURN a; ELSE RETURN [2]; END IF; END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

A>

Ba

Cb

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' will reverse the logic.

Returning a in the else branch is incorrect.

The function compares a and b. If a is greater, it returns a, otherwise it returns b.

5fill in blank

hard

Fill all three blanks to create a function that returns a greeting message with the user's name.

SQL

CREATE FUNCTION greet_user(name TEXT) RETURNS TEXT AS $$ BEGIN RETURN 'Hello, ' || [1] || [2] || [3]; END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

Aname

B'!'

C' '

D'?'

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to add space between name and punctuation.

Using wrong punctuation marks.

The function concatenates 'Hello, ' with the user's name, a space, and an exclamation mark to form a greeting.