Function vs procedure decision in SQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a function that returns the sum of two numbers.

SQL

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT 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 - instead of + will subtract instead of add.

The plus sign (+) adds two numbers, which is what the function should do.

2fill in blank

medium

Complete the code to create a procedure that inserts a new user into the users table.

SQL

CREATE PROCEDURE add_user(name VARCHAR) LANGUAGE plpgsql AS $$ BEGIN INSERT INTO users (username) VALUES ([1]); END; $$;

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

Aname

Busername

C'name'

Duser

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around the parameter name inserts a string literal instead of the variable value.

The procedure uses the parameter 'name' to insert the username value.

3fill in blank

hard

Fix the error in the function that should return the length of a string.

SQL

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

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

Alength

Blen

Ccount

Dsize

Attempts:

3 left

💡 Hint

Common Mistakes

Using len() causes an error because it's not a valid SQL function.

In SQL, the function to get string length is length(), not len().

4fill in blank

hard

Fill both blanks to create a procedure that updates a user's email by user ID.

SQL

CREATE PROCEDURE update_email(user_id INT, new_email VARCHAR) LANGUAGE plpgsql AS $$ BEGIN UPDATE users SET email = [1] WHERE id = [2]; END; $$;

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

Anew_email

Buser_id

C'new_email'

D'user_id'

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around parameters causes the query to use literal strings instead of variable values.

The procedure uses the parameters new_email and user_id directly without quotes to update the record.

5fill in blank

hard

Fill all three blanks to create a function that returns the average score from a scores table for a given player.

SQL

CREATE FUNCTION avg_score(player_name VARCHAR) RETURNS NUMERIC LANGUAGE plpgsql AS $$ BEGIN RETURN (SELECT AVG([1]) FROM scores WHERE [3] = [2]); END; $$;

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

Ascore

Bplayer_name

C'player_name'

Dplayer

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the parameter in quotes treats it as a string literal, not a variable.

The function calculates the average of the score column, filtering rows where player equals the parameter player_name. The parameter is used without quotes, and the column name player is used in the WHERE clause.