List partitioning by category in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a partitioned table by category.

PostgreSQL

CREATE TABLE products (id SERIAL, name TEXT, category TEXT, PRIMARY KEY (id, category)) PARTITION BY [1] (category);

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

ARANGE

BCOLUMN

CHASH

DLIST

Attempts:

3 left

💡 Hint

Common Mistakes

Using RANGE instead of LIST for category partitioning.

Using HASH which is for hashing values, not categories.

The LIST partitioning method is used to partition a table by specific values in a column, such as categories.

2fill in blank

medium

Complete the code to create a partition for the 'electronics' category.

PostgreSQL

CREATE TABLE products_electronics PARTITION OF products FOR VALUES [1] ('electronics');

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

ABETWEEN

BFROM

CIN

DLIKE

Attempts:

3 left

💡 Hint

Common Mistakes

Using FROM which is for RANGE partitions.

Using BETWEEN which is for ranges, not lists.

For LIST partitioning, the IN keyword specifies the list of values for the partition.

3fill in blank

hard

Fix the error in the partition creation statement.

PostgreSQL

CREATE TABLE products_clothing PARTITION OF products FOR VALUES [1] ('clothing', 'apparel');

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

AIN

BBETWEEN

CFROM

DLIKE

Attempts:

3 left

💡 Hint

Common Mistakes

Using FROM or BETWEEN which are for RANGE partitions.

Using LIKE which is not valid here.

Multiple values in a LIST partition must be specified with IN and a list of values.

4fill in blank

hard

Fill both blanks to query all products in the 'books' or 'magazines' categories.

PostgreSQL

SELECT * FROM products WHERE category [1] ('books', 'magazines') ORDER BY [2];

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

AIN

Bcategory

CBETWEEN

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Using BETWEEN which is for ranges, not lists.

Ordering by category instead of name.

The IN keyword filters rows by matching any value in the list. Ordering by name sorts the results alphabetically.

5fill in blank

hard

Fill all three blanks to insert a new product into the 'furniture' partition.

PostgreSQL

INSERT INTO products ([1], [2], [3]) VALUES (DEFAULT, 'Chair', 'furniture');

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

Aid

Bname

Ccategory

Dprice

Attempts:

3 left

💡 Hint

Common Mistakes

Including price column which is not in the table.

Mixing up column order.

The columns are id (auto-generated), name, and category. The values match these columns.