VALUES clause for inline data in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple VALUES clause

What is the output of this SQL query in PostgreSQL?

VALUES (1, 'apple'), (2, 'banana'), (3, 'cherry');

PostgreSQL

VALUES (1, 'apple'), (2, 'banana'), (3, 'cherry');

A Three rows with two columns: (1, 'apple'), (2, 'banana'), (3, 'cherry')

B One row with six columns: 1, 'apple', 2, 'banana', 3, 'cherry'

C Syntax error due to missing SELECT keyword

D Empty result set

💡 Hint

VALUES creates rows of data directly without needing a table.

The VALUES clause returns each tuple as a separate row with columns matching the tuple elements.

❓ query_result

intermediate

Using VALUES with column aliases

What is the output of this query?

SELECT * FROM (VALUES (10, 'red'), (20, 'blue')) AS colors(id, name);

PostgreSQL

SELECT * FROM (VALUES (10, 'red'), (20, 'blue')) AS colors(id, name);

A Error: aliasing columns not allowed in VALUES

B Two rows with columns 'id' and 'name': (10, 'red'), (20, 'blue')

C One row with columns 'id' and 'name' containing (10, 'red', 20, 'blue')

D Empty result set

💡 Hint

You can give names to columns when using VALUES inside FROM.

The alias 'colors(id, name)' names the columns for the inline table created by VALUES.

📝 Syntax

advanced

Identify the syntax error in VALUES usage

Which option contains a syntax error when using the VALUES clause in PostgreSQL?

A VALUES (1, 'a'), (2, 'b');

B SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS t(col1, col2);

C SELECT * FROM (VALUES (1, 'a'), (2, 'b')) t(col1, col2);

D VALUES (1, 'a'), (2, 'b') AS t(col1, col2);

💡 Hint

VALUES cannot be aliased directly without FROM or parentheses.

Option D tries to alias VALUES directly without FROM or parentheses, which is invalid syntax.

❓ optimization

advanced

Best way to insert multiple rows using VALUES

Which query is the most efficient and correct way to insert multiple rows into a table named fruits with columns id and name?

A INSERT INTO fruits (id, name) VALUES (1, 'apple'), (2, 'banana'), (3, 'cherry');

B INSERT INTO fruits VALUES (1, 'apple'), (2, 'banana'), (3, 'cherry');

C INSERT INTO fruits (id, name) SELECT * FROM (VALUES (1, 'apple'), (2, 'banana'), (3, 'cherry'));

D INSERT INTO fruits (id, name) SELECT * FROM (VALUES (1, 'apple'), (2, 'banana'), (3, 'cherry')) AS t;

💡 Hint

Specifying columns is safer and clearer.

Option A explicitly names columns and uses VALUES directly, which is efficient and clear.

🧠 Conceptual

expert

Understanding the scope of VALUES in complex queries

Consider this query:

WITH data AS (VALUES (1, 'x'), (2, 'y')) SELECT * FROM data WHERE column1 = 2;

What will happen when this query runs in PostgreSQL?

PostgreSQL

WITH data AS (VALUES (1, 'x'), (2, 'y')) SELECT * FROM data WHERE column1 = 2;

A Returns two rows: (1, 'x') and (2, 'y')

B Returns one row: (2, 'y')

C Error: column1 is not recognized because VALUES columns are unnamed by default

D Empty result set

💡 Hint

VALUES columns need explicit names in CTEs to be referenced.

Without column aliases, the columns in VALUES have no names, so 'column1' is unknown causing an error.