Why advanced PL/pgSQL matters in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to declare a variable in PL/pgSQL.

PostgreSQL

DECLARE my_var [1]; 

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

AIS

BINTEGER

CAS

DTYPE

Attempts:

3 left

💡 Hint

Common Mistakes

Using keywords like 'IS', 'AS', or 'TYPE' before the data type.

Omitting the data type altogether.

In PL/pgSQL, variables are declared using the variable_name TYPE; syntax inside the DECLARE block. Here, use INTEGER.

2fill in blank

medium

Complete the code to start a PL/pgSQL function.

PostgreSQL

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER) RETURNS INTEGER LANGUAGE plpgsql AS $$ BEGIN RETURN a [1] b; END; $$;

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

A+

B-

C*

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using subtraction or multiplication instead of addition.

Forgetting to use an operator.

The function adds two numbers, so the operator should be +.

3fill in blank

hard

Fix the error in the IF statement condition.

PostgreSQL

IF total [1] 100 THEN RAISE NOTICE 'Total is over 100'; END IF;

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

A=

B<

C==

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '==' instead of '>'.

Using '=' which checks for equality, not greater than.

In PL/pgSQL, to check if total is greater than 100, use the > operator. The == operator is not valid in SQL.

4fill in blank

hard

Fill both blanks to complete the loop that sums numbers from 1 to 5.

PostgreSQL

DECLARE total INTEGER := 0; BEGIN FOR i IN [1] LOOP total := total + i; END LOOP; RETURN [2]; END;

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

A1..5

B0..5

Ctotal

Di

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0..5 which includes zero unnecessarily.

Returning the loop variable instead of the total.

The loop runs from 1 to 5 using 1..5. The function returns the total sum.

5fill in blank

hard

Fill all three blanks to create a conditional that raises a notice if count is zero.

PostgreSQL

IF [1] = [2] THEN RAISE NOTICE '[3] is zero'; END IF;

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

Acount

B0

Dtotal

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'total' instead of 'count'.

Using a string '0' instead of numeric 0.

The condition checks if count equals zero, then raises a notice saying 'count is zero'.