Parameter binding mental model in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of parameter binding with positional placeholders

Consider the following SQL query with parameter binding using positional placeholders:

SELECT * FROM users WHERE age > ? AND city = ?;

If the parameters are bound as (30, 'New York'), what will be the result of the query assuming the users table has these rows:

id | name | age | city
1 | Alice | 25 | New York
2 | Bob | 35 | New York
3 | Carol | 40 | Boston

Which rows will be returned?

A No rows will be returned

B Only the row with id 2 (Bob)

C Only the row with id 1 (Alice)

D Rows with id 2 (Bob) and 3 (Carol)

💡 Hint

Remember the order of parameters matches the placeholders in the query.

The query filters users with age > 30 and city = 'New York'. Only Bob (id 2) satisfies both conditions.

🧠 Conceptual

intermediate

Understanding named parameter binding

In SQL, named parameters allow binding values by name instead of position.

Given the query:

SELECT * FROM products WHERE category = :cat AND price < :max_price;

Which of the following parameter bindings is correct to find products in category 'Books' priced below 20?

A {'cat': 'Books', 'max_price': 20}

B {':cat': 'Books', ':max_price': 20}

C {':category': 'Books', ':price': 20}

D {'category': 'Books', 'price': 20}

💡 Hint

Named parameters usually do not include the colon in the binding dictionary keys.

When binding named parameters, the keys should match the parameter names without the colon prefix.

📝 Syntax

advanced

Identify the syntax error in parameter binding

Which of the following SQL parameter binding syntaxes will cause a syntax error when executed?

A SELECT * FROM orders WHERE order_date = ?order_date;

B SELECT * FROM orders WHERE order_date = :order_date;

C SELECT * FROM orders WHERE order_date = $order_date;

D SELECT * FROM orders WHERE order_date = ?;

💡 Hint

Check which placeholder formats are valid in standard SQL parameter binding.

The placeholder '?order_date' is invalid syntax; '?' is positional and cannot be combined with a name.

❓ optimization

advanced

Performance impact of parameter binding

Why is using parameter binding in SQL queries generally better for performance compared to string concatenation of values?

A Parameter binding reduces the size of the query string sent to the database.

B Parameter binding automatically indexes the columns used in the query.

C Parameter binding encrypts the query parameters for security.

D Parameter binding allows the database to cache the query plan and reuse it with different values.

💡 Hint

Think about how databases optimize repeated queries.

Using parameters lets the database prepare the query once and reuse the plan, improving speed.

🔧 Debug

expert

Debugging incorrect parameter binding causing no results

A developer runs this query with parameter binding:

SELECT * FROM employees WHERE department = ? AND salary > ?;

They bind parameters as ('Sales', '50000') but get no results, even though there are employees in Sales with salary above 50000.

What is the most likely cause?

A The query uses positional placeholders but the parameters are named.

B The order of parameters is reversed; salary should be first, then department.

C The salary parameter is bound as a string instead of a number, causing a type mismatch.

D The table employees does not have a salary column.

💡 Hint

Check the data types of bound parameters versus column types.

Binding salary as a string can cause the database to compare differently, resulting in no matches.