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.

Practice

1. What does the VALUES clause do in PostgreSQL?

easy

A. Creates inline rows of data without needing a table

B. Deletes rows from a table

C. Updates existing rows in a table

D. Creates a new table in the database

Solution

1. Step 1: Understand the purpose of VALUES clause

   The VALUES clause is used to create rows of data directly in a query without referencing an existing table.

2. Step 2: Compare with other SQL commands

   DELETE removes rows, UPDATE modifies rows, and CREATE TABLE makes new tables, which are different from VALUES usage.

3. Final Answer:

   Creates inline rows of data without needing a table -> Option A

4. Quick Check:

   VALUES clause = inline data rows [OK]

Hint: VALUES = inline rows, no table needed [OK]

Common Mistakes:

• Confusing VALUES with DELETE or UPDATE commands
• Thinking VALUES creates tables
• Assuming VALUES modifies existing data

2. Which of the following is the correct syntax to create two rows with columns id and name using VALUES?

easy

A. VALUES (id=1, name='Alice'), (id=2, name='Bob');

B. VALUES (1, 'Alice'), (2, 'Bob');

C. VALUES (1 'Alice'), (2 'Bob');

D. VALUES 1, 'Alice'; VALUES 2, 'Bob';

Solution

1. Step 1: Check VALUES row syntax

   Each row must be enclosed in parentheses with values separated by commas, like (1, 'Alice').

2. Step 2: Validate options

   VALUES (1, 'Alice'), (2, 'Bob'); correctly uses parentheses and commas. VALUES 1, 'Alice'; VALUES 2, 'Bob'; splits VALUES incorrectly. VALUES (1 'Alice'), (2 'Bob'); misses commas between values. VALUES (id=1, name='Alice'), (id=2, name='Bob'); uses invalid assignment syntax inside VALUES.

3. Final Answer:

   VALUES (1, 'Alice'), (2, 'Bob'); -> Option B

4. Quick Check:

   Parentheses + commas = correct VALUES syntax [OK]

Hint: Use parentheses and commas for each row [OK]

Common Mistakes:

• Omitting parentheses around rows
• Missing commas between values
• Using assignment syntax inside VALUES

3. What is the output of this query?

SELECT * FROM (VALUES (1, 'Red'), (2, 'Blue')) AS colors(id, name);

medium

A. One row with array [(1, 'Red'), (2, 'Blue')]

B. Syntax error due to missing table

C. Empty result set

D. Two rows with columns id and name: (1, 'Red') and (2, 'Blue')

Solution

1. Step 1: Understand VALUES with alias

   The VALUES clause creates two rows. The alias 'colors' names the columns as id and name.

2. Step 2: Result of SELECT * from VALUES

   The query returns two rows: first row (1, 'Red'), second row (2, 'Blue'), with columns id and name.

3. Final Answer:

   Two rows with columns id and name: (1, 'Red') and (2, 'Blue') -> Option D

4. Quick Check:

   VALUES + alias = rows with named columns [OK]

Hint: VALUES with alias returns rows with named columns [OK]

Common Mistakes:

• Expecting a syntax error without a real table
• Thinking VALUES returns arrays
• Assuming empty results without data

4. Identify the error in this query:

SELECT * FROM VALUES (1, 'A'), (2, 'B') AS t(id, name);

medium

A. Missing parentheses around the entire VALUES clause

B. Missing parentheses around VALUES rows

C. Alias must come before VALUES

D. No error, query is correct

Solution

1. Step 1: Check VALUES clause syntax

   VALUES rows must be enclosed in parentheses as a table expression: (VALUES ...)

2. Step 2: Analyze the query

   The query misses parentheses around the entire VALUES clause. It should be: FROM (VALUES ...) AS t(id, name)

3. Final Answer:

   Missing parentheses around the entire VALUES clause -> Option A

4. Quick Check:

   VALUES clause needs parentheses around all rows [OK]

Hint: Wrap all VALUES rows in parentheses when used in FROM [OK]

Common Mistakes:

• Not wrapping VALUES in parentheses in FROM clause
• Placing alias before VALUES
• Assuming no parentheses needed for inline data

5. You want to create a temporary inline table of products with columns product_id, name, and price using VALUES. Which query correctly creates this and selects products priced over 10?

hard

A. SELECT * FROM (VALUES (product_id=1, name='Pen', price=5), (product_id=2, name='Notebook', price=15), (product_id=3, name='Eraser', price=8)) p(product_id, name, price) WHERE price > 10;

B. SELECT * FROM VALUES (1, 'Pen', 5), (2, 'Notebook', 15), (3, 'Eraser', 8) AS p(product_id, name, price) WHERE price > 10;

C. SELECT * FROM (VALUES (1, 'Pen', 5), (2, 'Notebook', 15), (3, 'Eraser', 8)) AS p(product_id, name, price) WHERE price > 10;

D. SELECT * FROM (VALUES (1, 'Pen', 5), (2, 'Notebook', 15), (3, 'Eraser', 8)) AS p WHERE price > 10;

Solution

1. Step 1: Check correct VALUES syntax with alias and columns

   The VALUES clause must be enclosed in parentheses and aliased with column names, like SELECT * FROM (VALUES (1, 'Pen', 5), (2, 'Notebook', 15), (3, 'Eraser', 8)) AS p(product_id, name, price) WHERE price > 10.

2. Step 2: Validate options

   A uses invalid assignment syntax (product_id=1 etc.) inside VALUES rows. B misses parentheses around the entire VALUES clause. C is correct. D misses column names in the alias, making 'price' undefined.

3. Final Answer:

   SELECT * FROM (VALUES (1, 'Pen', 5), (2, 'Notebook', 15), (3, 'Eraser', 8)) AS p(product_id, name, price) WHERE price > 10; -> Option C

4. Quick Check:

   VALUES + (parens) + AS alias(columns) + WHERE = correct [OK]

Hint: Wrap VALUES in parentheses, alias with columns, then filter [OK]

Common Mistakes:

• Forgetting parentheses around VALUES
• Not aliasing columns when filtering by column name
• Using assignment syntax like product_id=1 in VALUES rows
• Filtering on column without alias or column names