Variable declaration and assignment in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to declare a variable named counter of type integer.

PostgreSQL

DECLARE counter [1];

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

ABOOLEAN

BTEXT

CINTEGER

DDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEXT instead of INTEGER for numeric variables.

Forgetting the semicolon at the end.

The INTEGER type is used to declare a variable that holds whole numbers.

2fill in blank

medium

Complete the code to assign the value 10 to the variable counter.

PostgreSQL

counter [1] 10;

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

A<-

B:=

C==

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using = instead of := for assignment.

Using == which is not valid in PostgreSQL.

In PostgreSQL procedural language, := is used to assign values to variables.

3fill in blank

hard

Fix the error in the variable declaration to declare username as a text variable.

PostgreSQL

DECLARE username [1];

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

AINT

BVARCHAR

CBOOLEAN

DTEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using INT for text variables.

Using BOOLEAN instead of a string type.

The TEXT type is used for variable-length strings in PostgreSQL.

4fill in blank

hard

Fill both blanks to declare a variable total as integer and assign it the value 0.

PostgreSQL

DECLARE total [1];
BEGIN
  total [2] 0;
END;

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

AINTEGER

B=

C:=

DTEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using = instead of := for assignment.

Declaring the variable as TEXT instead of INTEGER.

Use INTEGER to declare the variable type and := to assign the value.

5fill in blank

hard

Fill all three blanks to declare a variable flag as boolean, assign it true, and then reassign it false.

PostgreSQL

DECLARE flag [1];
BEGIN
  flag [2] TRUE;
  flag [3] FALSE;
END;

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

ABOOLEAN

B:=

C=

DINTEGER

Attempts:

3 left

💡 Hint

Common Mistakes

Using = instead of := for assignment.

Declaring the variable as INTEGER instead of BOOLEAN.

Declare flag as BOOLEAN. Use := to assign values in PL/pgSQL.

Practice

1. What is the correct way to declare a variable named counter of type integer in a PL/pgSQL block?

easy

A. DECLARE counter integer;

B. counter integer;

C. DECLARE counter integer = 0;

D. counter := integer;

Solution

1. Step 1: Understand variable declaration syntax

   In PL/pgSQL, variables are declared inside a DECLARE section without repeating the DECLARE keyword for each variable. The correct syntax is: variable_name data_type;

2. Step 2: Identify the correct declaration

   The line counter integer; correctly declares the variable inside the DECLARE block. Including the DECLARE keyword before each variable is incorrect.

3. Final Answer:

   counter integer; -> Option B

4. Quick Check:

   Declare variables inside DECLARE block without repeating DECLARE [OK]

Hint: Declare variables inside DECLARE block with type only [OK]

Common Mistakes:

• Omitting DECLARE keyword
• Assigning value during declaration without :=
• Using := in declaration line

2. Which of the following is the correct syntax to assign the value 10 to a variable count after it has been declared in PL/pgSQL?

easy

A. count = 10;

B. count == 10;

C. SET count = 10;

D. count := 10;

Solution

1. Step 1: Recall assignment operator in PL/pgSQL

   PL/pgSQL uses := to assign values to variables, not = or ==.

2. Step 2: Identify correct assignment syntax

   count := 10; uses count := 10; which is the correct way to assign a value.

3. Final Answer:

   count := 10; -> Option D

4. Quick Check:

   Use := for assignment in PL/pgSQL [OK]

Hint: Use := to assign values to variables [OK]

Common Mistakes:

• Using = instead of :=
• Using SET keyword incorrectly
• Using == like in other languages

3. Consider the following PL/pgSQL block:

DECLARE
  total integer := 5;
BEGIN
  total := total + 3;
  RAISE NOTICE '%', total;
END;

What will be the output when this block runs?

medium

A. 8

B. 5

C. 3

D. Error: variable not initialized

Solution

1. Step 1: Analyze initial value assignment

   The variable total is declared and initialized to 5.

2. Step 2: Calculate the new value after addition

   The statement total := total + 3; adds 3 to 5, resulting in 8.

3. Final Answer:

   8 -> Option A

4. Quick Check:

   5 + 3 = 8 [OK]

Hint: Add assigned values step-by-step to find final result [OK]

Common Mistakes:

• Ignoring initial value and assuming zero
• Confusing assignment operator with equality
• Expecting error due to missing BEGIN

4. Identify the error in the following PL/pgSQL code snippet:

DECLARE
  name text;
BEGIN
  name = 'Alice';
END;

medium

A. Incorrect assignment operator used

B. Missing DECLARE keyword

C. Variable name not declared

D. Missing semicolon after BEGIN

Solution

1. Step 1: Check variable declaration

   The variable name is declared correctly with type text.

2. Step 2: Check assignment syntax

   The assignment uses = which is incorrect in PL/pgSQL; it should use :=.

3. Final Answer:

   Incorrect assignment operator used -> Option A

4. Quick Check:

   Use := for assignment, not = [OK]

Hint: Use := for assignment, not = [OK]

Common Mistakes:

• Using = instead of :=
• Forgetting semicolon after assignment
• Confusing declaration and assignment syntax

5. You want to declare two variables a and b as integers, assign a the value 4, and then assign b the value of a multiplied by 3. Which of the following PL/pgSQL code snippets correctly does this?

hard

A. DECLARE
b integer := a * 3;
a integer := 4;
BEGIN
NULL;
END;

B. DECLARE
a integer;
b integer;
BEGIN
a = 4;
b = a * 3;
END;

C. DECLARE
a integer;
b integer;
BEGIN
a := 4;
b := a * 3;
END;

D. DECLARE
a integer := 4;
b integer;
BEGIN
b = a * 3;
END;

Solution

1. Step 1: Check variable declaration and initialization

   DECLARE a integer; b integer; BEGIN a := 4; b := a * 3; END; declares both variables without initial values, then assigns values inside the BEGIN block using correct assignment operator :=.

2. Step 2: Verify assignment and calculation

   It assigns a := 4; and then b := a * 3;, which correctly sets b to 12.

3. Final Answer:

   correctly declares and assigns variables with := -> Option C

4. Quick Check:

   Declare first, assign with := inside BEGIN [OK]

Hint: Declare variables first, assign values inside BEGIN with := [OK]

Common Mistakes:

• Using = instead of :=
• Assigning values during declaration with expressions
• Missing BEGIN block for assignments