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.

Practice

1. Which of the following is true about SQL functions compared to procedures?

easy

A. Functions return a value and can be used in SELECT queries.

B. Functions perform actions but do not return any value.

C. Procedures always return a value and can be used in WHERE clauses.

D. Procedures can be used inside SELECT statements like functions.

Solution

1. Step 1: Understand the purpose of functions

   Functions are designed to return a value and can be called within queries like SELECT.

2. Step 2: Compare with procedures

   Procedures perform tasks but do not return values directly and cannot be used inside SELECT statements.

3. Final Answer:

   Functions return a value and can be used in SELECT queries. -> Option A

4. Quick Check:

   Functions return values [OK]

Hint: Functions return values; procedures perform tasks [OK]

Common Mistakes:

• Thinking procedures return values like functions
• Using procedures inside SELECT queries
• Confusing function usage with procedure usage

2. Which of the following is the correct syntax to create a SQL function that returns an integer?

easy

A. CREATE PROCEDURE getCount() BEGIN RETURN 10; END;

B. CREATE PROCEDURE getCount() RETURNS INT BEGIN RETURN 10; END;

C. CREATE FUNCTION getCount() BEGIN RETURN 10; END;

D. CREATE FUNCTION getCount() RETURNS INT BEGIN RETURN 10; END;

Solution

1. Step 1: Identify function syntax

   Functions require specifying RETURNS type and a RETURN statement inside BEGIN...END.

2. Step 2: Check options for correctness

   CREATE FUNCTION getCount() RETURNS INT BEGIN RETURN 10; END; correctly declares a function with RETURNS INT and returns a value. Others either use PROCEDURE or miss RETURNS.

3. Final Answer:

   CREATE FUNCTION getCount() RETURNS INT BEGIN RETURN 10; END; -> Option D

4. Quick Check:

   Functions need RETURNS and RETURN [OK]

Hint: Functions need RETURNS and RETURN statements [OK]

Common Mistakes:

• Using PROCEDURE keyword for functions
• Omitting RETURNS clause in function
• Using RETURN in procedures incorrectly

3. Given the following SQL code, what will be the output of SELECT calculateTax(100); if calculateTax is a function that returns 10% of the input amount?

CREATE FUNCTION calculateTax(amount INT) RETURNS INT BEGIN RETURN amount * 0.1; END;

medium

A. 100

B. 0.1

C. 10

D. Error: Cannot multiply INT by decimal

Solution

1. Step 1: Understand function logic

   The function multiplies the input amount by 0.1 to calculate 10% tax.

2. Step 2: Calculate output for input 100

   100 * 0.1 = 10, so the function returns 10.

3. Final Answer:

   10 -> Option C

4. Quick Check:

   100 * 0.1 = 10 [OK]

Hint: Multiply input by 0.1 to get 10% tax [OK]

Common Mistakes:

• Confusing 0.1 with 10
• Expecting decimal output when function returns INT
• Assuming error due to type mismatch

4. You wrote this procedure to update a user's email:

CREATE PROCEDURE updateEmail(userId INT, newEmail VARCHAR(100)) BEGIN UPDATE users SET email = newEmail WHERE id = userId; END;

When you try to call it with SELECT updateEmail(1, '[e-mail]');, you get an error. What is the problem?

medium

A. Procedures cannot be called inside SELECT statements.

B. The procedure is missing a RETURN statement.

C. The UPDATE statement syntax is incorrect.

D. The procedure parameters must be declared as OUT.

Solution

1. Step 1: Understand procedure usage

   Procedures perform actions but do not return values and cannot be called inside SELECT queries.

2. Step 2: Identify error cause

   Calling a procedure with SELECT causes error because procedures are invoked with CALL, not SELECT.

3. Final Answer:

   Procedures cannot be called inside SELECT statements. -> Option A

4. Quick Check:

   Use CALL for procedures, not SELECT [OK]

Hint: Call procedures with CALL, not SELECT [OK]

Common Mistakes:

• Trying to use SELECT to call procedures
• Expecting procedures to return values
• Misunderstanding parameter directions

5. You need to create a reusable SQL routine that calculates the discount price and also logs the discount application in a table. Which approach is best?

hard

A. Use a single function to calculate discount and log the discount inside it.

B. Use a function to calculate discount and a procedure to log the discount.

C. Use a procedure to calculate discount and return the discounted price.

D. Use a procedure only to calculate discount and log the discount.

Solution

1. Step 1: Separate calculation and action

   Functions should be used for calculations returning values; procedures for actions like logging.

2. Step 2: Apply best practice

   Use a function to return the discount price, and a procedure to perform the logging task separately.

3. Final Answer:

   Use a function to calculate discount and a procedure to log the discount. -> Option B

4. Quick Check:

   Functions calculate; procedures perform tasks [OK]

Hint: Calculate with functions; log with procedures [OK]

Common Mistakes:

• Trying to do logging inside functions
• Expecting procedures to return values
• Mixing calculation and side effects in one routine