Dynamic SQL with EXECUTE in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to execute a dynamic SQL query stored in the variable.

PostgreSQL

EXECUTE [1];

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

Aquery_string

B'query_string'

CEXEC query_string

DEXECUTE query_string()

Attempts:

3 left

💡 Hint

Common Mistakes

Putting quotes around the variable name, which makes it a string literal.

Trying to call EXECUTE as a function.

The EXECUTE command runs the SQL command stored in the variable query_string. You do not put quotes around the variable name.

2fill in blank

medium

Complete the code to build a dynamic SQL query that selects all columns from a table named in the variable.

PostgreSQL

query_string := 'SELECT * FROM ' || [1] || ';';

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

A'TABLE_NAME'

B'table_name'

Ctable_name

DTABLE_NAME

Attempts:

3 left

💡 Hint

Common Mistakes

Adding quotes around the variable name, which breaks the query.

Using uppercase variable names that are not defined.

To build the query, concatenate the variable table_name without quotes so the table name is inserted dynamically.

3fill in blank

hard

Fix the error in the dynamic SQL execution by completing the code.

PostgreSQL

EXECUTE [1] USING user_id;

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

A'SELECT * FROM users WHERE id = $1'

Bquery_string

C'query_string'

DEXEC query_string

Attempts:

3 left

💡 Hint

Common Mistakes

Putting quotes around the query variable.

Trying to use EXEC instead of EXECUTE.

The variable query_string should be passed directly to EXECUTE. The query string itself should already contain the placeholder $1 for the parameter.

4fill in blank

hard

Fill both blanks to create a dynamic SQL query that selects a column and table dynamically.

PostgreSQL

query_string := 'SELECT ' || [1] || ' FROM ' || [2] || ';';

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

Acolumn_name

B'column_name'

Ctable_name

D'table_name'

Attempts:

3 left

💡 Hint

Common Mistakes

Adding quotes around variable names.

Mixing up column and table variable names.

Use the variables column_name and table_name directly to build the query dynamically without quotes.

5fill in blank

hard

Fill all three blanks to build and execute a dynamic SQL query with a parameter.

PostgreSQL

query_string := 'SELECT * FROM ' || [1] || ' WHERE ' || [2] || ' = $1'; EXECUTE [3] USING param_value;

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

Atable_name

Bcolumn_name

Cquery_string

D'table_name'

Attempts:

3 left

💡 Hint

Common Mistakes

Quoting variable names in the query string.

Passing a quoted string instead of the query variable to EXECUTE.

Use the variables table_name and column_name to build the query string, then execute the query_string with the parameter param_value.