GENERATED columns (stored and virtual) in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a table with a generated column that stores the sum of two columns.

PostgreSQL

CREATE TABLE sales (
  price NUMERIC,
  quantity INT,
  total NUMERIC GENERATED ALWAYS AS (price [1] quantity) STORED
);

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

A+

B/

C*

D-

Attempts:

3 left

💡 Hint

Common Mistakes

Using multiplication or division instead of addition.

Forgetting to specify STORED for the generated column.

The generated column total calculates the sum of price and quantity using the plus operator +.

2fill in blank

medium

Complete the code to create a virtual generated column that concatenates first and last names.

PostgreSQL

CREATE TABLE users (
  first_name TEXT,
  last_name TEXT,
  full_name TEXT GENERATED ALWAYS AS (first_name [1] last_name) VIRTUAL
);

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

A&&

B|| ' ' ||

CCONCAT

D+ ' ' +

Attempts:

3 left

💡 Hint

Common Mistakes

Using plus + for string concatenation, which is invalid in SQL.

Using unsupported operators like &&.

In PostgreSQL, the string concatenation operator is ||. To add a space between names, we use || ' ' ||.

3fill in blank

hard

Fix the error in the generated column definition to calculate the discounted price.

PostgreSQL

CREATE TABLE products (
  price NUMERIC,
  discount_rate NUMERIC,
  discounted_price NUMERIC GENERATED ALWAYS AS (price [1] discount_rate) STORED
);

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

A/

B+

C-

D*

Attempts:

3 left

💡 Hint

Common Mistakes

Using subtraction instead of multiplication.

Using division which changes the meaning.

The discounted price is calculated by multiplying price by discount_rate. Using * is correct.

4fill in blank

hard

Fill both blanks to create a stored generated column that calculates the area of a rectangle.

PostgreSQL

CREATE TABLE rectangles (
  width INT,
  height INT,
  area INT GENERATED ALWAYS AS (width [1] height) [2]
);

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

A*

B+

CSTORED

DVIRTUAL

Attempts:

3 left

💡 Hint

Common Mistakes

Using addition instead of multiplication.

Using VIRTUAL when STORED is required.

The area is width multiplied by height, so * is used. The column is stored, so STORED is correct.

5fill in blank

hard

Fill all three blanks to create a virtual generated column that calculates the full address by combining street, city, and zip code.

PostgreSQL

CREATE TABLE addresses (
  street TEXT,
  city TEXT,
  zip_code TEXT,
  full_address TEXT GENERATED ALWAYS AS (street [1] city [2] zip_code) [3]
);

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

A|| ', ' ||

B|| ' ' ||

CVIRTUAL

DSTORED

Attempts:

3 left

💡 Hint

Common Mistakes

Using plus signs for string concatenation.

Mixing up STORED and VIRTUAL keywords.

To build the full address, we concatenate street and city with a comma and space (|| ', ' ||), then city and zip code with a space (|| ' ' ||). The column is virtual, so VIRTUAL is used.