VALUES clause for inline data in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the VALUES clause do in SQL?

The VALUES clause lets you create a set of rows with specified data directly inside a query, without needing a table.

beginner

How do you write a simple VALUES clause with two rows of data?

Use VALUES followed by rows in parentheses, separated by commas. Example: VALUES (1, 'apple'), (2, 'banana')

intermediate

Can you use the VALUES clause as a table in a SELECT statement?

Yes, you can treat the VALUES clause like a temporary table and select from it using an alias.

intermediate

Why might you use the VALUES clause instead of creating a temporary table?

VALUES is quick and simple for small sets of data you want to use just once, without creating or managing a table.

intermediate

How do you assign column names to data from a VALUES clause?

Use an alias with column names after the VALUES clause. Example: VALUES (1, 'a') AS t(id, letter)

What keyword starts the inline data list in SQL?

A ROWS

B DATA

C VALUES

D INLINE

The VALUES keyword is used to start the inline data list.

How do you separate multiple rows in a VALUES clause?

A With semicolons (;)

B With periods (.)

C With colons (:)

D With commas (,)

Rows in a VALUES clause are separated by commas.

Which of these is a valid way to use VALUES in a SELECT?

A SELECT * FROM (VALUES (1, 'x'), (2, 'y')) AS t(id, letter)

B SELECT * FROM VALUES (1, 'x'), (2, 'y')

C VALUES SELECT (1, 'x'), (2, 'y')

D FROM VALUES (1, 'x'), (2, 'y') SELECT *

You must wrap VALUES in parentheses and give it an alias with column names to use in SELECT.

What is a good reason to use VALUES instead of a table?

A To quickly test or use small sets of data inline

B To store large amounts of data permanently

C To create indexes automatically

D To backup data

VALUES is best for quick, small inline data sets, not for permanent storage.

How do you name columns when using VALUES inline?

A Use AS with the VALUES keyword

B Use an alias with column names after VALUES in parentheses

C You cannot name columns in VALUES

D Use the COLUMN keyword

You assign column names by aliasing the VALUES clause with names in parentheses.

Explain how to use the VALUES clause to create inline data and select from it.

Think about how to write rows and give them names to use like a table.

Describe a situation where using the VALUES clause is better than creating a temporary table.

When you want to avoid extra setup and just use some data once.

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